-- Audit Logging Migration
-- Creates comprehensive audit trail for compliance and security

-- Create audit_logs table
CREATE TABLE IF NOT EXISTS `audit_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED,
    `action` VARCHAR(50) NOT NULL COMMENT 'CREATE, UPDATE, DELETE, LOGIN, PAYMENT, etc.',
    `entity` VARCHAR(50) NOT NULL COMMENT 'users, invoices, payments, settings, etc.',
    `entity_id` INT UNSIGNED,
    `description` TEXT COMMENT 'Human-readable description of the action',
    `changes` JSON COMMENT 'JSON object tracking field changes: {field: {old: value, new: value}}',
    `ip_address` VARCHAR(45) COMMENT 'IPv4 or IPv6',
    `user_agent` VARCHAR(255),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Indexes for fast querying
    INDEX idx_user_id (user_id),
    INDEX idx_action (action),
    INDEX idx_entity (entity),
    INDEX idx_entity_id (entity_id),
    INDEX idx_created_at (created_at),
    INDEX idx_composite (entity, action, created_at),
    
    -- Foreign key
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create index for finding logs by user and date range (common queries)
CREATE INDEX idx_user_date ON audit_logs(user_id, created_at DESC);

-- Add audit-related columns to settings table if not exists
ALTER TABLE `settings` ADD COLUMN IF NOT EXISTS `enable_audit_logging` TINYINT(1) DEFAULT 1 COMMENT 'Enable/disable audit logging';
ALTER TABLE `settings` ADD COLUMN IF NOT EXISTS `audit_log_retention_days` INT DEFAULT 90 COMMENT 'Number of days to retain audit logs';

-- Create audit_log_types reference table (optional but helpful)
CREATE TABLE IF NOT EXISTS `audit_log_types` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `action_name` VARCHAR(50) UNIQUE NOT NULL,
    `description` VARCHAR(255),
    `severity` ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert common audit log types
INSERT IGNORE INTO `audit_log_types` (action_name, description, severity) VALUES
('CREATE', 'Record creation', 'low'),
('UPDATE', 'Record update', 'low'),
('DELETE', 'Record deletion', 'high'),
('LOGIN', 'User login', 'low'),
('LOGOUT', 'User logout', 'low'),
('LOGIN_FAILED', 'Failed login attempt', 'medium'),
('PAYMENT', 'Payment transaction', 'high'),
('PERMISSION_CHANGE', 'User permission modified', 'high'),
('SETTINGS_CHANGE', 'System settings modified', 'high'),
('EXPORT', 'Data export', 'medium'),
('IMPORT', 'Data import', 'high'),
('BULK_UPDATE', 'Bulk record update', 'medium'),
('SEND_NOTIFICATION', 'Notification sent', 'low'),
('DOWNLOAD', 'File download', 'low'),
('UPLOAD', 'File upload', 'medium');
