SET SQL_MODE = '';
WITH shift_punches AS (
    SELECT 
        COALESCE(sp.shift, em.SHIFT) AS shift_code,
        t.punchingcode,
        CASE 
            WHEN t.datetime < CONCAT(DATE(t.datetime), ' ', s.punch_start_time)
            THEN DATE_SUB(DATE(t.datetime), INTERVAL 1 DAY)
            ELSE DATE(t.datetime)
        END AS shift_date, 
        t.datetime,
        em.EMP_NUMBER AS emp_cd,
        t.unit_cd
    FROM tblt_timesheet t
    LEFT JOIN emp_master em on t.punchingcode = em.CARD_NO 
    LEFT JOIN shift_plan sp ON sp.emp_cd = em.EMP_NUMBER AND t.datetime BETWEEN sp.from_dt AND sp.to_dt AND sp.unit_cd = t.unit_cd
    LEFT JOIN shift_master s ON s.shift_code = COALESCE(sp.shift, em.SHIFT) AND s.unit_cd = t.unit_cd
    WHERE 1=1 AND t.date BETWEEN '2025-05-01' AND '2025-05-24' AND em.EMP_NUMBER = 'E-009' AND t.unit_cd = 101 AND em.OVER_TIME = 'Y'
),

punch_counts AS (
    SELECT 
        sp.punchingcode,
        sp.shift_code,
        sp.shift_date, 
        sp.emp_cd,
        sp.unit_cd,
        COUNT(sp.punchingcode) AS total_punches,
        MIN(sp.datetime) AS in_datetime,
        CASE 
            WHEN COUNT(sp.punchingcode) % 2 = 0 THEN MAX(sp.datetime)
            ELSE NULL
        END AS out_datetime
    FROM shift_punches sp 
    GROUP BY sp.punchingcode, sp.shift_code, sp.shift_date
)

SELECT 
    pc.punchingcode,
    pc.shift_code,
    sm.name AS shift_nm,
    pc.shift_date, 
    pc.total_punches,
    pc.in_datetime,
    pc.out_datetime,
    sm.start_time,
    sm.end_time,
    TIME_TO_SEC(IFNULL(sm.total_shift_time, (SELECT os.time FROM open_shift_break os where pc.shift_code = os.shift  and pc.unit_cd = os.unit_cd and os.pay_per = 1 limit 1))) / 60 as total_shift_time,
    sm.min_over,
    sm.max_over,
    pc.emp_cd,
    em.NAME AS emp_nm,
    hdt1.present_percent AS in_value,
    hdt3.out_present_percent AS out_value,
    -- Calculate Overtime in Minutes
    /*sum(CASE 
        WHEN pc.out_datetime IS NOT NULL AND pc.out_datetime > (if (sm.start_time < sm.end_time, CONCAT(pc.shift_date, ' ', sm.end_time), CONCAT(DATE_ADD(pc.shift_date, INTERVAL 1 DAY), ' ', sm.end_time)))
        THEN TIMESTAMPDIFF(MINUTE, (if (sm.start_time < sm.end_time, CONCAT(pc.shift_date, ' ', sm.end_time), CONCAT(DATE_ADD(pc.shift_date, INTERVAL 1 DAY), ' ', sm.end_time))), pc.out_datetime)
        ELSE 0
    END) AS */
    TIMESTAMPDIFF(MINUTE,em.DUTY_HOUR, TIMEDIFF(pc.out_datetime, pc.in_datetime))
     as overtime,
    -- Calculate Time Difference between In and Out
     sum(CASE 
        WHEN pc.in_datetime IS NOT NULL AND pc.out_datetime IS NOT NULL 
        THEN TIMESTAMPDIFF(MINUTE, pc.in_datetime, pc.out_datetime)
        ELSE NULL
    END) AS worked_minutes,
    -- If shift_date is Sunday, worked time is overtime
     sum(CASE 
        WHEN DAYOFWEEK(pc.shift_date) = 1 AND pc.in_datetime IS NOT NULL AND pc.out_datetime IS NOT NULL
        THEN TIMESTAMPDIFF(MINUTE, pc.in_datetime, pc.out_datetime)
        ELSE 0
    END) AS sunday,
    -- Calculate Holiday Working Minutes
      sum(CASE 
        WHEN h.holiday IS NOT NULL AND pc.in_datetime IS NOT NULL AND pc.out_datetime IS NOT NULL 
        THEN TIMESTAMPDIFF(MINUTE, pc.in_datetime, pc.out_datetime)
        ELSE 0
    END) AS holiday,
    -- Flags for Sunday and Holiday
    CASE 
        WHEN DAYOFWEEK(pc.shift_date) = 1 THEN 'Y' 
        ELSE 'N' 
    END AS is_sunday,
    CASE 
        WHEN h.holiday IS NOT NULL THEN 'Y' 
        ELSE 'N' 
    END AS is_holiday
FROM punch_counts pc
LEFT JOIN shift_master sm ON pc.shift_code = sm.shift_code AND sm.unit_cd = pc.unit_cd
LEFT JOIN emp_master em ON pc.emp_cd = em.EMP_NUMBER
LEFT JOIN hr_holiday_mast h ON pc.shift_date = h.holiday
LEFT JOIN hrm_day_timing hdt1 
    ON hdt1.shift = pc.shift_code AND hdt1.unit_cd = pc.unit_cd
    AND TIME(pc.in_datetime) BETWEEN hdt1.from_time AND hdt1.to_time
    AND hdt1.from_time = (
        SELECT hdt2.from_time
        FROM hrm_day_timing hdt2
        WHERE hdt2.shift = pc.shift_code AND hdt2.unit_cd = pc.unit_cd
        AND TIME(pc.in_datetime) BETWEEN hdt2.from_time AND hdt2.to_time
        ORDER BY ABS(TIMESTAMPDIFF(SECOND, pc.in_datetime, CONCAT(pc.shift_date, ' ', hdt2.from_time))) ASC
        LIMIT 1
    )
LEFT JOIN hrm_day_timing hdt3 
    ON hdt3.shift = pc.shift_code AND hdt3.unit_cd = pc.unit_cd
    AND TIME(pc.out_datetime) BETWEEN hdt3.from_time AND hdt3.to_time
    AND hdt3.from_time = (
        SELECT hdt4.from_time
        FROM hrm_day_timing hdt4
        WHERE hdt4.shift = pc.shift_code AND hdt4.unit_cd = pc.unit_cd
        AND TIME(pc.out_datetime) BETWEEN hdt4.from_time AND hdt4.to_time
        ORDER BY ABS(TIMESTAMPDIFF(SECOND, pc.out_datetime, CONCAT(pc.shift_date, ' ', hdt4.from_time))) ASC
        LIMIT 1
    ) 
WHERE pc.emp_cd is not null
GROUP BY pc.punchingcode, pc.shift_code, pc.shift_date;

i want to add column that display the difference of my next punching in after punching out