"""MCP's OWN database (ats_mcp_db). The MCP stores MFA enrollment here.
Projects never touch this DB directly — they call MCP tools, which use it.
"""
import os

import psycopg2

DB = {
    "host": os.getenv("MCP_DB_HOST", "localhost"),
    "port": os.getenv("MCP_DB_PORT", "5432"),
    "dbname": os.getenv("MCP_DB_NAME", "ats_mcp_db"),
    "user": os.getenv("MCP_DB_USER", "postgres"),
    "password": os.getenv("MCP_DB_PASSWORD", "root"),
}


def get_conn():
    return psycopg2.connect(**DB)


def ensure_schema():
    with get_conn() as c, c.cursor() as cur:
        cur.execute("""
            CREATE TABLE IF NOT EXISTS mfa_users (
                user_key     VARCHAR(255) PRIMARY KEY,
                method       VARCHAR(10)  DEFAULT 'none',
                totp_secret  VARCHAR(64)  DEFAULT '',
                otp_code     VARCHAR(6)   DEFAULT '',
                otp_created  TIMESTAMP    NULL,
                mfa_enabled  SMALLINT     DEFAULT 0,
                updated_at   TIMESTAMP    DEFAULT NOW()
            )
        """)
        cur.execute("""
            CREATE TABLE IF NOT EXISTS mcp_users (
                email        VARCHAR(255) PRIMARY KEY,
                password     VARCHAR(255) NOT NULL,
                role         VARCHAR(50)  DEFAULT 'ADMIN',
                created_at   TIMESTAMP    DEFAULT NOW()
            )
        """)
        # Seed users. Mirrors the TA-ATS demo user seeder (backend/seed_demo_users.py):
        # emails ats@<role>.com, common password ats@2468, plus the original admin.
        # Upserted every run so the MCP store stays in sync with the ATS users.
        seed_users = [
            ("kunal.verma@indovisionservices.in", "pbkdf2_sha256$1200000$ZRXLhoTY6YtobBJ8kjSL7X$tHxpOCiXQZtI8T9y6jCH3F4r3DrBt6z1ugUD+eSllRU=", "ADMIN"),
            # --- TA-ATS demo users (password: ats@2468) ---
            ("ats@admin.com", "pbkdf2_sha256$1200000$nJS8JsarYSA5jvsVhC1r3d$dSvqoI+frTjgxMwJWQmG56XIwwFDmGT8lP7AWWEfwQ0=", "ADMIN"),
            ("ats@hiringmanager.com", "pbkdf2_sha256$1200000$oeAIDcjrzw1qdrOuKymkPU$45kGsFeg1+Ioi3kV29K6P7/lkJsIhQqmqpsrV1XWrX4=", "HIRING_MANAGER"),
            ("ats@projectmanager.com", "pbkdf2_sha256$1200000$gmuE8Dx9gmEEcS6vQR2fZo$OlbGydL5jXXKesquZFDevFY20oMRGcOo6TEprgrMFUs=", "PROJECT_MANAGER"),
            ("ats@recruiter.com", "pbkdf2_sha256$1200000$sao41XZ6NjAk9UMUSRUG5r$jm9V7auyYc/ET9LAuQNSYcg1WDHpSSwCk1xn2cn0y90=", "RECRUITER"),
            ("ats@interviewer.com", "pbkdf2_sha256$1200000$6lxZz6dT4RT1q8SDJXRZ83$Ts9OJAAkZZtcUS7pkjDNP+mwM4tDwTmGpquZPV1RycQ=", "INTERVIEWER"),
            ("ats@candidate.com", "pbkdf2_sha256$1200000$ZwcunJF96rl0rR0Ics44lw$7CdnHFCjqeawDu7aStxhO+oUGj6lSFNFqjwuLsSRSZc=", "CANDIDATE"),
        ]
        cur.executemany("""
            INSERT INTO mcp_users (email, password, role)
            VALUES (%s, %s, %s)
            ON CONFLICT (email) DO UPDATE SET password=EXCLUDED.password, role=EXCLUDED.role;
        """, seed_users)
        c.commit()