0009_buyer_marketplace.sql (1253B)
1 CREATE TABLE buyer_carts ( 2 buyer_context_key TEXT PRIMARY KEY NOT NULL, 3 farm_id TEXT REFERENCES farms(id) ON DELETE SET NULL, 4 buyer_name TEXT NOT NULL DEFAULT '', 5 buyer_email TEXT NOT NULL DEFAULT '', 6 buyer_phone TEXT NOT NULL DEFAULT '', 7 buyer_order_note TEXT NOT NULL DEFAULT '', 8 updated_at TEXT NOT NULL 9 ); 10 11 CREATE TABLE buyer_cart_lines ( 12 buyer_context_key TEXT NOT NULL REFERENCES buyer_carts(buyer_context_key) ON DELETE CASCADE, 13 product_id TEXT NOT NULL REFERENCES products(id) ON DELETE CASCADE, 14 quantity INTEGER NOT NULL CHECK (quantity > 0), 15 updated_at TEXT NOT NULL, 16 PRIMARY KEY (buyer_context_key, product_id) 17 ); 18 19 CREATE INDEX idx_buyer_cart_lines_context_updated_at 20 ON buyer_cart_lines(buyer_context_key, updated_at DESC, product_id DESC); 21 22 ALTER TABLE orders ADD COLUMN buyer_context_key TEXT; 23 ALTER TABLE orders ADD COLUMN buyer_email TEXT NOT NULL DEFAULT ''; 24 ALTER TABLE orders ADD COLUMN buyer_phone TEXT NOT NULL DEFAULT ''; 25 ALTER TABLE orders ADD COLUMN buyer_order_note TEXT NOT NULL DEFAULT ''; 26 27 CREATE INDEX idx_orders_buyer_context_updated_at 28 ON orders(buyer_context_key, updated_at DESC, id DESC) 29 WHERE buyer_context_key IS NOT NULL AND trim(buyer_context_key) <> '';