-- Ops Runway — Full Database Schema (v2 — includes My Day, compulsory tasks, evidence)
-- Import this via cPanel's phpMyAdmin after creating your database

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  display_name VARCHAR(100) NOT NULL,
  department VARCHAR(50) NOT NULL, -- marketing, operations, communications, accounting, cx, hr, ceo
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS jobs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  job_code VARCHAR(20) NOT NULL UNIQUE,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  priority ENUM('low','medium','high') DEFAULT 'medium',
  created_by VARCHAR(100),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS job_todos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  job_id INT NOT NULL,
  department VARCHAR(50) NOT NULL, -- marketing, operations, communications, accounting, cx
  text VARCHAR(500) NOT NULL,
  status ENUM('open','done','expired','query','closed') DEFAULT 'open',
  deadline DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  created_by VARCHAR(100),
  query_note TEXT NULL,
  proof_note TEXT NULL,
  proof_file_name VARCHAR(255) NULL,
  proof_file_path VARCHAR(500) NULL,
  proof_by VARCHAR(100) NULL,
  proof_at DATETIME NULL,
  FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE
);

-- Company-wide compulsory task list. Same for everyone. Weights should sum to 70.
CREATE TABLE IF NOT EXISTS compulsory_tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  text VARCHAR(255) NOT NULL,
  weight INT NOT NULL,
  sort_order INT DEFAULT 0
);

-- One row per person, per day. This is the heart of "My Day".
CREATE TABLE IF NOT EXISTS myday_records (
  id INT AUTO_INCREMENT PRIMARY KEY,
  person_name VARCHAR(100) NOT NULL,
  the_date DATE NOT NULL,
  clock_in_time DATETIME NULL,
  clock_out_time DATETIME NULL,
  normal_slot_1_text VARCHAR(255) DEFAULT '',
  normal_slot_1_done TINYINT(1) DEFAULT 0,
  normal_slot_2_text VARCHAR(255) DEFAULT '',
  normal_slot_2_done TINYINT(1) DEFAULT 0,
  normal_slot_3_text VARCHAR(255) DEFAULT '',
  normal_slot_3_done TINYINT(1) DEFAULT 0,
  evidence_file_name VARCHAR(255) NULL,
  evidence_file_size INT NULL,
  evidence_file_path VARCHAR(500) NULL, -- actual file saved on disk, path stored here
  evidence_uploaded_at DATETIME NULL,
  UNIQUE KEY unique_person_date (person_name, the_date)
);

-- Which compulsory tasks were done, per myday_record
CREATE TABLE IF NOT EXISTS myday_compulsory_done (
  id INT AUTO_INCREMENT PRIMARY KEY,
  myday_record_id INT NOT NULL,
  compulsory_task_id INT NOT NULL,
  FOREIGN KEY (myday_record_id) REFERENCES myday_records(id) ON DELETE CASCADE,
  FOREIGN KEY (compulsory_task_id) REFERENCES compulsory_tasks(id) ON DELETE CASCADE,
  UNIQUE KEY unique_record_task (myday_record_id, compulsory_task_id)
);

-- Records every meaningful action on the Board, so HR/CEO can see full history.
CREATE TABLE IF NOT EXISTS activity_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  job_id INT NULL,
  todo_id INT NULL,
  department VARCHAR(50) NULL,
  action VARCHAR(50) NOT NULL, -- created_job, added_todo, marked_done, undo_done, hr_closed, hr_reopened, hr_queried
  detail TEXT NULL, -- e.g. the to-do text, or the query note
  performed_by VARCHAR(100) NOT NULL,
  performed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_performed_at (performed_at)
);

-- IMPORTANT: Do NOT insert user accounts or compulsory tasks here manually.
-- After importing this schema, open setup.php in your browser ONCE.
-- It creates the 7 default accounts (marketing, operations, communications,
-- accounting, cx, hr, ceo) and 3 starter compulsory tasks, then tell you
-- to delete setup.php. See SETUP_GUIDE.md for full steps.