-- install/schema_stage3.sql
-- Tables for Stage 3

CREATE TABLE IF NOT EXISTS offish (
  id INT AUTO_INCREMENT PRIMARY KEY,
  team_id INT NOT NULL,
  date_fa VARCHAR(16) NOT NULL,
  hour VARCHAR(8) NOT NULL,
  status ENUM('pending','approved','rejected','done') DEFAULT 'pending',
  start_ts INT NULL,
  arrive_ts INT NULL,
  finish_ts INT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  owner_user_id INT NOT NULL,
  role ENUM('scenario','editor','admin') NOT NULL,
  title VARCHAR(255) NOT NULL,
  status ENUM('open','done','late') DEFAULT 'open',
  due_ts INT NOT NULL,
  created_ts INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS penalties (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  type ENUM('public','manual') NOT NULL,
  amount INT NOT NULL,
  status ENUM('need_acc','need_ceo','approved','rejected') DEFAULT 'need_acc',
  reason VARCHAR(500) DEFAULT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reminders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  task_id INT NOT NULL,
  at_ts INT NOT NULL,
  sent TINYINT(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- optional light reports (aggregations can be computed live too)
CREATE TABLE IF NOT EXISTS reports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  kind VARCHAR(32) NOT NULL,
  payload TEXT,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
