-- Activity Audit Log table
-- MySQL / MariaDB
-- Permission setup is maintained in database/sql/2026_09_04_rbac_permissions.sql.

CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NULL,
  `client_id` BIGINT UNSIGNED NULL,
  `action` VARCHAR(100) NOT NULL,
  `event` VARCHAR(20) NOT NULL,
  `subject_type` VARCHAR(255) NOT NULL,
  `subject_id` BIGINT UNSIGNED NULL,
  `description` VARCHAR(255) NULL,
  `old_values` JSON NULL,
  `new_values` JSON NULL,
  `request_method` VARCHAR(10) NULL,
  `request_url` TEXT NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` TEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `audit_logs_action_index` (`action`),
  KEY `audit_logs_event_index` (`event`),
  KEY `audit_logs_subject_index` (`subject_type`, `subject_id`),
  KEY `audit_logs_client_created_index` (`client_id`, `created_at`),
  KEY `audit_logs_user_created_index` (`user_id`, `created_at`),
  CONSTRAINT `audit_logs_user_id_foreign`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `audit_logs_client_id_foreign`
    FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;