-- Active: 1782426846920@@thomas.proxy.rlwy.net@19878@railway
-- Athena operations-client provisioning script.
--
-- Applies the Athena auth/session, organization, chat, email, storage, and
-- related runtime tables to an Athena client database.
--
-- Usage:
--   psql "$DATABASE_URL" -f sql/provision.sql
--   athena_rs provision --uri "$DATABASE_URL"
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE SCHEMA IF NOT EXISTS athena;

-- 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);
-- BEGIN GENERATED: storage-files
-- Managed storage/files schema provisioned from the canonical Athena storage SQL.
-- Generated from sql/files/athena-files.sql by scripts/generate_storage_files_artifacts.py.
-- Do not edit this file directly.

-- Canonical Athena-managed file and S3 catalog tables.
-- This file is intentionally clean DDL only.
-- Generate sql/provision_files.sql, the storage-files block in sql/provision.sql,
-- and docs/architecture/storage-files-schema.md from this file via
-- scripts/generate_storage_files_artifacts.py.
-- Canonical names here are singular:
--   athena.audit_log_files
--   athena.event_log_files
-- Do not recreate plural alias objects such as athena.audit_logs_files / athena.event_logs_files.


CREATE SCHEMA IF NOT EXISTS athena;

CREATE TABLE IF NOT EXISTS athena.s3 (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name text NOT NULL,
    description text NOT NULL DEFAULT '',
    endpoint text NOT NULL,
    region text NOT NULL,
    bucket text,
    provider text NOT NULL DEFAULT 's3',
    is_active boolean NOT NULL DEFAULT true,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz,
    CONSTRAINT athena_s3_name_nonempty CHECK (btrim(name) <> ''),
    CONSTRAINT athena_s3_endpoint_nonempty CHECK (btrim(endpoint) <> ''),
    CONSTRAINT athena_s3_region_nonempty CHECK (btrim(region) <> ''),
    CONSTRAINT athena_s3_provider_nonempty CHECK (btrim(provider) <> ''),
    CONSTRAINT athena_s3_metadata_is_object CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE UNIQUE INDEX IF NOT EXISTS athena_s3_name_unique
    ON athena.s3 (lower(name))
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS athena_s3_active_created_idx
    ON athena.s3 (is_active, created_at DESC)
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS athena_s3_bucket_created_idx
    ON athena.s3 (bucket, created_at DESC)
    WHERE bucket IS NOT NULL AND deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS athena.s3_credentials (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    s3_id uuid NOT NULL REFERENCES athena.s3(id) ON DELETE CASCADE,
    description text NOT NULL DEFAULT '',
    access_key_id text NOT NULL,
    secret_key text NOT NULL,
    session_token text,
    is_active boolean NOT NULL DEFAULT true,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    rotated_at timestamptz,
    expires_at timestamptz,
    CONSTRAINT athena_s3_credentials_access_key_nonempty CHECK (btrim(access_key_id) <> ''),
    CONSTRAINT athena_s3_credentials_secret_nonempty CHECK (btrim(secret_key) <> ''),
    CONSTRAINT athena_s3_credentials_metadata_is_object CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE UNIQUE INDEX IF NOT EXISTS athena_s3_credentials_one_active_per_s3_idx
    ON athena.s3_credentials (s3_id)
    WHERE is_active = true;

CREATE INDEX IF NOT EXISTS athena_s3_credentials_active_created_idx
    ON athena.s3_credentials (is_active, created_at DESC);

CREATE INDEX IF NOT EXISTS athena_s3_credentials_s3_created_idx
    ON athena.s3_credentials (s3_id, created_at DESC);

CREATE TABLE IF NOT EXISTS athena.files (
    id text PRIMARY KEY,
    name text NOT NULL,
    original_name text,
    url text,
    bucket text NOT NULL,
    s3_id uuid REFERENCES athena.s3(id) ON DELETE SET NULL,
    prefix_path text,
    size_bytes bigint,
    mime_type text,
    resource_id text,
    organization_id text NOT NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    storage_key text NOT NULL,
    uploaded_by_user_id text,
    extension text,
    is_public boolean NOT NULL DEFAULT false,
    status text NOT NULL DEFAULT 'active',
    deleted_at timestamptz,
    CONSTRAINT athena_files_name_nonempty CHECK (btrim(name) <> ''),
    CONSTRAINT athena_files_bucket_nonempty CHECK (btrim(bucket) <> ''),
    CONSTRAINT athena_files_organization_nonempty CHECK (btrim(organization_id) <> ''),
    CONSTRAINT athena_files_storage_key_nonempty CHECK (btrim(storage_key) <> ''),
    CONSTRAINT athena_files_metadata_is_object CHECK (jsonb_typeof(metadata) = 'object'),
    CONSTRAINT athena_files_status_valid CHECK (status IN ('active', 'deleted', 'archived'))
);

CREATE INDEX IF NOT EXISTS athena_files_organization_resource_created_idx
    ON athena.files (organization_id, resource_id, created_at DESC);

CREATE INDEX IF NOT EXISTS athena_files_organization_status_updated_idx
    ON athena.files (organization_id, status, updated_at DESC);

CREATE INDEX IF NOT EXISTS athena_files_storage_key_idx
    ON athena.files (storage_key);

CREATE INDEX IF NOT EXISTS athena_files_s3_id_created_idx
    ON athena.files (s3_id, created_at DESC)
    WHERE s3_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS athena_files_bucket_prefix_idx
    ON athena.files (bucket, prefix_path, created_at DESC);

CREATE UNIQUE INDEX IF NOT EXISTS athena_files_org_s3_storage_key_active_uidx
    ON athena.files (organization_id, s3_id, storage_key)
    WHERE deleted_at IS NULL AND s3_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS athena.audit_log_files (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    audit_log_file_id uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    file_id text REFERENCES athena.files(id) ON DELETE SET NULL,
    action text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    time numeric,
    route text,
    request text,
    status text,
    message text,
    author_user_id text,
    user_id text,
    organization_id text,
    new_status text,
    table_name text NOT NULL DEFAULT 'files',
    resource_id text,
    resource jsonb,
    diff_resource jsonb,
    user_object jsonb,
    event_name text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT athena_audit_log_files_action_nonempty CHECK (btrim(action) <> ''),
    CONSTRAINT athena_audit_log_files_metadata_is_object CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE INDEX IF NOT EXISTS athena_audit_log_files_file_created_idx
    ON athena.audit_log_files (file_id, created_at DESC)
    WHERE file_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS athena_audit_log_files_organization_created_idx
    ON athena.audit_log_files (organization_id, created_at DESC)
    WHERE organization_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS athena_audit_log_files_action_created_idx
    ON athena.audit_log_files (action, created_at DESC);

CREATE TABLE IF NOT EXISTS athena.event_log_files (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    event_log_file_id uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    file_id text REFERENCES athena.files(id) ON DELETE SET NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    directive text,
    time numeric,
    path text,
    content text,
    server text,
    api_version text,
    severity text,
    message text,
    payload jsonb,
    user_id text,
    organization_id text,
    status text,
    ref_id text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT athena_event_log_files_metadata_is_object CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE INDEX IF NOT EXISTS athena_event_log_files_file_created_idx
    ON athena.event_log_files (file_id, created_at DESC)
    WHERE file_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS athena_event_log_files_organization_created_idx
    ON athena.event_log_files (organization_id, created_at DESC)
    WHERE organization_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS athena.file_permissions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    file_id text NOT NULL REFERENCES athena.files(id) ON DELETE CASCADE,
    principal_type text NOT NULL,
    principal_id text NOT NULL,
    permission text NOT NULL,
    granted_by_user_id text,
    created_at timestamptz NOT NULL DEFAULT now(),
    expires_at timestamptz,
    revoked_at timestamptz,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT athena_file_permissions_principal_type_valid CHECK (
        principal_type IN ('user', 'organization', 'team', 'role')
    ),
    CONSTRAINT athena_file_permissions_permission_valid CHECK (
        permission IN ('read', 'write', 'delete', 'share', 'owner')
    ),
    CONSTRAINT athena_file_permissions_metadata_is_object CHECK (jsonb_typeof(metadata) = 'object'),
    UNIQUE (file_id, principal_type, principal_id, permission)
);

DROP INDEX IF EXISTS athena.athena_file_permissions_lookup_idx;
DROP INDEX IF EXISTS athena.athena_file_permissions_principal_idx;

CREATE INDEX IF NOT EXISTS file_permissions_lookup_idx
    ON athena.file_permissions (file_id, principal_type, principal_id, permission)
    WHERE revoked_at IS NULL;

CREATE INDEX IF NOT EXISTS file_permissions_principal_idx
    ON athena.file_permissions (principal_type, principal_id)
    WHERE revoked_at IS NULL;

CREATE TABLE IF NOT EXISTS athena.s3_url_cache (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    file_id text NOT NULL REFERENCES athena.files(id) ON DELETE CASCADE,
    purpose text NOT NULL DEFAULT 'read',
    bucket text NOT NULL,
    storage_key text NOT NULL,
    url text NOT NULL,
    expires_at timestamptz NOT NULL,
    expires_at_epoch_seconds bigint NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT athena_s3_url_cache_purpose_valid CHECK (
        purpose IN ('read', 'download', 'stream')
    ),
    CONSTRAINT athena_s3_url_cache_metadata_is_object CHECK (jsonb_typeof(metadata) = 'object'),
    UNIQUE (file_id, purpose)
);

CREATE INDEX IF NOT EXISTS athena_s3_url_cache_lookup_idx
    ON athena.s3_url_cache (file_id, purpose, expires_at);

CREATE INDEX IF NOT EXISTS athena_s3_url_cache_expiry_idx
    ON athena.s3_url_cache (expires_at);
-- END GENERATED: storage-files
-- ---------------------------------------------------------------------------
-- Athena auth, organization, chat, email, and operations tables
-- ---------------------------------------------------------------------------
-- Athena generated DDL
-- Client: suits-formations
-- Generated at: 2026-06-28T01:32:44.047Z
-- Table: athena.account
CREATE TABLE IF NOT EXISTS athena.account (
  id text NOT NULL,
  account_id text NOT NULL,
  provider_id text NOT NULL,
  user_id text NOT NULL,
  access_token text,
  refresh_token text,
  id_token text,
  access_token_expires_at timestamp with time zone,
  refresh_token_expires_at timestamp with time zone,
  scope text,
  password text,
  created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp with time zone NOT NULL
);

-- Table: athena.accounts
CREATE TABLE IF NOT EXISTS athena.accounts (
  id text NOT NULL,
  account_id text NOT NULL,
  provider_id text NOT NULL,
  user_id text NOT NULL,
  access_token text,
  refresh_token text,
  id_token text,
  access_token_expires_at timestamp with time zone,
  refresh_token_expires_at timestamp with time zone,
  scope text,
  password text,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);

-- Table: athena.admin_audit_logs
CREATE TABLE IF NOT EXISTS athena.admin_audit_logs (
  id text NOT NULL,
  actor_user_id text,
  actor_session_token text,
  action text NOT NULL,
  target_type text,
  target_id text,
  success boolean NOT NULL DEFAULT false,
  error_message text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamp with time zone NOT NULL DEFAULT now()
);

-- Table: athena.api_keys
CREATE TABLE IF NOT EXISTS athena.api_keys (
  id text NOT NULL,
  name text,
  start text,
  prefix text,
  key text NOT NULL,
  user_id text NOT NULL,
  refill_interval integer,
  refill_amount integer,
  last_refill_at timestamp with time zone,
  enabled boolean NOT NULL DEFAULT true,
  rate_limit_enabled boolean NOT NULL DEFAULT false,
  rate_limit_time_window integer,
  rate_limit_max integer,
  request_count bigint DEFAULT 0,
  remaining integer,
  last_request timestamp with time zone,
  expires_at timestamp with time zone,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now(),
  permissions text,
  metadata text
);

-- Table: athena.apikey
CREATE TABLE IF NOT EXISTS athena.apikey (
  id text NOT NULL,
  name text,
  start text,
  prefix text,
  key text NOT NULL,
  user_id text NOT NULL,
  refill_interval integer,
  refill_amount integer,
  last_refill_at timestamp with time zone,
  enabled boolean,
  rate_limit_enabled boolean,
  rate_limit_time_window integer,
  rate_limit_max integer,
  request_count integer,
  remaining integer,
  last_request timestamp with time zone,
  expires_at timestamp with time zone,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  permissions text,
  metadata text
);

-- Table: athena.athena_clients
CREATE TABLE IF NOT EXISTS athena.athena_clients (
  id text NOT NULL,
  client_name text NOT NULL,
  description text,
  is_active boolean NOT NULL DEFAULT true,
  pg_uri_env_var text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);

-- Table: athena.audit_log
CREATE TABLE IF NOT EXISTS athena.audit_log (
  id bigint NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT (now() AT TIME ZONE 'utc'::text),
  user_id text,
  username text,
  action text,
  time numeric,
  route text,
  request text,
  status text,
  message text,
  author_user_id text,
  email text,
  domain text DEFAULT 'suitsbooks.nl'::text,
  organization_id text,
  new_status text,
  old_value text,
  new_value text,
  table text,
  resource_id text,
  resource json,
  diff_resource json,
  user_object json,
  ipv4 text,
  audit_log_id uuid NOT NULL DEFAULT gen_random_uuid(),
  ipv4_address text
);

-- Table: athena.chat_message_attachments
CREATE TABLE IF NOT EXISTS athena.chat_message_attachments (
  message_id uuid NOT NULL,
  file_id text NOT NULL,
  ordinal integer NOT NULL
);

-- Table: athena.chat_message_reactions
CREATE TABLE IF NOT EXISTS athena.chat_message_reactions (
  message_id uuid NOT NULL,
  user_id text NOT NULL,
  emoji text NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now()
);

-- Table: athena.chat_messages
CREATE TABLE IF NOT EXISTS athena.chat_messages (
  id uuid NOT NULL,
  room_id uuid NOT NULL,
  room_seq bigint NOT NULL,
  sender_id text NOT NULL,
  client_message_id text,
  body_text text,
  body_json jsonb,
  reply_to_message_id uuid,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  edited_at timestamp with time zone,
  deleted_at timestamp with time zone,
  metadata_json jsonb
);

-- Table: athena.chat_outbox
CREATE TABLE IF NOT EXISTS athena.chat_outbox (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  aggregate_type text NOT NULL,
  aggregate_id text NOT NULL,
  room_id uuid NOT NULL,
  room_seq bigint,
  event_type text NOT NULL,
  payload_json jsonb NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  published_at timestamp with time zone,
  publish_attempts integer NOT NULL DEFAULT 0
);

-- Table: athena.chat_room_members
CREATE TABLE IF NOT EXISTS athena.chat_room_members (
  room_id uuid NOT NULL,
  user_id text NOT NULL,
  role text NOT NULL,
  joined_at timestamp with time zone NOT NULL DEFAULT now(),
  last_read_seq bigint NOT NULL DEFAULT 0,
  last_read_message_id uuid,
  muted boolean NOT NULL DEFAULT false,
  notification_mode text,
  hidden_at timestamp with time zone
);

-- Table: athena.chat_rooms
CREATE TABLE IF NOT EXISTS athena.chat_rooms (
  id uuid NOT NULL,
  organization_id text NOT NULL,
  kind text NOT NULL,
  title text,
  created_by text NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now(),
  archived_at timestamp with time zone,
  last_message_id uuid,
  last_message_seq bigint NOT NULL DEFAULT 0,
  last_message_at timestamp with time zone,
  version bigint NOT NULL DEFAULT 1
);

-- Table: athena.chat_session_auth_log
CREATE TABLE IF NOT EXISTS athena.chat_session_auth_log (
  id bigint NOT NULL,
  chat_session_auth_log_id uuid NOT NULL DEFAULT gen_random_uuid(),
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  request_id text NOT NULL,
  client_name text,
  transport text NOT NULL,
  method text NOT NULL,
  path text NOT NULL,
  auth_source text,
  outcome text NOT NULL,
  failure_reason text,
  user_id text,
  session_id text,
  organization_id text,
  remote_addr text,
  user_agent text,
  details jsonb NOT NULL DEFAULT '{}'::jsonb
);

-- Table: athena.email_event_types
CREATE TABLE IF NOT EXISTS athena.email_event_types (
  event_type text NOT NULL,
  category text NOT NULL,
  description text NOT NULL,
  default_template_key text,
  required_variables jsonb NOT NULL DEFAULT '[]'::jsonb,
  optional_variables jsonb NOT NULL DEFAULT '[]'::jsonb,
  is_active boolean NOT NULL DEFAULT true,
  is_system boolean NOT NULL DEFAULT true,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);

-- Table: athena.email_send_failures
CREATE TABLE IF NOT EXISTS athena.email_send_failures (
  id text NOT NULL,
  user_id text,
  recipient_email text NOT NULL,
  flow text NOT NULL,
  provider text,
  error_message text NOT NULL,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  resolved boolean NOT NULL DEFAULT false,
  resolution_note text,
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);

-- Table: athena.email_templates
CREATE TABLE IF NOT EXISTS athena.email_templates (
  id text NOT NULL,
  template_key text NOT NULL,
  locale text NOT NULL DEFAULT 'en'::text,
  subject_template text NOT NULL,
  text_template text,
  html_template text,
  variables jsonb NOT NULL DEFAULT '[]'::jsonb,
  is_active boolean NOT NULL DEFAULT true,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now(),
  event_type text,
  variable_bindings jsonb NOT NULL DEFAULT '[]'::jsonb,
  attachments jsonb NOT NULL DEFAULT '[]'::jsonb,
  attachment_failure_mode text NOT NULL DEFAULT 'fail'::text
);

-- Table: athena.emails
CREATE TABLE IF NOT EXISTS athena.emails (
  id text NOT NULL,
  recipient_email text NOT NULL,
  subject text NOT NULL,
  from_address text NOT NULL,
  from_name text,
  text_body text,
  html_body text,
  provider text NOT NULL,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  flow text,
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);

-- Table: athena.invitation
CREATE TABLE IF NOT EXISTS athena.invitation (
  id text NOT NULL,
  organization_id text NOT NULL,
  email text NOT NULL,
  role text NOT NULL DEFAULT 'member'::text,
  status text NOT NULL DEFAULT 'pending'::text,
  inviter_id text NOT NULL,
  expires_at timestamp with time zone NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  customer_id uuid
);

-- Table: athena.ip_profiles
CREATE TABLE IF NOT EXISTS athena.ip_profiles (
  id text NOT NULL,
  ip_address text NOT NULL,
  provider text NOT NULL,
  country_code text,
  country_name text,
  region_name text,
  city text,
  latitude double precision,
  longitude double precision,
  timezone text,
  isp text,
  organization text,
  asn text,
  is_vpn boolean,
  is_proxy boolean,
  is_tor boolean,
  is_hosting boolean,
  raw_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  lookup_expires_at timestamp with time zone,
  first_seen_at timestamp with time zone NOT NULL DEFAULT now(),
  last_seen_at timestamp with time zone NOT NULL DEFAULT now(),
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);

-- Table: athena.member
CREATE TABLE IF NOT EXISTS athena.member (
  id text NOT NULL,
  organization_id text NOT NULL,
  user_id text NOT NULL,
  role text NOT NULL DEFAULT 'member'::text,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  customer_id uuid
);

-- Table: athena.oauth_clients
CREATE TABLE IF NOT EXISTS athena.oauth_clients (
  id text NOT NULL,
  client_name text NOT NULL,
  provider text NOT NULL,
  description text,
  response_type text NOT NULL DEFAULT 'code'::text,
  grant_type text NOT NULL DEFAULT 'authorization_code'::text,
  token_authentication_method text NOT NULL DEFAULT 'none'::text,
  redirect_uris text[] NOT NULL DEFAULT ARRAY[]::text[],
  client_url text,
  scopes text[] NOT NULL DEFAULT ARRAY[]::text[],
  is_active boolean NOT NULL DEFAULT true,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);

-- Table: athena.organization
CREATE TABLE IF NOT EXISTS athena.organization (
  id text NOT NULL,
  name text NOT NULL,
  slug text NOT NULL,
  logo text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);

-- Table: athena.passkeys
CREATE TABLE IF NOT EXISTS athena.passkeys (
  id text NOT NULL,
  name text NOT NULL,
  public_key text NOT NULL,
  user_id text NOT NULL,
  credential_id text NOT NULL,
  counter bigint NOT NULL DEFAULT 0,
  device_type text NOT NULL DEFAULT 'singleDevice'::text,
  backed_up boolean NOT NULL DEFAULT false,
  transports text,
  created_at timestamp with time zone NOT NULL DEFAULT now()
);

-- Table: athena.session_ip_profiles
CREATE TABLE IF NOT EXISTS athena.session_ip_profiles (
  id text NOT NULL,
  session_id text NOT NULL,
  session_token text NOT NULL,
  user_id text NOT NULL,
  ip_address text NOT NULL,
  ip_profile_id text,
  country_code text,
  country_name text,
  is_vpn boolean,
  is_proxy boolean,
  is_tor boolean,
  is_hosting boolean,
  country_changed boolean NOT NULL DEFAULT false,
  previous_country_code text,
  provider text,
  created_at timestamp with time zone NOT NULL DEFAULT now()
);

-- Table: athena.sessions
CREATE TABLE IF NOT EXISTS athena.sessions (
  id text NOT NULL,
  expires_at timestamp with time zone NOT NULL,
  token text NOT NULL,
  ip_address text,
  user_agent text,
  user_id text NOT NULL,
  impersonated_by text,
  active_organization_id text,
  active boolean NOT NULL DEFAULT true,
  created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ipv4_address text
);

-- Table: athena.storage_audit_events
CREATE TABLE IF NOT EXISTS athena.storage_audit_events (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  operation text NOT NULL,
  file_id text,
  s3_id uuid,
  bucket text,
  storage_key text,
  actor_user_id text,
  organization_id text,
  status text NOT NULL,
  error text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamp with time zone NOT NULL DEFAULT now()
);

-- Table: athena.two_factor
CREATE TABLE IF NOT EXISTS athena.two_factor (
  id text NOT NULL,
  secret text NOT NULL,
  backup_codes text,
  user_id text NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);

-- Table: athena.user_permission_scopes
CREATE TABLE IF NOT EXISTS athena.user_permission_scopes (
  id bigint NOT NULL,
  created_at timestamp with time zone DEFAULT now(),
  user_id text,
  scope text,
  global boolean DEFAULT false,
  organization_id text,
  user_permission_scope_id uuid DEFAULT gen_random_uuid(),
  enabled boolean DEFAULT false
);

-- Table: athena.users
CREATE TABLE IF NOT EXISTS athena.users (
  id text NOT NULL,
  name text,
  email text,
  email_verified boolean NOT NULL DEFAULT false,
  image text,
  username text,
  display_username text,
  two_factor_enabled boolean NOT NULL DEFAULT false,
  role text,
  banned boolean NOT NULL DEFAULT false,
  ban_reason text,
  ban_expires timestamp with time zone,
  last_sign_in_at timestamp with time zone,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);

-- Table: athena.verifications
CREATE TABLE IF NOT EXISTS athena.verifications (
  id text NOT NULL,
  identifier text NOT NULL,
  value text NOT NULL,
  expires_at timestamp with time zone NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS athena.webhook_sink_events (
  id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  webhook_sink_event_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now(),
  sink_name text NOT NULL,
  received_at timestamptz NOT NULL,
  request_path text NOT NULL,
  http_method text NOT NULL,
  source_ip text,
  content_type text,
  verified boolean NOT NULL DEFAULT false,
  event_type text,
  external_id text,
  query_params_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  headers_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  payload_json jsonb,
  payload_text text,
  payload_sha256 text NOT NULL,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  CONSTRAINT webhook_sink_events_sink_name_nonempty CHECK (btrim(sink_name) <> ''),
  CONSTRAINT webhook_sink_events_request_path_nonempty CHECK (btrim(request_path) <> ''),
  CONSTRAINT webhook_sink_events_http_method_nonempty CHECK (btrim(http_method) <> ''),
  CONSTRAINT webhook_sink_events_payload_sha256_nonempty CHECK (btrim(payload_sha256) <> ''),
  CONSTRAINT webhook_sink_events_query_params_json_object CHECK (jsonb_typeof(query_params_json) = 'object'),
  CONSTRAINT webhook_sink_events_headers_json_object CHECK (jsonb_typeof(headers_json) = 'object'),
  CONSTRAINT webhook_sink_events_metadata_object CHECK (jsonb_typeof(metadata) = 'object')
);

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;
