import os
import json
import asyncpg
import time
import uuid
from dotenv import load_dotenv

load_dotenv(override=True)

# ─────────────────────────────────────────
# TENANT CACHE — avoids hitting main DB every request
# ─────────────────────────────────────────
_tenant_cache = {}
_CACHE_TTL = 300  # 5 minutes


def _cache_get(tenant_id: str):
    item = _tenant_cache.get(tenant_id)
    if not item:
        return None
    if item["expires"] < time.time():
        _tenant_cache.pop(tenant_id, None)
        return None
    return item["db_name"]


def _cache_set(tenant_id: str, db_name: str):
    _tenant_cache[tenant_id] = {
        "db_name": db_name,
        "expires": time.time() + _CACHE_TTL
    }


# ─────────────────────────────────────────
# DSN BUILDERS — reads from .env
# ─────────────────────────────────────────

def _main_dsn() -> str:
    """Main fyndo DB — has organizations table"""
    return (
        f"postgresql://{os.getenv('MAIN_DB_USER', 'postgres')}:{os.getenv('MAIN_DB_PASSWORD', 'fyndo123')}"
        f"@{os.getenv('MAIN_DB_HOST', '192.168.12.40')}:{os.getenv('MAIN_DB_PORT', '5432')}"
        f"/{os.getenv('MAIN_DB_NAME', 'Fyndo')}"
    )


def _tenant_dsn(db_name: str) -> str:
    """Tenant specific DB — same host, different db_name"""
    return (
        f"postgresql://{os.getenv('MAIN_DB_USER', 'postgres')}:{os.getenv('MAIN_DB_PASSWORD', 'fyndo123')}"
        f"@{os.getenv('MAIN_DB_HOST', '192.168.12.40')}:{os.getenv('MAIN_DB_PORT', '5432')}"
        f"/{db_name}"
    )


# ─────────────────────────────────────────
# GET TENANT DB NAME from organizations table
# ─────────────────────────────────────────

async def get_tenant_db_name(tenant_id: str) -> str:
    cached = _cache_get(tenant_id)
    if cached:
        print(f"[TENANT] cache hit — {tenant_id} → {cached}")
        return cached

    try:
        conn = await asyncpg.connect(dsn=_main_dsn())
        try:
            row = await conn.fetchrow(
                "SELECT db_name FROM organizations WHERE id = $1 LIMIT 1",
                str(tenant_id)
            )
            if not row:
                raise Exception(f"Tenant {tenant_id} not found in organizations")
            db_name = row["db_name"]
            _cache_set(tenant_id, db_name)
            print(f"[TENANT] resolved — {tenant_id} → {db_name}")
            return db_name
        finally:
            await conn.close()
    except Exception as e:
        print(f"[TENANT] lookup failed: {e}")
        raise


async def get_tenant_conn(tenant_id: str):
    """Get asyncpg connection to tenant DB"""
    db_name = await get_tenant_db_name(tenant_id)
    return await asyncpg.connect(dsn=_tenant_dsn(db_name))


# ─────────────────────────────────────────
# VALIDATE USER in tenant DB
# ─────────────────────────────────────────

async def validate_user(tenant_id: str, user_id: str) -> dict:
    conn = await get_tenant_conn(tenant_id)
    try:
        row = await conn.fetchrow(
            "SELECT id, role_id FROM users WHERE id = $1 LIMIT 1",
            str(user_id)
        )
        if not row:
            print(f"[TENANT] user {user_id} not found")
            return {"id": user_id, "role_id": None}
        print(f"[TENANT] user validated — {user_id}")
        return dict(row)
    finally:
        await conn.close()


# ─────────────────────────────────────────
# TABLE: workspaceupload
# ─────────────────────────────────────────

async def save_upload_tenant(
    tenant_id: str, chat_id: str,
    organization_id: str, user_id: str, workspace_id: str,
    uploaded_files: list, batch_id: str = None, section_id: str = None
) -> str:
    conn = await get_tenant_conn(tenant_id)
    filenames = json.dumps([f.get("filename") for f in uploaded_files])
    try:
        row = await conn.fetchrow("""
            INSERT INTO workspaceupload
            (id, organization_id, user_id, workspace_id, section_id,
             chat_id, batch_id, filenames, total_files, response)
            VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)
            RETURNING id
        """,
            str(uuid.uuid4()),
            organization_id, user_id, workspace_id, section_id,
            chat_id, batch_id or '', filenames, len(uploaded_files),
            json.dumps({"uploaded_files": uploaded_files})
        )
        print(f"[TENANT DB] workspaceupload saved — chat={chat_id}")
        return str(row["id"])
    finally:
        await conn.close()


# ─────────────────────────────────────────
# TABLE: workspacethreeboom
# ─────────────────────────────────────────

async def save_three_boom_tenant(
    tenant_id: str, chat_id: str,
    organization_id: str, user_id: str, workspace_id: str,
    result: dict, section_id: str = None
):
    conn = await get_tenant_conn(tenant_id)
    classifications = result.get("classifications", [])
    first = next((c for c in classifications if c.get("batch_id")), {})
    batch_id = first.get("batch_id") or ''
    try:
        await conn.execute("""
            INSERT INTO workspacethreeboom
            (id, organization_id, user_id, workspace_id, section_id,
             chat_id, batch_id, product_name, plant,
             classifications, readiness_summary, extraction_summary, validate)
            VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13)
        """,
            str(uuid.uuid4()),
            organization_id, user_id, workspace_id, section_id,
            chat_id, batch_id,
            first.get("product_name"), first.get("plant"),
            json.dumps(classifications),
            json.dumps(result.get("readiness_summary", {})),
            json.dumps(result.get("extraction_summary", {})),
            json.dumps(result.get("validate", {}))
        )
        if batch_id:
            await conn.execute(
                "UPDATE workspaceupload SET batch_id=$1 WHERE chat_id=$2",
                batch_id, chat_id
            )
        print(f"[TENANT DB] workspacethreeboom saved — chat={chat_id}")
    finally:
        await conn.close()


# ─────────────────────────────────────────
# TABLE: workspaceexceptions
# ─────────────────────────────────────────

async def save_exceptions_tenant(
    tenant_id: str, chat_id: str,
    organization_id: str, user_id: str, workspace_id: str,
    result: dict, batch_id: str = None, section_id: str = None
):
    conn = await get_tenant_conn(tenant_id)
    s = result.get("summary", {})
    try:
        await conn.execute("""
            INSERT INTO workspaceexceptions
            (id, organization_id, user_id, workspace_id, section_id,
             chat_id, batch_id, total_exceptions, critical,
             major, minor, risk_level, response)
            VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13)
        """,
            str(uuid.uuid4()),
            organization_id, user_id, workspace_id, section_id,
            chat_id, batch_id or result.get("batch_id") or '', 
            s.get("total_exceptions", 0), s.get("critical", 0),
            s.get("major", 0), s.get("minor", 0),
            s.get("risk_level"), json.dumps(result)
        )
        print(f"[TENANT DB] workspaceexceptions saved — chat={chat_id}")
    finally:
        await conn.close()


# ─────────────────────────────────────────
# TABLE: workspacerac
# ─────────────────────────────────────────

async def save_rca_tenant(
    tenant_id: str, chat_id: str,
    organization_id: str, user_id: str, workspace_id: str,
    result: dict, batch_id: str = None, section_id: str = None
):
    conn = await get_tenant_conn(tenant_id)
    summary = result.get("investigation_summary", {})
    trip = result.get("trip_score", {})
    try:
        await conn.execute("""
            INSERT INTO workspacerac
            (id, organization_id, user_id, workspace_id, section_id,
             chat_id, batch_id, risk_level, trip_score,
             approval_status, confirmed, response)
            VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
        """,
            str(uuid.uuid4()),
            organization_id, user_id, workspace_id, section_id,
            chat_id, batch_id or result.get("batch_id") or '',
            summary.get("risk_level"),
            int(float(trip.get("score", 0)) * 100) if trip.get("score") else None,
            result.get("approval_status"), False,
            json.dumps(result)
        )
        print(f"[TENANT DB] workspacerac saved — chat={chat_id}")
    finally:
        await conn.close()


# ─────────────────────────────────────────
# TABLE: workspacecapa
# ─────────────────────────────────────────

async def save_capa_tenant(
    tenant_id: str, chat_id: str,
    organization_id: str, user_id: str, workspace_id: str,
    result: dict, batch_id: str = None, section_id: str = None
):
    conn = await get_tenant_conn(tenant_id)
    try:
        await conn.execute("""
            INSERT INTO workspacecapa
            (id, organization_id, user_id, workspace_id, section_id,
             chat_id, batch_id, deviation_id, severity,
             total_actions, plan_status, response)
            VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
        """,
            str(uuid.uuid4()),
            organization_id, user_id, workspace_id, section_id,
            chat_id, batch_id or result.get("batch_id") or '',
            result.get("deviation_id"), result.get("severity"),
            len(result.get("capa_actions", [])), "Pending",
            json.dumps(result)
        )
        print(f"[TENANT DB] workspacecapa saved — chat={chat_id}")
    finally:
        await conn.close()


# ─────────────────────────────────────────
# TABLE: workspacereports
# ─────────────────────────────────────────

async def save_report_tenant(
    tenant_id: str, chat_id: str,
    organization_id: str, user_id: str, workspace_id: str,
    result: dict, batch_id: str = None, section_id: str = None
):
    conn = await get_tenant_conn(tenant_id)
    meta = result.get("report_metadata", {})
    try:
        await conn.execute("""
            INSERT INTO workspacereports
            (id, organization_id, user_id, workspace_id, section_id,
             chat_id, batch_id, ref_id, severity, generated_date,
             pdf_filename, download_url, evidence_score, response)
            VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
        """,
            str(uuid.uuid4()),
            organization_id, user_id, workspace_id, section_id,
            chat_id, batch_id or meta.get("batch_id") or '',
            meta.get("ref_id"), meta.get("severity"),
            meta.get("generated_date"),
            result.get("pdf_filename"), result.get("download_url"),
            result.get("evidence_coverage_score"),
            json.dumps(result)
        )
        print(f"[TENANT DB] workspacereports saved — chat={chat_id}")
    finally:
        await conn.close()