from __future__ import annotations

import json

from .config import (
    INGEST_BATCH,
    ROOT_DIR,
    SOURCE_CODE,
    SOURCE_NAME,
    SOURCE_TYPE,
    SOURCE_YEAR,
)
from .models import ProjectCandidate


def ensure_classification_tables(connection) -> None:
    with connection.cursor() as cursor:
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS dossier_sources (
                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                source_code VARCHAR(50) NOT NULL UNIQUE,
                source_name VARCHAR(255) NOT NULL,
                source_path TEXT NOT NULL,
                source_year INTEGER,
                source_type VARCHAR(100) NOT NULL,
                notes TEXT,
                created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
            );
            """
        )
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS dossier_entries (
                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                source_id UUID NOT NULL REFERENCES dossier_sources(id) ON DELETE CASCADE,
                entry_name VARCHAR(255) NOT NULL,
                entry_path TEXT NOT NULL,
                entry_level INTEGER NOT NULL DEFAULT 1,
                entry_kind VARCHAR(50) NOT NULL,
                classification VARCHAR(100) NOT NULL,
                is_project_candidate BOOLEAN NOT NULL DEFAULT FALSE,
                metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
                created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
            );
            """
        )
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS dossier_project_candidates (
                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                source_id UUID NOT NULL REFERENCES dossier_sources(id) ON DELETE CASCADE,
                project_code VARCHAR(50) NOT NULL UNIQUE,
                project_name VARCHAR(255) NOT NULL,
                client_name VARCHAR(255),
                location TEXT,
                description TEXT,
                project_type VARCHAR(100),
                status VARCHAR(50) NOT NULL DEFAULT 'planning',
                priority VARCHAR(50) NOT NULL DEFAULT 'medium',
                progress_percent INTEGER NOT NULL DEFAULT 0,
                notes TEXT,
                metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
                created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
            );
            """
        )
    connection.commit()


def upsert_source(connection, root_dir) -> str:
    with connection.cursor() as cursor:
        cursor.execute(
            """
            INSERT INTO dossier_sources (
                source_code,
                source_name,
                source_path,
                source_year,
                source_type,
                notes
            )
            VALUES (%s, %s, %s, %s, %s, %s)
            ON CONFLICT (source_code) DO UPDATE
            SET source_name = EXCLUDED.source_name,
                source_path = EXCLUDED.source_path,
                source_year = EXCLUDED.source_year,
                source_type = EXCLUDED.source_type,
                notes = EXCLUDED.notes
            RETURNING id;
            """,
            (
                SOURCE_CODE,
                SOURCE_NAME,
                str(root_dir),
                SOURCE_YEAR,
                SOURCE_TYPE,
                "Kho hồ sơ hỗn hợp gồm công trình, hợp đồng thương mại, vật tư và hồ sơ dịch vụ.",
            ),
        )
        source_id = cursor.fetchone()["id"]
    connection.commit()
    return source_id


def replace_entry_rows(
    connection, source_id: str, rows: list[dict[str, object]]
) -> None:
    with connection.cursor() as cursor:
        cursor.execute("DELETE FROM dossier_entries WHERE source_id = %s", (source_id,))
        for row in rows:
            cursor.execute(
                """
                INSERT INTO dossier_entries (
                    source_id,
                    entry_name,
                    entry_path,
                    entry_level,
                    entry_kind,
                    classification,
                    is_project_candidate,
                    metadata
                )
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s::jsonb)
                """,
                (
                    source_id,
                    row["entry_name"],
                    str(ROOT_DIR / str(row["entry_name"])),
                    1,
                    row["entry_type"],
                    row["category"],
                    row["category"] == "owner_project_dossier",
                    json.dumps(
                        {
                            "review_scope": "top_level",
                            "source_code": SOURCE_CODE,
                            "ingest_batch": INGEST_BATCH,
                        },
                        ensure_ascii=False,
                    ),
                ),
            )
    connection.commit()


def replace_project_candidates(
    connection, source_id: str, candidates: list[ProjectCandidate]
) -> None:
    with connection.cursor() as cursor:
        cursor.execute(
            "DELETE FROM dossier_project_candidates WHERE source_id = %s", (source_id,)
        )
        for candidate in candidates:
            cursor.execute(
                """
                INSERT INTO dossier_project_candidates (
                    source_id,
                    project_code,
                    project_name,
                    client_name,
                    location,
                    description,
                    project_type,
                    status,
                    priority,
                    progress_percent,
                    notes,
                    metadata
                )
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s::jsonb)
                ON CONFLICT (project_code) DO UPDATE
                SET project_name = EXCLUDED.project_name,
                    client_name = EXCLUDED.client_name,
                    location = EXCLUDED.location,
                    description = EXCLUDED.description,
                    project_type = EXCLUDED.project_type,
                    status = EXCLUDED.status,
                    priority = EXCLUDED.priority,
                    progress_percent = EXCLUDED.progress_percent,
                    notes = EXCLUDED.notes,
                    metadata = EXCLUDED.metadata
                """,
                (
                    source_id,
                    candidate.project_code,
                    candidate.project_name,
                    candidate.client_name,
                    candidate.location,
                    candidate.description,
                    candidate.project_type,
                    candidate.status,
                    candidate.priority,
                    candidate.progress_percent,
                    candidate.notes,
                    json.dumps(
                        {
                            "ingest_source": "hd2026_manual_classification",
                            "source_code": SOURCE_CODE,
                            "ingest_batch": INGEST_BATCH,
                        },
                        ensure_ascii=False,
                    ),
                ),
            )
    connection.commit()