-- ResellBD Step 21: adapter registry + idempotent provider event inbox. NEW ResellBD database only.
SET NAMES utf8mb4;

ALTER TABLE orders MODIFY payment_method ENUM('cod','manual_bkash','manual_nagad','manual_rocket','bank_transfer','cash','nagad_gateway','card_gateway') NOT NULL DEFAULT 'cod';

CREATE TABLE IF NOT EXISTS payment_gateway_transactions (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 order_id BIGINT UNSIGNED NOT NULL,
 provider_code VARCHAR(40) NOT NULL,
 provider_transaction_id VARCHAR(190) NULL,
 merchant_reference VARCHAR(100) NOT NULL,
 amount_paisa BIGINT UNSIGNED NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'BDT',
 status ENUM('created','pending','authorized','paid','failed','cancelled','refunded') NOT NULL DEFAULT 'created',
 idempotency_key CHAR(64) NOT NULL,
 provider_response_json JSON NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY(id), UNIQUE KEY uq_gateway_idempotency(idempotency_key), UNIQUE KEY uq_gateway_merchant_ref(merchant_reference),
 UNIQUE KEY uq_gateway_provider_tx(provider_code,provider_transaction_id), KEY idx_gateway_order(order_id,created_at), KEY idx_gateway_status(status,updated_at),
 CONSTRAINT fk_gateway_order FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS payment_provider_events (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 provider_code VARCHAR(40) NOT NULL,
 event_id VARCHAR(190) NOT NULL,
 event_type VARCHAR(100) NOT NULL,
 payload_sha256 CHAR(64) NOT NULL,
 processing_status ENUM('received','processed','ignored','failed') NOT NULL DEFAULT 'received',
 received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 processed_at DATETIME NULL,
 error_message VARCHAR(500) NULL,
 PRIMARY KEY(id), UNIQUE KEY uq_provider_event(provider_code,event_id), KEY idx_provider_event_status(processing_status,received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS courier_provider_events (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 provider_code VARCHAR(40) NOT NULL,
 event_id VARCHAR(190) NOT NULL,
 tracking_number VARCHAR(160) NULL,
 event_type VARCHAR(100) NOT NULL,
 payload_sha256 CHAR(64) NOT NULL,
 processing_status ENUM('received','processed','ignored','failed') NOT NULL DEFAULT 'received',
 received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 processed_at DATETIME NULL,
 error_message VARCHAR(500) NULL,
 PRIMARY KEY(id), UNIQUE KEY uq_courier_provider_event(provider_code,event_id), KEY idx_courier_event_status(processing_status,received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;