-- Step 32: Video editor staff role and editor assignment for overtime recordings.
-- Run after step31_cameraman_duty_times.sql.

SET @step32_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `cameramen` ADD COLUMN `staff_role` ENUM(''Cameraman'',''Editor'',''Both'') NOT NULL DEFAULT ''Cameraman'' AFTER `status`',
    'SELECT ''cameramen.staff_role already exists'' AS message')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'cameramen'
    AND COLUMN_NAME = 'staff_role'
);
PREPARE step32_stmt FROM @step32_sql;
EXECUTE step32_stmt;
DEALLOCATE PREPARE step32_stmt;

SET @step32_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `cameramen` ADD KEY `idx_cameramen_staff_role_status` (`staff_role`, `status`)',
    'SELECT ''idx_cameramen_staff_role_status already exists'' AS message')
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'cameramen'
    AND INDEX_NAME = 'idx_cameramen_staff_role_status'
);
PREPARE step32_stmt FROM @step32_sql;
EXECUTE step32_stmt;
DEALLOCATE PREPARE step32_stmt;

SET @step32_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `schedule` ADD COLUMN `video_editor_id` INT UNSIGNED DEFAULT NULL AFTER `cameraman_id`',
    'SELECT ''schedule.video_editor_id already exists'' AS message')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'schedule'
    AND COLUMN_NAME = 'video_editor_id'
);
PREPARE step32_stmt FROM @step32_sql;
EXECUTE step32_stmt;
DEALLOCATE PREPARE step32_stmt;

SET @step32_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `daily_recording_instances` ADD COLUMN `video_editor_id` INT UNSIGNED DEFAULT NULL AFTER `cameraman_id`',
    'SELECT ''daily_recording_instances.video_editor_id already exists'' AS message')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'daily_recording_instances'
    AND COLUMN_NAME = 'video_editor_id'
);
PREPARE step32_stmt FROM @step32_sql;
EXECUTE step32_stmt;
DEALLOCATE PREPARE step32_stmt;

SET @step32_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `schedule` ADD KEY `idx_schedule_video_editor_id` (`video_editor_id`)',
    'SELECT ''idx_schedule_video_editor_id already exists'' AS message')
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'schedule'
    AND INDEX_NAME = 'idx_schedule_video_editor_id'
);
PREPARE step32_stmt FROM @step32_sql;
EXECUTE step32_stmt;
DEALLOCATE PREPARE step32_stmt;

SET @step32_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `daily_recording_instances` ADD KEY `idx_daily_instances_video_editor_id` (`video_editor_id`)',
    'SELECT ''idx_daily_instances_video_editor_id already exists'' AS message')
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'daily_recording_instances'
    AND INDEX_NAME = 'idx_daily_instances_video_editor_id'
);
PREPARE step32_stmt FROM @step32_sql;
EXECUTE step32_stmt;
DEALLOCATE PREPARE step32_stmt;

SET @step32_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `schedule` ADD CONSTRAINT `fk_schedule_video_editor` FOREIGN KEY (`video_editor_id`) REFERENCES `cameramen`(`id`) ON UPDATE CASCADE ON DELETE SET NULL',
    'SELECT ''fk_schedule_video_editor already exists'' AS message')
  FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND TABLE_NAME = 'schedule'
    AND CONSTRAINT_NAME = 'fk_schedule_video_editor'
);
PREPARE step32_stmt FROM @step32_sql;
EXECUTE step32_stmt;
DEALLOCATE PREPARE step32_stmt;

SET @step32_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `daily_recording_instances` ADD CONSTRAINT `fk_daily_instances_video_editor` FOREIGN KEY (`video_editor_id`) REFERENCES `cameramen`(`id`) ON UPDATE CASCADE ON DELETE SET NULL',
    'SELECT ''fk_daily_instances_video_editor already exists'' AS message')
  FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND TABLE_NAME = 'daily_recording_instances'
    AND CONSTRAINT_NAME = 'fk_daily_instances_video_editor'
);
PREPARE step32_stmt FROM @step32_sql;
EXECUTE step32_stmt;
DEALLOCATE PREPARE step32_stmt;

UPDATE cameramen
SET staff_role = CASE WHEN staff_role = 'Cameraman' THEN 'Both' ELSE staff_role END
WHERE UPPER(name) LIKE '%FAISAL%'
   OR UPPER(name) LIKE '%HAMZA%';

INSERT INTO cameramen
  (name, username, password, phone, availability, status, staff_role, is_supervisor, overtime_hourly_rate, duty_start_time, duty_end_time, created_at)
SELECT
  'SHAH FAISAL',
  'shah_faisal_editor',
  '$2y$10$/A/PiYF6oNzo9/OQYW4THOKqczqlaUXVSk7zg8VSFYW6b6IkgmrSm',
  NULL,
  'Available',
  'Active',
  'Editor',
  0,
  0.00,
  NULL,
  NULL,
  NOW()
WHERE NOT EXISTS (SELECT 1 FROM cameramen WHERE UPPER(name) LIKE '%FAISAL%')
  AND NOT EXISTS (SELECT 1 FROM cameramen WHERE username = 'shah_faisal_editor');

INSERT INTO cameramen
  (name, username, password, phone, availability, status, staff_role, is_supervisor, overtime_hourly_rate, duty_start_time, duty_end_time, created_at)
SELECT
  'HAMZA',
  'hamza_editor',
  '$2y$10$/A/PiYF6oNzo9/OQYW4THOKqczqlaUXVSk7zg8VSFYW6b6IkgmrSm',
  NULL,
  'Available',
  'Active',
  'Editor',
  0,
  0.00,
  NULL,
  NULL,
  NOW()
WHERE NOT EXISTS (SELECT 1 FROM cameramen WHERE UPPER(name) LIKE '%HAMZA%')
  AND NOT EXISTS (SELECT 1 FROM cameramen WHERE username = 'hamza_editor');