from fastapi import APIRouter, Depends
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func
import uuid

from app.core.database import get_db
from app.core.security import require_permission
from app.models.yard_models import VehicleModelMaster
from app.models.bom_models import ProductMaster, BomMaster, SpecialFeatureBom
from app.models.dealer_models import SpecialFeatureOptionMaster, SpecialFeatureMaster, ModelBodyTypeMap, ModelSegmentMap, CustomerSegmentMaster, BodyTypeMaster

router = APIRouter(dependencies=[Depends(require_permission('BOM', 'view'))])

@router.get("/dashboard")
async def get_bom_dashboard(db: AsyncSession = Depends(get_db)):
    # Base Models Coverage
    # Join VehicleModelMaster with ProductMaster (via model_number == product_code)
    # and BomMaster to see if it has a BOM.
    base_query = select(
        VehicleModelMaster.model_number,
        BodyTypeMaster.name.label("body_type_name"),
        CustomerSegmentMaster.name.label("segment_name"),
        ProductMaster.product_id,
        func.count(BomMaster.bom_id).label("bom_count")
    ).join(
        ModelBodyTypeMap, ModelBodyTypeMap.model_id == VehicleModelMaster.model_id
    ).join(
        BodyTypeMaster, BodyTypeMaster.body_type_id == ModelBodyTypeMap.body_type_id
    ).join(
        ModelSegmentMap, ModelSegmentMap.model_id == VehicleModelMaster.model_id
    ).join(
        CustomerSegmentMaster, CustomerSegmentMaster.segment_id == ModelSegmentMap.segment_id
    ).outerjoin(
        ProductMaster, ProductMaster.product_code == func.concat(
            func.upper(func.replace(func.trim(BodyTypeMaster.name), ' ', '_')),
            '___',
            func.upper(func.replace(func.trim(CustomerSegmentMaster.name), ' ', '_')),
            '___',
            func.upper(func.replace(func.trim(VehicleModelMaster.model_number), ' ', '_'))
        )
    ).outerjoin(
        BomMaster, BomMaster.product_id == ProductMaster.product_id
    ).group_by(
        VehicleModelMaster.model_id, BodyTypeMaster.body_type_id, CustomerSegmentMaster.segment_id, ProductMaster.product_id
    )
    
    base_result = await db.execute(base_query)
    base_models = base_result.all()
    
    total_base_models = len(base_models)
    missing_base_boms = [
        {"model_number": r.model_number, "body_type_name": r.body_type_name, "segment_name": r.segment_name, "product_id": str(r.product_id) if r.product_id else None}
        for r in base_models if r.bom_count == 0
    ]
    
    # Feature Options Coverage (Exclude 'Not Required' options)
    feature_query = select(
        SpecialFeatureOptionMaster.option_id,
        SpecialFeatureOptionMaster.option_value,
        SpecialFeatureMaster.feature_name,
        func.count(SpecialFeatureBom.id).label("mapped_count")
    ).join(
        SpecialFeatureMaster, SpecialFeatureMaster.feature_id == SpecialFeatureOptionMaster.feature_id
    ).outerjoin(
        SpecialFeatureBom, SpecialFeatureBom.option_id == SpecialFeatureOptionMaster.option_id
    ).where(
        func.lower(func.trim(SpecialFeatureOptionMaster.option_value)) != 'not required'
    ).group_by(
        SpecialFeatureOptionMaster.option_id, SpecialFeatureMaster.feature_id
    )
    
    feature_result = await db.execute(feature_query)
    feature_options = feature_result.all()
    
    total_feature_options = len(feature_options)
    missing_feature_boms = [
        {"option_id": str(r.option_id), "option_value": r.option_value, "feature_name": r.feature_name}
        for r in feature_options if r.mapped_count == 0
    ]
    
    return {
        "kpis": {
            "total_base_models": total_base_models,
            "configured_base_models": total_base_models - len(missing_base_boms),
            "total_feature_options": total_feature_options,
            "configured_feature_options": total_feature_options - len(missing_feature_boms),
        },
        "action_required": {
            "missing_base_boms": missing_base_boms,
            "missing_feature_boms": missing_feature_boms
        }
    }

@router.get("/base-models")
async def get_bom_base_models(db: AsyncSession = Depends(get_db)):
    base_query = select(
        VehicleModelMaster.model_id,
        VehicleModelMaster.model_number,
        VehicleModelMaster.image_url,
        VehicleModelMaster.is_active,
        BodyTypeMaster.body_type_id,
        BodyTypeMaster.name.label("body_type_name"),
        CustomerSegmentMaster.segment_id,
        CustomerSegmentMaster.name.label("segment_name"),
        ProductMaster.product_id,
        BomMaster.bom_id,
        BomMaster.bom_number
    ).join(
        ModelBodyTypeMap, ModelBodyTypeMap.model_id == VehicleModelMaster.model_id
    ).join(
        BodyTypeMaster, BodyTypeMaster.body_type_id == ModelBodyTypeMap.body_type_id
    ).join(
        ModelSegmentMap, ModelSegmentMap.model_id == VehicleModelMaster.model_id
    ).join(
        CustomerSegmentMaster, CustomerSegmentMaster.segment_id == ModelSegmentMap.segment_id
    ).outerjoin(
        ProductMaster, ProductMaster.product_code == func.concat(
            func.upper(func.replace(func.trim(BodyTypeMaster.name), ' ', '_')),
            '___',
            func.upper(func.replace(func.trim(CustomerSegmentMaster.name), ' ', '_')),
            '___',
            func.upper(func.replace(func.trim(VehicleModelMaster.model_number), ' ', '_'))
        )
    ).outerjoin(
        BomMaster, BomMaster.product_id == ProductMaster.product_id
    ).order_by(VehicleModelMaster.model_number, BodyTypeMaster.name, CustomerSegmentMaster.name)
    
    result = await db.execute(base_query)
    rows = result.all()
    
    response = []
    for r in rows:
        response.append({
            "model_id": str(r.model_id),
            "model_number": r.model_number,
            "body_type_name": r.body_type_name,
            "segment_name": r.segment_name,
            "image_url": r.image_url,
            "is_active": r.is_active,
            "product_id": str(r.product_id) if r.product_id else None,
            "bom_id": str(r.bom_id) if r.bom_id else None,
            "bom_number": r.bom_number
        })
    return response

@router.get("/feature-versions/{option_id}")
async def get_feature_versions(option_id: uuid.UUID, db: AsyncSession = Depends(get_db)):
    """Feature BOMs are global: an option has one parts list that applies to every model."""
    rm_count = (await db.execute(
        select(func.count(SpecialFeatureBom.id)).where(SpecialFeatureBom.option_id == option_id)
    )).scalar_one()
    if not rm_count:
        return []
    return [{
        "model_id": None,
        "body_type_id": None,
        "segment_id": None,
        "model_number": "All Models",
        "body_type_name": "All Body Types",
        "segment_name": "All Segments",
        "rm_count": rm_count,
    }]


@router.get("/features")
async def get_bom_features(db: AsyncSession = Depends(get_db)):
    """Every feature option with the number of raw materials mapped to it."""
    subquery = select(
        SpecialFeatureBom.option_id,
        func.count(SpecialFeatureBom.id).label("total_rm_count")
    ).group_by(SpecialFeatureBom.option_id).subquery()

    query = select(
        SpecialFeatureOptionMaster.option_id,
        SpecialFeatureOptionMaster.option_value,
        SpecialFeatureMaster.feature_id,
        SpecialFeatureMaster.feature_name,
        func.coalesce(subquery.c.total_rm_count, 0).label("total_rm_count")
    ).join(
        SpecialFeatureMaster, SpecialFeatureMaster.feature_id == SpecialFeatureOptionMaster.feature_id
    ).outerjoin(
        subquery, subquery.c.option_id == SpecialFeatureOptionMaster.option_id
    ).where(
        func.lower(func.trim(SpecialFeatureOptionMaster.option_value)) != 'not required'
    ).order_by(SpecialFeatureMaster.feature_name, SpecialFeatureOptionMaster.option_value)
    
    result = await db.execute(query)
    rows = result.all()
    
    response = []
    for r in rows:
        response.append({
            "option_id": str(r.option_id),
            "option_value": r.option_value,
            "feature_id": str(r.feature_id),
            "feature_name": r.feature_name,
            "version_count": 1 if r.total_rm_count else 0,
            "total_rm_count": r.total_rm_count
        })
    return response