import os
import sqlite3
import logging
from contextlib import contextmanager

logger = logging.getLogger(__name__)

DB_PATH = "data/senator.db"

_initialized = False


def get_connection():
    conn = sqlite3.connect(get_db_path())
    return _ConnectionContextManager(conn)


def get_db_path():
    return os.getenv("SENATOR_DB_PATH", DB_PATH)


def set_db_path(path):
    global DB_PATH
    DB_PATH = path


class _ConnectionContextManager:
    """Context manager for sqlite3 connections that auto-closes."""
    def __init__(self, conn):
        self.conn = conn

    def __enter__(self):
        return self.conn

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.conn.close()
        return False


def init_database():
    global _initialized
    if _initialized:
        return

    _initialized = True

    db_path = get_db_path()
    db_dir = os.path.dirname(db_path)
    if db_dir and not os.path.exists(db_dir):
        os.makedirs(db_dir)

    conn = sqlite3.connect(db_path)
    try:
        c = conn.cursor()

        c.execute("PRAGMA foreign_keys = ON")

        c.execute("""
        CREATE TABLE IF NOT EXISTS settings (
            key TEXT PRIMARY KEY,
            value TEXT
        )""")

        c.execute("""
        CREATE TABLE IF NOT EXISTS cron_jobs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            cron_expression TEXT NOT NULL,
            type TEXT NOT NULL,
            channel_id INTEGER NOT NULL,
            active INTEGER DEFAULT 1,
            audio_filename TEXT,
            message TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )""")

        c.execute("""
        CREATE TABLE IF NOT EXISTS voice_activity (
            user_id TEXT PRIMARY KEY,
            join_time REAL,
            total_active REAL DEFAULT 0,
            total_afk REAL DEFAULT 0,
            is_afk INTEGER DEFAULT 0,
            afk_start REAL,
            active_start REAL,
            channel_name TEXT,
            last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )""")

        c.execute("CREATE INDEX IF NOT EXISTS idx_voice_activity_user_id ON voice_activity(user_id)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_cron_jobs_active ON cron_jobs(active)")

        c.execute("""
        CREATE TABLE IF NOT EXISTS triggers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )""")

        c.execute("""
        CREATE TABLE IF NOT EXISTS trigger_keywords (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            trigger_id INTEGER NOT NULL,
            keyword TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (trigger_id) REFERENCES triggers(id) ON DELETE CASCADE,
            UNIQUE(trigger_id, keyword)
        )""")

        c.execute("""
        CREATE TABLE IF NOT EXISTS trigger_responses (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            trigger_id INTEGER NOT NULL,
            response_text TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (trigger_id) REFERENCES triggers(id) ON DELETE CASCADE
        )""")

        c.execute("CREATE INDEX IF NOT EXISTS idx_trigger_keywords_trigger_id ON trigger_keywords(trigger_id)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_trigger_keywords_keyword ON trigger_keywords(keyword)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_trigger_responses_trigger_id ON trigger_responses(trigger_id)")

        conn.commit()
        logger.info("Database initialized successfully")
    finally:
        conn.close()