-- =============================================================================
-- fact_audit_logs.sql — audit trail for the passcode-gated System Logs feature
-- (Settings > System Logs, unlocked with "5421"). Records one row per
-- successful mutating event call (POST/PUT .../event with a 2xx response),
-- written generically by ActivityLoggingFilter for every module that uses the
-- event/webhook convention — see DiagnosticLogService for the companion
-- file-based error/warning/success log (logs/system-activity.json).
-- Safe to re-run: CREATE TABLE IF NOT EXISTS.
-- =============================================================================

CREATE TABLE IF NOT EXISTS fact_audit_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    module VARCHAR(50) NOT NULL,
    action VARCHAR(80) NOT NULL,
    user_id INT NULL,
    user_email VARCHAR(150) NULL,
    summary VARCHAR(300) NOT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    INDEX idx_audit_tenant_created (tenant_id, created_at),
    INDEX idx_audit_module (tenant_id, module),
    INDEX idx_audit_user (tenant_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;