from uuid import UUID
from typing import Optional

from fastapi import HTTPException
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.rm_models import StationMaster
from .schemas import StationCreate, StationUpdate


async def list_stations_service(
    db: AsyncSession,
    skip: int = 0,
    limit: int = 100,
    is_active: Optional[str] = None
):
    stmt = select(StationMaster)

    if is_active is not None and is_active.lower() not in ("null", "none", ""):
        active_val = is_active.lower() in ("true", "1", "yes")
        stmt = stmt.where(StationMaster.is_active == active_val)

    stmt = stmt.offset(skip).limit(limit)

    result = await db.execute(stmt)

    return list(result.scalars().all())


async def create_station_service(
    db: AsyncSession,
    data: StationCreate
):
    stmt = select(StationMaster).where(
        StationMaster.station_code == data.station_code
    )

    result = await db.execute(stmt)
    existing_station = result.scalar_one_or_none()

    if existing_station:
        raise HTTPException(
            status_code=400,
            detail="Station code already exists"
        )

    station = StationMaster(
        **data.model_dump(exclude_none=True)
    )

    db.add(station)

    await db.commit()
    await db.refresh(station)

    return station


async def get_station_service(
    db: AsyncSession,
    station_id: UUID
):
    station = await db.get(StationMaster, station_id)

    if not station:
        raise HTTPException(
            status_code=404,
            detail="Station not found"
        )

    return station


async def update_station_service(
    db: AsyncSession,
    station_id: UUID,
    data: StationUpdate
):
    station = await db.get(StationMaster, station_id)

    if not station:
        raise HTTPException(
            status_code=404,
            detail="Station not found"
        )

    update_data = data.model_dump(exclude_unset=True)
    if "standard_cycle_time_min" in update_data:
        val = update_data["standard_cycle_time_min"]
        update_data["standard_cycle_time_min"] = val if (val is not None and val > 0) else None

    if "station_code" in update_data:
        stmt = select(StationMaster).where(
            StationMaster.station_code == update_data["station_code"],
            StationMaster.station_id != station_id
        )

        result = await db.execute(stmt)

        if result.scalar_one_or_none():
            raise HTTPException(
                status_code=400,
                detail="Station code already exists"
            )

    for key, value in update_data.items():
        setattr(station, key, value)

    await db.commit()
    await db.refresh(station)

    return station


async def delete_station_service(
    db: AsyncSession,
    station_id: UUID
):
    station = await db.get(StationMaster, station_id)

    if not station:
        raise HTTPException(
            status_code=404,
            detail="Station not found"
        )

    station.is_active = False

    await db.commit()


async def permanent_delete_station_service(
    db: AsyncSession,
    station_id: UUID
):
    from app.models.rm_models import RmMaster
    from app.models.location_models import StationQaChecklistItemMaster, ChassisStationQaResult
    from app.models.qa_models import (
        QaStationTemplateMap,
        QaChecksheetTemplate,
        QaChecksheetInstance,
        QaCheckpointResponse,
        QaChecksheetSignoff,
        QaShortageLog,
    )
    from app.models.production_models import ChassisStationLog, ProductionHold
    from app.models.yard_models import ChassisMaster
    from sqlalchemy import delete, update, select

    station = await db.get(StationMaster, station_id)

    if not station:
        raise HTTPException(
            status_code=404,
            detail="Station not found"
        )

    # 1. Clear station references on nullable foreign key tables
    await db.execute(
        update(QaChecksheetTemplate)
        .where(QaChecksheetTemplate.station_id == station_id)
        .values(station_id=None)
    )
    await db.execute(
        update(RmMaster)
        .where(RmMaster.station_id == station_id)
        .values(station_id=None)
    )
    await db.execute(
        update(ChassisMaster)
        .where(ChassisMaster.current_station_id == station_id)
        .values(current_station_id=None)
    )
    await db.execute(
        update(ProductionHold)
        .where(ProductionHold.station_id == station_id)
        .values(station_id=None)
    )

    # 2. Delete configuration mappings
    await db.execute(
        delete(QaStationTemplateMap).where(QaStationTemplateMap.station_id == station_id)
    )
    await db.execute(
        delete(StationQaChecklistItemMaster).where(StationQaChecklistItemMaster.station_id == station_id)
    )

    # 3. Clean up related operational logs
    await db.execute(
        delete(ChassisStationQaResult).where(ChassisStationQaResult.station_id == station_id)
    )
    await db.execute(
        delete(QaShortageLog).where(QaShortageLog.station_id == station_id)
    )
    await db.execute(
        delete(ChassisStationLog).where(ChassisStationLog.station_id == station_id)
    )

    # Clean up QA checksheet instances for this station
    instance_ids = (await db.execute(
        select(QaChecksheetInstance.instance_id).where(QaChecksheetInstance.station_id == station_id)
    )).scalars().all()
    if instance_ids:
        await db.execute(delete(QaChecksheetSignoff).where(QaChecksheetSignoff.instance_id.in_(instance_ids)))
        await db.execute(delete(QaCheckpointResponse).where(QaCheckpointResponse.instance_id.in_(instance_ids)))
        await db.execute(delete(QaChecksheetInstance).where(QaChecksheetInstance.instance_id.in_(instance_ids)))

    await db.delete(station)
    await db.commit()