-- 9. UNIVERSAL ACTIVITY LOG
--    Every controller/service action writes here — the single audit trail
--    for the whole platform (who did what, to what, when, from where).
-- ---------------------------------------------------------------------

CREATE TABLE activity_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED DEFAULT NULL,       -- NULL for unauthenticated/system events
  role_name VARCHAR(50) DEFAULT NULL,          -- denormalized snapshot at time of action
  action VARCHAR(100) NOT NULL,                -- created, updated, deleted, approved, logged_in...
  module VARCHAR(50) NOT NULL,                 -- students, results, fees, exams, auth...
  entity_type VARCHAR(50) DEFAULT NULL,        -- table/model name affected
  entity_id BIGINT UNSIGNED DEFAULT NULL,
  description VARCHAR(255) DEFAULT NULL,       -- human-readable summary
  old_values JSON DEFAULT NULL,
  new_values JSON DEFAULT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  user_agent VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_activity_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  KEY idx_activity_user (user_id),
  KEY idx_activity_entity (entity_type, entity_id),
  KEY idx_activity_module_action (module, action),
  KEY idx_activity_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;