CREATE TABLE auth_login_attempts (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    identifier text NOT NULL,
    remote_ip text NOT NULL,
    user_agent text,
    success boolean NOT NULL,
    failure_reason text,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX auth_login_attempts_identifier_created_at_idx
    ON auth_login_attempts (identifier, created_at DESC);

CREATE INDEX auth_login_attempts_remote_ip_created_at_idx
    ON auth_login_attempts (remote_ip, created_at DESC);

CREATE INDEX auth_login_attempts_success_created_at_idx
    ON auth_login_attempts (success, created_at DESC);

CREATE TABLE auth_lockouts (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    identifier text NOT NULL,
    remote_ip text NOT NULL,
    reason text NOT NULL,
    failed_count integer NOT NULL CHECK (failed_count > 0),
    locked_until timestamptz NOT NULL,
    released_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX auth_lockouts_identifier_active_idx
    ON auth_lockouts (identifier, locked_until DESC)
    WHERE released_at IS NULL;

CREATE INDEX auth_lockouts_remote_ip_active_idx
    ON auth_lockouts (remote_ip, locked_until DESC)
    WHERE released_at IS NULL;

CREATE TABLE security_events (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    event_type text NOT NULL CHECK (event_type IN (
        'login_failed_velocity',
        'login_lockout_created',
        'login_blocked',
        'login_rate_limited',
        'suspicious_login'
    )),
    severity text NOT NULL CHECK (severity IN ('low', 'medium', 'high', 'critical')),
    status text NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'reviewing', 'resolved', 'ignored')),
    identifier text NOT NULL,
    remote_ip text NOT NULL,
    user_agent text,
    details jsonb NOT NULL DEFAULT '{}'::jsonb,
    resolved_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    resolution_note text,
    resolved_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX security_events_status_created_at_idx
    ON security_events (status, created_at DESC);

CREATE INDEX security_events_type_created_at_idx
    ON security_events (event_type, created_at DESC);

CREATE INDEX security_events_user_created_at_idx
    ON security_events (user_id, created_at DESC);

CREATE TRIGGER auth_lockouts_set_updated_at
BEFORE UPDATE ON auth_lockouts
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER security_events_set_updated_at
BEFORE UPDATE ON security_events
FOR EACH ROW EXECUTE FUNCTION set_updated_at();