from __future__ import annotations

import json
import os
import uuid
from datetime import datetime, timezone
from typing import Any

from fastapi import HTTPException

try:
    import asyncpg  # type: ignore
except Exception:  # pragma: no cover
    asyncpg = None  # type: ignore


_DB_URL = os.getenv("DATABASE_URL", "")


def _now() -> datetime:
    return datetime.now(timezone.utc)


def _iso(value: datetime | None) -> str | None:
    return value.isoformat() if value else None


def _coerce_datetime(value: datetime | str | None) -> datetime | None:
    if value is None or isinstance(value, datetime):
        return value
    if isinstance(value, str) and value.strip():
        try:
            return datetime.fromisoformat(value)
        except ValueError:
            return None
    return None


def _db_ready() -> bool:
    return bool(_DB_URL and not _DB_URL.startswith("PLACEHOLDER") and asyncpg is not None)


class OracleActionService:
    async def ensure_schema(self) -> None:
        if not _db_ready():
            return
        assert asyncpg is not None
        conn = await asyncpg.connect(_DB_URL)
        try:
            await conn.execute(
                """
                CREATE TABLE IF NOT EXISTS oracle_actions (
                    action_id UUID PRIMARY KEY,
                    execution_id UUID,
                    tenant_id TEXT NOT NULL,
                    page_id UUID,
                    branch_id TEXT,
                    actor_id TEXT NOT NULL,
                    target_entity_type TEXT NOT NULL,
                    target_entity_id TEXT,
                    action_type TEXT NOT NULL,
                    status TEXT NOT NULL DEFAULT 'planned',
                    prompt TEXT,
                    workflow_dispatch JSONB NOT NULL DEFAULT '{}'::jsonb,
                    component_ids JSONB NOT NULL DEFAULT '[]'::jsonb,
                    writeback_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
                    result_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
                    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
                )
                """
            )
            await conn.execute(
                "CREATE INDEX IF NOT EXISTS idx_oracle_actions_execution ON oracle_actions(execution_id, created_at DESC)"
            )
            await conn.execute(
                "CREATE INDEX IF NOT EXISTS idx_oracle_actions_target ON oracle_actions(target_entity_type, target_entity_id, created_at DESC)"
            )
        finally:
            await conn.close()

    async def create_from_execution(
        self,
        *,
        execution: dict[str, Any],
        target_entity_type: str = "canvas_page",
        target_entity_id: str | None = None,
        action_type: str = "oracle_canvas_generation",
        writeback_payload: dict[str, Any] | None = None,
    ) -> dict[str, Any]:
        action = {
            "actionId": str(uuid.uuid4()),
            "executionId": execution.get("executionId"),
            "tenantId": execution.get("tenantId"),
            "pageId": execution.get("pageId"),
            "branchId": execution.get("branchId"),
            "actorId": execution.get("actorId"),
            "targetEntityType": target_entity_type,
            "targetEntityId": target_entity_id or execution.get("pageId"),
            "actionType": action_type,
            "status": "planned",
            "prompt": execution.get("prompt"),
            "workflowDispatch": execution.get("workflowDispatch") or {},
            "componentIds": execution.get("componentsCreated") or [],
            "writebackPayload": writeback_payload or {},
            "resultPayload": {},
            "createdAt": _now(),
            "updatedAt": _now(),
        }
        await self._persist_action(action)
        return action

    async def get_action(self, action_id: str) -> dict[str, Any] | None:
        if not _db_ready():
            return None
        assert asyncpg is not None
        conn = await asyncpg.connect(_DB_URL)
        try:
            row = await conn.fetchrow(
                """
                SELECT action_id, execution_id, tenant_id, page_id, branch_id, actor_id,
                       target_entity_type, target_entity_id, action_type, status, prompt,
                       workflow_dispatch, component_ids, writeback_payload, result_payload,
                       created_at, updated_at
                FROM oracle_actions
                WHERE action_id = $1::uuid
                """,
                action_id,
            )
        finally:
            await conn.close()
        return self._serialize(row) if row else None

    async def list_actions(self, *, status: str | None = None, limit: int = 50) -> list[dict[str, Any]]:
        if not _db_ready():
            return []
        assert asyncpg is not None
        conn = await asyncpg.connect(_DB_URL)
        try:
            if status:
                rows = await conn.fetch(
                    """
                    SELECT action_id, execution_id, tenant_id, page_id, branch_id, actor_id,
                           target_entity_type, target_entity_id, action_type, status, prompt,
                           workflow_dispatch, component_ids, writeback_payload, result_payload,
                           created_at, updated_at
                    FROM oracle_actions
                    WHERE status = $1
                    ORDER BY created_at DESC
                    LIMIT $2
                    """,
                    status,
                    limit,
                )
            else:
                rows = await conn.fetch(
                    """
                    SELECT action_id, execution_id, tenant_id, page_id, branch_id, actor_id,
                           target_entity_type, target_entity_id, action_type, status, prompt,
                           workflow_dispatch, component_ids, writeback_payload, result_payload,
                           created_at, updated_at
                    FROM oracle_actions
                    ORDER BY created_at DESC
                    LIMIT $1
                    """,
                    limit,
                )
        finally:
            await conn.close()
        return [self._serialize(row) for row in rows]

    async def apply_writeback(self, payload: dict[str, Any]) -> dict[str, Any]:
        if not _db_ready():
            raise HTTPException(status_code=503, detail="Oracle writeback store unavailable.")
        if payload["target_entity_type"] != "lead":
            raise HTTPException(status_code=422, detail="Only lead writebacks are supported in this pass.")

        assert asyncpg is not None
        await self.ensure_schema()
        conn = await asyncpg.connect(_DB_URL)
        try:
            target_lead_id = payload["target_entity_id"]
            action_id = payload["action_id"]
            writeback = payload["writeback_payload"]

            existing = await conn.fetchrow(
                "SELECT id, notes, metadata, kanban_status, qualification, score FROM leads WHERE id = $1",
                target_lead_id,
            )
            if existing is None:
                raise HTTPException(status_code=404, detail=f"Lead '{target_lead_id}' not found for Oracle writeback.")

            metadata = dict(existing["metadata"] or {})
            metadata_patch = writeback.get("metadata_patch") or {}
            if isinstance(metadata_patch, dict):
                metadata.update(metadata_patch)

            score = int(existing["score"] or 0) + int(writeback.get("score_delta") or 0)
            updated_notes = (existing["notes"] or "").strip()
            notes_append = writeback.get("notes_append")
            if notes_append:
                separator = "\n\n" if updated_notes else ""
                updated_notes = f"{updated_notes}{separator}{notes_append}"

            updated = await conn.fetchrow(
                """
                UPDATE leads
                SET notes = $2,
                    metadata = $3::jsonb,
                    kanban_status = COALESCE($4, kanban_status),
                    qualification = COALESCE($5, qualification),
                    score = $6,
                    updated_at = NOW()
                WHERE id = $1
                RETURNING id, notes, metadata, kanban_status, qualification, score, updated_at
                """,
                target_lead_id,
                updated_notes,
                json.dumps(metadata),
                writeback.get("kanban_status"),
                writeback.get("qualification"),
                max(score, 0),
            )

            oracle_message = writeback.get("oracle_message")
            if oracle_message:
                await conn.execute(
                    """
                    INSERT INTO chat_logs (id, lead_id, sender, channel, content, metadata, created_at)
                    VALUES ($1, $2, 'oracle', 'oracle', $3, $4::jsonb, NOW())
                    """,
                    str(uuid.uuid4()),
                    target_lead_id,
                    oracle_message,
                    json.dumps({"oracle_action_id": action_id, "writeback": True}),
                )

            result_payload = {
                "lead_id": updated["id"],
                "kanban_status": updated["kanban_status"],
                "qualification": updated["qualification"],
                "score": updated["score"],
                "updated_at": updated["updated_at"].isoformat() if updated["updated_at"] else None,
            }

            await conn.execute(
                """
                INSERT INTO oracle_actions (
                    action_id, execution_id, tenant_id, page_id, branch_id, actor_id,
                    target_entity_type, target_entity_id, action_type, status, prompt,
                    workflow_dispatch, component_ids, writeback_payload, result_payload,
                    created_at, updated_at
                )
                VALUES (
                    $1::uuid, NULL, $2, NULL, NULL, $3,
                    $4, $5, $6, 'applied', NULL,
                    '{}'::jsonb, '[]'::jsonb, $7::jsonb, $8::jsonb,
                    NOW(), NOW()
                )
                ON CONFLICT (action_id)
                DO UPDATE SET
                    status = 'applied',
                    writeback_payload = EXCLUDED.writeback_payload,
                    result_payload = EXCLUDED.result_payload,
                    updated_at = NOW()
                """,
                action_id,
                payload.get("tenant_id", "tenant_velocity"),
                payload.get("actor_id", "oracle_operator"),
                payload["target_entity_type"],
                target_lead_id,
                payload.get("action_type", "lead_writeback"),
                json.dumps(writeback),
                json.dumps(result_payload),
            )
        finally:
            await conn.close()

        return {
            "actionId": action_id,
            "status": "applied",
            "targetEntityType": payload["target_entity_type"],
            "targetEntityId": payload["target_entity_id"],
            "resultPayload": result_payload,
        }

    async def _persist_action(self, action: dict[str, Any]) -> None:
        if not _db_ready():
            return
        await self.ensure_schema()
        assert asyncpg is not None
        conn = await asyncpg.connect(_DB_URL)
        try:
            await conn.execute(
                """
                INSERT INTO oracle_actions (
                    action_id, execution_id, tenant_id, page_id, branch_id, actor_id,
                    target_entity_type, target_entity_id, action_type, status, prompt,
                    workflow_dispatch, component_ids, writeback_payload, result_payload,
                    created_at, updated_at
                )
                VALUES (
                    $1::uuid, $2::uuid, $3, $4::uuid, $5, $6,
                    $7, $8, $9, $10, $11,
                    $12::jsonb, $13::jsonb, $14::jsonb, $15::jsonb,
                    $16::timestamptz, $17::timestamptz
                )
                ON CONFLICT (action_id)
                DO UPDATE SET
                    status = EXCLUDED.status,
                    workflow_dispatch = EXCLUDED.workflow_dispatch,
                    component_ids = EXCLUDED.component_ids,
                    writeback_payload = EXCLUDED.writeback_payload,
                    result_payload = EXCLUDED.result_payload,
                    updated_at = EXCLUDED.updated_at
                """,
                action["actionId"],
                action.get("executionId"),
                action["tenantId"],
                action.get("pageId"),
                action.get("branchId"),
                action["actorId"],
                action["targetEntityType"],
                action.get("targetEntityId"),
                action["actionType"],
                action["status"],
                action.get("prompt"),
                json.dumps(action.get("workflowDispatch") or {}),
                json.dumps(action.get("componentIds") or []),
                json.dumps(action.get("writebackPayload") or {}),
                json.dumps(action.get("resultPayload") or {}),
                _coerce_datetime(action["createdAt"]),
                _coerce_datetime(action["updatedAt"]),
            )
        finally:
            await conn.close()

    @staticmethod
    def _serialize(row: Any) -> dict[str, Any]:
        return {
            "actionId": str(row["action_id"]),
            "executionId": str(row["execution_id"]) if row["execution_id"] else None,
            "tenantId": row["tenant_id"],
            "pageId": str(row["page_id"]) if row["page_id"] else None,
            "branchId": row["branch_id"],
            "actorId": row["actor_id"],
            "targetEntityType": row["target_entity_type"],
            "targetEntityId": row["target_entity_id"],
            "actionType": row["action_type"],
            "status": row["status"],
            "prompt": row["prompt"],
            "workflowDispatch": row["workflow_dispatch"] or {},
            "componentIds": row["component_ids"] or [],
            "writebackPayload": row["writeback_payload"] or {},
            "resultPayload": row["result_payload"] or {},
            "createdAt": _iso(row["created_at"]),
            "updatedAt": _iso(row["updated_at"]),
        }


oracle_action_service = OracleActionService()
