-- Migration: Enhance Audit Logging System
-- Description: Add change tracking fields to existing audit_logs table
-- Created: 2025-11-26

-- ============================================================================
-- ENHANCE AUDIT_LOGS TABLE
-- ============================================================================

-- Add new columns for enhanced tracking
ALTER TABLE `audit_logs`
ADD COLUMN `user_name` VARCHAR(255) NULL COMMENT 'Cached user name for historical records' AFTER `user_id`,
ADD COLUMN `description` VARCHAR(500) NULL COMMENT 'Human-readable description' AFTER `entity_id`,
ADD COLUMN `old_values` JSON NULL COMMENT 'Previous values (for updates/deletes)' AFTER `description`,
ADD COLUMN `new_values` JSON NULL COMMENT 'New values (for creates/updates)' AFTER `old_values`,
ADD COLUMN `changed_fields` JSON NULL COMMENT 'Array of field names that changed' AFTER `new_values`,
ADD COLUMN `url` VARCHAR(500) NULL COMMENT 'Request URL' AFTER `changed_fields`,
ADD COLUMN `method` VARCHAR(10) NULL COMMENT 'HTTP method (GET, POST, etc.)' AFTER `url`,
ADD COLUMN `tags` JSON NULL COMMENT 'Custom tags for filtering' AFTER `method`,
ADD COLUMN `metadata` JSON NULL COMMENT 'Additional context data' AFTER `tags`;

-- Add indexes for new columns
CREATE INDEX `idx_user_name` ON `audit_logs` (`user_name`);
CREATE INDEX `idx_description` ON `audit_logs` (`description`(255));

-- Update existing records to have user_name
UPDATE `audit_logs` al
LEFT JOIN `users` u ON al.user_id = u.id
SET al.user_name = CONCAT(u.first_name, ' ', u.last_name)
WHERE al.user_name IS NULL AND u.id IS NOT NULL;

-- ============================================================================
-- CREATE AUDIT LOG STATISTICS VIEW
-- ============================================================================

CREATE OR REPLACE VIEW `audit_log_stats` AS
SELECT 
    DATE(created_at) as date,
    entity_type as model,
    action,
    COUNT(*) as count,
    COUNT(DISTINCT user_id) as unique_users
FROM audit_logs
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(created_at), entity_type, action
ORDER BY date DESC, count DESC;

-- ============================================================================
-- NOTES
-- ============================================================================

-- This migration enhances the existing audit_logs table with:
-- 1. Change tracking (old_values, new_values, changed_fields)
-- 2. Better context (description, url, method)
-- 3. Flexible metadata (tags, metadata JSON fields)
-- 4. Cached user_name for historical records

-- The existing columns are preserved:
-- - user_id, action, entity_type, entity_id, ip_address, user_agent, created_at

-- Usage:
-- 1. Run this migration: mysql -u user -p database < 100_enhance_audit_logging.sql
-- 2. Use the enhanced AuditLog trait in your models
-- 3. Existing audit logs will continue to work