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

from app.models.yard_models import VehicleModelMaster
from app.models.dealer_models import (
    BodyTypeMaster, CustomerSegmentMaster, RtoMaster,
    SpecialFeatureMaster, SpecialFeatureOptionMaster,
    ModelBodyTypeMap, ModelSegmentMap, SeatingLayoutMaster, FeatureSegmentMap,
    SeatModelConfiguration
)
from app.models.bom_models import ProductMaster


async def _apply_update(obj, payload):
    for field, value in payload.model_dump(exclude_unset=True).items():
        setattr(obj, field, value)


# ── Body types ──

async def list_body_types(db: AsyncSession):
    return (await db.execute(select(BodyTypeMaster).order_by(BodyTypeMaster.name))).scalars().all()


async def create_body_type(db: AsyncSession, payload) -> BodyTypeMaster:
    row = BodyTypeMaster(body_type_id=uuid.uuid4(), **payload.model_dump())
    db.add(row)
    await db.commit()
    await db.refresh(row)
    return row


async def update_body_type(db: AsyncSession, body_type_id: uuid.UUID, payload) -> BodyTypeMaster:
    row = await db.get(BodyTypeMaster, body_type_id)
    if not row:
        raise HTTPException(status_code=404, detail="Body type not found")
    await _apply_update(row, payload)
    await db.commit()
    await db.refresh(row)
    return row


async def delete_body_type(db: AsyncSession, body_type_id: uuid.UUID):
    row = await db.get(BodyTypeMaster, body_type_id)
    if not row:
        raise HTTPException(status_code=404, detail="Body type not found")
    try:
        await db.delete(row)
        await db.commit()
    except Exception:
        await db.rollback()
        raise HTTPException(status_code=400, detail="Cannot delete this body type because it is in use.")

# ── Customer segments ──

async def list_segments(db: AsyncSession):
    return (await db.execute(select(CustomerSegmentMaster).order_by(CustomerSegmentMaster.name))).scalars().all()


async def create_segment(db: AsyncSession, payload) -> CustomerSegmentMaster:
    row = CustomerSegmentMaster(segment_id=uuid.uuid4(), **payload.model_dump())
    db.add(row)
    await db.commit()
    await db.refresh(row)
    return row


async def update_segment(db: AsyncSession, segment_id: uuid.UUID, payload) -> CustomerSegmentMaster:
    row = await db.get(CustomerSegmentMaster, segment_id)
    if not row:
        raise HTTPException(status_code=404, detail="Segment not found")
    await _apply_update(row, payload)
    await db.commit()
    await db.refresh(row)
    return row


async def delete_segment(db: AsyncSession, segment_id: uuid.UUID):
    row = await db.get(CustomerSegmentMaster, segment_id)
    if not row:
        raise HTTPException(status_code=404, detail="Segment not found")
    try:
        await db.delete(row)
        await db.commit()
    except Exception:
        await db.rollback()
        raise HTTPException(status_code=400, detail="Cannot delete this segment because it is in use.")

# ── RTOs ──

async def list_rtos(db: AsyncSession):
    return (await db.execute(select(RtoMaster).order_by(RtoMaster.name))).scalars().all()


async def create_rto(db: AsyncSession, payload) -> RtoMaster:
    row = RtoMaster(rto_id=uuid.uuid4(), **payload.model_dump())
    db.add(row)
    await db.commit()
    await db.refresh(row)
    return row


async def update_rto(db: AsyncSession, rto_id: uuid.UUID, payload) -> RtoMaster:
    row = await db.get(RtoMaster, rto_id)
    if not row:
        raise HTTPException(status_code=404, detail="RTO not found")
    await _apply_update(row, payload)
    await db.commit()
    await db.refresh(row)
    return row


async def delete_rto(db: AsyncSession, rto_id: uuid.UUID):
    row = await db.get(RtoMaster, rto_id)
    if not row:
        raise HTTPException(status_code=404, detail="RTO not found")
    try:
        await db.delete(row)
        await db.commit()
    except Exception:
        await db.rollback()
        raise HTTPException(status_code=400, detail="Cannot delete this RTO because it is in use.")

# ── Vehicle models ──

async def list_models(db: AsyncSession):
    return (await db.execute(select(VehicleModelMaster).order_by(VehicleModelMaster.model_number))).scalars().all()


async def create_model(db: AsyncSession, payload) -> VehicleModelMaster:
    existing = (await db.execute(
        select(VehicleModelMaster).where(VehicleModelMaster.model_number == payload.model_number)
    )).scalar_one_or_none()
    if existing:
        raise HTTPException(status_code=400, detail="A model with this number already exists")
    row = VehicleModelMaster(model_id=uuid.uuid4(), **payload.model_dump())
    db.add(row)
    await db.commit()
    await db.refresh(row)
    return row


async def update_model(db: AsyncSession, model_id: uuid.UUID, payload) -> VehicleModelMaster:
    row = await db.get(VehicleModelMaster, model_id)
    if not row:
        raise HTTPException(status_code=404, detail="Model not found")
    await _apply_update(row, payload)
    await db.commit()
    await db.refresh(row)
    return row


async def delete_model(db: AsyncSession, model_id: uuid.UUID):
    row = await db.get(VehicleModelMaster, model_id)
    if not row:
        raise HTTPException(status_code=404, detail="Model not found")
    try:
        await db.delete(row)
        await db.commit()
    except Exception:
        await db.rollback()
        raise HTTPException(status_code=400, detail="Cannot delete this model because it is in use.")

# ── Special features + options ──

async def list_features(db: AsyncSession):
    features = (await db.execute(
        select(SpecialFeatureMaster).order_by(SpecialFeatureMaster.sequence_no)
    )).scalars().all()
    options = (await db.execute(
        select(SpecialFeatureOptionMaster).order_by(SpecialFeatureOptionMaster.sequence_no)
    )).scalars().all()
    by_feature = {}
    for o in options:
        by_feature.setdefault(o.feature_id, []).append(o)
    for f in features:
        f.options = by_feature.get(f.feature_id, [])
    return features


async def create_feature(db: AsyncSession, payload) -> SpecialFeatureMaster:
    row = SpecialFeatureMaster(feature_id=uuid.uuid4(), **payload.model_dump())
    db.add(row)
    await db.commit()
    await db.refresh(row)
    row.options = []
    return row


async def update_feature(db: AsyncSession, feature_id: uuid.UUID, payload) -> SpecialFeatureMaster:
    row = await db.get(SpecialFeatureMaster, feature_id)
    if not row:
        raise HTTPException(status_code=404, detail="Feature not found")
    await _apply_update(row, payload)
    await db.commit()
    await db.refresh(row)
    row.options = (await db.execute(
        select(SpecialFeatureOptionMaster).where(SpecialFeatureOptionMaster.feature_id == feature_id)
        .order_by(SpecialFeatureOptionMaster.sequence_no)
    )).scalars().all()
    return row


async def delete_feature(db: AsyncSession, feature_id: uuid.UUID):
    row = await db.get(SpecialFeatureMaster, feature_id)
    if not row:
        raise HTTPException(status_code=404, detail="Feature not found")
    try:
        from sqlalchemy import text
        await db.execute(text('DELETE FROM dealer_order_vehicle_feature WHERE feature_id = :fid'), {'fid': feature_id})
        await db.execute(text('DELETE FROM dealer_vehicle_template_feature WHERE feature_id = :fid'), {'fid': feature_id})
        await db.execute(text('DELETE FROM feature_segment_map WHERE feature_id = :fid'), {'fid': feature_id})
        await db.execute(text('DELETE FROM special_feature_bom WHERE option_id IN (SELECT option_id FROM special_feature_option_master WHERE feature_id = :fid)'), {'fid': feature_id})
        await db.execute(text('DELETE FROM special_feature_option_master WHERE feature_id = :fid'), {'fid': feature_id})
        await db.delete(row)
        await db.commit()
    except Exception as e:
        await db.rollback()
        raise HTTPException(status_code=400, detail=f"Failed to delete feature: {str(e)}")


async def create_feature_option(db: AsyncSession, feature_id: uuid.UUID, payload) -> SpecialFeatureOptionMaster:
    feature = await db.get(SpecialFeatureMaster, feature_id)
    if not feature:
        raise HTTPException(status_code=404, detail="Feature not found")
    row = SpecialFeatureOptionMaster(option_id=uuid.uuid4(), feature_id=feature_id, **payload.model_dump())
    db.add(row)
    await db.commit()
    await db.refresh(row)
    return row


async def update_feature_option(db: AsyncSession, feature_id: uuid.UUID, option_id: uuid.UUID, payload) -> SpecialFeatureOptionMaster:
    row = await db.get(SpecialFeatureOptionMaster, option_id)
    if not row or row.feature_id != feature_id:
        raise HTTPException(status_code=404, detail="Option not found")
    await _apply_update(row, payload)
    await db.commit()
    await db.refresh(row)
    return row

async def delete_feature_option(db: AsyncSession, feature_id: uuid.UUID, option_id: uuid.UUID):
    row = await db.get(SpecialFeatureOptionMaster, option_id)
    if row and row.feature_id == feature_id:
        try:
            from sqlalchemy import text
            await db.execute(text('DELETE FROM dealer_order_vehicle_feature WHERE feature_option_id = :oid'), {'oid': option_id})
            await db.execute(text('DELETE FROM dealer_vehicle_template_feature WHERE feature_option_id = :oid'), {'oid': option_id})
            await db.delete(row)
            await db.commit()
        except Exception as e:
            await db.rollback()
            raise HTTPException(status_code=400, detail=f"Failed to delete option: {str(e)}")


# ── Model <-> body-type / segment relationship maps ──

async def list_model_body_types(db: AsyncSession):
    return (await db.execute(select(ModelBodyTypeMap))).scalars().all()


async def ensure_product_combinations_for_model(db: AsyncSession, model_id: uuid.UUID):
    model = await db.get(VehicleModelMaster, model_id)
    if not model: return
    
    body_types = (await db.execute(
        select(BodyTypeMaster).join(ModelBodyTypeMap, ModelBodyTypeMap.body_type_id == BodyTypeMaster.body_type_id)
        .where(ModelBodyTypeMap.model_id == model_id)
    )).scalars().all()
    
    segments = (await db.execute(
        select(CustomerSegmentMaster).join(ModelSegmentMap, ModelSegmentMap.segment_id == CustomerSegmentMaster.segment_id)
        .where(ModelSegmentMap.model_id == model_id)
    )).scalars().all()
    
    def sanitize(val):
        return str(val).strip().replace(" ", "_").upper() if val else ""
        
    for bt in body_types:
        for seg in segments:
            p_code = f"{sanitize(bt.name)}___{sanitize(seg.name)}___{sanitize(model.model_number)}"
            p_name = f"{bt.name} - {seg.name} - {model.model_number}"
            
            existing = (await db.execute(select(ProductMaster).where(ProductMaster.product_code == p_code))).scalar_one_or_none()
            if not existing:
                new_prod = ProductMaster(
                    product_id=uuid.uuid4(),
                    name=p_name,
                    product_code=p_code,
                    unit_of_measurement="NOS"
                )
                db.add(new_prod)
    await db.commit()


async def add_model_body_type(db: AsyncSession, payload) -> ModelBodyTypeMap:
    existing = (await db.execute(
        select(ModelBodyTypeMap).where(
            ModelBodyTypeMap.model_id == payload.model_id, ModelBodyTypeMap.body_type_id == payload.body_type_id,
        )
    )).scalar_one_or_none()
    if existing:
        return existing
    row = ModelBodyTypeMap(id=uuid.uuid4(), model_id=payload.model_id, body_type_id=payload.body_type_id)
    db.add(row)
    await db.commit()
    await db.refresh(row)
    await ensure_product_combinations_for_model(db, payload.model_id)
    return row


async def remove_model_body_type(db: AsyncSession, link_id: uuid.UUID):
    row = await db.get(ModelBodyTypeMap, link_id)
    if row:
        await db.delete(row)
        await db.commit()


async def list_model_segments(db: AsyncSession):
    return (await db.execute(select(ModelSegmentMap))).scalars().all()


async def add_model_segment(db: AsyncSession, payload) -> ModelSegmentMap:
    existing = (await db.execute(
        select(ModelSegmentMap).where(
            ModelSegmentMap.model_id == payload.model_id, ModelSegmentMap.segment_id == payload.segment_id,
        )
    )).scalar_one_or_none()
    if existing:
        return existing
    row = ModelSegmentMap(id=uuid.uuid4(), model_id=payload.model_id, segment_id=payload.segment_id)
    db.add(row)
    await db.commit()
    await db.refresh(row)
    await ensure_product_combinations_for_model(db, payload.model_id)
    return row


async def remove_model_segment(db: AsyncSession, link_id: uuid.UUID):
    row = await db.get(ModelSegmentMap, link_id)
    if row:
        await db.delete(row)
        await db.commit()

async def list_feature_segments(db: AsyncSession):
    return (await db.execute(select(FeatureSegmentMap))).scalars().all()

async def add_feature_segment(db: AsyncSession, payload) -> FeatureSegmentMap:
    existing = (await db.execute(
        select(FeatureSegmentMap).where(
            FeatureSegmentMap.feature_id == payload.feature_id, FeatureSegmentMap.segment_id == payload.segment_id,
        )
    )).scalar_one_or_none()
    if existing:
        return existing
    row = FeatureSegmentMap(id=uuid.uuid4(), feature_id=payload.feature_id, segment_id=payload.segment_id)
    db.add(row)
    await db.commit()
    await db.refresh(row)
    return row

async def remove_feature_segment(db: AsyncSession, link_id: uuid.UUID):
    row = await db.get(FeatureSegmentMap, link_id)
    if row:
        await db.delete(row)
        await db.commit()

# ── Seating Layouts ──

async def list_seating_layouts(db: AsyncSession):
    return (await db.execute(select(SeatingLayoutMaster).order_by(SeatingLayoutMaster.layout_no))).scalars().all()

async def search_seating_layouts(db: AsyncSession, chassis_model: str, body_type: str, segment: str, door_type: str, seat_type: str):
    dt_lower = door_type.lower() if door_type else ""
    if "single door" in dt_lower or "first door" in dt_lower or dt_lower == "jk door":
        door_type = "Single door"
    elif "double door" in dt_lower or "both jk door" in dt_lower or "both door" in dt_lower:
        door_type = "Double door"

    st_lower = seat_type.lower() if seat_type else ""
    if "regular" in st_lower or "low back" in st_lower:
        seat_type = "Low Back"
    elif "push back" in st_lower or st_lower == "pb":
        seat_type = "Push Back"
    elif "hhr" in st_lower:
        seat_type = "HHR"

    cm = chassis_model.replace(" ", "") if chassis_model else ""
    q = select(SeatingLayoutMaster).where(
        func.replace(SeatingLayoutMaster.chassis_model, ' ', '').ilike(f"%{cm}%"),
        SeatingLayoutMaster.body_type.ilike(body_type),
        SeatingLayoutMaster.segment.ilike(segment),
        SeatingLayoutMaster.door_type.ilike(door_type),
        SeatingLayoutMaster.seat_type.ilike(seat_type)
    )
    return (await db.execute(q)).scalars().all()

async def create_seating_layout(db: AsyncSession, payload) -> SeatingLayoutMaster:
    existing = (await db.execute(
        select(SeatingLayoutMaster).where(SeatingLayoutMaster.layout_no == payload.layout_no)
    )).scalar_one_or_none()
    if existing:
        raise HTTPException(status_code=400, detail="A layout with this number already exists")
    row = SeatingLayoutMaster(layout_id=uuid.uuid4(), **payload.model_dump(exclude_unset=True))
    db.add(row)
    await db.commit()
    await db.refresh(row)
    return row

async def update_seating_layout(db: AsyncSession, layout_id: uuid.UUID, payload) -> SeatingLayoutMaster:
    row = await db.get(SeatingLayoutMaster, layout_id)
    if not row:
        raise HTTPException(status_code=404, detail="Seating layout not found")
    await _apply_update(row, payload)
    await db.commit()
    await db.refresh(row)
    return row

async def delete_seating_layout(db: AsyncSession, layout_id: uuid.UUID):
    row = await db.get(SeatingLayoutMaster, layout_id)
    if not row:
        raise HTTPException(status_code=404, detail="Seating layout not found")
    await db.delete(row)
    await db.commit()

# ── Seat Model Configurations ──

async def list_seat_model_configs(db: AsyncSession):
    return (await db.execute(select(SeatModelConfiguration).order_by(SeatModelConfiguration.created_at))).scalars().all()

async def create_seat_model_config(db: AsyncSession, payload) -> SeatModelConfiguration:
    row = SeatModelConfiguration(id=uuid.uuid4(), **payload.model_dump(exclude_unset=True))
    db.add(row)
    await db.commit()
    await db.refresh(row)
    return row

async def update_seat_model_config(db: AsyncSession, config_id: uuid.UUID, payload) -> SeatModelConfiguration:
    row = await db.get(SeatModelConfiguration, config_id)
    if not row:
        raise HTTPException(status_code=404, detail="Seat model configuration not found")
    await _apply_update(row, payload)
    await db.commit()
    await db.refresh(row)
    return row

async def delete_seat_model_config(db: AsyncSession, config_id: uuid.UUID):
    row = await db.get(SeatModelConfiguration, config_id)
    if not row:
        raise HTTPException(status_code=404, detail="Seat model configuration not found")
    await db.delete(row)
    await db.commit()