Insert Missing Benefits Pay

SET NOCOUNT ON;

DECLARE @FILEKEY INT = 6043;
-- Replace with specific employee's FILEKEY

-- Create temporary table for employee info
CREATE TABLE #EmployeeInfo
(
    FILEKEY INT,
    ACTIVESTATUSEFFDATE DATE,
    FirstValidPeriodStart DATE
);

-- Populate employee info
INSERT INTO #EmployeeInfo
SELECT
    @FILEKEY AS FILEKEY,
    e.ACTIVESTATUSEFFDATE,
    DATEADD(WEEK, -1, 
        DATEADD(DAY, -(DATEPART(WEEKDAY, e.ACTIVESTATUSEFFDATE) - 1), 
            CAST(e.ACTIVESTATUSEFFDATE AS DATE))
    ) AS FirstValidPeriodStart
FROM EMPLOYEES e
WHERE e.FILEKEY = @FILEKEY
    AND e.ACTIVESTATUS = 0;

-- Create temporary table for missing benefit pay
CREATE TABLE #MissingBenefitPay
(
    FILEKEY INT,
    SCHDATE DATE,
    HOURS_MINUTES INT,
    HOURS_ACTUAL REAL,
    RATE REAL,
    WG1 INT,
    WG2 INT,
    WG3 INT,
    WG4 INT,
    WG5 INT,
    BENEFIT INT,
    PAYDESNUM INT,
    CALCULATED_DOLLARS REAL
);

-- Populate missing benefit pay
INSERT INTO #MissingBenefitPay
SELECT
    s.FILEKEY,
    s.SCHDATE,
    s.HOURS AS HOURS_MINUTES,
    s.HOURS / 60.0 AS HOURS_ACTUAL, -- Convert minutes to hours
    s.RATE,
    s.WG1,
    s.WG2,
    s.WG3,
    s.WG4,
    s.WG5,
    s.BENEFIT,
    b.PAYDESNUM,
    (s.HOURS / 60.0) * s.RATE AS CALCULATED_DOLLARS
-- Hours * Rate
FROM #EmployeeInfo ei
    INNER JOIN SCHEDULES s ON ei.FILEKEY = s.FILEKEY
        AND s.SCHDATE >= ei.FirstValidPeriodStart
        AND s.SCHDATE > '2025-04-19'
        AND s.SCHDATE <= CAST(GETDATE() AS DATE)
        AND s.SCHTYPE = 2 -- Only benefit schedules
    INNER JOIN BENEFITS b ON s.BENEFIT = b.NUM
    LEFT JOIN PAYDESHIST p ON s.FILEKEY = p.FILEKEY
        AND s.SCHDATE = p.EVENTDATE
        AND b.PAYDESNUM = p.PAYDESNUM
    LEFT JOIN BENHIST bh ON s.FILEKEY = bh.FILEKEY
        AND s.SCHDATE = bh.EVENTDATE
        AND s.BENEFIT = bh.BENEFIT
WHERE p.FILEKEY IS NULL -- No matching pay record
    AND bh.FILEKEY IS NULL;
-- No matching benefit history record

-- Insert into PAYDESHIST
INSERT INTO PAYDESHIST
    (
    [FILEKEY],
    [EVENTDATE],
    [WG1],
    [WG2],
    [WG3],
    [PAYDESNUM],
    [RATE],
    [HOURS],
    [DOLLARS],
    [WG4],
    [WG5]
    )
SELECT
    FILEKEY,
    SCHDATE,
    WG1,
    WG2,
    WG3,
    PAYDESNUM,
    RATE,
    HOURS_ACTUAL, -- Use converted hours (240/60 = 4.0)
    CALCULATED_DOLLARS,
    WG4,
    WG5
FROM #MissingBenefitPay;

DECLARE @PayDesHistCount INT = @@ROWCOUNT;

-- Insert into BENHIST
INSERT INTO BENHIST
    (
    [FILEKEY],
    [EVENTDATE],
    [BENEFIT],
    [FIRSTUSE],
    [PAYDESNUM],
    [HOURS],
    [DOLLARS],
    [FLAGS]
    )
SELECT
    FILEKEY,
    SCHDATE,
    BENEFIT,
    0 AS FIRSTUSE, -- Default to 0
    PAYDESNUM,
    HOURS_ACTUAL, -- Use converted hours (240/60 = 4.0)
    CALCULATED_DOLLARS,
    2 AS FLAGS
-- Default to 2 as specified
FROM #MissingBenefitPay;

DECLARE @BenHistCount INT = @@ROWCOUNT;

-- Show what was inserted
SELECT
    'Inserted ' + CAST(@PayDesHistCount AS VARCHAR(10)) + ' records into PAYDESHIST and ' + 
    CAST(@BenHistCount AS VARCHAR(10)) + ' records into BENHIST for employee ' + 
    CAST(@FILEKEY AS VARCHAR(10)) AS Result;

-- Clean up temporary tables
DROP TABLE #EmployeeInfo;
DROP TABLE #MissingBenefitPay;

SET NOCOUNT OFF;