"""Database/table/column/join configuration CRUD endpoints."""

from __future__ import annotations

from fastapi import APIRouter, Request, HTTPException
from pydantic import BaseModel

from knowledge.table_registry import TableRegistry
from knowledge.column_registry import ColumnRegistry

router = APIRouter(prefix="/config", tags=["config"])


@router.get("/db-presets")
async def get_db_presets():
    """Return database type presets for the frontend connection form."""
    from database.db_presets import presets_for_api
    return presets_for_api()


# --- Database Connections ---

class ConnectionCreate(BaseModel):
    name: str
    db_type: str
    source_system: str | None = None
    connection_string: str | None = None
    # For discovered connections: template + credentials
    connection_string_template: str | None = None
    username: str | None = None
    password: str | None = None
    # For structured creation (preset-driven form)
    host: str | None = None
    port: int | str | None = None
    database: str | None = None
    driver: str | None = None
    extra_params: str | None = None


class ConnectionUpdate(BaseModel):
    name: str | None = None
    is_active: bool | None = None
    connection_string: str | None = None


@router.get("/connections")
async def list_connections(request: Request):
    db = request.app.state.db
    rows = await db.execute(
        "SELECT id, name, db_type, source_system, is_active, created_at, updated_at FROM v3q_config.database_connections ORDER BY name"
    )
    return rows


@router.post("/connections")
async def create_connection(request: Request, body: ConnectionCreate):
    db = request.app.state.db

    # Build connection string: direct, discovered template, or structured params
    conn_str = body.connection_string
    if not conn_str and body.connection_string_template:
        from discovery.odbc_discovery import ConnectionDiscovery
        conn_str = ConnectionDiscovery.fill_credentials(
            body.connection_string_template,
            body.username or "",
            body.password or "",
        )
    if not conn_str and body.host:
        from database.db_presets import build_connection_string
        conn_str = build_connection_string(
            db_type=body.db_type,
            host=body.host,
            port=body.port or "",
            database=body.database or "",
            username=body.username or "",
            password=body.password or "",
            driver=body.driver,
            extra_params=body.extra_params,
        )

    if not conn_str:
        raise HTTPException(400, "Provide connection_string, connection_string_template with credentials, or host with credentials")

    return await db.fetch_one("""
        INSERT INTO v3q_config.database_connections (name, db_type, source_system, connection_string)
        VALUES (:name, :db_type, :source_system, :connection_string)
        RETURNING id, name, db_type, source_system, is_active, created_at
    """, {
        "name": body.name,
        "db_type": body.db_type,
        "source_system": body.source_system,
        "connection_string": conn_str,
    })


@router.get("/connections/{connection_id}")
async def get_connection(request: Request, connection_id: str):
    db = request.app.state.db
    row = await db.fetch_one(
        "SELECT id, name, db_type, source_system, is_active, created_at, updated_at FROM v3q_config.database_connections WHERE id = :id",
        {"id": connection_id},
    )
    if not row:
        raise HTTPException(404, "Connection not found")
    return row


@router.put("/connections/{connection_id}")
async def update_connection(request: Request, connection_id: str, body: ConnectionUpdate):
    db = request.app.state.db
    updates = {k: v for k, v in body.model_dump().items() if v is not None}
    if not updates:
        raise HTTPException(400, "No fields to update")
    set_clause = ", ".join(f"{k} = :{k}" for k in updates)
    updates["id"] = connection_id
    return await db.fetch_one(
        f"UPDATE v3q_config.database_connections SET {set_clause}, updated_at = now() WHERE id = :id RETURNING id, name, db_type, source_system, is_active",
        updates,
    )


@router.delete("/connections/{connection_id}")
async def delete_connection(request: Request, connection_id: str):
    db = request.app.state.db
    await db.execute_write(
        "DELETE FROM v3q_config.database_connections WHERE id = :id",
        {"id": connection_id},
    )
    return {"status": "deleted"}


@router.post("/connections/{connection_id}/test")
async def test_connection(request: Request, connection_id: str):
    from database.remote_executor import RemoteExecutor
    db = request.app.state.db
    conn = await db.fetch_one(
        "SELECT * FROM v3q_config.database_connections WHERE id = :id",
        {"id": connection_id},
    )
    if not conn:
        raise HTTPException(404, "Connection not found")

    executor = RemoteExecutor(conn["connection_string"], conn["db_type"])
    result = await executor.test_connection()
    return {
        "status": "ok" if result["ok"] else "failed",
        "error": result["error"],
    }


@router.post("/connections/{connection_id}/discover")
async def discover_schema(request: Request, connection_id: str):
    from discovery.schema_discovery import SchemaDiscovery
    db = request.app.state.db
    tr = TableRegistry(db)
    cr = ColumnRegistry(db)
    discovery = SchemaDiscovery(db, tr, cr)
    try:
        return await discovery.discover_connection(connection_id)
    except Exception as e:
        raise HTTPException(500, f"Discovery failed: {e}")


@router.get("/connections/{connection_id}/browse")
async def browse_remote_tables(
    request: Request,
    connection_id: str,
    schema: str | None = None,
    table: str | None = None,
    limit: int = 500,
):
    """Browse tables on remote DB (read-only INFORMATION_SCHEMA query)."""
    from discovery.schema_discovery import SchemaDiscovery
    db = request.app.state.db
    tr = TableRegistry(db)
    cr = ColumnRegistry(db)
    discovery = SchemaDiscovery(db, tr, cr)
    try:
        return await discovery.browse_tables(
            connection_id, schema_filter=schema, table_filter=table, limit=limit,
        )
    except Exception as e:
        import traceback
        traceback.print_exc()
        raise HTTPException(500, f"Browse failed: {e}")


class RegisterTablesBody(BaseModel):
    selections: list[dict]  # [{schema: str, table: str}]


@router.post("/connections/{connection_id}/register-tables")
async def register_selected_tables(request: Request, connection_id: str, body: RegisterTablesBody):
    """Register selected tables (metadata only — no data copy)."""
    from discovery.schema_discovery import SchemaDiscovery
    db = request.app.state.db
    tr = TableRegistry(db)
    cr = ColumnRegistry(db)
    discovery = SchemaDiscovery(db, tr, cr)
    try:
        return await discovery.register_selected_tables(connection_id, body.selections)
    except Exception as e:
        raise HTTPException(500, f"Registration failed: {e}")


@router.get("/overview")
async def get_config_overview(request: Request):
    """Config health dashboard — all queries against local PG only."""
    db = request.app.state.db

    # Registry aggregates
    reg_stats = await db.fetch_one("""
        SELECT
            COUNT(*) AS total_tables,
            COUNT(*) FILTER (WHERE is_synced = true) AS synced_tables,
            COUNT(*) FILTER (WHERE is_synced = false) AS unsynced_tables,
            COUNT(*) FILTER (WHERE sync_schedule IS NOT NULL AND sync_schedule != '') AS scheduled_tables,
            COALESCE(SUM(row_count), 0) AS total_rows,
            MAX(last_synced_at) AS last_sync_time
        FROM v3q_config.table_registry
    """)

    # Per-connection breakdown
    connections = await db.execute("""
        SELECT
            dc.id, dc.name, dc.db_type, dc.source_system, dc.is_active,
            COUNT(tr.id) AS table_count,
            COUNT(tr.id) FILTER (WHERE tr.is_synced = true) AS synced_count,
            MAX(tr.last_synced_at) AS last_sync_at
        FROM v3q_config.database_connections dc
        LEFT JOIN v3q_config.table_registry tr ON tr.connection_id = dc.id
        GROUP BY dc.id, dc.name, dc.db_type, dc.source_system, dc.is_active
        ORDER BY dc.name
    """)

    # Local storage stats (v3q_data schema in internal PG)
    local_tables = await db.execute("""
        SELECT
            t.tablename AS table_name,
            pg_total_relation_size(quote_ident('v3q_data') || '.' || quote_ident(t.tablename)) AS size_bytes,
            c.reltuples::bigint AS row_estimate
        FROM pg_catalog.pg_tables t
        JOIN pg_catalog.pg_class c ON c.relname = t.tablename
        JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace AND n.nspname = 'v3q_data'
        WHERE t.schemaname = 'v3q_data'
        ORDER BY size_bytes DESC
    """)

    total_size = sum(t["size_bytes"] for t in local_tables) if local_tables else 0

    # Unsynced tables needing attention
    unsynced = await db.execute("""
        SELECT
            tr.id, tr.source_table, tr.local_table, tr.source_system,
            tr.last_synced_at, tr.sync_schedule, tr.row_count,
            dc.name AS connection_name
        FROM v3q_config.table_registry tr
        LEFT JOIN v3q_config.database_connections dc ON dc.id = tr.connection_id
        WHERE tr.is_synced = false
        ORDER BY tr.source_table
    """)

    return {
        "registry": dict(reg_stats) if reg_stats else {
            "total_tables": 0, "synced_tables": 0, "unsynced_tables": 0,
            "scheduled_tables": 0, "total_rows": 0, "last_sync_time": None,
        },
        "connections": connections,
        "local_tables": local_tables,
        "schema_totals": {
            "table_count": len(local_tables),
            "total_size_bytes": total_size,
        },
        "unsynced_tables": unsynced,
    }


# --- Table Registry ---

@router.get("/tables")
async def list_tables(request: Request):
    tr = TableRegistry(request.app.state.db)
    return await tr.list_all()


@router.get("/tables/{table_id}")
async def get_table(request: Request, table_id: str):
    tr = TableRegistry(request.app.state.db)
    table = await tr.get(table_id)
    if not table:
        raise HTTPException(404, "Table not found")
    cr = ColumnRegistry(request.app.state.db)
    columns = await cr.list_by_table(table_id)
    return {**table, "columns": columns}


class TableUpdate(BaseModel):
    display_name: str | None = None
    business_description: str | None = None
    sync_schedule: str | None = None
    sync_filter: str | None = None


@router.put("/tables/{table_id}")
async def update_table(request: Request, table_id: str, body: TableUpdate):
    tr = TableRegistry(request.app.state.db)
    updates = {k: v for k, v in body.model_dump().items() if v is not None}
    return await tr.update(table_id, **updates)


@router.delete("/tables/{table_id}")
async def delete_table(request: Request, table_id: str):
    tr = TableRegistry(request.app.state.db)
    await tr.delete(table_id)
    return {"status": "deleted"}


# --- Column Registry ---

class ColumnUpdate(BaseModel):
    display_name: str | None = None
    business_description: str | None = None


@router.put("/columns/{column_id}")
async def update_column(request: Request, column_id: str, body: ColumnUpdate):
    cr = ColumnRegistry(request.app.state.db)
    updates = {k: v for k, v in body.model_dump().items() if v is not None}
    return await cr.update(column_id, **updates)


# --- Cross-DB Joins ---

class JoinCreate(BaseModel):
    left_table_id: str
    left_column: str
    right_table_id: str
    right_column: str
    join_type: str = "INNER"
    description: str | None = None


@router.get("/joins")
async def list_joins(request: Request):
    db = request.app.state.db
    return await db.execute("""
        SELECT j.*, lt.local_table AS left_table, rt.local_table AS right_table
        FROM v3q_config.cross_db_joins j
        JOIN v3q_config.table_registry lt ON lt.id = j.left_table_id
        JOIN v3q_config.table_registry rt ON rt.id = j.right_table_id
        ORDER BY j.created_at DESC
    """)


@router.post("/joins")
async def create_join(request: Request, body: JoinCreate):
    db = request.app.state.db
    return await db.fetch_one("""
        INSERT INTO v3q_config.cross_db_joins
            (left_table_id, left_column, right_table_id, right_column, join_type, description)
        VALUES (:left_table_id, :left_column, :right_table_id, :right_column, :join_type, :description)
        RETURNING *
    """, body.model_dump())


@router.delete("/joins/{join_id}")
async def delete_join(request: Request, join_id: str):
    db = request.app.state.db
    await db.execute_write(
        "DELETE FROM v3q_config.cross_db_joins WHERE id = :id",
        {"id": join_id},
    )
    return {"status": "deleted"}


@router.post("/joins/infer")
async def infer_joins(request: Request):
    from discovery.relationship_inference import RelationshipInference
    db = request.app.state.db
    inference = RelationshipInference(db)
    relationships = await inference.infer_relationships()
    saved = await inference.save_inferred(relationships)
    return {"inferred": len(relationships), "saved": saved, "relationships": relationships}


# --- App Settings ---

@router.get("/settings")
async def get_settings(request: Request):
    db = request.app.state.db
    rows = await db.execute("SELECT * FROM v3q_config.app_settings ORDER BY key")
    return {r["key"]: r["value"] for r in rows}


class SettingUpdate(BaseModel):
    key: str
    value: dict | str | int | bool | list


@router.put("/settings")
async def update_setting(request: Request, body: SettingUpdate):
    import json
    db = request.app.state.db
    await db.execute_write("""
        INSERT INTO v3q_config.app_settings (key, value, updated_at)
        VALUES (:key, :value, now())
        ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value, updated_at = now()
    """, {"key": body.key, "value": json.dumps(body.value)})
    return {"status": "ok"}


# --- Domain Knowledge ---

class DomainKnowledgeUpdate(BaseModel):
    """Domain-specific knowledge that helps the LLMs understand the data.

    Examples:
    - "The DUNS number column for suppliers is called KRAUS"
    - "Only Mandant 001 is relevant for our analysis"
    - "Empty fields may contain spaces instead of NULL"
    """
    items: list[str]


@router.get("/domain-knowledge")
async def get_domain_knowledge(request: Request):
    """Get stored domain knowledge items."""
    import json
    db = request.app.state.db
    row = await db.fetch_one(
        "SELECT value FROM v3q_config.app_settings WHERE key = 'domain_knowledge'",
    )
    if row:
        data = json.loads(row["value"]) if isinstance(row["value"], str) else row["value"]
        return {"items": data if isinstance(data, list) else []}
    return {"items": []}


@router.put("/domain-knowledge")
async def update_domain_knowledge(request: Request, body: DomainKnowledgeUpdate):
    """Store domain-specific knowledge items."""
    import json
    db = request.app.state.db
    await db.execute_write("""
        INSERT INTO v3q_config.app_settings (key, value, updated_at)
        VALUES ('domain_knowledge', :value, now())
        ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value, updated_at = now()
    """, {"value": json.dumps(body.items)})
    return {"status": "ok", "count": len(body.items)}


# --- Maintenance ---

@router.post("/cleanup-patterns")
async def cleanup_stale_patterns(request: Request):
    """Delete query patterns referencing tables that no longer exist."""
    db = request.app.state.db

    # Get all existing local_table names
    tables = await db.execute("SELECT local_table FROM v3q_config.table_registry WHERE local_table IS NOT NULL")
    existing = {r["local_table"] for r in tables}

    # Get all patterns
    patterns = await db.execute("SELECT id, tables_used FROM v3q_config.query_patterns")

    deleted = []
    for p in patterns:
        tables_used = p["tables_used"] or []
        # Check if ANY referenced table still exists (strip v3q_data. prefix)
        still_valid = False
        for t in tables_used:
            clean = t.replace("v3q_data.", "")
            if clean in existing:
                still_valid = True
                break
        if not still_valid:
            await db.execute_write(
                "DELETE FROM v3q_config.query_patterns WHERE id = :id",
                {"id": str(p["id"])},
            )
            deleted.append(", ".join(tables_used))

    return {"deleted": len(deleted), "details": deleted}


@router.post("/tables/{table_id}/enrich")
async def enrich_table_columns(request: Request, table_id: str):
    """Manually trigger column enrichment for an existing table."""
    from local_llm.column_enricher import ColumnEnricher
    from local_llm.ollama_client import OllamaClient

    db = request.app.state.db
    tr = TableRegistry(db)
    cr = ColumnRegistry(db)

    table = await tr.get(table_id)
    if not table:
        raise HTTPException(404, "Table not found")

    ollama = request.app.state.ollama
    if not ollama:
        raise HTTPException(503, "Ollama not available")

    enricher = ColumnEnricher(ollama, db, cr)

    # Load domain hints
    domain_hints = None
    try:
        import json
        row = await db.fetch_one(
            "SELECT value FROM v3q_config.app_settings WHERE key = 'domain_knowledge'",
        )
        if row:
            data = json.loads(row["value"]) if isinstance(row["value"], str) else row["value"]
            if isinstance(data, list):
                domain_hints = "\n".join(f"- {item}" for item in data)
    except Exception:
        pass

    enriched = await enricher.enrich_table(
        table_id=table_id,
        local_table=table["local_table"],
        source_system=table.get("source_system"),
        domain_hints=domain_hints,
    )
    return {"enriched": len(enriched), "columns": list(enriched.keys())}


# --- Audit Log ---

@router.get("/audit")
async def get_audit_log(request: Request, limit: int = 50):
    from security.audit_log import AuditLog
    audit = AuditLog(request.app.state.db)
    return await audit.list_recent(limit=limit)


@router.get("/audit/stats")
async def get_audit_stats(request: Request):
    from security.audit_log import AuditLog
    audit = AuditLog(request.app.state.db)
    return await audit.get_stats()
