-- Demo data for Payslips tab
-- This creates time_clock_aggregates and payslips with realistic data

-- Create time clock aggregates for multiple employees with different pay periods
-- Using existing user_ids and org_id from the database

-- Employee 1: Bhaskaran Chetty - Recent pay period (Nov 1-15, 2025)
INSERT INTO time_clock_aggregates (
    time_clock_aggregate_id,
    org_id,
    user_id,
    start_date,
    end_date,
    total_duration_minutes,
    total_net_minutes,
    total_overtime_minutes,
    total_regular_minutes,
    total_payable_minutes,
    total_days_worked,
    created_at,
    isdeleted,
    updatedat
) VALUES (
    gen_random_uuid(),
    '0194a8b2-1234-7123-8123-123456789abc', -- Cybex PTE LTD
    '0194a8b2-1111-7123-8123-123456789ac0', -- Bhaskaran Chetty
    '2025-11-01',
    '2025-11-15',
    6960, -- 116 hours (14.5 days * 8 hours/day)
    6600, -- Net time after breaks
    240,  -- 4 hours overtime
    6360, -- Regular time
    6600, -- Payable minutes
    11,   -- Days worked
    now(),
    false,
    now()
);

-- Employee 1: Bhaskaran Chetty - Earlier pay period (Oct 16-31, 2025)
INSERT INTO time_clock_aggregates (
    time_clock_aggregate_id,
    org_id,
    user_id,
    start_date,
    end_date,
    total_duration_minutes,
    total_net_minutes,
    total_overtime_minutes,
    total_regular_minutes,
    total_payable_minutes,
    total_days_worked,
    created_at,
    isdeleted,
    updatedat
) VALUES (
    gen_random_uuid(),
    '0194a8b2-1234-7123-8123-123456789abc',
    '0194a8b2-1111-7123-8123-123456789ac0',
    '2025-10-16',
    '2025-10-31',
    7200, -- 120 hours (15 days * 8 hours/day)
    6840, -- Net time after breaks
    360,  -- 6 hours overtime
    6480, -- Regular time
    6840, -- Payable minutes
    12,   -- Days worked
    now(),
    false,
    now()
);

-- Employee 2: Roma S - Recent pay period (Nov 1-15, 2025)
INSERT INTO time_clock_aggregates (
    time_clock_aggregate_id,
    org_id,
    user_id,
    start_date,
    end_date,
    total_duration_minutes,
    total_net_minutes,
    total_overtime_minutes,
    total_regular_minutes,
    total_payable_minutes,
    total_days_worked,
    created_at,
    isdeleted,
    updatedat
) VALUES (
    gen_random_uuid(),
    '0194a8b2-1234-7123-8123-123456789abc',
    '0194a8b2-4445-7123-8123-123456789ac3', -- Roma S
    '2025-11-01',
    '2025-11-15',
    6480, -- 108 hours (13.5 days * 8 hours/day)
    6120, -- Net time after breaks
    180,  -- 3 hours overtime
    5940, -- Regular time
    6120, -- Payable minutes
    10,   -- Days worked
    now(),
    false,
    now()
);

-- Employee 3: Jagath - Recent pay period (Nov 1-15, 2025)
INSERT INTO time_clock_aggregates (
    time_clock_aggregate_id,
    org_id,
    user_id,
    start_date,
    end_date,
    total_duration_minutes,
    total_net_minutes,
    total_overtime_minutes,
    total_regular_minutes,
    total_payable_minutes,
    total_days_worked,
    created_at,
    isdeleted,
    updatedat
) VALUES (
    gen_random_uuid(),
    '0194a8b2-1234-7123-8123-123456789abc',
    '0194a8b2-4444-7123-8123-123456789ac3', -- Jagath
    '2025-11-01',
    '2025-11-15',
    7440, -- 124 hours (15.5 days * 8 hours/day)
    7080, -- Net time after breaks
    480,  -- 8 hours overtime
    6600, -- Regular time
    7080, -- Payable minutes
    13,   -- Days worked
    now(),
    false,
    now()
);

-- Employee 2: Roma S - Earlier pay period (Oct 16-31, 2025)
INSERT INTO time_clock_aggregates (
    time_clock_aggregate_id,
    org_id,
    user_id,
    start_date,
    end_date,
    total_duration_minutes,
    total_net_minutes,
    total_overtime_minutes,
    total_regular_minutes,
    total_payable_minutes,
    total_days_worked,
    created_at,
    isdeleted,
    updatedat
) VALUES (
    gen_random_uuid(),
    '0194a8b2-1234-7123-8123-123456789abc',
    '0194a8b2-4445-7123-8123-123456789ac3',
    '2025-10-16',
    '2025-10-31',
    6720, -- 112 hours (14 days * 8 hours/day)
    6360, -- Net time after breaks
    300,  -- 5 hours overtime
    6060, -- Regular time
    6360, -- Payable minutes
    11,   -- Days worked
    now(),
    false,
    now()
);

-- Now create corresponding payslips for each time_clock_aggregate
-- We need to reference the time_clock_aggregate_id we just created

-- Payslip 1: Bhaskaran Chetty - Nov 1-15, 2025
-- Gross: 110 hours * $25/hr = $2,750
-- Deductions: Tax + FNPF
INSERT INTO payslips (
    payslip_id,
    user_id,
    time_clock_aggregate_id,
    gross_pay,
    net_pay,
    org_id,
    isdeleted,
    updatedat
)
SELECT
    gen_random_uuid(),
    '0194a8b2-1111-7123-8123-123456789ac0',
    time_clock_aggregate_id,
    2750.00,
    2200.00, -- After deductions
    '0194a8b2-1234-7123-8123-123456789abc',
    false,
    now()
FROM time_clock_aggregates
WHERE user_id = '0194a8b2-1111-7123-8123-123456789ac0'
  AND start_date = '2025-11-01'
  AND end_date = '2025-11-15'
  AND isdeleted = false;

-- Payslip 2: Bhaskaran Chetty - Oct 16-31, 2025
INSERT INTO payslips (
    payslip_id,
    user_id,
    time_clock_aggregate_id,
    gross_pay,
    net_pay,
    org_id,
    isdeleted,
    updatedat
)
SELECT
    gen_random_uuid(),
    '0194a8b2-1111-7123-8123-123456789ac0',
    time_clock_aggregate_id,
    2850.00,
    2280.00,
    '0194a8b2-1234-7123-8123-123456789abc',
    false,
    now()
FROM time_clock_aggregates
WHERE user_id = '0194a8b2-1111-7123-8123-123456789ac0'
  AND start_date = '2025-10-16'
  AND end_date = '2025-10-31'
  AND isdeleted = false;

-- Payslip 3: Roma S - Nov 1-15, 2025
-- Gross: 102 hours * $22/hr = $2,244
INSERT INTO payslips (
    payslip_id,
    user_id,
    time_clock_aggregate_id,
    gross_pay,
    net_pay,
    org_id,
    isdeleted,
    updatedat
)
SELECT
    gen_random_uuid(),
    '0194a8b2-4445-7123-8123-123456789ac3',
    time_clock_aggregate_id,
    2244.00,
    1795.20,
    '0194a8b2-1234-7123-8123-123456789abc',
    false,
    now()
FROM time_clock_aggregates
WHERE user_id = '0194a8b2-4445-7123-8123-123456789ac3'
  AND start_date = '2025-11-01'
  AND end_date = '2025-11-15'
  AND isdeleted = false;

-- Payslip 4: Jagath - Nov 1-15, 2025
-- Gross: 118 hours * $30/hr = $3,540
INSERT INTO payslips (
    payslip_id,
    user_id,
    time_clock_aggregate_id,
    gross_pay,
    net_pay,
    org_id,
    isdeleted,
    updatedat
)
SELECT
    gen_random_uuid(),
    '0194a8b2-4444-7123-8123-123456789ac3',
    time_clock_aggregate_id,
    3540.00,
    2832.00,
    '0194a8b2-1234-7123-8123-123456789abc',
    false,
    now()
FROM time_clock_aggregates
WHERE user_id = '0194a8b2-4444-7123-8123-123456789ac3'
  AND start_date = '2025-11-01'
  AND end_date = '2025-11-15'
  AND isdeleted = false;

-- Payslip 5: Roma S - Oct 16-31, 2025
-- Gross: 106 hours * $22/hr = $2,332
INSERT INTO payslips (
    payslip_id,
    user_id,
    time_clock_aggregate_id,
    gross_pay,
    net_pay,
    org_id,
    isdeleted,
    updatedat
)
SELECT
    gen_random_uuid(),
    '0194a8b2-4445-7123-8123-123456789ac3',
    time_clock_aggregate_id,
    2332.00,
    1865.60,
    '0194a8b2-1234-7123-8123-123456789abc',
    false,
    now()
FROM time_clock_aggregates
WHERE user_id = '0194a8b2-4445-7123-8123-123456789ac3'
  AND start_date = '2025-10-16'
  AND end_date = '2025-10-31'
  AND isdeleted = false;

-- Add payslip deductions for each payslip
-- Tax deductions (assume ~15% tax)
-- FNPF deductions (assume ~8%)

-- Deductions for Payslip 1 (Bhaskaran Nov 1-15)
INSERT INTO payslip_deductions (
    payslip_deduction_id,
    payslip_id,
    deduction_type_id,
    amount,
    isdeleted,
    updatedat
)
SELECT
    gen_random_uuid(),
    p.payslip_id,
    '0199a286-1818-78ab-9722-845f59b9bcef', -- Tax
    412.50, -- 15% of 2750
    false,
    now()
FROM payslips p
JOIN time_clock_aggregates tca ON p.time_clock_aggregate_id = tca.time_clock_aggregate_id
WHERE p.user_id = '0194a8b2-1111-7123-8123-123456789ac0'
  AND tca.start_date = '2025-11-01'
  AND tca.end_date = '2025-11-15'
  AND p.isdeleted = false;

INSERT INTO payslip_deductions (
    payslip_deduction_id,
    payslip_id,
    deduction_type_id,
    amount,
    isdeleted,
    updatedat
)
SELECT
    gen_random_uuid(),
    p.payslip_id,
    '0199a286-1818-7d71-80f4-be423ac706eb', -- FNPF
    137.50, -- 5% of 2750
    false,
    now()
FROM payslips p
JOIN time_clock_aggregates tca ON p.time_clock_aggregate_id = tca.time_clock_aggregate_id
WHERE p.user_id = '0194a8b2-1111-7123-8123-123456789ac0'
  AND tca.start_date = '2025-11-01'
  AND tca.end_date = '2025-11-15'
  AND p.isdeleted = false;

-- Deductions for Payslip 2 (Bhaskaran Oct 16-31)
INSERT INTO payslip_deductions (
    payslip_deduction_id,
    payslip_id,
    deduction_type_id,
    amount,
    isdeleted,
    updatedat
)
SELECT
    gen_random_uuid(),
    p.payslip_id,
    '0199a286-1818-78ab-9722-845f59b9bcef', -- Tax
    427.50, -- 15% of 2850
    false,
    now()
FROM payslips p
JOIN time_clock_aggregates tca ON p.time_clock_aggregate_id = tca.time_clock_aggregate_id
WHERE p.user_id = '0194a8b2-1111-7123-8123-123456789ac0'
  AND tca.start_date = '2025-10-16'
  AND tca.end_date = '2025-10-31'
  AND p.isdeleted = false;

INSERT INTO payslip_deductions (
    payslip_deduction_id,
    payslip_id,
    deduction_type_id,
    amount,
    isdeleted,
    updatedat
)
SELECT
    gen_random_uuid(),
    p.payslip_id,
    '0199a286-1818-7d71-80f4-be423ac706eb', -- FNPF
    142.50, -- 5% of 2850
    false,
    now()
FROM payslips p
JOIN time_clock_aggregates tca ON p.time_clock_aggregate_id = tca.time_clock_aggregate_id
WHERE p.user_id = '0194a8b2-1111-7123-8123-123456789ac0'
  AND tca.start_date = '2025-10-16'
  AND tca.end_date = '2025-10-31'
  AND p.isdeleted = false;

-- Deductions for Payslip 3 (Roma Nov 1-15)
INSERT INTO payslip_deductions (
    payslip_deduction_id,
    payslip_id,
    deduction_type_id,
    amount,
    isdeleted,
    updatedat
)
SELECT
    gen_random_uuid(),
    p.payslip_id,
    '0199a286-1818-78ab-9722-845f59b9bcef', -- Tax
    336.60, -- 15% of 2244
    false,
    now()
FROM payslips p
JOIN time_clock_aggregates tca ON p.time_clock_aggregate_id = tca.time_clock_aggregate_id
WHERE p.user_id = '0194a8b2-4445-7123-8123-123456789ac3'
  AND tca.start_date = '2025-11-01'
  AND tca.end_date = '2025-11-15'
  AND p.isdeleted = false;

INSERT INTO payslip_deductions (
    payslip_deduction_id,
    payslip_id,
    deduction_type_id,
    amount,
    isdeleted,
    updatedat
)
SELECT
    gen_random_uuid(),
    p.payslip_id,
    '0199a286-1818-7d71-80f4-be423ac706eb', -- FNPF
    112.20, -- 5% of 2244
    false,
    now()
FROM payslips p
JOIN time_clock_aggregates tca ON p.time_clock_aggregate_id = tca.time_clock_aggregate_id
WHERE p.user_id = '0194a8b2-4445-7123-8123-123456789ac3'
  AND tca.start_date = '2025-11-01'
  AND tca.end_date = '2025-11-15'
  AND p.isdeleted = false;

-- Deductions for Payslip 4 (Jagath Nov 1-15)
INSERT INTO payslip_deductions (
    payslip_deduction_id,
    payslip_id,
    deduction_type_id,
    amount,
    isdeleted,
    updatedat
)
SELECT
    gen_random_uuid(),
    p.payslip_id,
    '0199a286-1818-78ab-9722-845f59b9bcef', -- Tax
    531.00, -- 15% of 3540
    false,
    now()
FROM payslips p
JOIN time_clock_aggregates tca ON p.time_clock_aggregate_id = tca.time_clock_aggregate_id
WHERE p.user_id = '0194a8b2-4444-7123-8123-123456789ac3'
  AND tca.start_date = '2025-11-01'
  AND tca.end_date = '2025-11-15'
  AND p.isdeleted = false;

INSERT INTO payslip_deductions (
    payslip_deduction_id,
    payslip_id,
    deduction_type_id,
    amount,
    isdeleted,
    updatedat
)
SELECT
    gen_random_uuid(),
    p.payslip_id,
    '0199a286-1818-7d71-80f4-be423ac706eb', -- FNPF
    177.00, -- 5% of 3540
    false,
    now()
FROM payslips p
JOIN time_clock_aggregates tca ON p.time_clock_aggregate_id = tca.time_clock_aggregate_id
WHERE p.user_id = '0194a8b2-4444-7123-8123-123456789ac3'
  AND tca.start_date = '2025-11-01'
  AND tca.end_date = '2025-11-15'
  AND p.isdeleted = false;

-- Deductions for Payslip 5 (Roma Oct 16-31)
INSERT INTO payslip_deductions (
    payslip_deduction_id,
    payslip_id,
    deduction_type_id,
    amount,
    isdeleted,
    updatedat
)
SELECT
    gen_random_uuid(),
    p.payslip_id,
    '0199a286-1818-78ab-9722-845f59b9bcef', -- Tax
    349.80, -- 15% of 2332
    false,
    now()
FROM payslips p
JOIN time_clock_aggregates tca ON p.time_clock_aggregate_id = tca.time_clock_aggregate_id
WHERE p.user_id = '0194a8b2-4445-7123-8123-123456789ac3'
  AND tca.start_date = '2025-10-16'
  AND tca.end_date = '2025-10-31'
  AND p.isdeleted = false;

INSERT INTO payslip_deductions (
    payslip_deduction_id,
    payslip_id,
    deduction_type_id,
    amount,
    isdeleted,
    updatedat
)
SELECT
    gen_random_uuid(),
    p.payslip_id,
    '0199a286-1818-7d71-80f4-be423ac706eb', -- FNPF
    116.60, -- 5% of 2332
    false,
    now()
FROM payslips p
JOIN time_clock_aggregates tca ON p.time_clock_aggregate_id = tca.time_clock_aggregate_id
WHERE p.user_id = '0194a8b2-4445-7123-8123-123456789ac3'
  AND tca.start_date = '2025-10-16'
  AND tca.end_date = '2025-10-31'
  AND p.isdeleted = false;

-- Verify the data was inserted
SELECT 
    u.full_name,
    tca.start_date,
    tca.end_date,
    tca.total_days_worked,
    (tca.total_payable_minutes / 60.0) as total_hours,
    p.gross_pay,
    p.net_pay,
    p.gross_pay - p.net_pay as total_deductions
FROM payslips p
JOIN users u ON p.user_id = u.user_id
JOIN time_clock_aggregates tca ON p.time_clock_aggregate_id = tca.time_clock_aggregate_id
WHERE p.isdeleted = false
ORDER BY tca.start_date DESC, u.full_name;
