-- =============================================================================
-- SSO VietProDev — Clean Consolidated Migration
-- Version: 1.0.0
-- Date: 2026-06-18
-- Purpose: Single, clean, ordered migration for the SSO project
--          Consolidates all 37 original migrations in dependency order
-- Design principles:
--   - Idempotent: All CREATE statements use IF NOT EXISTS / IF NOT EXISTS
--   - Ordered: Tables created in dependency order (no forward references)
--   - No duplicates: Consolidated index/constraint changes into single ops
--   - Production-ready: triggers, functions, partitions, views included
-- =============================================================================

BEGIN;

-- =============================================================================
-- PHASE 1: EXTENSIONS
-- =============================================================================

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "citext";
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- =============================================================================
-- PHASE 2: ENUM TYPES
-- =============================================================================

-- user_status: Account status
DO $$ BEGIN
    CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended', 'pending_verification');
EXCEPTION WHEN duplicate_object THEN null;
END $$;

-- session_status: Session lifecycle state
DO $$ BEGIN
    CREATE TYPE session_status AS ENUM ('active', 'revoked', 'expired', 'logged_out');
EXCEPTION WHEN duplicate_object THEN null;
END $$;

-- platform_type: Client platform
DO $$ BEGIN
    CREATE TYPE platform_type AS ENUM ('web', 'mobile', 'zalo_miniapp', 'internal');
EXCEPTION WHEN duplicate_object THEN null;
END $$;

-- notification_status: Delivery status
DO $$ BEGIN
    CREATE TYPE notification_status AS ENUM (
        'queued',
        'processing',
        'sent',
        'delivered',
        'failed',
        'cancelled',
        'expired'
    );
EXCEPTION WHEN duplicate_object THEN null;
END $$;

-- notification_type: Constrained notification categories
DO $$ BEGIN
    CREATE TYPE notification_type AS ENUM (
        'order_created',
        'payment_success',
        'payment_failed',
        'order_shipped',
        'order_delivered',
        'system_alert',
        'promotion',
        'chat_message',
        'security_alert',
        'account_update'
    );
EXCEPTION WHEN duplicate_object THEN null;
END $$;

-- notification_channel: Delivery channel
DO $$ BEGIN
    CREATE TYPE notification_channel AS ENUM (
        'in_app',
        'push',
        'email',
        'sms',
        'zalo'
    );
EXCEPTION WHEN duplicate_object THEN null;
END $$;

-- notification_event_type: Analytics events
DO $$ BEGIN
    CREATE TYPE notification_event_type AS ENUM (
        'read',
        'opened',
        'clicked',
        'dismissed'
    );
EXCEPTION WHEN duplicate_object THEN null;
END $$;

-- file_provider: Storage backend
DO $$ BEGIN
    CREATE TYPE enum_file_provider AS ENUM ('local', 'minio', 's3');
EXCEPTION WHEN duplicate_object THEN null;
END $$;

-- file_status: Upload lifecycle
DO $$ BEGIN
    CREATE TYPE enum_file_status AS ENUM (
        'pending_upload',
        'uploaded',
        'scanning',
        'quarantined',
        'processing',
        'ready',
        'failed',
        'expired'
    );
EXCEPTION WHEN duplicate_object THEN null;
END $$;

-- file_visibility: Access scope
DO $$ BEGIN
    CREATE TYPE enum_file_visibility AS ENUM (
        'private',
        'tenant',
        'public'
    );
EXCEPTION WHEN duplicate_object THEN null;
END $$;

-- webhook_status: Webhook processing state
DO $$ BEGIN
    CREATE TYPE webhook_status AS ENUM ('PENDING', 'PROCESSING', 'SUCCESS', 'FAILED');
EXCEPTION WHEN duplicate_object THEN null;
END $$;

-- audit_severity: Audit event severity
DO $$ BEGIN
    CREATE TYPE audit_severity AS ENUM ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL');
EXCEPTION WHEN duplicate_object THEN null;
END $$;

-- =============================================================================
-- PHASE 3: CORE AUTH TABLES (001-auth-schema + 004 consolidations)
-- =============================================================================

-- users: Master user account table
CREATE TABLE IF NOT EXISTS users (
    id             UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email          CITEXT NOT NULL,
    username       CITEXT UNIQUE,
    first_name     VARCHAR(100),
    last_name      VARCHAR(100),
    phone          VARCHAR(20),
    avatar_id      UUID,  -- FK added after files table; managed separately
    status         user_status DEFAULT 'pending_verification',
    email_verified_at TIMESTAMP WITH TIME ZONE,
    address        TEXT,
    created_by     UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by     UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    deleted_at     TIMESTAMP WITH TIME ZONE,

    CONSTRAINT users_phone_format CHECK (
        phone IS NULL OR phone ~* '^\+?[0-9\s\-\(\)]+$'
    )
);

-- user_auth: Password + 2FA credentials
CREATE TABLE IF NOT EXISTS user_auth (
    id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id             UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    password_hash       VARCHAR(255) NOT NULL,
    is_oauth_only       BOOLEAN DEFAULT FALSE,  -- OAuth-only accounts (no password)
    last_login_at       TIMESTAMP WITH TIME ZONE,
    password_changed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    login_attempts      INTEGER DEFAULT 0,
    locked_until        TIMESTAMP WITH TIME ZONE,
    twofa_secret        VARCHAR(255),
    twofa_enabled       BOOLEAN DEFAULT FALSE,
    twofa_method        VARCHAR(20) DEFAULT 'totp',
    created_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT user_auth_twofa_method CHECK (twofa_method IN ('totp', 'sms', 'email')),
    UNIQUE (user_id)
);

-- roles: Role definitions with multi-tenant support
CREATE TABLE IF NOT EXISTS roles (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name            VARCHAR(50) NOT NULL,
    description     TEXT,
    tenant_id       UUID,
    is_system_role  BOOLEAN DEFAULT FALSE,
    scope           VARCHAR(100),
    created_by      UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by      UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    UNIQUE (tenant_id, name)
);

-- permissions: Granular permission definitions
CREATE TABLE IF NOT EXISTS permissions (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    key         VARCHAR(150) UNIQUE NOT NULL,
    resource    VARCHAR(100) NOT NULL,
    action      VARCHAR(50) NOT NULL,
    name        VARCHAR(100),
    description TEXT,
    created_by  UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by  UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    UNIQUE (resource, action)
);

-- user_roles: User-role assignments
CREATE TABLE IF NOT EXISTS user_roles (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    role_id     UUID NOT NULL REFERENCES roles(id) ON DELETE RESTRICT,
    assigned_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    assigned_by UUID REFERENCES users(id) ON DELETE SET NULL,
    is_primary  BOOLEAN DEFAULT FALSE,
    created_by  UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by  UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    UNIQUE (user_id, role_id)
);

-- role_permissions: Role-permission assignments
CREATE TABLE IF NOT EXISTS role_permissions (
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    role_id       UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    permission_id UUID NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
    created_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by    UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by    UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    UNIQUE (role_id, permission_id)
);

-- user_sessions: Session management with token rotation
CREATE TABLE IF NOT EXISTS user_sessions (
    id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id             UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    token_version       INTEGER DEFAULT 1,
    status              session_status DEFAULT 'active',
    device_info         JSONB,
    ip_address          INET,
    user_agent          TEXT,
    expires_at          TIMESTAMP WITH TIME ZONE NOT NULL,
    refresh_expires_at  TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_activity_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    platform            platform_type,
    device_id           VARCHAR(255),
    device_name         VARCHAR(255),
    revoked_at          TIMESTAMP WITH TIME ZONE,
    logged_out_at       TIMESTAMP WITH TIME ZONE,
    tenant_id           UUID,
    refresh_token_hash  VARCHAR(64),
    access_token_hash   VARCHAR(64)
);

-- password_reset_tokens: Password reset flow tokens
CREATE TABLE IF NOT EXISTS password_reset_tokens (
    id         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id    UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    token_hash CHAR(64) UNIQUE NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    used_at    TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- email_verify_tokens: Email verification tokens
CREATE TABLE IF NOT EXISTS email_verify_tokens (
    id         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id    UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    token_hash CHAR(64) UNIQUE NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    used_at    TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- user_password_history: Password reuse prevention
CREATE TABLE IF NOT EXISTS user_password_history (
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id       UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    password_hash VARCHAR(255) NOT NULL,
    changed_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    changed_by    UUID REFERENCES users(id),
    reason        TEXT
);

-- =============================================================================
-- PHASE 4: FILE STORAGE (002-file-schema)
-- =============================================================================

-- files: Centralized file storage with lifecycle
CREATE TABLE IF NOT EXISTS files (
    id             UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id      UUID,
    owner_user_id  UUID REFERENCES users(id) ON DELETE SET NULL,
    original_name  VARCHAR(255) NOT NULL,
    display_name   VARCHAR(255),
    title          VARCHAR(255),
    description    TEXT,
    note           TEXT,
    provider       enum_file_provider NOT NULL DEFAULT 'local',
    bucket_name    VARCHAR(100) NOT NULL DEFAULT '',
    object_key     TEXT NOT NULL,
    mime_type      VARCHAR(100) NOT NULL,
    extension      VARCHAR(20),
    category       VARCHAR(50),
    size_bytes     BIGINT NOT NULL CHECK (size_bytes >= 0),
    width          INTEGER,
    height         INTEGER,
    duration_sec   INTEGER,
    page_count     INTEGER,
    checksum_sha256 VARCHAR(64),
    visibility     enum_file_visibility NOT NULL DEFAULT 'private',
    public_token  VARCHAR(100),
    is_library     BOOLEAN NOT NULL DEFAULT FALSE,
    status         enum_file_status NOT NULL DEFAULT 'pending_upload',
    last_error     TEXT,
    processed_at   TIMESTAMPTZ,
    deleted_at     TIMESTAMPTZ,
    deleted_by     UUID REFERENCES users(id) ON DELETE SET NULL,
    created_by     UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by     UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_files_storage_location  UNIQUE (provider, bucket_name, object_key),
    CONSTRAINT uq_files_public_token     UNIQUE (public_token)
);

-- file_variants: Derivatives (thumbnails, resized, etc.)
CREATE TABLE IF NOT EXISTS file_variants (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    file_id     UUID NOT NULL REFERENCES files(id) ON DELETE CASCADE,
    variant_name VARCHAR(50) NOT NULL,
    provider    enum_file_provider NOT NULL DEFAULT 'local',
    bucket_name VARCHAR(100) NOT NULL DEFAULT '',
    object_key  TEXT NOT NULL,
    mime_type   VARCHAR(100),
    size_bytes  BIGINT CHECK (size_bytes >= 0),
    width       INTEGER,
    height      INTEGER,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_file_variant             UNIQUE (file_id, variant_name),
    CONSTRAINT uq_variant_storage_location UNIQUE (provider, bucket_name, object_key)
);

-- Add avatar_id FK to users now that files table exists
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = 'public' AND table_name = 'users' AND column_name = 'avatar_id'
    ) THEN
        ALTER TABLE users ADD COLUMN avatar_id UUID REFERENCES files(id) ON DELETE SET NULL;
    END IF;
END $$;

-- =============================================================================
-- PHASE 5: NOTIFICATION (003-notification-schema + 032 templates)
-- =============================================================================

-- notification_messages: Broadcast message templates
CREATE TABLE IF NOT EXISTS notification_messages (
    id           UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    title        VARCHAR(255) NOT NULL,
    body         TEXT,
    type         notification_type NOT NULL,
    data         JSONB,
    tenant_id    UUID,
    scheduled_at TIMESTAMP WITH TIME ZONE,
    priority     INTEGER DEFAULT 0 CHECK (priority >= 0),
    created_by   UUID,
    campaign_id  UUID,
    template_id  UUID,
    external_key VARCHAR(255),
    dedupe_key   VARCHAR(255),
    created_at   TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- notification_receipts: Per-user inbox state
CREATE TABLE IF NOT EXISTS notification_receipts (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    message_id  UUID NOT NULL REFERENCES notification_messages(id) ON DELETE CASCADE,
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    channel     notification_channel NOT NULL,
    read_at     TIMESTAMP WITH TIME ZONE,
    deleted_at  TIMESTAMP WITH TIME ZONE,
    archived_at TIMESTAMP WITH TIME ZONE,
    created_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    UNIQUE (message_id, user_id, channel)
);

-- notification_devices: Push token registry
CREATE TABLE IF NOT EXISTS notification_devices (
    id                   UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id              UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    platform             platform_type NOT NULL,
    device_id            VARCHAR(255) NOT NULL,
    device_name          VARCHAR(255),
    device_info          JSONB,
    push_token           TEXT,
    one_signal_player_id TEXT,
    is_active            BOOLEAN DEFAULT TRUE,
    last_used_at         TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at           TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at           TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    UNIQUE (user_id, device_id, platform)
);

-- notification_delivery_attempts: Delivery tracking (partitioned)
CREATE TABLE IF NOT EXISTS notification_delivery_attempts (
    id                  UUID DEFAULT uuid_generate_v4(),
    receipt_id          UUID NOT NULL REFERENCES notification_receipts(id) ON DELETE CASCADE,
    device_id           UUID REFERENCES notification_devices(id) ON DELETE SET NULL,
    channel             notification_channel NOT NULL,
    status              notification_status NOT NULL,
    attempt_no          INTEGER DEFAULT 1 CHECK (attempt_no >= 1),
    provider_name       VARCHAR(50),
    provider_message_id VARCHAR(255),
    latency_ms          INTEGER CHECK (latency_ms >= 0),
    response_payload    JSONB,
    error_message      TEXT,
    sent_at             TIMESTAMP WITH TIME ZONE,
    delivered_at        TIMESTAMP WITH TIME ZONE,
    failed_at           TIMESTAMP WITH TIME ZONE,
    next_retry_at       TIMESTAMP WITH TIME ZONE,
    created_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    CHECK (attempt_no >= 1),
    CHECK (latency_ms >= 0),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- notification_preferences: User notification settings
CREATE TABLE IF NOT EXISTS notification_preferences (
    id                 UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id            UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    notification_type  notification_type NOT NULL,
    channel            notification_channel NOT NULL,
    is_enabled         BOOLEAN DEFAULT TRUE,
    quiet_hours_start  TIME,
    quiet_hours_end    TIME,
    timezone           VARCHAR(50) DEFAULT 'Asia/Ho_Chi_Minh',
    created_at         TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at         TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    UNIQUE (user_id, notification_type, channel)
);

-- notification_templates: Templated notification content
-- type/channel/status are VARCHAR (not ENUM) since notification_template_* ENUMs were dropped
CREATE TABLE IF NOT EXISTS notification_templates (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code        VARCHAR(50) NOT NULL UNIQUE,
    name        VARCHAR(255) NOT NULL,
    type        VARCHAR(50) NOT NULL,
    channel     VARCHAR(50) NOT NULL,
    content     TEXT NOT NULL,
    subject     VARCHAR(500),
    description TEXT,
    variables   JSONB,
    status      VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    created_by  UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by  UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at  TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- webhook_logs: Incoming webhook audit trail for partner integrations.
-- resident_id is a free UUID (no FK) because the legacy residents table was removed.
CREATE TABLE IF NOT EXISTS webhook_logs (
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    event_id      VARCHAR(255),
    webhook_type  VARCHAR(50) NOT NULL,
    status        webhook_status NOT NULL DEFAULT 'PROCESSING',
    payload       JSONB,
    response_data JSONB,
    error_message TEXT,
    resident_id   UUID,
    ip_address    INET,
    user_agent    TEXT,
    retry_count   INTEGER DEFAULT 0,
    processed_at  TIMESTAMP WITH TIME ZONE,
    failed_at     TIMESTAMP WITH TIME ZONE,
    created_at    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- =============================================================================
-- PHASE 14: OIDC / OAUTH2 (035, 036)
-- =============================================================================

-- oidc_grants: OIDC AuthorizationCode, RefreshToken, AccessToken storage
CREATE TABLE IF NOT EXISTS oidc_grants (
    id        VARCHAR(128) NOT NULL,
    model     VARCHAR(64) NOT NULL,
    payload   JSONB NOT NULL,
    expires_at TIMESTAMPTZ,
    consumed  BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (model, id)
);

-- clients: OIDC registered applications
CREATE TABLE IF NOT EXISTS clients (
    id                            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    app_code                      TEXT UNIQUE NOT NULL,
    client_id                     TEXT UNIQUE NOT NULL,
    client_secret_hash            TEXT,
    name                          TEXT NOT NULL,
    redirect_uris                 TEXT[] NOT NULL DEFAULT '{}',
    post_logout_redirect_uris    TEXT[] NOT NULL DEFAULT '{}',
    grant_types                   TEXT[] NOT NULL DEFAULT '{authorization_code,refresh_token}',
    response_types                TEXT[] NOT NULL DEFAULT '{code}',
    scopes                        TEXT[] NOT NULL DEFAULT '{openid,profile,email}',
    token_endpoint_auth_method    TEXT NOT NULL DEFAULT 'none',
    require_pkce                  BOOLEAN NOT NULL DEFAULT TRUE,
    status                        TEXT NOT NULL DEFAULT 'active',
    created_at                    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- =============================================================================
-- PHASE 15: AUDIT SCHEMA (014, 015)
-- =============================================================================

CREATE SCHEMA IF NOT EXISTS audit;

-- audit_maintainer: Dedicated role for audit maintenance
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'audit_maintainer') THEN
        CREATE ROLE audit_maintainer;
    END IF;
END $$;

-- audit_outbox: Transactional outbox for async audit processing
CREATE TABLE IF NOT EXISTS audit.audit_outbox (
    id             UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    request_id     UUID NOT NULL,
    trace_id       UUID NOT NULL,
    event_type     VARCHAR(50) NOT NULL,
    payload        JSONB NOT NULL,
    payload_size   INTEGER CHECK (payload_size <= 65536),
    status         VARCHAR(20) DEFAULT 'PENDING',
    processed_at   TIMESTAMP WITH TIME ZONE,
    error_message  TEXT,
    retry_count    INTEGER DEFAULT 0,
    max_retries    INTEGER DEFAULT 3,
    failed_at      TIMESTAMP WITH TIME ZONE,
    created_at     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT audit_outbox_status_check CHECK (
        status IN ('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED', 'RETRYING', 'FAILED_PERMANENT')
    )
);

-- system_audit_logs: Immutable audit log (partitioned)
CREATE TABLE IF NOT EXISTS audit.system_audit_logs (
    id              UUID DEFAULT uuid_generate_v4(),
    outbox_event_id UUID NOT NULL,
    request_id      UUID NOT NULL,
    trace_id        UUID NOT NULL,
    session_id      UUID,
    tenant_id       UUID,
    actor_id        UUID REFERENCES public.users(id) ON DELETE SET NULL,
    actor_name      VARCHAR(200),
    actor_email     VARCHAR(255),
    actor_role      VARCHAR(50),
    action          VARCHAR(50) NOT NULL,
    module          VARCHAR(50) NOT NULL,
    entity_id       UUID,
    entity_type     VARCHAR(50),
    batch_id        UUID,
    description     TEXT,
    severity        audit_severity DEFAULT 'LOW',
    ip_address      INET,
    user_agent      TEXT,
    payload         JSONB,
    payload_size    INTEGER,
    success         BOOLEAN DEFAULT TRUE,
    failure_reason  TEXT,
    event_version   INTEGER DEFAULT 1,
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_at),
    UNIQUE (outbox_event_id, created_at)
) PARTITION BY RANGE (created_at);

-- entity_change_logs: Field-level change tracking (partitioned)
CREATE TABLE IF NOT EXISTS audit.entity_change_logs (
    id             UUID DEFAULT uuid_generate_v4(),
    outbox_event_id UUID NOT NULL,
    request_id     UUID NOT NULL,
    trace_id       UUID NOT NULL,
    entity_type    VARCHAR(50) NOT NULL,
    entity_id      UUID NOT NULL,
    actor_id       UUID REFERENCES public.users(id) ON DELETE SET NULL,
    actor_name     VARCHAR(200),
    actor_email    VARCHAR(255),
    change_type    VARCHAR(50) NOT NULL,
    payload        JSONB NOT NULL,
    payload_size   INTEGER,
    reason         TEXT,
    event_version  INTEGER DEFAULT 1,
    created_at     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_at),
    UNIQUE (outbox_event_id, created_at)
) PARTITION BY RANGE (created_at);

-- auth_audit_logs: Auth event audit (partitioned)
CREATE TABLE IF NOT EXISTS audit.auth_audit_logs (
    id             UUID DEFAULT uuid_generate_v4(),
    outbox_event_id UUID,
    request_id     UUID,
    trace_id       UUID,
    user_id        UUID REFERENCES public.users(id) ON DELETE SET NULL,
    action         VARCHAR(50) NOT NULL,
    ip_address     INET,
    user_agent     TEXT,
    device_info    JSONB,
    success        BOOLEAN DEFAULT TRUE,
    failure_reason TEXT,
    session_id     UUID,
    event_version  INTEGER DEFAULT 1,
    created_at     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- refresh_token_audit_logs: Token rotation audit (partitioned)
CREATE TABLE IF NOT EXISTS audit.refresh_token_audit_logs (
    id                   UUID DEFAULT uuid_generate_v4(),
    outbox_event_id      UUID,
    request_id           UUID,
    trace_id             UUID,
    user_id              UUID NOT NULL REFERENCES public.users(id) ON DELETE RESTRICT,
    session_id           UUID,
    action               VARCHAR(50) NOT NULL,
    ip_address           INET,
    user_agent           TEXT,
    platform             VARCHAR(50),
    device_id            VARCHAR(255),
    previous_token_hash  VARCHAR(255),
    new_token_hash       VARCHAR(255),
    reason               TEXT,
    event_version        INTEGER DEFAULT 1,
    created_at           TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- audit_dead_letter: Failed audit event queue
CREATE TABLE IF NOT EXISTS audit.audit_dead_letter (
    id                    UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    original_event_id     UUID NOT NULL,
    event_type            VARCHAR(50) NOT NULL,
    original_payload      JSONB NOT NULL,
    error_message         TEXT NOT NULL,
    error_stack          TEXT,
    failed_at             TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    replayed             BOOLEAN DEFAULT FALSE,
    replayed_at          TIMESTAMP WITH TIME ZONE,
    replay_count         INTEGER DEFAULT 0,
    investigation_status VARCHAR(50) DEFAULT 'PENDING',
    resolution_notes      TEXT,
    created_at            TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create dynamic partitions for current + next 3 months
DO $$
DECLARE
    i INTEGER;
    start_date DATE;
    end_date DATE;
    part_name TEXT;
    tables TEXT[] := ARRAY['system_audit_logs', 'entity_change_logs', 'auth_audit_logs', 'refresh_token_audit_logs'];
    tbl TEXT;
BEGIN
    FOREACH tbl IN ARRAY tables LOOP
        FOR i IN 0..3 LOOP
            start_date := date_trunc('month', CURRENT_DATE + (i || ' months')::interval);
            end_date := start_date + INTERVAL '1 month';
            part_name := tbl || '_' || to_char(start_date, 'YYYY_MM');

            EXECUTE format(
                'CREATE TABLE IF NOT EXISTS audit.%I PARTITION OF audit.%I
                 FOR VALUES FROM (%L) TO (%L)',
                part_name, tbl, start_date, end_date
            );
        END LOOP;

        -- Default partition for safety
        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS audit.%I_default PARTITION OF audit.%I DEFAULT',
            tbl, tbl
        );
    END LOOP;
END $$;

-- =============================================================================
-- PHASE 16: INDEXES
-- =============================================================================

-- users indexes
CREATE UNIQUE INDEX IF NOT EXISTS ux_users_email_active ON users(email) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_users_status          ON users(status);
CREATE INDEX IF NOT EXISTS idx_users_created_at      ON users(created_at);
CREATE INDEX IF NOT EXISTS idx_users_deleted_at     ON users(deleted_at);
CREATE INDEX IF NOT EXISTS idx_users_username        ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_avatar_id       ON users(avatar_id);
CREATE INDEX IF NOT EXISTS idx_users_email_verified_at ON users(email_verified_at);

-- user_auth indexes
CREATE INDEX IF NOT EXISTS idx_user_auth_user_id       ON user_auth(user_id);
CREATE INDEX IF NOT EXISTS idx_user_auth_locked_until  ON user_auth(locked_until);
CREATE INDEX IF NOT EXISTS idx_user_auth_twofa_enabled ON user_auth(twofa_enabled) WHERE twofa_enabled = TRUE;
CREATE INDEX IF NOT EXISTS idx_user_auth_is_oauth_only  ON user_auth(is_oauth_only) WHERE is_oauth_only = TRUE;

-- roles / permissions indexes
CREATE INDEX IF NOT EXISTS idx_roles_name            ON roles(name);
CREATE UNIQUE INDEX IF NOT EXISTS idx_permissions_key        ON permissions(key);
CREATE INDEX IF NOT EXISTS idx_permissions_resource   ON permissions(resource);
CREATE INDEX IF NOT EXISTS idx_user_roles_user_id     ON user_roles(user_id);
CREATE INDEX IF NOT EXISTS idx_user_roles_role_id     ON user_roles(role_id);
CREATE INDEX IF NOT EXISTS idx_role_permissions_role_id ON role_permissions(role_id);

-- user_sessions indexes (optimized - only critical ones kept)
CREATE INDEX IF NOT EXISTS idx_user_sessions_user_id        ON user_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_user_sessions_expires_at     ON user_sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_user_sessions_status        ON user_sessions(status);
CREATE UNIQUE INDEX IF NOT EXISTS idx_user_sessions_refresh_token_hash ON user_sessions(refresh_token_hash) WHERE status = 'active';
CREATE UNIQUE INDEX IF NOT EXISTS idx_user_sessions_access_token_hash  ON user_sessions(access_token_hash) WHERE status = 'active' AND access_token_hash IS NOT NULL;

-- token tables indexes
CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_user_id    ON password_reset_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_expires_at ON password_reset_tokens(expires_at);
CREATE INDEX IF NOT EXISTS idx_email_verify_tokens_user_id      ON email_verify_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_email_verify_tokens_expires_at  ON email_verify_tokens(expires_at);

-- password history indexes
CREATE INDEX IF NOT EXISTS idx_user_password_history_user_id    ON user_password_history(user_id);
CREATE INDEX IF NOT EXISTS idx_user_password_history_changed_at ON user_password_history(changed_at DESC);

-- files indexes
CREATE INDEX IF NOT EXISTS idx_files_owner_created  ON files(owner_user_id, created_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_files_tenant_created  ON files(tenant_id, created_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_files_status_created ON files(status, created_at ASC) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_files_category        ON files(category) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_files_is_library      ON files(is_library) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_files_visibility      ON files(visibility);
CREATE INDEX IF NOT EXISTS idx_files_checksum        ON files(checksum_sha256);
CREATE INDEX IF NOT EXISTS idx_files_owner_size      ON files(owner_user_id, size_bytes) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_file_variants_file_created ON file_variants(file_id, created_at DESC);

-- notification indexes
CREATE INDEX IF NOT EXISTS idx_notification_messages_type   ON notification_messages(type);
CREATE INDEX IF NOT EXISTS idx_notification_messages_dedupe_key ON notification_messages(dedupe_key) WHERE dedupe_key IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_notification_receipts_unread  ON notification_receipts(user_id, created_at DESC) WHERE read_at IS NULL AND deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_notification_receipts_user_id ON notification_receipts(user_id);
CREATE INDEX IF NOT EXISTS idx_notification_devices_user_platform ON notification_devices(user_id, platform);
CREATE INDEX IF NOT EXISTS idx_notification_devices_push_token ON notification_devices(push_token) WHERE push_token IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_notification_delivery_attempts_retry ON notification_delivery_attempts(status, next_retry_at) WHERE status IN ('queued', 'failed');
CREATE INDEX IF NOT EXISTS idx_notification_prefs_user_id ON notification_preferences(user_id);
CREATE INDEX IF NOT EXISTS idx_notification_templates_type_status ON notification_templates(type, status);

-- webhook_logs indexes
CREATE INDEX IF NOT EXISTS idx_webhook_logs_event_id     ON webhook_logs(event_id);
CREATE INDEX IF NOT EXISTS idx_webhook_logs_status       ON webhook_logs(status);
CREATE INDEX IF NOT EXISTS idx_webhook_logs_webhook_type ON webhook_logs(webhook_type);

-- oidc_grants indexes
CREATE INDEX IF NOT EXISTS idx_oidc_grants_expires_at           ON oidc_grants(expires_at);
CREATE INDEX IF NOT EXISTS idx_oidc_grants_payload_uid           ON oidc_grants((payload->>'uid'));

-- audit schema indexes
CREATE INDEX IF NOT EXISTS idx_audit_outbox_status_created ON audit.audit_outbox(status, created_at);
CREATE INDEX IF NOT EXISTS idx_audit_outbox_pending_created ON audit.audit_outbox(status, created_at) WHERE status = 'PENDING';
CREATE INDEX IF NOT EXISTS idx_audit_outbox_retry ON audit.audit_outbox(status, retry_count) WHERE status = 'FAILED';
CREATE INDEX IF NOT EXISTS idx_system_audit_actor_id   ON audit.system_audit_logs(actor_id);
CREATE INDEX IF NOT EXISTS idx_system_audit_action     ON audit.system_audit_logs(action);
CREATE INDEX IF NOT EXISTS idx_system_audit_module     ON audit.system_audit_logs(module);
CREATE INDEX IF NOT EXISTS idx_system_audit_entity    ON audit.system_audit_logs(entity_type, entity_id);
CREATE INDEX IF NOT EXISTS idx_system_audit_created_at ON audit.system_audit_logs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_system_audit_created_brin ON audit.system_audit_logs USING BRIN(created_at);
CREATE INDEX IF NOT EXISTS idx_entity_change_entity   ON audit.entity_change_logs(entity_type, entity_id);
CREATE INDEX IF NOT EXISTS idx_entity_change_created  ON audit.entity_change_logs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_entity_change_created_brin ON audit.entity_change_logs USING BRIN(created_at);
CREATE INDEX IF NOT EXISTS idx_dead_letter_investigation_status ON audit.audit_dead_letter(investigation_status);
CREATE INDEX IF NOT EXISTS idx_refresh_token_audit_created_brin ON audit.refresh_token_audit_logs USING BRIN(created_at);

-- =============================================================================
-- PHASE 17: TRIGGERS
-- =============================================================================

-- Shared update_updated_at trigger function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply update_updated_at to all tables
DO $$
DECLARE
    tbl TEXT;
BEGIN
    FOREACH tbl IN ARRAY ARRAY[
        'user_auth', 'roles', 'user_roles', 'role_permissions',
        'files', 'notification_messages', 'notification_receipts',
        'notification_devices', 'notification_delivery_attempts',
        'users', 'user_auth', 'user_roles', 'role_permissions',
        'user_sessions', 'user_password_history',
        'password_reset_tokens', 'email_verify_tokens',
        'files', 'file_variants',
        'notification_messages', 'notification_receipts',
        'notification_devices', 'notification_delivery_attempts',
        'notification_preferences', 'notification_templates',
        'webhook_logs',
        'oidc_grants', 'clients'
    ] LOOP
        EXECUTE format('DROP TRIGGER IF EXISTS update_%s_updated_at ON %s', tbl, tbl);
        EXECUTE format(
            'CREATE TRIGGER update_%s_updated_at BEFORE UPDATE ON %s FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()',
            tbl, tbl
        );
    END LOOP;
END $$;


-- Inject created_by from session variable
CREATE OR REPLACE FUNCTION inject_created_by()
RETURNS TRIGGER AS $$
BEGIN
    NEW.created_by := current_setting('app.current_user_id', true)::uuid;
    RETURN NEW;
EXCEPTION WHEN OTHERS THEN RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Inject updated_by from session variable
CREATE OR REPLACE FUNCTION inject_updated_by()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_by := current_setting('app.current_user_id', true)::uuid;
    RETURN NEW;
EXCEPTION WHEN OTHERS THEN RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- =============================================================================
-- PHASE 18: VIEWS
-- =============================================================================

-- active_users: Quick lookup of active user accounts
CREATE OR REPLACE VIEW active_users AS
SELECT
    u.id,
    u.email,
    u.username,
    u.first_name,
    u.last_name,
    primary_role.name AS primary_role_name,
    ua.last_login_at,
    u.created_at
FROM users u
LEFT JOIN user_auth ua ON u.id = ua.user_id
LEFT JOIN user_roles ur ON u.id = ur.user_id AND ur.is_primary = TRUE
LEFT JOIN roles primary_role ON ur.role_id = primary_role.id
WHERE u.status = 'active' AND u.deleted_at IS NULL;

-- user_role_details: Full user-role breakdown
CREATE OR REPLACE VIEW user_role_details AS
SELECT
    u.id            AS user_id,
    u.email,
    u.username,
    r.id            AS role_id,
    r.name          AS role_name,
    r.description   AS role_description,
    ur.is_primary,
    ur.assigned_at,
    ur.assigned_by,
    assigner.email  AS assigned_by_email
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
LEFT JOIN users assigner ON ur.assigned_by = assigner.id
WHERE u.deleted_at IS NULL
ORDER BY u.id, ur.is_primary DESC, ur.assigned_at ASC;

-- =============================================================================
-- PHASE 19: STORED FUNCTIONS
-- =============================================================================

-- cleanup_expired_sessions: Mark expired sessions as expired
CREATE OR REPLACE FUNCTION cleanup_expired_sessions()
RETURNS INTEGER AS $$
DECLARE updated_count INTEGER;
BEGIN
    UPDATE user_sessions
    SET status = 'expired', updated_at = CURRENT_TIMESTAMP
    WHERE status = 'active'
      AND (expires_at < CURRENT_TIMESTAMP OR refresh_expires_at < CURRENT_TIMESTAMP);
    GET DIAGNOSTICS updated_count = ROW_COUNT;
    RETURN updated_count;
END;
$$ LANGUAGE plpgsql;

-- cleanup_expired_password_reset_tokens: Purge expired reset tokens
CREATE OR REPLACE FUNCTION cleanup_expired_password_reset_tokens()
RETURNS INTEGER AS $$
DECLARE deleted_count INTEGER;
BEGIN
    DELETE FROM password_reset_tokens WHERE expires_at < CURRENT_TIMESTAMP;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

-- cleanup_expired_email_verify_tokens: Purge expired email tokens
CREATE OR REPLACE FUNCTION cleanup_expired_email_verify_tokens()
RETURNS INTEGER AS $$
DECLARE deleted_count INTEGER;
BEGIN
    DELETE FROM email_verify_tokens WHERE expires_at < CURRENT_TIMESTAMP;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

-- invalidate_user_sessions: Revoke all sessions for a user
CREATE OR REPLACE FUNCTION invalidate_user_sessions(user_uuid UUID)
RETURNS INTEGER AS $$
DECLARE updated_count INTEGER;
BEGIN
    UPDATE user_sessions
    SET token_version = token_version + 1,
        status = 'revoked',
        revoked_at = CURRENT_TIMESTAMP,
        updated_at = CURRENT_TIMESTAMP
    WHERE user_id = user_uuid AND status = 'active';
    GET DIAGNOSTICS updated_count = ROW_COUNT;
    RETURN updated_count;
END;
$$ LANGUAGE plpgsql;

-- mark_notifications_read: Bulk mark as read
CREATE OR REPLACE FUNCTION mark_notifications_read(user_uuid UUID)
RETURNS INTEGER AS $$
DECLARE updated_count INTEGER;
BEGIN
    UPDATE notification_receipts
    SET read_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
    WHERE user_id = user_uuid AND read_at IS NULL AND deleted_at IS NULL;
    GET DIAGNOSTICS updated_count = ROW_COUNT;
    RETURN updated_count;
END;
$$ LANGUAGE plpgsql;

-- cleanup_expired_notifications: Soft delete old notifications
CREATE OR REPLACE FUNCTION cleanup_expired_notifications()
RETURNS INTEGER AS $$
DECLARE updated_count INTEGER;
BEGIN
    UPDATE notification_receipts
    SET deleted_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
    WHERE id IN (
        SELECT id FROM notification_receipts
        WHERE created_at < CURRENT_TIMESTAMP - INTERVAL '90 days'
          AND deleted_at IS NULL
        LIMIT 10000
    );
    GET DIAGNOSTICS updated_count = ROW_COUNT;
    RETURN updated_count;
END;
$$ LANGUAGE plpgsql;

-- cleanup_old_dedupe_keys: Purge orphaned notification messages
CREATE OR REPLACE FUNCTION cleanup_old_dedupe_keys()
RETURNS INTEGER AS $$
DECLARE deleted_count INTEGER;
BEGIN
    DELETE FROM notification_messages
    WHERE dedupe_key IS NOT NULL
      AND created_at < CURRENT_TIMESTAMP - INTERVAL '7 days'
      AND id NOT IN (
          SELECT DISTINCT message_id FROM notification_receipts WHERE deleted_at IS NULL
      );
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

-- audit.notify_outbox_insert: NOTIFY channel on new outbox events
CREATE OR REPLACE FUNCTION audit.notify_outbox_insert()
RETURNS TRIGGER AS $$
BEGIN
    NOTIFY audit_channel;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_notify_outbox ON audit.audit_outbox;
CREATE TRIGGER trigger_notify_outbox
    AFTER INSERT ON audit.audit_outbox
    FOR EACH STATEMENT EXECUTE FUNCTION audit.notify_outbox_insert();

-- audit.recover_stuck_processing_events: Reset stale PROCESSING events
CREATE OR REPLACE FUNCTION audit.recover_stuck_processing_events()
RETURNS INTEGER AS $$
DECLARE recovered_count INTEGER;
BEGIN
    UPDATE audit.audit_outbox
    SET status = 'PENDING', updated_at = CURRENT_TIMESTAMP
    WHERE status = 'PROCESSING'
      AND updated_at < CURRENT_TIMESTAMP - INTERVAL '5 minutes';
    GET DIAGNOSTICS recovered_count = ROW_COUNT;
    RETURN recovered_count;
END;
$$ LANGUAGE plpgsql;

-- =============================================================================
-- PHASE 20: COMMENTS
-- =============================================================================

COMMENT ON TABLE users                          IS 'Master user account — identity and profile only. Auth credentials live in user_auth.';
COMMENT ON TABLE user_auth                     IS 'Password hash, 2FA secrets, and login security state.';
COMMENT ON TABLE user_sessions                 IS 'Active session state. Token rotation supported via refresh_token_hash.';
COMMENT ON TABLE oidc_grants                  IS 'OIDC AuthorizationCode/RefreshToken/AccessToken storage (consumed after use).';
COMMENT ON TABLE clients                      IS 'OIDC registered client applications (SPAs, mobile apps, etc.).';
COMMENT ON TABLE webhook_logs                 IS 'Incoming webhook audit trail for partner integrations.';
COMMENT ON TABLE audit.system_audit_logs      IS 'Immutable system-wide audit log (partitioned by month).';
COMMENT ON TABLE audit.entity_change_logs    IS 'Entity field-level change tracking (partitioned by month).';
COMMENT ON TABLE audit.audit_outbox           IS 'Transactional outbox for async audit event processing.';
COMMENT ON TABLE audit.audit_dead_letter      IS 'Dead letter queue for permanently failed audit events.';

COMMIT;
