from __future__ import annotations

import sys

from sqlalchemy import text

from app import create_app
from app.extensions import db
from app.models.client import Client
from app.models.company import Company
from app.models.invoice import Invoice
from app.services.invoice_party_snapshot import (
    build_client_snapshot,
    build_company_snapshot,
    dumps_snapshot,
)
from app.services.invoice_xml_store import load_original_invoice_xml
from app.services.xml_fatturapa import parse_fpr12


def _column_exists(column_name: str) -> bool:
    sql = text(
        """
        SELECT COUNT(*)
        FROM information_schema.columns
        WHERE table_schema = DATABASE()
          AND table_name = 'invoices'
          AND column_name = :col
        """
    )
    return int(db.session.execute(sql, {"col": column_name}).scalar() or 0) > 0


def _ensure_columns() -> None:
    wanted = [
        ("client_snapshot_json", "LONGTEXT NULL"),
        ("company_snapshot_json", "LONGTEXT NULL"),
    ]

    missing = [(c, ddl) for (c, ddl) in wanted if not _column_exists(c)]
    if not missing:
        print("OK: columns already exist")
        return

    clauses = ", ".join([f"ADD COLUMN {c} {ddl}" for (c, ddl) in missing])
    alter = f"ALTER TABLE invoices {clauses};"
    print("Applying:", alter)
    db.session.execute(text(alter))
    db.session.commit()
    print("OK: columns added")


def _backfill() -> None:
    total = 0
    updated = 0
    from_xml = 0
    from_fk = 0

    q = Invoice.query.order_by(Invoice.id.asc())
    batch = 0
    for inv in q.yield_per(200):
        total += 1
        changed = False

        if not getattr(inv, "client_snapshot_json", None):
            snap = None
            original = load_original_invoice_xml(inv.id)
            if original:
                try:
                    parsed = parse_fpr12(original)
                    snap = dumps_snapshot(build_client_snapshot(parsed.client))
                    from_xml += 1
                except Exception:
                    snap = None
            if not snap and inv.client_id:
                c = Client.query.get(inv.client_id)
                if c:
                    snap = dumps_snapshot(build_client_snapshot(c))
                    from_fk += 1
            if snap:
                inv.client_snapshot_json = snap
                changed = True

        if not getattr(inv, "company_snapshot_json", None):
            snap = None
            original = load_original_invoice_xml(inv.id)
            if original:
                try:
                    parsed = parse_fpr12(original)
                    snap = dumps_snapshot(build_company_snapshot(parsed.company))
                    from_xml += 1
                except Exception:
                    snap = None
            if not snap and inv.company_id:
                c = Company.query.get(inv.company_id)
                if c:
                    snap = dumps_snapshot(build_company_snapshot(c))
                    from_fk += 1
            if snap:
                inv.company_snapshot_json = snap
                changed = True

        if changed:
            db.session.add(inv)
            updated += 1
            batch += 1

        if batch >= 200:
            db.session.commit()
            batch = 0

    if batch:
        db.session.commit()

    print(f"Backfill done: invoices={total}, updated={updated}, from_xml={from_xml}, from_fk={from_fk}")


def main() -> int:
    app = create_app()
    with app.app_context():
        _ensure_columns()
        _backfill()
    return 0


if __name__ == "__main__":
    raise SystemExit(main())