-- HamBaa KAYA schema cleanup: remove duplicate unique indexes created by earlier revisions
-- Safe to run multiple times.
--
-- MariaDB compatibility: use information_schema + dynamic SQL.

-- listing_availability: keep uniq_listing_day, drop uq_listing_day if both exist
SET @has_keep := (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME='listing_availability'
    AND INDEX_NAME='uniq_listing_day'
);

SET @has_dup := (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME='listing_availability'
    AND INDEX_NAME='uq_listing_day'
);

SET @sql := IF(@has_keep > 0 AND @has_dup > 0, 'ALTER TABLE listing_availability DROP INDEX uq_listing_day', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ledger_entries: keep uniq_idem, drop uq_ledger_idem if both exist
SET @has_keep := (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME='ledger_entries'
    AND INDEX_NAME='uniq_idem'
);

SET @has_dup := (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME='ledger_entries'
    AND INDEX_NAME='uq_ledger_idem'
);

SET @sql := IF(@has_keep > 0 AND @has_dup > 0, 'ALTER TABLE ledger_entries DROP INDEX uq_ledger_idem', 'SELECT 1');
PREPARE stmt2 FROM @sql;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

-- webhook_events: keep uniq_event, drop uq_webhook_provider_event if both exist
SET @has_keep := (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME='webhook_events'
    AND INDEX_NAME='uniq_event'
);

SET @has_dup := (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME='webhook_events'
    AND INDEX_NAME='uq_webhook_provider_event'
);

SET @sql := IF(@has_keep > 0 AND @has_dup > 0, 'ALTER TABLE webhook_events DROP INDEX uq_webhook_provider_event', 'SELECT 1');
PREPARE stmt3 FROM @sql;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;
