ALTER TABLE security_events
    DROP CONSTRAINT IF EXISTS security_events_event_type_check;

ALTER TABLE security_events
    ADD CONSTRAINT security_events_event_type_check CHECK (event_type IN (
        'login_failed_velocity',
        'login_lockout_created',
        'login_blocked',
        'login_rate_limited',
        'suspicious_login',
        'session_anomaly',
        'session_remote_revoked'
    ));

CREATE TABLE user_mfa_recovery_codes (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    code_hash text NOT NULL UNIQUE,
    used_at timestamptz,
    used_remote_ip text,
    used_user_agent text,
    replaced_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    CHECK (used_at IS NULL OR used_remote_ip IS NOT NULL)
);

CREATE INDEX user_mfa_recovery_codes_user_active_idx
    ON user_mfa_recovery_codes (user_id, created_at DESC)
    WHERE used_at IS NULL AND replaced_at IS NULL;

CREATE TABLE mfa_reset_requests (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    target_user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    requester_admin_user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    reviewer_admin_user_id uuid REFERENCES users(id) ON DELETE RESTRICT,
    status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'canceled')),
    reason text NOT NULL,
    decision_note text,
    decided_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (reviewer_admin_user_id IS NULL OR reviewer_admin_user_id <> requester_admin_user_id)
);

CREATE INDEX mfa_reset_requests_status_created_at_idx
    ON mfa_reset_requests (status, created_at DESC);

CREATE INDEX mfa_reset_requests_target_created_at_idx
    ON mfa_reset_requests (target_user_id, created_at DESC);

CREATE TRIGGER mfa_reset_requests_set_updated_at
BEFORE UPDATE ON mfa_reset_requests
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE admin_scope_assignments (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    scope text NOT NULL CHECK (scope IN (
        'self:read',
        'money:read',
        'money:write',
        'cards:write',
        'crypto:write',
        'admin:read',
        'admin:write',
        'compliance:read',
        'compliance:write',
        'ledger:read',
        'audit:read'
    )),
    status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'revoked')),
    source text NOT NULL DEFAULT 'manual' CHECK (source IN ('manual', 'role_change', 'jit', 'migration')),
    granted_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    revoked_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    reason text NOT NULL DEFAULT '',
    expires_at timestamptz,
    revoked_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (status = 'active' OR revoked_at IS NOT NULL)
);

CREATE UNIQUE INDEX admin_scope_assignments_user_scope_active_idx
    ON admin_scope_assignments (user_id, scope)
    WHERE status = 'active';

CREATE INDEX admin_scope_assignments_user_status_idx
    ON admin_scope_assignments (user_id, status, expires_at);

CREATE TRIGGER admin_scope_assignments_set_updated_at
BEFORE UPDATE ON admin_scope_assignments
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

INSERT INTO admin_scope_assignments (user_id, scope, status, source, granted_by_admin_user_id, reason)
SELECT u.id, scope_value.scope, 'active', 'migration', u.id, 'seed existing admin role scopes'
FROM users u
CROSS JOIN unnest(ARRAY[
    'self:read',
    'money:read',
    'money:write',
    'cards:write',
    'crypto:write',
    'admin:read',
    'admin:write',
    'compliance:read',
    'compliance:write',
    'ledger:read',
    'audit:read'
]::text[]) AS scope_value(scope)
WHERE u.role = 'admin'
ON CONFLICT DO NOTHING;

CREATE TABLE admin_role_change_requests (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    target_user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    requester_admin_user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    reviewer_admin_user_id uuid REFERENCES users(id) ON DELETE RESTRICT,
    requested_role text NOT NULL CHECK (requested_role IN ('customer', 'admin')),
    requested_scopes text[] NOT NULL DEFAULT ARRAY[]::text[],
    status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'canceled')),
    reason text NOT NULL,
    decision_note text,
    decided_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (reviewer_admin_user_id IS NULL OR reviewer_admin_user_id <> requester_admin_user_id)
);

CREATE INDEX admin_role_change_requests_status_created_at_idx
    ON admin_role_change_requests (status, created_at DESC);

CREATE INDEX admin_role_change_requests_target_created_at_idx
    ON admin_role_change_requests (target_user_id, created_at DESC);

CREATE TRIGGER admin_role_change_requests_set_updated_at
BEFORE UPDATE ON admin_role_change_requests
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE admin_jit_elevation_requests (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    admin_user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    requester_admin_user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    reviewer_admin_user_id uuid REFERENCES users(id) ON DELETE RESTRICT,
    scopes text[] NOT NULL,
    status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'canceled', 'revoked', 'expired')),
    reason text NOT NULL,
    decision_note text,
    starts_at timestamptz,
    expires_at timestamptz,
    revoked_at timestamptz,
    decided_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (reviewer_admin_user_id IS NULL OR reviewer_admin_user_id <> requester_admin_user_id),
    CHECK (expires_at IS NULL OR starts_at IS NULL OR expires_at > starts_at)
);

CREATE INDEX admin_jit_elevation_requests_admin_status_idx
    ON admin_jit_elevation_requests (admin_user_id, status, expires_at);

CREATE INDEX admin_jit_elevation_requests_status_created_at_idx
    ON admin_jit_elevation_requests (status, created_at DESC);

CREATE TRIGGER admin_jit_elevation_requests_set_updated_at
BEFORE UPDATE ON admin_jit_elevation_requests
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE webauthn_challenges (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    challenge_hash text NOT NULL UNIQUE,
    challenge_type text NOT NULL CHECK (challenge_type IN ('registration', 'authentication')),
    expires_at timestamptz NOT NULL,
    consumed_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX webauthn_challenges_user_active_idx
    ON webauthn_challenges (user_id, expires_at)
    WHERE consumed_at IS NULL;

CREATE TABLE webauthn_credentials (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    credential_id text NOT NULL UNIQUE,
    public_key text NOT NULL,
    nickname text NOT NULL DEFAULT '',
    transports text[] NOT NULL DEFAULT ARRAY[]::text[],
    aaguid text NOT NULL DEFAULT '',
    sign_count bigint NOT NULL DEFAULT 0 CHECK (sign_count >= 0),
    backup_eligible boolean NOT NULL DEFAULT false,
    backup_state boolean NOT NULL DEFAULT false,
    status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'revoked')),
    last_used_at timestamptz,
    revoked_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (status = 'active' OR revoked_at IS NOT NULL)
);

CREATE INDEX webauthn_credentials_user_status_idx
    ON webauthn_credentials (user_id, status, created_at DESC);

CREATE TRIGGER webauthn_credentials_set_updated_at
BEFORE UPDATE ON webauthn_credentials
FOR EACH ROW EXECUTE FUNCTION set_updated_at();