"""Postgres Cold Ledger schema — sessions, events, swarm_sagas tables.

This module owns the DDL for Genesis's three core cold-ledger tables:

    sessions      — One row per agent session (start/end timestamps, agent ID).
    events        — Append-only event log linked to a session.
    swarm_sagas   — Swarm orchestration saga records with status lifecycle.

All DDL uses IF NOT EXISTS clauses, making every function fully idempotent.
"""

from __future__ import annotations

# ---------------------------------------------------------------------------
# DDL constants — single source of truth for all three tables
# ---------------------------------------------------------------------------

SESSIONS_DDL = """
CREATE TABLE IF NOT EXISTS sessions (
    id          UUID PRIMARY KEY,
    started_at  TIMESTAMP NOT NULL DEFAULT NOW(),
    ended_at    TIMESTAMP,
    agent_id    VARCHAR(100) NOT NULL,
    metadata    JSONB
);
"""

EVENTS_DDL = """
CREATE TABLE IF NOT EXISTS events (
    id          UUID PRIMARY KEY,
    session_id  UUID REFERENCES sessions(id),
    event_type  VARCHAR(50) NOT NULL,
    payload     JSONB,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_events_session_id ON events(session_id);
CREATE INDEX IF NOT EXISTS idx_events_type ON events(event_type);
CREATE INDEX IF NOT EXISTS idx_events_created ON events(created_at DESC);
"""

SWARM_SAGAS_DDL = """
CREATE TABLE IF NOT EXISTS swarm_sagas (
    saga_id          UUID PRIMARY KEY,
    session_id       UUID REFERENCES sessions(id),
    orchestrator_dag JSONB,
    proposed_deltas  JSONB,
    resolved_state   JSONB,
    status           VARCHAR(20) CHECK (status IN ('RUNNING','COMPLETED','PARTIAL_FAIL','FAILED')),
    created_at       TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_sagas_session_id ON swarm_sagas(session_id);
CREATE INDEX IF NOT EXISTS idx_sagas_status ON swarm_sagas(status);
"""


# ---------------------------------------------------------------------------
# Public API
# ---------------------------------------------------------------------------

def create_all_tables(conn) -> None:
    """Execute all DDL statements in a single transaction.

    Idempotent — safe to run multiple times (every statement uses IF NOT EXISTS).
    Tables are created in FK dependency order: sessions → events → swarm_sagas.

    Args:
        conn: An open psycopg2 connection (or compatible DB-API 2.0 connection).
    """
    with conn.cursor() as cur:
        cur.execute(SESSIONS_DDL)
        cur.execute(EVENTS_DDL)
        cur.execute(SWARM_SAGAS_DDL)
    conn.commit()


def drop_all_tables(conn) -> None:
    """Drop all Cold Ledger tables in reverse FK order.

    Uses CASCADE so dependent FK constraints are automatically dropped.

    WARNING: FOR TEST TEARDOWN AND DEVELOPMENT USE ONLY.
             Never call this in production without an explicit confirmation gate.

    Args:
        conn: An open psycopg2 connection (or compatible DB-API 2.0 connection).
    """
    with conn.cursor() as cur:
        cur.execute("DROP TABLE IF EXISTS swarm_sagas CASCADE")
        cur.execute("DROP TABLE IF EXISTS events CASCADE")
        cur.execute("DROP TABLE IF EXISTS sessions CASCADE")
    conn.commit()


# VERIFICATION_STAMP
# Story: 5.01
# Verified By: parallel-builder
# Verified At: 2026-02-25
# Tests: 11/11
# Coverage: 100%