-- ============================================================
-- HAMMERHEAD WORKSPACE - COMPLETE PATCH SET (everything since initial setup)
-- Every statement below is idempotent (safe to run even if parts
-- were already applied before). Run this single file top to bottom
-- in phpMyAdmin's SQL tab against your live database.
--
-- Does NOT include hammerhead_original.sql (your base schema/data -
-- that's a one-time import you already did when the DB was first created).
-- ============================================================

-- ================= schema_extension.sql =================
-- ============================================================
-- Hammerhead Workspace - Schema Extension
-- Adds tables needed for: Workspace Home, Super Admin Panel,
-- Notifications, Activity/Audit Log, Project Dashboards config.
-- Run AFTER hammerhead_original.sql
-- ============================================================

-- 1. NOTIFICATIONS (Workspace Home > Notifications / Pending Items)
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_type` enum('superadmin','manager','employee') NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(200) NOT NULL,
  `message` varchar(500) DEFAULT '',
  `link` varchar(300) DEFAULT '',
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_notif_user` (`user_type`,`user_id`,`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. ACTIVITY LOG (Workspace Home > Recent Activity, and audit trail)
CREATE TABLE IF NOT EXISTS `activity_log` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_type` enum('superadmin','manager','employee') NOT NULL,
  `user_id` int(11) NOT NULL,
  `user_name` varchar(200) DEFAULT '',
  `department_id` int(10) UNSIGNED DEFAULT NULL,
  `project_id` int(10) UNSIGNED DEFAULT NULL,
  `action` varchar(100) NOT NULL,          -- e.g. 'task_created', 'user_updated', 'login'
  `description` varchar(500) DEFAULT '',
  `ip` varchar(45) DEFAULT '',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_activity_user` (`user_type`,`user_id`),
  KEY `idx_activity_dept` (`department_id`),
  KEY `idx_activity_project` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. AUDIT LOG (Super Admin > Reports & Audit Logs - sensitive admin actions)
CREATE TABLE IF NOT EXISTS `audit_log` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `actor_type` enum('superadmin','manager','employee') NOT NULL,
  `actor_id` int(11) NOT NULL,
  `actor_name` varchar(200) DEFAULT '',
  `entity_type` varchar(60) NOT NULL,      -- 'user','role','department','project','permission'
  `entity_id` varchar(60) DEFAULT '',
  `action` enum('create','update','delete','enable','disable','role_change','permission_change','password_reset','login','logout','login_failed') NOT NULL,
  `before_data` longtext DEFAULT NULL,     -- JSON snapshot
  `after_data` longtext DEFAULT NULL,      -- JSON snapshot
  `ip` varchar(45) DEFAULT '',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_audit_actor` (`actor_type`,`actor_id`),
  KEY `idx_audit_entity` (`entity_type`,`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. PROJECT DASHBOARD WIDGETS (config-driven, so dashboards are not hardcoded per project)
CREATE TABLE IF NOT EXISTS `dashboard_widgets` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `project_id` int(10) UNSIGNED NOT NULL,
  `widget_type` enum('stat_card','pie_chart','bar_chart','line_chart','recent_activity','quick_actions','table') NOT NULL,
  `title` varchar(150) NOT NULL,
  `config` longtext DEFAULT NULL,          -- JSON: data source query key, colors, columns etc.
  `sort_order` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `idx_widget_project` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. PROJECT FORMS (generic form builder reference per project)
CREATE TABLE IF NOT EXISTS `project_forms` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `project_id` int(10) UNSIGNED NOT NULL,
  `name` varchar(150) NOT NULL,
  `slug` varchar(150) NOT NULL,
  `schema` longtext DEFAULT NULL,          -- JSON field definitions
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_project_slug` (`project_id`,`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. PROJECT REPORTS (saved/report definitions per project)
CREATE TABLE IF NOT EXISTS `project_reports` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `project_id` int(10) UNSIGNED NOT NULL,
  `name` varchar(150) NOT NULL,
  `description` varchar(400) DEFAULT '',
  `query_config` longtext DEFAULT NULL,    -- JSON: filters, columns, grouping
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. UNIFIED SESSIONS (for "One Login" across superadmin/manager/employee)
CREATE TABLE IF NOT EXISTS `sessions` (
  `id` varchar(64) NOT NULL,               -- session token (hashed)
  `user_type` enum('superadmin','manager','employee') NOT NULL,
  `user_id` int(11) NOT NULL,
  `ip` varchar(45) DEFAULT '',
  `user_agent` varchar(300) DEFAULT '',
  `last_activity` timestamp NOT NULL DEFAULT current_timestamp(),
  `expires_at` datetime NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_sess_user` (`user_type`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8a. Widen user_type enums to include 'employee' — original schema only allowed
--     ('manager','superadmin'), which would silently block employee access assignment.
ALTER TABLE `user_roles`
  MODIFY `user_type` enum('manager','superadmin','employee') NOT NULL;

ALTER TABLE `user_departments`
  MODIFY `user_type` enum('manager','superadmin','employee') NOT NULL;

ALTER TABLE `user_projects`
  MODIFY `user_type` enum('manager','superadmin','employee') NOT NULL;

ALTER TABLE `user_permission_overrides`
  MODIFY `user_type` enum('manager','superadmin','employee') NOT NULL;

-- 8b. Add missing columns to employees for login (spec says employees can also log in)
ALTER TABLE `employees`
  ADD COLUMN IF NOT EXISTS `username` varchar(100) DEFAULT NULL AFTER `name`,
  ADD COLUMN IF NOT EXISTS `email` varchar(200) DEFAULT NULL AFTER `username`,
  ADD COLUMN IF NOT EXISTS `password_hash` varchar(255) DEFAULT NULL AFTER `email`,
  ADD COLUMN IF NOT EXISTS `is_active` tinyint(1) DEFAULT 1 AFTER `password_hash`,
  ADD COLUMN IF NOT EXISTS `reset_token` varchar(100) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `reset_expires` datetime DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `last_login` datetime DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `reports_to_type` enum('manager','superadmin') DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `reports_to_id` int(11) DEFAULT NULL;

ALTER TABLE `managers`
  ADD COLUMN IF NOT EXISTS `reports_to_type` enum('manager','superadmin') DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `reports_to_id` int(11) DEFAULT NULL;

-- ============================================================
-- NOTE: roles, modules, permissions, role_permissions, and
-- departments are already fully seeded in your existing database
-- (roles: super_admin/CEO, department_head, manager, team_lead,
-- employee — modules 1-9 — permissions 1-72). No re-seeding needed
-- or wanted here; doing so would conflict with your existing IDs.
-- ============================================================


-- ================= patch_widen_employee_enums.sql =================
-- Hammerhead Workspace - Patch: widen user_type enums to include 'employee'
-- Safe to run even if already applied.

ALTER TABLE `user_roles`
  MODIFY `user_type` enum('manager','superadmin','employee') NOT NULL;

ALTER TABLE `user_departments`
  MODIFY `user_type` enum('manager','superadmin','employee') NOT NULL;

ALTER TABLE `user_projects`
  MODIFY `user_type` enum('manager','superadmin','employee') NOT NULL;

ALTER TABLE `user_permission_overrides`
  MODIFY `user_type` enum('manager','superadmin','employee') NOT NULL;

-- ================= patch_2fa_and_settings.sql =================
-- Hammerhead Workspace - Patch 2: Two-Factor Auth + System Settings
-- Safe to re-run (uses IF NOT EXISTS / ON DUPLICATE KEY UPDATE).

ALTER TABLE `superadmins`
  ADD COLUMN IF NOT EXISTS `two_factor_enabled` tinyint(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `two_factor_secret` varchar(64) DEFAULT NULL;

ALTER TABLE `managers`
  ADD COLUMN IF NOT EXISTS `two_factor_enabled` tinyint(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `two_factor_secret` varchar(64) DEFAULT NULL;

ALTER TABLE `employees`
  ADD COLUMN IF NOT EXISTS `two_factor_enabled` tinyint(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `two_factor_secret` varchar(64) DEFAULT NULL;

CREATE TABLE IF NOT EXISTS `system_settings` (
  `setting_key` varchar(100) NOT NULL,
  `setting_value` longtext DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `system_settings` (`setting_key`, `setting_value`) VALUES
  ('app_name', 'Hammerhead Workspace'),
  ('require_2fa_for_admins', '0'),
  ('session_timeout_minutes', '120'),
  ('max_login_attempts', '5')
ON DUPLICATE KEY UPDATE `setting_value` = `setting_value`;

-- ================= patch_kpi_tracker.sql =================
-- Hammerhead Workspace - Patch 3: KPI Tracker
-- Replaces the Google Sheets / Apps Script KPI workflow with native tables.
-- Safe to re-run.

CREATE TABLE IF NOT EXISTS `kpi_metric_defs` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `department_id` int(10) UNSIGNED NOT NULL,
  `metric_key` varchar(80) NOT NULL,
  `metric_label` varchar(150) NOT NULL,
  `unit` varchar(30) DEFAULT '',          -- '%', 'count', 'hrs', etc.
  `input_type` enum('number','percent','text','select') NOT NULL DEFAULT 'number',
  `select_options` text DEFAULT NULL,     -- pipe-separated, only used when input_type = 'select'
  `sort_order` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_dept_metric` (`department_id`,`metric_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `kpi_entries` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `department_id` int(10) UNSIGNED NOT NULL,
  `user_type` enum('manager','superadmin','employee') NOT NULL,
  `user_id` int(11) NOT NULL,
  `user_name` varchar(200) DEFAULT '',
  `period_start` date NOT NULL,           -- Monday of the reporting week
  `values_json` longtext NOT NULL,        -- { metric_key: value, ... }
  `submitted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_kpi_dept_period` (`department_id`,`period_start`),
  KEY `idx_kpi_user` (`user_type`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Starter metric definitions per department (edit anytime from Admin > KPI Tracker > Setup)
INSERT INTO `kpi_metric_defs` (`department_id`, `metric_key`, `metric_label`, `unit`, `input_type`, `sort_order`)
SELECT d.id, 'tasks_completed', 'Tasks Completed', 'count', 'number', 1 FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM kpi_metric_defs m WHERE m.department_id = d.id AND m.metric_key = 'tasks_completed');

INSERT INTO `kpi_metric_defs` (`department_id`, `metric_key`, `metric_label`, `unit`, `input_type`, `sort_order`)
SELECT d.id, 'completion_rate', 'Completion Rate', '%', 'percent', 2 FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM kpi_metric_defs m WHERE m.department_id = d.id AND m.metric_key = 'completion_rate');

INSERT INTO `kpi_metric_defs` (`department_id`, `metric_key`, `metric_label`, `unit`, `input_type`, `sort_order`)
SELECT d.id, 'notes', 'Notes / Blockers', '', 'text', 3 FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM kpi_metric_defs m WHERE m.department_id = d.id AND m.metric_key = 'notes');

-- ================= patch_reports_to_index.sql =================
-- Hammerhead Workspace - Patch 4: Reports-To Hierarchy
-- reports_to_type/reports_to_id columns already exist on managers/employees
-- (added by the original schema extension). This just adds an index for
-- fast "who reports to me" lookups used by the hierarchy going forward.
-- Safe to re-run.

ALTER TABLE `managers` ADD INDEX IF NOT EXISTS `idx_reports_to` (`reports_to_type`, `reports_to_id`);
ALTER TABLE `employees` ADD INDEX IF NOT EXISTS `idx_reports_to` (`reports_to_type`, `reports_to_id`);

-- ================= patch_add_scheduling_dept.sql =================
-- Hammerhead Workspace - Patch 5: Add Scheduling department
-- Safe to re-run (checks for existing name first).

INSERT INTO `departments` (`name`, `code`, `is_active`)
SELECT 'Scheduling', 'scheduling', 1
WHERE NOT EXISTS (SELECT 1 FROM `departments` WHERE `code` = 'scheduling');

-- ================= patch_add_roles.sql =================
-- Hammerhead Workspace - Patch 6: Additional roles
-- Adds Ops Manager, Office Manager, Field Team, Account Manager (and Director,
-- skipped if it already exists). Levels are slotted at the same tier as
-- "Manager" so they sort together in the role dropdown without disturbing
-- your existing CEO/Director/Manager/Employee ordering.
-- Safe to re-run.

INSERT INTO `roles` (`code`, `name`, `level`, `description`)
SELECT 'director', 'Director', COALESCE((SELECT level FROM roles WHERE code='manager' LIMIT 1), 3) - 1, 'Oversees multiple departments.'
WHERE NOT EXISTS (SELECT 1 FROM `roles` WHERE `name` = 'Director');

INSERT INTO `roles` (`code`, `name`, `level`, `description`)
SELECT 'ops_manager', 'Ops Manager', COALESCE((SELECT level FROM roles WHERE code='manager' LIMIT 1), 3), 'Manages Operations department.'
WHERE NOT EXISTS (SELECT 1 FROM `roles` WHERE `name` = 'Ops Manager');

INSERT INTO `roles` (`code`, `name`, `level`, `description`)
SELECT 'office_manager', 'Office Manager', COALESCE((SELECT level FROM roles WHERE code='manager' LIMIT 1), 3), 'Manages Office / Purchasing / Recruiting.'
WHERE NOT EXISTS (SELECT 1 FROM `roles` WHERE `name` = 'Office Manager');

INSERT INTO `roles` (`code`, `name`, `level`, `description`)
SELECT 'field_team', 'Field Team', COALESCE((SELECT level FROM roles WHERE code='manager' LIMIT 1), 3) + 1, 'Field-based team member/lead.'
WHERE NOT EXISTS (SELECT 1 FROM `roles` WHERE `name` = 'Field Team');

INSERT INTO `roles` (`code`, `name`, `level`, `description`)
SELECT 'account_manager', 'Account Manager', COALESCE((SELECT level FROM roles WHERE code='manager' LIMIT 1), 3), 'Manages client accounts.'
WHERE NOT EXISTS (SELECT 1 FROM `roles` WHERE `name` = 'Account Manager');

-- ================= patch_dedupe_roles_and_add_cfo_vp.sql =================
-- Hammerhead Workspace - Patch 8: De-duplicate roles
-- A previous patch inserted a role checking by `code`, but an existing role
-- with a different code already had the same display `name` (e.g. "Director"),
-- resulting in two rows with the same name. This keeps the OLDEST row (lowest id)
-- per duplicated name, re-points any user_roles/role_permissions that were
-- assigned to the newer duplicate over to the kept row, then removes the duplicate.
-- Safe to re-run (no-op if there are no duplicates left).

-- 1. Build a temp map of duplicate-name groups: keep_id = MIN(id), dupe_id = any others
DROP TEMPORARY TABLE IF EXISTS `_role_dupes`;
CREATE TEMPORARY TABLE `_role_dupes` AS
SELECT r.id AS dupe_id, keep.keep_id
FROM roles r
JOIN (
    SELECT name, MIN(id) AS keep_id
    FROM roles
    GROUP BY name
    HAVING COUNT(*) > 1
) keep ON keep.name = r.name
WHERE r.id <> keep.keep_id;

-- 2. Re-point any user_roles pointing at the duplicate role to the kept role
UPDATE user_roles ur
JOIN `_role_dupes` d ON d.dupe_id = ur.role_id
SET ur.role_id = d.keep_id;

-- 3. Re-point any role_permissions pointing at the duplicate role to the kept role
--    (ignore if it would create a duplicate primary key - keep the kept role's existing grants)
DROP TEMPORARY TABLE IF EXISTS `_rp_conflicts`;
CREATE TEMPORARY TABLE `_rp_conflicts` AS
SELECT rp.role_id, rp.permission_id
FROM role_permissions rp
JOIN `_role_dupes` d ON d.dupe_id = rp.role_id
JOIN role_permissions rp2 ON rp2.role_id = d.keep_id AND rp2.permission_id = rp.permission_id;

DELETE rp FROM role_permissions rp
JOIN `_rp_conflicts` c ON c.role_id = rp.role_id AND c.permission_id = rp.permission_id;

DROP TEMPORARY TABLE IF EXISTS `_rp_conflicts`;

UPDATE role_permissions rp
JOIN `_role_dupes` d ON d.dupe_id = rp.role_id
SET rp.role_id = d.keep_id;

-- 4. Delete the now-unreferenced duplicate role rows
DELETE r FROM roles r
JOIN `_role_dupes` d ON d.dupe_id = r.id;

DROP TEMPORARY TABLE IF EXISTS `_role_dupes`;

-- 5. Add the two newly requested roles (safe if they already exist)
INSERT INTO `roles` (`code`, `name`, `level`, `description`)
SELECT 'cfo', 'Chief Financial Officer (CFO)', COALESCE((SELECT level FROM roles WHERE code='ceo' LIMIT 1), 1) + 1, 'Senior financial executive.'
WHERE NOT EXISTS (SELECT 1 FROM `roles` WHERE `name` = 'Chief Financial Officer (CFO)');

INSERT INTO `roles` (`code`, `name`, `level`, `description`)
SELECT 'vice_president', 'Vice President', COALESCE((SELECT level FROM roles WHERE code='ceo' LIMIT 1), 1) + 1, 'Senior executive overseeing departments.'
WHERE NOT EXISTS (SELECT 1 FROM `roles` WHERE `name` = 'Vice President');

-- 6. Prevent this from happening again - enforce unique role names going forward
ALTER TABLE `roles` ADD UNIQUE KEY IF NOT EXISTS `uq_roles_name` (`name`);

-- ================= patch_forgot_password.sql =================
-- Hammerhead Workspace - Patch 7: Forgot Password support for Super Admins
-- managers/employees already have reset_token/reset_expires columns.
-- Safe to re-run.

ALTER TABLE `superadmins`
  ADD COLUMN IF NOT EXISTS `reset_token` varchar(100) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `reset_expires` datetime DEFAULT NULL;

-- ================= patch_force_password_change.sql =================
-- Hammerhead Workspace - Patch 9: Forced password change on first login
-- Safe to re-run.

ALTER TABLE `managers`
  ADD COLUMN IF NOT EXISTS `force_password_change` tinyint(1) NOT NULL DEFAULT 0;

ALTER TABLE `employees`
  ADD COLUMN IF NOT EXISTS `force_password_change` tinyint(1) NOT NULL DEFAULT 0;

-- ================= patch_revert_guard_onboarding.sql =================
-- Hammerhead Workspace - Patch 10: Revert accidental guard onboarding
-- The "Generate Logins for Everyone Without Access" feature had a bug that
-- pulled in employees (security guards) who should have stayed excluded.
-- This clears login credentials back off every employee record - nobody in
-- `employees` should have login access right now per explicit instruction.
-- Managers are completely untouched by this patch.
-- Safe to re-run.

UPDATE `employees`
SET `username` = NULL,
    `password_hash` = NULL,
    `force_password_change` = 0,
    `reset_token` = NULL,
    `reset_expires` = NULL;

-- Also clear any role/department/project assignments and audit log entries
-- that got created for employees during the accidental bulk run.
DELETE FROM `user_roles` WHERE `user_type` = 'employee';
DELETE FROM `user_departments` WHERE `user_type` = 'employee';
DELETE FROM `user_projects` WHERE `user_type` = 'employee';

-- ================= patch_kpi_scheduling_v2.sql =================
-- 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);

-- ============================================================
-- Deployment 11: fix placeholder project name -> "KPI"
-- (Already applied manually on production on 2026-07-02; kept
-- here so this file stays a true record of every patch applied.)
-- ============================================================
UPDATE `projects` SET `name` = 'KPI', `code` = 'kpi' WHERE `code` = 'scheduling_operations';
-- ============================================================
-- Hammerhead Workspace - Deployment 13 Patch
-- César KPI Hierarchy Setup
-- Establishes reporting structure for KPI submission/review flow:
--   Karan Kumar → César Saona → Sara Lasher
--   Harpreet Singh → César Saona → Sara Lasher
--
-- This ensures:
-- 1. César can submit his own KPIs (he's already in KpiConfig::schedulingEntryUsernames)
-- 2. His KPIs are sent to Sara for review
-- 3. Sara sees all KPI submissions from her direct reports
-- 4. Karan & Harpreet's existing submission flow remains unchanged
--
-- Safe to re-run (all IDs fetched dynamically)
-- ============================================================

-- First, get all required IDs
SET @cesar_id = (SELECT id FROM managers WHERE username = 'cesar.saona' LIMIT 1);
SET @sara_id = (SELECT id FROM managers WHERE username = 'sara.lasher' LIMIT 1);
SET @karan_id = (SELECT id FROM managers WHERE username = 'karan.kumar' LIMIT 1);
SET @harpreet_id = (SELECT id FROM managers WHERE username = 'harpreet.singh' LIMIT 1);

-- Verify all IDs were found
SELECT @cesar_id AS cesar_id, @sara_id AS sara_id, @karan_id AS karan_id, @harpreet_id AS harpreet_id;

-- Set up reporting structure only if all IDs exist
-- (Silent no-op if any user doesn't exist yet)
UPDATE managers
SET reports_to_type = 'manager', reports_to_id = @cesar_id
WHERE id = @karan_id AND @cesar_id IS NOT NULL;

UPDATE managers
SET reports_to_type = 'manager', reports_to_id = @cesar_id
WHERE id = @harpreet_id AND @cesar_id IS NOT NULL;

UPDATE managers
SET reports_to_type = 'manager', reports_to_id = @sara_id
WHERE id = @cesar_id AND @sara_id IS NOT NULL;

-- Verify the result
SELECT username, name, 
  CASE WHEN reports_to_type = 'manager' THEN CONCAT('Manager ID ', reports_to_id)
       WHEN reports_to_type = 'superadmin' THEN 'Super Admin'
       ELSE 'Not set' END AS 'Reports To'
FROM managers
WHERE username IN ('karan.kumar', 'harpreet.singh', 'cesar.saona', 'sara.lasher')
ORDER BY username;

-- ============================================================
-- Hammerhead Workspace — Deployment 13
-- César Operations Manager KPI Table
-- Completely separate from kpi_scheduling_entries.
-- Safe to re-run (CREATE TABLE IF NOT EXISTS).
-- ============================================================

CREATE TABLE IF NOT EXISTS `cesar_kpi_entries` (
  `id`               int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `week_ending`      date             NOT NULL,
  `status`           enum('draft','submitted','reviewed','approved','returned','locked') NOT NULL DEFAULT 'draft',

  -- Hours Pipeline
  `contracts_started` longtext DEFAULT NULL COMMENT 'JSON array of {site_id,site_name,custom_name,hours}',
  `contracts_lost`    longtext DEFAULT NULL COMMENT 'JSON array of {site_id,site_name,custom_name,hours,comments}',
  `net_hours_change`  decimal(10,2)    DEFAULT NULL COMMENT 'auto: started minus lost',

  -- Post Order Updates
  `post_order_updates` longtext DEFAULT NULL COMMENT 'JSON array of {site_id,site_name,comments}',

  -- Client Meetings
  `client_meetings`   longtext DEFAULT NULL COMMENT 'JSON array of {site_id,site_name,client_name,meeting_notes,open_notes,satisfaction,risk_level}',

  -- Site Visit Scores
  `site_visit_scores` longtext DEFAULT NULL COMMENT 'JSON array of {site_id,site_name,score,reason}',

  -- New Client Reach Outs
  `new_client_reachouts` longtext DEFAULT NULL COMMENT 'JSON array of {company_name,contact_name,phone,notes}',

  -- Contract Renewals
  `contract_renewals` longtext DEFAULT NULL COMMENT 'JSON array of {site_id,site_name,client_name,rate_increase,notes}',

  -- OT vs Budget
  `ot_budgeted`       decimal(10,2)    DEFAULT 0,
  `ot_actual`         decimal(10,2)    DEFAULT 0,
  `ot_variance`       decimal(10,2)    DEFAULT NULL COMMENT 'auto: actual minus budgeted',

  -- Manager Performance Scores
  `score_karan`       tinyint(2)       DEFAULT 0,
  `score_karan_notes` text             DEFAULT NULL,
  `score_harry`       tinyint(2)       DEFAULT 0,
  `score_harry_notes` text             DEFAULT NULL,
  `score_jatinder`    tinyint(2)       DEFAULT 0,
  `score_jatinder_notes` text          DEFAULT NULL,
  `score_michael`     tinyint(2)       DEFAULT 0,
  `score_michael_notes` text           DEFAULT NULL,

  -- Escalations & Investigations
  `open_investigations`     smallint(5) UNSIGNED DEFAULT 0,
  `investigation_desc`      text        DEFAULT NULL,
  `escalations_received`    smallint(5) UNSIGNED DEFAULT 0,
  `escalations_from_summary` text       DEFAULT NULL,

  -- Review / workflow
  `submitted_at`     datetime         DEFAULT NULL,
  `reviewer_type`    varchar(20)      DEFAULT NULL,
  `reviewer_id`      int(11)          DEFAULT NULL,
  `reviewer_name`    varchar(200)     DEFAULT NULL,
  `review_comment`   text             DEFAULT NULL,
  `reviewed_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_week` (`week_ending`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;