-- migration_audit_logs.sql
USE financeiro;

CREATE TABLE IF NOT EXISTS perm_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  subject_type ENUM('user','profile') NOT NULL,
  subject_id INT NOT NULL,
  changed_by_user_id INT NULL,
  source VARCHAR(40) NOT NULL,
  snapshot_json MEDIUMTEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_perm_hist_subject (subject_type, subject_id),
  CONSTRAINT fk_perm_hist_user FOREIGN KEY(changed_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  action VARCHAR(30) NOT NULL,
  entity VARCHAR(50) NOT NULL,
  entity_id VARCHAR(60) NULL,
  meta_json MEDIUMTEXT NULL,
  ip VARCHAR(64) NULL,
  user_agent VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_user (user_id, created_at),
  INDEX idx_audit_entity (entity, created_at),
  CONSTRAINT fk_audit_user FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB;
