-- ============================================================================
-- Phase 8: Analytics Data Generation Script
-- ============================================================================
-- Purpose: Generate substantial transaction data for Cybex PTE LTD
-- to populate Phase 8 analytics dashboards with realistic data
--
-- Branch Structure:
-- - Bulk (Parent) -> Shop, Liquor, Cosmetic, Milkbar
-- - Independent Branch (Separate)
--
-- Data Generated:
-- - 6 branches
-- - 50+ products across various categories
-- - 20+ customers
-- - 5+ cashiers
-- - 1000+ sales transactions over 12 months
-- - 3000+ sales line items
-- - 2000+ payment line items
-- ============================================================================

-- Get Cybex org_id
DO $$
DECLARE
    v_org_id UUID := '0194a8b2-1234-7123-8123-123456789abc';
    v_bulk_branch UUID;
    v_shop_branch UUID;
    v_liquor_branch UUID;
    v_cosmetic_branch UUID;
    v_milkbar_branch UUID;
    v_independent_branch UUID;
    
    -- Product IDs
    v_product_ids UUID[];
    v_category_ids UUID[];
    v_customer_ids UUID[];
    v_cashier_ids UUID[];
    
    -- Counters
    v_counter INT;
    v_transaction_id UUID;
    v_line_item_id UUID;
    v_payment_id UUID;
    
    -- Random variables
    v_random_product UUID;
    v_random_customer UUID;
    v_random_cashier UUID;
    v_random_branch UUID;
    v_random_date DATE;
    v_quantity DECIMAL(10,2);
    v_unit_price DECIMAL(10,2);
    v_discount_pct DECIMAL(5,2);
    v_vat_rate DECIMAL(5,2);
    v_line_total DECIMAL(10,2);
    v_payment_amount DECIMAL(10,2);
    
BEGIN
    RAISE NOTICE 'Starting Phase 8 Analytics Data Generation for Cybex PTE LTD';
    
    -- ========================================================================
    -- 1. CREATE BRANCHES
    -- ========================================================================
    RAISE NOTICE 'Step 1: Creating branches...';
    
    -- Bulk (Parent Branch)
    INSERT INTO branches (branch_id, name, org_id, parent_branch_id, isdeleted)
    VALUES (uuid_generate_v7(), 'Bulk Warehouse', v_org_id, NULL, false)
    RETURNING branch_id INTO v_bulk_branch;
    RAISE NOTICE '  Created Bulk Warehouse: %', v_bulk_branch;
    
    -- Child branches linked to Bulk
    INSERT INTO branches (branch_id, name, org_id, parent_branch_id, isdeleted)
    VALUES (uuid_generate_v7(), 'Shop', v_org_id, v_bulk_branch, false)
    RETURNING branch_id INTO v_shop_branch;
    RAISE NOTICE '  Created Shop: %', v_shop_branch;
    
    INSERT INTO branches (branch_id, name, org_id, parent_branch_id, isdeleted)
    VALUES (uuid_generate_v7(), 'Liquor Store', v_org_id, v_bulk_branch, false)
    RETURNING branch_id INTO v_liquor_branch;
    RAISE NOTICE '  Created Liquor Store: %', v_liquor_branch;
    
    INSERT INTO branches (branch_id, name, org_id, parent_branch_id, isdeleted)
    VALUES (uuid_generate_v7(), 'Cosmetic Shop', v_org_id, v_bulk_branch, false)
    RETURNING branch_id INTO v_cosmetic_branch;
    RAISE NOTICE '  Created Cosmetic Shop: %', v_cosmetic_branch;
    
    INSERT INTO branches (branch_id, name, org_id, parent_branch_id, isdeleted)
    VALUES (uuid_generate_v7(), 'Milk Bar', v_org_id, v_bulk_branch, false)
    RETURNING branch_id INTO v_milkbar_branch;
    RAISE NOTICE '  Created Milk Bar: %', v_milkbar_branch;
    
    -- Independent branch
    INSERT INTO branches (branch_id, name, org_id, parent_branch_id, isdeleted)
    VALUES (uuid_generate_v7(), 'Downtown Store', v_org_id, NULL, false)
    RETURNING branch_id INTO v_independent_branch;
    RAISE NOTICE '  Created Downtown Store: %', v_independent_branch;
    
    -- ========================================================================
    -- 2. CREATE PRODUCT CATEGORIES
    -- ========================================================================
    RAISE NOTICE 'Step 2: Creating product categories...';
    
    v_category_ids := ARRAY[]::UUID[];
    
    FOR v_counter IN 1..10 LOOP
        INSERT INTO product_categories (category_id, name, org_id, isdeleted)
        VALUES (
            uuid_generate_v7(),
            CASE v_counter
                WHEN 1 THEN 'Beverages'
                WHEN 2 THEN 'Snacks & Confectionery'
                WHEN 3 THEN 'Dairy Products'
                WHEN 4 THEN 'Alcoholic Beverages'
                WHEN 5 THEN 'Beauty & Cosmetics'
                WHEN 6 THEN 'Personal Care'
                WHEN 7 THEN 'Household Items'
                WHEN 8 THEN 'Tobacco'
                WHEN 9 THEN 'Fresh Produce'
                WHEN 10 THEN 'Frozen Foods'
            END,
            v_org_id,
            false
        ) RETURNING category_id INTO v_random_product;
        v_category_ids := array_append(v_category_ids, v_random_product);
    END LOOP;
    
    RAISE NOTICE '  Created % categories', array_length(v_category_ids, 1);
    
    -- ========================================================================
    -- 3. CREATE PRODUCTS
    -- ========================================================================
    RAISE NOTICE 'Step 3: Creating products...';
    
    v_product_ids := ARRAY[]::UUID[];
    
    -- Beverages (15 products)
    FOR v_counter IN 1..15 LOOP
        INSERT INTO products (product_id, name, category_id, barcode, unit_price, cost_price, vat_rate, org_id, isdeleted)
        VALUES (
            uuid_generate_v7(),
            'Beverage Product ' || v_counter,
            v_category_ids[1],
            'BEV' || LPAD(v_counter::TEXT, 6, '0'),
            (RANDOM() * 5 + 1)::DECIMAL(10,2), -- Price $1-6
            (RANDOM() * 3 + 0.5)::DECIMAL(10,2), -- Cost $0.5-3.5
            12.5,
            v_org_id,
            false
        ) RETURNING product_id INTO v_random_product;
        v_product_ids := array_append(v_product_ids, v_random_product);
    END LOOP;
    
    -- Snacks (10 products)
    FOR v_counter IN 1..10 LOOP
        INSERT INTO products (product_id, name, category_id, barcode, unit_price, cost_price, vat_rate, org_id, isdeleted)
        VALUES (
            uuid_generate_v7(),
            'Snack Item ' || v_counter,
            v_category_ids[2],
            'SNK' || LPAD(v_counter::TEXT, 6, '0'),
            (RANDOM() * 3 + 0.5)::DECIMAL(10,2),
            (RANDOM() * 2 + 0.3)::DECIMAL(10,2),
            12.5,
            v_org_id,
            false
        ) RETURNING product_id INTO v_random_product;
        v_product_ids := array_append(v_product_ids, v_random_product);
    END LOOP;
    
    -- Dairy (8 products)
    FOR v_counter IN 1..8 LOOP
        INSERT INTO products (product_id, name, category_id, barcode, unit_price, cost_price, vat_rate, org_id, isdeleted)
        VALUES (
            uuid_generate_v7(),
            'Dairy Product ' || v_counter,
            v_category_ids[3],
            'DRY' || LPAD(v_counter::TEXT, 6, '0'),
            (RANDOM() * 4 + 2)::DECIMAL(10,2),
            (RANDOM() * 2.5 + 1)::DECIMAL(10,2),
            0, -- Zero-rated
            v_org_id,
            false
        ) RETURNING product_id INTO v_random_product;
        v_product_ids := array_append(v_product_ids, v_random_product);
    END LOOP;
    
    -- Alcohol (12 products)
    FOR v_counter IN 1..12 LOOP
        INSERT INTO products (product_id, name, category_id, barcode, unit_price, cost_price, vat_rate, org_id, isdeleted)
        VALUES (
            uuid_generate_v7(),
            'Liquor ' || v_counter,
            v_category_ids[4],
            'LIQ' || LPAD(v_counter::TEXT, 6, '0'),
            (RANDOM() * 30 + 10)::DECIMAL(10,2), -- $10-40
            (RANDOM() * 20 + 5)::DECIMAL(10,2),
            12.5,
            v_org_id,
            false
        ) RETURNING product_id INTO v_random_product;
        v_product_ids := array_append(v_product_ids, v_random_product);
    END LOOP;
    
    -- Cosmetics (10 products)
    FOR v_counter IN 1..10 LOOP
        INSERT INTO products (product_id, name, category_id, barcode, unit_price, cost_price, vat_rate, org_id, isdeleted)
        VALUES (
            uuid_generate_v7(),
            'Cosmetic Item ' || v_counter,
            v_category_ids[5],
            'COS' || LPAD(v_counter::TEXT, 6, '0'),
            (RANDOM() * 15 + 5)::DECIMAL(10,2), -- $5-20
            (RANDOM() * 8 + 2)::DECIMAL(10,2),
            12.5,
            v_org_id,
            false
        ) RETURNING product_id INTO v_random_product;
        v_product_ids := array_append(v_product_ids, v_random_product);
    END LOOP;
    
    -- Other categories (5 products each)
    FOR v_counter IN 1..5 LOOP
        INSERT INTO products (product_id, name, category_id, barcode, unit_price, cost_price, vat_rate, org_id, isdeleted)
        VALUES (
            uuid_generate_v7(),
            'Personal Care ' || v_counter,
            v_category_ids[6],
            'PER' || LPAD(v_counter::TEXT, 6, '0'),
            (RANDOM() * 8 + 2)::DECIMAL(10,2),
            (RANDOM() * 4 + 1)::DECIMAL(10,2),
            12.5,
            v_org_id,
            false
        ) RETURNING product_id INTO v_random_product;
        v_product_ids := array_append(v_product_ids, v_random_product);
    END LOOP;
    
    RAISE NOTICE '  Created % products', array_length(v_product_ids, 1);
    
    -- ========================================================================
    -- 4. CREATE CUSTOMERS
    -- ========================================================================
    RAISE NOTICE 'Step 4: Creating customers...';
    
    v_customer_ids := ARRAY[]::UUID[];
    
    FOR v_counter IN 1..25 LOOP
        INSERT INTO customers (customer_id, name, email, phone, org_id, isdeleted)
        VALUES (
            uuid_generate_v7(),
            'Customer ' || v_counter,
            'customer' || v_counter || '@example.com',
            '+1234567' || LPAD(v_counter::TEXT, 4, '0'),
            v_org_id,
            false
        ) RETURNING customer_id INTO v_random_customer;
        v_customer_ids := array_append(v_customer_ids, v_random_customer);
    END LOOP;
    
    RAISE NOTICE '  Created % customers', array_length(v_customer_ids, 1);
    
    -- ========================================================================
    -- 5. CREATE CASHIERS (USERS)
    -- ========================================================================
    RAISE NOTICE 'Step 5: Creating cashiers...';
    
    v_cashier_ids := ARRAY[]::UUID[];
    
    FOR v_counter IN 1..6 LOOP
        INSERT INTO users (user_id, username, email, org_id, isdeleted)
        VALUES (
            uuid_generate_v7(),
            'cashier' || v_counter,
            'cashier' || v_counter || '@cybex.com',
            v_org_id,
            false
        ) RETURNING user_id INTO v_random_cashier;
        v_cashier_ids := array_append(v_cashier_ids, v_random_cashier);
    END LOOP;
    
    RAISE NOTICE '  Created % cashiers', array_length(v_cashier_ids, 1);
    
    -- ========================================================================
    -- 6. GENERATE TRANSACTIONS
    -- ========================================================================
    RAISE NOTICE 'Step 6: Generating transactions (this will take a few minutes)...';
    
    -- Generate 1500 transactions over 12 months
    FOR v_counter IN 1..1500 LOOP
        -- Random date in last 12 months
        v_random_date := CURRENT_DATE - (RANDOM() * 365)::INT;
        
        -- Pick random branch (weighted: 40% shop, 25% liquor, 15% cosmetic, 10% milkbar, 10% independent)
        v_random_product := RANDOM();
        IF v_random_product < 0.4 THEN
            v_random_branch := v_shop_branch;
        ELSIF v_random_product < 0.65 THEN
            v_random_branch := v_liquor_branch;
        ELSIF v_random_product < 0.80 THEN
            v_random_branch := v_cosmetic_branch;
        ELSIF v_random_product < 0.90 THEN
            v_random_branch := v_milkbar_branch;
        ELSE
            v_random_branch := v_independent_branch;
        END IF;
        
        -- Pick random customer (80% have customer, 20% walk-in)
        IF RANDOM() < 0.8 THEN
            v_random_customer := v_customer_ids[1 + (RANDOM() * (array_length(v_customer_ids, 1) - 1))::INT];
        ELSE
            v_random_customer := NULL;
        END IF;
        
        -- Pick random cashier
        v_random_cashier := v_cashier_ids[1 + (RANDOM() * (array_length(v_cashier_ids, 1) - 1))::INT];
        
        -- Create transaction
        v_transaction_id := uuid_generate_v7();
        
        INSERT INTO transactions (
            transaction_id, 
            org_id, 
            branch_id, 
            customer_id, 
            cashier_id,
            transaction_date, 
            total_amount,
            status,
            isdeleted
        ) VALUES (
            v_transaction_id,
            v_org_id,
            v_random_branch,
            v_random_customer,
            v_random_cashier,
            v_random_date,
            0, -- Will be updated after line items
            'completed',
            false
        );
        
        -- Create 1-8 line items per transaction
        v_line_total := 0;
        FOR v_line_item_id IN 1..(1 + (RANDOM() * 7)::INT) LOOP
            -- Pick random product
            v_random_product := v_product_ids[1 + (RANDOM() * (array_length(v_product_ids, 1) - 1))::INT];
            
            -- Get product details
            SELECT unit_price, cost_price, vat_rate INTO v_unit_price, v_discount_pct, v_vat_rate
            FROM products WHERE product_id = v_random_product;
            
            -- Random quantity (1-10)
            v_quantity := (1 + RANDOM() * 9)::DECIMAL(10,2);
            
            -- Random discount (0-20%)
            v_discount_pct := (RANDOM() * 20)::DECIMAL(5,2);
            
            -- Calculate line total
            v_payment_amount := (v_quantity * v_unit_price * (1 - v_discount_pct/100) * (1 + v_vat_rate/100))::DECIMAL(10,2);
            v_line_total := v_line_total + v_payment_amount;
            
            -- Create line item (will trigger sales_line_items extraction)
            INSERT INTO lineitems (
                lineitem_id,
                transaction_id,
                product_id,
                quantity,
                unit_price,
                discount_percentage,
                line_total,
                org_id,
                isdeleted,
                metadata
            ) VALUES (
                uuid_generate_v7(),
                v_transaction_id,
                v_random_product,
                v_quantity,
                v_unit_price,
                v_discount_pct,
                v_payment_amount,
                v_org_id,
                false,
                jsonb_build_object(
                    'transaction_id', v_transaction_id::TEXT,
                    'product_id', v_random_product::TEXT,
                    'branch_id', v_random_branch::TEXT,
                    'customer_id', COALESCE(v_random_customer::TEXT, ''),
                    'cashier_id', v_random_cashier::TEXT,
                    'transaction_date', v_random_date::TEXT,
                    'quantity_in_base_units', v_quantity,
                    'unit_price', v_unit_price,
                    'cost_price', v_discount_pct,
                    'discount_amount', (v_quantity * v_unit_price * v_discount_pct / 100)::DECIMAL(10,2),
                    'subtotal_after_discount', (v_quantity * v_unit_price * (1 - v_discount_pct/100))::DECIMAL(10,2),
                    'vat_rate', v_vat_rate,
                    'vat_amount', (v_quantity * v_unit_price * (1 - v_discount_pct/100) * v_vat_rate / 100)::DECIMAL(10,2),
                    'line_total', v_payment_amount,
                    'profit_margin', (v_payment_amount - v_quantity * v_discount_pct)::DECIMAL(10,2)
                )
            );
        END LOOP;
        
        -- Update transaction total
        UPDATE transactions SET total_amount = v_line_total WHERE transaction_id = v_transaction_id;
        
        -- Create payment (will trigger payment_line_items extraction)
        INSERT INTO lineitems_payments (
            payment_id,
            transaction_id,
            payment_amount,
            payment_method,
            org_id,
            cashier_id,
            isdeleted,
            metadata
        ) VALUES (
            uuid_generate_v7(),
            v_transaction_id,
            v_line_total,
            CASE (RANDOM() * 4)::INT
                WHEN 0 THEN 'cash'
                WHEN 1 THEN 'card'
                WHEN 2 THEN 'mobile'
                ELSE 'bank_transfer'
            END,
            v_org_id,
            v_random_cashier,
            false,
            jsonb_build_object(
                'transaction_id', v_transaction_id::TEXT,
                'branch_id', v_random_branch::TEXT,
                'customer_id', COALESCE(v_random_customer::TEXT, ''),
                'cashier_id', v_random_cashier::TEXT,
                'transaction_date', v_random_date::TEXT,
                'payment_amount', v_line_total
            )
        );
        
        -- Progress indicator
        IF v_counter % 100 = 0 THEN
            RAISE NOTICE '    Generated % / 1500 transactions', v_counter;
        END IF;
    END LOOP;
    
    RAISE NOTICE '  ✓ Generated 1500 transactions with line items and payments';
    
    -- ========================================================================
    -- 7. SUMMARY
    -- ========================================================================
    RAISE NOTICE '';
    RAISE NOTICE '============================================================================';
    RAISE NOTICE 'Phase 8 Analytics Data Generation COMPLETE!';
    RAISE NOTICE '============================================================================';
    RAISE NOTICE 'Summary:';
    RAISE NOTICE '  Branches: 6 (Bulk + 4 children + 1 independent)';
    RAISE NOTICE '  Products: %', array_length(v_product_ids, 1);
    RAISE NOTICE '  Categories: %', array_length(v_category_ids, 1);
    RAISE NOTICE '  Customers: %', array_length(v_customer_ids, 1);
    RAISE NOTICE '  Cashiers: %', array_length(v_cashier_ids, 1);
    RAISE NOTICE '  Transactions: 1500 (over 12 months)';
    RAISE NOTICE '  Estimated Line Items: ~4500';
    RAISE NOTICE '  Estimated Payment Items: ~1500';
    RAISE NOTICE '';
    RAISE NOTICE 'All data owned by: Cybex PTE LTD (%)', v_org_id;
    RAISE NOTICE 'Date range: % to %', CURRENT_DATE - 365, CURRENT_DATE;
    RAISE NOTICE '============================================================================';
    
END $$;
