"""SQLite helpers for the Lunch Money finance store."""
import datetime as dt
import json
import sqlite3
from pathlib import Path


def _json_default(o):
    if isinstance(o, (dt.date, dt.datetime)):
        return o.isoformat()
    raise TypeError(f"not JSON serializable: {type(o)}")


def _dumps(o):
    return json.dumps(o, default=_json_default)


ROOT = Path(__file__).parent
DB_PATH = ROOT / "data" / "transactions.db"
SCHEMA_PATH = ROOT / "schema.sql"


def connect():
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


def init_db():
    conn = connect()
    conn.executescript(SCHEMA_PATH.read_text())
    conn.commit()
    conn.close()


def upsert_category(c: dict):
    conn = connect()
    conn.execute(
        """INSERT OR REPLACE INTO categories
        (id, name, description, is_income, is_group, group_id, excluded_from_budget)
        VALUES (?, ?, ?, ?, ?, ?, ?)""",
        (
            c["id"], c["name"], c.get("description"),
            1 if c.get("is_income") else 0,
            1 if c.get("is_group") else 0,
            c.get("group_id"),
            1 if c.get("exclude_from_budget") else 0,
        ),
    )
    conn.commit()
    conn.close()


def _category_name(conn, category_id):
    if category_id is None:
        return None
    row = conn.execute("SELECT name FROM categories WHERE id = ?", (category_id,)).fetchone()
    return row["name"] if row else None


def upsert_transaction(t: dict):
    conn = connect()
    asset_id = t.get("asset_id")
    plaid_id = t.get("plaid_account_id")
    if asset_id is not None:
        account_id, source = str(asset_id), "asset"
    elif plaid_id is not None:
        account_id, source = str(plaid_id), "plaid"
    else:
        account_id, source = None, None

    category_name = _category_name(conn, t.get("category_id"))

    conn.execute(
        """INSERT OR REPLACE INTO transactions
        (id, account_id, account_source, date, payee, amount, currency,
         category_id, category_name, notes, status, is_pending, recurring_id,
         raw_json, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)""",
        (
            str(t["id"]),
            account_id,
            source,
            t["date"],
            t.get("payee") or "(unnamed)",
            float(t["amount"]),
            t.get("currency", "usd"),
            t.get("category_id"),
            category_name,
            t.get("notes"),
            t.get("status"),
            1 if t.get("is_pending") else 0,
            t.get("recurring_id"),
            _dumps(t),
        ),
    )
    conn.commit()
    conn.close()


def upsert_asset(a: dict):
    conn = connect()
    conn.execute(
        """INSERT OR REPLACE INTO accounts
        (id, source, name, type, subtype, mask, balance, currency, institution_name, last_synced_at)
        VALUES (?, 'asset', ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)""",
        (
            str(a["id"]),
            a.get("display_name") or a.get("name"),
            a.get("type_name"),
            a.get("subtype_name"),
            None,
            float(a["balance"]) if a.get("balance") is not None else None,
            a.get("currency", "usd"),
            a.get("institution_name"),
        ),
    )
    conn.commit()
    conn.close()


def upsert_plaid_account(p: dict):
    conn = connect()
    conn.execute(
        """INSERT OR REPLACE INTO accounts
        (id, source, name, type, subtype, mask, balance, currency, institution_name, last_synced_at)
        VALUES (?, 'plaid', ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)""",
        (
            str(p["id"]),
            p.get("name"),
            p.get("type"),
            p.get("subtype"),
            p.get("mask"),
            float(p["balance"]) if p.get("balance") is not None else None,
            p.get("currency", "usd"),
            p.get("institution_name"),
        ),
    )
    conn.commit()
    conn.close()


def upsert_recurring(r: dict):
    conn = connect()
    conn.execute(
        """INSERT OR REPLACE INTO recurring_groups
        (id, payee, typical_amount, cadence, start_date, end_date, category_id, notes, raw_json)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
        (
            r["id"],
            r.get("payee") or "(unnamed)",
            float(r["amount"]) if r.get("amount") is not None else None,
            r.get("cadence"),
            r.get("start_date"),
            r.get("end_date"),
            r.get("category_id"),
            r.get("notes"),
            _dumps(r),
        ),
    )
    conn.commit()
    conn.close()


def list_recent_transactions(days: int = 30):
    conn = connect()
    rows = conn.execute(
        """SELECT * FROM transactions
           WHERE date(date) >= date('now', ?)
           ORDER BY date DESC""",
        (f'-{days} days',),
    ).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def list_recurring():
    conn = connect()
    rows = conn.execute("SELECT * FROM recurring_groups ORDER BY typical_amount DESC NULLS LAST").fetchall()
    conn.close()
    return [dict(r) for r in rows]


def save_analysis(kind: str, period_start: str, period_end: str, summary_md: str, structured_json: dict):
    conn = connect()
    conn.execute(
        """INSERT INTO analyses (kind, period_start, period_end, summary_md, structured_json)
        VALUES (?, ?, ?, ?, ?)""",
        (kind, period_start, period_end, summary_md, _dumps(structured_json)),
    )
    conn.commit()
    conn.close()