"""Runs model.sql in DuckDB on the inputs of a Book and writes the results back.

``compute(book)`` is the same entry point the Python target offers, so the
reconciliation and the regression test run unchanged. The SQL itself is the
deliverable: model.sql and functions.sql run on any DuckDB; the manifest says
which table holds each sheet and which physical column holds each Excel
column.
"""

from __future__ import annotations

import json
from pathlib import Path

from .xl import Book, Unsupported, XlError, col_letter

_HERE = Path(__file__).parent
MANIFEST = json.loads((_HERE / "manifest.json").read_text(encoding="utf-8"))


def load_inputs(path: str | Path | None = None) -> Book:
    """Load the workbook's input cells and defined names into a Book."""
    return Book.from_json(str(path or _HERE / "inputs.json"))


def _connect():
    import duckdb

    con = duckdb.connect()
    con.execute((_HERE / "functions.sql").read_text(encoding="utf-8"))
    return con


def compute(book: Book) -> Book:
    """Load the Book's constant cells, run every step, and read the formula cells back."""
    con = _connect()
    con.execute("CREATE TABLE cells(sheet VARCHAR, row_num INTEGER, col VARCHAR, num DOUBLE, txt VARCHAR, flag BOOLEAN)")
    con.execute("CREATE TABLE sheet_rows(sheet VARCHAR, row_num INTEGER)")
    rows, spine = [], []
    for title, info in MANIFEST["sheets"].items():
        grid = book.sheets.get(title)
        spine += [(title, r) for r in range(1, info["max_row"] + 1)]
        if grid is None:
            continue
        for (r, c), v in grid.items():
            if v is None or isinstance(v, XlError):
                continue
            if isinstance(v, bool):
                rows.append((title, r, col_letter(c), None, None, v))
            elif isinstance(v, (int, float)):
                rows.append((title, r, col_letter(c), float(v), None, None))
            elif isinstance(v, str):
                if v != "":
                    rows.append((title, r, col_letter(c), None, v, None))
    con.executemany("INSERT INTO sheet_rows VALUES (?, ?)", spine)
    if rows:
        con.executemany("INSERT INTO cells VALUES (?, ?, ?, ?, ?, ?)", rows)
    con.execute((_HERE / "model.sql").read_text(encoding="utf-8"))

    values: dict[tuple[str, int, int], object] = {}
    for title, info in MANIFEST["sheets"].items():
        if not info["final"]:
            continue
        cur = con.execute(f'SELECT * FROM "{info["final"]}" ORDER BY row_num')
        names = [d[0] for d in cur.description]
        columns = info["columns"]
        for record in cur.fetchall():
            row = int(record[0])
            merged: dict[int, dict[str, object]] = {}
            for name, value in zip(names[1:], record[1:]):
                col, kind = columns[name]
                if value is not None:
                    merged.setdefault(col, {})[kind] = value
            for col, by_kind in merged.items():
                if "num" in by_kind:
                    values[(title, row, col)] = float(by_kind["num"])
                elif "text" in by_kind:
                    values[(title, row, col)] = by_kind["text"]
                elif "bool" in by_kind:
                    values[(title, row, col)] = bool(by_kind["bool"])
    for entry in MANIFEST["blocks"]:
        grid = book[entry["sheet"]]
        for r in range(entry["r0"], entry["r1"] + 1):
            for c in range(entry["c0"], entry["c1"] + 1):
                if entry["stub"]:
                    grid.set_raw(r, c, Unsupported(entry["stub"]))
                else:
                    grid.set_raw(r, c, values.get((entry["sheet"], r, c)))
    con.close()
    return book