30 lines
1.1 KiB
SQL
30 lines
1.1 KiB
SQL
-- Migration: Add offers tables and delivery slot offer_ids
|
|
-- Creates the offers feature: bundles of products at a fixed price
|
|
|
|
-- Step 1: Create offers table
|
|
CREATE TABLE `offers` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`name` text NOT NULL,
|
|
`short_description` text,
|
|
`long_description` text,
|
|
`price` text NOT NULL,
|
|
`market_price` text,
|
|
`images` text DEFAULT '[]',
|
|
`is_suspended` integer DEFAULT 0 NOT NULL,
|
|
`is_flash_enabled` integer DEFAULT 0 NOT NULL,
|
|
`added_on` text NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Step 2: Create offer_items junction table
|
|
CREATE TABLE `offer_items` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`offer_id` integer NOT NULL REFERENCES `offers`(`id`),
|
|
`product_id` integer NOT NULL REFERENCES `product_info`(`id`),
|
|
`quantity` text NOT NULL
|
|
);
|
|
|
|
-- Step 3: Unique index on offer + product
|
|
CREATE UNIQUE INDEX `unique_offer_product` ON `offer_items` (`offer_id`, `product_id`);
|
|
|
|
-- Step 4: Add offer_ids array to delivery slots
|
|
ALTER TABLE `delivery_slot_info` ADD COLUMN `offer_ids` text DEFAULT '[]';
|