File manager - Edit - /home/webapp69.cm.in.th/u69319090028/Shop/database/migrations/payment_system.sql
Back
-- ============================================================ -- CARGOO Payment System Migration -- Run once against: cargoo_shop -- Depends on: users, orders tables -- ============================================================ -- ============================================================ -- 1. Payment Accounts (Admin-managed receiving accounts) -- ============================================================ CREATE TABLE IF NOT EXISTS `payment_accounts` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `method` ENUM('PromptPay','BankTransfer') NOT NULL, `account_name` VARCHAR(120) NOT NULL COMMENT 'Name on account', `account_number` VARCHAR(80) NOT NULL COMMENT 'PromptPay ID or bank account number', `bank_name` VARCHAR(80) DEFAULT NULL COMMENT 'Bank name (BankTransfer only)', `bank_branch` VARCHAR(120) DEFAULT NULL COMMENT 'Branch name (optional)', `qr_image` VARCHAR(255) DEFAULT NULL COMMENT 'QR code image filename for PromptPay', `is_default` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = default for this method', `is_active` TINYINT(1) NOT NULL DEFAULT 1, `note` VARCHAR(255) DEFAULT NULL COMMENT 'Internal note', `created_by` INT UNSIGNED NOT NULL COMMENT 'Admin user id', `updated_by` INT UNSIGNED DEFAULT NULL COMMENT 'Admin user id', `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_pa_method` (`method`, `is_active`), KEY `idx_pa_default` (`method`, `is_default`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Platform payment receiving accounts managed by Admin'; -- ============================================================ -- 2. Payments (1:1 with orders — Payment Header) -- ============================================================ CREATE TABLE IF NOT EXISTS `payments` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `order_id` INT UNSIGNED NOT NULL, `order_no` VARCHAR(20) NOT NULL, `customer_id` INT UNSIGNED NOT NULL, `amount` DECIMAL(12,2) NOT NULL COMMENT 'Total amount to pay', `payment_method` ENUM('PromptPay','BankTransfer','COD') NOT NULL, `payment_status` ENUM('pending','awaiting_verification','confirmed','failed','expired','cancelled','refund_pending','refunded') NOT NULL DEFAULT 'pending', `payment_account_id` INT UNSIGNED DEFAULT NULL COMMENT 'Account customer should pay to', `idempotency_key` VARCHAR(64) NOT NULL COMMENT 'Unique key to prevent double payment', `expires_at` DATETIME NOT NULL COMMENT 'Payment deadline', `confirmed_at` DATETIME DEFAULT NULL, `confirmed_by` INT UNSIGNED DEFAULT NULL COMMENT 'Admin user id', `cancelled_at` DATETIME DEFAULT NULL, `note` TEXT DEFAULT 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 `uniq_payment_order` (`order_id`), UNIQUE KEY `uniq_idempotency_key` (`idempotency_key`), KEY `idx_payment_order_no` (`order_no`), KEY `idx_payment_customer` (`customer_id`), KEY `idx_payment_status` (`payment_status`), KEY `idx_payment_expires` (`expires_at`), KEY `idx_payment_method` (`payment_method`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Payment header — one per order'; -- ============================================================ -- 3. Payment Attempts (Multiple per Payment — slip uploads/retries) -- ============================================================ CREATE TABLE IF NOT EXISTS `payment_attempts` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `payment_id` INT UNSIGNED NOT NULL, `attempt_no` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Sequence number within payment', `slip_image` VARCHAR(255) DEFAULT NULL COMMENT 'Uploaded slip filename', `slip_uploaded_at` DATETIME DEFAULT NULL, `amount_claimed` DECIMAL(12,2) DEFAULT NULL COMMENT 'Amount shown on slip', `status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending', `submitted_by` INT UNSIGNED NOT NULL COMMENT 'customer user id', `reject_reason` TEXT DEFAULT NULL, `note` TEXT DEFAULT NULL COMMENT 'Customer note on this attempt', `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_pa_payment` (`payment_id`), KEY `idx_pa_status` (`status`), KEY `idx_pa_submitter` (`submitted_by`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Individual payment slip submission attempts'; -- ============================================================ -- 4. Payment Verifications (Audit trail for Admin verify actions) -- ============================================================ CREATE TABLE IF NOT EXISTS `payment_verifications` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `payment_id` INT UNSIGNED NOT NULL, `payment_attempt_id` INT UNSIGNED NOT NULL, `verified_by` INT UNSIGNED NOT NULL COMMENT 'Admin user id', `action` ENUM('approved','rejected') NOT NULL, `reason` TEXT DEFAULT NULL, `ip_address` VARCHAR(45) DEFAULT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_pv_payment` (`payment_id`), KEY `idx_pv_attempt` (`payment_attempt_id`), KEY `idx_pv_admin` (`verified_by`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Admin verification audit trail per payment attempt'; -- ============================================================ -- 5. Payment History (State-change event log) -- ============================================================ CREATE TABLE IF NOT EXISTS `payment_history` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `payment_id` INT UNSIGNED NOT NULL, `from_status` VARCHAR(40) NOT NULL, `to_status` VARCHAR(40) NOT NULL, `actor_id` INT UNSIGNED DEFAULT NULL, `actor_type` ENUM('customer','admin','system') NOT NULL DEFAULT 'system', `note` TEXT DEFAULT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_ph_payment` (`payment_id`), KEY `idx_ph_created` (`created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Immutable log of payment status transitions'; -- ============================================================ -- 6. Refunds -- ============================================================ CREATE TABLE IF NOT EXISTS `refunds` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `payment_id` INT UNSIGNED NOT NULL, `order_id` INT UNSIGNED NOT NULL, `order_no` VARCHAR(20) NOT NULL, `requested_by` INT UNSIGNED NOT NULL COMMENT 'customer user id', `amount` DECIMAL(12,2) NOT NULL, `reason` TEXT NOT NULL, `status` ENUM('pending','approved','rejected','completed') NOT NULL DEFAULT 'pending', `reviewed_by` INT UNSIGNED DEFAULT NULL COMMENT 'Admin user id', `reviewed_at` DATETIME DEFAULT NULL, `review_note` TEXT DEFAULT NULL, `completed_at` DATETIME DEFAULT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_refund_payment` (`payment_id`), KEY `idx_refund_order` (`order_id`), KEY `idx_refund_status` (`status`), KEY `idx_refund_requester` (`requested_by`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Refund requests and their Admin review status'; -- ============================================================ -- 7. Add payment_id reference column to orders (optional link back) -- ============================================================ ALTER TABLE `orders` ADD COLUMN IF NOT EXISTS `payment_id` INT UNSIGNED DEFAULT NULL COMMENT 'FK to payments.id — populated after payment record created';
| ver. 1.4 |
Github
|
.
| PHP 7.4.33 | Generation time: 0.24 |
proxy
|
phpinfo
|
Settings