-- 20260307_004_web_session_heartbeat.sql
ALTER TABLE customer_web_sessions
    ADD COLUMN IF NOT EXISTS last_activity DATETIME DEFAULT CURRENT_TIMESTAMP AFTER login_at,
    ADD COLUMN IF NOT EXISTS last_heartbeat_at DATETIME DEFAULT NULL AFTER last_active_at,
    ADD COLUMN IF NOT EXISTS is_active TINYINT(1) NOT NULL DEFAULT 1 AFTER last_heartbeat_at,
    ADD COLUMN IF NOT EXISTS closed_at DATETIME DEFAULT NULL AFTER logout_reason,
    ADD COLUMN IF NOT EXISTS closed_reason VARCHAR(50) DEFAULT NULL AFTER closed_at;

UPDATE customer_web_sessions
SET last_activity = COALESCE(last_activity, last_active_at, login_at, created_at),
    is_active = CASE
        WHEN logout_at IS NULL THEN 1
        ELSE 0
    END,
    closed_at = COALESCE(closed_at, logout_at),
    closed_reason = COALESCE(closed_reason, logout_reason);

CREATE INDEX IF NOT EXISTS idx_customer_active_v2
    ON customer_web_sessions (customer_id, is_active, logout_at, last_activity);

CREATE INDEX IF NOT EXISTS idx_session_liveness
    ON customer_web_sessions (is_active, last_activity);

CREATE TABLE IF NOT EXISTS customer_web_session_tabs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    session_uuid CHAR(36) NOT NULL,
    tab_uuid CHAR(36) NOT NULL,
    ip_address VARCHAR(64) DEFAULT NULL,
    user_agent VARCHAR(255) DEFAULT NULL,
    last_seen_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    disconnected_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_session_tab (session_uuid, tab_uuid),
    KEY idx_session_tab_active (session_uuid, disconnected_at, last_seen_at),
    KEY idx_last_seen (last_seen_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


