"""SQL queries for ACK delivery processing in Snowflake."""

INSERT_DELIVERY_ACKS = """
INSERT INTO TIKTOK_DELIVERY_ACK (sr_version_id, timestamp, ack)
{values}
"""

FETCH_UNACKED_DELIVERY_HISTORY = """
SELECT
    sound_recording_id,
    sound_recording_version_id,
    step_function_execution_id,
    srdh.timestamp
FROM
    ORCHARD_SOUND_RECORDING_DELIVERY_HISTORY srdh
LEFT JOIN
    TIKTOK_DELIVERY_ACK tdc
ON srdh.sound_recording_version_id = tdc.sr_version_id AND srdh.timestamp = tdc.timestamp
WHERE
    srdh.EVENT_TYPE = 'success'
    AND tdc.ACK IS NULL
    AND srdh.sound_recording_version_id NOT IN (
        SELECT sr_version_id FROM TIKTOK_DELIVERY_ACK WHERE ack = 'missing'
    )
    {filters}
ORDER BY srdh.timestamp ASC
{limit_sql}
"""

FETCH_AWAITED_DELIVERY_HISTORY = """
SELECT
    sound_recording_id,
    sound_recording_version_id,
    step_function_execution_id,
    srdh.timestamp
FROM
    ORCHARD_SOUND_RECORDING_DELIVERY_HISTORY srdh
INNER JOIN
    TIKTOK_DELIVERY_ACK tdc
ON srdh.sound_recording_version_id = tdc.sr_version_id AND srdh.timestamp = tdc.timestamp
WHERE
    srdh.EVENT_TYPE = 'success'
    AND tdc.ACK = 'awaited'
    {filters}
ORDER BY srdh.timestamp
{limit_offset}
"""

UPDATE_DELIVERY_ACKS_AS_SUCCESS = """
UPDATE TIKTOK_DELIVERY_ACK
SET ack='success'
WHERE (sr_version_id, timestamp) IN {acks}
"""

UPDATE_DELIVERY_ACKS_AS_MISSING = """
UPDATE TIKTOK_DELIVERY_ACK
SET ack = 'missing'
WHERE (sr_version_id, timestamp) IN {acks}
"""

UPDATE_DELIVERY_ACK_AS_ERROR = """
UPDATE TIKTOK_DELIVERY_ACK
SET ack='error', message={message}
WHERE (sr_version_id, timestamp) = {ack}
"""