-- Import after migration 020. Existing audit data remains unchanged.
CREATE TABLE activity_log (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 source_kind ENUM('unit','province') NOT NULL,
 source_id BIGINT UNSIGNED NOT NULL,
 actor_id BIGINT UNSIGNED NULL,
 province_id BIGINT UNSIGNED NULL,
 unit_id BIGINT UNSIGNED NULL,
 action VARCHAR(100) NOT NULL,
 target_type VARCHAR(50) NULL,
 target_id BIGINT UNSIGNED NULL,
 change_summary VARCHAR(250) NOT NULL,
 occurred_at DATETIME NOT NULL,
 UNIQUE KEY uq_activity_source(source_kind,source_id),
 KEY idx_activity_unit(unit_id,occurred_at,id),
 KEY idx_activity_province(province_id,occurred_at,id),
 KEY idx_activity_actor(actor_id,occurred_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- A database trigger also covers older endpoints that insert directly into
-- the existing audit tables. It copies identifiers, never request bodies.
CREATE TRIGGER trg_activity_from_unit AFTER INSERT ON audit_log FOR EACH ROW
INSERT INTO activity_log(source_kind,source_id,actor_id,province_id,unit_id,action,target_type,target_id,change_summary,occurred_at)
VALUES('unit',NEW.id,NEW.user_id,(SELECT u.province_id FROM units u WHERE u.id=NEW.unit_id),NEW.unit_id,
NEW.action,NEW.entity_type,NEW.entity_id,REPLACE(NEW.action,'_',' '),NEW.created_at);

CREATE TRIGGER trg_activity_from_province AFTER INSERT ON province_audit_log FOR EACH ROW
INSERT INTO activity_log(source_kind,source_id,actor_id,province_id,unit_id,action,target_type,target_id,change_summary,occurred_at)
VALUES('province',NEW.id,NEW.actor_id,NEW.province_id,NEW.unit_id,NEW.action,
IF(NEW.unit_id IS NULL,'province','unit'),IFNULL(NEW.unit_id,NEW.province_id),
REPLACE(NEW.action,'_',' '),NEW.created_at);

-- Existing rows are included for continuity without copying historical details.
INSERT IGNORE INTO activity_log(source_kind,source_id,actor_id,province_id,unit_id,action,target_type,target_id,change_summary,occurred_at)
SELECT 'unit',a.id,a.user_id,u.province_id,a.unit_id,a.action,a.entity_type,a.entity_id,
REPLACE(a.action,'_',' '),a.created_at FROM audit_log a LEFT JOIN units u ON u.id=a.unit_id;
INSERT IGNORE INTO activity_log(source_kind,source_id,actor_id,province_id,unit_id,action,target_type,target_id,change_summary,occurred_at)
SELECT 'province',a.id,a.actor_id,a.province_id,a.unit_id,a.action,
IF(a.unit_id IS NULL,'province','unit'),IFNULL(a.unit_id,a.province_id),
REPLACE(a.action,'_',' '),a.created_at FROM province_audit_log a;

CREATE TRIGGER trg_activity_immutable_update BEFORE UPDATE ON activity_log FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Activity history is append-only';
CREATE TRIGGER trg_activity_immutable_delete BEFORE DELETE ON activity_log FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Activity history is append-only';