19 lines
1.1 KiB
SQL
19 lines
1.1 KiB
SQL
-- Update orders table to support structured addresses and link to users
|
|
|
|
-- Add user_id to link orders to the users table (can be NULL for guest checkouts)
|
|
ALTER TABLE `orders` ADD COLUMN `user_id` INT NULL DEFAULT NULL AFTER `id`, ADD CONSTRAINT `fk_orders_users` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL;
|
|
|
|
-- Add structured shipping address fields
|
|
ALTER TABLE `orders`
|
|
ADD COLUMN `shipping_province` VARCHAR(100) NOT NULL AFTER `customer_phone`,
|
|
ADD COLUMN `shipping_city` VARCHAR(100) NOT NULL AFTER `shipping_province`,
|
|
ADD COLUMN `shipping_address_line` TEXT NOT NULL AFTER `shipping_city`,
|
|
ADD COLUMN `shipping_postal_code` VARCHAR(20) NOT NULL AFTER `shipping_address_line`;
|
|
|
|
-- Rename old columns to avoid confusion, but keep them for any old data
|
|
ALTER TABLE `orders`
|
|
CHANGE `customer_name` `billing_name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
|
|
CHANGE `customer_email` `billing_email` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
|
|
CHANGE `customer_address` `legacy_customer_address` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;
|
|
|