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

import sqlite3
import os
from datetime import datetime

DB_PATH = os.path.expanduser("~/.kanbante/kanbante.db")
KANBAN_COLUMNS = ["backlog", "todo", "in_progress", "review", "done"]


def get_db() -> sqlite3.Connection:
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    return conn


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

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

        CREATE INDEX IF NOT EXISTS idx_board_repo
            ON board_state(repo_full_name, column_name);
    """)
    conn.commit()
    conn.close()


def get_board_state(repo_full_name: str) -> dict[str, list[dict]]:
    """Return board columns with their issues."""
    conn = get_db()
    rows = conn.execute(
        "SELECT issue_number, column_name, updated_at FROM board_state WHERE repo_full_name = ?",
        (repo_full_name,),
    ).fetchall()
    conn.close()

    columns: dict[str, list[dict]] = {col: [] for col in KANBAN_COLUMNS}
    for row in rows:
        columns[row["column_name"]].append(
            {
                "issue_number": row["issue_number"],
                "updated_at": row["updated_at"],
            }
        )
    return columns


def move_issue(repo_full_name: str, issue_number: int, to_column: str) -> None:
    """Move an issue to a different column."""
    conn = get_db()
    conn.execute(
        """INSERT INTO board_state (repo_full_name, issue_number, column_name, updated_at)
           VALUES (?, ?, ?, datetime('now'))
           ON CONFLICT(repo_full_name, issue_number)
           DO UPDATE SET column_name = excluded.column_name, updated_at = excluded.updated_at""",
        (repo_full_name, issue_number, to_column),
    )
    conn.commit()
    conn.close()


def set_notes(repo_full_name: str, note: str) -> None:
    conn = get_db()
    conn.execute(
        """INSERT INTO project_notes (repo_full_name, note)
           VALUES (?, ?)
           ON CONFLICT(repo_full_name)
           DO UPDATE SET note = excluded.note""",
        (repo_full_name, note),
    )
    conn.commit()
    conn.close()


def get_notes(repo_full_name: str) -> str | None:
    conn = get_db()
    row = conn.execute(
        "SELECT note FROM project_notes WHERE repo_full_name = ?",
        (repo_full_name,),
    ).fetchone()
    conn.close()
    return row["note"] if row else None


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

    projects: dict[str, dict[str, int]] = {}
    for row in rows:
        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