-- migration_all_admin_ia.sql
USE financeiro;

-- 1) Tabelas principais (cria se não existir)
CREATE TABLE IF NOT EXISTS permission_profiles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  UNIQUE KEY uk_perm_profile (name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS profile_permissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  profile_id INT NOT NULL,
  perm_key VARCHAR(60) NOT NULL,
  can_view TINYINT(1) NOT NULL DEFAULT 0,
  can_create TINYINT(1) NOT NULL DEFAULT 0,
  can_edit TINYINT(1) NOT NULL DEFAULT 0,
  can_delete TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  UNIQUE KEY uk_profile_perm (profile_id, perm_key),
  CONSTRAINT fk_profile_perm_profile FOREIGN KEY(profile_id) REFERENCES permission_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_permissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  perm_key VARCHAR(60) NOT NULL,
  can_view TINYINT(1) NOT NULL DEFAULT 0,
  can_create TINYINT(1) NOT NULL DEFAULT 0,
  can_edit TINYINT(1) NOT NULL DEFAULT 0,
  can_delete TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  UNIQUE KEY uk_user_perm (user_id, perm_key),
  CONSTRAINT fk_perm_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

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;

CREATE TABLE IF NOT EXISTS prompt_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  UNIQUE KEY uk_prompt_cat (name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS prompts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  name VARCHAR(140) NOT NULL,
  body MEDIUMTEXT NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  CONSTRAINT fk_prompt_cat FOREIGN KEY(category_id) REFERENCES prompt_categories(id)
) ENGINE=InnoDB;

-- 2) Alterações de colunas (rode UMA vez; se já existir, o MySQL pode retornar erro)
ALTER TABLE users ADD COLUMN profile_id INT NULL;
ALTER TABLE users ADD CONSTRAINT fk_users_profile FOREIGN KEY(profile_id) REFERENCES permission_profiles(id);

ALTER TABLE ai_reports
  ADD COLUMN prompt_id INT NULL,
  ADD COLUMN prompt_name VARCHAR(140) NULL,
  ADD COLUMN prompt_body MEDIUMTEXT NULL;

ALTER TABLE ai_reports
  ADD CONSTRAINT fk_air_prompt FOREIGN KEY(prompt_id) REFERENCES prompts(id);

-- FIM
