WITH RECURSIVE days AS (
    SELECT MIN(DATE(starttime)) AS day
    FROM caretimes
    UNION ALL
    SELECT DATE_ADD(day, INTERVAL 1 DAY)
    FROM days
    WHERE day < (SELECT MAX(DATE(endtime)) FROM caretimes)
)
SELECT
    d.day,
    -- caretime: Differenz starttime-endtime oder 00:00
    LPAD(HOUR(SEC_TO_TIME(
        COALESCE(TIMESTAMPDIFF(SECOND, c.starttime, c.endtime), 0)
    )),2,'0') 
    || ':' || 
    LPAD(MINUTE(SEC_TO_TIME(
        COALESCE(TIMESTAMPDIFF(SECOND, c.starttime, c.endtime), 0)
    )),2,'0') AS caretime,

    -- minus_time: Differenz starttime_minus-endtime_minus oder 00:00
    LPAD(HOUR(SEC_TO_TIME(
        COALESCE(TIMESTAMPDIFF(SECOND, c.starttime_minus, c.endtime_minus), 0)
    )),2,'0') 
    || ':' || 
    LPAD(MINUTE(SEC_TO_TIME(
        COALESCE(TIMESTAMPDIFF(SECOND, c.starttime_minus, c.endtime_minus), 0)
    )),2,'0') AS minus_time,

    -- effective_caretime_father: caretime minus minus_time oder 00:00, auf volle Minuten
    LPAD(HOUR(SEC_TO_TIME(
        ROUND(
            COALESCE(TIMESTAMPDIFF(SECOND, c.starttime, c.endtime)
                - IF(
                    c.starttime_minus IS NOT NULL AND c.endtime_minus IS NOT NULL,
                    TIMESTAMPDIFF(SECOND, c.starttime_minus, c.endtime_minus),
                    0
                ), 0
            ) / 60
        ) * 60
    )),2,'0') 
    || ':' || 
    LPAD(MINUTE(SEC_TO_TIME(
        ROUND(
            COALESCE(TIMESTAMPDIFF(SECOND, c.starttime, c.endtime)
                - IF(
                    c.starttime_minus IS NOT NULL AND c.endtime_minus IS NOT NULL,
                    TIMESTAMPDIFF(SECOND, c.starttime_minus, c.endtime_minus),
                    0
                ), 0
            ) / 60
        ) * 60
    )),2,'0') AS effective_caretime_father,

    -- effective_caretime_mother: 24:00 für fehlende Tage oder verbleibende Zeit des Tages, auf volle Minuten
    CASE 
        WHEN c.starttime IS NULL THEN '24:00'
        ELSE LPAD(HOUR(SEC_TO_TIME(
                ROUND(
                    (24*3600 - (
                        TIMESTAMPDIFF(SECOND, c.starttime, c.endtime)
                        - IF(
                            c.starttime_minus IS NOT NULL AND c.endtime_minus IS NOT NULL,
                            TIMESTAMPDIFF(SECOND, c.starttime_minus, c.endtime_minus),
                            0
                        )
                    )) / 60
                ) * 60
            )),2,'0') 
            || ':' || 
            LPAD(MINUTE(SEC_TO_TIME(
                ROUND(
                    (24*3600 - (
                        TIMESTAMPDIFF(SECOND, c.starttime, c.endtime)
                        - IF(
                            c.starttime_minus IS NOT NULL AND c.endtime_minus IS NOT NULL,
                            TIMESTAMPDIFF(SECOND, c.starttime_minus, c.endtime_minus),
                            0
                        )
                    )) / 60
                ) * 60
            )),2,'0')
    END AS effective_caretime_mother

FROM days d
LEFT JOIN caretimes c ON DATE(c.starttime) = d.day
ORDER BY d.day;

---


WITH RECURSIVE days AS (
    SELECT MIN(DATE(starttime)) AS day
    FROM caretimes
    UNION ALL
    SELECT DATE_ADD(day, INTERVAL 1 DAY)
    FROM days
    WHERE day < (SELECT MAX(DATE(starttime)) FROM caretimes)
)
SELECT
    d.day,
    c.starttime,
    c.endtime,
    c.starttime_minus,
    c.endtime_minus,

    -- 1. Gesamtdauer von starttime bis endtime
    CASE 
        WHEN c.starttime IS NULL THEN NULL
        ELSE SEC_TO_TIME(TIMESTAMPDIFF(SECOND, c.starttime, c.endtime))
    END AS caretime,

    -- 2. Dauer von starttime_minus bis endtime_minus
    CASE
        WHEN c.starttime_minus IS NULL OR c.endtime_minus IS NULL THEN NULL
        ELSE SEC_TO_TIME(TIMESTAMPDIFF(SECOND, c.starttime_minus, c.endtime_minus))
    END AS minus_time,

    -- 3. Effektive Zeit Vater: caretime minus minus_time, falls vorhanden
    CASE
        WHEN c.starttime IS NULL THEN NULL
        ELSE SEC_TO_TIME(
            TIMESTAMPDIFF(SECOND, c.starttime, c.endtime)
            - IF(
                c.starttime_minus IS NOT NULL AND c.endtime_minus IS NOT NULL,
                TIMESTAMPDIFF(SECOND, c.starttime_minus, c.endtime_minus),
                0
            )
        )
    END AS effective_caretime_father,

    -- 4. Effektive Zeit Mutter: für fehlende Tage 24:00:00, sonst verbleibende Zeit
    CASE 
        WHEN c.starttime IS NULL THEN '24:00:00'
        ELSE SEC_TO_TIME(
            ROUND(
                (24*3600 - (
                    TIMESTAMPDIFF(SECOND, c.starttime, c.endtime)
                    - IF(
                        c.starttime_minus IS NOT NULL AND c.endtime_minus IS NOT NULL,
                        TIMESTAMPDIFF(SECOND, c.starttime_minus, c.endtime_minus),
                        0
                    )
                )) / 60
            ) * 60
        )
    END AS effective_caretime_mother

FROM days d
LEFT JOIN caretimes c ON DATE(c.starttime) = d.day
ORDER BY d.day;

---


WITH RECURSIVE days AS (
    SELECT MIN(DATE(starttime)) AS day
    FROM caretimes
    UNION ALL
    SELECT DATE_ADD(day, INTERVAL 1 DAY)
    FROM days
    WHERE day < (SELECT MAX(DATE(starttime)) FROM caretimes)
)
SELECT
    d.day,
    c.starttime,
    c.endtime,
    c.starttime_minus,
    c.endtime_minus,

    -- 1. Gesamtdauer von starttime bis endtime
    CASE 
        WHEN c.starttime IS NULL THEN NULL
        ELSE SEC_TO_TIME(TIMESTAMPDIFF(SECOND, c.starttime, c.endtime))
    END AS caretime,

    -- 2. Dauer von starttime_minus bis endtime_minus
    CASE
        WHEN c.starttime_minus IS NULL OR c.endtime_minus IS NULL THEN NULL
        ELSE SEC_TO_TIME(TIMESTAMPDIFF(SECOND, c.starttime_minus, c.endtime_minus))
    END AS minus_time,

    -- 3. Effektive Zeit Vater: caretime minus minus_time, auf volle Minuten gerundet
    CASE
        WHEN c.starttime IS NULL THEN NULL
        ELSE SEC_TO_TIME(
            ROUND(
                (
                    TIMESTAMPDIFF(SECOND, c.starttime, c.endtime)
                    - IF(
                        c.starttime_minus IS NOT NULL AND c.endtime_minus IS NOT NULL,
                        TIMESTAMPDIFF(SECOND, c.starttime_minus, c.endtime_minus),
                        0
                    )
                ) / 60
            ) * 60
        )
    END AS effective_caretime_father,

    -- 4. Effektive Zeit Mutter: für fehlende Tage 24:00, sonst verbleibende Zeit auf volle Minuten
    CASE 
        WHEN c.starttime IS NULL THEN '24:00:00'
        ELSE SEC_TO_TIME(
            ROUND(
                (
                    24*3600 - (
                        TIMESTAMPDIFF(SECOND, c.starttime, c.endtime)
                        - IF(
                            c.starttime_minus IS NOT NULL AND c.endtime_minus IS NOT NULL,
                            TIMESTAMPDIFF(SECOND, c.starttime_minus, c.endtime_minus),
                            0
                        )
                    )
                ) / 60
            ) * 60
        )
    END AS effective_caretime_mother

FROM days d
LEFT JOIN caretimes c ON DATE(c.starttime) = d.day
ORDER BY d.day;