-- Script de Carga de Datos de Prueba para Conceptos de Nómina (PostgreSQL)
-- Compañía Demo ID: d1e2f3a4-b5c6-4d7e-8f9a-0b1c2d3e4f5a

-- 1. Insertar Conceptos de Nómina Generales
INSERT INTO hr.payroll_concepts (id, company_id, name, type, calculation_type, default_value, is_active)
VALUES
  ('cc000001-0000-4000-8000-000000000001', 'd1e2f3a4-b5c6-4d7e-8f9a-0b1c2d3e4f5a', 'Bono Alimentación', 'earnings', 'fixed', 150.00, TRUE),
  ('cc000002-0000-4000-8000-000000000002', 'd1e2f3a4-b5c6-4d7e-8f9a-0b1c2d3e4f5a', 'Bono Asistencia', 'earnings', 'fixed', 50.00, TRUE),
  ('cc000003-0000-4000-8000-000000000003', 'd1e2f3a4-b5c6-4d7e-8f9a-0b1c2d3e4f5a', 'Seguro Social S.S.O', 'deductions', 'percentage', 4.00, TRUE),
  ('cc000004-0000-4000-8000-000000000004', 'd1e2f3a4-b5c6-4d7e-8f9a-0b1c2d3e4f5a', 'Ahorro Habitacional FAOV', 'deductions', 'percentage', 1.00, TRUE)
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name,
    type = EXCLUDED.type,
    calculation_type = EXCLUDED.calculation_type,
    default_value = EXCLUDED.default_value,
    is_active = EXCLUDED.is_active,
    updated_at = CURRENT_TIMESTAMP;

-- 2. Asignar Conceptos a Empleados (Carlos Mendoza y María Gómez)
-- Se eliminan relaciones previas para evitar duplicidad o conflictos de llave única
DELETE FROM hr.employee_concepts 
WHERE employee_id IN ('af000001-0000-4000-8000-000000000001', 'af000002-0000-4000-8000-000000000002');

-- Asignar a Carlos Mendoza (si existe en BD)
INSERT INTO hr.employee_concepts (id, employee_id, concept_id, value, is_active)
SELECT gen_random_uuid(), 'af000001-0000-4000-8000-000000000001', 'cc000001-0000-4000-8000-000000000001', 150.00, TRUE
WHERE EXISTS (SELECT 1 FROM hr.employees WHERE id = 'af000001-0000-4000-8000-000000000001');

INSERT INTO hr.employee_concepts (id, employee_id, concept_id, value, is_active)
SELECT gen_random_uuid(), 'af000001-0000-4000-8000-000000000001', 'cc000003-0000-4000-8000-000000000003', 4.00, TRUE
WHERE EXISTS (SELECT 1 FROM hr.employees WHERE id = 'af000001-0000-4000-8000-000000000001');

-- Asignar a María Gómez (si existe en BD)
INSERT INTO hr.employee_concepts (id, employee_id, concept_id, value, is_active)
SELECT gen_random_uuid(), 'af000002-0000-4000-8000-000000000002', 'cc000001-0000-4000-8000-000000000001', 150.00, TRUE
WHERE EXISTS (SELECT 1 FROM hr.employees WHERE id = 'af000002-0000-4000-8000-000000000002');

INSERT INTO hr.employee_concepts (id, employee_id, concept_id, value, is_active)
SELECT gen_random_uuid(), 'af000002-0000-4000-8000-000000000002', 'cc000002-0000-4000-8000-000000000002', 50.00, TRUE
WHERE EXISTS (SELECT 1 FROM hr.employees WHERE id = 'af000002-0000-4000-8000-000000000002');

INSERT INTO hr.employee_concepts (id, employee_id, concept_id, value, is_active)
SELECT gen_random_uuid(), 'af000002-0000-4000-8000-000000000002', 'cc000003-0000-4000-8000-000000000003', 4.00, TRUE
WHERE EXISTS (SELECT 1 FROM hr.employees WHERE id = 'af000002-0000-4000-8000-000000000002');

INSERT INTO hr.employee_concepts (id, employee_id, concept_id, value, is_active)
SELECT gen_random_uuid(), 'af000002-0000-4000-8000-000000000002', 'cc000004-0000-4000-8000-000000000004', 1.00, TRUE
WHERE EXISTS (SELECT 1 FROM hr.employees WHERE id = 'af000002-0000-4000-8000-000000000002');
