0006_farm_rules_workspace.sql (1886B)
1 ALTER TABLE farms ADD COLUMN timezone TEXT NOT NULL DEFAULT 'UTC'; 2 ALTER TABLE farms ADD COLUMN currency_code TEXT NOT NULL DEFAULT 'USD'; 3 4 CREATE TABLE farm_operating_rules ( 5 farm_id TEXT PRIMARY KEY NOT NULL REFERENCES farms(id) ON DELETE CASCADE, 6 promise_lead_hours INTEGER NOT NULL CHECK (promise_lead_hours >= 0), 7 substitution_policy TEXT NOT NULL, 8 created_at TEXT NOT NULL, 9 updated_at TEXT NOT NULL 10 ); 11 12 CREATE TABLE pickup_locations ( 13 id TEXT PRIMARY KEY NOT NULL, 14 farm_id TEXT NOT NULL REFERENCES farms(id) ON DELETE CASCADE, 15 label TEXT NOT NULL, 16 address_line TEXT NOT NULL, 17 directions TEXT, 18 is_default INTEGER NOT NULL CHECK (is_default IN (0, 1)), 19 created_at TEXT NOT NULL, 20 updated_at TEXT NOT NULL 21 ); 22 23 CREATE UNIQUE INDEX idx_pickup_locations_default_per_farm 24 ON pickup_locations(farm_id) 25 WHERE is_default = 1; 26 CREATE INDEX idx_pickup_locations_farm_updated_at 27 ON pickup_locations(farm_id, updated_at DESC, id DESC); 28 29 ALTER TABLE fulfillment_windows 30 ADD COLUMN pickup_location_id TEXT REFERENCES pickup_locations(id) ON DELETE SET NULL; 31 ALTER TABLE fulfillment_windows ADD COLUMN label TEXT NOT NULL DEFAULT ''; 32 ALTER TABLE fulfillment_windows ADD COLUMN order_cutoff_at TEXT; 33 34 UPDATE fulfillment_windows 35 SET order_cutoff_at = starts_at 36 WHERE order_cutoff_at IS NULL OR trim(order_cutoff_at) = ''; 37 38 CREATE INDEX idx_fulfillment_windows_pickup_location 39 ON fulfillment_windows(pickup_location_id); 40 41 CREATE TABLE blackout_periods ( 42 id TEXT PRIMARY KEY NOT NULL, 43 farm_id TEXT NOT NULL REFERENCES farms(id) ON DELETE CASCADE, 44 label TEXT NOT NULL, 45 starts_at TEXT NOT NULL, 46 ends_at TEXT NOT NULL, 47 created_at TEXT NOT NULL, 48 updated_at TEXT NOT NULL, 49 CHECK (ends_at > starts_at) 50 ); 51 52 CREATE INDEX idx_blackout_periods_farm_starts_at 53 ON blackout_periods(farm_id, starts_at, id);