-- This is the combination of the two parts of the query to get a list
-- of exposures to be magic-ed

--------------------------------------------------------------------------------
-- THIS FILE IS INTENDED FOR TESTING ONLY!  IT IS NOT USED BY stacktool.
-- CHANGES SHOULD BE MADE TO magictool_definebyquery_select_part1.sql
-- AND magictool_definebyquery_select_part2.sql
--------------------------------------------------------------------------------

-- magictool_definebyquery_select_part1.sql
SELECT
    *
FROM (
    -- Number of skycells as a function of exposure
    SELECT
        exp_id,
        filter,
        COUNT(skycell_id) AS num_todo
    FROM rawExp
    JOIN chipRun USING(exp_id)
    JOIN camRun USING(chip_id)
    JOIN fakeRun USING(cam_id)
    JOIN warpRun USING(fake_id)
    JOIN warpSkyCellMap USING(warp_id)
    JOIN warpSkyfile USING(warp_id, skycell_id)
    WHERE
        warpSkyfile.ignored = 0
        AND warpRun.state = 'stop'
    -- INSERT HERE any additional restrictions (e.g., exp_id, warpSkyfile.good_frac)
-- magictool_definebyquery_select_part2.sql
    GROUP BY
        exp_id
    ) AS magicSkycellNums
JOIN (
    -- Number of completed diffs for an exposure
    SELECT
        exp_id,
        COUNT(diff_id) AS num_done
    FROM magicBestDiffs
    GROUP BY
        exp_id
    ) AS magicDiffNums USING(exp_id)
LEFT JOIN magicRun USING(exp_id)
WHERE
    num_done = num_todo
    AND magicRun.magic_id IS NULL
