-- migration_permissions_prompts.sql
USE financeiro;

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_pp_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 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;

CREATE INDEX idx_prompts_cat ON prompts(category_id);
CREATE INDEX idx_prompts_active ON prompts(is_active);

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

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


ALTER TABLE users ADD COLUMN IF NOT EXISTS profile_id INT NULL;
ALTER TABLE users ADD CONSTRAINT fk_users_profile FOREIGN KEY(profile_id) REFERENCES permission_profiles(id);
