from __future__ import annotations

import hashlib
import hmac
import json
import sqlite3
from pathlib import Path
from typing import Any, Dict, List, Optional

DB_FILE = Path(__file__).parent / "kanban.db"

_PBKDF2_SALT = b"pm-mvp-salt"
_PBKDF2_ITERATIONS = 260_000

DEFAULT_BOARD = {
    "columns": [
        {"id": "col-backlog", "title": "Backlog", "cardIds": ["card-1", "card-2"]},
        {"id": "col-discovery", "title": "Discovery", "cardIds": ["card-3"]},
        {"id": "col-progress", "title": "In Progress", "cardIds": ["card-4", "card-5"]},
        {"id": "col-review", "title": "Review", "cardIds": ["card-6"]},
        {"id": "col-done", "title": "Done", "cardIds": ["card-7", "card-8"]},
    ],
    "cards": {
        "card-1": {
            "id": "card-1",
            "title": "Align roadmap themes",
            "details": "Draft quarterly themes with impact statements and metrics.",
        },
        "card-2": {
            "id": "card-2",
            "title": "Gather customer signals",
            "details": "Review support tags, sales notes, and churn feedback.",
        },
        "card-3": {
            "id": "card-3",
            "title": "Prototype analytics view",
            "details": "Sketch initial dashboard layout and key drill-downs.",
        },
        "card-4": {
            "id": "card-4",
            "title": "Refine status language",
            "details": "Standardize column labels and tone across the board.",
        },
        "card-5": {
            "id": "card-5",
            "title": "Design card layout",
            "details": "Add hierarchy and spacing for scanning dense lists.",
        },
        "card-6": {
            "id": "card-6",
            "title": "QA micro-interactions",
            "details": "Verify hover, focus, and loading states.",
        },
        "card-7": {
            "id": "card-7",
            "title": "Ship marketing page",
            "details": "Final copy approved and asset pack delivered.",
        },
        "card-8": {
            "id": "card-8",
            "title": "Close onboarding sprint",
            "details": "Document release notes and share internally.",
        },
    },
}

_EMPTY_BOARD = {
    "columns": [{**col, "cardIds": []} for col in DEFAULT_BOARD["columns"]],
    "cards": {},
}

DEFAULT_USER = {"username": "user", "password": "password"}


def _hash_password(password: str) -> str:
    dk = hashlib.pbkdf2_hmac("sha256", password.encode(), _PBKDF2_SALT, _PBKDF2_ITERATIONS)
    return dk.hex()


def verify_user(username: str, password: str) -> bool:
    with get_connection() as connection:
        row = connection.execute(
            "SELECT password FROM users WHERE username = ?",
            (username,),
        ).fetchone()
    if not row:
        return False
    return hmac.compare_digest(row["password"], _hash_password(password))


def get_connection() -> sqlite3.Connection:
    connection = sqlite3.connect(DB_FILE)
    connection.row_factory = sqlite3.Row
    return connection


def initialize_schema(connection: sqlite3.Connection) -> None:
    connection.executescript(
        """
        PRAGMA foreign_keys = ON;

        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS boards (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            name TEXT NOT NULL DEFAULT 'My Board',
            board_json TEXT NOT NULL,
            updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        );

        DROP INDEX IF EXISTS idx_boards_user_id;
        CREATE INDEX IF NOT EXISTS idx_boards_user_id ON boards(user_id);

        CREATE UNIQUE INDEX IF NOT EXISTS idx_users_username ON users(username);
        """
    )

    try:
        connection.execute("ALTER TABLE boards ADD COLUMN name TEXT NOT NULL DEFAULT 'My Board'")
        connection.commit()
    except sqlite3.OperationalError:
        pass


def initialize_default_data(connection: sqlite3.Connection) -> None:
    hashed = _hash_password(DEFAULT_USER["password"])

    row = connection.execute(
        "SELECT id, password FROM users WHERE username = ?",
        (DEFAULT_USER["username"],),
    ).fetchone()

    if row is None:
        cursor = connection.execute(
            "INSERT INTO users (username, password) VALUES (?, ?)",
            (DEFAULT_USER["username"], hashed),
        )
        user_id = cursor.lastrowid
    else:
        user_id = row["id"]
        if len(row["password"]) != 64:
            connection.execute(
                "UPDATE users SET password = ? WHERE id = ?",
                (hashed, user_id),
            )

    board_row = connection.execute(
        "SELECT id FROM boards WHERE user_id = ?",
        (user_id,),
    ).fetchone()

    if board_row is None:
        connection.execute(
            "INSERT INTO boards (user_id, name, board_json) VALUES (?, ?, ?)",
            (user_id, "My Board", json.dumps(DEFAULT_BOARD, separators=(",", ":"))),
        )


def init_db() -> None:
    DB_FILE.parent.mkdir(parents=True, exist_ok=True)
    with get_connection() as connection:
        initialize_schema(connection)
        initialize_default_data(connection)
        connection.commit()


def get_boards_for_user(username: str) -> List[Dict[str, Any]]:
    with get_connection() as connection:
        rows = connection.execute(
            "SELECT b.id, b.name FROM boards b JOIN users u ON b.user_id = u.id WHERE u.username = ? ORDER BY b.id",
            (username,),
        ).fetchall()
    return [{"id": row["id"], "name": row["name"]} for row in rows]


def get_board_by_id(username: str, board_id: int) -> Optional[Dict[str, Any]]:
    with get_connection() as connection:
        row = connection.execute(
            "SELECT b.board_json FROM boards b JOIN users u ON b.user_id = u.id WHERE u.username = ? AND b.id = ?",
            (username, board_id),
        ).fetchone()
    if not row:
        return None
    return json.loads(row["board_json"])


def save_board_by_id(username: str, board_id: int, board: Dict[str, Any]) -> bool:
    with get_connection() as connection:
        cursor = connection.execute(
            """
            UPDATE boards SET board_json = ?, updated_at = CURRENT_TIMESTAMP
            WHERE id = ? AND user_id = (SELECT id FROM users WHERE username = ?)
            """,
            (json.dumps(board, separators=(",", ":")), board_id, username),
        )
        connection.commit()
    return cursor.rowcount > 0


def create_board_for_user(username: str, name: str) -> Optional[int]:
    with get_connection() as connection:
        user_row = connection.execute(
            "SELECT id FROM users WHERE username = ?", (username,)
        ).fetchone()
        if not user_row:
            return None
        empty_board = _EMPTY_BOARD
        cursor = connection.execute(
            "INSERT INTO boards (user_id, name, board_json) VALUES (?, ?, ?)",
            (user_row["id"], name, json.dumps(empty_board, separators=(",", ":"))),
        )
        connection.commit()
    return cursor.lastrowid