-- Get list of warps that can be diffed, with any associated diff,
-- and the best stack to use as a template
-- Warps without an existing diff can be identified by a NULL diff_id
SELECT
    warpsToDiff.warp_id,
    warpsToDiff.skycell_id,
    warpsToDiff.tess_id,
    warpsToDiff.filter,
    warpsToDiff.good_frac,
    warpsToDiff.diff_id,
    warpsToDiff.kind,
    current_stack_id,
    best_stack_id
FROM (
    -- Get list of warps that can be diffed, with any associated diff
    SELECT
        warpSkyfile.warp_id,
        warpSkyfile.skycell_id,
        warpSkyfile.tess_id,
        warpSkyfile.good_frac,
        filter,
        diffInputs.diff_id,
        diffInputs.kind,
        diffTemplates.stack_id AS current_stack_id
    FROM warpSkyfile
    JOIN warpRun USING(warp_id, tess_id)
    JOIN fakeRun USING(fake_id, tess_id)
    JOIN camRun USING(cam_id, tess_id)
    JOIN chipRun USING(chip_id, tess_id)
    JOIN rawExp USING(exp_id, tess_id)
    -- Check if it has an associated diff
    LEFT JOIN diffInputSkyfile AS diffInputs
        ON diffInputs.warp_id = warpSkyfile.warp_id
        AND diffInputs.skycell_id = warpSkyfile.skycell_id
        AND diffInputs.tess_id = warpSkyfile.tess_id
        AND diffInputs.template = 0 -- only join input files
    -- Get the stack_id currently used as a template, if any
    LEFT JOIN diffInputSkyfile AS diffTemplates
        ON diffTemplates.diff_id = diffInputs.diff_id
        AND diffTemplates.template != 0 -- only join template files
    WHERE
        warpSkyfile.fault = 0
        AND warpSkyfile.ignored = 0
    ) AS warpsToDiff
-- Get best stack as a function of skycell_id, filter
JOIN (
    SELECT
        MAX(stack_id) AS best_stack_id, -- most recent stack, by virtue of auto-increment
        skycell_id,
        tess_id,
        filter
    FROM stackRun
    JOIN stackSumSkyfile USING(stack_id)
    WHERE stackRun.state = 'stop'
        AND stackSumSkyfile.fault = 0
    GROUP BY
        skycell_id,
        tess_id,
        filter
    ) AS stacksForDiff USING(skycell_id, tess_id, filter)
