import os
import json
import psycopg2
from dotenv import load_dotenv

load_dotenv(override=True)

DB_CONFIG = {
    "host": os.getenv("DB_HOST", "localhost"),
    "port": os.getenv("DB_PORT", "5432"),
    "database": os.getenv("DB_NAME", "fyndo"),
    "user": os.getenv("DB_USER", "postgres"),
    "password": os.getenv("DB_PASSWORD", "")
}


def get_connection():
    return psycopg2.connect(**DB_CONFIG)


# ─────────────────────────────────────────
# 1. UPLOADS — POST /upload
# ─────────────────────────────────────────

def save_upload(uploaded_files: list, chat_id: str = None) -> int:
    conn = get_connection()
    cur = conn.cursor()
    filenames = [f.get("filename") for f in uploaded_files]
    cur.execute("""
        INSERT INTO uploads (chat_id, filenames, total_files, response)
        VALUES (%s, %s, %s, %s)
        RETURNING id
    """, (
        chat_id,
        json.dumps(filenames),
        len(uploaded_files),
        json.dumps({"uploaded_files": uploaded_files})
    ))
    row_id = cur.fetchone()[0]
    conn.commit()
    cur.close()
    conn.close()
    print(f"[DB] uploads saved — chat_id={chat_id}")
    return row_id


# ─────────────────────────────────────────
# 2. THREE BOOM — POST /agent/validate
# ─────────────────────────────────────────

def save_three_boom(chat_id: str, result: dict):
    conn = get_connection()
    cur = conn.cursor()
    classifications = result.get("classifications", [])
    first = next((c for c in classifications if c.get("batch_id")), {})
    batch_id = first.get("batch_id")

    cur.execute("""
        INSERT INTO three_boom
        (chat_id, batch_id, product_name, plant,
         classifications, readiness_summary, extraction_summary, validate)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        ON CONFLICT (chat_id) DO UPDATE SET
            batch_id = EXCLUDED.batch_id,
            product_name = EXCLUDED.product_name,
            plant = EXCLUDED.plant,
            classifications = EXCLUDED.classifications,
            readiness_summary = EXCLUDED.readiness_summary,
            extraction_summary = EXCLUDED.extraction_summary,
            validate = EXCLUDED.validate
    """, (
        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", {}))
    ))

    # Update batch_id in uploads
    if batch_id:
        cur.execute(
            "UPDATE uploads SET batch_id = %s WHERE chat_id = %s",
            (batch_id, chat_id)
        )

    conn.commit()
    cur.close()
    conn.close()
    print(f"[DB] three_boom saved — chat_id={chat_id} batch={batch_id}")


# ─────────────────────────────────────────
# 3. EXCEPTIONS — POST /agent/exceptions
# ─────────────────────────────────────────

def save_exceptions(chat_id: str, result: dict):
    conn = get_connection()
    cur = conn.cursor()
    summary = result.get("summary", {})
    cur.execute("""
        INSERT INTO exceptions
        (chat_id, batch_id, total_exceptions, critical,
         major, minor, risk_level, response)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        ON CONFLICT (chat_id) DO UPDATE SET
            batch_id = EXCLUDED.batch_id,
            total_exceptions = EXCLUDED.total_exceptions,
            critical = EXCLUDED.critical,
            major = EXCLUDED.major,
            minor = EXCLUDED.minor,
            risk_level = EXCLUDED.risk_level,
            response = EXCLUDED.response
    """, (
        chat_id,
        result.get("batch_id"),
        summary.get("total_exceptions", 0),
        summary.get("critical", 0),
        summary.get("major", 0),
        summary.get("minor", 0),
        summary.get("risk_level"),
        json.dumps(result)
    ))
    conn.commit()
    cur.close()
    conn.close()
    print(f"[DB] exceptions saved — chat_id={chat_id} total={summary.get('total_exceptions', 0)}")


# ─────────────────────────────────────────
# 4. RCA — POST /agent/rca
# ─────────────────────────────────────────

def save_rca(chat_id: str, result: dict):
    conn = get_connection()
    cur = conn.cursor()
    summary = result.get("investigation_summary", {})
    trip = result.get("trip_score", {})
    cur.execute("""
        INSERT INTO rca
        (chat_id, batch_id, risk_level, trip_score,
         approval_status, confirmed, response)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
    """, (
        chat_id,
        result.get("batch_id"),
        summary.get("risk_level"),
        trip.get("score"),
        result.get("approval_status"),
        False,
        json.dumps(result)
    ))
    conn.commit()
    cur.close()
    conn.close()
    print(f"[DB] rca saved — chat_id={chat_id} risk={summary.get('risk_level')}")


# ─────────────────────────────────────────
# 4b. RCA CONFIRM — POST /rca-confirm
# ─────────────────────────────────────────

def update_rca_confirmation(chat_id: str, payload: dict):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("""
        UPDATE rca SET
            confirmed    = %s,
            confirmed_by = %s,
            confirmed_at = %s,
            cause_id     = %s,
            role         = %s
        WHERE chat_id = %s
    """, (
        True,
        payload.get("confirmed_by"),
        payload.get("confirmed_at"),
        payload.get("cause_id"),
        payload.get("role"),
        chat_id
    ))
    conn.commit()
    cur.close()
    conn.close()
    print(f"[DB] rca confirmed — chat_id={chat_id} by={payload.get('confirmed_by')}")


# ─────────────────────────────────────────
# 5. CAPA — POST /agent/capa
# ─────────────────────────────────────────

def save_capa(chat_id: str, result: dict):
    conn = get_connection()
    cur = conn.cursor()
    actions = result.get("capa_actions", [])
    cur.execute("""
        INSERT INTO capa
        (chat_id, batch_id, deviation_id, severity,
         total_actions, plan_status, response)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
    """, (
        chat_id,
        result.get("batch_id"),
        result.get("deviation_id"),
        result.get("severity"),
        len(actions),
        "Pending",
        json.dumps(result)
    ))
    conn.commit()
    cur.close()
    conn.close()
    print(f"[DB] capa saved — chat_id={chat_id} actions={len(actions)}")


# ─────────────────────────────────────────
# 5b. CAPA APPROVE — POST /capa-approve
# ─────────────────────────────────────────

def update_capa_approval(chat_id: str, payload: dict):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("""
        UPDATE capa SET
            plan_status        = %s,
            approved_by        = %s,
            approved_at        = %s,
            send_for_execution = %s
        WHERE chat_id = %s
    """, (
        "Approved",
        payload.get("approved_by"),
        payload.get("approved_at"),
        payload.get("send_for_execution", False),
        chat_id
    ))
    conn.commit()
    cur.close()
    conn.close()
    print(f"[DB] capa approved — chat_id={chat_id} by={payload.get('approved_by')}")


# ─────────────────────────────────────────
# 6. REPORTS — POST /agent/report
# ─────────────────────────────────────────

def save_report(chat_id: str, result: dict):
    conn = get_connection()
    cur = conn.cursor()
    meta = result.get("report_metadata", {})
    cur.execute("""
        INSERT INTO reports
        (chat_id, batch_id, ref_id, severity,
         generated_date, pdf_filename, download_url,
         evidence_score, response)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
        ON CONFLICT (chat_id) DO UPDATE SET
            batch_id = EXCLUDED.batch_id,
            ref_id = EXCLUDED.ref_id,
            severity = EXCLUDED.severity,
            generated_date = EXCLUDED.generated_date,
            pdf_filename = EXCLUDED.pdf_filename,
            download_url = EXCLUDED.download_url,
            evidence_score = EXCLUDED.evidence_score,
            response = EXCLUDED.response
    """, (
        chat_id,
        meta.get("batch_id"),
        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)
    ))
    conn.commit()
    cur.close()
    conn.close()
    print(f"[DB] report saved — chat_id={chat_id} ref={meta.get('ref_id')}")