"""Import Data - Quotations 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_quotations_file

router = APIRouter()


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 QuotationImportBody(BaseModel):
    property_id: str
    quotations: list[dict]


@router.get("/")
async def list_quotations(
    request: Request,
    property_id: Optional[str] = Query(None),
    limit: int = Query(20, le=100),
    offset: int = Query(0, ge=0),
    sort_by: str = Query("issue_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 ("issue_date", "created_at", "amount", "quotation_number"):
        order_col = f"q.{sort_by}" if sort_by != "issue_date" else "COALESCE(q.issue_date, q.created_at::date)"
    else:
        order_col = "COALESCE(q.issue_date, q.created_at::date)"
    where = " q.property_id = :pid "
    params = {"pid": pid}
    if status and status in ("pending", "approved", "rejected"):
        where += " AND q.status = :st "
        params["st"] = status

    with csql.pool.connect() as conn:
        total = conn.execute(
            sqlalchemy.text(f"SELECT COUNT(*) FROM quotations q 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 = 'quotation'"
            ),
            {"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 AS category, COUNT(*)::int AS lines, COALESCE(SUM(qty),0)::float AS qty, COALESCE(SUM(line_amount),0)::float AS amount
            FROM quotation_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 AS product, category AS category, COALESCE(SUM(qty),0)::float AS qty, COALESCE(SUM(line_amount),0)::float AS amount
            FROM quotation_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 quotations 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 q.id, q.quotation_number, q.customer, q.amount, q.status, q.issue_date, q.valid_until, q.created_at
            FROM quotations q 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]),
            "quotation_number": r[1],
            "customer": r[2],
            "amount": float(r[3]),
            "status": r[4],
            "issue_date": str(r[5]) if r[5] else None,
            "valid_until": 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_quotations_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_quotations_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 q in rows:
            if not q.get("quotation_number"):
                continue
            cust = (q.get("customer") or "").strip() or "(unknown)"
            amt = float(q.get("amount") or 0)
            st = (q.get("status") or "pending").strip().lower()
            if st not in ("pending", "approved", "rejected"):
                st = "pending"
            res = conn.execute(
                sqlalchemy.text("""
                    INSERT INTO quotations (property_id, quotation_number, customer, amount, status, issue_date, valid_until, created_by)
                    VALUES (:pid, :qn, :cust, :amt, :st, :idate, :vuntil, :uid)
                    RETURNING id
                """),
                {
                    "pid": pid,
                    "qn": str(q["quotation_number"]).strip(),
                    "cust": cust,
                    "amt": amt,
                    "st": st,
                    "idate": q.get("issue_date"),
                    "vuntil": q.get("valid_until"),
                    "uid": user_id,
                },
            )
            qid = res.scalar()
            for line in q.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 quotation_lines (quotation_id, property_id, line_no, product, product_sku, qty, unit_price, line_amount, category)
                        VALUES (:qid, :pid, :ln, :prod, :sku, :qty, :up, :la, :cat)
                    """),
                    {
                        "qid": qid,
                        "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, 'quotation', 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} quotation(s)", "imported": imported})


@router.post("/import")
async def import_quotations_json(request: Request, body: QuotationImportBody):
    user_id = getattr(request.state, "user_id", None)
    imported = 0
    csql = get_cloudsql()
    with csql.pool.connect() as conn:
        for q in body.quotations:
            if not q.get("quotation_number"):
                continue
            cust = (q.get("customer") or "").strip() or "(unknown)"
            amt = float(q.get("amount") or 0)
            st = (str(q.get("status") or "pending").strip().lower())
            if st not in ("pending", "approved", "rejected"):
                st = "pending"
            res = conn.execute(
                sqlalchemy.text("""
                    INSERT INTO quotations (property_id, quotation_number, customer, amount, status, issue_date, valid_until, created_by)
                    VALUES (:pid, :qn, :cust, :amt, :st, :idate, :vuntil, :uid)
                    RETURNING id
                """),
                {
                    "pid": body.property_id,
                    "qn": str(q["quotation_number"]).strip(),
                    "cust": cust,
                    "amt": amt,
                    "st": st,
                    "idate": q.get("issue_date"),
                    "vuntil": q.get("valid_until"),
                    "uid": user_id,
                },
            )
            qid = res.scalar()
            for line in q.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 quotation_lines (quotation_id, property_id, line_no, product, product_sku, qty, unit_price, line_amount, category)
                        VALUES (:qid, :pid, :ln, :prod, :sku, :qty, :up, :la, :cat)
                    """),
                    {
                        "qid": qid,
                        "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, 'quotation', 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} quotation(s)", "imported": imported})


_QUOTE_CSV = "quotation_number,customer,amount,status,issue_date,valid_until,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_quotations_csv():
    return Response(
        content=_QUOTE_CSV.encode("utf-8"),
        media_type="text/csv; charset=utf-8",
        headers={"Content-Disposition": 'attachment; filename="quotations-example.csv"'},
    )


@router.get("/import/example/xlsx")
async def example_quotations_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 = ["quotation_number", "customer", "amount", "status", "issue_date", "valid_until", "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="quotations-example.xlsx"'},
    )