ALTER TABLE payments ADD COLUMN IF NOT EXISTS provider_payment_id VARCHAR(120) NULL AFTER provider_reference;
ALTER TABLE payments ADD COLUMN IF NOT EXISTS external_reference VARCHAR(120) NULL AFTER provider_payment_id;
ALTER TABLE payments MODIFY status ENUM('pending','approved','paid','failed','refunded','cancelled','expired') NOT NULL DEFAULT 'pending';
ALTER TABLE campaign_numbers MODIFY status ENUM('available','reserved','paid','cancelled','expired') NOT NULL DEFAULT 'available';

CREATE INDEX idx_payment_provider_id ON payments (provider, provider_payment_id);
CREATE INDEX idx_payment_external_reference ON payments (external_reference);

CREATE TABLE IF NOT EXISTS webhook_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider VARCHAR(40) NOT NULL,
  event_type VARCHAR(80) NULL,
  provider_payment_id VARCHAR(120) NULL,
  status VARCHAR(40) NULL,
  payload JSON NULL,
  processed TINYINT(1) NOT NULL DEFAULT 0,
  error_message TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_webhook_provider_payment (provider, provider_payment_id),
  INDEX idx_webhook_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;