-- This query is a little complicated, since it contains multiple self-joins.
-- This is because of the options involved: both templates and inputs can be either warps or stacks.
-- All of the templates and inputs need to exist, so we query against all of them.
SELECT DISTINCT
    diffRun.diff_id,
    diffRun.workdir,
    diffRun.skycell_id,
    diffRun.tess_id,
    diffRun.label,
    diffRun.state
FROM diffRun
-- Get list of templates for each diffRun
JOIN diffInputSkyfile AS diffTemplateSkyfile
    ON diffRun.diff_id = diffTemplateSkyfile.diff_id
    AND diffRun.skycell_id = diffTemplateSkyfile.skycell_id
    AND diffTemplateSkyfile.template = 1
-- Get list of inputs for each diffRun
JOIN diffInputSkyfile
    ON diffRun.diff_id = diffInputSkyfile.diff_id
    AND diffRun.skycell_id = diffInputSkyfile.skycell_id
    AND diffInputSkyfile.template = 0
-- Get warp templates
LEFT JOIN warpRun AS warpTemplateRun
    ON warpTemplateRun.warp_id = diffTemplateSkyfile.warp_id
    AND diffTemplateSkyfile.warp_id IS NOT NULL
LEFT JOIN warpSkyfile AS warpTemplateSkyfile
    ON warpTemplateSkyfile.warp_id = warpTemplateRun.warp_id
    AND warpTemplateSkyfile.skycell_id = diffTemplateSkyfile.skycell_id
-- Get warp inputs
LEFT JOIN warpRun
    ON warpRun.warp_id = diffInputSkyfile.warp_id
    AND diffInputSkyfile.warp_id IS NOT NULL
LEFT JOIN warpSkyfile
    ON warpSkyfile.warp_id = warpRun.warp_id
    AND warpSkyfile.skycell_id = diffInputSkyfile.skycell_id
-- Get stack templates
LEFT JOIN stackRun AS stackTemplateRun
    ON stackTemplateRun.stack_id = diffTemplateSkyfile.stack_id
LEFT JOIN stackSumSkyfile AS stackTemplateSkyfile
    ON stackTemplateSkyfile.stack_id = stackTemplateRun.stack_id
-- Get stack inputs
LEFT JOIN stackRun
    ON stackRun.stack_id = diffInputSkyfile.stack_id
    AND diffInputSkyfile.warp_id IS NULL
LEFT JOIN stackSumSkyfile
    ON stackSumSkyfile.stack_id = stackRun.stack_id
    AND diffInputSkyfile.warp_id IS NULL
-- Get what's already been processed
LEFT JOIN diffSkyfile
    ON diffInputSkyfile.diff_id = diffSkyfile.diff_id
WHERE
-- Ready to be processed
    ((diffRun.state = 'new'
    AND diffSkyfile.diff_id IS NULL)
    OR (diffRun.state = 'update'
    AND diffSkyfile.fault = 0)
    )
-- Ensure input warps are available
    AND (diffInputSkyfile.warp_id IS NULL
    OR (warpRun.state = 'full'
    AND warpSkyfile.fault = 0
    AND warpSkyfile.ignored = 0))
-- Ensure input stacks are available
    AND (diffInputSkyfile.stack_id IS NULL
    OR (stackRun.state = 'full'
    AND stackSumSkyfile.fault = 0))
-- Ensure template warps are available
    AND (diffTemplateSkyfile.warp_id IS NULL
    OR (warpTemplateRun.state = 'full'
    AND warpTemplateSkyfile.fault = 0
    AND warpTemplateSkyfile.ignored = 0))
-- Ensure template stacks are available
    AND (diffTemplateSkyfile.stack_id IS NULL
    OR (stackTemplateRun.state = 'full'
    AND stackTemplateSkyfile.fault = 0))



