-- 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 '[]';