-- 003_sessions.sql
-- 用户会话表 + 心跳管理 + 离线检测 + pg_cron 调度

-- ============================================================
-- 1. 用户会话表（跟踪当前状态和心跳）
-- ============================================================
CREATE TABLE IF NOT EXISTS user_sessions (
    user_id          VARCHAR(128) PRIMARY KEY,
    current_state    VARCHAR(64)  NOT NULL DEFAULT 'Offline',
    last_heartbeat   BIGINT       NOT NULL,
    last_state_change BIGINT      NOT NULL,
    updated_at       TIMESTAMPTZ  NOT NULL DEFAULT now()
);

-- ============================================================
-- 2. 离线检测函数：心跳超时 60s 的用户自动补一条 Offline 检查点
--    pg_cron 每 1 分钟执行一次
-- ============================================================
CREATE OR REPLACE FUNCTION detect_offline_users(
    timeout_secs BIGINT DEFAULT 60
) RETURNS SETOF BIGINT AS $$
DECLARE
    r RECORD;
    now_ts BIGINT;
BEGIN
    now_ts := EXTRACT(EPOCH FROM now())::BIGINT;

    FOR r IN
        SELECT user_id, last_heartbeat
        FROM user_sessions
        WHERE current_state != 'Offline'
          AND (now_ts - last_heartbeat) > timeout_secs
    LOOP
        -- 插入离线检查点（时间戳 = 最后心跳 + 超时时间）
        INSERT INTO checkpoints (user_id, state, timestamp)
        VALUES (r.user_id, 'Offline', r.last_heartbeat + timeout_secs);

        -- 更新会话状态
        UPDATE user_sessions
        SET current_state = 'Offline',
            last_state_change = r.last_heartbeat + timeout_secs,
            updated_at = now()
        WHERE user_id = r.user_id;

        RETURN NEXT 1;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- ============================================================
-- 3. pg_cron 调度（需要超级用户手动执行一次）
-- ============================================================
-- 
-- -- 安装扩展（仅需一次）
-- CREATE EXTENSION IF NOT EXISTS pg_cron;
--
-- -- 每 1 分钟检测离线用户
-- SELECT cron.schedule(
--     'detect-offline',
--     '* * * * *',
--     $$ SELECT detect_offline_users(60); $$
-- );
--
-- -- 查看状态
-- SELECT * FROM cron.job;