-- Get a list of exposures on which magic may be performed
SELECT DISTINCT
    exp_id,
    filter,
    num_todo,
    num_done,
    MAX(magic_id)
FROM (
    -- Number of skycells as a function of exposure
    SELECT
        exp_id,
        filter,
        COUNT(DISTINCT warpSkyfile.tess_id,warpSkyfile.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)
    JOIN diffInputSkyfile USING(warp_id,skycell_id)
    JOIN diffRun USING(diff_id)
    WHERE
        warpSkyfile.ignored = 0
        -- magicSkycellNums WHERE hook %s
    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)
