-- Alignement non destructif du dump c2685816c_maintenance_back.sql vers le schema local.
--
-- Variante production:
-- - aucune ligne n'est supprimee sans avoir ete archivee dans la meme base
-- - les doublons incompatibles avec le schema local sont copies vers des tables d'archive
-- - les photos orphelines sur report_id=0 sont conservees en archive

START TRANSACTION;

-- Archives techniques conservees dans la base cible.
CREATE TABLE IF NOT EXISTS maintenance_ftth_import_history_alignment_archive AS
SELECT h.*, CAST(NULL AS DATETIME) AS archived_at, CAST(NULL AS CHAR(64)) AS archive_reason
FROM maintenance_ftth_import_history h
WHERE 1 = 0;

CREATE TABLE IF NOT EXISTS maintenance_ftth_reports_alignment_archive AS
SELECT r.*, CAST(NULL AS DATETIME) AS archived_at, CAST(NULL AS CHAR(64)) AS archive_reason
FROM maintenance_ftth_reports r
WHERE 1 = 0;

CREATE TABLE IF NOT EXISTS maintenance_ftth_photos_alignment_archive AS
SELECT p.*, CAST(NULL AS DATETIME) AS archived_at, CAST(NULL AS CHAR(64)) AS archive_reason
FROM maintenance_ftth_photos p
WHERE 1 = 0;

-- 1) maintenance_ftth_assignments
ALTER TABLE maintenance_ftth_assignments
  ADD COLUMN __align_seq BIGINT NULL;

SET @seq := 0;
UPDATE maintenance_ftth_assignments
SET __align_seq = (@seq := @seq + 1)
ORDER BY assigned_at, ticket_id, technician_id, assigned_by, notes;

SET @next_id := (SELECT COALESCE(MAX(id), 0) FROM maintenance_ftth_assignments);
UPDATE maintenance_ftth_assignments
SET id = (@next_id := @next_id + 1)
WHERE id = 0
ORDER BY __align_seq;

ALTER TABLE maintenance_ftth_assignments
  ADD PRIMARY KEY (id),
  ADD KEY idx_ticket (ticket_id),
  ADD KEY idx_tech (technician_id),
  ADD KEY assigned_by (assigned_by),
  MODIFY id INT NOT NULL AUTO_INCREMENT,
  DROP COLUMN __align_seq;

-- 2) maintenance_ftth_import_history
ALTER TABLE maintenance_ftth_import_history
  ADD COLUMN __align_seq BIGINT NULL;

SET @seq := 0;
UPDATE maintenance_ftth_import_history
SET __align_seq = (@seq := @seq + 1)
ORDER BY COALESCE(last_imported_at, first_imported_at), import_count, file_hash, original_name;

INSERT INTO maintenance_ftth_import_history_alignment_archive
(
  id,
  file_hash,
  original_name,
  row_count,
  first_imported_at,
  first_imported_by,
  last_imported_at,
  last_imported_by,
  import_count,
  last_imported_rows,
  last_skipped_rows,
  archived_at,
  archive_reason
)
SELECT h.id,
       h.file_hash,
       h.original_name,
       h.row_count,
       h.first_imported_at,
       h.first_imported_by,
       h.last_imported_at,
       h.last_imported_by,
       h.import_count,
       h.last_imported_rows,
       h.last_skipped_rows,
       NOW() AS archived_at,
       'duplicate_file_hash' AS archive_reason
FROM maintenance_ftth_import_history h
JOIN (
  SELECT __align_seq,
         ROW_NUMBER() OVER (
           PARTITION BY file_hash
           ORDER BY COALESCE(last_imported_at, first_imported_at) DESC,
                    import_count DESC,
                    __align_seq DESC
         ) AS rn
  FROM maintenance_ftth_import_history
) ranked ON ranked.__align_seq = h.__align_seq
WHERE ranked.rn > 1;

DELETE h
FROM maintenance_ftth_import_history h
JOIN (
  SELECT __align_seq,
         ROW_NUMBER() OVER (
           PARTITION BY file_hash
           ORDER BY COALESCE(last_imported_at, first_imported_at) DESC,
                    import_count DESC,
                    __align_seq DESC
         ) AS rn
  FROM maintenance_ftth_import_history
) ranked ON ranked.__align_seq = h.__align_seq
WHERE ranked.rn > 1;

SET @next_id := (SELECT COALESCE(MAX(id), 0) FROM maintenance_ftth_import_history);
UPDATE maintenance_ftth_import_history
SET id = (@next_id := @next_id + 1)
WHERE id = 0
ORDER BY __align_seq;

ALTER TABLE maintenance_ftth_import_history
  ADD PRIMARY KEY (id),
  ADD UNIQUE KEY uq_file_hash (file_hash),
  ADD KEY idx_last_imported_at (last_imported_at),
  MODIFY id INT NOT NULL AUTO_INCREMENT,
  DROP COLUMN __align_seq,
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 3) maintenance_ftth_reports
ALTER TABLE maintenance_ftth_reports
  ADD COLUMN __align_seq BIGINT NULL;

SET @seq := 0;
UPDATE maintenance_ftth_reports
SET __align_seq = (@seq := @seq + 1)
ORDER BY COALESCE(submitted_at, updated_at, created_at), ticket_id, technician_id;

INSERT INTO maintenance_ftth_reports_alignment_archive
(
  id,
  ticket_id,
  technician_id,
  operation_type,
  intervention_date,
  intervention_start,
  intervention_end,
  constat_comments,
  impacted_equipment,
  materials_used,
  selected_otb_id,
  selected_otb_port_number,
  selected_otb_port_status,
  selected_otb_label,
  recommended_otb_id,
  recommended_otb_label,
  otb_distance_meters,
  otb_selection_mode,
  otb_recommendation_payload,
  otb_occupancy_synced,
  general_comments,
  satisfaction_service,
  satisfaction_delay,
  satisfaction_quality,
  satisfaction_behavior,
  client_name_onsite,
  client_visit_status,
  planned_intervention_date,
  planned_intervention_time,
  planning_comment,
  client_remarks,
  service_restored,
  client_signature_path,
  agent_signature_path,
  submit_gps_lat,
  submit_gps_lng,
  submit_gps_accuracy_meters,
  status,
  workflow_phase,
  study_technician_id,
  installation_technician_id,
  study_submitted_at,
  installation_sent_at,
  study_gps_lat,
  study_gps_lng,
  study_gps_accuracy_meters,
  submitted_at,
  validated_at,
  validated_by,
  rejection_reason,
  created_at,
  updated_at,
  archived_at,
  archive_reason
)
SELECT r.id,
       r.ticket_id,
       r.technician_id,
       r.operation_type,
       r.intervention_date,
       r.intervention_start,
       r.intervention_end,
       r.constat_comments,
       r.impacted_equipment,
       r.materials_used,
       r.selected_otb_id,
       r.selected_otb_port_number,
       r.selected_otb_port_status,
       r.selected_otb_label,
       r.recommended_otb_id,
       r.recommended_otb_label,
       r.otb_distance_meters,
       r.otb_selection_mode,
       r.otb_recommendation_payload,
       r.otb_occupancy_synced,
       r.general_comments,
       r.satisfaction_service,
       r.satisfaction_delay,
       r.satisfaction_quality,
       r.satisfaction_behavior,
       r.client_name_onsite,
       r.client_visit_status,
       r.planned_intervention_date,
       r.planned_intervention_time,
       r.planning_comment,
       r.client_remarks,
       r.service_restored,
       r.client_signature_path,
       r.agent_signature_path,
       r.submit_gps_lat,
       r.submit_gps_lng,
       r.submit_gps_accuracy_meters,
       r.status,
       r.workflow_phase,
       r.study_technician_id,
       r.installation_technician_id,
       r.study_submitted_at,
       r.installation_sent_at,
       r.study_gps_lat,
       r.study_gps_lng,
       r.study_gps_accuracy_meters,
       r.submitted_at,
       r.validated_at,
       r.validated_by,
       r.rejection_reason,
       r.created_at,
       r.updated_at,
       NOW() AS archived_at,
       'duplicate_ticket_id' AS archive_reason
FROM maintenance_ftth_reports r
JOIN (
  SELECT __align_seq,
         ROW_NUMBER() OVER (
           PARTITION BY ticket_id
           ORDER BY COALESCE(submitted_at, updated_at, created_at) DESC,
                    __align_seq DESC
         ) AS rn
  FROM maintenance_ftth_reports
) ranked ON ranked.__align_seq = r.__align_seq
WHERE ranked.rn > 1;

DELETE r
FROM maintenance_ftth_reports r
JOIN (
  SELECT __align_seq,
         ROW_NUMBER() OVER (
           PARTITION BY ticket_id
           ORDER BY COALESCE(submitted_at, updated_at, created_at) DESC,
                    __align_seq DESC
         ) AS rn
  FROM maintenance_ftth_reports
) ranked ON ranked.__align_seq = r.__align_seq
WHERE ranked.rn > 1;

SET @next_id := (SELECT COALESCE(MAX(id), 0) FROM maintenance_ftth_reports);
UPDATE maintenance_ftth_reports
SET id = (@next_id := @next_id + 1)
WHERE id = 0
ORDER BY __align_seq;

ALTER TABLE maintenance_ftth_reports
  MODIFY impacted_equipment JSON DEFAULT NULL COMMENT '[{"name":"...", "photo_path":"...", "comment":"..."}]',
  MODIFY materials_used JSON DEFAULT NULL COMMENT '[{"name":"...", "qty":1, "ref":"...", "photo_path":"...", "comment":"..."}]',
  MODIFY otb_recommendation_payload JSON DEFAULT NULL,
  ADD PRIMARY KEY (id),
  ADD UNIQUE KEY ticket_id (ticket_id),
  ADD KEY idx_ticket (ticket_id),
  ADD KEY idx_tech (technician_id),
  ADD KEY idx_status (status),
  ADD KEY validated_by (validated_by),
  MODIFY id INT NOT NULL AUTO_INCREMENT,
  DROP COLUMN __align_seq;

-- 4) maintenance_ftth_photos
-- Les photos en report_id=0 ne peuvent pas etre rattachees sans ambiguite: elles sont archivees avant retrait.
INSERT INTO maintenance_ftth_photos_alignment_archive
(
  id,
  report_id,
  tab_type,
  item_index,
  photo_path,
  comment,
  gps_lat,
  gps_lng,
  taken_at,
  created_at,
  archived_at,
  archive_reason
)
SELECT p.id,
       p.report_id,
       p.tab_type,
       p.item_index,
       p.photo_path,
       p.comment,
       p.gps_lat,
       p.gps_lng,
       p.taken_at,
       p.created_at,
       NOW() AS archived_at,
       'orphan_report_id_0' AS archive_reason
FROM maintenance_ftth_photos p
WHERE p.report_id = 0;

DELETE FROM maintenance_ftth_photos WHERE report_id = 0;

ALTER TABLE maintenance_ftth_photos
  ADD COLUMN __align_seq BIGINT NULL;

SET @seq := 0;
UPDATE maintenance_ftth_photos
SET __align_seq = (@seq := @seq + 1)
ORDER BY created_at, report_id, tab_type, photo_path;

SET @next_id := (SELECT COALESCE(MAX(id), 0) FROM maintenance_ftth_photos);
UPDATE maintenance_ftth_photos
SET id = (@next_id := @next_id + 1)
WHERE id = 0
ORDER BY __align_seq;

ALTER TABLE maintenance_ftth_photos
  ADD PRIMARY KEY (id),
  ADD KEY idx_report (report_id),
  MODIFY id INT NOT NULL AUTO_INCREMENT,
  DROP COLUMN __align_seq;

-- 5) maintenance_ftth_ticket_events
ALTER TABLE maintenance_ftth_ticket_events
  ADD COLUMN __align_seq BIGINT NULL;

SET @seq := 0;
UPDATE maintenance_ftth_ticket_events
SET __align_seq = (@seq := @seq + 1)
ORDER BY created_at, ticket_id, actor_id, event_type;

SET @next_id := (SELECT COALESCE(MAX(id), 0) FROM maintenance_ftth_ticket_events);
UPDATE maintenance_ftth_ticket_events
SET id = (@next_id := @next_id + 1)
WHERE id = 0
ORDER BY __align_seq;

ALTER TABLE maintenance_ftth_ticket_events
  ADD PRIMARY KEY (id),
  ADD KEY idx_ticket_created (ticket_id, created_at),
  ADD KEY idx_actor (actor_id),
  MODIFY id INT NOT NULL AUTO_INCREMENT,
  DROP COLUMN __align_seq,
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 6) maintenance_ftth_tickets
ALTER TABLE maintenance_ftth_tickets
  MODIFY extra_fields JSON DEFAULT NULL,
  ADD PRIMARY KEY (id),
  ADD KEY idx_status (status),
  ADD KEY idx_client (client_name),
  ADD KEY imported_by (imported_by),
  MODIFY id INT NOT NULL AUTO_INCREMENT;

-- 7) raccordement_zone_settings
ALTER TABLE raccordement_zone_settings
  MODIFY technician_ids JSON DEFAULT NULL,
  MODIFY supervisor_ids JSON DEFAULT NULL,
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 8) roles
ALTER TABLE roles
  MODIFY permissions JSON DEFAULT NULL;

COMMIT;

-- 9) Vue locale portable, sans DEFINER specifique a l'environnement source.
CREATE OR REPLACE VIEW raccordement_clients AS
SELECT
  t.id AS id,
  t.ref_code AS ref_code,
  t.fixed_line AS fixed_line,
  t.client_name AS client_name,
  t.client_code AS client_code,
  t.client_phone AS client_phone,
  t.client_phone2 AS client_phone2,
  t.client_email AS client_email,
  t.public_client_id AS public_client_id,
  t.client_address AS client_address,
  t.client_lat AS client_lat,
  t.client_lng AS client_lng,
  t.sro_client AS sro_client,
  t.jdv_client AS jdv_client,
  t.pco_client AS pco_client,
  t.company_name AS company_name,
  t.dispatch_zone_key AS dispatch_zone_key,
  t.dispatch_zone_label AS dispatch_zone_label,
  t.source_channel AS source_channel,
  t.last_public_incident_at AS last_public_incident_at,
  t.site_name AS site_name,
  t.nature_intervention AS nature_intervention,
  t.avancement AS avancement,
  t.equipes AS equipes,
  t.date_cloture AS date_cloture,
  t.description AS description,
  t.cause_comment AS cause_comment,
  t.extra_fields AS extra_fields,
  t.priority AS priority,
  t.status AS status,
  t.imported_at AS imported_at,
  t.imported_by AS imported_by,
  t.created_at AS created_at,
  t.updated_at AS updated_at
FROM maintenance_ftth_tickets t
WHERE (
  LOWER(COALESCE(t.nature_intervention, '')) LIKE '%raccordement%'
  OR LOWER(COALESCE(t.extra_fields, '')) LIKE '%numeroabonnement%'
  OR LOWER(COALESCE(t.extra_fields, '')) LIKE '%equipe_raccordement%'
  OR LOWER(COALESCE(t.extra_fields, '')) LIKE '%date_enregistrement_instances%'
  OR LOWER(COALESCE(t.extra_fields, '')) LIKE '%date_envoi_instances_prestataires%'
  OR LOWER(COALESCE(t.extra_fields, '')) LIKE '%snont%'
  OR LOWER(COALESCE(t.extra_fields, '')) LIKE '%"module":"raccordement-clients"%'
);