DatabaseCreation: dml.sql

SET search_path TO myschema;

-- 1. ROLES
INSERT INTO roles (role_id, role_name, description) VALUES
(1, 'ADMIN', 'Full system access'),
(2, 'CUSTOMER', 'Regular buyer'),
(3, 'SELLER', 'Store owner/vendor'),
(4, 'SUPPORT STAFF', 'Customer service'),
(5, 'COURIER', 'Delivery personnel');

-- 2. CATEGORIES
INSERT INTO categories (category_id, category_name, parent_category_id) VALUES
(1, 'Computing', NULL),
(2, 'Mobile & Tablets', NULL),
(3, 'Audio', NULL),
(4, 'TV & Home Theater', NULL),
(5, 'Video Games', NULL),
(6, 'Appliances', NULL),
(7, 'Laptops', 1),
(8, 'Desktops', 1),
(9, 'PC Components', 1),
(10, 'Smartphones', 2),
(11, 'iPads', 2),
(12, 'Android Tablets', 2),
(13, 'Headphones', 3),
(14, 'Bluetooth Speakers', 3),
(15, 'OLED TVs', 4),
(16, 'Projectors', 4),
(17, 'Consoles', 5),
(18, 'VR Headsets', 5),
(19, 'Refrigerators', 6),
(20, 'Washing Machines', 6);

-- 3. PRODUCT_ATTRIBUTES
INSERT INTO product_attributes (attribute_name) VALUES
('Processor'),
('RAM Memory'),
('Storage (SSD/HDD)'),
('Screen Size'),
('Display Type'),
('Battery Life'),
('GPU (Graphics)'),
('Color'),
('Power Consumption'),
('Energy Efficiency Rating');

-- 4. MANUFACTURERS
INSERT INTO manufacturers (manufacturer_id, name, contact_info)
SELECT
    i,
    brand || ' ' || industry AS name,
    'support@' || lower(brand) || '.com' AS contact_info
FROM (
    SELECT
        i,
        CASE
            WHEN (i - 1) % 6 = 0 THEN 'Computing'
            WHEN (i - 1) % 6 = 1 THEN 'Mobile'
            WHEN (i - 1) % 6 = 2 THEN 'Sound'
            WHEN (i - 1) % 6 = 3 THEN 'Display'
            WHEN (i - 1) % 6 = 4 THEN 'Gaming'
            ELSE 'Appliances'
        END AS industry,
        (ARRAY['Apex','Nova','Zenith','Quantum','Elite','Aura','Pixel','Sonic','Giga','Volt'])[((i / 6) % 10) + 1] AS brand
    FROM generate_series(1, 600) AS i
) s;

-- 5. SHIPPING_METHODS
INSERT INTO shipping_methods (method_id, method_name, cost, estimated_days) VALUES
(1, 'Standard Delivery', 150.00, 10),
(2, 'Express Delivery', 450.00, 3),
(3, 'Free Shipping', 0.00, 10);

-- 6. COUPONS
INSERT INTO coupons (coupon_id, code, discount_value, discount_type, valid_from, valid_to, min_order_value) VALUES
(1, 'EASTER24', 15.00, 'PERCENTAGE', '2024-04-15 00:00:00', '2024-04-30 23:59:59', 1500.00),
(2, 'WOMAN8MAR', 500.00, 'FIXED', '2024-03-07 00:00:00', '2024-03-10 23:59:59', 3000.00),
(3, 'BLACKFRIDAY24', 30.00, 'PERCENTAGE', '2024-11-25 00:00:00', '2024-11-30 23:59:59', 500.00),
(4, 'SINGLES11', 1111.00, 'FIXED', '2024-11-11 00:00:00', '2024-11-11 23:59:59', 6000.00),
(5, 'XMAS24', 20.00, 'PERCENTAGE', '2024-12-15 00:00:00', '2025-01-05 23:59:59', 2000.00),
(6, 'EARTH24', 10.00, 'PERCENTAGE', '2024-04-22 00:00:00', '2024-04-22 23:59:59', 0.00),
(7, 'WELCOME_BLINK', 200.00, 'FIXED', '2023-01-01 00:00:00', '2025-12-31 23:59:59', 1000.00),
(8, 'LOYALTY_VIP', 25.00, 'PERCENTAGE', '2023-01-01 00:00:00', '2025-12-31 23:59:59', 5000.00);

-- 7. USERS
INSERT INTO users (user_id, email, password_hash, status, created_at)
SELECT
    gs,
    CASE
        WHEN gs = 1 THEN 'admin@blinkbuy.com'
        WHEN gs BETWEEN 800001 AND 950000 THEN 'seller' || gs || '@blinkbuy-sellers.com'
        WHEN gs BETWEEN 975001 AND 1000000 THEN 'courier' || gs || '@blinkbuy-logistics.com'
        ELSE 'user' || gs || '@' || (ARRAY['gmail.com','outlook.com','yahoo.com'])[(gs % 3) + 1]
    END AS email,
    md5(gs::text) AS password_hash,
    CASE WHEN gs % 1000 = 0 THEN 'INACTIVE' ELSE 'ACTIVE' END AS status,
    '2023-01-01 00:00:00+00'::timestamptz + ((gs % 1095) * interval '1 day') AS created_at
FROM generate_series(1, 1000000) gs;

-- 8. USER_ROLES
INSERT INTO user_roles (user_id, role_id, assigned_at)
SELECT
    user_id,
    CASE
        WHEN user_id = 1 THEN 1
        WHEN user_id BETWEEN 2 AND 800000 THEN 2
        WHEN user_id BETWEEN 800001 AND 950000 THEN 3
        WHEN user_id BETWEEN 950001 AND 975000 THEN 4
        ELSE 5
    END AS role_id,
    created_at::date AS assigned_at
FROM users;

-- 9. USER_PROFILES
INSERT INTO user_profiles (user_id, first_name, last_name, phone_number, profile_picture_url)
SELECT
    u.user_id,
    CASE
        WHEN u.user_id = 1 THEN 'System'
        WHEN ur.role_id = 3 THEN (ARRAY['ByteForge','CircuitCore','PixelWave','QuantumTech','NeonByte','TechNova','DigitalEdge','ElectroLink'])[(u.user_id % 8) + 1]
        WHEN ur.role_id = 5 THEN (ARRAY['Fast','Rapid','Cargo','Sky','City','Speedy','Star','Blue'])[(u.user_id % 8) + 1]
        WHEN u.user_id % 2 = 0 THEN (ARRAY['Ivan','Petar','Stefan','Igor','Goran','Darko','Bojan','Dragan'])[(u.user_id % 8) + 1]
        ELSE (ARRAY['Marija','Elena','Ana','Jovana','Milica','Katerina','Sara','Ivana'])[(u.user_id % 8) + 1]
    END AS first_name,
    CASE
        WHEN u.user_id = 1 THEN 'Administrator'
        WHEN ur.role_id = 3 THEN (ARRAY['Technologies','Electronics','Systems','Digital','Devices','Computing','Hardware','Solutions'])[(u.user_id % 8) + 1]
        WHEN ur.role_id = 5 THEN (ARRAY['Logistics','Delivery','Courier','Express','Post','Transport','Ship','Way'])[(u.user_id % 8) + 1]
        WHEN u.user_id % 2 = 0 THEN (ARRAY['Stojanovski','Petrovski','Trajkov','Popov','Nikolov','Ristov','Arsov','Kuzmanov'])[(u.user_id % 8) + 1]
        ELSE (ARRAY['Stojanovska','Petrovska','Trajkova','Popova','Nikolova','Ristovska','Arsova','Kuzmanova'])[(u.user_id % 8) + 1]
    END AS last_name,
    '+3897' || ((u.user_id % 6) + 1)::text || lpad((100000 + (u.user_id % 899999))::text, 6, '0') AS phone_number,
    'https://api.dicebear.com/7.x/avataaars/svg?seed=' || u.user_id AS profile_picture_url
FROM users u
JOIN user_roles ur ON ur.user_id = u.user_id;

-- 10. ADDRESSES
INSERT INTO addresses (user_id, street_address, city, country, is_default, address_type)
SELECT
    u.user_id,
    CASE
        WHEN u.user_id = 1 THEN 'BlinkBuy HQ, Macedonia St. 1'
        WHEN ur.role_id = 3 THEN 'Industrial Zone ' || (ARRAY['A','B','C'])[(u.user_id % 3) + 1] || '-' || (u.user_id % 100)
        WHEN ur.role_id = 5 THEN 'Logistics Center Hub ' || (u.user_id % 50)
        ELSE (ARRAY['Partizanska','Ilindenska','Jane Sandanski','Ruzveltova'])[(u.user_id % 4) + 1] || ' No.' || (u.user_id % 200)
    END AS street_address,
    (ARRAY['Skopje','Bitola','Kumanovo','Tetovo','Veles','Stip'])[(u.user_id % 6) + 1] AS city,
    'Macedonia' AS country,
    true AS is_default,
    CASE
        WHEN u.user_id = 1 THEN 'HEADQUARTERS'
        WHEN ur.role_id = 3 THEN 'WAREHOUSE'
        WHEN ur.role_id = 5 THEN 'OFFICE'
        ELSE 'HOME'
    END AS address_type
FROM users u
JOIN user_roles ur ON ur.user_id = u.user_id;

-- 11. WAREHOUSES
INSERT INTO warehouses (warehouse_name, location, user_id)
SELECT
    'Warehouse ' || ur.user_id || '-1' AS warehouse_name,
    (ARRAY['Skopje Industrial Zone','Bitola Logistics Park','Kumanovo Distribution Center','Tetovo Storage Hub','Veles Warehouse Area','Stip Industrial Complex'])[(ur.user_id % 6) + 1] AS location,
    ur.user_id
FROM user_roles ur
WHERE ur.role_id = 3
UNION ALL
SELECT
    'Warehouse ' || ur.user_id || '-2' AS warehouse_name,
    (ARRAY['Skopje Industrial Zone','Kumanovo Distribution Center','Tetovo Storage Hub'])[(ur.user_id % 3) + 1] AS location,
    ur.user_id
FROM user_roles ur
WHERE ur.role_id = 3
  AND ur.user_id % 2 = 0;

-- 12. PRODUCTS
INSERT INTO products (product_id, category_id, manufacturer_id, name, brand, description)
SELECT
    i AS product_id,
    cat_id AS category_id,
    manufacturer_id,
    brand_prefix || ' ' || model_line || ' ' || (100 + (i % 900)) AS name,
    brand_full_name AS brand,
    'High-end device designed for professional performance and durability.' AS description
FROM (
    SELECT
        i,
        (i % 14 + 7) AS cat_id,
        (((i % 100) * 6) + CASE
            WHEN (i % 14 + 7) BETWEEN 7 AND 9 THEN 1
            WHEN (i % 14 + 7) BETWEEN 10 AND 12 THEN 2
            WHEN (i % 14 + 7) BETWEEN 13 AND 14 THEN 3
            WHEN (i % 14 + 7) BETWEEN 15 AND 16 THEN 4
            WHEN (i % 14 + 7) BETWEEN 17 AND 18 THEN 5
            ELSE 6
        END) AS manufacturer_id
    FROM generate_series(1, 500000) i
) x
JOIN LATERAL (
    SELECT name AS brand_full_name, split_part(name, ' ', 1) AS brand_prefix
    FROM manufacturers
    WHERE manufacturer_id = x.manufacturer_id
) m ON true
JOIN LATERAL (
    SELECT category_name AS model_line
    FROM categories
    WHERE category_id = x.cat_id
) c ON true;

-- 13. PRODUCT_VARIANTS
INSERT INTO product_variants (product_id, sku, price, stock_total, manufacturer_id)
SELECT
    p.product_id,
    'SKU-' || p.product_id || '-' || v AS sku,
    CASE
        WHEN p.category_id BETWEEN 7 AND 12 THEN (15000 + (p.product_id % 80000) + v * 1000)::numeric(12,2)
        WHEN p.category_id BETWEEN 15 AND 18 THEN (30000 + (p.product_id % 150000) + v * 1500)::numeric(12,2)
        ELSE (5000 + (p.product_id % 40000) + v * 500)::numeric(12,2)
    END AS price,
    0 AS stock_total,
    p.manufacturer_id
FROM products p
CROSS JOIN generate_series(1, 3) v;

-- 14. PRODUCT_PRICE_HISTORY
INSERT INTO product_price_history (variant_id, old_price, new_price, change_date)
WITH base AS (
    SELECT
        pv.variant_id,
        pv.price AS current_price,
        CASE
            WHEN pv.variant_id % 10 BETWEEN 0 AND 5 THEN 1
            WHEN pv.variant_id % 10 BETWEEN 6 AND 8 THEN 2
            ELSE 3
        END AS history_count
    FROM product_variants pv
), h1 AS (
    SELECT
        variant_id,
        history_count,
        (current_price * (1 + (((variant_id % 30) + 1) / 100.0)))::numeric(12,2) AS old_price,
        current_price AS new_price,
        now() - (((variant_id % 6) + 1) || ' months')::interval AS change_date
    FROM base
), h2 AS (
    SELECT
        variant_id,
        history_count,
        (old_price * (1 + (((variant_id % 25) + 5) / 100.0)))::numeric(12,2) AS old_price,
        old_price AS new_price,
        change_date - ((((variant_id + 2) % 6) + 1) || ' months')::interval AS change_date
    FROM h1
    WHERE history_count >= 2
), h3 AS (
    SELECT
        variant_id,
        history_count,
        (old_price * (1 + (((variant_id % 20) + 10) / 100.0)))::numeric(12,2) AS old_price,
        old_price AS new_price,
        change_date - ((((variant_id + 4) % 6) + 1) || ' months')::interval AS change_date
    FROM h2
    WHERE history_count >= 3
)
SELECT variant_id, old_price, new_price, change_date FROM h1
UNION ALL
SELECT variant_id, old_price, new_price, change_date FROM h2
UNION ALL
SELECT variant_id, old_price, new_price, change_date FROM h3;

-- 15. INVENTORY_ITEMS
INSERT INTO inventory_items (warehouse_id, variant_id, quantity)
WITH sellers AS (
    SELECT user_id, ROW_NUMBER() OVER (ORDER BY user_id) - 1 AS seller_rn
    FROM user_roles
    WHERE role_id = 3
), seller_count AS (
    SELECT COUNT(*) AS total_sellers FROM sellers
), variant_to_seller AS (
    SELECT pv.variant_id, s.user_id AS seller_user_id
    FROM product_variants pv
    CROSS JOIN seller_count sc
    JOIN sellers s ON s.seller_rn = (pv.variant_id % sc.total_sellers)
), seller_warehouses AS (
    SELECT
        warehouse_id,
        user_id,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY warehouse_id) - 1 AS warehouse_rn,
        COUNT(*) OVER (PARTITION BY user_id) AS warehouse_count
    FROM warehouses
)
SELECT
    sw.warehouse_id,
    vts.variant_id,
    (10 + (vts.variant_id % 50))::int AS quantity
FROM variant_to_seller vts
JOIN seller_warehouses sw
  ON sw.user_id = vts.seller_user_id
 AND sw.warehouse_rn = (vts.variant_id % sw.warehouse_count);

UPDATE product_variants pv
SET stock_total = s.total_quantity
FROM (
    SELECT variant_id, SUM(quantity)::int AS total_quantity
    FROM inventory_items
    GROUP BY variant_id
) s
WHERE s.variant_id = pv.variant_id;

-- 16. PRODUCT_INSTANCES
INSERT INTO product_instances (variant_id, warehouse_id, serial_number, status)
SELECT
    ii.variant_id,
    ii.warehouse_id,
    'SN-' || ii.inventory_id || '-' || gs || '-' || ii.variant_id AS serial_number,
    'AVAILABLE' AS status
FROM inventory_items ii
JOIN product_variants pv ON pv.variant_id = ii.variant_id
JOIN products p ON p.product_id = pv.product_id
CROSS JOIN LATERAL generate_series(
    1,
    CASE
        WHEN p.category_id IN (7,8,10,11,12,15,16,17,18,19,20) THEN LEAST(ii.quantity, 3 + ((ii.inventory_id % 4)::int))
        WHEN p.category_id IN (9,13,14) THEN LEAST(ii.quantity, 1 + ((ii.inventory_id % 2)::int))
        ELSE 1
    END
) gs;

-- 17. WARRANTIES
INSERT INTO warranties (instance_id, duration_months, terms_conditions)
SELECT
    pi.instance_id,
    CASE
        WHEN p.category_id IN (7,8,10,11,12,15,16,17,18,19,20) THEN 24
        WHEN p.category_id IN (9,13,14) THEN 12
        ELSE 6
    END AS duration_months,
    CASE
        WHEN p.category_id IN (7,8,10,11,12,15,16,17,18,19,20) THEN 'Standard manufacturer warranty for high-value electronic devices.'
        WHEN p.category_id IN (9,13,14) THEN 'Limited warranty for components and audio equipment.'
        ELSE 'Basic limited warranty.'
    END AS terms_conditions
FROM product_instances pi
JOIN product_variants pv ON pv.variant_id = pi.variant_id
JOIN products p ON p.product_id = pv.product_id;

-- 18. PRODUCT_ATTRIBUTE_VALUES
INSERT INTO product_attribute_values (variant_id, attribute_id, attr_value)
SELECT
    pv.variant_id,
    pa.attribute_id,
    CASE
        WHEN pa.attribute_name = 'Processor' AND p.category_id IN (7,8,9,10,11,12,17,18) THEN (ARRAY['Apple M3','Intel i5','Intel i7','Intel i9','Ryzen 5','Ryzen 7','Snapdragon 8 Gen 3'])[(pv.variant_id % 7) + 1]
        WHEN pa.attribute_name = 'RAM Memory' AND p.category_id IN (7,8,10,11,12) THEN (ARRAY['8GB','16GB','32GB','64GB'])[(pv.variant_id % 4) + 1]
        WHEN pa.attribute_name = 'Storage (SSD/HDD)' AND p.category_id IN (7,8,9,10,11,12,17,18) THEN (ARRAY['128GB','256GB','512GB','1TB','2TB'])[(pv.variant_id % 5) + 1]
        WHEN pa.attribute_name = 'Screen Size' AND p.category_id IN (7,10,11,12,15,16,18) THEN (ARRAY['6.1"','10.9"','14"','15.6"','55"','65"','75"'])[(pv.variant_id % 7) + 1]
        WHEN pa.attribute_name = 'Display Type' AND p.category_id IN (7,10,11,12,15,16,18) THEN (ARRAY['LCD','LED','OLED','QLED','Mini-LED'])[(pv.variant_id % 5) + 1]
        WHEN pa.attribute_name = 'Battery Life' AND p.category_id IN (7,10,11,12,13,14,18) THEN (ARRAY['6 hours','8 hours','10 hours','12 hours','24 hours'])[(pv.variant_id % 5) + 1]
        WHEN pa.attribute_name = 'GPU (Graphics)' AND p.category_id IN (7,8,9,17,18) THEN (ARRAY['Integrated','RTX 4060','RTX 4070','RTX 4080','Radeon RX 7800'])[(pv.variant_id % 5) + 1]
        WHEN pa.attribute_name = 'Color' THEN (ARRAY['Black','White','Silver','Blue','Gray'])[(pv.variant_id % 5) + 1]
        ELSE 'Not specified'
    END AS attr_value
FROM product_variants pv
JOIN products p ON p.product_id = pv.product_id
CROSS JOIN product_attributes pa;

-- 19. REVIEWS
INSERT INTO reviews (user_id, product_id, rating, comment_text, created_at)
SELECT
    ((gs * 3571) % 799999 + 2)::bigint AS user_id,
    ((gs * 7919) % 500000 + 1)::bigint AS product_id,
    CASE
        WHEN gs % 100 < 50 THEN 5
        WHEN gs % 100 < 70 THEN 4
        WHEN gs % 100 < 85 THEN 3
        WHEN gs % 100 < 95 THEN 2
        ELSE 1
    END AS rating,
    CASE
        WHEN gs % 100 < 50 THEN 'Excellent product, works perfectly and feels premium.'
        WHEN gs % 100 < 70 THEN 'Very good product, satisfied with the purchase.'
        WHEN gs % 100 < 85 THEN 'Average product, works fine but nothing special.'
        WHEN gs % 100 < 95 THEN 'Not fully satisfied, expected better quality.'
        ELSE 'Poor experience, product did not meet expectations.'
    END AS comment_text,
    now() - ((gs % 365) * interval '1 day') AS created_at
FROM generate_series(1, 300000) gs;

-- 20. REVIEW_HELPFULNESS
INSERT INTO review_helpfulness (user_id, review_id, vote_type, voted_at)
SELECT DISTINCT ON ((((gs::bigint * 3571) % 799999) + 2), (((gs::bigint * 7919) % 300000) + 1))
    (((gs::bigint * 3571) % 799999) + 2)::bigint AS user_id,
    (((gs::bigint * 7919) % 300000) + 1)::bigint AS review_id,
    CASE WHEN gs % 3 IN (0,1) THEN 'HELPFUL' ELSE 'NOT_HELPFUL' END AS vote_type,
    now() - ((gs % 90) * interval '1 day') AS voted_at
FROM generate_series(1, 500000) gs;

-- 21. LOYALTY_ACCOUNTS
INSERT INTO loyalty_accounts (user_id, current_balance, last_updated)
SELECT
    user_id,
    0 AS current_balance,
    now() AS last_updated
FROM user_roles
WHERE role_id = 2;

-- 22. WISHLISTS
INSERT INTO wishlists (user_id, created_at)
SELECT
    user_id,
    '2023-01-01 00:00:00+00'::timestamptz + ((user_id % 700) * interval '1 day') AS created_at
FROM user_roles
WHERE role_id = 2;

-- 23. WISHLIST_ITEMS
INSERT INTO wishlist_items (wishlist_id, variant_id, added_at)
SELECT
    w.wishlist_id,
    ((w.user_id * 7919 + gs * 3571)::bigint % 1500000 + 1)::bigint AS variant_id,
    w.created_at + (gs * interval '1 day') AS added_at
FROM wishlists w
CROSS JOIN LATERAL generate_series(1, CASE WHEN w.user_id % 100 < 70 THEN 1 ELSE 2 END) gs
ON CONFLICT (wishlist_id, variant_id) DO NOTHING;

-- 24. PRODUCT_WAITLISTS
INSERT INTO product_waitlists (user_id, variant_id, added_at, status)
SELECT DISTINCT ON (user_id, variant_id)
    ((gs * 3571) % 799999 + 2)::bigint AS user_id,
    ((gs * 7919) % 1500000 + 1)::bigint AS variant_id,
    now() - ((gs % 120) * interval '1 day') AS added_at,
    CASE
        WHEN gs % 100 < 70 THEN 'ACTIVE'
        WHEN gs % 100 < 85 THEN 'NOTIFIED'
        WHEN gs % 100 < 95 THEN 'REMOVED'
        ELSE 'FULFILLED'
    END AS status
FROM generate_series(1, 120000) gs;

-- 25. ORDERS
INSERT INTO orders (order_id, user_id, shipping_method_id, order_date, total_amount, status)
SELECT
    gs AS order_id,
    ((gs * 3571) % 799999 + 2)::bigint AS user_id,
    CASE WHEN gs % 100 < 15 THEN NULL ELSE ((gs % 3) + 1)::bigint END AS shipping_method_id,
    CASE WHEN gs % 100 < 15 THEN NULL ELSE '2024-01-01 00:00:00+00'::timestamptz + ((gs % 365) * interval '1 day') END AS order_date,
    CASE WHEN gs % 100 < 15 THEN NULL ELSE 1::numeric(12,2) END AS total_amount,
    CASE
        WHEN gs % 100 < 15 THEN 'CART'
        WHEN gs % 100 < 35 THEN 'PLACED'
        WHEN gs % 100 < 55 THEN 'PAID'
        WHEN gs % 100 < 75 THEN 'SHIPPED'
        WHEN gs % 100 < 95 THEN 'COMPLETED'
        ELSE 'CANCELLED'
    END AS status
FROM generate_series(1, 2000000) gs;

-- 26. ORDER_ITEMS
INSERT INTO order_items (order_id, price_history_id, variant_id, quantity, unit_price)
WITH latest_price AS (
    SELECT DISTINCT ON (variant_id)
        history_id,
        variant_id,
        new_price
    FROM product_price_history
    ORDER BY variant_id, change_date DESC, history_id DESC
)
SELECT
    o.order_id,
    lp.history_id AS price_history_id,
    ov.variant_id,
    CASE
        WHEN p.category_id IN (7,8,10,11,12,15,16,17,18,19,20) THEN ((o.order_id + gs.i) % 3) + 1
        ELSE 1
    END AS quantity,
    lp.new_price AS unit_price
FROM orders o
CROSS JOIN LATERAL generate_series(1, 5) gs(i)
CROSS JOIN LATERAL (
    SELECT ((o.order_id * 7919 + gs.i * 3571)::bigint % 1500000 + 1)::bigint AS variant_id
) ov
JOIN latest_price lp ON lp.variant_id = ov.variant_id
JOIN product_variants pv ON pv.variant_id = ov.variant_id
JOIN products p ON p.product_id = pv.product_id;

UPDATE orders o
SET total_amount = s.order_total
FROM (
    SELECT order_id, SUM(quantity * unit_price)::numeric(12,2) AS order_total
    FROM order_items
    GROUP BY order_id
) s
WHERE s.order_id = o.order_id
  AND o.status <> 'CART';

WITH limits AS (
    SELECT
        percentile_cont(0.50) WITHIN GROUP (ORDER BY total_amount) AS p50_limit,
        percentile_cont(0.75) WITHIN GROUP (ORDER BY total_amount) AS p75_limit
    FROM orders
    WHERE status <> 'CART'
      AND total_amount IS NOT NULL
)
UPDATE orders o
SET shipping_method_id = CASE
    WHEN o.status = 'CART' THEN NULL
    WHEN o.total_amount < limits.p50_limit THEN 1
    WHEN o.total_amount < limits.p75_limit THEN 2
    ELSE 3
END
FROM limits;

-- 27. ORDER_STATUS_HISTORY
INSERT INTO order_status_history (order_id, old_status, new_status, change_date)
SELECT
    o.order_id,
    h.old_status,
    h.new_status,
    o.order_date + h.time_offset AS change_date
FROM orders o
CROSS JOIN LATERAL (
    SELECT 'CART' AS old_status, 'PLACED' AS new_status, (((o.order_id % 10) + 5) * interval '1 minute') AS time_offset
    WHERE o.status IN ('PLACED','PAID','SHIPPED','COMPLETED','CANCELLED')
    UNION ALL
    SELECT 'PLACED', 'PAID', (((o.order_id % 60) + 20) * interval '1 minute')
    WHERE o.status IN ('PAID','SHIPPED','COMPLETED')
    UNION ALL
    SELECT 'PAID', 'SHIPPED', (((o.order_id % 3) + 1) * interval '1 day')
    WHERE o.status IN ('SHIPPED','COMPLETED')
    UNION ALL
    SELECT 'SHIPPED', 'COMPLETED', (((o.order_id % 7) + 4) * interval '1 day')
    WHERE o.status = 'COMPLETED'
    UNION ALL
    SELECT 'PLACED', 'CANCELLED', (((o.order_id % 24) + 1) * interval '1 hour')
    WHERE o.status = 'CANCELLED'
) h
WHERE o.status <> 'CART';

-- 28. ORDER_DISCOUNTS
INSERT INTO order_discounts (order_id, coupon_id, applied_at, amount_saved)
WITH valid_orders AS (
    SELECT order_id, order_date, total_amount
    FROM orders
    WHERE status <> 'CART'
      AND total_amount IS NOT NULL
      AND order_id % 5 = 0
), eligible_coupons AS (
    SELECT
        vo.order_id,
        vo.order_date,
        vo.total_amount,
        c.coupon_id,
        c.discount_type,
        c.discount_value,
        ROW_NUMBER() OVER (PARTITION BY vo.order_id ORDER BY c.coupon_id) AS rn,
        COUNT(*) OVER (PARTITION BY vo.order_id) AS cnt
    FROM valid_orders vo
    JOIN coupons c
      ON vo.total_amount >= COALESCE(c.min_order_value, 0)
     AND vo.order_date BETWEEN c.valid_from AND c.valid_to
)
SELECT
    order_id,
    coupon_id,
    order_date + interval '10 minutes' AS applied_at,
    LEAST(total_amount, CASE WHEN upper(discount_type) LIKE '%PERCENT%' THEN total_amount * discount_value / 100.0 ELSE discount_value END)::numeric(12,2) AS amount_saved
FROM eligible_coupons
WHERE rn = ((order_id * 7919)::bigint % cnt) + 1
ON CONFLICT (order_id, coupon_id) DO NOTHING;

-- 29. PAYMENTS
INSERT INTO payments (order_id, payment_method, amount, payment_status, transaction_id, payment_date)
SELECT
    o.order_id,
    (ARRAY['CARD','PAYPAL','BANK_TRANSFER','CASH_ON_DELIVERY'])[(o.order_id % 4) + 1] AS payment_method,
    GREATEST(o.total_amount - COALESCE(od.discount_total, 0), 0)::numeric(12,2) AS amount,
    CASE
        WHEN o.status IN ('PAID','SHIPPED','COMPLETED') THEN 'SUCCESS'
        WHEN o.status = 'PLACED' THEN 'PENDING'
        WHEN o.status = 'CANCELLED' THEN CASE WHEN o.order_id % 2 = 0 THEN 'FAILED' ELSE 'REFUNDED' END
        ELSE 'FAILED'
    END AS payment_status,
    'TXN-' || o.order_id || '-MAIN' AS transaction_id,
    o.order_date + interval '30 minutes' AS payment_date
FROM orders o
LEFT JOIN (
    SELECT order_id, SUM(amount_saved) AS discount_total
    FROM order_discounts
    GROUP BY order_id
) od ON od.order_id = o.order_id
WHERE o.status <> 'CART';

-- 30. LOYALTY_HISTORY
INSERT INTO loyalty_history (loyalty_account_id, order_id, payment_id, points_change, transaction_type, description, created_at)
SELECT
    la.loyalty_account_id,
    o.order_id,
    p.payment_id,
    CASE
        WHEN p.payment_status = 'SUCCESS' THEN floor(p.amount / 100)::int
        WHEN p.payment_status = 'REFUNDED' THEN -floor(p.amount / 100)::int
        ELSE 0
    END AS points_change,
    CASE
        WHEN p.payment_status = 'SUCCESS' THEN 'EARN'
        WHEN p.payment_status = 'REFUNDED' THEN 'REFUND'
        ELSE 'ADJUSTMENT'
    END AS transaction_type,
    CASE
        WHEN p.payment_status = 'SUCCESS' THEN 'Points earned from successful payment.'
        WHEN p.payment_status = 'REFUNDED' THEN 'Points reversed because payment was refunded.'
        ELSE 'No point movement for unsuccessful payment.'
    END AS description,
    p.payment_date AS created_at
FROM payments p
JOIN orders o ON o.order_id = p.order_id
JOIN loyalty_accounts la ON la.user_id = o.user_id
WHERE p.payment_status IN ('SUCCESS','REFUNDED');

UPDATE loyalty_accounts la
SET current_balance = GREATEST(s.balance, 0),
    last_updated = now()
FROM (
    SELECT loyalty_account_id, SUM(points_change)::int AS balance
    FROM loyalty_history
    GROUP BY loyalty_account_id
) s
WHERE s.loyalty_account_id = la.loyalty_account_id;

-- 31. SHIPMENTS
INSERT INTO shipments (order_id, address_id, courier_id, tracking_number, shipped_date, estimated_arrival, delivered_date, status)
SELECT
    o.order_id,
    a.address_id,
    975001 + (o.order_id % 25000) AS courier_id,
    'TRK-' || o.order_id AS tracking_number,
    CASE WHEN o.status IN ('SHIPPED','COMPLETED') THEN o.order_date + (((o.order_id % 3) + 1) * interval '1 day') ELSE NULL END AS shipped_date,
    CASE WHEN o.status IN ('SHIPPED','COMPLETED') THEN (o.order_date + (((o.order_id % 10) + 3) * interval '1 day'))::date ELSE NULL END AS estimated_arrival,
    CASE WHEN o.status = 'COMPLETED' THEN o.order_date + (((o.order_id % 7) + 4) * interval '1 day') ELSE NULL END AS delivered_date,
    CASE
        WHEN o.status = 'COMPLETED' THEN 'DELIVERED'
        WHEN o.status = 'SHIPPED' THEN CASE WHEN o.order_id % 2 = 0 THEN 'SHIPPED' ELSE 'IN_TRANSIT' END
        ELSE 'PENDING'
    END AS status
FROM orders o
JOIN addresses a ON a.user_id = o.user_id AND a.is_default = true
WHERE o.status IN ('PAID','SHIPPED','COMPLETED');

-- 32. SHIPMENT_ITEMS
INSERT INTO shipment_items (shipment_id, order_item_id, instance_id, quantity_shipped)
WITH demand AS (
    SELECT
        s.shipment_id,
        oi.order_item_id,
        oi.variant_id,
        ROW_NUMBER() OVER (PARTITION BY oi.variant_id ORDER BY s.shipment_id, oi.order_item_id, gs) AS rn
    FROM shipments s
    JOIN orders o ON o.order_id = s.order_id
    JOIN order_items oi ON oi.order_id = o.order_id
    CROSS JOIN LATERAL generate_series(1, oi.quantity) gs
    WHERE o.status IN ('SHIPPED','COMPLETED')
), available_instances AS (
    SELECT
        pi.instance_id,
        pi.variant_id,
        ROW_NUMBER() OVER (PARTITION BY pi.variant_id ORDER BY pi.instance_id) AS rn
    FROM product_instances pi
    WHERE pi.status = 'AVAILABLE'
)
SELECT
    d.shipment_id,
    d.order_item_id,
    ai.instance_id,
    1 AS quantity_shipped
FROM demand d
JOIN available_instances ai
  ON ai.variant_id = d.variant_id
 AND ai.rn = d.rn;

UPDATE product_instances pi
SET status = 'DELIVERED'
FROM shipment_items si
WHERE si.instance_id = pi.instance_id;

UPDATE inventory_items ii
SET quantity = s.available_count
FROM (
    SELECT variant_id, warehouse_id, COUNT(*)::int AS available_count
    FROM product_instances
    WHERE status = 'AVAILABLE'
    GROUP BY variant_id, warehouse_id
) s
WHERE s.variant_id = ii.variant_id
  AND s.warehouse_id = ii.warehouse_id;

UPDATE inventory_items ii
SET quantity = 0
WHERE NOT EXISTS (
    SELECT 1
    FROM product_instances pi
    WHERE pi.variant_id = ii.variant_id
      AND pi.warehouse_id = ii.warehouse_id
      AND pi.status = 'AVAILABLE'
);

UPDATE product_variants pv
SET stock_total = COALESCE(s.total_quantity, 0)
FROM (
    SELECT variant_id, SUM(quantity)::int AS total_quantity
    FROM inventory_items
    GROUP BY variant_id
) s
WHERE s.variant_id = pv.variant_id;

-- 33. AUTH_LOGS
INSERT INTO auth_logs (user_id, login_timestamp, ip_address, device_info, status)
SELECT
    ((gs * 3571) % 1000000 + 1)::bigint AS user_id,
    now() - ((gs % 365) * interval '1 day'),
    '192.168.' || (gs % 255) || '.' || ((gs * 7) % 255) AS ip_address,
    (ARRAY['iPhone 15 Pro','Windows 11 / Chrome','MacOS / Safari','Android 14 / Firefox'])[(gs % 4) + 1] AS device_info,
    CASE WHEN gs % 100 < 85 THEN 'SUCCESS' ELSE 'FAILED' END AS status
FROM generate_series(1, 1000000) gs;

-- 34. USER_SESSIONS
INSERT INTO user_sessions (user_id, session_token, login_time, expiry_time)
SELECT
    user_id,
    md5(user_id::text || '-session-token') AS session_token,
    now() - interval '1 hour' AS login_time,
    now() + interval '2 hours' AS expiry_time
FROM users
WHERE user_id BETWEEN 1 AND 1000000;

-- 35. USER_NOTIFICATIONS
INSERT INTO user_notifications (user_id, type, message, sent_at)
SELECT
    ur.user_id,
    CASE gs.t
        WHEN 1 THEN 'ORDER_UPDATE'
        WHEN 2 THEN 'PRICE_DROP'
        WHEN 3 THEN 'PROMOTION'
        WHEN 4 THEN 'ACCOUNT_SECURITY'
        WHEN 5 THEN 'SHIPMENT_UPDATE'
        WHEN 6 THEN 'REVIEW_REQUEST'
        ELSE 'WISHLIST_UPDATE'
    END AS type,
    CASE gs.t
        WHEN 1 THEN 'Your order has been updated and is now being processed.'
        WHEN 2 THEN 'Good news! An item in your cart has a new lower price.'
        WHEN 3 THEN 'Flash Sale! Use code BLINK20 for 20% off your next tech purchase.'
        WHEN 4 THEN 'Security alert: A new login was detected on your account.'
        WHEN 5 THEN 'Your BlinkBuy package is out for delivery with our courier.'
        WHEN 6 THEN 'How do you like your new device? Share your thoughts with a review.'
        ELSE 'An item from your wishlist is back in stock.'
    END AS message,
    now() - ((ur.user_id % 60) * interval '1 day') AS sent_at
FROM user_roles ur
CROSS JOIN LATERAL (SELECT ((ur.user_id % 7) + 1) AS t) gs
WHERE ur.role_id = 2;

-- 36. SUPPORT_TICKETS
INSERT INTO support_tickets (user_id, subject, message_body, status, priority, created_at, resolved_at)
SELECT
    ((gs * 3571) % 799999 + 2)::bigint AS user_id,
    'Support request #' || gs AS subject,
    'Customer reported an issue with an order, product, payment, shipment, or account setting.' AS message_body,
    CASE
        WHEN gs % 100 < 35 THEN 'OPEN'
        WHEN gs % 100 < 60 THEN 'IN_PROGRESS'
        WHEN gs % 100 < 85 THEN 'RESOLVED'
        ELSE 'CLOSED'
    END AS status,
    CASE
        WHEN gs % 100 < 70 THEN 'LOW'
        WHEN gs % 100 < 90 THEN 'MEDIUM'
        ELSE 'HIGH'
    END AS priority,
    now() - ((gs % 180) * interval '1 day') AS created_at,
    CASE WHEN gs % 100 >= 60 THEN now() - ((gs % 100) * interval '1 day') ELSE NULL END AS resolved_at
FROM generate_series(1, 40000) gs;