-- 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
    exp_id,
    warpSkyfile.skycell_id,
    warpSkyfile.tess_id,
    MAX(diffSkyfile.diff_id) AS diff_id
FROM rawExp
JOIN chipRun USING(exp_id, tess_id)
JOIN camRun USING(chip_id, tess_id)
JOIN fakeRun USING(cam_id, tess_id)
JOIN warpRun USING(fake_id, tess_id)
JOIN warpSkyCellMap USING(warp_id, tess_id)
JOIN warpSkyfile USING(warp_id, skycell_id, tess_id)
JOIN diffInputSkyfile
    ON diffInputSkyfile.warp_id = warpSkyfile.warp_id
    AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id
    AND diffInputSkyfile.tess_id = warpSkyfile.tess_id
    AND diffInputSkyfile.template = 0 -- selecting inputs only
JOIN diffSkyfile USING(diff_id)
WHERE
    diffSkyfile.fault = 0
-----    AND warpSkyfile.good_frac >= 0.5
-- magictool_definebyquery_temp_insert_groupby.sql
GROUP BY
    exp_id,
    warpSkyfile.skycell_id
;


-- magictool_definebyquery_select_part1.sql
-- This is part 1 of 2 of a query to get a list of exposures on which magic may be performed
-- After this follows magictool_definebyquery_select_part2.sql
SELECT
    exp_id,
    filter,
    num_todo,
    num_done
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, tess_id)
    JOIN camRun USING(chip_id, tess_id)
    JOIN fakeRun USING(cam_id, tess_id)
    JOIN warpRun USING(fake_id, tess_id)
    JOIN warpSkyCellMap USING(warp_id, tess_id)
    JOIN warpSkyfile USING(warp_id, skycell_id, tess_id)
    WHERE
        warpSkyfile.ignored = 0
        AND warpRun.state = 'stop'
    -- INSERT HERE any additional restrictions (e.g., exp_id, warpSkyfile.good_frac)
-----        AND warpSkyfile.good_frac > 0.5
-- INSERT HERE magictool_definebyquery_select_part2.sql
-- magictool_definebyquery_select_part2.sql
-- This is part 2 of 2 of a query to get a list of exposures on which magic may be performed
-- This follows magictool_definebyquery_select_part1.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
;

-- 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
;
