# -*- coding: utf-8 -*-
"""
SQLite connection with schema migration (v1 -> v2).

Migration strategy:
- Check PRAGMA user_version.
- If version < 2 and old 'accounts' table exists, back it up as
  'accounts_legacy_backup_<timestamp>' before creating new tables.
- New tables are created with CREATE TABLE IF NOT EXISTS.
- Logs all migration steps.
"""
import os
import sqlite3
import time
from typing import Optional

from db.schema import SCHEMA_VERSION, ensure_schema
from util.constants import SKILL_SLUG
from util.logging_config import get_skill_logger
from util.runtime_paths import get_db_path


def get_conn() -> sqlite3.Connection:
    """Open a connection to the skill database (autocommit off)."""
    conn = sqlite3.connect(get_db_path())
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=OFF")
    return conn


def _backup_old_accounts(conn: sqlite3.Connection) -> bool:
    """Rename old 'accounts' table to 'accounts_legacy_backup_<ts>' if it exists with old schema.
    Returns True if backup was performed."""
    cur = conn.cursor()
    cur.execute(
        "SELECT name FROM sqlite_master WHERE type='table' AND name='accounts'"
    )
    if not cur.fetchone():
        return False

    # Check if it's the old schema (has 'phone' column but no 'platform_key')
    # We just check the column count — old schema had fewer columns.
    cur.execute("PRAGMA table_info(accounts)")
    columns = [row[1] for row in cur.fetchall()]
    # Old schema: id, name, platform, phone, profile_dir, url, extra_json, created_at, updated_at = 9
    # New schema: many more. If it looks like old, back it up.
    has_legacy_structure = "phone" in columns and "platform_key" not in columns
    if not has_legacy_structure:
        return False

    ts = int(time.time())
    backup_name = f"accounts_legacy_backup_{ts}"
    cur.execute(f"ALTER TABLE accounts RENAME TO {backup_name}")
    conn.commit()
    logger = get_skill_logger()
    logger.info(
        "schema_migration_backup old_table=accounts backup_table=%s",
        backup_name,
    )
    return True


def _check_schema_version(conn: sqlite3.Connection) -> int:
    cur = conn.cursor()
    cur.execute("PRAGMA user_version")
    row = cur.fetchone()
    return int(row[0]) if row else 0


def init_db() -> None:
    """Idempotent: migrate if needed, create tables, set schema version."""
    log = get_skill_logger()
    log.info("schema_init_start")
    conn = get_conn()
    try:
        old_version = _check_schema_version(conn)
        log.info("schema_current_version=%s", old_version)

        if old_version < SCHEMA_VERSION:
            if old_version < 2:
                backed_up = _backup_old_accounts(conn)
                if backed_up:
                    log.info("schema_init_backup_done old_version=%s", old_version)

        ensure_schema(conn)

        cur = conn.cursor()
        cur.execute(f"PRAGMA user_version = {SCHEMA_VERSION}")
        conn.commit()

        if old_version < SCHEMA_VERSION:
            log.info(
                "schema_init_done version=%s migrated_from=%s",
                SCHEMA_VERSION,
                old_version,
            )
        else:
            log.info("schema_init_uptodate version=%s", SCHEMA_VERSION)
    except Exception:
        log.exception("schema_init_failure")
        raise
    finally:
        conn.close()


def get_db_version() -> int:
    """Return current PRAGMA user_version without initializing."""
    conn = get_conn()
    try:
        cur = conn.cursor()
        cur.execute("PRAGMA user_version")
        row = cur.fetchone()
        return int(row[0]) if row else 0
    finally:
        conn.close()