-- Sample Discount Data for Testing DiscountEngine
-- Run this after replacing UUIDs with actual values from your database

-- ============================================
-- SAMPLE PRODUCT DISCOUNTS (Simple Fixed Amount)
-- ============================================

-- Example: $5 off Product A
INSERT INTO product_discounts (
    org_id,
    product_id,
    discount_amount,
    start_date,
    end_date,
    isDeleted
) VALUES (
    'YOUR-ORG-UUID-HERE',      -- Replace with actual org_id
    'YOUR-PRODUCT-UUID-HERE',   -- Replace with actual product_id
    5.00,
    CURRENT_DATE,
    CURRENT_DATE + INTERVAL '30 days',
    FALSE
);

-- ============================================
-- SAMPLE DISCOUNT RULES (Complex Promotions)
-- ============================================

-- 1. Product Percentage Discount (20% off specific product)
INSERT INTO discount_rules (
    org_id,
    name,
    discount_type,
    product_id,
    percentage_value,
    start_date,
    end_date,
    isDeleted
) VALUES (
    'YOUR-ORG-UUID-HERE',
    '20% Off Flash Sale',
    'product_percent',
    'YOUR-PRODUCT-UUID-HERE',
    20.0,
    CURRENT_DATE,
    CURRENT_DATE + INTERVAL '7 days',
    FALSE
);

-- 2. Category Percentage Discount (15% off all items in category)
INSERT INTO discount_rules (
    org_id,
    name,
    discount_type,
    category_id,
    percentage_value,
    start_date,
    end_date,
    isDeleted
) VALUES (
    'YOUR-ORG-UUID-HERE',
    '15% Off Electronics',
    'category_percent',
    'YOUR-CATEGORY-UUID-HERE',
    15.0,
    CURRENT_DATE,
    CURRENT_DATE + INTERVAL '14 days',
    FALSE
);

-- 3. Storewide Percentage Discount (10% off everything)
INSERT INTO discount_rules (
    org_id,
    name,
    discount_type,
    percentage_value,
    start_date,
    end_date,
    isDeleted
) VALUES (
    'YOUR-ORG-UUID-HERE',
    'Storewide 10% Off Weekend Sale',
    'store_percent',
    10.0,
    CURRENT_DATE,
    CURRENT_DATE + INTERVAL '3 days',
    FALSE
);

-- 4. BOGO Product (Buy 1 Get 1 Free)
INSERT INTO discount_rules (
    org_id,
    name,
    discount_type,
    product_id,
    buy_quantity,
    free_quantity,
    start_date,
    end_date,
    isDeleted
) VALUES (
    'YOUR-ORG-UUID-HERE',
    'Buy 1 Get 1 Free Special',
    'bogo_product',
    'YOUR-PRODUCT-UUID-HERE',
    1,
    1,
    CURRENT_DATE,
    CURRENT_DATE + INTERVAL '7 days',
    FALSE
);

-- 5. BOGO Category (Buy 2 Get 1 Free on category)
INSERT INTO discount_rules (
    org_id,
    name,
    discount_type,
    category_id,
    buy_quantity,
    free_quantity,
    start_date,
    end_date,
    isDeleted
) VALUES (
    'YOUR-ORG-UUID-HERE',
    'Buy 2 Shirts Get 1 Free',
    'bogo_category',
    'YOUR-CATEGORY-UUID-HERE',
    2,
    1,
    CURRENT_DATE,
    CURRENT_DATE + INTERVAL '14 days',
    FALSE
);

-- 6. BOGO Store (Buy 5 items Get 1 Free storewide)
INSERT INTO discount_rules (
    org_id,
    name,
    discount_type,
    buy_quantity,
    free_quantity,
    start_date,
    end_date,
    isDeleted
) VALUES (
    'YOUR-ORG-UUID-HERE',
    'Buy 5 Get 1 Free Storewide',
    'bogo_store',
    5,
    1,
    CURRENT_DATE,
    CURRENT_DATE + INTERVAL '30 days',
    FALSE
);

-- 7. Bundle Pricing (3 for $10)
INSERT INTO discount_rules (
    org_id,
    name,
    discount_type,
    product_id,
    bundle_quantity,
    bundle_price,
    start_date,
    end_date,
    isDeleted
) VALUES (
    'YOUR-ORG-UUID-HERE',
    '3 for $10 Bundle Deal',
    'x_for_y',
    'YOUR-PRODUCT-UUID-HERE',
    3,
    10.00,
    CURRENT_DATE,
    CURRENT_DATE + INTERVAL '14 days',
    FALSE
);

-- 8. Quantity Threshold (Buy 5+ Save 20%)
INSERT INTO discount_rules (
    org_id,
    name,
    discount_type,
    product_id,
    threshold_quantity,
    percentage_value,
    start_date,
    end_date,
    isDeleted
) VALUES (
    'YOUR-ORG-UUID-HERE',
    'Buy 5 or More Save 20%',
    'percent_for_x_items',
    'YOUR-PRODUCT-UUID-HERE',
    5,
    20.0,
    CURRENT_DATE,
    CURRENT_DATE + INTERVAL '30 days',
    FALSE
);

-- 9. Product Amount Discount (Fixed $10 off)
INSERT INTO discount_rules (
    org_id,
    name,
    discount_type,
    product_id,
    amount_value,
    start_date,
    end_date,
    isDeleted
) VALUES (
    'YOUR-ORG-UUID-HERE',
    '$10 Off Premium Item',
    'product_amount',
    'YOUR-PRODUCT-UUID-HERE',
    10.00,
    CURRENT_DATE,
    CURRENT_DATE + INTERVAL '7 days',
    FALSE
);

-- ============================================
-- QUERIES TO HELP FIND YOUR UUIDs
-- ============================================

-- Find your organization ID
-- SELECT org_id, name FROM orgs WHERE isDeleted = FALSE;

-- Find product IDs
-- SELECT product_id, name, price FROM products WHERE isDeleted = FALSE LIMIT 10;

-- Find category IDs
-- SELECT category_id, name FROM product_categories WHERE isDeleted = FALSE;

-- ============================================
-- TESTING QUERIES
-- ============================================

-- Check active simple discounts
SELECT 
    pd.discount_id,
    p.name as product_name,
    pd.discount_amount,
    pd.start_date,
    pd.end_date
FROM product_discounts pd
JOIN products p ON pd.product_id = p.product_id
WHERE pd.isDeleted = FALSE
    AND (pd.start_date IS NULL OR pd.start_date <= CURRENT_DATE)
    AND (pd.end_date IS NULL OR pd.end_date >= CURRENT_DATE);

-- Check active discount rules
SELECT 
    discount_rule_id,
    name,
    discount_type,
    start_date,
    end_date
FROM discount_rules
WHERE isDeleted = FALSE
    AND (start_date IS NULL OR start_date <= CURRENT_DATE)
    AND (end_date IS NULL OR end_date >= CURRENT_DATE)
ORDER BY discount_type;

-- Find products with multiple applicable discounts (for testing best discount selection)
SELECT 
    p.product_id,
    p.name,
    p.price,
    COUNT(DISTINCT pd.discount_id) as simple_discounts,
    COUNT(DISTINCT dr.discount_rule_id) as rule_discounts
FROM products p
LEFT JOIN product_discounts pd ON p.product_id = pd.product_id 
    AND pd.isDeleted = FALSE
    AND (pd.start_date IS NULL OR pd.start_date <= CURRENT_DATE)
    AND (pd.end_date IS NULL OR pd.end_date >= CURRENT_DATE)
LEFT JOIN discount_rules dr ON (
    (dr.product_id = p.product_id OR dr.category_id = p.category_id OR dr.discount_type = 'store_percent')
    AND dr.isDeleted = FALSE
    AND (dr.start_date IS NULL OR dr.start_date <= CURRENT_DATE)
    AND (dr.end_date IS NULL OR dr.end_date >= CURRENT_DATE)
)
WHERE p.isDeleted = FALSE
GROUP BY p.product_id, p.name, p.price
HAVING COUNT(DISTINCT pd.discount_id) + COUNT(DISTINCT dr.discount_rule_id) > 1;

-- ============================================
-- CLEANUP (Run if you want to remove test data)
-- ============================================

-- Delete test discounts (be careful!)
-- DELETE FROM product_discounts WHERE name LIKE '%Test%' OR name LIKE '%Sample%';
-- DELETE FROM discount_rules WHERE name LIKE '%Test%' OR name LIKE '%Sample%';
