-- =====================================================================
--  Migration optimisation & observabilité (Phase finale step 7)
--  Date: 2025-08-26
--  - Indexes supplémentaires sur tables fortement sollicitées
--  - Ajout index sur bank(journal_entry_id) pour jointures rapides
--  Idempotent via information_schema
-- =====================================================================

SET @idx := (SELECT COUNT(1) FROM information_schema.statistics
              WHERE table_schema = DATABASE()
                AND table_name = 'bank'
                AND index_name = 'idx_bank_copro_bank_date');
SET @sql := IF(@idx=0,
  'ALTER TABLE bank ADD INDEX idx_bank_copro_bank_date (id_copro,id_bank,date)',
  'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

SET @idx2 := (SELECT COUNT(1) FROM information_schema.statistics
              WHERE table_schema = DATABASE()
                AND table_name = 'bank'
                AND index_name = 'idx_bank_journal');
SET @sql2 := IF(@idx2=0,
  'ALTER TABLE bank ADD INDEX idx_bank_journal (journal_entry_id)',
  'SELECT 1');
PREPARE st2 FROM @sql2; EXECUTE st2; DEALLOCATE PREPARE st2;

SET @idx3 := (SELECT COUNT(1) FROM information_schema.statistics
              WHERE table_schema = DATABASE()
                AND table_name = 'journal_entries'
                AND index_name = 'idx_journal_period');
SET @sql3 := IF(@idx3=0,
  'ALTER TABLE journal_entries ADD INDEX idx_journal_period (id_copro,period_id,date)',
  'SELECT 1');
PREPARE st3 FROM @sql3; EXECUTE st3; DEALLOCATE PREPARE st3;

-- =====================================================================
--  FIN MIGRATION OPTIMISATION
-- =====================================================================