from __future__ import annotations

import sqlite3
from contextlib import contextmanager
from datetime import datetime, timezone
from typing import Any, Iterable

from .config import DB_PATH, ensure_directories


def utcnow() -> str:
    return datetime.now(timezone.utc).isoformat(timespec='seconds')


def connect() -> sqlite3.Connection:
    ensure_directories()
    conn = sqlite3.connect(DB_PATH, timeout=30)
    conn.row_factory = sqlite3.Row
    conn.execute('PRAGMA foreign_keys = ON')
    conn.execute('PRAGMA journal_mode = WAL')
    conn.execute('PRAGMA synchronous = NORMAL')
    return conn


@contextmanager
def transaction():
    conn = connect()
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


SCHEMA = r'''
CREATE TABLE IF NOT EXISTS admins (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    created_at TEXT NOT NULL,
    last_login_at TEXT
);
CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS instagram_accounts (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    username TEXT,
    instagram_user_id TEXT,
    profile_pic_url TEXT,
    status TEXT NOT NULL DEFAULT 'disconnected',
    session_status TEXT NOT NULL DEFAULT 'missing',
    challenge_status TEXT NOT NULL DEFAULT 'none',
    rate_limit_status TEXT NOT NULL DEFAULT 'ok',
    last_login_at TEXT,
    last_successful_request_at TEXT,
    last_error TEXT,
    login_failures INTEGER NOT NULL DEFAULT 0,
    updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS instagram_sessions (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    file_path TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    valid INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS automations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    enabled INTEGER NOT NULL DEFAULT 1,
    trigger_type TEXT NOT NULL DEFAULT 'comment',
    media_scope TEXT NOT NULL DEFAULT 'all',
    reply_mode TEXT NOT NULL DEFAULT 'random',
    sequential_index INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS automation_keywords (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    automation_id INTEGER NOT NULL REFERENCES automations(id) ON DELETE CASCADE,
    keyword TEXT NOT NULL,
    match_type TEXT NOT NULL DEFAULT 'exact'
);
CREATE TABLE IF NOT EXISTS automation_steps (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    automation_id INTEGER NOT NULL REFERENCES automations(id) ON DELETE CASCADE,
    position INTEGER NOT NULL,
    step_type TEXT NOT NULL,
    config_json TEXT NOT NULL DEFAULT '{}'
);
CREATE TABLE IF NOT EXISTS automation_media (
    automation_id INTEGER NOT NULL REFERENCES automations(id) ON DELETE CASCADE,
    media_id TEXT NOT NULL,
    PRIMARY KEY (automation_id, media_id)
);
CREATE TABLE IF NOT EXISTS comment_replies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    automation_id INTEGER NOT NULL REFERENCES automations(id) ON DELETE CASCADE,
    text TEXT NOT NULL,
    position INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS processed_comments (
    comment_id TEXT PRIMARY KEY,
    automation_id INTEGER,
    user_id TEXT,
    username TEXT,
    media_id TEXT,
    original_text TEXT,
    normalized_text TEXT,
    reply_status TEXT,
    error TEXT,
    processed_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS processed_messages (
    message_id TEXT PRIMARY KEY,
    automation_id INTEGER,
    user_id TEXT,
    username TEXT,
    thread_id TEXT,
    original_text TEXT,
    normalized_text TEXT,
    reply_status TEXT,
    error TEXT,
    processed_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS processed_events (
    event_key TEXT PRIMARY KEY,
    event_type TEXT NOT NULL,
    processed_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS conversations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    instagram_user_id TEXT NOT NULL UNIQUE,
    username TEXT,
    automation_id INTEGER,
    state TEXT,
    thread_id TEXT,
    updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS conversation_states (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    instagram_user_id TEXT NOT NULL,
    automation_id INTEGER,
    state TEXT NOT NULL,
    payload_json TEXT NOT NULL DEFAULT '{}',
    updated_at TEXT NOT NULL,
    UNIQUE(instagram_user_id, automation_id)
);
CREATE TABLE IF NOT EXISTS follow_checks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    instagram_user_id TEXT NOT NULL,
    username TEXT,
    result TEXT NOT NULL,
    source TEXT NOT NULL,
    duration_ms INTEGER,
    error TEXT,
    checked_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_follow_checks_user_time ON follow_checks(instagram_user_id, checked_at DESC);
CREATE TABLE IF NOT EXISTS media_files (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    original_name TEXT NOT NULL,
    stored_name TEXT NOT NULL UNIQUE,
    mime_type TEXT NOT NULL,
    file_type TEXT NOT NULL,
    size_bytes INTEGER NOT NULL,
    created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS worker_runs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    started_at TEXT NOT NULL,
    ended_at TEXT,
    duration_ms INTEGER,
    result TEXT,
    comments_checked INTEGER NOT NULL DEFAULT 0,
    new_comments INTEGER NOT NULL DEFAULT 0,
    dm_checked INTEGER NOT NULL DEFAULT 0,
    new_dm INTEGER NOT NULL DEFAULT 0,
    automations_triggered INTEGER NOT NULL DEFAULT 0,
    messages_sent INTEGER NOT NULL DEFAULT 0,
    errors INTEGER NOT NULL DEFAULT 0,
    details_json TEXT NOT NULL DEFAULT '{}'
);
CREATE TABLE IF NOT EXISTS cron_status (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    last_run_at TEXT,
    last_success_at TEXT,
    last_error TEXT,
    expected_interval_seconds INTEGER NOT NULL DEFAULT 60,
    updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS debug_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    run_id INTEGER,
    stage TEXT NOT NULL,
    result TEXT NOT NULL,
    message TEXT,
    created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS error_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    error_type TEXT NOT NULL,
    module TEXT,
    action TEXT,
    message TEXT NOT NULL,
    technical_details TEXT,
    user_id TEXT,
    automation_id INTEGER,
    comment_id TEXT,
    message_id TEXT,
    resolved INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS activity_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    level TEXT NOT NULL DEFAULT 'info',
    category TEXT NOT NULL,
    action TEXT NOT NULL,
    message TEXT NOT NULL,
    details_json TEXT NOT NULL DEFAULT '{}',
    created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS system_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    level TEXT NOT NULL,
    source TEXT NOT NULL,
    message TEXT NOT NULL,
    created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS database_migrations (
    version INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    applied_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS login_attempts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    remote_addr TEXT,
    username TEXT,
    success INTEGER NOT NULL,
    attempted_at TEXT NOT NULL
);
'''

DEFAULT_SETTINGS = {
    'setup_complete': '0',
    'bot_enabled': '1',
    'debug_mode': '0',
    'dry_run': '0',
    'comment_check_limit': '20',
    'dm_check_limit': '20',
    'posts_check_limit': '5',
    'message_delay_min': '1',
    'message_delay_max': '3',
    'follow_cache_duration': '600',
    'max_follow_checks': '3',
    'follow_check_window': '600',
    'log_retention_days': '30',
    'scheduler_mode': 'cpanel_cron',
    'timezone': 'UTC',
}


def init_db() -> None:
    with transaction() as conn:
        conn.executescript(SCHEMA)
        now = utcnow()
        for key, value in DEFAULT_SETTINGS.items():
            conn.execute(
                'INSERT OR IGNORE INTO settings(key, value, updated_at) VALUES (?, ?, ?)',
                (key, value, now),
            )
        conn.execute(
            "INSERT OR IGNORE INTO instagram_accounts(id, updated_at) VALUES (1, ?)",
            (now,),
        )
        conn.execute(
            "INSERT OR IGNORE INTO cron_status(id, updated_at) VALUES (1, ?)",
            (now,),
        )
        conn.execute(
            "INSERT OR IGNORE INTO database_migrations(version, name, applied_at) VALUES (1, 'initial_schema', ?)",
            (now,),
        )


def query_one(sql: str, params: Iterable[Any] = ()):
    conn = connect()
    try:
        return conn.execute(sql, tuple(params)).fetchone()
    finally:
        conn.close()


def query_all(sql: str, params: Iterable[Any] = ()):
    conn = connect()
    try:
        return conn.execute(sql, tuple(params)).fetchall()
    finally:
        conn.close()


def execute(sql: str, params: Iterable[Any] = ()) -> int:
    with transaction() as conn:
        cur = conn.execute(sql, tuple(params))
        return cur.lastrowid


def get_setting(key: str, default: str | None = None) -> str | None:
    row = query_one('SELECT value FROM settings WHERE key = ?', (key,))
    return row['value'] if row else default


def set_setting(key: str, value: Any) -> None:
    execute(
        '''INSERT INTO settings(key, value, updated_at) VALUES (?, ?, ?)
           ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at''',
        (key, str(value), utcnow()),
    )


def get_bool(key: str, default: bool = False) -> bool:
    value = get_setting(key, '1' if default else '0')
    return str(value).lower() in {'1', 'true', 'yes', 'on'}


def get_int(key: str, default: int) -> int:
    try:
        return int(get_setting(key, str(default)) or default)
    except (TypeError, ValueError):
        return default