"""workshop knowledge — the part the assistant is supposed to *learn*, in its own
database, kept forever.

WHY THIS IS A SECOND DATABASE AND NOT FOUR MORE TABLES IN workshop.db
---------------------------------------------------------------------
`workshop.db` is a **record of work**: this project, these decisions, this log. It is
scoped to things that happen and then are over. `knowledge.db` is a **record of what
is true**, and it outlives every project in it — the standing instruction about how you
solder, the pinout you looked up in March, the fact that the good multimeter lives in
the third drawer. Those two have different lifetimes, different backup value, and
different blast radius when one is wrong, and keeping them in one file would mean the
first `DROP`/rebuild of a project store took the second with it.

**NOTHING HERE IS EVER PRUNED.** Every other store in this project has a retention
window — `doorway.py` keeps sightings 30 days, `digest-engine`'s archive keeps 400 —
because stale observations are worse than none. This is the opposite kind of data: the
whole point is that you tell it once. A retention policy here would be a policy of
forgetting the thing the feature exists to remember, so there is deliberately no
cutoff, no cleanup pass, and no `created_at <` anywhere in this file.

THE FOUR TABLES, AND WHY EACH IS SEPARATE
------------------------------------------
They are all "things the assistant should know", but they are retrieved by different
keys, and that is what makes them different tables rather than one `facts` table with
a `type` column:

- `workflow_notes` — retrieved by **activity**. Standing instructions: "when I'm
  soldering, always X". Surfaced whether or not anyone asked.
- `facts`         — retrieved by **keyword**. Specifics: device specs, URLs, part
  numbers. Surfaced when the subject comes up.
- `project_knowledge` — retrieved by **project**. What was learned *about this build*
  that outlives its log entries.
- `hardware`      — retrieved by **what you own**. Where a thing is stored, and what
  it is currently promised to.

`GET /context` is what ties them together and is the endpoint that makes this a
learning system rather than four lists: given an activity, a subject and a project, it
returns everything that applies, so the assistant is told the standing considerations
instead of being reminded of them.

HARDWARE IS HERE, NOT IN workshop.db
-------------------------------------
An earlier cut had a `parts` table in the project store. That was a second inventory,
and two inventories is exactly the failure this repo's own docs keep warning about —
the one you didn't update becomes a lie, and you find out by buying a part you already
own. What you own is knowledge (durable, yours, survives every project); what a project
*needs* is a claim on it, which is the `project_slug` column here.
"""

from __future__ import annotations

import json
import logging
import os
import re
import sqlite3
from datetime import datetime, timezone
from pathlib import Path

LOG = logging.getLogger("workshop.knowledge")

DB_PATH = Path(os.environ.get("WORKSHOP_KNOWLEDGE_DB_PATH", "/data/workshop-knowledge.db"))

# What a piece of hardware can be. The middle two are the distinction that earns this
# column, and conflating them is the mistake worth spending four lines to avoid:
#
#   available — on the shelf, unpromised. Fair game.
#   assigned  — RESERVED for a project. Still physically on the shelf, still something
#               you could pick up, but spoken for. This is "I plan to use it for that".
#   in_use    — ACTUALLY INSTALLED and working somewhere. Getting it back means taking
#               something apart, and probably means that something stops working.
#   retired   — dead, sold, or given away. Kept rather than deleted so "didn't I have
#               one of those?" has an answer other than silence.
#
# Why they are not one "unavailable": the question you ask at 23:00 is "can I use this
# right now?", and "it's reserved for the NAS I haven't started" and "it's in the NAS,
# which is serving the house" are wildly different answers. The first is a decision you
# can revisit in a second; the second is an evening's work and an outage.
HARDWARE_STATUSES = ("available", "assigned", "in_use", "retired")

_SPLIT_RE = re.compile(r"[,;]+")


def _now() -> str:
    return datetime.now(timezone.utc).isoformat().replace("+00:00", "Z")


def _keywords(value) -> str:
    """Keywords stored as a comma-separated, lowercased, de-duplicated string.

    Not a join table. A join table is the correct schema and the wrong tool here: this
    is a single-household knowledge base where the largest realistic query is "does any
    row mention 'esp32'", and a LIKE against a normalised string answers that without
    three tables and a migration. Lowercasing on write is what makes the match
    case-insensitive without a function index.
    """
    if isinstance(value, str):
        parts = _SPLIT_RE.split(value)
    elif isinstance(value, (list, tuple)):
        parts = [str(v) for v in value]
    else:
        return ""
    seen: list[str] = []
    for part in parts:
        word = " ".join(str(part).strip().lower().split())
        if word and word not in seen:
            seen.append(word)
    return ",".join(seen)


def _db() -> sqlite3.Connection:
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(DB_PATH, timeout=10)
    conn.row_factory = sqlite3.Row
    return conn


def init_db() -> None:
    with _db() as conn:
        conn.executescript(
            """
            -- 1. THINGS TO ALWAYS NOTE ABOUT HOW I WORK -------------------------
            -- Standing instructions tied to an activity, so the same considerations
            -- do not have to be repeated every session. This is the table that stops
            -- the assistant being told twice.
            CREATE TABLE IF NOT EXISTS workflow_notes (
                id INTEGER PRIMARY KEY,
                -- The activity this applies to: 'soldering', 'pcb-design', 'laptop
                -- repair'. The literal '*' means ALWAYS — advice that is true of every
                -- session, which is deliberately a value rather than a second table so
                -- that "always" is retrieved by the same query as everything else.
                activity TEXT NOT NULL,
                instruction TEXT NOT NULL,
                -- Why the instruction exists. Optional, and the field most worth
                -- filling: an instruction whose reason is recorded can be re-evaluated
                -- when circumstances change, and one without a reason gets followed
                -- forever or dropped for the wrong reasons.
                why TEXT,
                -- 1 = mention it every time; 2 = mention when relevant. Two levels on
                -- purpose. A five-point scale invites tuning that nobody ever does, and
                -- the only distinction that changes behaviour is "always say this" vs
                -- "have this ready".
                importance INTEGER NOT NULL DEFAULT 2,
                keywords TEXT NOT NULL DEFAULT '',
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            );

            -- 2. SPECIFIC THINGS, FOUND BY KEYWORD ------------------------------
            -- Device specs, URLs, part numbers, "the NAS is 192.168.30.40".
            CREATE TABLE IF NOT EXISTS facts (
                id INTEGER PRIMARY KEY,
                subject TEXT NOT NULL,
                body TEXT NOT NULL,
                -- Where it came from. Same rule as the project store's parts: a fact
                -- with no source is a fact somebody typed, which is fine and is
                -- recorded as such. What must never happen is a spec arriving here
                -- from a model's memory wearing a URL it did not read.
                source_url TEXT,
                keywords TEXT NOT NULL DEFAULT '',
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            );

            -- 3. WHAT WAS LEARNED ABOUT A PROJECT -------------------------------
            -- Distinct from workshop.db's `notes`, and the distinction is lifetime:
            -- a note is the running log of a session ("board arrived, started on the
            -- PSU"), this is what remains true afterwards ("this board's BIOS needs
            -- CSM off or the HBA won't post"). The log is disposable; this is not.
            CREATE TABLE IF NOT EXISTS project_knowledge (
                id INTEGER PRIMARY KEY,
                -- The project's slug in workshop.db. Deliberately NOT a foreign key —
                -- separate database, and more importantly the knowledge should survive
                -- the project being deleted. What you learned building the NAS is still
                -- true when the NAS is gone.
                project_slug TEXT NOT NULL,
                body TEXT NOT NULL,
                keywords TEXT NOT NULL DEFAULT '',
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            );

            -- 4. WHAT I OWN, AND WHERE IT IS ------------------------------------
            CREATE TABLE IF NOT EXISTS hardware (
                id INTEGER PRIMARY KEY,
                -- What is printed on it — the label-first principle from
                -- docs/workshop-assistant.md, in the schema.
                designation TEXT NOT NULL,
                kind TEXT,
                quantity INTEGER NOT NULL DEFAULT 1,
                -- WHERE IT IS PHYSICALLY. Free text on purpose: "third drawer, blue
                -- box", "under the bench", "lent to Linus". Any structure imposed here
                -- would be a structure somebody has to maintain, and the value is
                -- entirely in it being written down at all.
                storage_location TEXT,
                status TEXT NOT NULL DEFAULT 'available',
                -- The claim. Setting this is what "I plan to use it for that" means,
                -- and it is why you can ask what is already spoken for before ordering
                -- more. Nulled when it goes back to available.
                project_slug TEXT,
                specs TEXT,
                source_url TEXT,
                notes TEXT,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            );

            CREATE INDEX IF NOT EXISTS workflow_by_activity ON workflow_notes (activity);
            CREATE INDEX IF NOT EXISTS knowledge_by_project ON project_knowledge (project_slug);
            CREATE INDEX IF NOT EXISTS hardware_by_status ON hardware (status);
            CREATE INDEX IF NOT EXISTS hardware_by_project ON hardware (project_slug);
            """
        )


def _row(row) -> dict:
    return {k: row[k] for k in row.keys()}


def _like(term: str) -> str:
    return f"%{term.strip().lower()}%"


# --- 1. workflow notes ------------------------------------------------------------
def list_workflow_notes(activity: str = "") -> dict:
    """Notes for an activity, plus the always-notes. Always-first, then by importance.

    An empty activity returns everything, because "show me what you think you know
    about how I work" has to be answerable — a knowledge base you cannot audit is one
    you stop trusting the moment it says something odd.
    """
    with _db() as conn:
        if activity:
            rows = conn.execute(
                "SELECT * FROM workflow_notes WHERE activity = '*' OR activity = ? OR keywords LIKE ? "
                "ORDER BY (activity = '*') DESC, importance ASC, id ASC",
                (activity.strip().lower(), _like(activity)),
            ).fetchall()
        else:
            rows = conn.execute(
                "SELECT * FROM workflow_notes ORDER BY (activity = '*') DESC, importance ASC, id ASC"
            ).fetchall()
    return {"workflow_notes": [_row(r) for r in rows]}


def add_workflow_note(payload: dict) -> dict:
    instruction = str(payload.get("instruction") or "").strip()
    if not instruction:
        return {"ok": False, "reason": "bad_field", "message": "'instruction' is required."}
    activity = str(payload.get("activity") or "*").strip().lower() or "*"
    try:
        importance = int(payload.get("importance", 2))
    except (TypeError, ValueError):
        importance = 2
    importance = 1 if importance <= 1 else 2

    with _db() as conn:
        # A duplicate instruction for the same activity is almost always somebody
        # telling it the same thing twice — which is the exact problem this table
        # exists to solve, so it updates rather than accumulating near-identical rows.
        existing = conn.execute(
            "SELECT id FROM workflow_notes WHERE activity = ? AND lower(instruction) = lower(?)",
            (activity, instruction),
        ).fetchone()
        if existing:
            conn.execute(
                "UPDATE workflow_notes SET why = COALESCE(?, why), importance = ?, keywords = ?, "
                "updated_at = ? WHERE id = ?",
                (str(payload.get("why") or "").strip() or None, importance,
                 _keywords(payload.get("keywords")), _now(), existing["id"]),
            )
            return {"ok": True, "id": existing["id"], "updated": True}
        cur = conn.execute(
            "INSERT INTO workflow_notes (activity, instruction, why, importance, keywords, created_at, "
            "updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)",
            (activity, instruction, str(payload.get("why") or "").strip() or None, importance,
             _keywords(payload.get("keywords")), _now(), _now()),
        )
    return {"ok": True, "id": cur.lastrowid, "updated": False}


# --- 2. facts ---------------------------------------------------------------------
def list_facts(query: str = "") -> dict:
    with _db() as conn:
        if query:
            rows = conn.execute(
                "SELECT * FROM facts WHERE lower(subject) LIKE ? OR keywords LIKE ? OR lower(body) LIKE ? "
                "ORDER BY subject",
                (_like(query), _like(query), _like(query)),
            ).fetchall()
        else:
            rows = conn.execute("SELECT * FROM facts ORDER BY subject").fetchall()
    return {"facts": [_row(r) for r in rows]}


def add_fact(payload: dict) -> dict:
    subject = str(payload.get("subject") or "").strip()
    body = str(payload.get("body") or "").strip()
    if not subject or not body:
        return {"ok": False, "reason": "bad_field", "message": "'subject' and 'body' are both required."}
    body_value = body
    if isinstance(payload.get("body"), (dict, list)):
        body_value = json.dumps(payload["body"])

    with _db() as conn:
        existing = conn.execute("SELECT id FROM facts WHERE lower(subject) = lower(?)", (subject,)).fetchone()
        if existing:
            # Facts are corrected far more often than they are duplicated — a spec you
            # looked up again is usually a spec you got wrong the first time.
            conn.execute(
                "UPDATE facts SET body = ?, source_url = ?, keywords = ?, updated_at = ? WHERE id = ?",
                (body_value, str(payload.get("source_url") or "").strip() or None,
                 _keywords(payload.get("keywords")), _now(), existing["id"]),
            )
            return {"ok": True, "id": existing["id"], "updated": True}
        cur = conn.execute(
            "INSERT INTO facts (subject, body, source_url, keywords, created_at, updated_at) "
            "VALUES (?, ?, ?, ?, ?, ?)",
            (subject, body_value, str(payload.get("source_url") or "").strip() or None,
             _keywords(payload.get("keywords")), _now(), _now()),
        )
    return {"ok": True, "id": cur.lastrowid, "updated": False}


# --- 3. project knowledge ---------------------------------------------------------
def list_project_knowledge(project_slug: str = "", query: str = "") -> dict:
    clauses, params = [], []
    if project_slug:
        clauses.append("project_slug = ?")
        params.append(project_slug.strip().lower())
    if query:
        clauses.append("(lower(body) LIKE ? OR keywords LIKE ?)")
        params.extend([_like(query), _like(query)])
    where = f"WHERE {' AND '.join(clauses)}" if clauses else ""
    with _db() as conn:
        rows = conn.execute(
            f"SELECT * FROM project_knowledge {where} ORDER BY project_slug, id DESC", params
        ).fetchall()
    return {"project_knowledge": [_row(r) for r in rows]}


def add_project_knowledge(project_slug: str, payload: dict) -> dict:
    body = str(payload.get("body") or "").strip()
    if not body:
        return {"ok": False, "reason": "bad_field", "message": "'body' is required."}
    with _db() as conn:
        cur = conn.execute(
            "INSERT INTO project_knowledge (project_slug, body, keywords, created_at, updated_at) "
            "VALUES (?, ?, ?, ?, ?)",
            (project_slug.strip().lower(), body, _keywords(payload.get("keywords")), _now(), _now()),
        )
    return {"ok": True, "id": cur.lastrowid}


# --- 4. hardware inventory --------------------------------------------------------
def list_hardware(query: str = "", status: str = "", project_slug: str = "") -> dict:
    clauses, params = [], []
    if query:
        clauses.append("(lower(designation) LIKE ? OR lower(COALESCE(kind,'')) LIKE ? "
                       "OR lower(COALESCE(storage_location,'')) LIKE ?)")
        params.extend([_like(query), _like(query), _like(query)])
    if status:
        clauses.append("status = ?")
        params.append(status.strip().lower())
    if project_slug:
        clauses.append("project_slug = ?")
        params.append(project_slug.strip().lower())
    where = f"WHERE {' AND '.join(clauses)}" if clauses else ""
    with _db() as conn:
        rows = conn.execute(
            f"SELECT * FROM hardware {where} ORDER BY status, designation", params
        ).fetchall()
    return {"hardware": [_row(r) for r in rows]}


def add_hardware(payload: dict) -> dict:
    designation = str(payload.get("designation") or "").strip()
    if not designation:
        return {
            "ok": False,
            "reason": "bad_field",
            "message": "'designation' is required — what is printed on the thing.",
        }
    status = str(payload.get("status") or "").strip().lower()
    project_slug = str(payload.get("project_slug") or "").strip().lower()
    # Naming a project IS the assignment. Requiring a separate status field to agree
    # would be a second thing to get wrong, so the two are derived from each other.
    if not status:
        status = "assigned" if project_slug else "available"
    if status not in HARDWARE_STATUSES:
        return {"ok": False, "reason": "bad_field",
                "message": f"status must be one of {', '.join(HARDWARE_STATUSES)}."}
    try:
        quantity = max(0, int(payload.get("quantity", 1)))
    except (TypeError, ValueError):
        quantity = 1
    specs = payload.get("specs")

    with _db() as conn:
        cur = conn.execute(
            "INSERT INTO hardware (designation, kind, quantity, storage_location, status, project_slug, "
            "specs, source_url, notes, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (
                designation,
                str(payload.get("kind") or "").strip() or None,
                quantity,
                str(payload.get("storage_location") or "").strip() or None,
                status,
                project_slug or None,
                json.dumps(specs) if isinstance(specs, (dict, list)) else (specs or None),
                str(payload.get("source_url") or "").strip() or None,
                str(payload.get("notes") or "").strip() or None,
                _now(), _now(),
            ),
        )
    return {"ok": True, "id": cur.lastrowid}


def update_hardware(hardware_id: int, payload: dict) -> dict:
    """Edit a piece of hardware — including the one edit that matters, assigning it.

    `{"project_slug": "nas-build"}` moves it to `assigned` on its own; an explicit
    `{"project_slug": null}` releases it back to `available`. That coupling is
    deliberate: an item assigned to a project while still reading `available` is how an
    inventory starts lying, and there is no case where you want those two apart.

    **`in_use` is never overwritten by a re-assignment**, which is the one exception
    and the reason the statuses are distinct at all. Naming the project of something
    that is currently installed and working means "this is what it's in", not "please
    demote it to reserved" — and silently downgrading it would turn a thing you'd have
    to unscrew into a thing the list says you can just take.
    """
    updates: list[tuple[str, object]] = []

    if "project_slug" in payload:
        slug = str(payload.get("project_slug") or "").strip().lower()
        updates.append(("project_slug", slug or None))
        if "status" not in payload:
            with _db() as conn:
                row = conn.execute("SELECT status FROM hardware WHERE id = ?", (hardware_id,)).fetchone()
            currently_in_use = row is not None and row["status"] == "in_use"
            if not (currently_in_use and slug):
                updates.append(("status", "assigned" if slug else "available"))
    if "status" in payload:
        status = str(payload.get("status") or "").strip().lower()
        if status not in HARDWARE_STATUSES:
            return {"ok": False, "reason": "bad_field",
                    "message": f"status must be one of {', '.join(HARDWARE_STATUSES)}."}
        updates.append(("status", status))
        # Releasing something must not leave a stale claim behind.
        if status == "available" and "project_slug" not in payload:
            updates.append(("project_slug", None))
    for field in ("designation", "kind", "storage_location", "source_url", "notes"):
        if field in payload:
            value = str(payload[field] or "").strip()
            if field == "designation" and not value:
                return {"ok": False, "reason": "bad_field", "message": "'designation' cannot be empty."}
            updates.append((field, value or None))
    if "quantity" in payload:
        try:
            updates.append(("quantity", max(0, int(payload["quantity"]))))
        except (TypeError, ValueError):
            return {"ok": False, "reason": "bad_field", "message": "'quantity' must be a number."}
    if "specs" in payload:
        specs = payload["specs"]
        updates.append(("specs", json.dumps(specs) if isinstance(specs, (dict, list)) else (specs or None)))

    if not updates:
        return {"ok": False, "reason": "bad_field", "message": "Nothing to update."}

    with _db() as conn:
        if conn.execute("SELECT 1 FROM hardware WHERE id = ?", (hardware_id,)).fetchone() is None:
            return {"ok": False, "reason": "no_such_item", "message": "No hardware with that id."}
        for column, value in updates:
            conn.execute(f"UPDATE hardware SET {column} = ? WHERE id = ?", (value, hardware_id))
        conn.execute("UPDATE hardware SET updated_at = ? WHERE id = ?", (_now(), hardware_id))
        row = conn.execute("SELECT * FROM hardware WHERE id = ?", (hardware_id,)).fetchone()
    return {"ok": True, "hardware": _row(row)}


def delete_row(table: str, row_id: int) -> dict:
    """Corrections. The only deletion path, and it is per-row on purpose.

    "No retention period" means nothing expires by itself; it does not mean nothing is
    ever wrong. A knowledge base you cannot correct becomes one you route around.
    """
    if table not in ("workflow_notes", "facts", "project_knowledge", "hardware"):
        return {"ok": False, "reason": "bad_field", "message": "Unknown table."}
    with _db() as conn:
        cur = conn.execute(f"DELETE FROM {table} WHERE id = ?", (row_id,))
    if cur.rowcount == 0:
        return {"ok": False, "reason": "no_such_item", "message": "Nothing with that id."}
    return {"ok": True}


# --- the payoff -------------------------------------------------------------------
def context(activity: str = "", query: str = "", project_slug: str = "") -> dict:
    """Everything that applies right now, in one call.

    This is the endpoint that makes the four tables a memory rather than four lists.
    An assistant starting a conversation asks it once with whatever it knows —
    the room's activity, the subject at hand, the open project — and gets the standing
    instructions it would otherwise have to be told again, the specifics it would
    otherwise look up, and what hardware is already promised elsewhere.

    Everything is best-effort and additive: an unknown activity returns the always-
    notes rather than nothing, because "I have no idea what you're doing" is not a
    reason to forget how you like to work.
    """
    result = {
        "activity": activity,
        "project_slug": project_slug,
        "workflow_notes": list_workflow_notes(activity)["workflow_notes"],
        "facts": list_facts(query)["facts"] if query else [],
        "project_knowledge": (
            list_project_knowledge(project_slug)["project_knowledge"] if project_slug else []
        ),
        "hardware": [],
    }
    if project_slug:
        result["hardware"] = list_hardware(project_slug=project_slug)["hardware"]
    elif query:
        result["hardware"] = list_hardware(query=query)["hardware"]
    return result
