-- Idempotent update: introduce system audit log, permissions and defaults.

CREATE TABLE IF NOT EXISTS `system_audit_log` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `event_uuid` CHAR(36) NOT NULL,
  `request_id` CHAR(36) NULL,
  `channel` VARCHAR(16) NOT NULL,
  `event_type` VARCHAR(64) NOT NULL,
  `outcome` VARCHAR(16) NOT NULL,
  `error_code` VARCHAR(100) NULL,
  `actor_user_id` INT UNSIGNED NULL,
  `actor_tenant_id` INT UNSIGNED NULL,
  `target_type` VARCHAR(64) NULL,
  `target_id` INT UNSIGNED NULL,
  `target_uuid` CHAR(36) NULL,
  `method` VARCHAR(8) NULL,
  `path` VARCHAR(255) NULL,
  `ip_hash` CHAR(64) NULL,
  `user_agent_hash` CHAR(64) NULL,
  `metadata_json` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_system_audit_event_uuid` (`event_uuid`),
  KEY `idx_system_audit_created` (`created_at`),
  KEY `idx_system_audit_event_created` (`event_type`, `created_at`),
  KEY `idx_system_audit_actor_created` (`actor_user_id`, `created_at`),
  KEY `idx_system_audit_target_created` (`target_type`, `target_uuid`, `created_at`),
  KEY `idx_system_audit_request_created` (`request_id`, `created_at`),
  KEY `idx_system_audit_outcome_created` (`outcome`, `created_at`),
  CONSTRAINT `fk_system_audit_actor_user` FOREIGN KEY (`actor_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_system_audit_actor_tenant` FOREIGN KEY (`actor_tenant_id`) REFERENCES `tenants` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `permissions` (`key`, `description`, `active`, `is_system`)
VALUES
  ('system_audit.view', 'Can view system audit logs', 1, 1),
  ('system_audit.purge', 'Can purge system audit logs', 1, 1)
ON DUPLICATE KEY UPDATE
  `description` = VALUES(`description`),
  `active` = VALUES(`active`),
  `is_system` = VALUES(`is_system`);

INSERT INTO `role_permissions` (`role_id`, `permission_id`, `created`)
SELECT r.id, p.id, NOW()
FROM roles r
JOIN permissions p ON p.`key` IN ('system_audit.view', 'system_audit.purge')
WHERE r.description IN ('Admin', 'Administrator') OR r.id = 1
ON DUPLICATE KEY UPDATE role_id = role_id;

INSERT INTO `settings` (`key`, `value`, `description`)
VALUES
  ('system_audit_enabled', '1', 'setting.system_audit_enabled'),
  ('system_audit_retention_days', '365', 'setting.system_audit_retention_days')
ON DUPLICATE KEY UPDATE
  `value` = VALUES(`value`),
  `description` = VALUES(`description`);

INSERT INTO `scheduled_jobs` (
  `job_key`,
  `label`,
  `description`,
  `enabled`,
  `timezone`,
  `schedule_type`,
  `schedule_interval`,
  `schedule_time`,
  `schedule_weekdays_csv`,
  `catchup_once`,
  `next_run_at`
)
VALUES (
  'system_audit_purge',
  'System audit purge',
  'Purges system audit entries by retention policy',
  1,
  'Europe/Berlin',
  'daily',
  1,
  '03:00',
  NULL,
  1,
  NULL
)
ON DUPLICATE KEY UPDATE
  `label` = VALUES(`label`),
  `description` = VALUES(`description`);