-- ============================================================
-- Wedding Set / Multi-Product Set — Database Migration Script
-- Date: 2026-06-29
-- ============================================================
-- STEP 1: Add item_type flag to designs table
--   Default = 1 (Single Item) → all existing rows unaffected
-- ============================================================
ALTER TABLE `designs`
  ADD COLUMN `item_type` TINYINT(1) NOT NULL DEFAULT 1
  COMMENT '1 = Single Item (existing flow), 2 = Set Items (Wedding Set etc.)'
  AFTER `design_id`;


-- ============================================================
-- STEP 2: Create new design_set_products table
--   Stores sub-products (Ring, Bangle, etc.) for set designs
-- ============================================================
CREATE TABLE `design_set_products` (
  `set_product_id`       INT           NOT NULL AUTO_INCREMENT,
  `set_product_rfrnc_id` INT           NOT NULL DEFAULT 0,
  `design_id`            INT           NOT NULL  COMMENT 'FK → designs.design_id',
  `set_product_name`     VARCHAR(100)  NOT NULL  COMMENT 'e.g. Ring, Bangle, Earring',
  `set_product_order`    INT           NOT NULL DEFAULT 1  COMMENT 'Display order within the set',
  `gross_wgt`            DECIMAL(10,3) NOT NULL DEFAULT 0.000  COMMENT 'Gross weight of this sub-product',
  `net_weight`           DECIMAL(10,3) NOT NULL DEFAULT 0.000  COMMENT 'Net weight of this sub-product',
  `st_wgt`               DECIMAL(10,3) NOT NULL DEFAULT 0.000  COMMENT 'Total stone weight for this sub-product',
  `no_of_st`             INT           NOT NULL DEFAULT 0  COMMENT 'Total no. of stones for this sub-product',
  `role_status`          TINYINT(1)    NOT NULL DEFAULT 1  COMMENT '1=Active, 0=Inactive',
  `action_type`          VARCHAR(10)   NOT NULL DEFAULT 'inst'  COMMENT 'inst/edit/dlt',
  `active_row`           VARCHAR(3)    NOT NULL DEFAULT 'Lat'   COMMENT 'Lat=Latest, Old=Old',
  `post_by`              INT           NOT NULL DEFAULT 0,
  `post_dt`              DATETIME      NOT NULL,
  `update_by`            INT           NOT NULL DEFAULT 0,
  `update_dt`            DATETIME      NULL DEFAULT NULL,
  PRIMARY KEY (`set_product_id`),
  KEY `idx_dsp_design_id` (`design_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Sub-products within a Set design (Wedding Set, etc.)';

-- ============================================================
-- STEP 3: Add set_product_id column to design_det table
--   NULL  = Single Item row (existing rows, unchanged)
--   Value = FK → design_set_products.set_product_id
-- ============================================================
ALTER TABLE `design_det`
  ADD COLUMN `set_product_id` INT NULL DEFAULT NULL
  COMMENT 'NULL=Single Item; NOT NULL=FK → design_set_products.set_product_id'
  AFTER `design_id`;