-- 003_users_plans.sql
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','operator') NOT NULL DEFAULT 'operator',
  operator_id INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (operator_id) REFERENCES operators(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS kpi_plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ym CHAR(7) NOT NULL,
  name VARCHAR(120) NOT NULL,
  description TEXT,
  created_by INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY (ym, name)
);

CREATE TABLE IF NOT EXISTS kpi_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  plan_id INT NOT NULL,
  title VARCHAR(120) NOT NULL,
  description TEXT,
  target_int INT DEFAULT NULL,
  evidence_types SET('text','username','image','audio','video') NOT NULL,
  required TINYINT(1) DEFAULT 1,
  sort_order INT DEFAULT 0,
  FOREIGN KEY (plan_id) REFERENCES kpi_plans(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS kpi_assignments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  plan_id INT NOT NULL,
  operator_id INT NOT NULL,
  assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY (plan_id, operator_id),
  FOREIGN KEY (plan_id) REFERENCES kpi_plans(id) ON DELETE CASCADE,
  FOREIGN KEY (operator_id) REFERENCES operators(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS kpi_submissions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  plan_id INT NOT NULL,
  item_id INT NOT NULL,
  operator_id INT NOT NULL,
  ym CHAR(7) NOT NULL,
  text_content TEXT,
  username_ref VARCHAR(120),
  file_path VARCHAR(255),
  file_hash CHAR(64),
  file_mime VARCHAR(80),
  file_size INT,
  status ENUM('pending','approved','rejected') DEFAULT 'pending',
  reviewer_id INT DEFAULT NULL,
  review_note TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  reviewed_at TIMESTAMP NULL DEFAULT NULL,
  FOREIGN KEY (plan_id) REFERENCES kpi_plans(id) ON DELETE CASCADE,
  FOREIGN KEY (item_id) REFERENCES kpi_items(id) ON DELETE CASCADE,
  FOREIGN KEY (operator_id) REFERENCES operators(id) ON DELETE CASCADE,
  FOREIGN KEY (reviewer_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX (operator_id, item_id, ym),
  INDEX (file_hash)
);
