-- HamBaa KAYA schema upgrade: add missing uniqueness constraints (idempotency + availability)
-- Safe to run multiple times (checks information_schema)

-- Unique: listing availability per day
SET @idx := (
  SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='listing_availability' AND INDEX_NAME IN ('uq_listing_day','uniq_listing_day')
);
SET @sql := IF(@idx=0,
  'ALTER TABLE listing_availability ADD UNIQUE KEY uq_listing_day (listing_id, day)',
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Unique: webhook event idempotency (per provider)
SET @idx := (
  SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='webhook_events' AND INDEX_NAME IN ('uq_webhook_provider_event','uniq_event')
);
SET @sql := IF(@idx=0,
  'ALTER TABLE webhook_events ADD UNIQUE KEY uq_webhook_provider_event (provider, event_id)',
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Unique: ledger idempotency key
SET @idx := (
  SELECT COUNT(1) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='ledger_entries' AND INDEX_NAME IN ('uq_ledger_idem','uniq_idem')
);
SET @sql := IF(@idx=0,
  'ALTER TABLE ledger_entries ADD UNIQUE KEY uq_ledger_idem (scope_type, scope_id, ref_type, ref_id, direction)',
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
