"""Schema-level integrity checks: FKs, check constraints, uniqueness, cascades.

    python -m app.db.verify_schema
"""
from datetime import date, timedelta

from sqlalchemy import func, select, text
from sqlalchemy.exc import DataError, IntegrityError

from app.core.time import today_local, utcnow
from app.db.session import SessionLocal, engine
from app.models.application import ApplicationStatusHistory, ConnectionApplication
from app.models.customer import Connection, Customer
from app.models.enums import (
    ApplicationStatus,
    DistributionStatus,
    EquipmentCondition,
    HolderType,
    RequisitionStatus,
)
from app.models.equipment import EquipmentDistribution, EquipmentItem
from app.models.reference import Area, EquipmentType, Location
from app.models.requisition import Requisition, RequisitionItem, RequisitionStatusHistory
from app.models.user import User

PASS, FAIL = "PASS", "FAIL"
results: list[tuple[str, str, str]] = []


def check(name: str, fn) -> None:
    try:
        fn()
        results.append((PASS, name, ""))
    except AssertionError as exc:
        results.append((FAIL, name, str(exc)))
    except Exception as exc:  # unexpected error is also a failure
        results.append((FAIL, name, f"{type(exc).__name__}: {exc}"))


def main() -> None:
    db = SessionLocal()
    area = db.scalar(select(Area).limit(1))
    location = db.scalar(select(Location).where(Location.is_default.is_(True)))
    eq_type = db.scalar(select(EquipmentType).where(EquipmentType.code == "ONU"))
    tech = db.scalar(select(User).where(User.email == "tech1@isp.local"))
    admin = db.scalar(select(User).where(User.email == "admin@isp.local"))

    # --- 1. reference data present ---
    def ref_data():
        assert area and location and eq_type and tech and admin, "seed data missing"
    check("seed reference data present", ref_data)

    # --- 2. FK enforcement: bogus area_id must be rejected ---
    def fk_reject():
        app = ConnectionApplication(
            application_no="TEST-FK-1", applicant_name="X", phone="017", address="a",
            area_id=999999, requested_date=today_local(),
        )
        db.add(app)
        try:
            db.flush()
            raise AssertionError("bogus area_id was accepted")
        except IntegrityError:
            db.rollback()
    check("FK rejects invalid area_id", fk_reject)

    # --- 3. enum rejects unknown label ---
    def enum_reject():
        try:
            db.execute(text(
                "INSERT INTO connection_applications "
                "(application_no, applicant_name, phone, address, area_id, "
                " requested_date, status, created_at, updated_at, is_deleted) "
                "VALUES ('TEST-ENUM','X','017','a',:a,CURRENT_DATE,'bogus_status',now(),now(),false)"
            ), {"a": area.id})
            db.flush()
            raise AssertionError("invalid enum label was accepted")
        except (DataError, IntegrityError):
            db.rollback()
    check("enum rejects invalid status label", enum_reject)

    # --- 4. unique constraint on application_no ---
    def unique_app_no():
        for _ in range(2):
            db.add(ConnectionApplication(
                application_no="TEST-UNIQ", applicant_name="Y", phone="018", address="b",
                area_id=area.id, requested_date=today_local(),
            ))
        try:
            db.flush()
            raise AssertionError("duplicate application_no accepted")
        except IntegrityError:
            db.rollback()
    check("unique application_no enforced", unique_app_no)

    # --- 5. holder_target_consistent check constraint ---
    def holder_consistency():
        # technician holder with no user set -> must fail
        db.add(EquipmentItem(
            equipment_type_id=eq_type.id, serial_number="TEST-BAD-HOLDER",
            condition=EquipmentCondition.NEW, quantity=1,
            holder_type=HolderType.TECHNICIAN, holder_user_id=None,
        ))
        try:
            db.flush()
            raise AssertionError("inconsistent holder accepted")
        except IntegrityError:
            db.rollback()
    check("holder_target_consistent constraint", holder_consistency)

    # --- 6. returned_quantity <= quantity ---
    def returned_bounds():
        item = EquipmentItem(
            equipment_type_id=eq_type.id, serial_number="TEST-RQ-1",
            condition=EquipmentCondition.NEW, quantity=1,
            holder_type=HolderType.WAREHOUSE, location_id=location.id,
        )
        db.add(item)
        db.flush()
        db.add(EquipmentDistribution(
            equipment_item_id=item.id, quantity=1, returned_quantity=5,
            issued_to_type=HolderType.TECHNICIAN, issued_to_user_id=tech.id,
            issued_by_id=admin.id, issued_at=utcnow(), status=DistributionStatus.ISSUED,
        ))
        try:
            db.flush()
            raise AssertionError("returned_quantity > quantity accepted")
        except IntegrityError:
            db.rollback()
    check("returned_within_issued constraint", returned_bounds)

    # --- 7. status-history cascade delete ---
    def history_cascade():
        app = ConnectionApplication(
            application_no="TEST-CASCADE", applicant_name="Z", phone="019", address="c",
            area_id=area.id, requested_date=today_local(),
            status=ApplicationStatus.PENDING,
        )
        db.add(app)
        db.flush()
        db.add(ApplicationStatusHistory(
            application_id=app.id, from_status=None, to_status=ApplicationStatus.PENDING,
            changed_by_id=admin.id, changed_at=utcnow(),
        ))
        db.flush()
        app_id = app.id
        db.delete(app)
        db.flush()
        left = db.scalar(select(func.count()).select_from(ApplicationStatusHistory)
                         .where(ApplicationStatusHistory.application_id == app_id))
        assert left == 0, f"{left} orphan history rows remain"
        db.rollback()
    check("history cascades on application delete", history_cascade)

    # --- 8. RESTRICT blocks deleting a referenced area ---
    def restrict_area():
        app = ConnectionApplication(
            application_no="TEST-RESTRICT", applicant_name="W", phone="016", address="d",
            area_id=area.id, requested_date=today_local(),
        )
        db.add(app)
        db.flush()
        try:
            db.execute(text("DELETE FROM areas WHERE id = :a"), {"a": area.id})
            db.flush()
            raise AssertionError("deleting referenced area was allowed")
        except IntegrityError:
            db.rollback()
    check("RESTRICT protects referenced area", restrict_area)

    # --- 9. timestamps auto-populate; UTC-aware ---
    def timestamps():
        app = ConnectionApplication(
            application_no="TEST-TS", applicant_name="T", phone="015", address="e",
            area_id=area.id, requested_date=today_local(),
        )
        db.add(app)
        db.flush()
        db.refresh(app)
        assert app.created_at is not None and app.updated_at is not None, "timestamps not set"
        assert app.created_at.tzinfo is not None, "created_at is naive"
        assert app.is_deleted is False, "is_deleted should default False"
        db.rollback()
    check("created_at/updated_at/is_deleted defaults", timestamps)

    # --- 10. overdue distribution detection ---
    def overdue_flag():
        item = EquipmentItem(
            equipment_type_id=eq_type.id, serial_number="TEST-OVERDUE",
            condition=EquipmentCondition.NEW, quantity=1,
            holder_type=HolderType.TECHNICIAN, holder_user_id=tech.id,
        )
        db.add(item)
        db.flush()
        dist = EquipmentDistribution(
            equipment_item_id=item.id, quantity=1,
            issued_to_type=HolderType.TECHNICIAN, issued_to_user_id=tech.id,
            issued_by_id=admin.id, issued_at=utcnow(),
            expected_return_date=today_local() - timedelta(days=3),
            status=DistributionStatus.ISSUED,
        )
        db.add(dist)
        db.flush()
        assert dist.is_overdue is True, "overdue distribution not flagged"
        db.rollback()
    check("overdue distribution flagged", overdue_flag)

    # --- 11. UTF-8 / Bangla text round-trip ---
    def bangla():
        name = "মোহাম্মদ রহিম উদ্দিন"
        app = ConnectionApplication(
            application_no="TEST-BN", applicant_name=name, phone="014",
            address="মিরপুর, ঢাকা", area_id=area.id,
            requested_date=today_local(),
        )
        db.add(app)
        db.flush()
        db.refresh(app)
        assert app.applicant_name == name, f"got {app.applicant_name!r}"
        db.rollback()
    check("Bangla (UTF-8) text round-trip", bangla)

    # --- 12. connection<->application 1:1 uniqueness ---
    def one_to_one():
        app = ConnectionApplication(
            application_no="TEST-1TO1", applicant_name="V", phone="013", address="f",
            area_id=area.id, requested_date=today_local(),
        )
        db.add(app)
        db.flush()
        cust = Customer(
            customer_code="TEST-C1", full_name="V", phone="013", address="f", area_id=area.id
        )
        db.add(cust)
        db.flush()
        for code in ("TEST-CN1", "TEST-CN2"):
            db.add(Connection(
                connection_code=code, customer_id=cust.id, application_id=app.id,
                area_id=area.id, activated_on=date.today(),
            ))
        try:
            db.flush()
            raise AssertionError("two connections shared one application")
        except IntegrityError:
            db.rollback()
    check("application_id unique on connections", one_to_one)

    # --- 13. requisition line items cascade on order delete ---
    def requisition_items_cascade():
        req = Requisition(
            req_no="TEST-REQ", purpose="schema check",
            requested_by_id=admin.id, requested_at=utcnow(),
        )
        db.add(req)
        db.flush()
        db.add(RequisitionItem(
            requisition_id=req.id, item_name="Test item", quantity=2, unit="pcs"
        ))
        db.add(RequisitionStatusHistory(
            requisition_id=req.id, from_status=None, to_status=RequisitionStatus.PENDING,
            changed_by_id=admin.id, changed_at=utcnow(),
        ))
        db.flush()
        req_id = req.id
        db.delete(req)
        db.flush()
        for table in (RequisitionItem, RequisitionStatusHistory):
            left = db.scalar(
                select(func.count()).select_from(table).where(table.requisition_id == req_id)
            )
            assert left == 0, f"{left} orphan rows remain on {table.__tablename__}"
        db.rollback()
    check("requisition items/history cascade on delete", requisition_items_cascade)

    # --- 14. requisition quantity must be positive ---
    def requisition_quantity_positive():
        req = Requisition(
            req_no="TEST-REQ-QTY", purpose="schema check",
            requested_by_id=admin.id, requested_at=utcnow(),
        )
        db.add(req)
        db.flush()
        db.add(RequisitionItem(
            requisition_id=req.id, item_name="Test item", quantity=0, unit="pcs"
        ))
        try:
            db.flush()
            raise AssertionError("quantity <= 0 accepted")
        except IntegrityError:
            db.rollback()
    check("requisition quantity must be positive", requisition_quantity_positive)

    db.close()

    # --- report ---
    width = max(len(n) for _, n, _ in results) + 2
    print("\nSchema verification")
    print("-" * (width + 30))
    for status, name, detail in results:
        line = f"[{status}] {name.ljust(width)}"
        if detail:
            line += f" -> {detail}"
        print(line)
    failed = sum(1 for s, _, _ in results if s == FAIL)
    print("-" * (width + 30))
    print(f"{len(results) - failed}/{len(results)} checks passed")
    if failed:
        raise SystemExit(1)


if __name__ == "__main__":
    main()