-- Active: 1775754236134@@mainline.proxy.rlwy.net@31091@railway
-- Full database provision for athena-auth (single script for psql -f sql/provision.sql).
-- Canonical DDL lives in migrations/001_create_core_tables.sql .. 014_email_event_types_and_template_assignment.sql;
-- keep this file in sync when adding migrations.
CREATE SCHEMA IF NOT EXISTS athena;

-- Athena Auth: Core tables (users, sessions, accounts, verifications)
CREATE TABLE
    IF NOT EXISTS athena.users (
        id TEXT PRIMARY KEY,
        name TEXT,
        email TEXT UNIQUE,
        email_verified BOOLEAN NOT NULL DEFAULT FALSE,
        image TEXT,
        username TEXT UNIQUE,
        display_username TEXT,
        two_factor_enabled BOOLEAN NOT NULL DEFAULT FALSE,
        role TEXT,
        banned BOOLEAN NOT NULL DEFAULT FALSE,
        ban_reason TEXT,
        ban_expires TIMESTAMPTZ,
        last_sign_in_at TIMESTAMPTZ,
        metadata JSONB NOT NULL DEFAULT '{}',
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW ()
    );

CREATE TABLE
    IF NOT EXISTS athena.sessions (
        id TEXT PRIMARY KEY,
        expires_at TIMESTAMPTZ NOT NULL,
        token TEXT NOT NULL UNIQUE,
        ip_address TEXT,
        user_agent TEXT,
        user_id TEXT NOT NULL REFERENCES athena.users (id) ON DELETE CASCADE,
        impersonated_by TEXT,
        active_organization_id TEXT,
        active BOOLEAN NOT NULL DEFAULT TRUE,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW ()
    );

CREATE TABLE
    IF NOT EXISTS athena.accounts (
        id TEXT PRIMARY KEY,
        account_id TEXT NOT NULL,
        provider_id TEXT NOT NULL,
        user_id TEXT NOT NULL REFERENCES athena.users (id) ON DELETE CASCADE,
        access_token TEXT,
        refresh_token TEXT,
        id_token TEXT,
        access_token_expires_at TIMESTAMPTZ,
        refresh_token_expires_at TIMESTAMPTZ,
        scope TEXT,
        password TEXT,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        UNIQUE (provider_id, account_id)
    );

CREATE TABLE
    IF NOT EXISTS athena.verifications (
        id TEXT PRIMARY KEY,
        identifier TEXT NOT NULL,
        value TEXT NOT NULL,
        expires_at TIMESTAMPTZ NOT NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW ()
    );

-- Indexes
CREATE INDEX IF NOT EXISTS idx_users_email ON athena.users (email);

CREATE INDEX IF NOT EXISTS idx_users_username ON athena.users (username);
CREATE INDEX IF NOT EXISTS idx_users_last_sign_in_at ON athena.users (last_sign_in_at);

CREATE INDEX IF NOT EXISTS idx_sessions_token ON athena.sessions (token);

CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON athena.sessions (user_id);

CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON athena.sessions (expires_at);

CREATE INDEX IF NOT EXISTS idx_accounts_user_id ON athena.accounts (user_id);

CREATE INDEX IF NOT EXISTS idx_accounts_provider_account ON athena.accounts (provider_id, account_id);

-- Athena Auth: Organization tables (organization, member, invitation)
CREATE TABLE
    IF NOT EXISTS athena.organization (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        slug TEXT NOT NULL UNIQUE,
        logo TEXT,
        metadata JSONB NOT NULL DEFAULT '{}',
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW ()
    );

CREATE TABLE
    IF NOT EXISTS athena.member (
        id TEXT PRIMARY KEY,
        organization_id TEXT NOT NULL REFERENCES athena.organization (id) ON DELETE CASCADE,
        user_id TEXT NOT NULL REFERENCES athena.users (id) ON DELETE CASCADE,
        role TEXT NOT NULL DEFAULT 'member',
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        UNIQUE (organization_id, user_id)
    );

CREATE TABLE
    IF NOT EXISTS athena.invitation (
        id TEXT PRIMARY KEY,
        organization_id TEXT NOT NULL REFERENCES athena.organization (id) ON DELETE CASCADE,
        email TEXT NOT NULL,
        role TEXT NOT NULL DEFAULT 'member',
        status TEXT NOT NULL DEFAULT 'pending',
        inviter_id TEXT NOT NULL REFERENCES athena.users (id) ON DELETE CASCADE,
        expires_at TIMESTAMPTZ NOT NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW ()
    );

-- Indexes
CREATE INDEX IF NOT EXISTS idx_organization_slug ON athena.organization (slug);

CREATE INDEX IF NOT EXISTS idx_member_organization_id ON athena.member (organization_id);

CREATE INDEX IF NOT EXISTS idx_member_user_id ON athena.member (user_id);

CREATE INDEX IF NOT EXISTS idx_invitation_organization_id ON athena.invitation (organization_id);

CREATE INDEX IF NOT EXISTS idx_invitation_email ON athena.invitation (email);

CREATE INDEX IF NOT EXISTS idx_invitation_status ON athena.invitation (status);

-- Athena Auth: Two-factor authentication table
CREATE TABLE
    IF NOT EXISTS athena.two_factor (
        id TEXT PRIMARY KEY,
        secret TEXT NOT NULL,
        backup_codes TEXT,
        user_id TEXT NOT NULL REFERENCES athena.users (id) ON DELETE CASCADE,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        UNIQUE (user_id)
    );

CREATE INDEX IF NOT EXISTS idx_two_factor_user_id ON athena.two_factor (user_id);

-- Athena Auth: API Keys table
CREATE TABLE
    IF NOT EXISTS athena.api_keys (
        id TEXT PRIMARY KEY,
        name TEXT,
        start TEXT,
        prefix TEXT,
        key TEXT NOT NULL UNIQUE,
        user_id TEXT NOT NULL REFERENCES athena.users (id) ON DELETE CASCADE,
        refill_interval INTEGER,
        refill_amount INTEGER,
        last_refill_at TIMESTAMPTZ,
        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 TIMESTAMPTZ,
        expires_at TIMESTAMPTZ,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        permissions TEXT,
        metadata TEXT
    );

ALTER TABLE athena.api_keys
    ADD COLUMN IF NOT EXISTS request_count BIGINT DEFAULT 0;

ALTER TABLE athena.api_keys
ALTER COLUMN request_count TYPE BIGINT USING request_count::BIGINT;

CREATE INDEX IF NOT EXISTS idx_api_keys_user_id ON athena.api_keys (user_id);

-- Athena Auth: Passkey table for WebAuthn/Passkey authentication
CREATE TABLE
    IF NOT EXISTS athena.passkeys (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        public_key TEXT NOT NULL,
        user_id TEXT NOT NULL REFERENCES athena.users (id) ON DELETE CASCADE,
        credential_id TEXT NOT NULL UNIQUE,
        counter BIGINT NOT NULL DEFAULT 0,
        device_type TEXT NOT NULL DEFAULT 'singleDevice',
        backed_up BOOLEAN NOT NULL DEFAULT FALSE,
        transports TEXT,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW ()
    );

CREATE INDEX IF NOT EXISTS idx_passkeys_user_id ON athena.passkeys (user_id);

CREATE INDEX IF NOT EXISTS idx_passkeys_credential_id ON athena.passkeys (credential_id);

-- Athena Auth: persistent email send failure log
CREATE TABLE
    IF NOT EXISTS athena.email_send_failures (
        id TEXT PRIMARY KEY,
        user_id TEXT REFERENCES athena.users (id) ON DELETE SET NULL,
        recipient_email TEXT NOT NULL,
        flow TEXT NOT NULL,
        provider TEXT,
        error_message TEXT NOT NULL,
        metadata JSONB NOT NULL DEFAULT '{}',
        resolved BOOLEAN NOT NULL DEFAULT FALSE,
        resolution_note TEXT,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW ()
    );

-- Self-heal existing deployments that were created before email failure triage columns existed.
ALTER TABLE athena.email_send_failures
    ADD COLUMN IF NOT EXISTS resolved BOOLEAN NOT NULL DEFAULT FALSE;

ALTER TABLE athena.email_send_failures
    ADD COLUMN IF NOT EXISTS resolution_note TEXT;

ALTER TABLE athena.email_send_failures
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW ();

CREATE INDEX IF NOT EXISTS idx_email_send_failures_created_at ON athena.email_send_failures (created_at DESC);

CREATE INDEX IF NOT EXISTS idx_email_send_failures_recipient_email ON athena.email_send_failures (recipient_email);

CREATE INDEX IF NOT EXISTS idx_email_send_failures_resolved ON athena.email_send_failures (resolved);

CREATE INDEX IF NOT EXISTS idx_email_send_failures_flow ON athena.email_send_failures (flow);

-- Athena Auth: audit log of successfully sent emails
CREATE TABLE
    IF NOT EXISTS athena.emails (
        id TEXT PRIMARY KEY,
        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,
        flow TEXT,
        metadata JSONB NOT NULL DEFAULT '{}',
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW ()
    );

-- Self-heal existing deployments that predate flow/update metadata on sent-email audit rows.
ALTER TABLE athena.emails
    ADD COLUMN IF NOT EXISTS flow TEXT;

ALTER TABLE athena.emails
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW ();

CREATE INDEX IF NOT EXISTS idx_emails_created_at ON athena.emails (created_at DESC);

CREATE INDEX IF NOT EXISTS idx_emails_recipient_email ON athena.emails (recipient_email);

CREATE INDEX IF NOT EXISTS idx_emails_provider ON athena.emails (provider);

CREATE INDEX IF NOT EXISTS idx_emails_flow ON athena.emails (flow);

-- Athena Auth: reusable email template catalog for DB-backed templating
CREATE TABLE
    IF NOT EXISTS athena.email_templates (
        id TEXT PRIMARY KEY,
        template_key TEXT NOT NULL,
        event_type TEXT,
        locale TEXT NOT NULL DEFAULT 'en',
        subject_template TEXT NOT NULL,
        text_template TEXT,
        html_template TEXT,
        variables JSONB NOT NULL DEFAULT '[]',
        variable_bindings JSONB NOT NULL DEFAULT '[]',
        is_active BOOLEAN NOT NULL DEFAULT TRUE,
        metadata JSONB NOT NULL DEFAULT '{}',
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        UNIQUE (template_key, locale)
    );

ALTER TABLE athena.email_templates
    ADD COLUMN IF NOT EXISTS variable_bindings JSONB NOT NULL DEFAULT '[]';

CREATE INDEX IF NOT EXISTS idx_email_templates_template_key ON athena.email_templates (template_key);

CREATE INDEX IF NOT EXISTS idx_email_templates_is_active ON athena.email_templates (is_active);

-- Athena Auth: canonical email event taxonomy + template assignment
CREATE TABLE
    IF NOT EXISTS athena.email_event_types (
        event_type TEXT PRIMARY KEY,
        category TEXT NOT NULL,
        description TEXT NOT NULL,
        default_template_key TEXT,
        required_variables JSONB NOT NULL DEFAULT '[]',
        optional_variables JSONB NOT NULL DEFAULT '[]',
        is_active BOOLEAN NOT NULL DEFAULT TRUE,
        is_system BOOLEAN NOT NULL DEFAULT TRUE,
        metadata JSONB NOT NULL DEFAULT '{}',
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW ()
    );

INSERT INTO athena.email_event_types (
    event_type,
    category,
    description,
    default_template_key,
    required_variables,
    optional_variables,
    is_active,
    is_system,
    metadata
)
VALUES
    (
        'user.email.verify',
        'user_lifecycle',
        'Verify a user email address after signup or manual verification resend.',
        'verification_email',
        '["verification_url"]'::jsonb,
        '["app_name"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"auth"}'::jsonb
    ),
    (
        'user.password.reset',
        'user_security',
        'Send a password reset link.',
        'password_reset_email',
        '["reset_url"]'::jsonb,
        '["app_name"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"auth"}'::jsonb
    ),
    (
        'user.email.change.confirmation',
        'user_security',
        'Confirm a pending user email address change.',
        'change_email_confirmation_email',
        '["verification_url"]'::jsonb,
        '["app_name"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"auth"}'::jsonb
    ),
    (
        'user.account.delete.confirmation',
        'user_security',
        'Confirm account deletion before destructive delete.',
        'account_deletion_confirmation_email',
        '["verification_url"]'::jsonb,
        '["app_name"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"auth"}'::jsonb
    ),
    (
        'user.sign-in.otp',
        'user_security',
        'Deliver one-time passcode for two-factor authentication.',
        'two_factor_otp_email',
        '["otp_code"]'::jsonb,
        '["app_name"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"auth"}'::jsonb
    ),
    (
        'organization.member.invite',
        'organization_lifecycle',
        'Invite a member to join an organization.',
        'organization_invitation_email',
        '["organization_name","role","inviter_identity","invitation_url"]'::jsonb,
        '["app_name"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"organization"}'::jsonb
    ),
    (
        'user.sign-in.email',
        'user_security',
        'Email-based sign-in link or magic-link authentication flow.',
        NULL,
        '["sign_in_url"]'::jsonb,
        '["app_name"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    ),
    (
        'user.sign-up.welcome',
        'user_lifecycle',
        'Welcome message after account creation.',
        NULL,
        '["user_name"]'::jsonb,
        '["app_name","dashboard_url"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    ),
    (
        'user.password.changed',
        'user_security',
        'Security notification after password change completes.',
        NULL,
        '[]'::jsonb,
        '["app_name","support_url"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    ),
    (
        'user.security.alert',
        'user_security',
        'General security alert notification (suspicious sign-in, new device, policy event).',
        NULL,
        '["alert_title","alert_details"]'::jsonb,
        '["app_name","support_url"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    ),
    (
        'organization.create',
        'organization_lifecycle',
        'Organization creation confirmation and onboarding.',
        NULL,
        '["organization_name"]'::jsonb,
        '["app_name","organization_url"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    ),
    (
        'organization.member.added',
        'organization_lifecycle',
        'Notify when a member is added without invite flow.',
        NULL,
        '["organization_name","member_identity"]'::jsonb,
        '["app_name","actor_identity"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    ),
    (
        'organization.member.removed',
        'organization_lifecycle',
        'Notify when a member is removed from an organization.',
        NULL,
        '["organization_name","member_identity"]'::jsonb,
        '["app_name","actor_identity"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    ),
    (
        'organization.member.role.updated',
        'organization_lifecycle',
        'Notify when organization member role is changed.',
        NULL,
        '["organization_name","member_identity","new_role"]'::jsonb,
        '["app_name","previous_role","actor_identity"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    ),
    (
        'organization.member.invite.reminder',
        'organization_lifecycle',
        'Reminder message for pending organization invitations.',
        NULL,
        '["organization_name","invitation_url"]'::jsonb,
        '["app_name","role","inviter_identity"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    ),
    (
        'organization.member.invite.revoked',
        'organization_lifecycle',
        'Notification that an invitation has been revoked.',
        NULL,
        '["organization_name","invited_email"]'::jsonb,
        '["app_name","inviter_identity"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    )
ON CONFLICT (event_type) DO UPDATE
SET
    category = EXCLUDED.category,
    description = EXCLUDED.description,
    default_template_key = EXCLUDED.default_template_key,
    required_variables = EXCLUDED.required_variables,
    optional_variables = EXCLUDED.optional_variables,
    is_active = EXCLUDED.is_active,
    is_system = EXCLUDED.is_system,
    metadata = EXCLUDED.metadata,
    updated_at = NOW ();

ALTER TABLE athena.email_templates
    ADD COLUMN IF NOT EXISTS event_type TEXT;

UPDATE athena.email_templates AS t
SET event_type = m.event_type
FROM (
    VALUES
        ('verification_email', 'user.email.verify'),
        ('password_reset_email', 'user.password.reset'),
        ('change_email_confirmation_email', 'user.email.change.confirmation'),
        ('account_deletion_confirmation_email', 'user.account.delete.confirmation'),
        ('two_factor_otp_email', 'user.sign-in.otp'),
        ('organization_invitation_email', 'organization.member.invite')
) AS m(template_key, event_type)
WHERE t.event_type IS NULL
  AND t.template_key = m.template_key;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'fk_email_templates_event_type'
          AND conrelid = 'athena.email_templates'::regclass
    ) THEN
        ALTER TABLE athena.email_templates
            ADD CONSTRAINT fk_email_templates_event_type
            FOREIGN KEY (event_type)
            REFERENCES athena.email_event_types (event_type)
            ON UPDATE CASCADE
            ON DELETE SET NULL;
    END IF;
END $$;

-- Athena Auth: admin audit log and Athena client registry
CREATE TABLE
    IF NOT EXISTS athena.athena_clients (
        id TEXT PRIMARY KEY,
        client_name TEXT NOT NULL UNIQUE,
        description TEXT,
        is_active BOOLEAN NOT NULL DEFAULT TRUE,
        pg_uri_env_var TEXT,
        metadata JSONB NOT NULL DEFAULT '{}',
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW ()
    );

CREATE INDEX IF NOT EXISTS idx_athena_clients_client_name ON athena.athena_clients (client_name);

CREATE INDEX IF NOT EXISTS idx_athena_clients_is_active ON athena.athena_clients (is_active);

CREATE TABLE
    IF NOT EXISTS athena.oauth_clients (
        id TEXT PRIMARY KEY,
        client_name TEXT NOT NULL UNIQUE,
        provider TEXT NOT NULL,
        description TEXT,
        response_type TEXT NOT NULL DEFAULT 'code',
        grant_type TEXT NOT NULL DEFAULT 'authorization_code',
        token_authentication_method TEXT NOT NULL DEFAULT 'none',
        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 '{}',
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW ()
    );

CREATE INDEX IF NOT EXISTS idx_oauth_clients_client_name ON athena.oauth_clients (client_name);

CREATE INDEX IF NOT EXISTS idx_oauth_clients_provider_active ON athena.oauth_clients (provider, is_active);

CREATE TABLE
    IF NOT EXISTS athena.admin_audit_logs (
        id TEXT PRIMARY KEY,
        actor_user_id TEXT REFERENCES athena.users (id) ON DELETE SET NULL,
        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 '{}',
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW ()
    );

CREATE INDEX IF NOT EXISTS idx_admin_audit_logs_created_at ON athena.admin_audit_logs (created_at DESC);

CREATE INDEX IF NOT EXISTS idx_admin_audit_logs_action ON athena.admin_audit_logs (action);

CREATE INDEX IF NOT EXISTS idx_admin_audit_logs_actor_user_id ON athena.admin_audit_logs (actor_user_id);

CREATE INDEX IF NOT EXISTS idx_admin_audit_logs_target ON athena.admin_audit_logs (target_type, target_id);

CREATE INDEX IF NOT EXISTS idx_admin_audit_logs_success_action_created ON athena.admin_audit_logs (success, action, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_admin_audit_logs_metadata_gin ON athena.admin_audit_logs USING GIN (metadata);

CREATE INDEX IF NOT EXISTS idx_email_send_failures_resolved_created_at ON athena.email_send_failures (resolved, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_emails_flow_created_at ON athena.emails (flow, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_email_templates_key_locale_active ON athena.email_templates (template_key, locale, is_active);

CREATE INDEX IF NOT EXISTS idx_email_event_types_category ON athena.email_event_types (category);

CREATE INDEX IF NOT EXISTS idx_email_event_types_is_active ON athena.email_event_types (is_active);

CREATE INDEX IF NOT EXISTS idx_email_templates_event_type ON athena.email_templates (event_type);

CREATE INDEX IF NOT EXISTS idx_email_templates_event_type_locale_active ON athena.email_templates (event_type, locale, is_active);

-- Athena Auth: optional session intelligence tables
CREATE TABLE
    IF NOT EXISTS athena.ip_profiles (
        id TEXT PRIMARY KEY,
        ip_address TEXT NOT NULL UNIQUE,
        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 '{}',
        lookup_expires_at TIMESTAMPTZ,
        first_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW ()
    );

CREATE INDEX IF NOT EXISTS idx_ip_profiles_lookup_expires_at ON athena.ip_profiles (lookup_expires_at);
CREATE INDEX IF NOT EXISTS idx_ip_profiles_country_code ON athena.ip_profiles (country_code);
CREATE INDEX IF NOT EXISTS idx_ip_profiles_last_seen_at ON athena.ip_profiles (last_seen_at DESC);

CREATE TABLE
    IF NOT EXISTS athena.session_ip_profiles (
        id TEXT PRIMARY KEY,
        session_id TEXT NOT NULL UNIQUE REFERENCES athena.sessions (id) ON DELETE CASCADE,
        session_token TEXT NOT NULL,
        user_id TEXT NOT NULL REFERENCES athena.users (id) ON DELETE CASCADE,
        ip_address TEXT NOT NULL,
        ip_profile_id TEXT REFERENCES athena.ip_profiles (id) ON DELETE SET NULL,
        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 TIMESTAMPTZ NOT NULL DEFAULT NOW ()
    );

CREATE UNIQUE INDEX IF NOT EXISTS idx_session_ip_profiles_session_token ON athena.session_ip_profiles (session_token);
CREATE INDEX IF NOT EXISTS idx_session_ip_profiles_user_created ON athena.session_ip_profiles (user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_session_ip_profiles_country_code ON athena.session_ip_profiles (country_code);
CREATE INDEX IF NOT EXISTS idx_session_ip_profiles_ip_address ON athena.session_ip_profiles (ip_address);
CREATE INDEX IF NOT EXISTS idx_session_ip_profiles_country_changed ON athena.session_ip_profiles (country_changed);
