-- Alignement du dump c2685816c_maintenance_back.sql vers le schema local bd_insuite_backbone.
--
-- Ce script cible les ecarts verifies pendant la comparaison reelle schema->schema:
-- - colonnes JSON encore en LONGTEXT+CHECK dans le dump
-- - absence de PK/AUTO_INCREMENT/index sur plusieurs tables FTTH
-- - presence de lignes parasites avec id=0 qui bloquent l'ajout des contraintes locales
-- - doublons incompatibles avec les contraintes locales sur maintenance_ftth_reports.ticket_id
--   et maintenance_ftth_import_history.file_hash
-- - recreation portable de la vue raccordement_clients sans DEFINER env-specifique

START TRANSACTION;

-- 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;

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;

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 orphelines sur report_id=0 ne sont plus raccordables une fois les rapports dedoubles.
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"%'
);