-- Restaura a marcacao "Sair no laudo PDF" das 9 fotos principais.
-- MySQL/MariaDB
-- Codigos: EV-001, EV-002, EV-003, EV-004, EV-005, EV-006, EV-007, EV-013 e EV-017.

START TRANSACTION;

-- Conferencia antes da alteracao.
SELECT
    ie.id AS evidencia_id,
    ie.inspection_id AS vistoria_id,
    r.title AS foto,
    SUBSTRING(r.description, LOCATE('(EV-', r.description) + 1, 6) AS codigo,
    ie.include_in_report AS marcacao_atual
FROM inspection_evidences AS ie
INNER JOIN inspection_evidence_requirements AS r
    ON r.id = ie.evidence_requirement_id
WHERE r.evidence_type = 'foto'
  AND ie.inspection_id BETWEEN 4 AND 23
  AND r.description REGEXP '\\(EV-(001|002|003|004|005|006|007|013|017)\\)'
  AND ie.file_url IS NOT NULL
  AND TRIM(ie.file_url) <> ''
  AND COALESCE(ie.include_in_report, 0) <> 1
ORDER BY ie.inspection_id, r.sort_order, ie.id;

-- Marca as 9 fotos principais existentes para inclusao no laudo.
UPDATE inspection_evidences AS ie
INNER JOIN inspection_evidence_requirements AS r
    ON r.id = ie.evidence_requirement_id
SET ie.include_in_report = 1,
    ie.updated_at = CURRENT_TIMESTAMP
WHERE r.evidence_type = 'foto'
  AND ie.inspection_id BETWEEN 4 AND 23
  AND r.description REGEXP '\\(EV-(001|002|003|004|005|006|007|013|017)\\)'
  AND ie.file_url IS NOT NULL
  AND TRIM(ie.file_url) <> ''
  AND COALESCE(ie.include_in_report, 0) <> 1;

SELECT ROW_COUNT() AS fotos_corrigidas;

-- Resumo final: cada vistoria deve possuir ate 9 fotos marcadas, conforme
-- as evidencias que realmente foram enviadas.
SELECT
    ie.inspection_id AS vistoria_id,
    COUNT(*) AS fotos_principais_enviadas,
    SUM(ie.include_in_report = 1) AS fotos_marcadas
FROM inspection_evidences AS ie
INNER JOIN inspection_evidence_requirements AS r
    ON r.id = ie.evidence_requirement_id
WHERE r.evidence_type = 'foto'
  AND ie.inspection_id BETWEEN 4 AND 23
  AND r.description REGEXP '\\(EV-(001|002|003|004|005|006|007|013|017)\\)'
  AND ie.file_url IS NOT NULL
  AND TRIM(ie.file_url) <> ''
GROUP BY ie.inspection_id
HAVING fotos_marcadas <> fotos_principais_enviadas
    OR fotos_marcadas <> 9
ORDER BY ie.inspection_id;

COMMIT;