"""
SQLite models for Kanbante — lightweight local state.

Local state only stores what Gitea does not: the column of each issue,
its position inside the column, and per-project notes. The database path
honours the KANBANTE_DB_PATH environment variable (see app.config) —
that is the path the Dockerfile / docker-compose mount their volume on.

Migrations are additive and idempotent: init_db() creates fresh schemas
and upgrades older databases in place (position column, notes
updated_at, cleanup of rows carrying an unknown column name).
"""

import json
import os
import sqlite3
from contextlib import contextmanager
from typing import Iterator

KANBAN_COLUMNS = ["backlog", "todo", "in_progress", "qa", "review", "done"]


def db_path() -> str:
    """Resolve the database path from the environment (per call)."""
    return os.path.expanduser(
        os.getenv("KANBANTE_DB_PATH", "~/.kanbante/kanbante.db")
    )


@contextmanager
def get_db() -> Iterator[sqlite3.Connection]:
    path = db_path()
    directory = os.path.dirname(path)
    if directory:
        os.makedirs(directory, exist_ok=True)
    conn = sqlite3.connect(path)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    try:
        yield conn
        conn.commit()
    finally:
        conn.close()


def init_db() -> None:
    with get_db() as conn:
        conn.executescript(
            """
            CREATE TABLE IF NOT EXISTS board_state (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                workspace_id INTEGER NOT NULL DEFAULT 1,
                connection_id INTEGER NOT NULL DEFAULT 1,
                repo_full_name TEXT NOT NULL,
                issue_number INTEGER NOT NULL,
                column_name TEXT NOT NULL DEFAULT 'backlog',
                position REAL NOT NULL DEFAULT 0,
                updated_at TEXT NOT NULL DEFAULT (datetime('now')),
                UNIQUE(workspace_id, connection_id, repo_full_name, issue_number)
            );

            CREATE TABLE IF NOT EXISTS project_notes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                workspace_id INTEGER NOT NULL DEFAULT 1,
                connection_id INTEGER NOT NULL DEFAULT 1,
                repo_full_name TEXT NOT NULL,
                note TEXT NOT NULL,
                created_at TEXT NOT NULL DEFAULT (datetime('now')),
                updated_at TEXT NOT NULL DEFAULT (datetime('now')),
                UNIQUE(workspace_id, connection_id, repo_full_name)
            );

            CREATE TABLE IF NOT EXISTS board_events (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                workspace_id INTEGER NOT NULL DEFAULT 1,
                connection_id INTEGER NOT NULL DEFAULT 1,
                repo_full_name TEXT NOT NULL,
                issue_number INTEGER NOT NULL,
                event_type TEXT NOT NULL,
                from_column TEXT,
                to_column TEXT,
                source TEXT NOT NULL DEFAULT 'ui',
                created_at TEXT NOT NULL DEFAULT (datetime('now'))
            );

            CREATE INDEX IF NOT EXISTS idx_board_repo
                ON board_state(repo_full_name, column_name);

            CREATE TABLE IF NOT EXISTS today_picks (
                pick_date TEXT NOT NULL,
                workspace_id INTEGER NOT NULL DEFAULT 1,
                connection_id INTEGER NOT NULL DEFAULT 1,
                repo_full_name TEXT NOT NULL,
                issue_number INTEGER NOT NULL,
                created_at TEXT NOT NULL DEFAULT (datetime('now')),
                PRIMARY KEY (pick_date, workspace_id, connection_id, repo_full_name, issue_number)
            );
            CREATE TABLE IF NOT EXISTS kv_store (
                key TEXT PRIMARY KEY,
                value TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS snapshots (
                key TEXT PRIMARY KEY,
                payload TEXT NOT NULL,
                updated_at TEXT NOT NULL DEFAULT (datetime('now'))
            );

            -- ── Multi-forges, utilisateurs, espaces (0.24.0) ──
            CREATE TABLE IF NOT EXISTS forge_connections (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                slug TEXT NOT NULL UNIQUE,
                kind TEXT NOT NULL DEFAULT 'gitea',
                name TEXT NOT NULL,
                base_url TEXT NOT NULL,
                token_encrypted TEXT NOT NULL DEFAULT '',
                account_login TEXT NOT NULL DEFAULT '',
                webhook_secret TEXT NOT NULL DEFAULT '',
                owner_user_id INTEGER,
                enabled INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL DEFAULT (datetime('now'))
            );

            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT NOT NULL UNIQUE,
                password_hash TEXT NOT NULL,
                role TEXT NOT NULL DEFAULT 'member',
                display_name TEXT NOT NULL DEFAULT '',
                draco_sub TEXT UNIQUE,
                active INTEGER NOT NULL DEFAULT 1,
                must_change_password INTEGER NOT NULL DEFAULT 0,
                created_at TEXT NOT NULL DEFAULT (datetime('now')),
                last_login_at TEXT
            );

            CREATE TABLE IF NOT EXISTS workspaces (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                slug TEXT NOT NULL UNIQUE,
                name TEXT NOT NULL,
                color TEXT NOT NULL DEFAULT '#7c5cfc',
                created_at TEXT NOT NULL DEFAULT (datetime('now'))
            );

            CREATE TABLE IF NOT EXISTS workspace_connections (
                workspace_id INTEGER NOT NULL,
                connection_id INTEGER NOT NULL,
                PRIMARY KEY (workspace_id, connection_id)
            );

            CREATE TABLE IF NOT EXISTS workspace_projects (
                workspace_id INTEGER NOT NULL,
                connection_id INTEGER NOT NULL,
                repo_full_name TEXT NOT NULL,
                PRIMARY KEY (workspace_id, connection_id, repo_full_name)
            );

            CREATE TABLE IF NOT EXISTS workspace_members (
                workspace_id INTEGER NOT NULL,
                user_id INTEGER NOT NULL,
                role TEXT NOT NULL DEFAULT 'member',
                PRIMARY KEY (workspace_id, user_id)
            );

            CREATE TABLE IF NOT EXISTS project_states (
                workspace_id INTEGER NOT NULL,
                connection_id INTEGER NOT NULL,
                repo_full_name TEXT NOT NULL,
                local_archived INTEGER NOT NULL DEFAULT 0,
                updated_at TEXT NOT NULL DEFAULT (datetime('now')),
                PRIMARY KEY (workspace_id, connection_id, repo_full_name)
            );

            CREATE TABLE IF NOT EXISTS admin_audit (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER,
                action TEXT NOT NULL,
                details TEXT NOT NULL DEFAULT '',
                created_at TEXT NOT NULL DEFAULT (datetime('now'))
            );

            CREATE INDEX IF NOT EXISTS idx_events_repo
                ON board_events(repo_full_name, issue_number, created_at);
            """
        )
        _migrate(conn)


def _migrate(conn: sqlite3.Connection) -> None:
    """Upgrade schemas created by Kanbante <= 0.1.0."""
    board_cols = [r[1] for r in conn.execute("PRAGMA table_info(board_state)")]
    if board_cols and "position" not in board_cols:
        conn.execute(
            "ALTER TABLE board_state ADD COLUMN position REAL NOT NULL DEFAULT 0"
        )
    notes_cols = [r[1] for r in conn.execute("PRAGMA table_info(project_notes)")]
    if notes_cols and "updated_at" not in notes_cols:
        # SQLite rejects non-constant defaults in ADD COLUMN: add the
        # column plainly, then backfill from created_at.
        conn.execute("ALTER TABLE project_notes ADD COLUMN updated_at TEXT")
        conn.execute(
            "UPDATE project_notes SET updated_at = created_at "
            "WHERE updated_at IS NULL"
        )
    _migrate_v24(conn)
    # Rows written under a column name that no longer exists used to crash
    # get_board_state() with a KeyError. Drop them once, here.
    placeholders = ",".join("?" for _ in KANBAN_COLUMNS)
    conn.execute(
        f"DELETE FROM board_state WHERE column_name NOT IN ({placeholders})",
        KANBAN_COLUMNS,
    )


def _migrate_v24(conn: sqlite3.Connection) -> None:
    """0.24.0: scope columns (workspace_id, connection_id).

    board_state / project_notes / today_picks carry UNIQUE
    constraints on repo_full_name alone — SQLite cannot alter a
    constraint, so legacy tables are rebuilt with the scoped
    uniqueness; existing rows land in scope (1, 1) — the legacy
    connection and the Principal workspace.
    """

    def _cols(table: str) -> list[str]:
        return [r[1] for r in conn.execute(f"PRAGMA table_info({table})")]

    if _cols("board_state") and "connection_id" not in _cols("board_state"):
        conn.executescript(
            """
            CREATE TABLE board_state_v24 (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                workspace_id INTEGER NOT NULL DEFAULT 1,
                connection_id INTEGER NOT NULL DEFAULT 1,
                repo_full_name TEXT NOT NULL,
                issue_number INTEGER NOT NULL,
                column_name TEXT NOT NULL DEFAULT 'backlog',
                position REAL NOT NULL DEFAULT 0,
                updated_at TEXT NOT NULL DEFAULT (datetime('now')),
                UNIQUE(workspace_id, connection_id, repo_full_name, issue_number)
            );
            INSERT INTO board_state_v24
                (id, workspace_id, connection_id, repo_full_name,
                 issue_number, column_name, position, updated_at)
            SELECT id, 1, 1, repo_full_name, issue_number, column_name,
                   position, updated_at FROM board_state;
            DROP TABLE board_state;
            ALTER TABLE board_state_v24 RENAME TO board_state;
            CREATE INDEX IF NOT EXISTS idx_board_repo
                ON board_state(repo_full_name, column_name);
            """
        )
    if _cols("project_notes") and "connection_id" not in _cols("project_notes"):
        conn.executescript(
            """
            CREATE TABLE project_notes_v24 (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                workspace_id INTEGER NOT NULL DEFAULT 1,
                connection_id INTEGER NOT NULL DEFAULT 1,
                repo_full_name TEXT NOT NULL,
                note TEXT NOT NULL,
                created_at TEXT NOT NULL DEFAULT (datetime('now')),
                updated_at TEXT NOT NULL DEFAULT (datetime('now')),
                UNIQUE(workspace_id, connection_id, repo_full_name)
            );
            INSERT INTO project_notes_v24
                (id, workspace_id, connection_id, repo_full_name, note,
                 created_at, updated_at)
            SELECT id, 1, 1, repo_full_name, note, created_at, updated_at
            FROM project_notes;
            DROP TABLE project_notes;
            ALTER TABLE project_notes_v24 RENAME TO project_notes;
            """
        )
    if _cols("today_picks") and "connection_id" not in _cols("today_picks"):
        conn.executescript(
            """
            CREATE TABLE today_picks_v24 (
                pick_date TEXT NOT NULL,
                workspace_id INTEGER NOT NULL DEFAULT 1,
                connection_id INTEGER NOT NULL DEFAULT 1,
                repo_full_name TEXT NOT NULL,
                issue_number INTEGER NOT NULL,
                created_at TEXT NOT NULL DEFAULT (datetime('now')),
                PRIMARY KEY (pick_date, workspace_id, connection_id,
                             repo_full_name, issue_number)
            );
            INSERT INTO today_picks_v24
                (pick_date, workspace_id, connection_id, repo_full_name,
                 issue_number, created_at)
            SELECT pick_date, 1, 1, repo_full_name, issue_number, created_at
            FROM today_picks;
            DROP TABLE today_picks;
            ALTER TABLE today_picks_v24 RENAME TO today_picks;
            """
        )
    if _cols("board_events") and "connection_id" not in _cols("board_events"):
        conn.execute(
            "ALTER TABLE board_events ADD COLUMN workspace_id "
            "INTEGER NOT NULL DEFAULT 1"
        )
        conn.execute(
            "ALTER TABLE board_events ADD COLUMN connection_id "
            "INTEGER NOT NULL DEFAULT 1"
        )


def ping_db() -> bool:
    """Cheap check used by /api/readiness."""
    try:
        with get_db() as conn:
            conn.execute("SELECT 1").fetchone()
        return True
    except Exception:
        return False


def get_board_state(
    repo_full_name: str, workspace_id: int = 1, connection_id: int = 1
) -> dict[str, list[dict]]:
    """Return board columns with their issues, ordered by position."""
    with get_db() as conn:
        rows = conn.execute(
            "SELECT issue_number, column_name, position, updated_at "
            "FROM board_state WHERE workspace_id = ? AND connection_id = ? "
            "AND repo_full_name = ? "
            "ORDER BY position ASC, issue_number ASC",
            (workspace_id, connection_id, repo_full_name),
        ).fetchall()

    columns: dict[str, list[dict]] = {col: [] for col in KANBAN_COLUMNS}
    for row in rows:
        column = row["column_name"]
        if column not in columns:
            # Defensive: never let one bad row break the whole board.
            continue
        columns[column].append(
            {
                "issue_number": row["issue_number"],
                "position": row["position"],
                "updated_at": row["updated_at"],
            }
        )
    return columns


def move_issue(
    repo_full_name: str,
    issue_number: int,
    to_column: str,
    workspace_id: int = 1,
    connection_id: int = 1,
) -> None:
    """Move an issue to a different column (appended at the end)."""
    if to_column not in KANBAN_COLUMNS:
        raise ValueError(f"Invalid column: {to_column}")
    with get_db() as conn:
        row = conn.execute(
            "SELECT COALESCE(MAX(position), -1) + 1 AS next_pos FROM board_state "
            "WHERE workspace_id = ? AND connection_id = ? "
            "AND repo_full_name = ? AND column_name = ?",
            (workspace_id, connection_id, repo_full_name, to_column),
        ).fetchone()
        conn.execute(
            """INSERT INTO board_state
                   (workspace_id, connection_id, repo_full_name, issue_number,
                    column_name, position, updated_at)
               VALUES (?, ?, ?, ?, ?, ?, datetime('now'))
               ON CONFLICT(workspace_id, connection_id, repo_full_name, issue_number)
               DO UPDATE SET column_name = excluded.column_name,
                             position = excluded.position,
                             updated_at = excluded.updated_at""",
            (workspace_id, connection_id, repo_full_name, issue_number,
             to_column, row["next_pos"]),
        )


def set_column_order(
    repo_full_name: str,
    column_name: str,
    ordered_issue_numbers: list[int],
    workspace_id: int = 1,
    connection_id: int = 1,
) -> None:
    """Persist the exact order of the cards in one column.

    Each listed card keeps (or gains) this column and its position becomes
    its index in the list. Used after a drag & drop so manual ordering
    survives reloads.
    """
    if column_name not in KANBAN_COLUMNS:
        raise ValueError(f"Invalid column: {column_name}")
    with get_db() as conn:
        for index, issue_number in enumerate(ordered_issue_numbers):
            conn.execute(
                """INSERT INTO board_state
                       (workspace_id, connection_id, repo_full_name,
                        issue_number, column_name, position, updated_at)
                   VALUES (?, ?, ?, ?, ?, ?, datetime('now'))
                   ON CONFLICT(workspace_id, connection_id, repo_full_name, issue_number)
                   DO UPDATE SET column_name = excluded.column_name,
                                 position = excluded.position,
                                 updated_at = excluded.updated_at""",
                (workspace_id, connection_id, repo_full_name,
                 issue_number, column_name, float(index)),
            )


def reconcile_board_state(
    repo_full_name: str,
    open_numbers: set[int],
    closed_numbers: set[int],
    workspace_id: int = 1,
    connection_id: int = 1,
) -> dict[str, int]:
    """Reconcile local board state with the truth in Gitea.

    - Issues closed in Gitea move to the "done" column.
    - Rows for issues that no longer exist in Gitea are deleted.
    Returns counters of what changed.
    """
    changed: dict = {"closed_to_done": 0, "purged": 0,
                     "closed_numbers": [], "purged_numbers": []}
    known = open_numbers | closed_numbers
    with get_db() as conn:
        rows = conn.execute(
            "SELECT issue_number, column_name FROM board_state "
            "WHERE workspace_id = ? AND connection_id = ? "
            "AND repo_full_name = ?",
            (workspace_id, connection_id, repo_full_name),
        ).fetchall()
        for row in rows:
            number = row["issue_number"]
            if number not in known:
                conn.execute(
                    "DELETE FROM board_state "
                    "WHERE workspace_id = ? AND connection_id = ? "
                    "AND repo_full_name = ? AND issue_number = ?",
                    (workspace_id, connection_id, repo_full_name, number),
                )
                changed["purged"] += 1
                changed["purged_numbers"].append(number)
            elif number in closed_numbers and row["column_name"] != "done":
                conn.execute(
                    "UPDATE board_state SET column_name = 'done', "
                    "updated_at = datetime('now') "
                    "WHERE workspace_id = ? AND connection_id = ? "
                    "AND repo_full_name = ? AND issue_number = ?",
                    (workspace_id, connection_id, repo_full_name, number),
                )
                changed["closed_to_done"] += 1
                changed["closed_numbers"].append(number)
    return changed


def get_picks(
    pick_date: str, workspace_id: int = 1, connection_id: int | None = None
) -> list[dict]:
    """The day's focus picks (issue #5), in pinning order."""
    with get_db() as conn:
        if connection_id is None:
            rows = conn.execute(
                "SELECT connection_id, repo_full_name, issue_number "
                "FROM today_picks WHERE pick_date = ? AND workspace_id = ? "
                "ORDER BY created_at, repo_full_name, issue_number",
                (pick_date, workspace_id),
            ).fetchall()
        else:
            rows = conn.execute(
                "SELECT connection_id, repo_full_name, issue_number "
                "FROM today_picks WHERE pick_date = ? AND workspace_id = ? "
                "AND connection_id = ? "
                "ORDER BY created_at, repo_full_name, issue_number",
                (pick_date, workspace_id, connection_id),
            ).fetchall()
    return [dict(row) for row in rows]


def add_pick(
    pick_date: str,
    repo_full_name: str,
    issue_number: int,
    workspace_id: int = 1,
    connection_id: int = 1,
) -> None:
    with get_db() as conn:
        conn.execute(
            "INSERT OR IGNORE INTO today_picks "
            "(pick_date, workspace_id, connection_id, repo_full_name, "
            "issue_number) VALUES (?, ?, ?, ?, ?)",
            (pick_date, workspace_id, connection_id, repo_full_name,
             issue_number),
        )


def remove_pick(
    pick_date: str,
    repo_full_name: str,
    issue_number: int,
    workspace_id: int = 1,
    connection_id: int = 1,
) -> None:
    with get_db() as conn:
        conn.execute(
            "DELETE FROM today_picks WHERE pick_date = ? "
            "AND workspace_id = ? AND connection_id = ? "
            "AND repo_full_name = ? AND issue_number = ?",
            (pick_date, workspace_id, connection_id, repo_full_name,
             issue_number),
        )


def get_kv(key: str) -> str | None:
    with get_db() as conn:
        row = conn.execute(
            "SELECT value FROM kv_store WHERE key = ?", (key,)
        ).fetchone()
    return row["value"] if row else None


def set_kv(key: str, value: str) -> None:
    with get_db() as conn:
        conn.execute(
            "INSERT INTO kv_store (key, value) VALUES (?, ?) "
            "ON CONFLICT(key) DO UPDATE SET value = excluded.value",
            (key, value),
        )


# ── Snapshots (chantier B, 0.22.0) ─────────────────────────
# Persisted copies of the Gitea list responses the pages read
# (repos, issues, milestones, starred). The client cache is
# hydrated from these rows at startup, so even the first page
# after a restart renders from local data while the background
# refresher re-fetches from Gitea.

def save_snapshot(key: str, payload) -> None:
    with get_db() as conn:
        conn.execute(
            "INSERT INTO snapshots (key, payload, updated_at) "
            "VALUES (?, ?, datetime('now')) "
            "ON CONFLICT(key) DO UPDATE SET payload = excluded.payload, "
            "updated_at = excluded.updated_at",
            (key, json.dumps(payload)),
        )


def load_snapshots() -> dict:
    """All snapshots as {key: (payload, updated_at_utc_str)}."""
    with get_db() as conn:
        rows = conn.execute(
            "SELECT key, payload, updated_at FROM snapshots"
        ).fetchall()
    out = {}
    for row in rows:
        try:
            out[row["key"]] = (json.loads(row["payload"]), row["updated_at"])
        except (ValueError, TypeError):
            continue
    return out


def latest_snapshot_at() -> str | None:
    """UTC timestamp (SQLite format) of the freshest snapshot."""
    with get_db() as conn:
        row = conn.execute(
            "SELECT MAX(updated_at) AS latest FROM snapshots"
        ).fetchone()
    return row["latest"] if row else None


# ── Event journal ──────────────────────────────────────────

EVENT_TYPES = ("created", "moved", "closed", "reopened", "commented", "rule")


def log_event(
    repo_full_name: str,
    issue_number: int,
    event_type: str,
    from_column: str | None = None,
    to_column: str | None = None,
    source: str = "ui",
    created_at: str | None = None,
    workspace_id: int = 1,
    connection_id: int = 1,
) -> None:
    """Append one entry to the board's event journal.

    The journal is append-only: it is the memory board_state lacks
    (time-in-column, activity feed, throughput / cycle-time metrics).
    """
    if event_type not in EVENT_TYPES:
        raise ValueError(f"Invalid event type: {event_type}")
    with get_db() as conn:
        if created_at:
            conn.execute(
                "INSERT INTO board_events (workspace_id, connection_id, "
                "repo_full_name, issue_number, event_type, from_column, "
                "to_column, source, created_at) "
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
                (workspace_id, connection_id, repo_full_name, issue_number,
                 event_type, from_column, to_column, source, created_at),
            )
        else:
            conn.execute(
                "INSERT INTO board_events (workspace_id, connection_id, "
                "repo_full_name, issue_number, event_type, from_column, "
                "to_column, source) "
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
                (workspace_id, connection_id, repo_full_name, issue_number,
                 event_type, from_column, to_column, source),
            )


def get_events(
    repo_full_name: str | None = None, limit: int = 100
) -> list[dict]:
    """Most recent journal entries, newest first (one repo or all)."""
    with get_db() as conn:
        if repo_full_name:
            rows = conn.execute(
                "SELECT * FROM board_events WHERE repo_full_name = ? "
                "ORDER BY id DESC LIMIT ?",
                (repo_full_name, limit),
            ).fetchall()
        else:
            rows = conn.execute(
                "SELECT * FROM board_events ORDER BY id DESC LIMIT ?",
                (limit,),
            ).fetchall()
    return [dict(row) for row in rows]


def column_entry_times(
    repo_full_name: str, workspace_id: int = 1, connection_id: int = 1
) -> dict[int, str]:
    """When each issue entered its current column, per the journal.

    Latest event per issue, comments excluded (a comment does not move
    a card). Issues without journal entries are absent — callers fall
    back to board_state.updated_at, then to the issue's creation date.
    """
    with get_db() as conn:
        rows = conn.execute(
            "SELECT issue_number, MAX(created_at) AS entered_at "
            "FROM board_events WHERE workspace_id = ? AND connection_id = ? "
            "AND repo_full_name = ? "
            "AND event_type != 'commented' GROUP BY issue_number",
            (workspace_id, connection_id, repo_full_name),
        ).fetchall()
    return {row["issue_number"]: row["entered_at"] for row in rows}


def get_project_activity(days: int = 7) -> dict:
    """Per-repo journal activity in ONE aggregated pass (issue #19).

    For every repo present in the journal: closures and moves of
    the last `days` days (journal stamps are UTC strings), plus
    the most recent event ever seen for that repo.
    """
    from datetime import datetime, timedelta, timezone

    cutoff = (datetime.now(timezone.utc) - timedelta(days=days)).strftime(
        "%Y-%m-%d %H:%M:%S"
    )
    with get_db() as conn:
        rows = conn.execute(
            """
            SELECT repo_full_name,
                   SUM(CASE WHEN event_type = 'closed' AND created_at >= ?
                            THEN 1 ELSE 0 END),
                   SUM(CASE WHEN event_type = 'moved' AND created_at >= ?
                            THEN 1 ELSE 0 END),
                   MAX(created_at)
            FROM board_events
            GROUP BY repo_full_name
            """,
            (cutoff, cutoff),
        ).fetchall()
    return {
        r[0]: {"closed": r[1] or 0, "moved": r[2] or 0, "last_at": r[3]}
        for r in rows
    }


def get_metrics(repo_full_name: str | None = None, window_days: int = 30) -> dict:
    """Throughput and cycle time computed from the journal.

    Cycle time = time from entering in_progress (or from creation, when
    no in_progress entry exists) to the closing event, averaged over
    issues closed inside the window.
    """
    from datetime import datetime, timedelta, timezone

    def _parse(value: str):
        try:
            return datetime.fromisoformat(value).replace(tzinfo=timezone.utc)
        except (ValueError, TypeError):
            return None

    with get_db() as conn:
        if repo_full_name:
            rows = conn.execute(
                "SELECT repo_full_name, issue_number, event_type, to_column, "
                "created_at FROM board_events WHERE repo_full_name = ? "
                "ORDER BY created_at, id",
                (repo_full_name,),
            ).fetchall()
        else:
            rows = conn.execute(
                "SELECT repo_full_name, issue_number, event_type, to_column, "
                "created_at FROM board_events ORDER BY created_at, id"
            ).fetchall()

    cutoff = datetime.now(timezone.utc) - timedelta(days=window_days)
    counts: dict[str, int] = {}
    starts: dict[tuple, datetime] = {}
    cycles: list[float] = []
    for row in rows:
        at = _parse(row["created_at"])
        if at is None:
            continue
        if at >= cutoff:
            counts[row["event_type"]] = counts.get(row["event_type"], 0) + 1
        key = (row["repo_full_name"], row["issue_number"])
        if row["event_type"] == "created":
            starts.setdefault(key, at)
        elif row["event_type"] == "moved" and row["to_column"] == "in_progress":
            starts[key] = at  # cycle clock (re)starts on entering in_progress
        elif row["event_type"] == "closed" and key in starts and at >= cutoff:
            cycles.append((at - starts[key]).total_seconds() / 86400.0)
    return {
        "window_days": window_days,
        "created": counts.get("created", 0),
        "moved": counts.get("moved", 0),
        "closed": counts.get("closed", 0),
        "reopened": counts.get("reopened", 0),
        "avg_cycle_days": round(sum(cycles) / len(cycles), 2) if cycles else None,
        "cycle_samples": len(cycles),
    }


def set_notes(
    repo_full_name: str,
    note: str,
    workspace_id: int = 1,
    connection_id: int = 1,
) -> None:
    with get_db() as conn:
        conn.execute(
            """INSERT INTO project_notes
                   (workspace_id, connection_id, repo_full_name, note)
               VALUES (?, ?, ?, ?)
               ON CONFLICT(workspace_id, connection_id, repo_full_name)
               DO UPDATE SET note = excluded.note,
                             updated_at = datetime('now')""",
            (workspace_id, connection_id, repo_full_name, note),
        )


def get_notes(
    repo_full_name: str, workspace_id: int = 1, connection_id: int = 1
) -> str | None:
    with get_db() as conn:
        row = conn.execute(
            "SELECT note FROM project_notes WHERE workspace_id = ? "
            "AND connection_id = ? AND repo_full_name = ?",
            (workspace_id, connection_id, repo_full_name),
        ).fetchone()
    return row["note"] if row else None


def get_stats() -> dict:
    """Get overall stats across all projects."""
    with get_db() as conn:
        rows = conn.execute(
            "SELECT repo_full_name, column_name, COUNT(*) as cnt "
            "FROM board_state GROUP BY repo_full_name, column_name"
        ).fetchall()

    projects: dict[str, dict[str, int]] = {}
    for row in rows:
        if row["column_name"] not in KANBAN_COLUMNS:
            continue
        name = row["repo_full_name"]
        if name not in projects:
            projects[name] = {col: 0 for col in KANBAN_COLUMNS}
        projects[name][row["column_name"]] = row["cnt"]
    return projects


# ── Connexions aux forges (0.24.0, P0) ────────────────────

def list_connections(include_disabled: bool = True) -> list[dict]:
    query = "SELECT * FROM forge_connections"
    if not include_disabled:
        query += " WHERE enabled = 1"
    query += " ORDER BY id"
    with get_db() as conn:
        return [dict(r) for r in conn.execute(query).fetchall()]


def get_connection(connection_id: int) -> dict | None:
    with get_db() as conn:
        row = conn.execute(
            "SELECT * FROM forge_connections WHERE id = ?", (connection_id,)
        ).fetchone()
    return dict(row) if row else None


def get_connection_by_slug(slug: str) -> dict | None:
    with get_db() as conn:
        row = conn.execute(
            "SELECT * FROM forge_connections WHERE slug = ?", (slug,)
        ).fetchone()
    return dict(row) if row else None


def create_connection(
    slug: str,
    kind: str,
    name: str,
    base_url: str,
    token_encrypted: str = "",
    account_login: str = "",
    webhook_secret: str = "",
    owner_user_id: int | None = None,
) -> int:
    with get_db() as conn:
        cur = conn.execute(
            "INSERT INTO forge_connections (slug, kind, name, base_url, "
            "token_encrypted, account_login, webhook_secret, owner_user_id) "
            "VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
            (slug, kind, name, base_url, token_encrypted, account_login,
             webhook_secret, owner_user_id),
        )
        return int(cur.lastrowid)


def update_connection(connection_id: int, **fields) -> None:
    allowed = {
        "slug", "kind", "name", "base_url", "token_encrypted",
        "account_login", "webhook_secret", "enabled", "owner_user_id",
    }
    items = {k: v for k, v in fields.items() if k in allowed}
    if not items:
        return
    assignments = ", ".join(f"{k} = ?" for k in items)
    with get_db() as conn:
        conn.execute(
            f"UPDATE forge_connections SET {assignments} WHERE id = ?",
            (*items.values(), connection_id),
        )


def delete_connection(connection_id: int) -> None:
    with get_db() as conn:
        conn.execute(
            "DELETE FROM forge_connections WHERE id = ?", (connection_id,)
        )
        conn.execute(
            "DELETE FROM workspace_connections WHERE connection_id = ?",
            (connection_id,),
        )
        conn.execute(
            "DELETE FROM workspace_projects WHERE connection_id = ?",
            (connection_id,),
        )


# ── Utilisateurs (0.24.0, P2) ──────────────────────────────

def count_users() -> int:
    with get_db() as conn:
        return int(conn.execute("SELECT COUNT(*) FROM users").fetchone()[0])


def list_users() -> list[dict]:
    with get_db() as conn:
        return [
            dict(r)
            for r in conn.execute("SELECT * FROM users ORDER BY id").fetchall()
        ]


def get_user(user_id: int) -> dict | None:
    with get_db() as conn:
        row = conn.execute(
            "SELECT * FROM users WHERE id = ?", (user_id,)
        ).fetchone()
    return dict(row) if row else None


def get_user_by_username(username: str) -> dict | None:
    with get_db() as conn:
        row = conn.execute(
            "SELECT * FROM users WHERE username = ?", (username,)
        ).fetchone()
    return dict(row) if row else None


def get_user_by_draco(sub: str) -> dict | None:
    with get_db() as conn:
        row = conn.execute(
            "SELECT * FROM users WHERE draco_sub = ?", (sub,)
        ).fetchone()
    return dict(row) if row else None


def create_user(
    username: str,
    password_hash: str,
    role: str = "member",
    display_name: str = "",
    must_change_password: int = 0,
) -> int:
    with get_db() as conn:
        cur = conn.execute(
            "INSERT INTO users (username, password_hash, role, display_name, "
            "must_change_password) VALUES (?, ?, ?, ?, ?)",
            (username, password_hash, role, display_name, must_change_password),
        )
        return int(cur.lastrowid)


def update_user(user_id: int, **fields) -> None:
    allowed = {
        "password_hash", "role", "display_name", "draco_sub", "active",
        "must_change_password", "last_login_at",
    }
    items = {k: v for k, v in fields.items() if k in allowed}
    if not items:
        return
    assignments = ", ".join(f"{k} = ?" for k in items)
    with get_db() as conn:
        conn.execute(
            f"UPDATE users SET {assignments} WHERE id = ?",
            (*items.values(), user_id),
        )


# ── Espaces de travail (0.24.0, P3) ────────────────────────

def list_workspaces() -> list[dict]:
    with get_db() as conn:
        return [
            dict(r)
            for r in conn.execute(
                "SELECT * FROM workspaces ORDER BY id"
            ).fetchall()
        ]


def get_workspace(workspace_id: int) -> dict | None:
    with get_db() as conn:
        row = conn.execute(
            "SELECT * FROM workspaces WHERE id = ?", (workspace_id,)
        ).fetchone()
    return dict(row) if row else None


def create_workspace(slug: str, name: str, color: str = "#7c5cfc") -> int:
    with get_db() as conn:
        cur = conn.execute(
            "INSERT INTO workspaces (slug, name, color) VALUES (?, ?, ?)",
            (slug, name, color),
        )
        return int(cur.lastrowid)


def update_workspace(workspace_id: int, **fields) -> None:
    allowed = {"slug", "name", "color"}
    items = {k: v for k, v in fields.items() if k in allowed}
    if not items:
        return
    assignments = ", ".join(f"{k} = ?" for k in items)
    with get_db() as conn:
        conn.execute(
            f"UPDATE workspaces SET {assignments} WHERE id = ?",
            (*items.values(), workspace_id),
        )


def delete_workspace(workspace_id: int) -> None:
    with get_db() as conn:
        for table in (
            "workspace_connections", "workspace_projects",
            "workspace_members", "project_states",
        ):
            conn.execute(
                f"DELETE FROM {table} WHERE workspace_id = ?", (workspace_id,)
            )
        conn.execute("DELETE FROM workspaces WHERE id = ?", (workspace_id,))


def workspace_connection_ids(workspace_id: int) -> list[int]:
    with get_db() as conn:
        rows = conn.execute(
            "SELECT connection_id FROM workspace_connections "
            "WHERE workspace_id = ? ORDER BY connection_id",
            (workspace_id,),
        ).fetchall()
    return [r[0] for r in rows]


def set_workspace_connections(workspace_id: int, connection_ids: list[int]) -> None:
    with get_db() as conn:
        conn.execute(
            "DELETE FROM workspace_connections WHERE workspace_id = ?",
            (workspace_id,),
        )
        conn.executemany(
            "INSERT OR IGNORE INTO workspace_connections "
            "(workspace_id, connection_id) VALUES (?, ?)",
            [(workspace_id, cid) for cid in connection_ids],
        )


def workspace_project_selection(workspace_id: int) -> list[tuple[int, str]]:
    """Explicit project inclusions; empty means "all projects"."""
    with get_db() as conn:
        rows = conn.execute(
            "SELECT connection_id, repo_full_name FROM workspace_projects "
            "WHERE workspace_id = ? ORDER BY connection_id, repo_full_name",
            (workspace_id,),
        ).fetchall()
    return [(r[0], r[1]) for r in rows]


def set_workspace_projects(
    workspace_id: int, selection: list[tuple[int, str]]
) -> None:
    with get_db() as conn:
        conn.execute(
            "DELETE FROM workspace_projects WHERE workspace_id = ?",
            (workspace_id,),
        )
        conn.executemany(
            "INSERT OR IGNORE INTO workspace_projects "
            "(workspace_id, connection_id, repo_full_name) VALUES (?, ?, ?)",
            [(workspace_id, cid, repo) for cid, repo in selection],
        )


def list_members(workspace_id: int) -> list[dict]:
    with get_db() as conn:
        rows = conn.execute(
            "SELECT m.user_id, m.role, u.username, u.display_name "
            "FROM workspace_members m JOIN users u ON u.id = m.user_id "
            "WHERE m.workspace_id = ? ORDER BY u.username",
            (workspace_id,),
        ).fetchall()
    return [dict(r) for r in rows]


def set_member(workspace_id: int, user_id: int, role: str) -> None:
    with get_db() as conn:
        conn.execute(
            "INSERT INTO workspace_members (workspace_id, user_id, role) "
            "VALUES (?, ?, ?) ON CONFLICT(workspace_id, user_id) "
            "DO UPDATE SET role = excluded.role",
            (workspace_id, user_id, role),
        )


def remove_member(workspace_id: int, user_id: int) -> None:
    with get_db() as conn:
        conn.execute(
            "DELETE FROM workspace_members WHERE workspace_id = ? "
            "AND user_id = ?",
            (workspace_id, user_id),
        )


def user_workspaces(user_id: int) -> list[dict]:
    with get_db() as conn:
        rows = conn.execute(
            "SELECT w.*, m.role AS member_role FROM workspaces w "
            "JOIN workspace_members m ON m.workspace_id = w.id "
            "WHERE m.user_id = ? ORDER BY w.id",
            (user_id,),
        ).fetchall()
    return [dict(r) for r in rows]


def workspace_role(workspace_id: int, user_id: int) -> str | None:
    with get_db() as conn:
        row = conn.execute(
            "SELECT role FROM workspace_members WHERE workspace_id = ? "
            "AND user_id = ?",
            (workspace_id, user_id),
        ).fetchone()
    return row[0] if row else None


# ── États de projets / archives locales (0.24.0, P4) ──────

def get_project_state(
    workspace_id: int, connection_id: int, repo_full_name: str
) -> dict | None:
    with get_db() as conn:
        row = conn.execute(
            "SELECT * FROM project_states WHERE workspace_id = ? "
            "AND connection_id = ? AND repo_full_name = ?",
            (workspace_id, connection_id, repo_full_name),
        ).fetchone()
    return dict(row) if row else None


def set_local_archived(
    workspace_id: int,
    connection_id: int,
    repo_full_name: str,
    archived: bool,
) -> None:
    with get_db() as conn:
        conn.execute(
            "INSERT INTO project_states (workspace_id, connection_id, "
            "repo_full_name, local_archived, updated_at) "
            "VALUES (?, ?, ?, ?, datetime('now')) "
            "ON CONFLICT(workspace_id, connection_id, repo_full_name) "
            "DO UPDATE SET local_archived = excluded.local_archived, "
            "updated_at = excluded.updated_at",
            (workspace_id, connection_id, repo_full_name, 1 if archived else 0),
        )


def list_local_archived(workspace_id: int) -> list[dict]:
    with get_db() as conn:
        rows = conn.execute(
            "SELECT * FROM project_states WHERE workspace_id = ? "
            "AND local_archived = 1 ORDER BY repo_full_name",
            (workspace_id,),
        ).fetchall()
    return [dict(r) for r in rows]


def purge_project_local_state(
    workspace_id: int, connection_id: int, repo_full_name: str
) -> None:
    """Drop every local trace of a project in one workspace."""
    with get_db() as conn:
        for table in ("board_state", "project_notes", "today_picks"):
            conn.execute(
                f"DELETE FROM {table} WHERE workspace_id = ? "
                "AND connection_id = ? AND repo_full_name = ?",
                (workspace_id, connection_id, repo_full_name),
            )
        conn.execute(
            "DELETE FROM project_states WHERE workspace_id = ? "
            "AND connection_id = ? AND repo_full_name = ?",
            (workspace_id, connection_id, repo_full_name),
        )


# ── Journal d'administration (0.24.0, P2) ─────────────────

def log_admin(user_id: int | None, action: str, details: str = "") -> None:
    with get_db() as conn:
        conn.execute(
            "INSERT INTO admin_audit (user_id, action, details) "
            "VALUES (?, ?, ?)",
            (user_id, action, details),
        )


def list_admin_audit(limit: int = 100) -> list[dict]:
    with get_db() as conn:
        rows = conn.execute(
            "SELECT a.*, u.username FROM admin_audit a "
            "LEFT JOIN users u ON u.id = a.user_id "
            "ORDER BY a.id DESC LIMIT ?",
            (limit,),
        ).fetchall()
    return [dict(r) for r in rows]