0020_declined_order_status.sql (4643B)
1 DROP INDEX IF EXISTS idx_order_lines_order_sort; 2 DROP INDEX IF EXISTS idx_buyer_order_coordination_context_state_updated_at; 3 DROP INDEX IF EXISTS idx_buyer_order_coordination_state_updated_at; 4 DROP INDEX IF EXISTS idx_orders_farm_status; 5 DROP INDEX IF EXISTS idx_orders_farm_window_status_updated_at; 6 DROP INDEX IF EXISTS idx_orders_buyer_context_updated_at; 7 8 ALTER TABLE order_lines RENAME TO order_lines_legacy; 9 ALTER TABLE buyer_order_coordination_records RENAME TO buyer_order_coordination_records_legacy; 10 ALTER TABLE orders RENAME TO orders_legacy; 11 12 CREATE TABLE orders ( 13 id TEXT PRIMARY KEY NOT NULL, 14 farm_id TEXT NOT NULL REFERENCES farms(id) ON DELETE CASCADE, 15 fulfillment_window_id TEXT REFERENCES fulfillment_windows(id) ON DELETE SET NULL, 16 order_number TEXT NOT NULL, 17 customer_display_name TEXT NOT NULL, 18 status TEXT NOT NULL CHECK ( 19 status IN ('needs_action', 'scheduled', 'packed', 'completed', 'declined') 20 ), 21 updated_at TEXT NOT NULL, 22 buyer_context_key TEXT, 23 buyer_email TEXT NOT NULL DEFAULT '', 24 buyer_phone TEXT NOT NULL DEFAULT '', 25 buyer_order_note TEXT NOT NULL DEFAULT '' 26 ); 27 28 INSERT INTO orders ( 29 id, 30 farm_id, 31 fulfillment_window_id, 32 order_number, 33 customer_display_name, 34 status, 35 updated_at, 36 buyer_context_key, 37 buyer_email, 38 buyer_phone, 39 buyer_order_note 40 ) 41 SELECT 42 id, 43 farm_id, 44 fulfillment_window_id, 45 order_number, 46 customer_display_name, 47 status, 48 updated_at, 49 buyer_context_key, 50 buyer_email, 51 buyer_phone, 52 buyer_order_note 53 FROM orders_legacy; 54 55 CREATE TABLE order_lines ( 56 id TEXT PRIMARY KEY NOT NULL, 57 order_id TEXT NOT NULL REFERENCES orders(id) ON DELETE CASCADE, 58 title TEXT NOT NULL, 59 quantity_value INTEGER NOT NULL CHECK (quantity_value >= 0), 60 quantity_unit_label TEXT NOT NULL DEFAULT '', 61 quantity_display TEXT NOT NULL, 62 sort_index INTEGER NOT NULL DEFAULT 0, 63 listing_bin_id TEXT, 64 unit_price_minor_units INTEGER CHECK ( 65 unit_price_minor_units IS NULL OR unit_price_minor_units >= 0 66 ), 67 price_currency TEXT NOT NULL DEFAULT 'USD', 68 farm_key TEXT, 69 listing_addr TEXT, 70 listing_event_id TEXT, 71 seller_pubkey TEXT, 72 listing_relays_json TEXT 73 ); 74 75 INSERT INTO order_lines ( 76 id, 77 order_id, 78 title, 79 quantity_value, 80 quantity_unit_label, 81 quantity_display, 82 sort_index, 83 listing_bin_id, 84 unit_price_minor_units, 85 price_currency, 86 farm_key, 87 listing_addr, 88 listing_event_id, 89 seller_pubkey, 90 listing_relays_json 91 ) 92 SELECT 93 id, 94 order_id, 95 title, 96 quantity_value, 97 quantity_unit_label, 98 quantity_display, 99 sort_index, 100 listing_bin_id, 101 unit_price_minor_units, 102 price_currency, 103 farm_key, 104 listing_addr, 105 listing_event_id, 106 seller_pubkey, 107 listing_relays_json 108 FROM order_lines_legacy; 109 110 CREATE TABLE buyer_order_coordination_records ( 111 order_id TEXT PRIMARY KEY NOT NULL REFERENCES orders(id) ON DELETE CASCADE, 112 buyer_context_key TEXT NOT NULL, 113 record_id TEXT, 114 state TEXT NOT NULL CHECK (state IN ('pending', 'synced', 'failed')), 115 payload_json TEXT, 116 attempt_count INTEGER NOT NULL DEFAULT 0 CHECK (attempt_count >= 0), 117 last_error_message TEXT, 118 created_at TEXT NOT NULL, 119 updated_at TEXT NOT NULL, 120 synced_at TEXT 121 ); 122 123 INSERT INTO buyer_order_coordination_records ( 124 order_id, 125 buyer_context_key, 126 record_id, 127 state, 128 payload_json, 129 attempt_count, 130 last_error_message, 131 created_at, 132 updated_at, 133 synced_at 134 ) 135 SELECT 136 order_id, 137 buyer_context_key, 138 record_id, 139 state, 140 payload_json, 141 attempt_count, 142 last_error_message, 143 created_at, 144 updated_at, 145 synced_at 146 FROM buyer_order_coordination_records_legacy; 147 148 CREATE INDEX idx_orders_farm_status ON orders(farm_id, status); 149 CREATE INDEX idx_orders_farm_window_status_updated_at 150 ON orders(farm_id, fulfillment_window_id, status, updated_at DESC, id DESC); 151 CREATE INDEX idx_orders_buyer_context_updated_at 152 ON orders(buyer_context_key, updated_at DESC, id DESC) 153 WHERE buyer_context_key IS NOT NULL AND trim(buyer_context_key) <> ''; 154 CREATE INDEX idx_order_lines_order_sort 155 ON order_lines(order_id, sort_index, id); 156 CREATE INDEX idx_buyer_order_coordination_context_state_updated_at 157 ON buyer_order_coordination_records(buyer_context_key, state, updated_at); 158 CREATE INDEX idx_buyer_order_coordination_state_updated_at 159 ON buyer_order_coordination_records(state, updated_at); 160 161 DROP TABLE order_lines_legacy; 162 DROP TABLE buyer_order_coordination_records_legacy; 163 DROP TABLE orders_legacy;