-- 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,
    current_stack_id,
    best_stack_id,
    exp_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,
        warpRun.label,
        filter,
        warpRun.label as warp_label,
        diffInputs.diff_id,
        diffInputs.stack2 AS current_stack_id,
        rawExp.exp_id
    FROM warpSkyfile
    JOIN warpRun USING(warp_id)
    JOIN fakeRun USING(fake_id)
    JOIN camRun USING(cam_id)
    JOIN chipRun USING(chip_id)
    JOIN rawExp USING(exp_id)
    -- Check if it has an associated diff
    LEFT JOIN diffInputSkyfile AS diffInputs
        ON diffInputs.warp1 = warpSkyfile.warp_id
        AND diffInputs.skycell_id = warpSkyfile.skycell_id
        AND diffInputs.stack2 IS NOT NULL
    WHERE
        warpSkyfile.fault = 0
        AND warpSkyfile.ignored = 0
    -- warpsToDiff WHERE hook %s
    ) 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,
        filter
    FROM stackRun
    JOIN stackSumSkyfile USING(stack_id)
    WHERE stackRun.state = 'full'
        AND stackSumSkyfile.fault = 0
    -- stacksForDiff WHERE hook %s
    GROUP BY
        skycell_id,
        filter
    ) AS stacksForDiff USING(skycell_id, filter)
