-- Migration: Create audit_logs table for security audit trail
-- Date: 2025-12-02
-- Description: Adds comprehensive audit logging for authentication and sensitive operations
-- Status: Ready for deployment

-- ============================================================
-- AUDIT LOGS TABLE
-- ============================================================
-- Tracks all sensitive operations for security auditing and compliance
-- Captured events: logins, password changes, bookings, payments, membership actions

CREATE TABLE IF NOT EXISTS `audit_logs` (
  `log_id` int NOT NULL AUTO_INCREMENT COMMENT 'Unique audit log identifier',
  `user_id` int DEFAULT NULL COMMENT 'ID of user performing action (NULL for anonymous attempts)',
  `action` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Action type: login_success, login_failure, password_change, etc.',
  `status` varchar(20) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Status: success, failure, pending',
  `ip_address` varchar(45) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Client IP address (supports IPv4 and IPv6)',
  `details` json DEFAULT NULL COMMENT 'Additional metadata (email, failure reason, amount, etc.)',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When the action occurred',
  
  PRIMARY KEY (`log_id`),
  
  -- Indexes for common queries
  KEY `idx_user_id` (`user_id`),
  KEY `idx_action` (`action`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_ip_address` (`ip_address`),
  KEY `idx_user_created` (`user_id`, `created_at`),
  
  -- Foreign key constraint (optional, remove if cascading deletes cause issues)
  CONSTRAINT `audit_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
COMMENT='Audit trail for security events and sensitive operations';

-- ============================================================
-- ACTION TYPES REFERENCE (for documentation)
-- ============================================================
-- login_success          - Successful login (email/password or Google OAuth)
-- login_failure          - Failed login attempt (captured with reason)
-- logout                 - User logout event
-- password_change        - User changed their password
-- password_reset         - User initiated password reset
-- booking_create         - New booking created
-- booking_cancel         - Booking cancelled
-- booking_modify         - Booking modified
-- payment_initiate       - Payment process started
-- payment_success        - Payment completed successfully
-- payment_failure        - Payment failed
-- membership_application - Membership application submitted
-- membership_approval    - Membership approved
-- membership_renewal     - Membership renewed
-- admin_action           - Admin performed action
-- access_denied          - Unauthorized access attempt

-- ============================================================
-- SAMPLE QUERIES FOR MONITORING
-- ============================================================

-- View last 10 login attempts
-- SELECT * FROM audit_logs WHERE action LIKE 'login%' ORDER BY created_at DESC LIMIT 10;

-- Count failed login attempts in last 15 minutes
-- SELECT COUNT(*) as failed_attempts FROM audit_logs 
-- WHERE action = 'login_failure' AND created_at > DATE_SUB(NOW(), INTERVAL 15 MINUTE);

-- Get all failed logins for a specific user
-- SELECT * FROM audit_logs WHERE user_id = 5 AND action = 'login_failure' ORDER BY created_at DESC;

-- Track login attempts by IP address (detect brute force)
-- SELECT ip_address, COUNT(*) as attempt_count, MAX(created_at) as last_attempt
-- FROM audit_logs 
-- WHERE action = 'login_failure' AND created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)
-- GROUP BY ip_address HAVING attempt_count > 5 ORDER BY attempt_count DESC;

-- View all payments for a user
-- SELECT * FROM audit_logs WHERE user_id = 5 AND action LIKE 'payment%' ORDER BY created_at DESC;

-- Audit trail for bookings
-- SELECT * FROM audit_logs WHERE user_id = 5 AND action LIKE 'booking%' ORDER BY created_at DESC;

-- Get logs with details decoded (for analysis)
-- SELECT log_id, user_id, action, status, ip_address, 
--        JSON_EXTRACT(details, '$.email') as email,
--        JSON_EXTRACT(details, '$.reason') as reason,
--        created_at
-- FROM audit_logs WHERE action = 'login_failure' ORDER BY created_at DESC LIMIT 20;

-- ============================================================
-- MAINTENANCE
-- ============================================================

-- Create retention policy (keep 1 year of logs, optional)
-- DELETE FROM audit_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- Check table size
-- SELECT 
--   table_name, 
--   ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
-- FROM information_schema.TABLES 
-- WHERE table_schema = DATABASE() AND table_name = 'audit_logs';