SELECT
    magicTree.*,
    magicRun.workdir,
    rawExp.exp_id,
    rawExp.camera,
    -- convert magic_id into a boolean value (1 or 0)
    -- note that the type stays a 64 bit int
    magicNodeResult.magic_id IS TRUE AS done,
    magicNodeResult.fault IS TRUE AS bad
FROM magicRun
JOIN magicTree USING(magic_id)
JOIN magicInputSkyfile USING(magic_id, node)
JOIN rawExp USING(exp_id)
JOIN diffSkyfile -- only get nodes that match a skycell
    ON diffSkyfile.diff_id = magicRun.diff_id
    AND diffSkyfile.skycell_id = magicInputSkyfile.node
JOIN diffRun
    ON diffRun.diff_id =  diffSkyfile.diff_id
LEFT JOIN magicNodeResult USING(magic_id, node)
WHERE
    magicRun.state = 'new'
    AND magicNodeResult.magic_id IS NULL
    AND magicNodeResult.node IS NULL
    AND diffRun.state = 'full'
-- WHERE hook %s
