-- pgcrypto provides gen_random_uuid(). hstore is not needed yet (v1.1+
-- KV service); leave it for the migration that introduces that feature.
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE scripts (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            TEXT NOT NULL,
    description     TEXT,
    version         INTEGER NOT NULL DEFAULT 1,
    source          TEXT NOT NULL,

    timeout_seconds INTEGER NOT NULL DEFAULT 30  CHECK (timeout_seconds  > 0 AND timeout_seconds  <= 300),
    memory_limit_mb INTEGER NOT NULL DEFAULT 256 CHECK (memory_limit_mb  > 0 AND memory_limit_mb  <= 2048),

    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Names are user-facing; unique so the dashboard list and any future
-- name-based routing have an obvious identifier to surface.
CREATE UNIQUE INDEX scripts_name_uidx ON scripts (LOWER(name));

CREATE TABLE execution_logs (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    script_id        UUID NOT NULL REFERENCES scripts(id) ON DELETE CASCADE,
    request_id       UUID NOT NULL,

    request_path     TEXT,
    request_headers  JSONB NOT NULL DEFAULT '{}'::jsonb,
    request_body     JSONB,

    response_code    INTEGER,
    response_body    JSONB,

    logs             JSONB NOT NULL DEFAULT '[]'::jsonb,
    duration_ms      INTEGER NOT NULL DEFAULT 0,
    status           TEXT   NOT NULL CHECK (status IN ('success','error','timeout','budget_exceeded')),

    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX execution_logs_script_id_created_at_idx
    ON execution_logs (script_id, created_at DESC);