"""Equipment stock engine: issue, collect, adjust, write off, and live balances.

**Source of truth.** `equipment_items` rows *are* the current stock. The
`equipment_stock_movements` ledger is append-only history for reports and charts —
nothing reads it back to compute a balance, so the two can never disagree.

**Two inventory shapes.** Serialized types (ONU, router) have one row per unit that
changes holder as it moves. Bulk types (cable, connectors) have one row per
(location, condition) whose quantity rises and falls; units in the field are tracked
by their distribution's outstanding quantity, not by a shadow inventory row.

**Item status vocabulary.**
`in_stock` at a location and issuable · `issued` out with a technician or customer ·
`returned` back at a location but faulty, awaiting inspection (quarantine) ·
`written_off` gone for good.
"""
from datetime import date, timedelta

from fastapi import HTTPException, status as http_status
from sqlalchemy import Select, and_, case, func, or_, select
from sqlalchemy.orm import Session

from app.core.time import today_local, utcnow
from app.models.enums import (
    DistributionStatus,
    EquipmentCondition,
    EquipmentItemStatus,
    HolderType,
    StockMovementType,
)
from app.models.equipment import (
    EquipmentCollection,
    EquipmentDistribution,
    EquipmentItem,
    EquipmentStockMovement,
)
from app.models.reference import EquipmentType, Location
from app.models.user import User
from app.schemas.equipment import (
    CollectionCreate,
    DistributionCreate,
)

#: Distributions with units still in the field.
OPEN_DISTRIBUTION_STATUSES = (DistributionStatus.ISSUED, DistributionStatus.PARTIALLY_RETURNED)

#: Item statuses whose quantity sits physically at `location_id`.
AT_LOCATION_STATUSES = (EquipmentItemStatus.IN_STOCK, EquipmentItemStatus.RETURNED)


def _bad_request(detail: str) -> HTTPException:
    return HTTPException(status_code=http_status.HTTP_400_BAD_REQUEST, detail=detail)


# --- helpers ---


def outstanding(distribution: EquipmentDistribution) -> int:
    return distribution.outstanding_quantity


def available_quantity(item: EquipmentItem) -> int:
    """Units of this row that can be issued right now."""
    if (
        item.status != EquipmentItemStatus.IN_STOCK
        or item.holder_type != HolderType.WAREHOUSE
        or item.is_deleted
    ):
        return 0
    return item.quantity


def record_movement(
    db: Session,
    *,
    equipment_type_id: int,
    movement_type: StockMovementType,
    quantity_delta: int,
    location_id: int | None,
    item_id: int | None = None,
    distribution_id: int | None = None,
    collection_id: int | None = None,
    user_id: int | None = None,
    notes: str | None = None,
    moved_at=None,
) -> EquipmentStockMovement:
    """Append one ledger row. `quantity_delta` is the signed change on hand at `location_id`."""
    movement = EquipmentStockMovement(
        equipment_type_id=equipment_type_id,
        equipment_item_id=item_id,
        location_id=location_id,
        movement_type=movement_type,
        quantity_delta=quantity_delta,
        distribution_id=distribution_id,
        collection_id=collection_id,
        moved_at=moved_at or utcnow(),
        created_by_id=user_id,
        notes=notes,
    )
    db.add(movement)
    return movement


def lock_item(db: Session, item_id: int) -> EquipmentItem:
    """Re-read an item FOR UPDATE so two concurrent issues cannot oversell one row.

    The lock is taken on a bare id select: EquipmentItem eager-loads its holder
    relationships, and Postgres rejects FOR UPDATE on the nullable side of those
    outer joins. Locking the row first, then loading it, gets both.
    """
    locked_id = db.scalar(
        select(EquipmentItem.id)
        .where(EquipmentItem.id == item_id, EquipmentItem.is_deleted.is_(False))
        .with_for_update()
    )
    if locked_id is None:
        raise HTTPException(
            status_code=http_status.HTTP_404_NOT_FOUND, detail="Equipment item not found"
        )
    return db.scalar(select(EquipmentItem).where(EquipmentItem.id == locked_id))


def _default_location(db: Session) -> Location | None:
    """Fallback stock location — the flagged default, else the lowest-numbered one."""
    return db.scalar(
        select(Location)
        .where(Location.is_deleted.is_(False))
        .order_by(Location.is_default.desc(), Location.id)
    )


# --- inventory intake ---

def validate_new_item(
    db: Session, *, equipment_type_id: int, location_id: int, serial_number: str | None, quantity: int
) -> EquipmentType:
    """Enforce the serialized/bulk contract before a row is created."""
    equipment_type = db.scalar(
        select(EquipmentType).where(
            EquipmentType.id == equipment_type_id, EquipmentType.is_deleted.is_(False)
        )
    )
    if equipment_type is None:
        raise HTTPException(status_code=422, detail="Unknown equipment type")
    if not db.scalar(
        select(Location.id).where(Location.id == location_id, Location.is_deleted.is_(False))
    ):
        raise HTTPException(status_code=422, detail="Unknown location")

    if equipment_type.is_serialized:
        if not serial_number:
            raise _bad_request(
                f"'{equipment_type.name}' is tracked by serial number — a serial is required"
            )
        if quantity != 1:
            raise _bad_request(
                f"'{equipment_type.name}' is serialized — each row is one unit, so quantity must be 1"
            )
    elif serial_number:
        raise _bad_request(
            f"'{equipment_type.name}' is a bulk item ({equipment_type.unit}) and cannot carry a serial number"
        )
    return equipment_type


def _find_bulk_row(
    db: Session,
    *,
    equipment_type_id: int,
    location_id: int,
    condition: EquipmentCondition,
    item_status: EquipmentItemStatus,
) -> EquipmentItem | None:
    """The single row that accumulates bulk stock of one condition at one location."""
    row_id = db.scalar(
        select(EquipmentItem.id)
        .where(
            EquipmentItem.equipment_type_id == equipment_type_id,
            EquipmentItem.location_id == location_id,
            EquipmentItem.condition == condition,
            EquipmentItem.status == item_status,
            EquipmentItem.holder_type == HolderType.WAREHOUSE,
            EquipmentItem.serial_number.is_(None),
            EquipmentItem.is_deleted.is_(False),
        )
        .order_by(EquipmentItem.id)
        .with_for_update()
    )
    if row_id is None:
        return None
    return db.scalar(select(EquipmentItem).where(EquipmentItem.id == row_id))


# --- issue ---


def issue(
    db: Session, payload: DistributionCreate, actor: User
) -> EquipmentDistribution:
    """Hand units to a technician or customer, deducting them from location stock."""
    item = lock_item(db, payload.equipment_item_id)
    equipment_type = item.equipment_type

    if item.status == EquipmentItemStatus.WRITTEN_OFF:
        raise _bad_request("This item has been written off and cannot be issued")
    if item.status == EquipmentItemStatus.RETURNED:
        raise _bad_request(
            "This stock is quarantined pending inspection — mark it usable before issuing it"
        )
    if item.status == EquipmentItemStatus.ISSUED or item.holder_type != HolderType.WAREHOUSE:
        raise _bad_request("This item is already out with someone")
    if item.condition == EquipmentCondition.FAULTY:
        raise _bad_request("Faulty equipment cannot be issued")

    on_hand = available_quantity(item)
    if payload.quantity > on_hand:
        raise _bad_request(
            f"Only {on_hand} {equipment_type.unit} of '{equipment_type.name}' available at "
            f"{item.location.name if item.location else 'this location'}"
        )
    if equipment_type.is_serialized and payload.quantity != 1:
        raise _bad_request(f"'{equipment_type.name}' is serialized — issue one unit at a time")

    _validate_issue_target(db, payload)

    from_location_id = item.location_id
    expected_return = payload.expected_return_date
    if (
        expected_return is None
        and payload.issued_to_type == HolderType.TECHNICIAN
        and equipment_type.default_return_days is not None
    ):
        # Technician hand-outs are loans; customer installs are permanent.
        expected_return = today_local() + timedelta(days=equipment_type.default_return_days)

    issued_at = payload.issued_at or utcnow()

    distribution = EquipmentDistribution(
        equipment_item_id=item.id,
        quantity=payload.quantity,
        returned_quantity=0,
        issued_to_type=payload.issued_to_type,
        issued_to_user_id=payload.issued_to_user_id,
        issued_to_customer_id=payload.issued_to_customer_id,
        connection_application_id=payload.connection_application_id,
        connection_id=payload.connection_id,
        from_location_id=from_location_id,
        issued_by_id=actor.id,
        issued_at=issued_at,
        expected_return_date=expected_return,
        status=DistributionStatus.ISSUED,
        notes=payload.notes,
    )
    db.add(distribution)

    if equipment_type.is_serialized:
        # The physical unit itself moves — the row follows it to its new holder.
        item.status = EquipmentItemStatus.ISSUED
        item.holder_type = payload.issued_to_type
        item.location_id = None
        item.holder_user_id = payload.issued_to_user_id
        item.holder_customer_id = payload.issued_to_customer_id
    else:
        # Bulk stock stays anchored at the location; only the count drops.
        item.quantity -= payload.quantity

    db.flush()
    record_movement(
        db,
        equipment_type_id=equipment_type.id,
        movement_type=StockMovementType.ISSUE,
        quantity_delta=-payload.quantity,
        location_id=from_location_id,
        item_id=item.id,
        distribution_id=distribution.id,
        user_id=actor.id,
        moved_at=issued_at,
        notes=payload.notes,
    )
    return distribution


def _validate_issue_target(db: Session, payload: DistributionCreate) -> None:
    """The schema guarantees a target field is present; check it actually exists."""
    from app.models.application import ConnectionApplication
    from app.models.customer import Connection, Customer
    from app.models.enums import UserRole

    if payload.issued_to_user_id is not None and not db.scalar(
        select(User.id).where(
            User.id == payload.issued_to_user_id,
            User.is_deleted.is_(False),
            User.is_active.is_(True),
            User.role.in_((UserRole.FIELD_STAFF, UserRole.MANAGER, UserRole.ADMIN, UserRole.SYSTEM_DEVELOPER)),
        )
    ):
        raise HTTPException(status_code=422, detail="Unknown or inactive staff member")

    if payload.issued_to_customer_id is not None and not db.scalar(
        select(Customer.id).where(
            Customer.id == payload.issued_to_customer_id, Customer.is_deleted.is_(False)
        )
    ):
        raise HTTPException(status_code=422, detail="Unknown customer")

    if payload.connection_id is not None and not db.scalar(
        select(Connection.id).where(
            Connection.id == payload.connection_id, Connection.is_deleted.is_(False)
        )
    ):
        raise HTTPException(status_code=422, detail="Unknown connection")

    if payload.connection_application_id is not None and not db.scalar(
        select(ConnectionApplication.id).where(
            ConnectionApplication.id == payload.connection_application_id,
            ConnectionApplication.is_deleted.is_(False),
        )
    ):
        raise HTTPException(status_code=422, detail="Unknown application")


# --- collect (return) ---


def collect(
    db: Session,
    distribution: EquipmentDistribution,
    payload: CollectionCreate,
    actor: User,
) -> EquipmentCollection:
    """Take units back. Partial returns are normal — a technician returns 3 of 10 drums."""
    if distribution.status not in OPEN_DISTRIBUTION_STATUSES:
        raise _bad_request(
            f"This distribution is already '{distribution.status.value}' — nothing is outstanding"
        )

    remaining = outstanding(distribution)
    if payload.quantity > remaining:
        raise _bad_request(f"Only {remaining} unit(s) are still outstanding on this issue")

    location = db.scalar(
        select(Location).where(
            Location.id == payload.returned_to_location_id, Location.is_deleted.is_(False)
        )
    )
    if location is None:
        raise HTTPException(status_code=422, detail="Unknown location")

    item = lock_item(db, distribution.equipment_item_id)
    equipment_type = item.equipment_type
    collected_at = payload.collected_at or utcnow()

    # Faulty returns go to quarantine rather than back into issuable stock.
    target_status = (
        EquipmentItemStatus.RETURNED
        if payload.condition_on_return == EquipmentCondition.FAULTY
        else EquipmentItemStatus.IN_STOCK
    )

    if equipment_type.is_serialized:
        item.status = target_status
        item.condition = payload.condition_on_return
        item.holder_type = HolderType.WAREHOUSE
        item.location_id = location.id
        item.holder_user_id = None
        item.holder_customer_id = None
        credited_item = item
    else:
        # Returned bulk stock joins the row for its condition at that location, so the
        # inventory never fragments into one row per return.
        credited_item = _find_bulk_row(
            db,
            equipment_type_id=equipment_type.id,
            location_id=location.id,
            condition=payload.condition_on_return,
            item_status=target_status,
        )
        if credited_item is None:
            credited_item = EquipmentItem(
                equipment_type_id=equipment_type.id,
                model=item.model,
                serial_number=None,
                condition=payload.condition_on_return,
                quantity=0,
                holder_type=HolderType.WAREHOUSE,
                location_id=location.id,
                status=target_status,
                unit_cost=item.unit_cost,
                notes=f"Returned stock ({payload.condition_on_return.value})",
            )
            db.add(credited_item)
            db.flush()
        credited_item.quantity += payload.quantity

    collection = EquipmentCollection(
        distribution_id=distribution.id,
        equipment_item_id=credited_item.id,
        quantity=payload.quantity,
        condition_on_return=payload.condition_on_return,
        collected_at=collected_at,
        collected_by_id=actor.id,
        collected_from_user_id=distribution.issued_to_user_id,
        collected_from_customer_id=distribution.issued_to_customer_id,
        returned_to_location_id=location.id,
        notes=payload.notes,
    )
    db.add(collection)

    distribution.returned_quantity += payload.quantity
    distribution.status = (
        DistributionStatus.RETURNED
        if outstanding(distribution) == 0
        else DistributionStatus.PARTIALLY_RETURNED
    )

    db.flush()
    record_movement(
        db,
        equipment_type_id=equipment_type.id,
        movement_type=StockMovementType.RETURN,
        quantity_delta=payload.quantity,
        location_id=location.id,
        item_id=credited_item.id,
        distribution_id=distribution.id,
        collection_id=collection.id,
        user_id=actor.id,
        moved_at=collected_at,
        notes=payload.notes,
    )
    return collection


# --- adjust / write off / release from quarantine ---


def adjust(db: Session, item: EquipmentItem, *, quantity_delta: int, reason: str, actor: User) -> None:
    """Correct a count after a physical stock take."""
    if item.status not in AT_LOCATION_STATUSES or item.holder_type != HolderType.WAREHOUSE:
        raise _bad_request("Only stock held at a location can be adjusted")
    if item.equipment_type.is_serialized:
        raise _bad_request(
            "A serialized unit cannot be quantity-adjusted — write it off or add a new unit instead"
        )
    if item.quantity + quantity_delta < 0:
        raise _bad_request(
            f"Adjustment would take stock below zero (on hand {item.quantity}, delta {quantity_delta})"
        )

    item.quantity += quantity_delta
    record_movement(
        db,
        equipment_type_id=item.equipment_type_id,
        movement_type=StockMovementType.ADJUSTMENT,
        quantity_delta=quantity_delta,
        location_id=item.location_id,
        item_id=item.id,
        user_id=actor.id,
        notes=reason,
    )


def write_off(db: Session, item: EquipmentItem, *, quantity: int, reason: str, actor: User) -> None:
    """Retire units — damaged beyond repair, or lost in the field.

    Units still out with a holder are written off where they are: the ledger row carries
    no location because they had already left stock when they were issued.
    """
    if item.status == EquipmentItemStatus.WRITTEN_OFF:
        raise _bad_request("This item is already written off")

    is_out = item.status == EquipmentItemStatus.ISSUED
    if is_out:
        open_distributions = [
            d
            for d in item.distributions
            if d.status in OPEN_DISTRIBUTION_STATUSES and not d.is_deleted
        ]
        total_out = sum(outstanding(d) for d in open_distributions)
        if quantity > total_out:
            raise _bad_request(f"Only {total_out} unit(s) are out with a holder")
        # Serialized units are all-or-nothing, so the whole issue closes.
        for distribution in open_distributions:
            distribution.status = DistributionStatus.WRITTEN_OFF
        item.status = EquipmentItemStatus.WRITTEN_OFF
        location_id = None
    else:
        if quantity > item.quantity:
            raise _bad_request(f"Only {item.quantity} unit(s) on hand")
        location_id = item.location_id
        item.quantity -= quantity
        if item.quantity == 0:
            item.status = EquipmentItemStatus.WRITTEN_OFF

    record_movement(
        db,
        equipment_type_id=item.equipment_type_id,
        movement_type=StockMovementType.WRITE_OFF,
        quantity_delta=-quantity,
        location_id=location_id,
        item_id=item.id,
        user_id=actor.id,
        notes=reason,
    )


def release_from_quarantine(
    db: Session, item: EquipmentItem, *, condition: EquipmentCondition, actor: User
) -> None:
    """Return inspected stock to issuable, e.g. a 'faulty' router that only needed a reset."""
    if item.status != EquipmentItemStatus.RETURNED:
        raise _bad_request("Only quarantined stock can be released back into inventory")
    if condition == EquipmentCondition.FAULTY:
        raise _bad_request("Release requires a usable condition — write the item off instead")

    item.condition = condition
    item.status = EquipmentItemStatus.IN_STOCK
    record_movement(
        db,
        equipment_type_id=item.equipment_type_id,
        movement_type=StockMovementType.ADJUSTMENT,
        quantity_delta=0,
        location_id=item.location_id,
        item_id=item.id,
        user_id=actor.id,
        notes=f"Released from quarantine as '{condition.value}'",
    )


# --- live balances ---


def _issued_out_subquery(*, issued_to_type: HolderType | None = None) -> Select:
    """Outstanding units per equipment type, from open distributions."""
    stmt = (
        select(
            EquipmentItem.equipment_type_id.label("type_id"),
            func.coalesce(
                func.sum(EquipmentDistribution.quantity - EquipmentDistribution.returned_quantity),
                0,
            ).label("qty"),
        )
        .join(EquipmentItem, EquipmentItem.id == EquipmentDistribution.equipment_item_id)
        .where(
            EquipmentDistribution.is_deleted.is_(False),
            EquipmentDistribution.status.in_(OPEN_DISTRIBUTION_STATUSES),
        )
        .group_by(EquipmentItem.equipment_type_id)
    )
    if issued_to_type is not None:
        stmt = stmt.where(EquipmentDistribution.issued_to_type == issued_to_type)
    return stmt.subquery()


def stock_by_type(
    db: Session, *, equipment_type_id: int | None = None, location_id: int | None = None
) -> list[dict]:
    """Live balance per equipment type, with a per-location breakdown."""
    types_stmt = select(EquipmentType).where(EquipmentType.is_deleted.is_(False))
    if equipment_type_id is not None:
        types_stmt = types_stmt.where(EquipmentType.id == equipment_type_id)
    types = db.scalars(types_stmt.order_by(EquipmentType.name)).all()

    # On-hand quantities, split by the status that makes them issuable or not.
    at_location_stmt = (
        select(
            EquipmentItem.equipment_type_id,
            EquipmentItem.location_id,
            func.coalesce(
                func.sum(
                    case(
                        (EquipmentItem.status == EquipmentItemStatus.IN_STOCK, EquipmentItem.quantity),
                        else_=0,
                    )
                ),
                0,
            ),
            func.coalesce(
                func.sum(
                    case(
                        (EquipmentItem.status == EquipmentItemStatus.RETURNED, EquipmentItem.quantity),
                        else_=0,
                    )
                ),
                0,
            ),
        )
        .where(
            EquipmentItem.is_deleted.is_(False),
            EquipmentItem.holder_type == HolderType.WAREHOUSE,
            EquipmentItem.status.in_(AT_LOCATION_STATUSES),
        )
        .group_by(EquipmentItem.equipment_type_id, EquipmentItem.location_id)
    )
    if location_id is not None:
        at_location_stmt = at_location_stmt.where(EquipmentItem.location_id == location_id)

    in_stock: dict[int, int] = {}
    quarantined: dict[int, int] = {}
    per_location: dict[int, dict[int, int]] = {}
    for type_id, loc_id, stock_qty, quarantine_qty in db.execute(at_location_stmt).all():
        in_stock[type_id] = in_stock.get(type_id, 0) + stock_qty
        quarantined[type_id] = quarantined.get(type_id, 0) + quarantine_qty
        if loc_id is not None and stock_qty:
            per_location.setdefault(type_id, {})[loc_id] = stock_qty

    written_off_stmt = (
        select(EquipmentItem.equipment_type_id, func.count())
        .where(
            EquipmentItem.is_deleted.is_(False),
            EquipmentItem.status == EquipmentItemStatus.WRITTEN_OFF,
        )
        .group_by(EquipmentItem.equipment_type_id)
    )
    written_off = dict(db.execute(written_off_stmt).all())

    def _issued(issued_to_type: HolderType | None = None) -> dict[int, int]:
        sub = _issued_out_subquery(issued_to_type=issued_to_type)
        return dict(db.execute(select(sub.c.type_id, sub.c.qty)).all())

    issued_total = _issued()
    with_techs = _issued(HolderType.TECHNICIAN)
    with_customers = _issued(HolderType.CUSTOMER)

    locations = {
        loc.id: loc
        for loc in db.scalars(select(Location).where(Location.is_deleted.is_(False))).all()
    }

    rows: list[dict] = []
    for equipment_type in types:
        stock = in_stock.get(equipment_type.id, 0)
        quarantine = quarantined.get(equipment_type.id, 0)
        # A location filter narrows on-hand stock; field units belong to no location.
        out = 0 if location_id is not None else issued_total.get(equipment_type.id, 0)
        rows.append(
            {
                "equipment_type": equipment_type,
                "in_stock": stock,
                "quarantined": quarantine,
                "issued_out": out,
                "with_technicians": 0 if location_id is not None else with_techs.get(equipment_type.id, 0),
                "with_customers": 0 if location_id is not None else with_customers.get(equipment_type.id, 0),
                "written_off": written_off.get(equipment_type.id, 0),
                "total": stock + quarantine + out,
                "reorder_level": equipment_type.reorder_level,
                "below_reorder": stock < equipment_type.reorder_level,
                "by_location": [
                    {"location": locations[loc_id], "quantity": qty}
                    for loc_id, qty in sorted(per_location.get(equipment_type.id, {}).items())
                    if loc_id in locations
                ],
            }
        )
    return rows


def summary(db: Session) -> dict:
    """Headline equipment numbers for the dashboard."""
    rows = stock_by_type(db)
    overdue = db.scalar(
        select(func.count())
        .select_from(EquipmentDistribution)
        .where(
            EquipmentDistribution.is_deleted.is_(False),
            EquipmentDistribution.status.in_(OPEN_DISTRIBUTION_STATUSES),
            EquipmentDistribution.expected_return_date.is_not(None),
            EquipmentDistribution.expected_return_date < today_local(),
        )
    ) or 0
    faulty = db.scalar(
        select(func.coalesce(func.sum(EquipmentItem.quantity), 0)).where(
            EquipmentItem.is_deleted.is_(False),
            EquipmentItem.condition == EquipmentCondition.FAULTY,
            EquipmentItem.status != EquipmentItemStatus.WRITTEN_OFF,
        )
    ) or 0
    total_items = db.scalar(
        select(func.count()).select_from(EquipmentItem).where(EquipmentItem.is_deleted.is_(False))
    ) or 0

    return {
        "total_items": total_items,
        "in_stock": sum(r["in_stock"] for r in rows),
        "issued_out": sum(r["issued_out"] for r in rows),
        "quarantined": sum(r["quarantined"] for r in rows),
        "faulty": int(faulty),
        "overdue_distributions": overdue,
        "types_below_reorder": sum(1 for r in rows if r["below_reorder"]),
    }


def overdue_filter():
    """Reusable predicate for 'still out and past its return date'."""
    return and_(
        EquipmentDistribution.status.in_(OPEN_DISTRIBUTION_STATUSES),
        EquipmentDistribution.expected_return_date.is_not(None),
        EquipmentDistribution.expected_return_date < today_local(),
    )


def searchable_holder_clause(pattern: str):
    """Match a distribution by whoever is holding the units."""
    from app.models.customer import Customer

    return or_(
        EquipmentDistribution.issued_to_user_id.in_(
            select(User.id).where(User.full_name.ilike(pattern))
        ),
        EquipmentDistribution.issued_to_customer_id.in_(
            select(Customer.id).where(
                or_(Customer.full_name.ilike(pattern), Customer.customer_code.ilike(pattern))
            )
        ),
    )