CREATE TABLE IF NOT EXISTS payment_numbers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 merchant_id INT UNSIGNED NOT NULL,
 payment_method ENUM('bkash','nagad') NOT NULL,
 payment_number VARCHAR(30) NOT NULL,
 amount DECIMAL(15,2) NOT NULL DEFAULT 100.00,
 display_order INT NOT NULL DEFAULT 0,
 status TINYINT(1) NOT NULL DEFAULT 1,
 current_uses INT UNSIGNED NOT NULL DEFAULT 0,
 last_used_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_payment_number (merchant_id,payment_method,payment_number,amount),
 INDEX idx_rotation (merchant_id,payment_method,amount,status,display_order,last_used_at),
 CONSTRAINT fk_pn_merchant FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE transactions
 ADD COLUMN payment_number_id BIGINT UNSIGNED NULL AFTER callback_url,
 ADD COLUMN payment_method ENUM('bkash','nagad') NULL AFTER payment_number_id,
 ADD COLUMN payment_display_number VARCHAR(30) NULL AFTER payment_method,
 ADD COLUMN payment_session VARCHAR(80) NULL AFTER payment_display_number,
 ADD INDEX idx_tx_payment_session (payment_session),
 ADD CONSTRAINT fk_tx_payment_number FOREIGN KEY (payment_number_id) REFERENCES payment_numbers(id) ON DELETE SET NULL;