-- Registration Activity Log Table
-- Comprehensive logging for all registration-related activities

CREATE TABLE IF NOT EXISTS registration_activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    activity_type VARCHAR(50) NOT NULL,
    registration_id INT NULL,
    delegate_id INT NULL,
    payment_transaction_id INT NULL,
    email_log_id INT NULL,
    user_id INT NULL,
    user_name VARCHAR(100) NULL,
    user_role VARCHAR(50) NULL,
    action_description TEXT NOT NULL,
    old_values JSON NULL,
    new_values JSON NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    session_id VARCHAR(255) NULL,
    affected_count INT DEFAULT 1,
    severity ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    tags JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_activity_type (activity_type),
    INDEX idx_registration_id (registration_id),
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at),
    INDEX idx_severity (severity),
    FOREIGN KEY (registration_id) REFERENCES registrations(id) ON DELETE SET NULL,
    FOREIGN KEY (delegate_id) REFERENCES delegates(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create view for recent registration activities with enhanced details
CREATE OR REPLACE VIEW recent_registration_activities AS
SELECT 
    ral.*,
    r.registration_number,
    r.contact_full_name,
    r.contact_email,
    r.payment_status,
    r.total_amount,
    CASE 
        WHEN ral.activity_type LIKE '%payment%' THEN '💳'
        WHEN ral.activity_type LIKE '%contact%' THEN '👤'
        WHEN ral.activity_type LIKE '%email%' THEN '📧'
        WHEN ral.activity_type LIKE '%admin_notes%' THEN '📝'
        WHEN ral.activity_type LIKE '%stock%' THEN '📦'
        WHEN ral.activity_type LIKE '%view%' THEN '👁️'
        WHEN ral.activity_type LIKE '%export%' THEN '📊'
        ELSE '🔄'
    END as activity_icon,
    CASE ral.severity
        WHEN 'low' THEN '#10b981'
        WHEN 'medium' THEN '#f59e0b'
        WHEN 'high' THEN '#ef4444'
        WHEN 'critical' THEN '#dc2626'
    END as severity_color
FROM registration_activity_log ral
LEFT JOIN registrations r ON ral.registration_id = r.id
ORDER BY ral.created_at DESC;

-- Add indexes for better performance
CREATE INDEX idx_registration_activity_composite ON registration_activity_log (registration_id, activity_type, created_at);
CREATE INDEX idx_registration_activity_user_date ON registration_activity_log (user_id, created_at);
