"""
The single local database on the Mac Studio. SQLite, one file, no server.

Phase 1 uses two of the tables: the cars entered by hand, and the assessment
results from each run. The other tables are created now so the shape is ready
for later phases (learned model figures, daily stock snapshots, sales history),
but nothing writes to them yet.

Everything stays local. Section 2 rule 5.
"""

import sqlite3
import os
import datetime

DB_DIR = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), "data")
DB_PATH = os.path.join(DB_DIR, "bidbrain.db")


SCHEMA = """
CREATE TABLE IF NOT EXISTS cars (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    reg TEXT,
    make TEXT,
    model TEXT,
    derivative TEXT,
    year INTEGER,
    mileage INTEGER,
    owners INTEGER,
    grade INTEGER,
    reserve INTEGER,
    cap_clean INTEGER,
    distance_miles REAL,
    engine TEXT,
    service_history TEXT,
    vat_qualifying INTEGER,
    glass_retail INTEGER,
    cazana_retail INTEGER,
    photo_url TEXT,
    listing_url TEXT,
    source TEXT,
    actually_paid INTEGER,
    loaded_at TEXT
);

CREATE TABLE IF NOT EXISTS assessments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    run_at TEXT,
    reg TEXT,
    status TEXT,
    governing_source TEXT,
    governing_value INTEGER,
    max_bid INTEGER,
    reasons TEXT,
    flags TEXT
);

-- Ready for Phase 3, not written to yet.
CREATE TABLE IF NOT EXISTS learned_models (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    make TEXT,
    model TEXT,
    avg_days_to_sell REAL,
    avg_prep_total INTEGER,
    prep_split TEXT,
    spread_held INTEGER,
    updated_at TEXT
);

CREATE TABLE IF NOT EXISTS stock_snapshots (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    snapshot_date TEXT,
    make TEXT,
    model TEXT,
    in_stock_count INTEGER
);

CREATE TABLE IF NOT EXISTS sales_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    stock_number TEXT,
    make TEXT,
    model TEXT,
    sold_date TEXT,
    days_to_sell INTEGER,
    margin INTEGER,
    prep_total INTEGER,
    source TEXT
);

-- Every car seen on each platform each day, so repeat appearances can be
-- flagged. Identity is the registration. One row per car per platform per
-- sale date.
CREATE TABLE IF NOT EXISTS sightings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    reg TEXT,
    platform TEXT,
    sale_date TEXT,
    make TEXT,
    model TEXT,
    derivative TEXT,
    year INTEGER,
    listing_url TEXT,
    recorded_at TEXT,
    UNIQUE(reg, platform, sale_date)
);

-- Cars Steven has hidden with a reason, suppressed from future daily lists.
-- One row per registration. active 1 means hidden, reviewed 0 means the reason
-- has not yet been turned into a rule.
CREATE TABLE IF NOT EXISTS hidden_cars (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    reg TEXT NOT NULL,
    reason TEXT,
    make TEXT,
    model TEXT,
    name TEXT,
    hidden_at TEXT,
    active INTEGER DEFAULT 1,
    reviewed INTEGER DEFAULT 0
);
CREATE UNIQUE INDEX IF NOT EXISTS ix_hidden_reg ON hidden_cars(reg);

-- Steven's own daily bid shortlist. The app never bids, this is his pick and a
-- learning signal. One row per car per sale date.
CREATE TABLE IF NOT EXISTS bids (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    reg TEXT NOT NULL,
    sale_date TEXT NOT NULL,
    make TEXT,
    model TEXT,
    name TEXT,
    max_bid INTEGER,
    reserve INTEGER,
    created_at TEXT,
    active INTEGER DEFAULT 1,
    UNIQUE(reg, sale_date)
);
"""


def connect(path: str = DB_PATH) -> sqlite3.Connection:
    os.makedirs(os.path.dirname(path), exist_ok=True)
    # timeout lets a brief write lock from the threaded server retry rather than
    # error, since the interactive page can write while a run is reading.
    conn = sqlite3.connect(path, timeout=5)
    conn.row_factory = sqlite3.Row
    return conn


def init_db(path: str = DB_PATH) -> None:
    conn = connect(path)
    try:
        conn.executescript(SCHEMA)
        conn.commit()
    finally:
        conn.close()


def _now() -> str:
    return datetime.datetime.now().isoformat(timespec="seconds")


def replace_cars(cars, path: str = DB_PATH) -> None:
    """Phase 1 loads a fresh hand entered set each run, so clear and reload."""
    conn = connect(path)
    try:
        conn.execute("DELETE FROM cars")
        now = _now()
        for c in cars:
            conn.execute(
                """INSERT INTO cars
                   (reg, make, model, derivative, year, mileage, owners, grade,
                    reserve, cap_clean, distance_miles, engine, service_history,
                    vat_qualifying, glass_retail, cazana_retail, photo_url,
                    listing_url, source, actually_paid, loaded_at)
                   VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
                (c.reg, c.make, c.model, c.derivative, c.year, c.mileage,
                 c.owners, c.grade, c.reserve, c.cap_clean, c.distance_miles,
                 c.engine, c.service_history, 1 if c.vat_qualifying else 0,
                 c.glass_retail, c.cazana_retail, c.photo_url, c.listing_url,
                 c.source, c.actually_paid, now),
            )
        conn.commit()
    finally:
        conn.close()


def _norm_reg(reg) -> str:
    return "".join(str(reg or "").upper().split())


def record_sightings(cars, platform: str, sale_date: str, path: str = DB_PATH) -> None:
    """Log every car seen on a platform for a given sale date. Cars without a
    readable registration cannot be tracked for repeats, so they are skipped.
    One row per car per platform per sale date, repeats on the same day ignored."""
    conn = connect(path)
    try:
        now = _now()
        for c in cars:
            reg = _norm_reg(getattr(c, "reg", ""))
            if not reg:
                continue
            conn.execute(
                """INSERT OR IGNORE INTO sightings
                   (reg, platform, sale_date, make, model, derivative, year,
                    listing_url, recorded_at)
                   VALUES (?,?,?,?,?,?,?,?,?)""",
                (reg, platform, sale_date, c.make, c.model, c.derivative,
                 c.year, c.listing_url, now),
            )
        conn.commit()
    finally:
        conn.close()


def repeat_summary(reg, sale_date: str, window_days: int = 30, path: str = DB_PATH) -> dict:
    """How many previous times this registration was seen on each platform in
    the window before sale_date, plus whether it is on more than one platform
    on sale_date itself.

    Returns: {"previous": {platform: count}, "same_day_platforms": [..]}.
    Counts distinct earlier sale dates, so two sightings on one day count once.
    """
    reg = _norm_reg(reg)
    out = {"previous": {}, "same_day_platforms": []}
    if not reg:
        return out
    conn = connect(path)
    try:
        # Previous distinct sale dates per platform, within the window, before today.
        rows = conn.execute(
            """SELECT platform, COUNT(DISTINCT sale_date) AS n
               FROM sightings
               WHERE reg = ?
                 AND sale_date < ?
                 AND sale_date >= date(?, ?)
               GROUP BY platform""",
            (reg, sale_date, sale_date, f"-{int(window_days)} days"),
        ).fetchall()
        for r in rows:
            if r["n"]:
                out["previous"][r["platform"]] = r["n"]

        # Platforms this car is on for the sale date itself.
        same = conn.execute(
            """SELECT DISTINCT platform FROM sightings
               WHERE reg = ? AND sale_date = ?""",
            (reg, sale_date),
        ).fetchall()
        out["same_day_platforms"] = sorted(r["platform"] for r in same)
        return out
    finally:
        conn.close()


def repeat_flag_lines(summary: dict) -> list:
    """Turn a repeat_summary into plain card lines, house style, no hyphens.
    Returns an empty list when there is nothing to flag."""
    lines = []
    prev = summary.get("previous", {})
    if prev:
        parts = []
        for platform in sorted(prev):
            n = prev[platform]
            parts.append(f"{n} previous {platform} {'sale' if n == 1 else 'sales'}")
        lines.append("Seen before within 30 days: " + ", ".join(parts) + ".")
    same = summary.get("same_day_platforms", [])
    if len(same) > 1:
        lines.append("In both " + " and ".join(same) + " today.")
    return lines


def hide_car(reg, reason="", make="", model="", name="", path: str = DB_PATH) -> None:
    """Hide a car from all future daily lists, with a reason. Upserts one row
    per registration and resets reviewed so the new reason is surfaced again."""
    reg = _norm_reg(reg)
    if not reg:
        return
    conn = connect(path)
    try:
        conn.execute(
            """INSERT INTO hidden_cars (reg, reason, make, model, name, hidden_at, active, reviewed)
               VALUES (?,?,?,?,?,?,1,0)
               ON CONFLICT(reg) DO UPDATE SET
                 reason=excluded.reason, make=excluded.make, model=excluded.model,
                 name=excluded.name, hidden_at=excluded.hidden_at, active=1, reviewed=0""",
            (reg, reason, make, model, name, _now()),
        )
        conn.commit()
    finally:
        conn.close()


def unhide_car(reg, path: str = DB_PATH) -> None:
    reg = _norm_reg(reg)
    conn = connect(path)
    try:
        conn.execute("UPDATE hidden_cars SET active=0 WHERE reg=?", (reg,))
        conn.commit()
    finally:
        conn.close()


def hidden_regs(path: str = DB_PATH) -> set:
    """The set of registrations currently hidden."""
    conn = connect(path)
    try:
        rows = conn.execute("SELECT reg FROM hidden_cars WHERE active=1").fetchall()
        return {r["reg"] for r in rows}
    finally:
        conn.close()


def hidden_notes(only_new=True, path: str = DB_PATH) -> list:
    """Active hidden cars that have a reason, for the assistant to mine into
    rules. only_new limits to those not yet marked reviewed."""
    conn = connect(path)
    try:
        sql = ("SELECT reg, reason, make, model, name, hidden_at FROM hidden_cars "
               "WHERE active=1 AND reason IS NOT NULL AND reason != ''")
        if only_new:
            sql += " AND reviewed=0"
        sql += " ORDER BY hidden_at"
        return [dict(r) for r in conn.execute(sql).fetchall()]
    finally:
        conn.close()


def mark_notes_reviewed(regs, path: str = DB_PATH) -> None:
    conn = connect(path)
    try:
        for reg in regs:
            conn.execute("UPDATE hidden_cars SET reviewed=1 WHERE reg=?", (_norm_reg(reg),))
        conn.commit()
    finally:
        conn.close()


def set_bid(reg, sale_date, on=True, make="", model="", name="",
            max_bid=None, reserve=None, path: str = DB_PATH) -> None:
    """Mark or unmark a car on Steven's bid shortlist for a sale date."""
    reg = _norm_reg(reg)
    if not reg:
        return
    conn = connect(path)
    try:
        conn.execute(
            """INSERT INTO bids (reg, sale_date, make, model, name, max_bid, reserve, created_at, active)
               VALUES (?,?,?,?,?,?,?,?,?)
               ON CONFLICT(reg, sale_date) DO UPDATE SET
                 active=excluded.active, make=excluded.make, model=excluded.model,
                 name=excluded.name, max_bid=excluded.max_bid, reserve=excluded.reserve,
                 created_at=excluded.created_at""",
            (reg, sale_date, make, model, name, max_bid, reserve, _now(), 1 if on else 0),
        )
        conn.commit()
    finally:
        conn.close()


def bids_for(sale_date, path: str = DB_PATH) -> set:
    """The set of registrations on the bid shortlist for a sale date."""
    conn = connect(path)
    try:
        rows = conn.execute(
            "SELECT reg FROM bids WHERE sale_date=? AND active=1", (sale_date,)
        ).fetchall()
        return {r["reg"] for r in rows}
    finally:
        conn.close()


def save_assessments(assessments, path: str = DB_PATH) -> None:
    conn = connect(path)
    try:
        now = _now()
        for a in assessments:
            gv = a.pricing.governing_value if a.pricing else None
            gs = a.pricing.governing_source if a.pricing else None
            mb = a.pricing.max_bid if a.pricing else None
            conn.execute(
                """INSERT INTO assessments
                   (run_at, reg, status, governing_source, governing_value,
                    max_bid, reasons, flags)
                   VALUES (?,?,?,?,?,?,?,?)""",
                (now, a.car.reg, a.status, gs, gv, mb,
                 " | ".join(a.reasons), " | ".join(a.flags)),
            )
        conn.commit()
    finally:
        conn.close()