-- ============================================
-- SYSTEM LOGGING TABLE
-- ============================================
-- Centraliseret logging til alle fejl, warnings og info-beskeder
-- Gør det nemt at fejlsøge og analysere problemer

CREATE TABLE IF NOT EXISTS system_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Tidsstempel
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Log niveau
    level ENUM('ERROR', 'WARNING', 'INFO', 'DEBUG') NOT NULL,
    
    -- Kontekst information
    context VARCHAR(255) NOT NULL COMMENT 'Beskriver hvor i systemet log-indgangen kommer fra',
    category VARCHAR(100) DEFAULT NULL COMMENT 'Kategorisering som API, DATABASE, AUTH, etc.',
    
    -- Fejlbeskrivelse
    message TEXT NOT NULL,
    error_code VARCHAR(50) DEFAULT NULL COMMENT 'Unik fejlkode for brugervenlig reference',
    
    -- Tekniske detaljer
    stack_trace TEXT DEFAULT NULL,
    sql_query TEXT DEFAULT NULL,
    sql_state VARCHAR(10) DEFAULT NULL,
    
    -- Request information
    request_method VARCHAR(10) DEFAULT NULL,
    request_url TEXT DEFAULT NULL,
    request_ip VARCHAR(45) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    
    -- Bruger information
    user_id INT DEFAULT NULL,
    user_email VARCHAR(255) DEFAULT NULL,
    
    -- Ekstra metadata (JSON format)
    metadata JSON DEFAULT NULL,
    
    -- Status
    resolved BOOLEAN DEFAULT FALSE,
    resolved_at TIMESTAMP NULL DEFAULT NULL,
    resolved_by VARCHAR(255) DEFAULT NULL,
    resolution_notes TEXT DEFAULT NULL,
    
    INDEX idx_level (level),
    INDEX idx_created_at (created_at),
    INDEX idx_context (context),
    INDEX idx_category (category),
    INDEX idx_error_code (error_code),
    INDEX idx_user_id (user_id),
    INDEX idx_resolved (resolved)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- REQUEST LOGS TABLE (Performance tracking)
-- ============================================
-- Tracker alle API requests for performance analyse

CREATE TABLE IF NOT EXISTS request_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Tidsstempel
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Request information
    method VARCHAR(10) NOT NULL,
    url TEXT NOT NULL,
    status_code INT NOT NULL,
    
    -- Timing
    response_time_ms INT NOT NULL COMMENT 'Response tid i millisekunder',
    
    -- Bruger information
    user_id INT DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    
    -- Ekstra metadata
    metadata JSON DEFAULT NULL,
    
    INDEX idx_created_at (created_at),
    INDEX idx_method (method),
    INDEX idx_status_code (status_code),
    INDEX idx_user_id (user_id),
    INDEX idx_response_time (response_time_ms)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- CLEANUP JOB (Hold logs i 90 dage)
-- ============================================
-- Denne skal køres regelmæssigt for at holde databasen ren

-- EVENT scheduler skal være aktiveret:
-- SET GLOBAL event_scheduler = ON;

DELIMITER $$

CREATE EVENT IF NOT EXISTS cleanup_old_logs
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO
BEGIN
    -- Slet logs ældre end 90 dage (behold ERROR logs i 180 dage)
    DELETE FROM system_logs 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
    AND level != 'ERROR';
    
    DELETE FROM system_logs 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 180 DAY)
    AND level = 'ERROR';
    
    -- Slet request logs ældre end 30 dage
    DELETE FROM request_logs 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
END$$

DELIMITER ;