-- Migration: split product_info into product_info (catalog) + product_skus + sku_features
-- and move all buyable references from product ids to sku ids.

-- PRAGMA foreign_keys=OFF;
PRAGMA defer_foreign_keys = on;

-- 1. Create new SKU tables.
CREATE TABLE `product_skus` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`product_id` integer NOT NULL,
	`name` text,
	`images` text,
	`is_offer` integer DEFAULT false NOT NULL,
	`is_combo_only` integer DEFAULT false NOT NULL,
	`is_deleted` integer DEFAULT false NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`product_id`) REFERENCES `product_info`(`id`) ON UPDATE no action ON DELETE no action
);

CREATE TABLE `sku_features` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`sku_id` integer NOT NULL,
	`feature_name` text,
	`feature_value` text NOT NULL,
	FOREIGN KEY (`sku_id`) REFERENCES `product_skus`(`id`) ON UPDATE no action ON DELETE no action
);

CREATE UNIQUE INDEX `unique_sku_feature_name` ON `sku_features` (`sku_id`,`feature_name`);

-- 2. Migrate each existing product into one default SKU plus a mandatory quantity feature.
INSERT INTO `product_skus` (
	`product_id`, `name`, `images`, `created_at`
)
SELECT
	`id`,
	NULL,
	`images`,
	`created_at`
FROM `product_info`;

INSERT INTO `sku_features` (`sku_id`, `feature_name`, `feature_value`)
SELECT
	`ps`.`id`,
	'quantity',
	(CAST(`pi`.`product_quantity` AS TEXT)) || COALESCE(`u`.`short_notation`, '')
FROM `product_info` `pi`
JOIN `product_skus` `ps` ON `ps`.`product_id` = `pi`.`id`
LEFT JOIN `units` `u` ON `u`.`id` = `pi`.`unit_id`;

-- 2b. Create product_market_stats to hold pricing/flash/stock per SKU, and backfill it.
CREATE TABLE `product_market_stats` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`sku_id` integer NOT NULL,
	`market_price` text,
	`our_price` text NOT NULL,
	`is_flash_available` integer DEFAULT false NOT NULL,
	`flash_price` text,
	`is_out_of_stock` integer DEFAULT false NOT NULL,
	`is_suspended` integer DEFAULT false NOT NULL,
	FOREIGN KEY (`sku_id`) REFERENCES `product_skus`(`id`) ON UPDATE no action ON DELETE no action
);

CREATE UNIQUE INDEX `product_market_stats_sku_id_unique` ON `product_market_stats` (`sku_id`);

INSERT INTO `product_market_stats` (`sku_id`, `market_price`, `our_price`, `is_flash_available`, `flash_price`, `is_out_of_stock`, `is_suspended`)
SELECT
	`ps`.`id`,
	`pi`.`market_price`,
	`pi`.`price`,
	`pi`.`is_flash_available`,
	`pi`.`flash_price`,
	`pi`.`is_out_of_stock`,
	`pi`.`is_suspended`
FROM `product_info` `pi`
JOIN `product_skus` `ps` ON `ps`.`product_id` = `pi`.`id`;

-- 3. Build a product_id -> sku_id mapping for downstream tables.
CREATE TABLE `__product_to_sku` (
	`product_id` integer PRIMARY KEY,
	`sku_id` integer NOT NULL
);

INSERT INTO `__product_to_sku` (`product_id`, `sku_id`)
SELECT `product_id`, `id` FROM `product_skus`;

-- 4. Recreate product_info with only catalog-level fields.
CREATE TABLE `__new_product_info` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`name` text NOT NULL,
	`short_description` text,
	`long_description` text,
	`store_id` integer,
	`increment_step` real DEFAULT 1 NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`store_id`) REFERENCES `store_info`(`id`) ON UPDATE no action ON DELETE no action
);

INSERT INTO `__new_product_info` (
	`id`, `name`, `short_description`, `long_description`, `store_id`, `increment_step`, `created_at`
)
SELECT
	`id`, `name`, `short_description`, `long_description`, `store_id`, `increment_step`, `created_at`
FROM `product_info`;

DROP TABLE `product_info`;
ALTER TABLE `__new_product_info` RENAME TO `product_info`;

-- 5. Recreate tables that previously referenced product_info.id so they now reference product_skus.id.
CREATE TABLE `__new_cart_items` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`user_id` integer NOT NULL,
	`sku_id` integer NOT NULL,
	`quantity` text NOT NULL,
	`added_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`sku_id`) REFERENCES `product_skus`(`id`) ON UPDATE no action ON DELETE no action
);

INSERT INTO `__new_cart_items` (`id`, `user_id`, `sku_id`, `quantity`, `added_at`)
SELECT
	`ci`.`id`, `ci`.`user_id`, `m`.`sku_id`, `ci`.`quantity`, `ci`.`added_at`
FROM `cart_items` `ci`
JOIN `__product_to_sku` `m` ON `m`.`product_id` = `ci`.`product_id`;

DROP TABLE `cart_items`;
ALTER TABLE `__new_cart_items` RENAME TO `cart_items`;
CREATE UNIQUE INDEX `unique_user_sku` ON `cart_items` (`user_id`,`sku_id`);

CREATE TABLE `__new_order_items` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`order_id` integer NOT NULL,
	`sku_id` integer NOT NULL,
	`quantity` text NOT NULL,
	`price` text NOT NULL,
	`discounted_price` text,
	`is_packaged` integer DEFAULT false NOT NULL,
	`is_package_verified` integer DEFAULT false NOT NULL,
	FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`sku_id`) REFERENCES `product_skus`(`id`) ON UPDATE no action ON DELETE no action
);

INSERT INTO `__new_order_items` (
	`id`, `order_id`, `sku_id`, `quantity`, `price`, `discounted_price`, `is_packaged`, `is_package_verified`
)
SELECT
	`oi`.`id`, `oi`.`order_id`, `m`.`sku_id`, `oi`.`quantity`, `oi`.`price`,
	`oi`.`discounted_price`, `oi`.`is_packaged`, `oi`.`is_package_verified`
FROM `order_items` `oi`
JOIN `__product_to_sku` `m` ON `m`.`product_id` = `oi`.`product_id`;

DROP TABLE `order_items`;
ALTER TABLE `__new_order_items` RENAME TO `order_items`;

CREATE TABLE `__new_special_deals` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`sku_id` integer NOT NULL,
	`quantity` text NOT NULL,
	`price` text NOT NULL,
	`valid_till` text NOT NULL,
	FOREIGN KEY (`sku_id`) REFERENCES `product_skus`(`id`) ON UPDATE no action ON DELETE no action
);

INSERT INTO `__new_special_deals` (`id`, `sku_id`, `quantity`, `price`, `valid_till`)
SELECT
	`sd`.`id`, `m`.`sku_id`, `sd`.`quantity`, `sd`.`price`, `sd`.`valid_till`
FROM `special_deals` `sd`
JOIN `__product_to_sku` `m` ON `m`.`product_id` = `sd`.`product_id`;

DROP TABLE `special_deals`;
ALTER TABLE `__new_special_deals` RENAME TO `special_deals`;

CREATE TABLE `__new_coupon_applicable_products` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`coupon_id` integer NOT NULL,
	`sku_id` integer NOT NULL,
	FOREIGN KEY (`coupon_id`) REFERENCES `coupons`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`sku_id`) REFERENCES `product_skus`(`id`) ON UPDATE no action ON DELETE no action
);

INSERT INTO `__new_coupon_applicable_products` (`id`, `coupon_id`, `sku_id`)
SELECT
	`cap`.`id`, `cap`.`coupon_id`, `m`.`sku_id`
FROM `coupon_applicable_products` `cap`
JOIN `__product_to_sku` `m` ON `m`.`product_id` = `cap`.`product_id`;

DROP TABLE `coupon_applicable_products`;
ALTER TABLE `__new_coupon_applicable_products` RENAME TO `coupon_applicable_products`;
CREATE UNIQUE INDEX `unique_coupon_sku` ON `coupon_applicable_products` (`coupon_id`,`sku_id`);

-- 5b. Recreate tag + group memberships to reference product_skus.id (SKU ids)
-- instead of product_info.id. ProductsSelector now saves SKU ids into these.
CREATE TABLE `__new_product_tags` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`product_id` integer NOT NULL,
	`tag_id` integer NOT NULL,
	`assigned_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`product_id`) REFERENCES `product_skus`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`tag_id`) REFERENCES `product_tag_info`(`id`) ON UPDATE no action ON DELETE no action
);

INSERT INTO `__new_product_tags` (`id`, `product_id`, `tag_id`, `assigned_at`)
SELECT
	`pt`.`id`, `m`.`sku_id`, `pt`.`tag_id`, `pt`.`assigned_at`
FROM `product_tags` `pt`
JOIN `__product_to_sku` `m` ON `m`.`product_id` = `pt`.`product_id`;

DROP TABLE `product_tags`;
ALTER TABLE `__new_product_tags` RENAME TO `product_tags`;
CREATE UNIQUE INDEX `unique_product_tag` ON `product_tags` (`product_id`,`tag_id`);

CREATE TABLE `__new_product_group_membership` (
	`product_id` integer NOT NULL,
	`group_id` integer NOT NULL,
	`added_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	PRIMARY KEY (`product_id`,`group_id`),
	FOREIGN KEY (`product_id`) REFERENCES `product_skus`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`group_id`) REFERENCES `product_group_info`(`id`) ON UPDATE no action ON DELETE no action
);

INSERT INTO `__new_product_group_membership` (`product_id`, `group_id`, `added_at`)
SELECT
	`m`.`sku_id`, `pgm`.`group_id`, `pgm`.`added_at`
FROM `product_group_membership` `pgm`
JOIN `__product_to_sku` `m` ON `m`.`product_id` = `pgm`.`product_id`;

DROP TABLE `product_group_membership`;
ALTER TABLE `__new_product_group_membership` RENAME TO `product_group_membership`;

-- 6. Remap JSON product id arrays to sku id arrays (before renaming the columns).
UPDATE `delivery_slot_info` AS `target`
SET `product_ids` = COALESCE(
	(SELECT json_group_array(`m`.`sku_id`)
	 FROM json_each(`target`.`product_ids`) AS `je`
	 JOIN `__product_to_sku` `m` ON `m`.`product_id` = `je`.`value`),
	CASE WHEN `target`.`product_ids` IS NULL THEN NULL ELSE '[]' END
);

UPDATE `home_banners` AS `target`
SET `product_ids` = COALESCE(
	(SELECT json_group_array(`m`.`sku_id`)
	 FROM json_each(`target`.`product_ids`) AS `je`
	 JOIN `__product_to_sku` `m` ON `m`.`product_id` = `je`.`value`),
	CASE WHEN `target`.`product_ids` IS NULL THEN NULL ELSE '[]' END
);

UPDATE `coupons` AS `target`
SET `product_ids` = COALESCE(
	(SELECT json_group_array(`m`.`sku_id`)
	 FROM json_each(`target`.`product_ids`) AS `je`
	 JOIN `__product_to_sku` `m` ON `m`.`product_id` = `je`.`value`),
	CASE WHEN `target`.`product_ids` IS NULL THEN NULL ELSE '[]' END
);

UPDATE `reserved_coupons` AS `target`
SET `product_ids` = COALESCE(
	(SELECT json_group_array(`m`.`sku_id`)
	 FROM json_each(`target`.`product_ids`) AS `je`
	 JOIN `__product_to_sku` `m` ON `m`.`product_id` = `je`.`value`),
	CASE WHEN `target`.`product_ids` IS NULL THEN NULL ELSE '[]' END
);

UPDATE `vendor_snippets` AS `target`
SET `product_ids` = COALESCE(
	(SELECT json_group_array(`m`.`sku_id`)
	 FROM json_each(`target`.`product_ids`) AS `je`
	 JOIN `__product_to_sku` `m` ON `m`.`product_id` = `je`.`value`),
	'[]'
);

-- 7. Rename JSON columns from product_ids to sku_ids.
ALTER TABLE `delivery_slot_info` RENAME COLUMN `product_ids` TO `sku_ids`;
ALTER TABLE `home_banners` RENAME COLUMN `product_ids` TO `sku_ids`;
ALTER TABLE `coupons` RENAME COLUMN `product_ids` TO `sku_ids`;
ALTER TABLE `reserved_coupons` RENAME COLUMN `product_ids` TO `sku_ids`;
ALTER TABLE `vendor_snippets` RENAME COLUMN `product_ids` TO `sku_ids`;

-- 8. Update popularItems in key_val_store from product IDs to SKU IDs.
UPDATE `key_val_store`
SET `value` = (
  SELECT json_group_array(`m`.`sku_id`)
  FROM json_each(`key_val_store`.`value`) AS `je`
  JOIN `__product_to_sku` `m` ON `m`.`product_id` = `je`.`value`
)
WHERE `key` = 'popularItems'
  AND `value` IS NOT NULL
  AND `value` LIKE '[%';

-- 9. Clean up helper table.
DROP TABLE `__product_to_sku`;

-- 10. Add product_type column and product_combos table for combo products.
ALTER TABLE `product_info` ADD COLUMN `product_type` text DEFAULT 'item';

CREATE TABLE `product_combos` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `combo_sku_id` integer NOT NULL,
  `sku_id` integer NOT NULL,
  FOREIGN KEY (`combo_sku_id`) REFERENCES `product_skus`(`id`) ON UPDATE no action ON DELETE no action,
  FOREIGN KEY (`sku_id`) REFERENCES `product_skus`(`id`) ON UPDATE no action ON DELETE no action
);

CREATE UNIQUE INDEX `unique_combo_sku_item` ON `product_combos` (`combo_sku_id`,`sku_id`);

-- 11. Add sort_order to product_tag_info for curated product ordering per tag.
ALTER TABLE `product_tag_info` ADD COLUMN `sort_order` text DEFAULT '[]';

-- PRAGMA foreign_keys=ON;
PRAGMA defer_foreign_keys = off;