"""
Deploy Odoo CRM sync for the live Richard/Chatwoot n8n workflows.

This script patches n8n through its Postgres metadata because the live
Chatwoot workflow is the source of truth and is not fully represented by the
older create_*.py files.

It creates or updates:
- fibro-sync-odoo-crm
- side branches in richard-chatwoot-fibromuebles
- side branch in fibro-tool-prepare-budget

It writes workflow backups to the OS temp directory, outside the git repo.
"""

from __future__ import annotations

import json
import subprocess
import tempfile
import uuid
from datetime import datetime
from pathlib import Path


MAIN_WORKFLOW_ID = "XoRXrhG7xb5jfHEl"
PREPARE_BUDGET_WORKFLOW_ID = "v4L6jrljF9vRQ3II"
SYNC_WORKFLOW_NAME = "fibro-sync-odoo-crm"
DEFAULT_SYNC_WORKFLOW_ID = "fibroSyncOdooCRM"
PROJECT_ID_FALLBACK_WORKFLOW_ID = MAIN_WORKFLOW_ID

WORKFLOW_DIR = Path(__file__).resolve().parent
CODE_DIR = WORKFLOW_DIR / "code-nodes"


def run(cmd: list[str], *, input_text: str | None = None, capture: bool = True) -> str:
    result = subprocess.run(
        cmd,
        input=input_text,
        text=True,
        capture_output=capture,
        check=True,
        encoding="utf-8",
    )
    return result.stdout if capture else ""


def get_n8n_db_env() -> tuple[str, str]:
    raw = run(["docker", "inspect", "eddos-n8n", "--format", "{{json .Config.Env}}"]).strip()
    env_items = json.loads(raw)
    env = {}
    for item in env_items:
        key, _, value = item.partition("=")
        env[key] = value
    user = env.get("DB_POSTGRESDB_USER")
    db = env.get("DB_POSTGRESDB_DATABASE")
    if not user or not db:
        raise RuntimeError("No pude resolver DB_POSTGRESDB_USER/DB_POSTGRESDB_DATABASE desde eddos-n8n")
    return user, db


DB_USER, DB_NAME = get_n8n_db_env()


def psql(sql: str, *, tuples_only: bool = True) -> str:
    cmd = ["docker", "exec", "-i", "n8n-db", "psql", "-U", DB_USER, "-d", DB_NAME]
    if tuples_only:
        cmd.append("-At")
    return run(cmd, input_text=sql)


def quote_sql(value: str) -> str:
    return "'" + value.replace("'", "''") + "'"


def json_sql(value) -> str:
    text = json.dumps(value, ensure_ascii=True)
    if "$odoo_sync_json$" in text:
        raise ValueError("JSON contiene el delimitador dollar-quote")
    return f"$odoo_sync_json${text}$odoo_sync_json$::json"


def read_code(filename: str) -> str:
    return (CODE_DIR / filename).read_text(encoding="utf-8")


def fetch_workflow(workflow_id: str) -> dict:
    sql = f"""
select json_build_object(
  'id', id,
  'name', name,
  'active', active,
  'nodes', nodes,
  'connections', connections,
  'settings', settings,
  'staticData', "staticData",
  'pinData', "pinData",
  'versionId', "versionId",
  'activeVersionId', "activeVersionId",
  'versionCounter', "versionCounter",
  'description', description,
  'parentFolderId', "parentFolderId",
  'meta', meta
)::text
from workflow_entity
where id={quote_sql(workflow_id)};
"""
    raw = psql(sql).strip()
    if not raw:
        raise RuntimeError(f"No existe workflow_entity id={workflow_id}")
    return json.loads(raw)


def save_backups(workflow_ids: list[str]) -> Path:
    stamp = datetime.now().strftime("%Y%m%d-%H%M%S")
    backup_dir = Path(tempfile.gettempdir()) / "fibro-n8n-workflow-backups" / stamp
    backup_dir.mkdir(parents=True, exist_ok=True)
    for workflow_id in workflow_ids:
        try:
            workflow = fetch_workflow(workflow_id)
        except RuntimeError:
            continue
        path = backup_dir / f"{workflow_id}-{workflow['name']}.json"
        path.write_text(json.dumps(workflow, ensure_ascii=False, indent=2), encoding="utf-8")
    return backup_dir


def get_project_id() -> str:
    sql = f"""
select "projectId"
from shared_workflow
where "workflowId"={quote_sql(PROJECT_ID_FALLBACK_WORKFLOW_ID)}
limit 1;
"""
    project_id = psql(sql).strip()
    if not project_id:
        raise RuntimeError("No pude resolver projectId desde shared_workflow")
    return project_id


def resolve_sync_workflow_id() -> str:
    sql = f"""
select id
from workflow_entity
where id={quote_sql(DEFAULT_SYNC_WORKFLOW_ID)} or name={quote_sql(SYNC_WORKFLOW_NAME)}
order by case when id={quote_sql(DEFAULT_SYNC_WORKFLOW_ID)} then 0 else 1 end
limit 1;
"""
    existing = psql(sql).strip()
    return existing or DEFAULT_SYNC_WORKFLOW_ID


def make_sync_workflow(sync_workflow_id: str) -> dict:
    sync_code = read_code("sync-odoo-crm.js")
    return {
        "id": sync_workflow_id,
        "name": SYNC_WORKFLOW_NAME,
        "active": True,
        "nodes": [
            {
                "parameters": {"inputSource": "passthrough"},
                "id": "odoo-sync-trigger",
                "name": "When Executed by Another Workflow",
                "type": "n8n-nodes-base.executeWorkflowTrigger",
                "typeVersion": 1.1,
                "position": [240, 300],
            },
            {
                "parameters": {"jsCode": sync_code},
                "id": "odoo-sync-code",
                "name": "Sync Odoo CRM",
                "type": "n8n-nodes-base.code",
                "typeVersion": 2,
                "position": [520, 300],
                "continueOnFail": True,
            },
        ],
        "connections": {
            "When Executed by Another Workflow": {
                "main": [[{"node": "Sync Odoo CRM", "type": "main", "index": 0}]]
            }
        },
        "settings": {
            "executionOrder": "v1",
            "callerPolicy": "workflowsFromSameOwner",
            "availableInMCP": False,
        },
        "description": "Espeja Chatwoot/Richard hacia Odoo CRM. Chatwoot sigue siendo el canal de WhatsApp.",
    }


def upsert_sync_workflow(sync_workflow_id: str, project_id: str) -> None:
    workflow = make_sync_workflow(sync_workflow_id)
    version_id = str(uuid.uuid4())
    exists = psql(f"select count(*) from workflow_entity where id={quote_sql(sync_workflow_id)};").strip() != "0"
    if exists:
        sql = f"""
begin;
insert into workflow_history ("versionId", "workflowId", authors, nodes, connections, name, description)
values (
  {quote_sql(version_id)},
  {quote_sql(sync_workflow_id)},
  'Codex',
  {json_sql(workflow["nodes"])},
  {json_sql(workflow["connections"])},
  {quote_sql(workflow["name"])},
  {quote_sql(workflow["description"])}
);
update workflow_entity
set
  name={quote_sql(workflow["name"])},
  active=true,
  nodes={json_sql(workflow["nodes"])},
  connections={json_sql(workflow["connections"])},
  settings={json_sql(workflow["settings"])},
  description={quote_sql(workflow["description"])},
  "versionId"={quote_sql(version_id)},
  "activeVersionId"={quote_sql(version_id)},
  "updatedAt"=now()
where id={quote_sql(sync_workflow_id)};
commit;
"""
    else:
        sql = f"""
begin;
insert into workflow_entity
(id, name, active, nodes, connections, settings, "versionId", "triggerCount", "isArchived", "versionCounter", description)
values (
  {quote_sql(sync_workflow_id)},
  {quote_sql(workflow["name"])},
  true,
  {json_sql(workflow["nodes"])},
  {json_sql(workflow["connections"])},
  {json_sql(workflow["settings"])},
  {quote_sql(version_id)},
  0,
  false,
  1,
  {quote_sql(workflow["description"])}
);
insert into shared_workflow ("workflowId", "projectId", role)
values ({quote_sql(sync_workflow_id)}, {quote_sql(project_id)}, 'workflow:owner')
on conflict ("workflowId", "projectId") do nothing;
insert into workflow_history ("versionId", "workflowId", authors, nodes, connections, name, description)
values (
  {quote_sql(version_id)},
  {quote_sql(sync_workflow_id)},
  'Codex',
  {json_sql(workflow["nodes"])},
  {json_sql(workflow["connections"])},
  {quote_sql(workflow["name"])},
  {quote_sql(workflow["description"])}
);
update workflow_entity
set "activeVersionId"={quote_sql(version_id)}
where id={quote_sql(sync_workflow_id)};
commit;
"""
    psql(sql, tuples_only=False)


def ensure_main_connection(connections: dict, from_node: str, to_node: str) -> None:
    node_connections = connections.setdefault(from_node, {})
    main_outputs = node_connections.setdefault("main", [])
    while len(main_outputs) < 1:
        main_outputs.append([])
    output_zero = main_outputs[0]
    if not any(conn.get("node") == to_node and conn.get("type") == "main" for conn in output_zero):
        output_zero.append({"node": to_node, "type": "main", "index": 0})


def upsert_node(nodes: list[dict], node: dict) -> None:
    for index, existing in enumerate(nodes):
        if existing.get("name") == node["name"]:
            merged = {**existing, **node}
            nodes[index] = merged
            return
    nodes.append(node)


def code_node(name: str, js_code: str, x: int, y: int) -> dict:
    return {
        "parameters": {"jsCode": js_code},
        "id": name.lower().replace(" ", "-"),
        "name": name,
        "type": "n8n-nodes-base.code",
        "typeVersion": 2,
        "position": [x, y],
    }


def execute_node(name: str, sync_workflow_id: str, x: int, y: int) -> dict:
    return {
        "parameters": {
            "source": "database",
            "workflowId": {"__rl": True, "mode": "id", "value": sync_workflow_id},
            "options": {"waitForSubWorkflow": False},
        },
        "id": name.lower().replace(" ", "-"),
        "name": name,
        "type": "n8n-nodes-base.executeWorkflow",
        "typeVersion": 1.1,
        "position": [x, y],
        "continueOnFail": True,
    }


MAIN_INCOMING_JS = r"""
const body = ($json && ($json.body || $json)) || {};
const inboxId = body.conversation?.inbox_id ?? body.inbox?.id;
const event = body.event;
const messageType = body.message_type;
const isIncoming = messageType === "incoming" || messageType === 0;
if (inboxId !== 3 || event !== "message_created" || !isIncoming || body.private === true) return [];
return [{ json: {
  event_id: body.id ? `chatwoot:${body.id}:incoming` : `chatwoot:${body.conversation?.id}:incoming:${Date.now()}`,
  message_id: body.id,
  conversation_id: body.conversation?.id,
  contact_id: body.sender?.id || body.meta?.sender?.id || "",
  phone: body.meta?.sender?.phone_number || body.sender?.phone_number || "",
  name: body.sender?.name || body.meta?.sender?.name || "Cliente WhatsApp",
  direction: "incoming",
  content: body.content || "",
  private: false,
  source: "chatwoot_webhook",
} }];
""".strip()


MAIN_PUBLIC_REPLY_JS = r"""
function firstJson(name) {
  try { return $(name).first().json || {}; } catch (_) { return {}; }
}
const proc = firstJson("Procesar mensaje");
const last = firstJson("Verificar ultimo mensaje");
const agent = firstJson("Richard - AI Agent");
const msg = $json || {};
const conversationId = last.convId || proc.conversation_id || proc.convId || msg.conversation_id || "";
const content = msg.content || agent.output || agent.text || "";
if (!conversationId || !content) return [];
return [{ json: {
  event_id: msg.id ? `chatwoot:${msg.id}:richard_outgoing` : `chatwoot:${conversationId}:richard_outgoing:${Date.now()}`,
  message_id: msg.id || "",
  conversation_id: conversationId,
  contact_id: proc.contact_id || "",
  phone: proc.contact_phone || proc.phone || "",
  name: proc.contact_name || proc.name || "Cliente WhatsApp",
  direction: "richard_outgoing",
  content,
  private: false,
  source: "richard_chatwoot_response",
} }];
""".strip()


MAIN_PRIVATE_NOTE_JS = r"""
function firstJson(name) {
  try { return $(name).first().json || {}; } catch (_) { return {}; }
}
const proc = firstJson("Procesar mensaje");
const ctx = firstJson("Preparar contexto");
const agent = firstJson("Richard - AI Agent");
const msg = $json || {};
const conversationId = ctx.convId || proc.conversation_id || proc.convId || msg.conversation_id || "";
const content = msg.content || agent.output || agent.text || "";
if (!conversationId || !content) return [];
return [{ json: {
  event_id: msg.id ? `chatwoot:${msg.id}:private_note` : `chatwoot:${conversationId}:private_note:${Date.now()}`,
  message_id: msg.id || "",
  conversation_id: conversationId,
  contact_id: proc.contact_id || "",
  phone: proc.contact_phone || proc.phone || "",
  name: proc.contact_name || proc.name || "Cliente WhatsApp",
  direction: "private_note",
  content,
  private: true,
  source: "richard_learning_private_note",
} }];
""".strip()


PREPARE_BUDGET_NOTE_JS = r"""
function firstJson(name) {
  try { return $(name).first().json || {}; } catch (_) { return {}; }
}
const prepared = firstJson("Procesar Datos");
const msg = $json || {};
const conversationId = prepared.convId || prepared.conversation_id || msg.conversation_id || "";
const content = prepared.noteContent || prepared.content || msg.content || "";
if (!conversationId || !content) return [];
return [{ json: {
  event_id: msg.id ? `chatwoot:${msg.id}:private_note` : `chatwoot:${conversationId}:budget_private_note:${Date.now()}`,
  message_id: msg.id || "",
  conversation_id: conversationId,
  phone: prepared.telefono || prepared.phone || "",
  name: prepared.cliente || prepared.name || "Cliente WhatsApp",
  direction: "private_note",
  content,
  summary: prepared.summary || prepared.pedido || "",
  bloque_odoo: prepared.bloque_odoo || prepared.bloque_importable || "",
  total_interno_estimado: prepared.total_interno_estimado || "",
  private: true,
  source: "prepare_budget_for_edelmar",
} }];
""".strip()


def patch_main_workflow(sync_workflow_id: str) -> None:
    workflow = fetch_workflow(MAIN_WORKFLOW_ID)
    nodes = workflow["nodes"]
    connections = workflow["connections"]

    upsert_node(nodes, code_node("Preparar Sync Odoo Entrante", MAIN_INCOMING_JS, 660, 80))
    upsert_node(nodes, execute_node("Sync Odoo Entrante", sync_workflow_id, 900, 80))
    upsert_node(nodes, code_node("Preparar Sync Odoo Respuesta", MAIN_PUBLIC_REPLY_JS, 1540, 100))
    upsert_node(nodes, execute_node("Sync Odoo Respuesta", sync_workflow_id, 1780, 100))
    upsert_node(nodes, code_node("Preparar Sync Odoo Nota Privada", MAIN_PRIVATE_NOTE_JS, 1540, -80))
    upsert_node(nodes, execute_node("Sync Odoo Nota Privada", sync_workflow_id, 1780, -80))

    ensure_main_connection(connections, "Respond 200 OK", "Preparar Sync Odoo Entrante")
    ensure_main_connection(connections, "Preparar Sync Odoo Entrante", "Sync Odoo Entrante")
    ensure_main_connection(connections, "Enviar a Chatwoot", "Preparar Sync Odoo Respuesta")
    ensure_main_connection(connections, "Preparar Sync Odoo Respuesta", "Sync Odoo Respuesta")
    ensure_main_connection(connections, "Enviar Nota Privada", "Preparar Sync Odoo Nota Privada")
    ensure_main_connection(connections, "Preparar Sync Odoo Nota Privada", "Sync Odoo Nota Privada")

    save_workflow_version(workflow, nodes, connections)


def patch_prepare_budget_workflow(sync_workflow_id: str) -> None:
    workflow = fetch_workflow(PREPARE_BUDGET_WORKFLOW_ID)
    nodes = workflow["nodes"]
    connections = workflow["connections"]
    prepare_code = read_code("tool-prepare-budget-for-edelmar.js")

    for node in nodes:
        if node.get("name") == "Procesar Datos":
            node.setdefault("parameters", {})["jsCode"] = prepare_code

    upsert_node(nodes, code_node("Preparar Sync Odoo Presupuesto", PREPARE_BUDGET_NOTE_JS, 1040, 80))
    upsert_node(nodes, execute_node("Sync Odoo Presupuesto", sync_workflow_id, 1280, 80))
    ensure_main_connection(connections, "Crear Nota Privada", "Preparar Sync Odoo Presupuesto")
    ensure_main_connection(connections, "Preparar Sync Odoo Presupuesto", "Sync Odoo Presupuesto")

    save_workflow_version(workflow, nodes, connections)


def save_workflow_version(workflow: dict, nodes: list[dict], connections: dict) -> None:
    workflow_id = workflow["id"]
    version_id = str(uuid.uuid4())
    settings = workflow.get("settings") or {}
    description = workflow.get("description") or ""
    sql = f"""
begin;
insert into workflow_history ("versionId", "workflowId", authors, nodes, connections, name, description)
values (
  {quote_sql(version_id)},
  {quote_sql(workflow_id)},
  'Codex',
  {json_sql(nodes)},
  {json_sql(connections)},
  {quote_sql(workflow["name"])},
  {quote_sql(description)}
);
update workflow_entity
set
  nodes={json_sql(nodes)},
  connections={json_sql(connections)},
  settings={json_sql(settings)},
  "versionId"={quote_sql(version_id)},
  "activeVersionId"={quote_sql(version_id)},
  "updatedAt"=now()
where id={quote_sql(workflow_id)};
commit;
"""
    psql(sql, tuples_only=False)


def main() -> None:
    sync_workflow_id = resolve_sync_workflow_id()
    project_id = get_project_id()
    backup_dir = save_backups([MAIN_WORKFLOW_ID, PREPARE_BUDGET_WORKFLOW_ID, sync_workflow_id])
    upsert_sync_workflow(sync_workflow_id, project_id)
    patch_main_workflow(sync_workflow_id)
    patch_prepare_budget_workflow(sync_workflow_id)
    print(f"Backups: {backup_dir}")
    print(f"Synced workflow: {sync_workflow_id} ({SYNC_WORKFLOW_NAME})")
    print("Patched: richard-chatwoot-fibromuebles, fibro-tool-prepare-budget")


if __name__ == "__main__":
    main()