USE db_abos_v0.1;

-- =============================================================================
-- inventory_module.sql — Multi-warehouse Inventory foundation (Phase J).
--
-- Introduces a real stock ledger, replacing the single flat
-- dim_web_product_variants.stock_qty counter with per-warehouse quantities and
-- an immutable movement log — same "movement log is the source of truth,
-- rollup is a cache" discipline already used for money (fact_ledger_entries +
-- dim_wallets.balance).
--
-- dim_inventory_items gives every stockable thing one first-class identity,
-- whether it's a storefront variant (WEB_VARIANT), a back-office product item
-- (PRODUCT_ITEM, dim_product_items — already exists, see full_schema_snapshot.sql),
-- or a standalone inventory-only entry with no catalog record at all
-- (STANDALONE). CRM order line items (Phase K) and storefront order items both
-- resolve to a dim_inventory_items row before touching stock.
--
-- Safe to re-run: CREATE TABLE IF NOT EXISTS throughout, information_schema-
-- guarded ALTER for the new dim_web_products column, and backfill INSERTs are
-- all guarded with WHERE NOT EXISTS so re-running never duplicates rows.
-- =============================================================================

-- ── 1. dim_warehouses ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS dim_warehouses (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    site_id BIGINT NULL,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(50) NULL,
    address VARCHAR(500) NULL,
    is_default TINYINT NOT NULL DEFAULT 0,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    INDEX idx_dim_warehouses_tenant (tenant_id),
    INDEX idx_dim_warehouses_business (tenant_id, business_id, is_deleted),
    INDEX idx_dim_warehouses_site (site_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 2. dim_inventory_items ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS dim_inventory_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    sku VARCHAR(100) NULL,
    name VARCHAR(255) NOT NULL,
    item_kind VARCHAR(20) NOT NULL DEFAULT 'STANDALONE',
    linked_variant_id BIGINT NULL,
    linked_product_item_id INT NULL,
    reorder_point INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_dim_inventory_items_variant (linked_variant_id),
    INDEX idx_dim_inventory_items_tenant (tenant_id),
    INDEX idx_dim_inventory_items_business (tenant_id, business_id, is_deleted),
    INDEX idx_dim_inventory_items_product_item (linked_product_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 3. fact_inventory_stock ──────────────────────────────────────────────────
-- Maintained rollup — quantity_on_hand is only ever changed alongside a
-- fact_inventory_movements row in the same transaction (see InventoryService).
CREATE TABLE IF NOT EXISTS fact_inventory_stock (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    warehouse_id BIGINT NOT NULL,
    inventory_item_id BIGINT NOT NULL,
    quantity_on_hand INT NOT NULL DEFAULT 0,
    quantity_reserved INT NOT NULL DEFAULT 0,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    UNIQUE KEY uq_fact_inventory_stock (warehouse_id, inventory_item_id),
    INDEX idx_fact_inventory_stock_tenant (tenant_id),
    INDEX idx_fact_inventory_stock_item (inventory_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 4. fact_inventory_movements ──────────────────────────────────────────────
-- Immutable ledger. reference_type/reference_id optionally point at whatever
-- caused the movement (e.g. 'WEB_ORDER'/fact_web_orders.id,
-- 'CRM_ORDER'/fact_orders.id) — same polymorphic, no-FK approach already used
-- by fact_activities/fact_attachments/fact_links.
CREATE TABLE IF NOT EXISTS fact_inventory_movements (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    warehouse_id BIGINT NOT NULL,
    inventory_item_id BIGINT NOT NULL,
    movement_type VARCHAR(20) NOT NULL,
    quantity_delta INT NOT NULL,
    reference_type VARCHAR(30) NULL,
    reference_id BIGINT NULL,
    occurred_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    notes VARCHAR(500) NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    INDEX idx_fact_inventory_movements_tenant (tenant_id),
    INDEX idx_fact_inventory_movements_item (inventory_item_id, occurred_at),
    INDEX idx_fact_inventory_movements_warehouse (warehouse_id, occurred_at),
    INDEX idx_fact_inventory_movements_reference (reference_type, reference_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 5. dim_web_products.product_type ─────────────────────────────────────────
-- PHYSICAL (default, needs shipping + stock tracking), DIGITAL (no shipping,
-- no stock tracking — unlimited), SERVICE (no shipping, no stock tracking).
SET @db := DATABASE();

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_web_products' AND COLUMN_NAME='product_type'),
  'ALTER TABLE dim_web_products ADD COLUMN product_type VARCHAR(20) NOT NULL DEFAULT ''PHYSICAL''');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── 6. Backfill — one Main Warehouse per business/site combo already selling
--       physical variants, one inventory item per existing variant, one stock
--       row seeded from that variant's current stock_qty. ─────────────────────
INSERT INTO dim_warehouses (tenant_id, business_id, site_id, name, code, is_default, is_active, created_at, updated_at)
SELECT DISTINCT v.tenant_id, v.business_id, v.site_id, 'Main Warehouse', 'MAIN', 1, 1, NOW(), NOW()
FROM dim_web_product_variants v
WHERE v.is_deleted = 0
  AND NOT EXISTS (
    SELECT 1 FROM dim_warehouses w
    WHERE w.tenant_id = v.tenant_id
      AND w.site_id <=> v.site_id
      AND w.is_default = 1
  );

INSERT INTO dim_inventory_items (tenant_id, business_id, sku, name, item_kind, linked_variant_id, created_at, updated_at)
SELECT v.tenant_id, v.business_id, v.sku, CONCAT('Variant #', v.id), 'WEB_VARIANT', v.id, NOW(), NOW()
FROM dim_web_product_variants v
WHERE v.is_deleted = 0
  AND NOT EXISTS (SELECT 1 FROM dim_inventory_items i WHERE i.linked_variant_id = v.id);

INSERT INTO fact_inventory_stock (tenant_id, warehouse_id, inventory_item_id, quantity_on_hand, updated_at)
SELECT v.tenant_id, w.id, i.id, v.stock_qty, NOW()
FROM dim_web_product_variants v
JOIN dim_inventory_items i ON i.linked_variant_id = v.id
JOIN dim_warehouses w ON w.tenant_id = v.tenant_id AND w.site_id <=> v.site_id AND w.is_default = 1
WHERE v.is_deleted = 0
  AND NOT EXISTS (
    SELECT 1 FROM fact_inventory_stock s WHERE s.warehouse_id = w.id AND s.inventory_item_id = i.id
  );