-- Migration: Create Audit Logs Table
-- Purpose: Track all user actions in the system
-- Date: 2025-01-11
-- Note: Minimal design - no before/after data, just who/what/when/where

CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action VARCHAR(50) NOT NULL COMMENT 'create, update, delete, login, logout, view, etc.',
    entity_type VARCHAR(100) NOT NULL COMMENT 'users, roles, customers, invoices, etc.',
    entity_id INT NULL COMMENT 'ID of the affected record',
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_action (action),
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add audit permission
INSERT INTO permissions (name, module, description, created_at, updated_at)
VALUES ('audit_logs.view', 'System', 'View audit logs and system activity', NOW(), NOW())
ON DUPLICATE KEY UPDATE name = name;