"""
Dashboard PostgreSQL Database — Schema + Tables for Admin Dashboard.
Uses CHECKPOINT_POSTGRES_URL from .env (same PG server as chat history).
"""
import psycopg

from common.pool_wrapper import get_pooled_connection_compat
from config import CHECKPOINT_POSTGRES_URL

DASHBOARD_SCHEMA = "dashboard_canifa"

def get_conn():
    """Get a pooled connection (returns to pool on .close())."""
    return get_pooled_connection_compat()

def _get_raw_conn():
    """Get a raw connection for init-time use (before pool is ready)."""
    return psycopg.connect(CHECKPOINT_POSTGRES_URL, autocommit=True)


def init_dashboard_schema():
    """Create schema and all tables for the admin dashboard."""
    conn = _get_raw_conn()
    cur = conn.cursor()

    # Create schema
    cur.execute(f"CREATE SCHEMA IF NOT EXISTS {DASHBOARD_SCHEMA}")

    # 1. admin_users — Account management
    cur.execute(f"""
        CREATE TABLE IF NOT EXISTS {DASHBOARD_SCHEMA}.admin_users (
            id          SERIAL PRIMARY KEY,
            username    VARCHAR(100) UNIQUE NOT NULL,
            password    VARCHAR(255) NOT NULL,
            role        VARCHAR(20) DEFAULT 'viewer' CHECK (role IN ('admin', 'editor', 'viewer')),
            created_at  TIMESTAMPTZ DEFAULT NOW(),
            last_login  TIMESTAMPTZ
        )
    """)

    # 2. activity_logs — Track user actions
    try:
        cur.execute(f"ALTER TABLE {DASHBOARD_SCHEMA}.admin_users ADD COLUMN IF NOT EXISTS settings JSONB DEFAULT '{{}}'::jsonb")
    except Exception:
        pass

    cur.execute(f"""
        CREATE TABLE IF NOT EXISTS {DASHBOARD_SCHEMA}.activity_logs (
            id          SERIAL PRIMARY KEY,
            user_id     INTEGER REFERENCES {DASHBOARD_SCHEMA}.admin_users(id) ON DELETE SET NULL,
            action      VARCHAR(100) NOT NULL,
            detail      TEXT,
            ip_address  VARCHAR(50),
            created_at  TIMESTAMPTZ DEFAULT NOW()
        )
    """)

    # 3. system_settings — Key-value config store
    cur.execute(f"""
        CREATE TABLE IF NOT EXISTS {DASHBOARD_SCHEMA}.system_settings (
            key         VARCHAR(100) PRIMARY KEY,
            value       TEXT,
            updated_at  TIMESTAMPTZ DEFAULT NOW()
        )
    """)

    # 4. chat_history — Save AI Data Analyst / AI SQL conversations for dashboard users
    cur.execute(f"""
        CREATE TABLE IF NOT EXISTS {DASHBOARD_SCHEMA}.chat_history (
            id          SERIAL PRIMARY KEY,
            user_id     INTEGER REFERENCES {DASHBOARD_SCHEMA}.admin_users(id) ON DELETE CASCADE,
            module      VARCHAR(50) NOT NULL DEFAULT 'ai_data_analyst',
            conversation_id VARCHAR(100),
            message     TEXT NOT NULL,
            is_human    BOOLEAN NOT NULL,
            created_at  TIMESTAMPTZ DEFAULT NOW()
        )
    """)
    try:
        cur.execute(f"ALTER TABLE {DASHBOARD_SCHEMA}.chat_history ADD COLUMN IF NOT EXISTS conversation_id VARCHAR(100)")
    except Exception:
        pass

    # 5. saved_reports — Store AI-generated reports with versioning
    cur.execute(f"""
        CREATE TABLE IF NOT EXISTS {DASHBOARD_SCHEMA}.saved_reports (
            id              SERIAL PRIMARY KEY,
            user_id         INTEGER REFERENCES {DASHBOARD_SCHEMA}.admin_users(id) ON DELETE SET NULL,
            title           TEXT NOT NULL,
            prompt          TEXT,
            html_content    TEXT,
            version         INTEGER NOT NULL DEFAULT 1,
            parent_id       INTEGER REFERENCES {DASHBOARD_SCHEMA}.saved_reports(id) ON DELETE SET NULL,
            conversation_id VARCHAR(100),
            tools_used      JSONB DEFAULT '[]'::jsonb,
            cycles_count    INTEGER DEFAULT 0,
            generation_time_ms INTEGER DEFAULT 0,
            created_at      TIMESTAMPTZ DEFAULT NOW()
        )
    """)
    try:
        cur.execute(f"ALTER TABLE {DASHBOARD_SCHEMA}.saved_reports ADD COLUMN IF NOT EXISTS conversation_id VARCHAR(100)")
    except Exception:
        pass

    # Create indexes for performance
    cur.execute(f"""
        CREATE INDEX IF NOT EXISTS idx_chat_history_user_module 
        ON {DASHBOARD_SCHEMA}.chat_history (user_id, module, created_at DESC)
    """)
    cur.execute(f"""
        CREATE INDEX IF NOT EXISTS idx_saved_reports_user 
        ON {DASHBOARD_SCHEMA}.saved_reports (user_id, created_at DESC)
    """)
    cur.execute(f"""
        CREATE INDEX IF NOT EXISTS idx_saved_reports_parent 
        ON {DASHBOARD_SCHEMA}.saved_reports (parent_id)
    """)
    
    cur.execute(f"""
        CREATE INDEX IF NOT EXISTS idx_saved_reports_conv 
        ON {DASHBOARD_SCHEMA}.saved_reports (conversation_id)
    """)
    cur.execute(f"""
        CREATE INDEX IF NOT EXISTS idx_chat_history_conv 
        ON {DASHBOARD_SCHEMA}.chat_history (conversation_id)
    """)

    cur.close()
    conn.close()
    print(f"[OK] Schema '{DASHBOARD_SCHEMA}' and all tables initialized (5 tables).")


# Auto-init on import
try:
    init_dashboard_schema()
except Exception as e:
    print(f"[WARN] Dashboard DB init skipped (PG not available): {e}")
