-- Canonical billing tables for client databases.
-- These tables intentionally live outside the `athena` schema so product data
-- can stay queryable without coupling every consumer to Athena internals.

CREATE SCHEMA IF NOT EXISTS billing;

CREATE TABLE IF NOT EXISTS billing.billing_provider_connections (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_kind text NOT NULL,
    owner_id text NOT NULL,
    provider text NOT NULL,
    mode text NOT NULL DEFAULT 'live',
    status text NOT NULL DEFAULT 'pending',
    credential_kind text NOT NULL DEFAULT 'api_key',
    provider_account_id text NOT NULL,
    provider_profile_id text,
    scopes jsonb NOT NULL DEFAULT '[]'::jsonb,
    config jsonb NOT NULL DEFAULT '{}'::jsonb,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz,
    CONSTRAINT billing_provider_connections_owner_kind_check CHECK (
        owner_kind IN ('user', 'org', 'workspace', 'tenant')
    ),
    CONSTRAINT billing_provider_connections_provider_check CHECK (
        provider IN ('mollie', 'stripe')
    ),
    CONSTRAINT billing_provider_connections_mode_check CHECK (
        mode IN ('live', 'test')
    ),
    CONSTRAINT billing_provider_connections_status_check CHECK (
        status IN ('pending', 'active', 'disabled', 'error')
    ),
    CONSTRAINT billing_provider_connections_credential_kind_check CHECK (
        credential_kind IN (
            'api_key',
            'advanced_access_token',
            'organization_access_token',
            'oauth_access_token',
            'secret_key',
            'restricted_key'
        )
    )
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_billing_provider_connections_owner_provider
    ON billing.billing_provider_connections (owner_kind, owner_id, provider, mode)
    WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS billing.billing_webhook_events (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    connection_id uuid NOT NULL REFERENCES billing.billing_provider_connections(id) ON DELETE CASCADE,
    provider text NOT NULL,
    envelope_kind text NOT NULL,
    verification_mode text NOT NULL,
    signature_header_name text,
    event_id text,
    event_type text,
    resource text,
    entity_id text,
    document_kind text NOT NULL,
    provider_ref text NOT NULL,
    signature_headers jsonb NOT NULL DEFAULT '[]'::jsonb,
    payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    summary jsonb NOT NULL DEFAULT '{}'::jsonb,
    received_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT billing_webhook_events_provider_check CHECK (provider IN ('mollie', 'stripe')),
    CONSTRAINT billing_webhook_events_envelope_kind_check CHECK (
        envelope_kind IN ('classic', 'next_gen', 'snapshot')
    ),
    CONSTRAINT billing_webhook_events_document_kind_check CHECK (document_kind IN ('payment', 'subscription', 'invoice'))
);

CREATE INDEX IF NOT EXISTS idx_billing_webhook_events_connection_received_at
    ON billing.billing_webhook_events (connection_id, received_at DESC);

CREATE TABLE IF NOT EXISTS billing.billing_payments (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    provider text NOT NULL,
    provider_payment_id text NOT NULL,
    provider_customer_id text,
    provider_profile_id text,
    provider_subscription_id text,
    provider_payment_link_id text,
    status text NOT NULL,
    amount_currency text NOT NULL,
    amount_value numeric(18, 6) NOT NULL,
    amount_refunded_currency text,
    amount_refunded_value numeric(18, 6),
    description text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    raw jsonb NOT NULL DEFAULT '{}'::jsonb,
    paid_at timestamptz,
    created_at timestamptz,
    ingested_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT billing_payments_provider_check CHECK (provider IN ('mollie', 'stripe')),
    CONSTRAINT billing_payments_status_check CHECK (
        status IN ('pending', 'authorized', 'paid', 'failed', 'canceled', 'refunded')
    )
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_billing_payments_provider_payment_id
    ON billing.billing_payments (provider, provider_payment_id);

CREATE TABLE IF NOT EXISTS billing.billing_subscriptions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    provider text NOT NULL,
    provider_subscription_id text NOT NULL,
    provider_customer_id text NOT NULL,
    provider_profile_id text,
    status text NOT NULL,
    amount_currency text,
    amount_value numeric(18, 6),
    interval text,
    description text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    raw jsonb NOT NULL DEFAULT '{}'::jsonb,
    next_payment_date text,
    created_at timestamptz,
    canceled_at timestamptz,
    ingested_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT billing_subscriptions_provider_check CHECK (provider IN ('mollie', 'stripe')),
    CONSTRAINT billing_subscriptions_status_check CHECK (
        status IN ('trialing', 'active', 'past_due', 'canceled', 'paused')
    )
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_billing_subscriptions_provider_subscription_id
    ON billing.billing_subscriptions (provider, provider_subscription_id);

CREATE TABLE IF NOT EXISTS billing.billing_invoices (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    provider text NOT NULL,
    provider_invoice_id text NOT NULL,
    provider_profile_id text,
    provider_customer_id text,
    status text NOT NULL,
    amount_currency text,
    amount_value numeric(18, 6),
    amount_paid_currency text,
    amount_paid_value numeric(18, 6),
    description text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    raw jsonb NOT NULL DEFAULT '{}'::jsonb,
    issued_at timestamptz,
    paid_at timestamptz,
    created_at timestamptz,
    ingested_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT billing_invoices_provider_check CHECK (provider IN ('mollie', 'stripe')),
    CONSTRAINT billing_invoices_status_check CHECK (
        status IN ('draft', 'open', 'paid', 'void', 'uncollectible')
    )
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_billing_invoices_provider_invoice_id
    ON billing.billing_invoices (provider, provider_invoice_id);
