-- ============================================================
-- 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;