-- Script pour corriger les champs avec type='reference' mais reference IS NULL
-- Ces champs ne sont PAS des références (foreign keys) mais des champs simples

-- Mettre à jour les types dans metadatas
UPDATE metadatas SET type = 'string' WHERE name = 'banques_source' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'string' WHERE name = 'bspcompanies_uuid' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'string' WHERE name = 'bspcompanies_airline_code' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'string' WHERE name = 'bspdetails_trnc' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'string' WHERE name = 'bspdetails_numero_document' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'datetime' WHERE name = 'bspdetails_date_emission' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'picklist' WHERE name = 'bsps_statut' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'string' WHERE name = 'details_pnr' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'picklist' WHERE name = 'details_etat' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'boolean' WHERE name = 'details_success' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'datetime' WHERE name = 'details_created_at' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'picklist' WHERE name = 'execution_gds_operation_type' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'picklist' WHERE name = 'execution_gds_gds_type' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'picklist' WHERE name = 'execution_gds_status' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'string' WHERE name = 'execution_gds_module_source' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'string' WHERE name = 'execution_gds_record_uuid' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'string' WHERE name = 'execution_gds_record_locator' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'datetime' WHERE name = 'execution_gds_created_at' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'picklist' WHERE name = 'financial_requests_status' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'datetime' WHERE name = 'financial_requests_expiry_date' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'string' WHERE name = 'financial_requests_message_uuid' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'string' WHERE name = 'rhcs_uuid' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'datetime' WHERE name = 'rhcs_collection_date' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'boolean' WHERE name = 'rhcs_is_deleted' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'datetime' WHERE name = 'rhcs_created_at' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'string' WHERE name = 'segments_pnr_uuid' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'integer' WHERE name = 'segments_segment_number' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'picklist' WHERE name = 'segments_type' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'picklist' WHERE name = 'segments_status' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'string' WHERE name = 'segments_departure_code' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'datetime' WHERE name = 'segments_departure_date' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'string' WHERE name = 'segments_arrival_code' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas SET type = 'string' WHERE name = 'segments_carrier' AND type = 'reference' AND (reference IS NULL OR reference = '');

-- Mettre à jour les types dans metadatas_backup
UPDATE metadatas_backup SET type = 'string' WHERE name = 'banques_source' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'string' WHERE name = 'bspcompanies_uuid' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'string' WHERE name = 'bspcompanies_airline_code' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'string' WHERE name = 'bspdetails_trnc' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'string' WHERE name = 'bspdetails_numero_document' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'datetime' WHERE name = 'bspdetails_date_emission' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'picklist' WHERE name = 'bsps_statut' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'string' WHERE name = 'details_pnr' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'picklist' WHERE name = 'details_etat' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'boolean' WHERE name = 'details_success' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'datetime' WHERE name = 'details_created_at' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'picklist' WHERE name = 'execution_gds_operation_type' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'picklist' WHERE name = 'execution_gds_gds_type' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'picklist' WHERE name = 'execution_gds_status' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'string' WHERE name = 'execution_gds_module_source' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'string' WHERE name = 'execution_gds_record_uuid' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'string' WHERE name = 'execution_gds_record_locator' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'datetime' WHERE name = 'execution_gds_created_at' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'picklist' WHERE name = 'financial_requests_status' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'datetime' WHERE name = 'financial_requests_expiry_date' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'string' WHERE name = 'financial_requests_message_uuid' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'string' WHERE name = 'rhcs_uuid' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'datetime' WHERE name = 'rhcs_collection_date' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'boolean' WHERE name = 'rhcs_is_deleted' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'datetime' WHERE name = 'rhcs_created_at' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'string' WHERE name = 'segments_pnr_uuid' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'integer' WHERE name = 'segments_segment_number' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'picklist' WHERE name = 'segments_type' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'picklist' WHERE name = 'segments_status' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'string' WHERE name = 'segments_departure_code' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'datetime' WHERE name = 'segments_departure_date' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'string' WHERE name = 'segments_arrival_code' AND type = 'reference' AND (reference IS NULL OR reference = '');
UPDATE metadatas_backup SET type = 'string' WHERE name = 'segments_carrier' AND type = 'reference' AND (reference IS NULL OR reference = '');

-- Afficher le résultat
SELECT
    CONCAT('Corrigé ', COUNT(*), ' champs de type reference sans clé étrangère') AS resultat
FROM metadatas
WHERE type IN ('string', 'datetime', 'picklist', 'boolean', 'integer')
    AND reference IS NULL
    AND name IN (
        'banques_source', 'bspcompanies_uuid', 'bspcompanies_airline_code',
        'bspdetails_trnc', 'bspdetails_numero_document', 'bspdetails_date_emission',
        'bsps_statut', 'details_pnr', 'details_etat', 'details_success', 'details_created_at',
        'execution_gds_operation_type', 'execution_gds_gds_type', 'execution_gds_status',
        'execution_gds_module_source', 'execution_gds_record_uuid', 'execution_gds_record_locator',
        'execution_gds_created_at', 'financial_requests_status', 'financial_requests_expiry_date',
        'financial_requests_message_uuid', 'rhcs_uuid', 'rhcs_collection_date',
        'rhcs_is_deleted', 'rhcs_created_at', 'segments_pnr_uuid', 'segments_segment_number',
        'segments_type', 'segments_status', 'segments_departure_code', 'segments_departure_date',
        'segments_arrival_code', 'segments_carrier'
    );