"""Import Data - Billings endpoints (header + line_items)."""
import io
from typing import Optional

from fastapi import APIRouter, Request, Query, UploadFile, File, Form, Header
from fastapi.responses import JSONResponse, Response
import sqlalchemy
from pydantic import BaseModel

from api.feature.import_data.db import get_cloudsql
from api.feature.import_data.parsing import parse_billings_file

router = APIRouter()

BILLING_STATUSES = ("pending", "paid", "overdue", "cancelled")


def _property_id(header: Optional[str], query: Optional[str]) -> str:
    return (query or header or "").strip()


def _iso(ts) -> Optional[str]:
    if ts is None:
        return None
    return ts.isoformat() if hasattr(ts, "isoformat") else str(ts)


class BillingImportBody(BaseModel):
    property_id: str
    billings: list[dict]


@router.get("/")
async def list_billings(
    request: Request,
    property_id: Optional[str] = Query(None),
    limit: int = Query(20, le=100),
    offset: int = Query(0, ge=0),
    sort_by: str = Query("invoice_date"),
    order: str = Query("desc"),
    status: Optional[str] = Query(None),
    x_property: Optional[str] = Header(None, alias="x-property"),
):
    pid = _property_id(x_property, property_id)
    if not pid:
        return JSONResponse(status_code=400, content={"success": False, "message": "property_id required"})

    csql = get_cloudsql()
    order_dir = "DESC" if (order or "desc").lower() == "desc" else "ASC"
    if sort_by in ("invoice_date", "created_at", "amount", "invoice_number"):
        order_col = f"b.{sort_by}" if sort_by != "invoice_date" else "COALESCE(b.invoice_date, b.created_at::date)"
    else:
        order_col = "COALESCE(b.invoice_date, b.created_at::date)"
    where = " b.property_id = :pid "
    params = {"pid": pid}
    if status and status in BILLING_STATUSES:
        where += " AND b.status = :st "
        params["st"] = status

    with csql.pool.connect() as conn:
        total = conn.execute(
            sqlalchemy.text(f"SELECT COUNT(*) FROM billings b WHERE {where}"), params
        ).scalar() or 0
        meta = conn.execute(
            sqlalchemy.text(
                "SELECT last_import_at, last_sync_at FROM sync_meta WHERE property_id = :pid AND entity = 'billing'"
            ),
            {"pid": pid},
        ).fetchone()
        last_import = _iso(meta[0]) if meta else None
        last_sync = _iso(meta[1]) if meta and len(meta) > 1 else None

        cat_sql = """
            SELECT category, COUNT(*)::int AS lines, COALESCE(SUM(qty),0)::float AS qty, COALESCE(SUM(line_amount),0)::float AS amount
            FROM billing_lines WHERE property_id = :pid AND category IS NOT NULL AND TRIM(category) <> ''
            GROUP BY category
        """
        cat_rows = conn.execute(sqlalchemy.text(cat_sql), {"pid": pid}).fetchall()
        category_summary = [{"category": r[0], "lines": r[1], "qty": r[2], "amount": float(r[3])} for r in cat_rows]

        top_sql = """
            SELECT product, category, COALESCE(SUM(qty),0)::float AS qty, COALESCE(SUM(line_amount),0)::float AS amount
            FROM billing_lines WHERE property_id = :pid
            GROUP BY product, category ORDER BY amount DESC LIMIT 10
        """
        top_rows = conn.execute(sqlalchemy.text(top_sql), {"pid": pid}).fetchall()
        top_products = [{"product": r[0], "category": r[1], "qty": r[2], "amount": float(r[3])} for r in top_rows]

        tv_row = conn.execute(
            sqlalchemy.text("SELECT COALESCE(SUM(amount),0) FROM billings WHERE property_id = :pid"),
            {"pid": pid},
        ).fetchone()
        total_value = float(tv_row[0] or 0)

        params["lim"] = limit
        params["off"] = offset
        list_sql = f"""
            SELECT b.id, b.invoice_number, b.customer, b.amount, b.status, b.invoice_date, b.due_date, b.created_at
            FROM billings b WHERE {where}
            ORDER BY {order_col} {order_dir} LIMIT :lim OFFSET :off
        """
        rows = conn.execute(sqlalchemy.text(list_sql), params).fetchall()

    data = [
        {
            "id": str(r[0]),
            "invoice_number": r[1],
            "customer": r[2],
            "amount": float(r[3]),
            "status": r[4],
            "invoice_date": str(r[5]) if r[5] else None,
            "due_date": str(r[6]) if r[6] else None,
            "created_at": _iso(r[7]),
        }
        for r in rows
    ]
    return JSONResponse(
        content={
            "success": True,
            "total": total,
            "last_import": last_import,
            "last_sync": last_sync,
            "limit": limit,
            "offset": offset,
            "category_summary": category_summary,
            "top_products": top_products,
            "total_value": total_value,
            "data": data,
        }
    )


@router.post("/import/file")
async def import_billings_file(
    request: Request,
    file: UploadFile = File(...),
    property_id: str = Form(""),
    x_property: Optional[str] = Header(None, alias="x-property"),
):
    pid = (property_id or (x_property or "")).strip()
    if not pid:
        return JSONResponse(status_code=400, content={"success": False, "message": "property_id required"})
    content = await file.read()
    try:
        rows = parse_billings_file(content, file.filename or "")
    except Exception as e:
        return JSONResponse(status_code=400, content={"success": False, "message": f"Parse error: {e}"})

    user_id = getattr(request.state, "user_id", None)
    imported = 0
    csql = get_cloudsql()
    with csql.pool.connect() as conn:
        for bl in rows:
            if not (bl.get("invoice_number") or "").strip():
                continue
            inv = str(bl.get("invoice_number") or "").strip()
            cust = (bl.get("customer") or "").strip() or "(unknown)"
            amt = float(bl.get("amount") or 0)
            st = (str(bl.get("status") or "pending").strip().lower())
            if st not in BILLING_STATUSES:
                st = "pending"
            res = conn.execute(
                sqlalchemy.text("""
                    INSERT INTO billings (property_id, invoice_number, customer, amount, status, invoice_date, due_date, created_by)
                    VALUES (:pid, :inv, :cust, :amt, :st, :idate, :ddate, :uid)
                    RETURNING id
                """),
                {
                    "pid": pid,
                    "inv": inv,
                    "cust": cust,
                    "amt": amt,
                    "st": st,
                    "idate": bl.get("invoice_date"),
                    "ddate": bl.get("due_date"),
                    "uid": user_id,
                },
            )
            bid = res.scalar()
            for line in bl.get("line_items") or []:
                ln = int(line.get("line_no") or line.get("line") or 0) or 1
                qty = float(line.get("qty") or 0)
                up = float(line.get("unit_price") or 0)
                la = float(line.get("line_amount") or 0) or (qty * up)
                conn.execute(
                    sqlalchemy.text("""
                        INSERT INTO billing_lines (billing_id, property_id, line_no, product, product_sku, qty, unit_price, line_amount, category)
                        VALUES (:bid, :pid, :ln, :prod, :sku, :qty, :up, :la, :cat)
                    """),
                    {
                        "bid": bid,
                        "pid": pid,
                        "ln": ln,
                        "prod": (line.get("product") or "").strip() or None,
                        "sku": (line.get("product_sku") or "").strip() or None,
                        "qty": qty,
                        "up": up,
                        "la": la,
                        "cat": (line.get("category") or "").strip() or None,
                    },
                )
            imported += 1
        conn.execute(
            sqlalchemy.text("""
                INSERT INTO sync_meta (property_id, entity, last_import_at, updated_at)
                VALUES (:pid, 'billing', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
                ON CONFLICT (property_id, entity) DO UPDATE SET last_import_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
            """),
            {"pid": pid},
        )
        conn.commit()
    return JSONResponse(content={"success": True, "message": f"Imported {imported} billing(s)", "imported": imported})


@router.post("/import")
async def import_billings_json(request: Request, body: BillingImportBody):
    user_id = getattr(request.state, "user_id", None)
    imported = 0
    csql = get_cloudsql()
    with csql.pool.connect() as conn:
        for bl in body.billings:
            if not (bl.get("invoice_number") or "").strip():
                continue
            inv = str(bl.get("invoice_number") or "").strip()
            cust = (bl.get("customer") or "").strip() or "(unknown)"
            amt = float(bl.get("amount") or 0)
            st = (str(bl.get("status") or "pending").strip().lower())
            if st not in BILLING_STATUSES:
                st = "pending"
            res = conn.execute(
                sqlalchemy.text("""
                    INSERT INTO billings (property_id, invoice_number, customer, amount, status, invoice_date, due_date, created_by)
                    VALUES (:pid, :inv, :cust, :amt, :st, :idate, :ddate, :uid)
                    RETURNING id
                """),
                {
                    "pid": body.property_id,
                    "inv": inv,
                    "cust": cust,
                    "amt": amt,
                    "st": st,
                    "idate": bl.get("invoice_date"),
                    "ddate": bl.get("due_date"),
                    "uid": user_id,
                },
            )
            bid = res.scalar()
            for line in bl.get("line_items") or []:
                ln = int(line.get("line_no") or line.get("line") or 0) or 1
                qty = float(line.get("qty") or 0)
                up = float(line.get("unit_price") or 0)
                la = float(line.get("line_amount") or 0) or (qty * up)
                conn.execute(
                    sqlalchemy.text("""
                        INSERT INTO billing_lines (billing_id, property_id, line_no, product, product_sku, qty, unit_price, line_amount, category)
                        VALUES (:bid, :pid, :ln, :prod, :sku, :qty, :up, :la, :cat)
                    """),
                    {
                        "bid": bid,
                        "pid": body.property_id,
                        "ln": ln,
                        "prod": (line.get("product") or "").strip() or None,
                        "sku": (line.get("product_sku") or "").strip() or None,
                        "qty": qty,
                        "up": up,
                        "la": la,
                        "cat": (line.get("category") or "").strip() or None,
                    },
                )
            imported += 1
        conn.execute(
            sqlalchemy.text("""
                INSERT INTO sync_meta (property_id, entity, last_import_at, updated_at)
                VALUES (:pid, 'billing', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
                ON CONFLICT (property_id, entity) DO UPDATE SET last_import_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
            """),
            {"pid": body.property_id},
        )
        conn.commit()
    return JSONResponse(content={"success": True, "message": f"Imported {imported} billing(s)", "imported": imported})


_BILL_CSV = "invoice_number,customer,amount,status,invoice_date,due_date,line_1_product,line_1_qty,line_1_unit_price,line_1_line_amount,line_1_category\n"


@router.get("/import/example/csv")
async def example_billings_csv():
    return Response(
        content=_BILL_CSV.encode("utf-8"),
        media_type="text/csv; charset=utf-8",
        headers={"Content-Disposition": 'attachment; filename="billings-example.csv"'},
    )


@router.get("/import/example/xlsx")
async def example_billings_xlsx():
    try:
        from openpyxl import Workbook
    except ImportError:
        return JSONResponse(status_code=501, content={"success": False, "message": "openpyxl not installed"})
    wb = Workbook()
    ws = wb.active
    headers = ["invoice_number", "customer", "amount", "status", "invoice_date", "due_date", "line_1_product", "line_1_qty", "line_1_unit_price", "line_1_line_amount", "line_1_category"]
    for i, h in enumerate(headers, 1):
        ws.cell(row=1, column=i, value=h)
    buf = io.BytesIO()
    wb.save(buf)
    buf.seek(0)
    return Response(
        content=buf.read(),
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": 'attachment; filename="billings-example.xlsx"'},
    )