backend/database/migrations/020_store_items_and_variants.sql

-- Meshkee CMS — store items (one per product) and store item variants (purchasable SKUs)
-- Replaces product_variants / product_variant_selections

-- ---------------------------------------------------------------------------
-- store_items (one listing per product in the shop)
-- ---------------------------------------------------------------------------
CREATE TABLE store_items (
  id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  business_id BIGINT NOT NULL,
  product_id  BIGINT NOT NULL,
  is_active   BOOLEAN NOT NULL DEFAULT TRUE,
  sort_order  INTEGER NOT NULL DEFAULT 0,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT store_items_business_id_fkey
    FOREIGN KEY (business_id) REFERENCES businesses (id) ON DELETE CASCADE,
  CONSTRAINT store_items_product_id_fkey
    FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE,
  CONSTRAINT store_items_business_product_unique UNIQUE (business_id, product_id)
);

CREATE INDEX idx_store_items_business_id ON store_items (business_id);
CREATE INDEX idx_store_items_product_id ON store_items (product_id);

CREATE TRIGGER store_items_set_updated_at
  BEFORE UPDATE ON store_items
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

-- ---------------------------------------------------------------------------
-- store_item_variants (purchasable combinations with price & stock)
-- ---------------------------------------------------------------------------
CREATE TABLE store_item_variants (
  id               BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  store_item_id    BIGINT NOT NULL,
  business_id      BIGINT NOT NULL,
  sku              VARCHAR(100),
  price            NUMERIC(12, 2),
  compare_at_price NUMERIC(12, 2),
  stock_quantity   INTEGER,
  is_active        BOOLEAN NOT NULL DEFAULT TRUE,
  is_festival      BOOLEAN NOT NULL DEFAULT FALSE,
  reward_points    INTEGER,
  sort_order       INTEGER NOT NULL DEFAULT 0,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  legacy_product_variant_id BIGINT,

  CONSTRAINT store_item_variants_store_item_id_fkey
    FOREIGN KEY (store_item_id) REFERENCES store_items (id) ON DELETE CASCADE,
  CONSTRAINT store_item_variants_business_id_fkey
    FOREIGN KEY (business_id) REFERENCES businesses (id) ON DELETE CASCADE,
  CONSTRAINT store_item_variants_stock_non_negative
    CHECK (stock_quantity IS NULL OR stock_quantity >= 0),
  CONSTRAINT store_item_variants_reward_points_non_negative
    CHECK (reward_points IS NULL OR reward_points >= 0)
);

CREATE INDEX idx_store_item_variants_store_item_id ON store_item_variants (store_item_id);
CREATE INDEX idx_store_item_variants_business_id ON store_item_variants (business_id);
CREATE UNIQUE INDEX idx_store_item_variants_legacy_id
  ON store_item_variants (legacy_product_variant_id)
  WHERE legacy_product_variant_id IS NOT NULL;

CREATE TRIGGER store_item_variants_set_updated_at
  BEFORE UPDATE ON store_item_variants
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

-- ---------------------------------------------------------------------------
-- store_item_variant_selections
-- ---------------------------------------------------------------------------
CREATE TABLE store_item_variant_selections (
  variant_id   BIGINT NOT NULL,
  variation_id BIGINT NOT NULL,
  option_id    BIGINT NOT NULL,

  CONSTRAINT store_item_variant_selections_pkey PRIMARY KEY (variant_id, variation_id),
  CONSTRAINT store_item_variant_selections_variant_id_fkey
    FOREIGN KEY (variant_id) REFERENCES store_item_variants (id) ON DELETE CASCADE,
  CONSTRAINT store_item_variant_selections_variation_id_fkey
    FOREIGN KEY (variation_id) REFERENCES category_variations (id) ON DELETE RESTRICT,
  CONSTRAINT store_item_variant_selections_option_id_fkey
    FOREIGN KEY (option_id) REFERENCES category_variation_options (id) ON DELETE RESTRICT,
  CONSTRAINT store_item_variant_selections_unique_option
    UNIQUE (variant_id, option_id)
);

CREATE INDEX idx_store_item_variant_selections_option_id
  ON store_item_variant_selections (option_id);

-- ---------------------------------------------------------------------------
-- migrate product_variants → store_items + store_item_variants
-- ---------------------------------------------------------------------------
INSERT INTO store_items (business_id, product_id, is_active, sort_order, created_at, updated_at)
SELECT DISTINCT
  pv.business_id,
  pv.product_id,
  TRUE,
  0,
  NOW(),
  NOW()
FROM product_variants pv;

INSERT INTO store_item_variants (
  store_item_id,
  business_id,
  sku,
  price,
  compare_at_price,
  stock_quantity,
  is_active,
  is_festival,
  reward_points,
  sort_order,
  created_at,
  updated_at,
  legacy_product_variant_id
)
SELECT
  si.id,
  pv.business_id,
  pv.sku,
  pv.price,
  pv.compare_at_price,
  pv.stock_quantity,
  pv.is_active,
  pv.is_festival,
  pv.reward_points,
  pv.sort_order,
  pv.created_at,
  pv.updated_at,
  pv.id
FROM product_variants pv
JOIN store_items si
  ON si.business_id = pv.business_id
 AND si.product_id = pv.product_id;

INSERT INTO store_item_variant_selections (variant_id, variation_id, option_id)
SELECT
  siv.id,
  pvs.variation_id,
  pvs.option_id
FROM product_variant_selections pvs
JOIN store_item_variants siv
  ON siv.legacy_product_variant_id = pvs.variant_id;

-- ---------------------------------------------------------------------------
-- repoint cart_items and order_items to store_item_variants
-- ---------------------------------------------------------------------------
ALTER TABLE cart_items DROP CONSTRAINT cart_items_variant_id_fkey;
ALTER TABLE cart_items RENAME COLUMN variant_id TO store_item_variant_id;

UPDATE cart_items ci
SET store_item_variant_id = siv.id
FROM store_item_variants siv
WHERE siv.legacy_product_variant_id = ci.store_item_variant_id;

ALTER TABLE cart_items
  ADD CONSTRAINT cart_items_store_item_variant_id_fkey
    FOREIGN KEY (store_item_variant_id) REFERENCES store_item_variants (id) ON DELETE CASCADE;

ALTER TABLE cart_items
  DROP CONSTRAINT IF EXISTS cart_items_cart_variant_unique;

ALTER TABLE cart_items
  ADD CONSTRAINT cart_items_cart_store_item_variant_unique
    UNIQUE (cart_id, store_item_variant_id);

DROP INDEX IF EXISTS idx_cart_items_variant_id;
CREATE INDEX idx_cart_items_store_item_variant_id
  ON cart_items (store_item_variant_id);

ALTER TABLE order_items DROP CONSTRAINT order_items_variant_id_fkey;
ALTER TABLE order_items RENAME COLUMN variant_id TO store_item_variant_id;

UPDATE order_items oi
SET store_item_variant_id = siv.id
FROM store_item_variants siv
WHERE siv.legacy_product_variant_id = oi.store_item_variant_id;

ALTER TABLE order_items
  ADD CONSTRAINT order_items_store_item_variant_id_fkey
    FOREIGN KEY (store_item_variant_id) REFERENCES store_item_variants (id) ON DELETE SET NULL;

DROP INDEX IF EXISTS idx_order_items_variant_id;
CREATE INDEX idx_order_items_store_item_variant_id
  ON order_items (store_item_variant_id);

-- ---------------------------------------------------------------------------
-- drop legacy tables
-- ---------------------------------------------------------------------------
DROP TABLE product_variant_selections;
DROP TABLE product_variants;

ALTER TABLE store_item_variants DROP COLUMN legacy_product_variant_id;
DROP INDEX IF EXISTS idx_store_item_variants_legacy_id;