-- =============================================================================
-- GeoLynx Login Sharing Protection — DDL
-- Run after geolynx_ddl.sql and admin_area.sql
-- =============================================================================

-- One active session per user. Replaced atomically on new login.
CREATE TABLE IF NOT EXISTS users.active_sessions (
    session_record_id  bigserial PRIMARY KEY,
    user_id            bigint NOT NULL REFERENCES users.users(id) ON DELETE CASCADE,
    session_id         varchar(128) NOT NULL,
    login_log_id       bigint REFERENCES users.users_login_log(id) ON DELETE SET NULL,
    created_at         timestamp without time zone DEFAULT now(),
    last_seen_at       timestamp without time zone DEFAULT now(),
    UNIQUE (user_id)
);

CREATE INDEX IF NOT EXISTS idx_active_sessions_session_id
    ON users.active_sessions(session_id);

-- Logout audit on login log
ALTER TABLE users.users_login_log
    ADD COLUMN IF NOT EXISTS logout_at     timestamp without time zone,
    ADD COLUMN IF NOT EXISTS logout_reason varchar(30);
-- Valid values: 'user_logout' | 'session_timeout' | 'displaced' | 'admin_kill'

-- Brute-force protection on users
ALTER TABLE users.users
    ADD COLUMN IF NOT EXISTS failed_login_attempts integer NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS locked_until          timestamp without time zone;