SELECT 
    warp_id,
    warp_skyfile_id,
    skycell_id,
    tess_id,
    fake_id,
    state,
    reduction,
    cam_id,
    camera,
    exp_tag,
    workdir,
    magicked
FROM (
    SELECT DISTINCT
        warpSkyCellMap.warp_id,
        warpImfile.warp_skyfile_id,
        warpSkyCellMap.skycell_id,
        warpSkyCellMap.tess_id,
        warpRun.fake_id,
        warpRun.state,
        warpRun.reduction,
        camRun.cam_id,
        rawExp.camera,
        rawExp.exp_tag,
        warpRun.workdir,
        chipRun.magicked
    FROM warpRun
    JOIN warpSkyCellMap
        USING(warp_id)
    JOIN warpImfile
        ON warpSkyCellMap.warp_id = warpImfile.warp_id
        AND warpSkyCellMap.skycell_id = warpImfile.skycell_id
    JOIN fakeRun
        USING(fake_id)
    JOIN camRun
        USING(cam_id)
    JOIN chipRun
        USING(chip_id)
    JOIN chipProcessedImfile
        USING(chip_id)
    JOIN rawExp
        ON chipRun.exp_id = rawExp.exp_id
    LEFT JOIN warpSkyfile
        ON warpRun.warp_id = warpSkyfile.warp_id
        AND warpSkyCellMap.skycell_id = warpSkyfile.skycell_id
        AND warpSkyCellMap.tess_id = warpSkyfile.tess_id
    LEFT JOIN warpMask
        ON warpRun.label = warpMask.label
    WHERE
        warpRun.state = 'new'
        AND warpSkyfile.warp_id IS NULL
        AND warpSkyfile.skycell_id IS NULL
        AND warpSkyfile.tess_id IS NULL
        AND fakeRun.state = 'full'
        AND camRun.state = 'full'
        AND chipRun.state = 'full'
        AND warpMask.label IS NULL
        AND warpSkyCellMap.fault = 0
        -- where hook 1 %s
        -- limit hook 1 %s
UNION
    SELECT
        warpSkyCellMap.warp_id,
        warpImfile.warp_skyfile_id,
        warpSkyCellMap.skycell_id,
        warpSkyCellMap.tess_id,
        warpRun.fake_id,
        warpRun.state,
        warpRun.reduction,
        camRun.cam_id,
        rawExp.camera,
        rawExp.exp_tag,
        warpRun.workdir,
        MIN(chipProcessedImfile.magicked) AS magicked
    FROM warpRun
    JOIN warpImfile USING(warp_id)
    JOIN warpSkyCellMap USING(warp_id, skycell_id)
    JOIN warpSkyfile USING(warp_id, skycell_id)
    JOIN fakeRun USING(fake_id)
    JOIN camRun USING(cam_id)
    JOIN chipRun USING(chip_id)
    JOIN rawExp USING(exp_id)
    LEFT JOIN chipProcessedImfile USING(chip_id, class_id)
    WHERE warpRun.state = 'update'
        AND warpSkyfile.data_state = 'update'
        AND warpSkyfile.fault = 0
        AND camRun.state = 'full'
        AND chipProcessedImfile.data_state = 'full'
        -- if warpSkyfile was magicked previously require inputs to be magicked
        -- this blocks processing until all the chip inputs have been destreaked
        AND (warpSkyfile.magicked = 0 OR chipProcessedImfile.magicked >= 0)
        -- where hook 2 %s
    GROUP BY warp_id, skycell_id
    HAVING COUNT(warpSkyCellMap.class_id) = COUNT(chipProcessedImfile.class_id)
    -- limit hook 2 %s
) as towarped

