File manager - Edit - /home/webapp69.cm.in.th/u69319090028/Shop/database/migrations/review_system.sql
Back
-- ============================================================ -- CARGOO Review & Rating System Migration -- Run against: cargoo_shop -- ============================================================ -- 1. Reviews Table CREATE TABLE IF NOT EXISTS `reviews` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `order_id` INT UNSIGNED NOT NULL COMMENT 'FK to orders', `order_item_id` INT UNSIGNED NOT NULL COMMENT 'FK to order_items - unique per item', `product_id` INT UNSIGNED NOT NULL COMMENT 'FK to products', `store_id` INT UNSIGNED NOT NULL COMMENT 'FK to stores', `user_id` INT UNSIGNED NOT NULL COMMENT 'FK to users (buyer)', `rating` TINYINT UNSIGNED NOT NULL COMMENT '1 to 5 stars', `review_text` TEXT DEFAULT NULL, `status` ENUM('active','hidden','removed') NOT NULL DEFAULT 'active', `is_anonymous` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = mask username', `admin_notes` 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_review_order_item` (`order_item_id`), KEY `idx_review_product_status` (`product_id`, `status`), KEY `idx_review_store_status` (`store_id`, `status`), KEY `idx_review_user` (`user_id`), KEY `idx_review_order` (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Product customer reviews'; -- 2. Review Images Table CREATE TABLE IF NOT EXISTS `review_images` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `review_id` INT UNSIGNED NOT NULL COMMENT 'FK to reviews', `image_path` VARCHAR(255) NOT NULL, `sort_order` TINYINT UNSIGNED NOT NULL DEFAULT 0, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_ri_review` (`review_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Customer review attachment images'; -- 3. Cache Columns for Products ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `rating_avg` DECIMAL(3,2) NOT NULL DEFAULT 0.00 COMMENT 'Cached average rating', ADD COLUMN IF NOT EXISTS `rating_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Cached active review count'; -- 4. Cache Columns for Stores ALTER TABLE `stores` ADD COLUMN IF NOT EXISTS `rating_avg` DECIMAL(3,2) NOT NULL DEFAULT 0.00 COMMENT 'Cached store average rating', ADD COLUMN IF NOT EXISTS `rating_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Cached store review count'; -- 5. Extend Reports Table to support 'review' report_type ALTER TABLE `reports` MODIFY COLUMN `report_type` ENUM('product','seller','order','review') NOT NULL;
| ver. 1.4 |
Github
|
.
| PHP 7.4.33 | Generation time: 0.24 |
proxy
|
phpinfo
|
Settings