-- =====================================================================
-- FCQC HR Master Data Seed (idempotent)
-- Baseline: org-unit tree, sections, designations, shifts, and a
-- factory geofence used for the strict GPS attendance test.
-- =====================================================================

-- ----------------------------------------------------------------
-- Shifts
-- ----------------------------------------------------------------
INSERT INTO shifts (company_id, name, code, start_time, end_time, grace_minutes, break_minutes, overtime_after_minutes, working_hours, is_night, color, status)
SELECT 1, 'General', 'GEN', '08:00:00', '17:00:00', 15, 60, 60, 8, 0, '#10b981', 'active'
WHERE NOT EXISTS (SELECT 1 FROM shifts WHERE company_id = 1 AND name = 'General');

INSERT INTO shifts (company_id, name, code, start_time, end_time, grace_minutes, break_minutes, overtime_after_minutes, working_hours, is_night, color, status)
SELECT 1, 'Morning', 'MOR', '06:00:00', '14:00:00', 10, 30, 60, 8, 0, '#f59e0b', 'active'
WHERE NOT EXISTS (SELECT 1 FROM shifts WHERE company_id = 1 AND name = 'Morning');

INSERT INTO shifts (company_id, name, code, start_time, end_time, grace_minutes, break_minutes, overtime_after_minutes, working_hours, is_night, color, status)
SELECT 1, 'Evening', 'EVE', '14:00:00', '22:00:00', 10, 30, 60, 8, 1, '#6366f1', 'active'
WHERE NOT EXISTS (SELECT 1 FROM shifts WHERE company_id = 1 AND name = 'Evening');

-- ----------------------------------------------------------------
-- Org unit tree: Business Unit -> Factory (wraps master_contractors)
-- ----------------------------------------------------------------
INSERT INTO hr_org_units (company_id, parent_id, type, name, code, contractor_id, status)
SELECT 1, NULL, 'business_unit', 'FCQC Corporate', 'FCQC', NULL, 'active'
WHERE NOT EXISTS (SELECT 1 FROM hr_org_units WHERE company_id = 1 AND type = 'business_unit' AND name = 'FCQC Corporate');

INSERT INTO hr_org_units (company_id, parent_id, type, name, code, contractor_id, status)
SELECT 1,
       (SELECT id FROM hr_org_units WHERE company_id = 1 AND type = 'business_unit' AND name = 'FCQC Corporate' LIMIT 1),
       'factory', mc.contractor_name, mc.contractor_code, mc.id, 'active'
FROM master_contractors mc
WHERE mc.company_id = 1
  AND NOT EXISTS (SELECT 1 FROM hr_org_units u WHERE u.company_id = 1 AND u.type = 'factory' AND u.contractor_id = mc.id);

-- A building under every factory
INSERT INTO hr_org_units (company_id, parent_id, type, name, code, status)
SELECT 1, f.id, 'building', CONCAT(f.name, ' — Main Building'), CONCAT(f.code, '-B1'), 'active'
FROM hr_org_units f
WHERE f.company_id = 1 AND f.type = 'factory'
  AND NOT EXISTS (SELECT 1 FROM hr_org_units b WHERE b.company_id = 1 AND b.type = 'building' AND b.parent_id = f.id);

-- Floors under the first factory's building (for the demo location tree)
INSERT INTO hr_org_units (company_id, parent_id, type, name, code, status)
SELECT 1, b.id, 'floor', 'Floor 1', CONCAT(COALESCE(f.code, 'F'), '-FL1'), 'active'
FROM hr_org_units b JOIN hr_org_units f ON f.id = b.parent_id
WHERE b.company_id = 1 AND b.type = 'building' AND f.id = (SELECT MIN(id) FROM hr_org_units WHERE company_id = 1 AND type = 'factory')
  AND NOT EXISTS (SELECT 1 FROM hr_org_units fl WHERE fl.company_id = 1 AND fl.type = 'floor' AND fl.parent_id = b.id AND fl.name = 'Floor 1');

INSERT INTO hr_org_units (company_id, parent_id, type, name, code, status)
SELECT 1, b.id, 'floor', 'Floor 2', CONCAT(COALESCE(f.code, 'F'), '-FL2'), 'active'
FROM hr_org_units b JOIN hr_org_units f ON f.id = b.parent_id
WHERE b.company_id = 1 AND b.type = 'building' AND f.id = (SELECT MIN(id) FROM hr_org_units WHERE company_id = 1 AND type = 'factory')
  AND NOT EXISTS (SELECT 1 FROM hr_org_units fl WHERE fl.company_id = 1 AND fl.type = 'floor' AND fl.parent_id = b.id AND fl.name = 'Floor 2');

-- ----------------------------------------------------------------
-- Factory geofence (Dhaka factory 1) — used by strict GPS enforcement
-- ----------------------------------------------------------------
UPDATE master_contractors SET geofence_lat = 23.8103, geofence_lng = 90.4125, geofence_radius = 500 WHERE id = 1;
UPDATE hr_org_units SET geofence_lat = 23.8103, geofence_lng = 90.4125, geofence_radius = 500 WHERE company_id = 1 AND type = 'factory' AND contractor_id = 1;

-- ----------------------------------------------------------------
-- Sections
-- ----------------------------------------------------------------
INSERT INTO hr_sections (company_id, department_id, name, code, status)
SELECT 1, d.id, 'Cutting Floor', 'CUT', 'active'
FROM master_departments d WHERE d.code = 'CUT'
  AND NOT EXISTS (SELECT 1 FROM hr_sections s WHERE s.department_id = d.id AND s.name = 'Cutting Floor');

INSERT INTO hr_sections (company_id, department_id, name, code, status)
SELECT 1, d.id, 'Line 1 - Sewing', 'SEW-L1', 'active'
FROM master_departments d WHERE d.code = 'SEW'
  AND NOT EXISTS (SELECT 1 FROM hr_sections s WHERE s.department_id = d.id AND s.name = 'Line 1 - Sewing');

INSERT INTO hr_sections (company_id, department_id, name, code, status)
SELECT 1, d.id, 'Line 2 - Sewing', 'SEW-L2', 'active'
FROM master_departments d WHERE d.code = 'SEW'
  AND NOT EXISTS (SELECT 1 FROM hr_sections s WHERE s.department_id = d.id AND s.name = 'Line 2 - Sewing');

INSERT INTO hr_sections (company_id, department_id, name, code, status)
SELECT 1, d.id, 'Finishing Floor', 'FIN', 'active'
FROM master_departments d WHERE d.code = 'FIN'
  AND NOT EXISTS (SELECT 1 FROM hr_sections s WHERE s.department_id = d.id AND s.name = 'Finishing Floor');

INSERT INTO hr_sections (company_id, department_id, name, code, status)
SELECT 1, d.id, 'Packing Floor', 'PKG', 'active'
FROM master_departments d WHERE d.code = 'PKG'
  AND NOT EXISTS (SELECT 1 FROM hr_sections s WHERE s.department_id = d.id AND s.name = 'Packing Floor');

INSERT INTO hr_sections (company_id, department_id, name, code, status)
SELECT 1, d.id, 'In-Line Quality', 'QA', 'active'
FROM master_departments d WHERE d.code = 'QA'
  AND NOT EXISTS (SELECT 1 FROM hr_sections s WHERE s.department_id = d.id AND s.name = 'In-Line Quality');

-- ----------------------------------------------------------------
-- Designations
-- ----------------------------------------------------------------
INSERT INTO designations (company_id, department_id, name, code, grade, status)
SELECT 1, d.id, 'Sewing Operator', 'SEW-OP', 'A', 'active'
FROM master_departments d WHERE d.code = 'SEW'
  AND NOT EXISTS (SELECT 1 FROM designations x WHERE x.name = 'Sewing Operator' AND x.company_id = 1);

INSERT INTO designations (company_id, department_id, name, code, grade, status)
SELECT 1, d.id, 'Senior Operator', 'SEW-SOP', 'B', 'active'
FROM master_departments d WHERE d.code = 'SEW'
  AND NOT EXISTS (SELECT 1 FROM designations x WHERE x.name = 'Senior Operator' AND x.company_id = 1);

INSERT INTO designations (company_id, department_id, name, code, grade, status)
SELECT 1, d.id, 'Line Leader', 'SEW-LL', 'C', 'active'
FROM master_departments d WHERE d.code = 'SEW'
  AND NOT EXISTS (SELECT 1 FROM designations x WHERE x.name = 'Line Leader' AND x.company_id = 1);

INSERT INTO designations (company_id, department_id, name, code, grade, status)
SELECT 1, d.id, 'Supervisor', 'PROD-SUP', 'C', 'active'
FROM master_departments d WHERE d.code = 'PROD'
  AND NOT EXISTS (SELECT 1 FROM designations x WHERE x.name = 'Supervisor' AND x.company_id = 1);

INSERT INTO designations (company_id, department_id, name, code, grade, status)
SELECT 1, d.id, 'Cutting Master', 'CUT-M', 'C', 'active'
FROM master_departments d WHERE d.code = 'CUT'
  AND NOT EXISTS (SELECT 1 FROM designations x WHERE x.name = 'Cutting Master' AND x.company_id = 1);

INSERT INTO designations (company_id, department_id, name, code, grade, status)
SELECT 1, d.id, 'Quality Inspector', 'QA-INSP', 'C', 'active'
FROM master_departments d WHERE d.code = 'QA'
  AND NOT EXISTS (SELECT 1 FROM designations x WHERE x.name = 'Quality Inspector' AND x.company_id = 1);

INSERT INTO designations (company_id, department_id, name, code, grade, status)
SELECT 1, d.id, 'Executive - HR', 'HR-EXE', 'D', 'active'
FROM master_departments d WHERE d.code = 'HR'
  AND NOT EXISTS (SELECT 1 FROM designations x WHERE x.name = 'Executive - HR' AND x.company_id = 1);

INSERT INTO designations (company_id, department_id, name, code, grade, status)
SELECT 1, d.id, 'Manager - HR', 'HR-MGR', 'E', 'active'
FROM master_departments d WHERE d.code = 'HR'
  AND NOT EXISTS (SELECT 1 FROM designations x WHERE x.name = 'Manager - HR' AND x.company_id = 1);

INSERT INTO designations (company_id, department_id, name, code, grade, status)
SELECT 1, d.id, 'Plant Manager', 'PROD-PM', 'F', 'active'
FROM master_departments d WHERE d.code = 'PROD'
  AND NOT EXISTS (SELECT 1 FROM designations x WHERE x.name = 'Plant Manager' AND x.company_id = 1);
