-- This is a sequence of SQL for searching for exposures on which
-- magic may be run, and populating the run.  This file is provided as
-- a convenience, and is intended solely for playing around and
-- testing.  Updates here will not affect the operation of magictool.
-- Conversely, it may not be up to date with the actual queries used
-- by magictool.

--------------------------------------------------------------------
-- NOTE: THIS FILE IS NOT USED BY magictool
-- NOTE : this file contains examples of tess_id as a join qualifier which are wrong
--------------------------------------------------------------------

-- magictool_definebyquery_temp_create.sql
CREATE TEMPORARY TABLE magicBestDiffs (
exp_id BIGINT,
skycell_id VARCHAR(64),
tess_id VARCHAR(64),
diff_id BIGINT,
PRIMARY KEY(exp_id, skycell_id, tess_id)
) ENGINE=MEMORY;



-- magictool_definebyquery_temp_insert.sql
-- List of best differences for each exposure
INSERT INTO magicBestDiffs
SELECT
    rawExp.exp_id,
    diffInputSkyfile.skycell_id,
    diffInputSkyfile.tess_id,
    MAX(diffSkyfile.diff_id) AS diff_id
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
    ON diffInputSkyfile.warp_id = warpSkyfile.warp_id
    AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id
    AND diffInputSkyfile.template = 0 -- selecting inputs only
JOIN diffRun USING(diff_id)
JOIN diffSkyfile USING(diff_id)
WHERE
    diffSkyfile.fault = 0
-- WHERE hook %s
AND exp_id = 36475
AND diffRun.label = 'magic_2008-11-12'
GROUP BY
    exp_id,
    diffInputSkyfile.skycell_id,
    diffInputSkyfile.tess_id
;


-- magictool_definebyquery_select.sql
-- Get a list of exposures on which magic may be performed
SELECT DISTINCT
    exp_id,
    filter,
    num_todo,
    num_done,
    MAX(magic_id) AS best_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
AND exp_id = 36475
AND diffRun.label = 'magic_2008-11-12'
    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
    magic_id IS NULL
;

-- magictool_definebyquery_insert.sql
-- Insert the best list of diffs as magic inputs
INSERT INTO magicInputSkyfile
SELECT
    @MAGIC_ID@, -- Update this with the appropriate magic_id
    diff_id,
    CONCAT(tess_id, '.', skycell_id) AS node
FROM magicBestDiffs
;
