defmodule DaProductApp.Repo.Migrations.CreateYspNotificationLogs do
  use Ecto.Migration

  def up do
    execute("""
    CREATE TABLE ysp_notification_logs (
      id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      transaction_id BIGINT UNSIGNED NOT NULL,
      payment_id VARCHAR(255),
      notification_url VARCHAR(500) NOT NULL,
      payload JSON NOT NULL,
      attempt_number INT DEFAULT 1,
      max_attempts INT DEFAULT 3,
      status ENUM('pending', 'success', 'failed', 'max_attempts_reached') DEFAULT 'pending',
      http_status_code INT,
      response_body TEXT,
      error_message TEXT,
      next_retry_at DATETIME,
      completed_at DATETIME,
      inserted_at DATETIME NOT NULL,
      updated_at DATETIME NOT NULL,
      INDEX idx_transaction_id (transaction_id),
      INDEX idx_payment_id (payment_id),
      INDEX idx_status (status),
      INDEX idx_next_retry (next_retry_at),
      FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    """)
  end

  def down do
    execute("DROP TABLE IF EXISTS ysp_notification_logs")
  end
end