-- ==============================================================================
-- SCRIPT DE MIGRATION DONNÉES 2026 - KOPILOT 360
-- ==============================================================================
-- Généré le : 22 janvier 2026
-- Objectif : Créer declarations_devis et projects pour les 100 opérations 2026
-- ==============================================================================

-- ÉTAPE 1: Créer les declarations_devis à partir des opérations
-- ==============================================================================

INSERT INTO declarations_devis (
  id,
  commercial_id,
  numero_devis,
  montant_ht,
  montant_total_ht,
  date_signature,
  service,
  marque,
  ville,
  date_coaching_debut,
  date_coaching_fin,
  date_evenement_debut,
  date_evenement_fin,
  nom_entite_client,
  groupe,
  ville_client,
  statut,
  type_declaration,
  operation_label,
  created_at,
  updated_at
)
SELECT 
  gen_random_uuid() as id,
  o.commercial_id,
  'MIG2026-' || LPAD(ROW_NUMBER() OVER (ORDER BY o.event_start_date)::text, 4, '0') as numero_devis,
  o."montant_HT" as montant_ht,
  o."montant_HT" as montant_total_ht,
  COALESCE(o.coaching_start_date, o.event_start_date) as date_signature,
  pm.task_template_service as service,
  o.nom_marque as marque,
  o.client_location as ville,
  o.coaching_start_date as date_coaching_debut,
  o.coaching_end_date as date_coaching_fin,
  o.event_start_date as date_evenement_debut,
  o.event_end_date as date_evenement_fin,
  o.nom_groupe as nom_entite_client,
  o.nom_groupe as groupe,
  o.client_location as ville_client,
  'valide' as statut,
  'projet' as type_declaration,
  o.title as operation_label,
  NOW() as created_at,
  NOW() as updated_at
FROM operations o
JOIN migration_emails_2026 me ON o.id = me.operation_id
LEFT JOIN product_mapping_operations pm ON o.product::text = pm.operation_product
WHERE o.id_declaration_devis IS NULL
ORDER BY o.event_start_date;

-- ==============================================================================
-- ÉTAPE 2: Mettre à jour les operations avec id_declaration_devis
-- ==============================================================================

-- On utilise une correspondance basée sur le titre et les dates
WITH declaration_mapping AS (
  SELECT 
    d.id as declaration_id,
    o.id as operation_id
  FROM declarations_devis d
  JOIN operations o ON 
    d.operation_label = o.title
    AND d.groupe = o.nom_groupe
    AND d.ville = o.client_location
    AND d.date_evenement_debut = o.event_start_date
    AND d.numero_devis LIKE 'MIG2026-%'
  WHERE o.id IN (SELECT operation_id FROM migration_emails_2026)
)
UPDATE operations o
SET id_declaration_devis = dm.declaration_id
FROM declaration_mapping dm
WHERE o.id = dm.operation_id;

-- ==============================================================================
-- ÉTAPE 3: Créer les projects à partir des declarations_devis
-- ==============================================================================

INSERT INTO projects (
  id_declaration_devis,
  id_chef_de_projet,
  id_commercial,
  nom_client,
  titre_projet,
  date_debut_ope,
  date_fin_ope,
  date_debut_coaching,
  date_fin_coaching,
  budget_initial,
  ville,
  marque,
  produit,
  groupe_auto,
  intitule_operation,
  semaine_operation,
  statut_operation,
  option_coach,
  created_at,
  updated_at
)
SELECT 
  d.id as id_declaration_devis,
  o.project_manager_id as id_chef_de_projet,
  o.commercial_id as id_commercial,
  d.groupe as nom_client,
  d.operation_label as titre_projet,
  d.date_evenement_debut as date_debut_ope,
  d.date_evenement_fin as date_fin_ope,
  d.date_coaching_debut as date_debut_coaching,
  d.date_coaching_fin as date_fin_coaching,
  d.montant_ht as budget_initial,
  d.ville,
  d.marque,
  d.service as produit,
  d.groupe as groupe_auto,
  d.operation_label as intitule_operation,
  EXTRACT(WEEK FROM d.date_evenement_debut)::integer as semaine_operation,
  'en_cours'::statut_operation as statut_operation,
  o.has_coaching as option_coach,
  NOW() as created_at,
  NOW() as updated_at
FROM declarations_devis d
JOIN operations o ON o.id_declaration_devis = d.id
WHERE d.numero_devis LIKE 'MIG2026-%';

-- ==============================================================================
-- ÉTAPE 4: Vérification
-- ==============================================================================

-- Compter les résultats
SELECT 
  'declarations_devis' as table_name,
  COUNT(*) as total
FROM declarations_devis
WHERE numero_devis LIKE 'MIG2026-%'

UNION ALL

SELECT 
  'projects' as table_name,
  COUNT(*) as total
FROM projects p
JOIN declarations_devis d ON p.id_declaration_devis = d.id
WHERE d.numero_devis LIKE 'MIG2026-%'

UNION ALL

SELECT 
  'operations_liees' as table_name,
  COUNT(*) as total
FROM operations
WHERE id_declaration_devis IN (
  SELECT id FROM declarations_devis WHERE numero_devis LIKE 'MIG2026-%'
);
