CREATE TABLE IF NOT EXISTS `apply_deluge` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `deluge_jobs_type_id` INT NOT NULL,
    `description` TEXT COLLATE utf8mb4_general_ci DEFAULT NULL,
    `date_limite` DATE DEFAULT NULL,
    `prenom` VARCHAR(100) COLLATE utf8mb4_general_ci NOT NULL,
    `nom` VARCHAR(100) COLLATE utf8mb4_general_ci NOT NULL,
    `gender` ENUM('Masculin', 'Feminin') NOT NULL,
    `nip` VARCHAR(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
    `entite` VARCHAR(255) COLLATE utf8mb4_general_ci NOT NULL,
    `tel` VARCHAR(20) COLLATE utf8mb4_general_ci NOT NULL,
    `region` VARCHAR(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
    `prefecture` VARCHAR(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
    `commune` VARCHAR(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
    `quartier` VARCHAR(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
    `secteur` VARCHAR(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
    `centre_vote` VARCHAR(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
    `bureau_vote` VARCHAR(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `preselectionner` TINYINT(1) DEFAULT '0',
    `preselected_by` VARCHAR(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
    `preselected_at` TIMESTAMP NULL DEFAULT NULL,
    `comment_preselection` TEXT COLLATE utf8mb4_general_ci DEFAULT NULL,
    `selectionner` TINYINT(1) DEFAULT '0',
    `selected_by` VARCHAR(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
    `selected_at` TIMESTAMP NULL DEFAULT NULL,
    `comment_selection` TEXT COLLATE utf8mb4_general_ci DEFAULT NULL,
    `rejected` TINYINT(1) DEFAULT '0',
    `rejected_by` VARCHAR(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
    `rejected_at` TIMESTAMP NULL DEFAULT NULL,
    `comment_reject` TEXT COLLATE utf8mb4_general_ci DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `deluge_jobs_type_id` (`deluge_jobs_type_id`),
    KEY `idx_region` (`region`),
    KEY `idx_prefecture` (`prefecture`),
    KEY `idx_commune` (`commune`),
    KEY `idx_preselected_by` (`preselected_by`),
    KEY `idx_selected_by` (`selected_by`),
    KEY `idx_rejected_by` (`rejected_by`),
    UNIQUE KEY `uniq_apply_deluge_job_tel` (`deluge_jobs_type_id`, `tel`),
    CONSTRAINT `apply_deluge_ibfk_1` FOREIGN KEY (`deluge_jobs_type_id`) REFERENCES `deluge_jobs_types` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;

ALTER TABLE `apply_agelecto_documents_types`
ADD COLUMN `apply_deluge_id` INT NULL AFTER `apply_mbv_id`;

ALTER TABLE `apply_agelecto_documents_types`
ADD KEY `apply_deluge_id` (`apply_deluge_id`);

ALTER TABLE `apply_agelecto_documents_types`
ADD CONSTRAINT `apply_agelecto_documents_types_ibfk_5`
FOREIGN KEY (`apply_deluge_id`) REFERENCES `apply_deluge` (`id`) ON DELETE CASCADE;

ALTER TABLE `apply_agelecto_documents_types`
DROP CONSTRAINT `apply_agelecto_documents_types_check`;

ALTER TABLE `apply_agelecto_documents_types`
ADD CONSTRAINT `apply_agelecto_documents_types_check`
CHECK (
    (`apply_cacv_id` IS NOT NULL AND `apply_crtpv_id` IS NULL AND `apply_mbv_id` IS NULL AND `apply_deluge_id` IS NULL)
    OR (`apply_cacv_id` IS NULL AND `apply_crtpv_id` IS NOT NULL AND `apply_mbv_id` IS NULL AND `apply_deluge_id` IS NULL)
    OR (`apply_cacv_id` IS NULL AND `apply_crtpv_id` IS NULL AND `apply_mbv_id` IS NOT NULL AND `apply_deluge_id` IS NULL)
    OR (`apply_cacv_id` IS NULL AND `apply_crtpv_id` IS NULL AND `apply_mbv_id` IS NULL AND `apply_deluge_id` IS NOT NULL)
);
