Advanced Conditional Logic Examples

-- ========================================
-- COMPLEX AND/OR COMBINATIONS
-- ========================================

-- Wrong: Without parentheses (confusing precedence)
SELECT * FROM products 
WHERE category = 'Electronics' OR category = 'Computers' 
  AND price < 500;  -- AND has higher precedence!

-- Correct: With parentheses for clarity
SELECT * FROM products 
WHERE (category = 'Electronics' OR category = 'Computers')
  AND price < 500;

-- Multiple condition groups
SELECT * FROM orders 
WHERE 
    (status = 'pending' AND priority = 'high')
    OR (status = 'processing' AND created_at < DATE_SUB(NOW(), INTERVAL 2 DAY))
    OR (customer_type = 'VIP' AND total_amount > 1000);

-- ========================================
-- XOR (Exclusive OR) - One or the other, not both
-- ========================================

-- Find products that are either on sale OR featured, but not both
SELECT * FROM products 
WHERE is_on_sale XOR is_featured;

-- Manual XOR implementation
SELECT * FROM products 
WHERE (is_on_sale = TRUE AND is_featured = FALSE)
   OR (is_on_sale = FALSE AND is_featured = TRUE);

-- ========================================
-- NESTED CONDITIONS WITH SUBQUERIES
-- ========================================

SELECT * FROM customers 
WHERE 
    customer_id IN (
        SELECT customer_id FROM orders 
        WHERE total_amount > 500
    )
    AND NOT EXISTS (
        SELECT 1 FROM complaints 
        WHERE complaints.customer_id = customers.customer_id
    )
    AND (
        loyalty_points > 1000 
        OR registration_date < DATE_SUB(NOW(), INTERVAL 2 YEAR)
    );

-- ========================================
-- DYNAMIC CONDITION BUILDING
-- ========================================

-- Simulating optional filters (useful for application logic)
SELECT * FROM products 
WHERE 1=1  -- Always true, allows easy AND additions
    AND (@category IS NULL OR category = @category)
    AND (@min_price IS NULL OR price >= @min_price)
    AND (@max_price IS NULL OR price <= @max_price)
    AND (@search IS NULL OR name LIKE CONCAT('%', @search, '%'));

-- Using COALESCE for optional parameters
SELECT * FROM products 
WHERE category = COALESCE(@category, category)
  AND price >= COALESCE(@min_price, 0)
  AND price <= COALESCE(@max_price, 999999);

-- ========================================
-- COMPLEX DATE FILTERING
-- ========================================

-- Business hours filter (weekdays 9-5)
SELECT * FROM orders 
WHERE 
    DAYOFWEEK(order_date) BETWEEN 2 AND 6  -- Monday to Friday
    AND HOUR(order_time) BETWEEN 9 AND 17;

-- Seasonal filtering
SELECT * FROM sales 
WHERE 
    (MONTH(sale_date) IN (12, 1, 2) AND product_category = 'Winter Clothing')
    OR (MONTH(sale_date) IN (6, 7, 8) AND product_category = 'Summer Clothing');

-- Relative date ranges with complex logic
SELECT * FROM events 
WHERE 
    (
        -- Future events in next 30 days
        event_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)
    )
    OR 
    (
        -- Recurring annual events
        MONTH(event_date) = MONTH(CURDATE()) 
        AND DAY(event_date) BETWEEN DAY(CURDATE()) AND DAY(DATE_ADD(CURDATE(), INTERVAL 7 DAY))
    );

-- ========================================
-- BITWISE OPERATIONS FOR FLAGS
-- ========================================

-- Permission flags using bitwise operations
-- 1 = Read, 2 = Write, 4 = Delete, 8 = Admin
SELECT * FROM users 
WHERE permissions & 1 = 1;  -- Has read permission

SELECT * FROM users 
WHERE permissions & 6 = 6;  -- Has both write AND delete (2 + 4)

SELECT * FROM users 
WHERE permissions & 8 = 8;  -- Is admin

-- Multiple permission check
SELECT 
    username,
    CASE 
        WHEN permissions & 8 = 8 THEN 'Admin'
        WHEN permissions & 6 = 6 THEN 'Editor'
        WHEN permissions & 1 = 1 THEN 'Viewer'
        ELSE 'No Access'
    END AS access_level
FROM users;