backend/database/migrations/034_repair_partial_cart_orders.sql

-- Repair: finish cart/orders/store selections after partial 019/020 on production.
-- Safe to re-run (IF NOT EXISTS / ON CONFLICT). Prefer running ordered migrations on a fresh DB.
-- Applied on api.meshkee.com 2026-07-21 when 019 created carts but failed before orders,
-- and 020 created store_items but failed before selections / cart remapping.

DO $$ BEGIN
  CREATE TYPE order_status AS ENUM (
    'pending','confirmed','processing','shipped','delivered','cancelled'
  );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE order_source AS ENUM ('website', 'admin');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS idx_store_item_variant_selections_option_id
  ON store_item_variant_selections (option_id);

CREATE TABLE IF NOT EXISTS cart_items (
  id                     BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  cart_id                BIGINT NOT NULL,
  store_item_variant_id  BIGINT NOT NULL,
  quantity               INT NOT NULL DEFAULT 1,
  created_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT cart_items_cart_id_fkey FOREIGN KEY (cart_id) REFERENCES carts (id) ON DELETE CASCADE,
  CONSTRAINT cart_items_store_item_variant_id_fkey
    FOREIGN KEY (store_item_variant_id) REFERENCES store_item_variants (id) ON DELETE CASCADE,
  CONSTRAINT cart_items_cart_store_item_variant_unique UNIQUE (cart_id, store_item_variant_id),
  CONSTRAINT cart_items_quantity_positive CHECK (quantity > 0)
);
CREATE INDEX IF NOT EXISTS idx_cart_items_cart_id ON cart_items (cart_id);
CREATE INDEX IF NOT EXISTS idx_cart_items_store_item_variant_id ON cart_items (store_item_variant_id);

CREATE TABLE IF NOT EXISTS orders (
  id                BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  business_id       BIGINT NOT NULL,
  user_id           BIGINT NOT NULL,
  order_number      VARCHAR(30) NOT NULL,
  status            order_status NOT NULL DEFAULT 'pending',
  source            order_source NOT NULL DEFAULT 'website',
  subtotal          NUMERIC(12, 2) NOT NULL DEFAULT 0,
  shipping_total    NUMERIC(12, 2) NOT NULL DEFAULT 0,
  discount_total    NUMERIC(12, 2) NOT NULL DEFAULT 0,
  total             NUMERIC(12, 2) NOT NULL DEFAULT 0,
  shipping_address  JSONB NOT NULL DEFAULT '{}',
  address_id        BIGINT,
  customer_notes    TEXT,
  admin_notes       TEXT,
  created_by        BIGINT,
  process_step_id   VARCHAR(64) NOT NULL DEFAULT 'processing',
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT orders_business_id_fkey FOREIGN KEY (business_id) REFERENCES businesses (id) ON DELETE CASCADE,
  CONSTRAINT orders_user_id_fkey FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
  CONSTRAINT orders_address_id_fkey FOREIGN KEY (address_id) REFERENCES addresses (id) ON DELETE SET NULL,
  CONSTRAINT orders_created_by_fkey FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT orders_business_order_number_unique UNIQUE (business_id, order_number),
  CONSTRAINT orders_subtotal_non_negative CHECK (subtotal >= 0),
  CONSTRAINT orders_shipping_total_non_negative CHECK (shipping_total >= 0),
  CONSTRAINT orders_discount_total_non_negative CHECK (discount_total >= 0),
  CONSTRAINT orders_total_non_negative CHECK (total >= 0)
);
CREATE INDEX IF NOT EXISTS idx_orders_business_created ON orders (business_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_orders_business_user ON orders (business_id, user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_orders_business_status ON orders (business_id, status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_orders_business_process_step ON orders (business_id, process_step_id);

CREATE TABLE IF NOT EXISTS order_items (
  id                     BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  order_id               BIGINT NOT NULL,
  store_item_variant_id  BIGINT,
  product_id             BIGINT NOT NULL,
  product_title          VARCHAR(255) NOT NULL,
  variant_sku            VARCHAR(100),
  unit_price             NUMERIC(12, 2) NOT NULL,
  compare_at_price       NUMERIC(12, 2),
  quantity               INT NOT NULL,
  line_total             NUMERIC(12, 2) NOT NULL,
  selections_snapshot    JSONB NOT NULL DEFAULT '[]',
  created_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT order_items_order_id_fkey FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE,
  CONSTRAINT order_items_store_item_variant_id_fkey
    FOREIGN KEY (store_item_variant_id) REFERENCES store_item_variants (id) ON DELETE SET NULL,
  CONSTRAINT order_items_product_id_fkey FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE RESTRICT,
  CONSTRAINT order_items_quantity_positive CHECK (quantity > 0),
  CONSTRAINT order_items_unit_price_non_negative CHECK (unit_price >= 0),
  CONSTRAINT order_items_line_total_non_negative CHECK (line_total >= 0)
);
CREATE INDEX IF NOT EXISTS idx_order_items_order_id ON order_items (order_id);
CREATE INDEX IF NOT EXISTS idx_order_items_store_item_variant_id ON order_items (store_item_variant_id);