-- A coluna entra nullable para permitir o backfill linha a linha.
ALTER TABLE `candidates` ADD COLUMN `curriculum_updated_at` DATETIME(3) NULL;

-- Backfill: quem já tem currículo recebe a alteração mais recente entre as 6
-- tabelas; quem não tem cai no created_at do próprio candidato.
UPDATE `candidates` `c`
SET `c`.`curriculum_updated_at` = COALESCE(
    (
        SELECT MAX(`t`.`updated_at`)
        FROM (
            SELECT `id_candidate`, `updated_at` FROM `personal_data`
            UNION ALL SELECT `id_candidate`, `updated_at` FROM `languages`
            UNION ALL SELECT `id_candidate`, `updated_at` FROM `experiences`
            UNION ALL SELECT `id_candidate`, `updated_at` FROM `educations`
            UNION ALL SELECT `id_candidate`, `updated_at` FROM `attachments`
            UNION ALL SELECT `id_candidate`, `updated_at` FROM `areas_of_interest`
        ) `t`
        WHERE `t`.`id_candidate` = `c`.`id`
    ),
    `c`.`created_at`
);

ALTER TABLE `candidates`
    MODIFY `curriculum_updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3);

-- CreateIndex
CREATE INDEX `candidates_curriculum_updated_at_idx` ON `candidates`(`curriculum_updated_at`);