File manager - Edit - /home/webapp69.cm.in.th/u69319090041/Shop41/migration_v3_vendor_payouts.sql
Back
-- ============================================================ -- MIGRATION SCRIPT: v3 Vendor Payouts & Enhanced Tracking -- ============================================================ SET FOREIGN_KEY_CHECKS = 0; -- 1. Create vendor payouts table if not exists CREATE TABLE IF NOT EXISTS `vendor_payouts` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `vendor_id` INT NOT NULL, `amount` DECIMAL(12,2) NOT NULL, `platform_fee` DECIMAL(10,2) DEFAULT 0.00, `net_amount` DECIMAL(12,2) NOT NULL, `bank_name` VARCHAR(100) NOT NULL, `bank_account` VARCHAR(50) NOT NULL, `bank_account_name` VARCHAR(100) NOT NULL, `status` ENUM('pending','processing','transferred','rejected') DEFAULT 'pending', `proof_image` VARCHAR(255) DEFAULT NULL, `notes` TEXT DEFAULT NULL, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, `transferred_at` DATETIME DEFAULT NULL, FOREIGN KEY (`vendor_id`) REFERENCES `vendors`(`id`) ON DELETE CASCADE, INDEX (`vendor_id`), INDEX (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 2. Add extra tracking columns to vendor_orders ALTER TABLE `vendor_orders` ADD COLUMN IF NOT EXISTS `carrier_name` VARCHAR(100) DEFAULT NULL AFTER `tracking_number`, ADD COLUMN IF NOT EXISTS `units_sold` INT DEFAULT 0 AFTER `vendor_earning`, ADD COLUMN IF NOT EXISTS `shipped_at` DATETIME DEFAULT NULL AFTER `updated_at`; -- 3. Add index for performance on vendor_orders date and status ALTER TABLE `vendor_orders` ADD INDEX IF NOT EXISTS `idx_vendor_created_status` (`vendor_id`, `created_at`, `status`); -- 4. Add low stock alert setting column to vendors table ALTER TABLE `vendors` ADD COLUMN IF NOT EXISTS `low_stock_threshold` INT DEFAULT 5 AFTER `commission_rate`; SET FOREIGN_KEY_CHECKS = 1; SELECT '✅ Migration v3 Complete: Vendor Payouts and Analytics structures updated.' AS migration_status;
| ver. 1.4 |
Github
|
.
| PHP 7.4.33 | Generation time: 0.24 |
proxy
|
phpinfo
|
Settings