-- 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);
