-- Step 33: Manual overtime entries for cameramen, editors, and general staff.
-- Run after step32_video_editors_for_overtime.sql.

SET @step33_sql = (
  SELECT CASE
    WHEN COUNT(*) = 0 THEN
      'ALTER TABLE `cameramen` ADD COLUMN `staff_role` ENUM(''Cameraman'',''Editor'',''Both'',''Staff'') NOT NULL DEFAULT ''Cameraman'' AFTER `status`'
    WHEN MAX(COLUMN_TYPE) NOT LIKE '%''Staff''%' THEN
      'ALTER TABLE `cameramen` MODIFY COLUMN `staff_role` ENUM(''Cameraman'',''Editor'',''Both'',''Staff'') NOT NULL DEFAULT ''Cameraman'''
    ELSE
      'SELECT ''cameramen.staff_role already supports Staff'' AS message'
  END
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'cameramen'
    AND COLUMN_NAME = 'staff_role'
);
PREPARE step33_stmt FROM @step33_sql;
EXECUTE step33_stmt;
DEALLOCATE PREPARE step33_stmt;

CREATE TABLE IF NOT EXISTS manual_overtime_entries (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  entry_date DATE NOT NULL,
  staff_role ENUM('Cameraman','Editor','Staff','Other') NOT NULL DEFAULT 'Staff',
  staff_member_id INT UNSIGNED DEFAULT NULL,
  staff_name VARCHAR(150) NOT NULL,
  source_type ENUM('schedule','daily_instance','manual') NOT NULL DEFAULT 'manual',
  source_id INT UNSIGNED DEFAULT NULL,
  class_title VARCHAR(200) NOT NULL,
  overtime_minutes INT UNSIGNED NOT NULL DEFAULT 0,
  hourly_rate DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  remarks TEXT DEFAULT NULL,
  created_by INT UNSIGNED DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_manual_ot_date_role (entry_date, staff_role),
  KEY idx_manual_ot_staff (staff_member_id),
  KEY idx_manual_ot_source (source_type, source_id),
  KEY idx_manual_ot_created_by (created_by),
  CONSTRAINT fk_manual_ot_staff
    FOREIGN KEY (staff_member_id) REFERENCES cameramen(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_manual_ot_created_by
    FOREIGN KEY (created_by) REFERENCES users(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;