demo_data.sql

-- =========================================================
-- OMS v4 demo seed data
-- Safe for an existing database.
-- Adds/upserts demo master data and inventory, then inserts
-- a demo order/history/comment/transaction batch only once.
-- =========================================================

SET search_path TO oms_schema;

BEGIN;

-- =========================================================
-- ORDER STATUS TRANSITIONS
-- =========================================================

INSERT INTO order_status_transition (order_type, old_status, new_status)
VALUES
    ('SALES', 'CREATED', 'PENDING'),
    ('SALES', 'CREATED', 'CANCELLED'),
    ('SALES', 'PENDING', 'COMPLETED'),
    ('SALES', 'PENDING', 'CANCELLED'),

    ('REPLENISHMENT', 'CREATED', 'APPROVED'),
    ('REPLENISHMENT', 'CREATED', 'REJECTED'),
    ('REPLENISHMENT', 'CREATED', 'CANCELLED'),
    ('REPLENISHMENT', 'APPROVED', 'PACKAGING'),
    ('REPLENISHMENT', 'APPROVED', 'CANCELLED'),
    ('REPLENISHMENT', 'PACKAGING', 'ON_THE_WAY'),
    ('REPLENISHMENT', 'PACKAGING', 'CANCELLED'),
    ('REPLENISHMENT', 'ON_THE_WAY', 'COMPLETED'),
    ('REPLENISHMENT', 'ON_THE_WAY', 'CANCELLED'),

    ('TRANSFER', 'CREATED', 'APPROVED'),
    ('TRANSFER', 'CREATED', 'REJECTED'),
    ('TRANSFER', 'CREATED', 'CANCELLED'),
    ('TRANSFER', 'APPROVED', 'PACKAGING'),
    ('TRANSFER', 'APPROVED', 'CANCELLED'),
    ('TRANSFER', 'PACKAGING', 'ON_THE_WAY'),
    ('TRANSFER', 'PACKAGING', 'CANCELLED'),
    ('TRANSFER', 'ON_THE_WAY', 'COMPLETED'),
    ('TRANSFER', 'ON_THE_WAY', 'CANCELLED')
ON CONFLICT (order_type, old_status, new_status) DO NOTHING;

-- =========================================================
-- STORES
-- Stores are guarded by name because name is not unique in the schema.
-- =========================================================

WITH store_seed (name, country, city, state, zip_code, address_line_1, address_line_2) AS (
    SELECT
        format('OMS Demo Store %s', lpad(gs::text, 2, '0')),
        'Russia',
        (ARRAY['Moscow','Saint Petersburg','Kazan','Novosibirsk','Yekaterinburg','Sochi','Rostov-on-Don','Samara','Perm','Ufa'])[((gs - 1) % 10) + 1],
        (ARRAY['Moscow','Saint Petersburg','Tatarstan','Novosibirsk Oblast','Sverdlovsk Oblast','Krasnodar Krai','Rostov Oblast','Samara Oblast','Perm Krai','Bashkortostan'])[((gs - 1) % 10) + 1],
        (100000 + gs)::text,
        format('Retail Street %s', gs * 7),
        CASE WHEN gs % 3 = 0 THEN format('Unit %s', gs) ELSE NULL END
    FROM generate_series(1, 20) AS gs
)
INSERT INTO stores (name, country, city, state, zip_code, address_line_1, address_line_2)
SELECT name, country, city, state, zip_code, address_line_1, address_line_2
FROM store_seed seed
WHERE NOT EXISTS (
    SELECT 1
    FROM stores existing
    WHERE existing.name = seed.name
);

-- =========================================================
-- WAREHOUSES
-- Warehouses are guarded by name because name is not unique in the schema.
-- =========================================================

WITH warehouse_seed (name, country, city, state, zip_code, address_line_1, address_line_2) AS (
    SELECT
        format('OMS Demo Warehouse %s', lpad(gs::text, 2, '0')),
        'Russia',
        (ARRAY['Moscow','Kazan','Novosibirsk','Yekaterinburg','Samara','Rostov-on-Don'])[gs],
        (ARRAY['Moscow','Tatarstan','Novosibirsk Oblast','Sverdlovsk Oblast','Samara Oblast','Rostov Oblast'])[gs],
        (200000 + gs)::text,
        format('Logistics Park %s', gs * 11),
        format('Warehouse Building %s', chr(64 + gs))
    FROM generate_series(1, 6) AS gs
)
INSERT INTO warehouses (name, country, city, state, zip_code, address_line_1, address_line_2)
SELECT name, country, city, state, zip_code, address_line_1, address_line_2
FROM warehouse_seed seed
WHERE NOT EXISTS (
    SELECT 1
    FROM warehouses existing
    WHERE existing.name = seed.name
);

-- =========================================================
-- ITEMS
-- =========================================================

INSERT INTO items (
    sku, style, product_name, description, units, color,
    size_identifier, size_value, brand, ean, photo, price, category, supplier
)
SELECT
    format('OMS-DEMO-SKU-%s', lpad(gs::text, 4, '0')) AS sku,
    (ARRAY['BASIC','CASUAL','SPORT','FORMAL','WINTER','SUMMER','TRAVEL','PREMIUM'])[((gs - 1) % 8) + 1] AS style,
    format('%s %s',
        (ARRAY['Jacket','Hoodie','T-Shirt','Jeans','Sneakers','Backpack','Cap','Socks','Dress','Shirt','Coat','Belt','Pants','Sweater','Scarf'])[((gs - 1) % 15) + 1],
        lpad(gs::text, 3, '0')
    ) AS product_name,
    format('Demo product %s for OMS testing, searchable by SKU, description, brand, and category.', gs) AS description,
    CASE WHEN gs % 12 = 0 THEN 5 ELSE 1 END AS units,
    (ARRAY['Black','White','Gray','Blue','Red','Green','Navy','Brown','Beige','Camel'])[((gs - 1) % 10) + 1] AS color,
    CASE WHEN gs % 5 IN (0, 1, 2) THEN 'EU' ELSE NULL END AS size_identifier,
    CASE WHEN gs % 5 IN (0, 1, 2) THEN 36 + (gs % 16) ELSE NULL END AS size_value,
    (ARRAY['NordWear','UrbanLine','DailyFit','DenimPro','RunFast','PackPlus','StreetCap','Elegance','OfficeWear','TrailMax'])[((gs - 1) % 10) + 1] AS brand,
    NULL AS ean,
    format('https://example.com/products/OMS-DEMO-SKU-%s.jpg', lpad(gs::text, 4, '0')) AS photo,
    (790 + ((gs * 137) % 12000))::numeric(12,2) AS price,
    (ARRAY['Outerwear','Sweatshirts','T-Shirts','Jeans','Shoes','Bags','Accessories','Dresses','Shirts','Pants'])[((gs - 1) % 10) + 1] AS category,
    format('%s Supplier', (ARRAY['NordWear','UrbanLine','DailyFit','DenimPro','RunFast','PackPlus','StreetCap','Elegance','OfficeWear','TrailMax'])[((gs - 1) % 10) + 1]) AS supplier
FROM generate_series(1, 120) AS gs
ON CONFLICT (sku) DO UPDATE SET
    style = EXCLUDED.style,
    product_name = EXCLUDED.product_name,
    description = EXCLUDED.description,
    units = EXCLUDED.units,
    color = EXCLUDED.color,
    size_identifier = EXCLUDED.size_identifier,
    size_value = EXCLUDED.size_value,
    brand = EXCLUDED.brand,
    photo = EXCLUDED.photo,
    price = EXCLUDED.price,
    category = EXCLUDED.category,
    supplier = EXCLUDED.supplier;

-- =========================================================
-- STORE INVENTORY
-- =========================================================

INSERT INTO store_inventory (
    store_id, item_id, real_quantity, minimum_quantity, maximum_quantity, reserved_quantity
)
SELECT
    s.store_id,
    i.item_id,
    40 + ((s.store_id * 17 + i.item_id * 13) % 180) AS real_quantity,
    5 + ((s.store_id + i.item_id) % 16) AS minimum_quantity,
    120 + ((s.store_id * 11 + i.item_id * 7) % 160) AS maximum_quantity,
    ((s.store_id * 5 + i.item_id * 3) % 18) AS reserved_quantity
FROM stores s
JOIN items i ON i.sku LIKE 'OMS-DEMO-SKU-%'
WHERE s.name LIKE 'OMS Demo Store %'
ON CONFLICT (store_id, item_id) DO UPDATE SET
    real_quantity = GREATEST(store_inventory.real_quantity, EXCLUDED.real_quantity),
    minimum_quantity = EXCLUDED.minimum_quantity,
    maximum_quantity = GREATEST(EXCLUDED.maximum_quantity, EXCLUDED.real_quantity, EXCLUDED.minimum_quantity),
    reserved_quantity = LEAST(
        GREATEST(store_inventory.reserved_quantity, EXCLUDED.reserved_quantity),
        GREATEST(store_inventory.real_quantity, EXCLUDED.real_quantity)
    );

-- =========================================================
-- WAREHOUSE INVENTORY
-- =========================================================

INSERT INTO warehouse_inventory (
    warehouse_id, item_id, real_quantity, reserved_quantity
)
SELECT
    w.warehouse_id,
    i.item_id,
    300 + ((w.warehouse_id * 31 + i.item_id * 19) % 900) AS real_quantity,
    ((w.warehouse_id * 7 + i.item_id * 5) % 60) AS reserved_quantity
FROM warehouses w
JOIN items i ON i.sku LIKE 'OMS-DEMO-SKU-%'
WHERE w.name LIKE 'OMS Demo Warehouse %'
ON CONFLICT (warehouse_id, item_id) DO UPDATE SET
    real_quantity = GREATEST(warehouse_inventory.real_quantity, EXCLUDED.real_quantity),
    reserved_quantity = LEAST(
        GREATEST(warehouse_inventory.reserved_quantity, EXCLUDED.reserved_quantity),
        GREATEST(warehouse_inventory.real_quantity, EXCLUDED.real_quantity)
    );

-- =========================================================
-- ORDER / HISTORY / COMMENT / TRANSACTION DEMO BATCH
-- Inserts the batch only once.
-- Transaction constants used:
--   TRANSFER_IN, TRANSFER_OUT, REPLENISHMENT_IN, REPLENISHMENT_OUT, SALES_COMPLETED
-- =========================================================

DO $$
DECLARE
    v_order_id BIGINT;
    v_comment_id BIGINT;
    v_marker_comment_id BIGINT;

    v_store_ids BIGINT[];
    v_warehouse_ids BIGINT[];
    v_item_ids BIGINT[];

    v_store_count INT;
    v_warehouse_count INT;
    v_item_count INT;

    v_order_type VARCHAR(30);
    v_created_at TIMESTAMP;
    v_store_id BIGINT;
    v_warehouse_id BIGINT;
    v_source_store_id BIGINT;
    v_destination_store_id BIGINT;
    v_priority VARCHAR(20);

    v_line_count INT;
    v_item_id BIGINT;
    v_quantity INT;
    v_unit_price NUMERIC(12,2);
    v_discount_rate NUMERIC(5,2);
    v_line_final_price NUMERIC(12,2);

    n INT;
    k INT;
    v_mod INT;
BEGIN
    IF EXISTS (
        SELECT 1
        FROM order_comments
        WHERE created_by = 'seed_script'
          AND comment_text = 'OMS demo seed batch v1'
    ) THEN
        RAISE NOTICE 'OMS demo seed batch already exists. Skipping order/transaction demo batch.';
        RETURN;
    END IF;

    SELECT array_agg(store_id ORDER BY store_id)
    INTO v_store_ids
    FROM stores
    WHERE name LIKE 'OMS Demo Store %';

    SELECT array_agg(warehouse_id ORDER BY warehouse_id)
    INTO v_warehouse_ids
    FROM warehouses
    WHERE name LIKE 'OMS Demo Warehouse %';

    SELECT array_agg(item_id ORDER BY item_id)
    INTO v_item_ids
    FROM items
    WHERE sku LIKE 'OMS-DEMO-SKU-%';

    v_store_count := COALESCE(array_length(v_store_ids, 1), 0);
    v_warehouse_count := COALESCE(array_length(v_warehouse_ids, 1), 0);
    v_item_count := COALESCE(array_length(v_item_ids, 1), 0);

    IF v_store_count < 2 THEN
        RAISE EXCEPTION 'Demo seed requires at least 2 demo stores.';
    END IF;

    IF v_warehouse_count < 1 THEN
        RAISE EXCEPTION 'Demo seed requires at least 1 demo warehouse.';
    END IF;

    IF v_item_count < 10 THEN
        RAISE EXCEPTION 'Demo seed requires at least 10 demo items.';
    END IF;

    FOR n IN 1..500 LOOP
        v_created_at := CURRENT_TIMESTAMP - ((n % 60) || ' days')::INTERVAL - ((n % 12) || ' hours')::INTERVAL;
        v_mod := n % 10;

        IF n % 3 = 0 THEN
            v_order_type := 'SALES';
            v_store_id := v_store_ids[((n * 3) % v_store_count) + 1];
            v_warehouse_id := NULL;
            v_source_store_id := NULL;
            v_destination_store_id := NULL;
            v_priority := NULL;

            INSERT INTO orders (order_type, store_id, warehouse_id, source_store_id, destination_store_id, priority, created_at)
            VALUES (v_order_type, v_store_id, NULL, NULL, NULL, NULL, v_created_at)
            RETURNING order_id INTO v_order_id;

        ELSIF n % 3 = 1 THEN
            v_order_type := 'REPLENISHMENT';
            v_store_id := v_store_ids[((n * 5) % v_store_count) + 1];
            v_warehouse_id := v_warehouse_ids[((n * 7) % v_warehouse_count) + 1];
            v_source_store_id := NULL;
            v_destination_store_id := NULL;
            v_priority := (ARRAY['LOW','NORMAL','HIGH','URGENT'])[(n % 4) + 1];

            INSERT INTO orders (order_type, store_id, warehouse_id, source_store_id, destination_store_id, priority, created_at)
            VALUES (v_order_type, v_store_id, v_warehouse_id, NULL, NULL, v_priority, v_created_at)
            RETURNING order_id INTO v_order_id;

        ELSE
            v_order_type := 'TRANSFER';
            v_store_id := NULL;
            v_warehouse_id := NULL;
            v_source_store_id := v_store_ids[((n * 2) % v_store_count) + 1];
            v_destination_store_id := v_store_ids[((n * 2 + 5) % v_store_count) + 1];

            IF v_source_store_id = v_destination_store_id THEN
                v_destination_store_id := v_store_ids[((n * 2 + 6) % v_store_count) + 1];
            END IF;

            v_priority := (ARRAY['LOW','NORMAL','HIGH','URGENT'])[(n % 4) + 1];

            INSERT INTO orders (order_type, store_id, warehouse_id, source_store_id, destination_store_id, priority, created_at)
            VALUES (v_order_type, NULL, NULL, v_source_store_id, v_destination_store_id, v_priority, v_created_at)
            RETURNING order_id INTO v_order_id;
        END IF;

        v_line_count := 1 + (n % 4);

        FOR k IN 1..v_line_count LOOP
            v_item_id := v_item_ids[((n * 11 + k * 17) % v_item_count) + 1];
            v_quantity := 1 + ((n + k) % 12);
            v_discount_rate := CASE WHEN (n + k) % 9 = 0 THEN 10 ELSE 0 END;

            SELECT price INTO v_unit_price
            FROM items
            WHERE item_id = v_item_id;

            v_line_final_price := ROUND(v_unit_price * v_quantity * (1 - (v_discount_rate / 100)), 2);

            INSERT INTO order_items (order_id, item_id, quantity, unit_price, discount_rate, final_price)
            VALUES (v_order_id, v_item_id, v_quantity, v_unit_price, v_discount_rate, v_line_final_price);
        END LOOP;

        IF n = 1 THEN
            INSERT INTO order_comments (order_id, comment_text, comment_type, created_by, created_at)
            VALUES (v_order_id, 'OMS demo seed batch v1', 'GENERAL', 'seed_script', v_created_at + INTERVAL '5 minutes')
            RETURNING comment_id INTO v_marker_comment_id;
        ELSE
            v_marker_comment_id := NULL;
        END IF;

        INSERT INTO order_history (order_id, old_status, new_status, comment_id, changed_by, changed_at)
        VALUES (v_order_id, NULL, 'CREATED', v_marker_comment_id, 'seed_script', v_created_at);

        IF n % 5 = 0 THEN
            INSERT INTO order_comments (order_id, comment_text, comment_type, created_by, created_at)
            VALUES (
                v_order_id,
                format('Operational note for demo order %s.', v_order_id),
                'GENERAL',
                'seed_script',
                v_created_at + INTERVAL '10 minutes'
            );
        END IF;

        IF v_order_type = 'SALES' THEN
            IF v_mod IN (0, 1, 2, 3, 4, 5) THEN
                UPDATE orders SET order_status = 'PENDING' WHERE order_id = v_order_id;
                INSERT INTO order_history (order_id, old_status, new_status, changed_by, changed_at)
                VALUES (v_order_id, 'CREATED', 'PENDING', 'store_manager', v_created_at + INTERVAL '20 minutes');

                UPDATE orders SET order_status = 'COMPLETED' WHERE order_id = v_order_id;
                INSERT INTO order_history (order_id, old_status, new_status, changed_by, changed_at)
                VALUES (v_order_id, 'PENDING', 'COMPLETED', 'store_manager', v_created_at + INTERVAL '2 hours');

                INSERT INTO store_inventory_transaction (order_id, item_id, store_id, quantity_change, transaction_type, created_at)
                SELECT v_order_id, oi.item_id, v_store_id, -oi.quantity, 'SALES_COMPLETED', v_created_at + INTERVAL '2 hours'
                FROM order_items oi
                WHERE oi.order_id = v_order_id;

                UPDATE store_inventory si
                SET real_quantity = GREATEST(si.reserved_quantity, si.real_quantity - oi.quantity)
                FROM order_items oi
                WHERE oi.order_id = v_order_id
                  AND si.store_id = v_store_id
                  AND si.item_id = oi.item_id;

            ELSIF v_mod IN (6, 7) THEN
                UPDATE orders SET order_status = 'PENDING' WHERE order_id = v_order_id;
                INSERT INTO order_history (order_id, old_status, new_status, changed_by, changed_at)
                VALUES (v_order_id, 'CREATED', 'PENDING', 'store_manager', v_created_at + INTERVAL '20 minutes');

            ELSIF v_mod = 8 THEN
                INSERT INTO order_comments (order_id, comment_text, comment_type, created_by, created_at)
                VALUES (v_order_id, 'Sales order cancelled by customer before completion.', 'CANCELLATION_REASON', 'store_manager', v_created_at + INTERVAL '30 minutes')
                RETURNING comment_id INTO v_comment_id;

                UPDATE orders SET order_status = 'CANCELLED' WHERE order_id = v_order_id;
                INSERT INTO order_history (order_id, old_status, new_status, comment_id, changed_by, changed_at)
                VALUES (v_order_id, 'CREATED', 'CANCELLED', v_comment_id, 'store_manager', v_created_at + INTERVAL '35 minutes');
            END IF;
        END IF;

        IF v_order_type = 'REPLENISHMENT' THEN
            IF v_mod IN (0, 1, 2, 3, 4) THEN
                UPDATE orders SET order_status = 'APPROVED' WHERE order_id = v_order_id;
                INSERT INTO order_history (order_id, old_status, new_status, changed_by, changed_at)
                VALUES (v_order_id, 'CREATED', 'APPROVED', 'warehouse_staff', v_created_at + INTERVAL '30 minutes');

                UPDATE orders SET order_status = 'PACKAGING' WHERE order_id = v_order_id;
                INSERT INTO order_history (order_id, old_status, new_status, changed_by, changed_at)
                VALUES (v_order_id, 'APPROVED', 'PACKAGING', 'warehouse_staff', v_created_at + INTERVAL '2 hours');

                UPDATE orders SET order_status = 'ON_THE_WAY' WHERE order_id = v_order_id;
                INSERT INTO order_history (order_id, old_status, new_status, changed_by, changed_at)
                VALUES (v_order_id, 'PACKAGING', 'ON_THE_WAY', 'warehouse_staff', v_created_at + INTERVAL '5 hours');

                INSERT INTO warehouse_inventory_transaction (order_id, item_id, warehouse_id, quantity_change, transaction_type, created_at)
                SELECT v_order_id, oi.item_id, v_warehouse_id, -oi.quantity, 'REPLENISHMENT_OUT', v_created_at + INTERVAL '5 hours'
                FROM order_items oi
                WHERE oi.order_id = v_order_id;

                UPDATE warehouse_inventory wi
                SET real_quantity = GREATEST(wi.reserved_quantity, wi.real_quantity - oi.quantity)
                FROM order_items oi
                WHERE oi.order_id = v_order_id
                  AND wi.warehouse_id = v_warehouse_id
                  AND wi.item_id = oi.item_id;

                UPDATE orders SET order_status = 'COMPLETED' WHERE order_id = v_order_id;
                INSERT INTO order_history (order_id, old_status, new_status, changed_by, changed_at)
                VALUES (v_order_id, 'ON_THE_WAY', 'COMPLETED', 'store_manager', v_created_at + INTERVAL '1 day');

                INSERT INTO store_inventory_transaction (order_id, item_id, store_id, quantity_change, transaction_type, created_at)
                SELECT v_order_id, oi.item_id, v_store_id, oi.quantity, 'REPLENISHMENT_IN', v_created_at + INTERVAL '1 day'
                FROM order_items oi
                WHERE oi.order_id = v_order_id;

                UPDATE store_inventory si
                SET real_quantity = si.real_quantity + oi.quantity
                FROM order_items oi
                WHERE oi.order_id = v_order_id
                  AND si.store_id = v_store_id
                  AND si.item_id = oi.item_id;

            ELSIF v_mod IN (5, 6) THEN
                UPDATE orders SET order_status = 'APPROVED' WHERE order_id = v_order_id;
                INSERT INTO order_history (order_id, old_status, new_status, changed_by, changed_at)
                VALUES (v_order_id, 'CREATED', 'APPROVED', 'warehouse_staff', v_created_at + INTERVAL '30 minutes');

            ELSIF v_mod = 7 THEN
                INSERT INTO order_comments (order_id, comment_text, comment_type, created_by, created_at)
                VALUES (v_order_id, 'Replenishment rejected because warehouse stock cannot fulfill the request.', 'REJECTION_REASON', 'warehouse_staff', v_created_at + INTERVAL '30 minutes')
                RETURNING comment_id INTO v_comment_id;

                UPDATE orders SET order_status = 'REJECTED' WHERE order_id = v_order_id;
                INSERT INTO order_history (order_id, old_status, new_status, comment_id, changed_by, changed_at)
                VALUES (v_order_id, 'CREATED', 'REJECTED', v_comment_id, 'warehouse_staff', v_created_at + INTERVAL '35 minutes');

            ELSIF v_mod = 8 THEN
                INSERT INTO order_comments (order_id, comment_text, comment_type, created_by, created_at)
                VALUES (v_order_id, 'Replenishment cancelled because the destination store no longer needs this stock.', 'CANCELLATION_REASON', 'store_manager', v_created_at + INTERVAL '30 minutes')
                RETURNING comment_id INTO v_comment_id;

                UPDATE orders SET order_status = 'CANCELLED' WHERE order_id = v_order_id;
                INSERT INTO order_history (order_id, old_status, new_status, comment_id, changed_by, changed_at)
                VALUES (v_order_id, 'CREATED', 'CANCELLED', v_comment_id, 'store_manager', v_created_at + INTERVAL '35 minutes');
            END IF;
        END IF;

        IF v_order_type = 'TRANSFER' THEN
            IF v_mod IN (0, 1, 2, 3, 4) THEN
                UPDATE orders SET order_status = 'APPROVED' WHERE order_id = v_order_id;
                INSERT INTO order_history (order_id, old_status, new_status, changed_by, changed_at)
                VALUES (v_order_id, 'CREATED', 'APPROVED', 'store_manager', v_created_at + INTERVAL '30 minutes');

                UPDATE orders SET order_status = 'PACKAGING' WHERE order_id = v_order_id;
                INSERT INTO order_history (order_id, old_status, new_status, changed_by, changed_at)
                VALUES (v_order_id, 'APPROVED', 'PACKAGING', 'store_manager', v_created_at + INTERVAL '2 hours');

                UPDATE orders SET order_status = 'ON_THE_WAY' WHERE order_id = v_order_id;
                INSERT INTO order_history (order_id, old_status, new_status, changed_by, changed_at)
                VALUES (v_order_id, 'PACKAGING', 'ON_THE_WAY', 'store_manager', v_created_at + INTERVAL '5 hours');

                INSERT INTO store_inventory_transaction (order_id, item_id, store_id, quantity_change, transaction_type, created_at)
                SELECT v_order_id, oi.item_id, v_source_store_id, -oi.quantity, 'TRANSFER_OUT', v_created_at + INTERVAL '5 hours'
                FROM order_items oi
                WHERE oi.order_id = v_order_id;

                UPDATE store_inventory si
                SET real_quantity = GREATEST(si.reserved_quantity, si.real_quantity - oi.quantity)
                FROM order_items oi
                WHERE oi.order_id = v_order_id
                  AND si.store_id = v_source_store_id
                  AND si.item_id = oi.item_id;

                IF v_mod IN (0, 1, 2) THEN
                    UPDATE orders SET order_status = 'COMPLETED' WHERE order_id = v_order_id;
                    INSERT INTO order_history (order_id, old_status, new_status, changed_by, changed_at)
                    VALUES (v_order_id, 'ON_THE_WAY', 'COMPLETED', 'store_manager', v_created_at + INTERVAL '1 day');

                    INSERT INTO store_inventory_transaction (order_id, item_id, store_id, quantity_change, transaction_type, created_at)
                    SELECT v_order_id, oi.item_id, v_destination_store_id, oi.quantity, 'TRANSFER_IN', v_created_at + INTERVAL '1 day'
                    FROM order_items oi
                    WHERE oi.order_id = v_order_id;

                    UPDATE store_inventory si
                    SET real_quantity = si.real_quantity + oi.quantity
                    FROM order_items oi
                    WHERE oi.order_id = v_order_id
                      AND si.store_id = v_destination_store_id
                      AND si.item_id = oi.item_id;
                END IF;

            ELSIF v_mod IN (5, 6) THEN
                UPDATE orders SET order_status = 'APPROVED' WHERE order_id = v_order_id;
                INSERT INTO order_history (order_id, old_status, new_status, changed_by, changed_at)
                VALUES (v_order_id, 'CREATED', 'APPROVED', 'store_manager', v_created_at + INTERVAL '30 minutes');

            ELSIF v_mod = 7 THEN
                INSERT INTO order_comments (order_id, comment_text, comment_type, created_by, created_at)
                VALUES (v_order_id, 'Transfer rejected because the source store cannot fulfill the requested quantity.', 'REJECTION_REASON', 'store_manager', v_created_at + INTERVAL '30 minutes')
                RETURNING comment_id INTO v_comment_id;

                UPDATE orders SET order_status = 'REJECTED' WHERE order_id = v_order_id;
                INSERT INTO order_history (order_id, old_status, new_status, comment_id, changed_by, changed_at)
                VALUES (v_order_id, 'CREATED', 'REJECTED', v_comment_id, 'store_manager', v_created_at + INTERVAL '35 minutes');

            ELSIF v_mod = 8 THEN
                INSERT INTO order_comments (order_id, comment_text, comment_type, created_by, created_at)
                VALUES (v_order_id, 'Transfer cancelled before dispatch.', 'CANCELLATION_REASON', 'store_manager', v_created_at + INTERVAL '30 minutes')
                RETURNING comment_id INTO v_comment_id;

                UPDATE orders SET order_status = 'CANCELLED' WHERE order_id = v_order_id;
                INSERT INTO order_history (order_id, old_status, new_status, comment_id, changed_by, changed_at)
                VALUES (v_order_id, 'CREATED', 'CANCELLED', v_comment_id, 'store_manager', v_created_at + INTERVAL '35 minutes');
            END IF;
        END IF;
    END LOOP;
END $$;

COMMIT;

-- =========================================================
-- QUICK CHECKS
-- =========================================================

SELECT 'stores' AS table_name, COUNT(*) AS row_count FROM stores WHERE name LIKE 'OMS Demo Store %'
UNION ALL
SELECT 'warehouses', COUNT(*) FROM warehouses WHERE name LIKE 'OMS Demo Warehouse %'
UNION ALL
SELECT 'items', COUNT(*) FROM items WHERE sku LIKE 'OMS-DEMO-SKU-%'
UNION ALL
SELECT 'orders', COUNT(*) FROM orders o JOIN order_comments c ON c.order_id = o.order_id WHERE c.created_by = 'seed_script'
UNION ALL
SELECT 'store_inventory_transaction', COUNT(*) FROM store_inventory_transaction WHERE transaction_type IN ('TRANSFER_IN','TRANSFER_OUT','REPLENISHMENT_IN','SALES_COMPLETED')
UNION ALL
SELECT 'warehouse_inventory_transaction', COUNT(*) FROM warehouse_inventory_transaction WHERE transaction_type = 'REPLENISHMENT_OUT';

SELECT transaction_type, COUNT(*) AS row_count
FROM store_inventory_transaction
WHERE transaction_type IN ('TRANSFER_IN','TRANSFER_OUT','REPLENISHMENT_IN','SALES_COMPLETED')
GROUP BY transaction_type
UNION ALL
SELECT transaction_type, COUNT(*) AS row_count
FROM warehouse_inventory_transaction
WHERE transaction_type = 'REPLENISHMENT_OUT'
GROUP BY transaction_type
ORDER BY transaction_type;