-- select detRun.det_id (det_id)
-- select detRun.iteration
-- by:
-- find the current iteration bassed on det_id
-- find all exp_ids in the current det_id/iteration from detInputExp
-- sort to detInputExp.imfiles to find the largest value per det_id/iter
-- compare imfiles to the number of detStackedImfiles by class_id
-- and:
-- ???
-- det_id is not in detStackedImfile;
-- iteration is not in detStackedImfile;

SELECT
    det_id,
    det_type,
    iteration,
    camera,
    workdir
FROM
    (SELECT DISTINCT
        detRun.det_id,
        detRun.det_type,
        detRun.iteration,
        detRun.workdir,
        rawExp.camera,
        detStackedImfile.class_id,
        rawImfile.class_id as rawimfile_class_id
    FROM detRun
    JOIN detInputExp
        ON detRun.det_id = detInputExp.det_id
        AND detRun.iteration = detInputExp.iteration
    JOIN rawExp
        ON detInputExp.exp_id = rawExp.exp_id
    JOIN rawImfile
        ON rawExp.exp_id = rawImfile.exp_id
    LEFT JOIN detStackedImfile
        ON detInputExp.det_id = detStackedImfile.det_id
        AND detInputExp.iteration = detStackedImfile.iteration
        AND rawImfile.class_id = detStackedImfile.class_id
    LEFT JOIN detNormalizedStatImfile
        ON detStackedImfile.det_id = detNormalizedStatImfile.det_id
        AND detStackedImfile.iteration = detNormalizedStatImfile.iteration
        AND detStackedImfile.class_id = detNormalizedStatImfile.class_id
    WHERE
        detRun.state = 'run'
        AND detRun.mode = 'master'
        AND detStackedImfile.fault = 0
        AND detNormalizedStatImfile.det_id IS NULL
        AND detNormalizedStatImfile.iteration IS NULL
        AND detNormalizedStatImfile.class_id IS NULL
    GROUP BY
--        rawExp.exp_id,
        detRun.iteration,
        detRun.det_id
    HAVING COUNT(rawImfile.class_id) = COUNT(detStackedImfile.class_id)
    ) as tonormalizedstat
