"""
Creates n8n workflow definitions for every entity in the experiments database.
Each workflow has an Execute Workflow Trigger + Switch (routes by `action` field)
+ one Postgres node per operation. They act as reusable sub-workflows.
"""

import json, uuid, requests

API   = "http://localhost:5678/api/v1"
KEY   = "n8n_api_ca07bd874c628ed0f52d9a2e65073960f9f617c5"
CRED  = {"id": "9iJuyA9iR5KUzmj5", "name": "Experiments DB (PostgreSQL)"}
HEADS = {"X-N8N-API-KEY": KEY, "Content-Type": "application/json"}

def uid(): return str(uuid.uuid4())

# ── Node builders ──────────────────────────────────────────────────────────────

def trigger_node(x, y):
    return {
        "id": uid(), "name": "Trigger",
        "type": "n8n-nodes-base.executeWorkflowTrigger",
        "typeVersion": 1.1,
        "position": [x, y],
        "parameters": {},
    }

def switch_node(name, actions, x, y):
    """Route by $json.action — one output per action string."""
    rules = []
    for action in actions:
        rules.append({
            "conditions": {
                "options": {"caseSensitive": False, "leftValue": "", "typeValidation": "loose"},
                "combinator": "and",
                "conditions": [{
                    "leftValue": "={{ $json.action }}",
                    "rightValue": action,
                    "operator": {"type": "string", "operation": "equals"},
                }],
            },
            "renameOutput": True,
            "outputKey": action,
        })
    return {
        "id": uid(), "name": name,
        "type": "n8n-nodes-base.switch",
        "typeVersion": 3.2,
        "position": [x, y],
        "parameters": {"rules": {"values": rules}, "options": {}},
    }

def pg_node(name, query, params, x, y):
    """Postgres executeQuery node. params is a list of n8n expressions for $1,$2,..."""
    node = {
        "id": uid(), "name": name,
        "type": "n8n-nodes-base.postgres",
        "typeVersion": 2.5,
        "position": [x, y],
        "credentials": {"postgres": CRED},
        "parameters": {
            "operation": "executeQuery",
            "query": query,
            "options": {},
        },
    }
    if params:
        node["parameters"]["options"]["queryReplacement"] = "={{ [" + ", ".join(params) + "] }}"
    return node

# ── Connection helpers ──────────────────────────────────────────────────────────

def conn(src, src_idx, dst):
    """Return a connections dict fragment."""
    return (src["name"], src_idx, dst["name"])

def build_connections(pairs):
    """
    pairs: list of (src_node, output_index, dst_node)
    Returns n8n connections object.
    """
    conns = {}
    for src, idx, dst in pairs:
        conns.setdefault(src, {"main": []})
        while len(conns[src]["main"]) <= idx:
            conns[src]["main"].append([])
        conns[src]["main"][idx].append({"node": dst, "type": "main", "index": 0})
    return conns

# ── Workflow factory ───────────────────────────────────────────────────────────

def make_workflow(name, trigger, switch, pg_nodes, connections):
    return {
        "name": name,
        "nodes": [trigger, switch] + pg_nodes,
        "connections": connections,
        "settings": {"executionOrder": "v1"},
    }

def post_workflow(wf):
    r = requests.post(f"{API}/workflows", headers=HEADS, json=wf)
    if r.ok:
        d = r.json()
        print(f"  ✓  [{d['id']}]  {wf['name']}")
    else:
        print(f"  ✗  {wf['name']}  →  {r.status_code}  {r.text[:200]}")

# ── Layouts ────────────────────────────────────────────────────────────────────
# Nodes stacked vertically, 220px apart; Switch at col 300, pg nodes at col 560

def pg_y(i): return i * 220 - 440   # center the stack

# ══════════════════════════════════════════════════════════════════════════════
# 1. EXPERIMENTS
# ══════════════════════════════════════════════════════════════════════════════

def wf_experiments():
    t   = trigger_node(60, 0)
    sw  = switch_node("Route", ["list","get","create","update","delete"], 300, 0)
    ops = [
        pg_node("List Experiments",
            "SELECT id, title, created_at FROM experiments ORDER BY created_at DESC",
            [], 560, pg_y(0)),
        pg_node("Get Experiment",
            "SELECT * FROM experiments WHERE id = $1",
            ["$json.id"], 560, pg_y(1)),
        pg_node("Create Experiment",
            "INSERT INTO experiments (id, title, template, subject_info_template) "
            "VALUES (gen_random_uuid(), $1, '[]'::jsonb, '[]'::jsonb) RETURNING *",
            ["$json.title"], 560, pg_y(2)),
        pg_node("Update Experiment",
            "UPDATE experiments SET title = $1 WHERE id = $2 RETURNING *",
            ["$json.title", "$json.id"], 560, pg_y(3)),
        pg_node("Delete Experiment",
            "DELETE FROM experiments WHERE id = $1 RETURNING id",
            ["$json.id"], 560, pg_y(4)),
    ]
    pairs = [(t["name"], 0, sw["name"])] + \
            [(sw["name"], i, ops[i]["name"]) for i in range(len(ops))]
    return make_workflow("ExpDB · Experiments", t, sw, ops, build_connections(pairs))

# ══════════════════════════════════════════════════════════════════════════════
# 2. ANIMALS
# ══════════════════════════════════════════════════════════════════════════════

def wf_animals():
    t   = trigger_node(60, 0)
    sw  = switch_node("Route", ["list","get","create","update","delete"], 300, 0)
    ops = [
        pg_node("List Animals",
            "SELECT a.*, COUNT(ds.id)::int AS daily_status_count "
            "FROM animals a "
            "LEFT JOIN daily_statuses ds ON ds.animal_id = a.id "
            "WHERE a.experiment_id = $1 "
            "GROUP BY a.id ORDER BY a.animal_name",
            ["$json.experiment_id"], 560, pg_y(0)),
        pg_node("Get Animal",
            "SELECT a.*, e.title AS experiment_title "
            "FROM animals a JOIN experiments e ON e.id = a.experiment_id "
            "WHERE a.id = $1",
            ["$json.id"], 560, pg_y(1)),
        pg_node("Create Animal",
            "INSERT INTO animals (id, experiment_id, animal_id_string, animal_name, subject_info) "
            "VALUES (gen_random_uuid(), $1, $2, $3, $4::jsonb) RETURNING *",
            ["$json.experiment_id", "$json.animal_id_string", "$json.animal_name",
             "JSON.stringify($json.subject_info ?? {})"], 560, pg_y(2)),
        pg_node("Update Animal",
            "UPDATE animals SET animal_id_string=$1, animal_name=$2, subject_info=$3::jsonb "
            "WHERE id=$4 RETURNING *",
            ["$json.animal_id_string", "$json.animal_name",
             "JSON.stringify($json.subject_info ?? {})", "$json.id"], 560, pg_y(3)),
        pg_node("Delete Animal",
            "DELETE FROM animals WHERE id = $1 RETURNING id",
            ["$json.id"], 560, pg_y(4)),
    ]
    pairs = [(t["name"], 0, sw["name"])] + \
            [(sw["name"], i, ops[i]["name"]) for i in range(len(ops))]
    return make_workflow("ExpDB · Animals", t, sw, ops, build_connections(pairs))

# ══════════════════════════════════════════════════════════════════════════════
# 3. DAILY STATUSES
# ══════════════════════════════════════════════════════════════════════════════

def wf_daily_statuses():
    t   = trigger_node(60, 0)
    sw  = switch_node("Route", ["list","get","create","update","delete"], 300, 0)
    ops = [
        pg_node("List Daily Statuses",
            "SELECT ds.id, ds.date, ds.experiment_description, ds.vitals, "
            "ds.treatment, ds.notes, ds.custom_fields, ds.analysis_summary, "
            "a.animal_name, a.animal_id_string "
            "FROM daily_statuses ds JOIN animals a ON a.id = ds.animal_id "
            "WHERE ds.animal_id = $1 ORDER BY ds.date DESC",
            ["$json.animal_id"], 560, pg_y(0)),
        pg_node("Get Daily Status",
            "SELECT ds.*, a.animal_name, a.animal_id_string, "
            "a.experiment_id, e.title AS experiment_title "
            "FROM daily_statuses ds "
            "JOIN animals a ON a.id = ds.animal_id "
            "JOIN experiments e ON e.id = a.experiment_id "
            "WHERE ds.id = $1",
            ["$json.id"], 560, pg_y(1)),
        pg_node("Create Daily Status",
            "INSERT INTO daily_statuses "
            "(id, animal_id, date, experiment_description, vitals, treatment, notes, custom_fields) "
            "VALUES (gen_random_uuid(), $1, $2::date, $3, $4, $5, $6, $7::jsonb) RETURNING *",
            ["$json.animal_id", "$json.date", "$json.experiment_description ?? null",
             "$json.vitals ?? null", "$json.treatment ?? null", "$json.notes ?? null",
             "JSON.stringify($json.custom_fields ?? {})"], 560, pg_y(2)),
        pg_node("Update Daily Status",
            "UPDATE daily_statuses SET "
            "experiment_description=$1, vitals=$2, treatment=$3, notes=$4, "
            "custom_fields=$5::jsonb "
            "WHERE id=$6 RETURNING *",
            ["$json.experiment_description ?? null", "$json.vitals ?? null",
             "$json.treatment ?? null", "$json.notes ?? null",
             "JSON.stringify($json.custom_fields ?? {})", "$json.id"], 560, pg_y(3)),
        pg_node("Delete Daily Status",
            "DELETE FROM daily_statuses WHERE id = $1 RETURNING id",
            ["$json.id"], 560, pg_y(4)),
    ]
    pairs = [(t["name"], 0, sw["name"])] + \
            [(sw["name"], i, ops[i]["name"]) for i in range(len(ops))]
    return make_workflow("ExpDB · Daily Statuses", t, sw, ops, build_connections(pairs))

# ══════════════════════════════════════════════════════════════════════════════
# 4. DAILY ANALYSES  (read + summary-push)
# ══════════════════════════════════════════════════════════════════════════════

def wf_daily_analyses():
    t   = trigger_node(60, 0)
    sw  = switch_node("Route", ["list","get","push_summary"], 300, 0)
    ops = [
        pg_node("List Analyses",
            "SELECT id, daily_status_id, created_at, file_name, "
            "timestamp_col, note_col, total_note_rows, session_end_ts "
            "FROM daily_analyses WHERE daily_status_id = $1 ORDER BY created_at DESC",
            ["$json.daily_status_id"], 560, pg_y(0)),
        pg_node("Get Analysis",
            "SELECT * FROM daily_analyses WHERE id = $1",
            ["$json.id"], 560, pg_y(1)),
        pg_node("Push Analysis Summary",
            "UPDATE daily_statuses SET analysis_summary = $1::jsonb WHERE id = $2 RETURNING id, analysis_summary",
            ["JSON.stringify($json.analysis_summary)", "$json.daily_status_id"],
            560, pg_y(2)),
    ]
    pairs = [(t["name"], 0, sw["name"])] + \
            [(sw["name"], i, ops[i]["name"]) for i in range(len(ops))]
    return make_workflow("ExpDB · Daily Analyses", t, sw, ops, build_connections(pairs))

# ══════════════════════════════════════════════════════════════════════════════
# 5. SESSION FILES
# ══════════════════════════════════════════════════════════════════════════════

def wf_session_files():
    t   = trigger_node(60, 0)
    sw  = switch_node("Route", ["list","get"], 300, 0)
    ops = [
        pg_node("List Session Files",
            "SELECT * FROM session_files WHERE daily_status_id = $1 ORDER BY created_at DESC",
            ["$json.daily_status_id"], 560, pg_y(0)),
        pg_node("Get Session File",
            "SELECT * FROM session_files WHERE id = $1",
            ["$json.id"], 560, pg_y(1)),
    ]
    pairs = [(t["name"], 0, sw["name"])] + \
            [(sw["name"], i, ops[i]["name"]) for i in range(len(ops))]
    return make_workflow("ExpDB · Session Files", t, sw, ops, build_connections(pairs))

# ══════════════════════════════════════════════════════════════════════════════
# 6. AUDIT LOGS
# ══════════════════════════════════════════════════════════════════════════════

def wf_audit_logs():
    t   = trigger_node(60, 0)
    sw  = switch_node("Route", ["list_by_record","list_by_table"], 300, 0)
    ops = [
        pg_node("List Logs by Record",
            "SELECT * FROM audit_logs "
            "WHERE table_name = $1 AND record_id = $2 "
            "ORDER BY timestamp DESC LIMIT $3",
            ["$json.table_name", "$json.record_id", "$json.limit ?? 50"],
            560, pg_y(0)),
        pg_node("List Logs by Table",
            "SELECT * FROM audit_logs "
            "WHERE table_name = $1 "
            "ORDER BY timestamp DESC LIMIT $2",
            ["$json.table_name", "$json.limit ?? 100"],
            560, pg_y(1)),
    ]
    pairs = [(t["name"], 0, sw["name"])] + \
            [(sw["name"], i, ops[i]["name"]) for i in range(len(ops))]
    return make_workflow("ExpDB · Audit Logs", t, sw, ops, build_connections(pairs))

# ══════════════════════════════════════════════════════════════════════════════
# BONUS: Cross-subject report — one query to summarise all subjects in an experiment
# ══════════════════════════════════════════════════════════════════════════════

def wf_reports():
    t   = trigger_node(60, 0)
    sw  = switch_node("Route", ["subjects_summary","daily_summary","recent_activity"], 300, 0)
    ops = [
        pg_node("Subjects Summary",
            "SELECT a.id, a.animal_name, a.animal_id_string, a.subject_info, "
            "COUNT(ds.id)::int AS total_days, "
            "MAX(ds.date) AS last_entry, "
            "COUNT(ds.analysis_summary)::int AS days_with_metrics "
            "FROM animals a "
            "LEFT JOIN daily_statuses ds ON ds.animal_id = a.id "
            "WHERE a.experiment_id = $1 "
            "GROUP BY a.id ORDER BY a.animal_name",
            ["$json.experiment_id"], 560, pg_y(0)),
        pg_node("Daily Summary",
            "SELECT ds.date, a.animal_name, "
            "ds.analysis_summary->>'total' AS total_attempts, "
            "ds.analysis_summary->>'success_rate' AS success_rate "
            "FROM daily_statuses ds JOIN animals a ON a.id = ds.animal_id "
            "WHERE a.experiment_id = $1 AND ds.analysis_summary IS NOT NULL "
            "ORDER BY ds.date, a.animal_name",
            ["$json.experiment_id"], 560, pg_y(1)),
        pg_node("Recent Activity",
            "SELECT al.timestamp, al.table_name, al.action, al.record_id, al.changes "
            "FROM audit_logs al "
            "ORDER BY al.timestamp DESC LIMIT $1",
            ["$json.limit ?? 50"], 560, pg_y(2)),
    ]
    pairs = [(t["name"], 0, sw["name"])] + \
            [(sw["name"], i, ops[i]["name"]) for i in range(len(ops))]
    return make_workflow("ExpDB · Reports", t, sw, ops, build_connections(pairs))

# ── Main ───────────────────────────────────────────────────────────────────────

if __name__ == "__main__":
    workflows = [
        wf_experiments(),
        wf_animals(),
        wf_daily_statuses(),
        wf_daily_analyses(),
        wf_session_files(),
        wf_audit_logs(),
        wf_reports(),
    ]
    print(f"Creating {len(workflows)} workflows…\n")
    for wf in workflows:
        post_workflow(wf)
    print("\nDone.")