-- Migration 013: Create audit_logs table + immutability triggers
-- Requirements: 15.1, 15.2, 15.3, 15.4, 15.5
-- Design: Data Models -> 13. AUDIT LOGS (immutable, append-only)
--
-- Tabel audit_logs bersifat append-only: tidak boleh UPDATE atau DELETE oleh
-- siapapun (termasuk admin). Trigger BEFORE UPDATE / BEFORE DELETE meraise
-- SQLSTATE '45000' untuk membatalkan operasi tersebut di level engine.
--
-- Trigger ditulis dengan body single-statement (tanpa BEGIN..END dan tanpa
-- DELIMITER) sehingga kompatibel dengan parser `multipleStatements: true`
-- pada mysql2 — runner migrasi mengeksekusi seluruh isi file lewat satu
-- panggilan `connection.query()`. Catatan: MySQL hanya mendukung satu
-- BEFORE UPDATE / BEFORE DELETE trigger per tabel, jadi penambahan trigger
-- tambahan di masa depan harus mengkonsolidasi perilaku di sini.
--
-- IF NOT EXISTS pada CREATE TRIGGER butuh MySQL 8.0.22+; karena
-- schema_migrations menjamin migrasi tidak pernah di-run dua kali, kita
-- tidak menggunakan klausa tersebut agar tetap kompatibel dengan MariaDB
-- 10.3+ dan MySQL 8.0.x lebih lama.

CREATE TABLE IF NOT EXISTS audit_logs (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id             BIGINT UNSIGNED NULL,
    action_type         ENUM(
        'LOGIN','LOGIN_FAILED','LOGOUT',
        'FILE_UPLOAD','UPLOAD_COMPLETED','UPLOAD_FAILED',
        'HPP_CHANGE','VARIANT_CREATE','VARIANT_UPDATE','VARIANT_DELETE',
        'PRODUCT_CREATE','PRODUCT_UPDATE','PRODUCT_DELETE',
        'MAPPING_RESOLVE','MAPPING_REASSIGN',
        'TEMPLATE_SAVE','SETTING_CHANGE',
        'EXPORT_REQUEST','EXPORT_COMPLETED'
    ) NOT NULL,
    entity_type         VARCHAR(64)   NULL,
    entity_id           BIGINT UNSIGNED NULL,
    details             JSON          NULL,
    ip_address          VARCHAR(45)   NULL,
    created_at          DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    KEY idx_audit_user_time (user_id, created_at),
    KEY idx_audit_action_time (action_type, created_at),
    KEY idx_audit_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TRIGGER trg_audit_no_update BEFORE UPDATE ON audit_logs
FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'audit_logs is append-only';

CREATE TRIGGER trg_audit_no_delete BEFORE DELETE ON audit_logs
FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'audit_logs is append-only';