-- Hammerhead Workspace - Patch: KPI Tracker (Scheduling) - Finalized Structure
-- Implements the finalized Karan/Harry Weekly Scheduling KPI form exactly as
-- specified: full field set, Draft/Submitted/Reviewed/Approved/Returned/Locked
-- workflow, and mandatory audit history for unlocks.
-- Safe to re-run.

CREATE TABLE IF NOT EXISTS `kpi_scheduling_entries` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,

  -- Who submitted it (existing manager record - no duplicate users created)
  `submitter_type` enum('manager','employee') NOT NULL,
  `submitter_id` int(11) NOT NULL,
  `submitter_name` varchar(200) NOT NULL,
  `department_id` int(10) UNSIGNED NOT NULL,
  `project_id` int(10) UNSIGNED DEFAULT NULL,

  -- Workflow
  `status` enum('draft','submitted','reviewed','approved','returned','locked') NOT NULL DEFAULT 'draft',

  -- Week
  `week_ending` date NOT NULL,

  -- Hours breakdown
  `total_hours` decimal(7,1) DEFAULT NULL,
  `static_hours` decimal(7,1) DEFAULT NULL,
  `patrol_hours` decimal(7,1) DEFAULT NULL,
  `event_hours` decimal(7,1) DEFAULT NULL,
  `ot_hours` decimal(7,1) DEFAULT NULL,
  `ot_pct` decimal(6,2) DEFAULT NULL,
  `dt_hours` decimal(7,1) DEFAULT NULL,
  `dt_pct` decimal(6,2) DEFAULT NULL,
  `open_post_hours` decimal(7,1) DEFAULT NULL,

  -- Staffing
  `new_hires` int(10) DEFAULT NULL,
  `terminations` int(10) DEFAULT NULL,
  `open_requisition_hours` decimal(7,1) DEFAULT NULL,
  `open_sites` varchar(500) DEFAULT NULL,

  -- Inspections & incidents
  `guard_inspections` int(10) DEFAULT NULL,
  `failed_inspections` int(10) DEFAULT NULL,
  `incidents` int(10) DEFAULT NULL,
  `avg_response_hours` decimal(6,1) DEFAULT NULL,
  `client_complaints` int(10) DEFAULT NULL,
  `employee_complaints` int(10) DEFAULT NULL,

  -- Time off / call-offs
  `pto_requests` int(10) DEFAULT NULL,
  `call_off_hours` decimal(7,1) DEFAULT NULL,

  -- Scheduling & audits
  `scheduled_hours` decimal(7,1) DEFAULT NULL,
  `audit_hours` decimal(7,1) DEFAULT NULL,

  -- Backup employees for weekend (repeatable rows, stored as JSON per original form)
  `backup_employees` longtext DEFAULT NULL,

  -- Training
  `cesar_training` enum('Yes','No') DEFAULT NULL,
  `cesar_training_details` text DEFAULT NULL,

  -- Notes
  `learning_development` text DEFAULT NULL,
  `employee_concerns` text DEFAULT NULL,
  `remarks` text DEFAULT NULL,

  -- Review / approval / return
  `reviewer_type` enum('manager','superadmin') DEFAULT NULL,
  `reviewer_id` int(11) DEFAULT NULL,
  `reviewer_name` varchar(200) DEFAULT NULL,
  `review_comment` text DEFAULT NULL,
  `return_reason` text DEFAULT NULL,

  -- Lock / unlock (Super Admin only)
  `unlock_reason` text DEFAULT NULL,
  `unlocked_by_type` enum('superadmin') DEFAULT NULL,
  `unlocked_by_id` int(11) DEFAULT NULL,
  `unlocked_by_name` varchar(200) DEFAULT NULL,
  `unlocked_at` datetime DEFAULT NULL,

  -- Timestamps
  `submitted_at` datetime DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `returned_at` datetime DEFAULT NULL,
  `locked_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_submitter_week` (`submitter_type`,`submitter_id`,`week_ending`),
  KEY `idx_dept_status` (`department_id`,`status`),
  KEY `idx_submitter` (`submitter_type`,`submitter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Full audit trail for every workflow action (required for unlock history per spec)
CREATE TABLE IF NOT EXISTS `kpi_entry_audit` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `kpi_entry_id` bigint(20) UNSIGNED NOT NULL,
  `actor_type` enum('manager','employee','superadmin') NOT NULL,
  `actor_id` int(11) NOT NULL,
  `actor_name` varchar(200) NOT NULL,
  `action` enum('draft_saved','submitted','reviewed','approved','returned','locked','unlocked') NOT NULL,
  `reason` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_entry` (`kpi_entry_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Ensure at least one project exists under Scheduling so "Workspace -> Projects ->
-- KPI Tracker" has somewhere to attach to. Only creates one if Scheduling has none.
INSERT INTO `projects` (`department_id`, `name`, `code`, `status`)
SELECT d.id, 'Scheduling Operations', 'scheduling_operations', 'active'
FROM `departments` d
WHERE d.code = 'scheduling'
  AND NOT EXISTS (SELECT 1 FROM `projects` p WHERE p.department_id = d.id);