CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE SCHEMA IF NOT EXISTS athena;

-- Keep the migration additive so the canonical full table definition lives only
-- in sql/webhook_sinks.sql.
CREATE TABLE IF NOT EXISTS athena.webhook_sink_events (
    id bigint
);

ALTER TABLE athena.webhook_sink_events
    ADD COLUMN IF NOT EXISTS webhook_sink_event_id uuid DEFAULT gen_random_uuid(),
    ADD COLUMN IF NOT EXISTS created_at timestamptz DEFAULT now(),
    ADD COLUMN IF NOT EXISTS sink_name text,
    ADD COLUMN IF NOT EXISTS received_at timestamptz DEFAULT now(),
    ADD COLUMN IF NOT EXISTS request_path text,
    ADD COLUMN IF NOT EXISTS http_method text DEFAULT 'POST',
    ADD COLUMN IF NOT EXISTS source_ip text,
    ADD COLUMN IF NOT EXISTS content_type text,
    ADD COLUMN IF NOT EXISTS verified boolean DEFAULT false,
    ADD COLUMN IF NOT EXISTS event_type text,
    ADD COLUMN IF NOT EXISTS external_id text,
    ADD COLUMN IF NOT EXISTS query_params_json jsonb DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS headers_json jsonb DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS payload_json jsonb,
    ADD COLUMN IF NOT EXISTS payload_text text,
    ADD COLUMN IF NOT EXISTS payload_sha256 text DEFAULT '',
    ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb;

ALTER TABLE athena.webhook_sink_events
    ALTER COLUMN id TYPE bigint USING id::bigint,
    ALTER COLUMN webhook_sink_event_id TYPE uuid USING webhook_sink_event_id::uuid,
    ALTER COLUMN created_at TYPE timestamptz USING created_at::timestamptz,
    ALTER COLUMN sink_name TYPE text USING sink_name::text,
    ALTER COLUMN received_at TYPE timestamptz USING received_at::timestamptz,
    ALTER COLUMN request_path TYPE text USING request_path::text,
    ALTER COLUMN http_method TYPE text USING http_method::text,
    ALTER COLUMN source_ip TYPE text USING source_ip::text,
    ALTER COLUMN content_type TYPE text USING content_type::text,
    ALTER COLUMN verified TYPE boolean USING verified::boolean,
    ALTER COLUMN event_type TYPE text USING event_type::text,
    ALTER COLUMN external_id TYPE text USING external_id::text,
    ALTER COLUMN query_params_json TYPE jsonb USING query_params_json::jsonb,
    ALTER COLUMN headers_json TYPE jsonb USING headers_json::jsonb,
    ALTER COLUMN payload_json TYPE jsonb USING payload_json::jsonb,
    ALTER COLUMN payload_text TYPE text USING payload_text::text,
    ALTER COLUMN payload_sha256 TYPE text USING payload_sha256::text,
    ALTER COLUMN metadata TYPE jsonb USING metadata::jsonb;

ALTER TABLE athena.webhook_sink_events
    ALTER COLUMN webhook_sink_event_id SET DEFAULT gen_random_uuid(),
    ALTER COLUMN created_at SET DEFAULT now(),
    ALTER COLUMN received_at SET DEFAULT now(),
    ALTER COLUMN http_method SET DEFAULT 'POST',
    ALTER COLUMN verified SET DEFAULT false,
    ALTER COLUMN query_params_json SET DEFAULT '{}'::jsonb,
    ALTER COLUMN headers_json SET DEFAULT '{}'::jsonb,
    ALTER COLUMN payload_sha256 SET DEFAULT '',
    ALTER COLUMN metadata SET DEFAULT '{}'::jsonb;

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'athena'
          AND table_name = 'webhook_sink_events'
          AND column_name = 'id'
          AND is_identity <> 'YES'
    ) THEN
        EXECUTE 'ALTER TABLE athena.webhook_sink_events ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY';
    END IF;
END
$$;

ALTER TABLE athena.webhook_sink_events
    ALTER COLUMN id SET NOT NULL,
    ALTER COLUMN webhook_sink_event_id SET NOT NULL,
    ALTER COLUMN created_at SET NOT NULL,
    ALTER COLUMN sink_name SET NOT NULL,
    ALTER COLUMN received_at SET NOT NULL,
    ALTER COLUMN request_path SET NOT NULL,
    ALTER COLUMN http_method SET NOT NULL,
    ALTER COLUMN verified SET NOT NULL,
    ALTER COLUMN query_params_json SET NOT NULL,
    ALTER COLUMN headers_json SET NOT NULL,
    ALTER COLUMN payload_sha256 SET NOT NULL,
    ALTER COLUMN metadata SET NOT NULL;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'webhook_sink_events_pkey'
    ) THEN
        EXECUTE 'ALTER TABLE athena.webhook_sink_events ADD CONSTRAINT webhook_sink_events_pkey PRIMARY KEY (id)';
    END IF;
END
$$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'webhook_sink_events_webhook_sink_event_id_unique'
    ) THEN
        EXECUTE 'ALTER TABLE athena.webhook_sink_events ADD CONSTRAINT webhook_sink_events_webhook_sink_event_id_unique UNIQUE (webhook_sink_event_id)';
    END IF;
END
$$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'webhook_sink_events_sink_name_nonempty'
    ) THEN
        EXECUTE 'ALTER TABLE athena.webhook_sink_events ADD CONSTRAINT webhook_sink_events_sink_name_nonempty CHECK (btrim(sink_name) <> '''')';
    END IF;
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'webhook_sink_events_request_path_nonempty'
    ) THEN
        EXECUTE 'ALTER TABLE athena.webhook_sink_events ADD CONSTRAINT webhook_sink_events_request_path_nonempty CHECK (btrim(request_path) <> '''')';
    END IF;
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'webhook_sink_events_http_method_nonempty'
    ) THEN
        EXECUTE 'ALTER TABLE athena.webhook_sink_events ADD CONSTRAINT webhook_sink_events_http_method_nonempty CHECK (btrim(http_method) <> '''')';
    END IF;
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'webhook_sink_events_payload_sha256_nonempty'
    ) THEN
        EXECUTE 'ALTER TABLE athena.webhook_sink_events ADD CONSTRAINT webhook_sink_events_payload_sha256_nonempty CHECK (btrim(payload_sha256) <> '''')';
    END IF;
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'webhook_sink_events_query_params_json_object'
    ) THEN
        EXECUTE 'ALTER TABLE athena.webhook_sink_events ADD CONSTRAINT webhook_sink_events_query_params_json_object CHECK (jsonb_typeof(query_params_json) = ''object'')';
    END IF;
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'webhook_sink_events_headers_json_object'
    ) THEN
        EXECUTE 'ALTER TABLE athena.webhook_sink_events ADD CONSTRAINT webhook_sink_events_headers_json_object CHECK (jsonb_typeof(headers_json) = ''object'')';
    END IF;
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'webhook_sink_events_metadata_object'
    ) THEN
        EXECUTE 'ALTER TABLE athena.webhook_sink_events ADD CONSTRAINT webhook_sink_events_metadata_object CHECK (jsonb_typeof(metadata) = ''object'')';
    END IF;
END
$$;

CREATE INDEX IF NOT EXISTS webhook_sink_events_sink_received_idx
    ON athena.webhook_sink_events (sink_name, received_at DESC);

CREATE INDEX IF NOT EXISTS webhook_sink_events_event_type_idx
    ON athena.webhook_sink_events (event_type, created_at DESC);

CREATE INDEX IF NOT EXISTS webhook_sink_events_external_id_idx
    ON athena.webhook_sink_events (external_id, created_at DESC)
    WHERE external_id IS NOT NULL;
