"""数据库初始化（SQLite，单用户表）。"""

from __future__ import annotations

import uuid
from datetime import datetime, timezone

from app.core.db_session import DBConnection, db_conn
from app.core.security import hash_password

_SCHEMA_VERSION = 1


def _now_iso() -> str:
    return datetime.now(timezone.utc).isoformat()


def init_database() -> None:
    with db_conn() as conn:
        conn.executescript(
            """
            CREATE TABLE IF NOT EXISTS schema_meta (
              key TEXT PRIMARY KEY,
              value TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS users (
              id TEXT PRIMARY KEY,
              username TEXT NOT NULL UNIQUE,
              email TEXT NOT NULL DEFAULT '',
              password_hash TEXT NOT NULL,
              display_name TEXT NOT NULL DEFAULT '',
              role TEXT NOT NULL DEFAULT 'user',
              status TEXT NOT NULL DEFAULT 'active',
              must_change_password INTEGER NOT NULL DEFAULT 0,
              created_at TEXT NOT NULL,
              updated_at TEXT NOT NULL,
              deleted_at TEXT
            );

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

            CREATE TABLE IF NOT EXISTS kb_entries (
              id TEXT PRIMARY KEY,
              title TEXT NOT NULL,
              category TEXT NOT NULL DEFAULT 'general',
              tags_json TEXT NOT NULL DEFAULT '[]',
              summary TEXT NOT NULL DEFAULT '',
              content TEXT NOT NULL,
              created_by TEXT NOT NULL DEFAULT '',
              created_at TEXT NOT NULL,
              updated_at TEXT NOT NULL,
              deleted_at TEXT
            );

            CREATE INDEX IF NOT EXISTS idx_kb_category ON kb_entries(category);
            CREATE INDEX IF NOT EXISTS idx_kb_updated ON kb_entries(updated_at);
            """
        )
        row = conn.execute("SELECT value FROM schema_meta WHERE key='version'").fetchone()
        if not row:
            conn.execute(
                "INSERT INTO schema_meta(key, value) VALUES ('version', ?)",
                (str(_SCHEMA_VERSION),),
            )
        admin = conn.execute(
            "SELECT id FROM users WHERE username = 'admin' AND deleted_at IS NULL LIMIT 1"
        ).fetchone()
        if not admin:
            _seed_admin(conn)


def _seed_admin(conn: DBConnection) -> None:
    now = _now_iso()
    conn.execute(
        """
        INSERT INTO users(
          id, username, email, password_hash, display_name,
          role, status, must_change_password, created_at, updated_at
        ) VALUES (?,?,?,?,?,?,?,?,?,?)
        """,
        (
            str(uuid.uuid4()),
            "admin",
            "admin@compilot.local",
            hash_password("admin123"),
            "系统管理员",
            "admin",
            "active",
            1,
            now,
            now,
        ),
    )


def row_to_dict(row: dict | None) -> dict | None:
    return row