CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE SCHEMA IF NOT EXISTS billing;

CREATE TABLE IF NOT EXISTS billing.billing_projection_events (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    billing_projection_event_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now(),
    provider text NOT NULL,
    sink_name text NOT NULL,
    event_type text,
    external_id text,
    verified boolean NOT NULL DEFAULT false,
    received_at timestamptz NOT NULL,
    payload_json jsonb,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT billing_projection_events_provider_nonempty
        CHECK (btrim(provider) <> ''),
    CONSTRAINT billing_projection_events_sink_name_nonempty
        CHECK (btrim(sink_name) <> ''),
    CONSTRAINT billing_projection_events_metadata_object
        CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE INDEX IF NOT EXISTS billing_projection_events_provider_received_idx
    ON billing.billing_projection_events (provider, received_at DESC);

CREATE INDEX IF NOT EXISTS billing_projection_events_event_type_idx
    ON billing.billing_projection_events (event_type, created_at DESC);

CREATE INDEX IF NOT EXISTS billing_projection_events_external_id_idx
    ON billing.billing_projection_events (external_id, created_at DESC)
    WHERE external_id IS NOT NULL;
