-- Audit immutability: hash chain for tamper detection + retention policies

ALTER TABLE audit_logs
    ADD COLUMN IF NOT EXISTS prev_hash VARCHAR(64) DEFAULT NULL AFTER ip_address,
    ADD COLUMN IF NOT EXISTS hash VARCHAR(64) DEFAULT NULL AFTER prev_hash,
    ADD INDEX idx_al_hash (hash),
    ADD INDEX idx_al_prev_hash (prev_hash);

CREATE TABLE IF NOT EXISTS audit_retention_policies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    module VARCHAR(50) NOT NULL,
    action VARCHAR(50) DEFAULT NULL,
    retention_days INT UNSIGNED NOT NULL DEFAULT 365,
    archive_action ENUM('delete','anonymize','archive') NOT NULL DEFAULT 'delete',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_arp_module_action (module, action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO audit_retention_policies (module, action, retention_days, archive_action) VALUES
('default', NULL, 730, 'delete'),
('vehicle_fuel_entry', NULL, 1095, 'archive'),
('vehicle_document_renewal', NULL, 2190, 'archive'),
('maintenance', NULL, 2190, 'archive'),
('fuel', NULL, 1095, 'archive');

CREATE TABLE IF NOT EXISTS audit_retention_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ran_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    records_deleted INT UNSIGNED DEFAULT 0,
    records_archived INT UNSIGNED DEFAULT 0,
    details JSON DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (code, description) VALUES
('audit.verify', 'Verify audit log integrity'),
('audit.retention.manage', 'Manage audit retention policies'),
('audit.retention.run', 'Execute audit log cleanup');