-- Active session management table and permission for Standalone User Dashboard

CREATE TABLE IF NOT EXISTS user_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    session_token_hash CHAR(64) NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    login_at DATETIME NULL,
    last_activity_at DATETIME NULL,
    expires_at DATETIME NULL,
    revoked_at DATETIME NULL,
    revoked_by_user_id BIGINT UNSIGNED NULL,
    revoked_reason VARCHAR(80) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_sessions_token (session_token_hash),
    KEY idx_user_sessions_user_active (user_id, revoked_at, expires_at),
    KEY idx_user_sessions_last_activity (last_activity_at),
    CONSTRAINT fk_user_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_sessions_revoked_by FOREIGN KEY (revoked_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO programs (program_code, program_name, is_active, created_at)
SELECT 'ACCESS_DASHBOARD', 'Access Dashboard', 1, NOW()
WHERE NOT EXISTS (SELECT 1 FROM programs WHERE program_code = 'ACCESS_DASHBOARD');

INSERT INTO permissions (program_id, permission_key, permission_name, permission_group, description, is_active, created_at)
SELECT p.id, 'security_sessions_manage', 'Manage Active Sessions', 'Security', 'View and revoke active login sessions.', 1, NOW()
FROM programs p
WHERE p.program_code = 'ACCESS_DASHBOARD'
  AND NOT EXISTS (
      SELECT 1 FROM permissions x
      WHERE x.program_id = p.id AND x.permission_key = 'security_sessions_manage'
  );

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, pm.id
FROM roles r
JOIN programs pr ON pr.program_code = 'ACCESS_DASHBOARD'
JOIN permissions pm ON pm.program_id = pr.id AND pm.permission_key = 'security_sessions_manage'
WHERE r.role_key = 'super_admin';