-- which returns a list of processed imfiles (with corresponding detrend id,
-- iteration, class id, uri, type) for which the appropriate stacked imfile has
-- been normalised, and which have not been masked out by detResidImfileAnalysis

SELECT DISTINCT
    detRun.det_id,
    detRun.iteration,
    detRun.det_type,
    detRun.mode,
    detRun.workdir,
    detRun.reduction,
    detProcessedImfile.exp_id,
    detProcessedImfile.class_id,
    detProcessedImfile.uri,
    detNormalizedImfile.uri AS det_uri,
    rawExp.camera,
    rawExp.exp_tag
FROM detRun
JOIN detInputExp
    USING(det_id, iteration)
JOIN rawExp
    ON detInputExp.exp_id = rawExp.exp_id
JOIN detProcessedImfile
    ON detRun.det_id = detProcessedImfile.det_id
    AND detInputExp.exp_id = detProcessedImfile.exp_id
JOIN detNormalizedImfile
    ON detRun.det_id = detNormalizedImfile.det_id
    AND detRun.iteration = detNormalizedImfile.iteration
    AND detProcessedImfile.class_id = detNormalizedImfile.class_id
JOIN detNormalizedExp
    ON detRun.det_id = detNormalizedExp.det_id
    AND detRun.iteration = detNormalizedExp.iteration
LEFT JOIN detResidImfile
    ON detRun.det_id = detResidImfile.det_id
    AND detRun.iteration = detResidImfile.iteration
    AND detProcessedImfile.exp_id = detResidImfile.exp_id
    AND detProcessedImfile.class_id = detResidImfile.class_id
WHERE
    detRun.state = 'run'
    AND detRun.mode = 'master'
    AND detNormalizedImfile.fault = 0
    AND detResidImfile.det_id IS NULL
    AND detResidImfile.iteration IS NULL
    AND detResidImfile.exp_id IS NULL
    AND detResidImfile.class_id IS NULL
UNION
SELECT
    detRun.det_id,
    detRun.iteration,
    detRun.det_type,
    detRun.mode,
    detRun.workdir,
    detRun.reduction,
    rawImfile.exp_id,
    rawImfile.class_id,
    rawImfile.uri,
    'NULL' AS det_uri,
    rawExp.camera,
    rawExp.exp_tag
FROM detRun
JOIN detInputExp
    USING(det_id, iteration)
JOIN rawExp
    ON detInputExp.exp_id = rawExp.exp_id
JOIN rawImfile
    ON detInputExp.exp_id = rawImfile.exp_id
LEFT JOIN detResidImfile
    ON detRun.det_id = detResidImfile.det_id
    AND detRun.iteration = detResidImfile.iteration
    AND rawImfile.exp_id = detResidImfile.exp_id
    AND rawImfile.class_id = detResidImfile.class_id
WHERE
    detRun.state = 'run'
    AND detRun.mode = 'verify'
    AND detResidImfile.det_id IS NULL
    AND detResidImfile.iteration IS NULL
    AND detResidImfile.exp_id IS NULL
    AND detResidImfile.class_id IS NULL
ORDER BY exp_id
