-- Basis INNER JOIN syntax
SELECT 
    table1.column1, 
    table1.column2,
    table2.column1, 
    table2.column2
FROM table1
INNER JOIN table2 
    ON table1.matching_column = table2.matching_column;

-- Haal alle klanten op die minstens één order hebben geplaatst
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    c.email,
    o.order_id,
    o.order_date,
    o.total_amount,
    o.status
FROM customers c
INNER JOIN orders o 
    ON c.customer_id = o.customer_id
ORDER BY o.order_date DESC;

-- Klanten met orders in 2025
SELECT 
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    COUNT(o.order_id) AS total_orders,
    SUM(o.total_amount) AS total_spent,
    MAX(o.order_date) AS last_order_date
FROM customers c
INNER JOIN orders o 
    ON c.customer_id = o.customer_id
WHERE o.order_date >= '2025-01-01'
    AND o.status = 'Delivered'
GROUP BY 
    c.customer_id, 
    c.first_name, 
    c.last_name
HAVING COUNT(o.order_id) >= 2
ORDER BY total_spent DESC;

-- Basis LEFT JOIN syntax
SELECT 
    left_table.column1, 
    left_table.column2,
    right_table.column1, 
    right_table.column2
FROM left_table
LEFT JOIN right_table 
    ON left_table.matching_column = right_table.matching_column;

-- Haal ALLE klanten op, ook die zonder orders
SELECT 
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    c.email,
    c.registration_date,
    o.order_id,
    o.order_date,
    o.total_amount,
    o.status,
    -- Toon 'No Orders' als klant geen orders heeft
    CASE 
        WHEN o.order_id IS NULL THEN 'No Orders'
        ELSE 'Has Orders'
    END AS order_status
FROM customers c
LEFT JOIN orders o 
    ON c.customer_id = o.customer_id
ORDER BY 
    o.order_date DESC NULLS LAST,
    c.registration_date;

-- Klanten die geregistreerd zijn maar NOOIT een order hebben geplaatst
SELECT 
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    c.email,
    c.registration_date,
    'No orders placed' AS status,
    -- Dagen sinds registratie
    DATEDIFF(DAY, c.registration_date, GETDATE()) AS days_since_registration
FROM customers c
LEFT JOIN orders o 
    ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL
    AND c.registration_date < DATEADD(MONTH, -3, GETDATE())  -- Meer dan 3 maanden geleden
ORDER BY c.registration_date DESC;

-- Basis RIGHT JOIN syntax
SELECT 
    left_table.column1, 
    left_table.column2,
    right_table.column1, 
    right_table.column2
FROM left_table
RIGHT JOIN right_table 
    ON left_table.matching_column = right_table.matching_column;

-- Vind orders waar klantgegevens mogelijk ontbreken in customers tabel
SELECT 
    o.order_id,
    o.order_date,
    o.total_amount,
    o.status,
    o.customer_id AS order_customer_id,
    c.customer_id AS customer_table_id,
    c.first_name,
    c.last_name,
    CASE 
        WHEN c.customer_id IS NULL THEN 'Customer Missing'
        ELSE 'Customer Found'
    END AS customer_status
FROM customers c
RIGHT JOIN orders o 
    ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL
    AND o.order_date >= '2025-01-01'
ORDER BY o.order_date DESC;

-- Basis FULL OUTER JOIN syntax
SELECT 
    table1.column1, 
    table1.column2,
    table2.column1, 
    table2.column2
FROM table1
FULL OUTER JOIN table2 
    ON table1.matching_column = table2.matching_column;

-- Vind alle data inconsistencies tussen customers en orders
SELECT 
    COALESCE(c.customer_id, o.customer_id) AS customer_id,
    CASE 
        WHEN c.customer_id IS NOT NULL AND o.customer_id IS NOT NULL THEN 'Match in Both'
        WHEN c.customer_id IS NOT NULL AND o.customer_id IS NULL THEN 'Only in Customers'
        WHEN c.customer_id IS NULL AND o.customer_id IS NOT NULL THEN 'Only in Orders'
    END AS data_status,
    c.first_name,
    c.last_name,
    c.email,
    COUNT(o.order_id) AS order_count,
    SUM(o.total_amount) AS total_spent,
    MAX(o.order_date) AS last_order_date
FROM customers c
FULL OUTER JOIN orders o 
    ON c.customer_id = o.customer_id
GROUP BY 
    COALESCE(c.customer_id, o.customer_id),
    c.first_name,
    c.last_name,
    c.email
ORDER BY 
    CASE 
        WHEN c.customer_id IS NOT NULL AND o.customer_id IS NOT NULL THEN 1
        WHEN c.customer_id IS NOT NULL AND o.customer_id IS NULL THEN 2
        ELSE 3
    END,
    order_count DESC;

-- Basis CROSS JOIN syntax
SELECT 
    table1.column1, 
    table1.column2,
    table2.column1, 
    table2.column2
FROM table1
CROSS JOIN table2;

-- Genereer een prijsmatrix voor alle product-korting combinaties
CREATE TABLE discounts (
    discount_id INT PRIMARY KEY,
    discount_name VARCHAR(50),
    discount_percentage DECIMAL(5, 2)
);

-- Voeg voorbeeld discount data toe
INSERT INTO discounts VALUES (1, 'Summer Sale', 10.00);
INSERT INTO discounts VALUES (2, 'Black Friday', 20.00);
INSERT INTO discounts VALUES (3, 'Christmas Special', 15.00);
INSERT INTO discounts VALUES (4, 'New Year', 25.00);

-- Genereer prijsmatrix
SELECT 
    p.product_id,
    p.product_name,
    p.category,
    p.price AS original_price,
    d.discount_name,
    d.discount_percentage,
    -- Bereken verkoopprijs na korting
    ROUND(p.price * (1 - d.discount_percentage/100), 2) AS discounted_price,
    -- Bereken kortingsbedrag
    ROUND(p.price * (d.discount_percentage/100), 2) AS discount_amount
FROM products p
CROSS JOIN discounts d
WHERE p.category = 'Electronics'  -- Filter voor specifieke categorie
ORDER BY 
    p.product_name, 
    d.discount_percentage DESC;

-- Basis SELF JOIN syntax
SELECT 
    a.column1, 
    a.column2,
    b.column1, 
    b.column2
FROM table_name a
JOIN table_name b 
    ON a.matching_column = b.matching_column;

-- Maak employees tabel met manager relaties
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    position VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    manager_id INT,
    hire_date DATE,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

-- Vind alle werknemers en hun managers
SELECT 
    e.employee_id AS employee_id,
    CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
    e.position AS employee_position,
    e.department AS department,
    e.salary AS employee_salary,
    m.employee_id AS manager_id,
    CONCAT(m.first_name, ' ', m.last_name) AS manager_name,
    m.position AS manager_position,
    -- Bereken salaris verschil met manager
    e.salary - m.salary AS salary_difference_from_manager
FROM employees e
LEFT JOIN employees m 
    ON e.manager_id = m.employee_id
ORDER BY 
    e.department,
    e.position,
    e.salary DESC;

-- Vind mogelijk duplicate customer records
SELECT 
    c1.customer_id AS customer1_id,
    c1.first_name AS customer1_first_name,
    c1.last_name AS customer1_last_name,
    c1.email AS customer1_email,
    c2.customer_id AS customer2_id,
    c2.first_name AS customer2_first_name,
    c2.last_name AS customer2_last_name,
    c2.email AS customer2_email,
    -- Bereken string similarity scores
    CASE 
        WHEN c1.email = c2.email THEN 'Exact Email Match'
        WHEN LOWER(c1.first_name) = LOWER(c2.first_name) 
             AND LOWER(c1.last_name) = LOWER(c2.last_name) 
             THEN 'Exact Name Match'
        WHEN SOUNDEX(c1.first_name) = SOUNDEX(c2.first_name) 
             AND SOUNDEX(c1.last_name) = SOUNDEX(c2.last_name) 
             THEN 'Similar Name (Soundex)'
        ELSE 'Potential Duplicate'
    END AS match_type,
    -- Toon match confidence
    CASE 
        WHEN c1.email = c2.email THEN 100
        WHEN LOWER(c1.first_name) = LOWER(c2.first_name) 
             AND LOWER(c1.last_name) = LOWER(c2.last_name) 
             THEN 90
        WHEN SOUNDEX(c1.first_name) = SOUNDEX(c2.first_name) 
             AND SOUNDEX(c1.last_name) = SOUNDEX(c2.last_name) 
             THEN 70
        ELSE 50
    END AS match_confidence
FROM customers c1
INNER JOIN customers c2 
    ON c1.customer_id < c2.customer_id  -- Voorkom dubbele paren en self matches
WHERE (
    c1.email = c2.email  -- Exact email match
    OR (
        LOWER(c1.first_name) = LOWER(c2.first_name) 
        AND LOWER(c1.last_name) = LOWER(c2.last_name)
    )  -- Exact name match
    OR (
        SOUNDEX(c1.first_name) = SOUNDEX(c2.first_name) 
        AND SOUNDEX(c1.last_name) = SOUNDEX(c2.last_name)
    )  -- Similar names
)
ORDER BY match_confidence DESC, c1.customer_id;

-- Complete sales analysis met alle gerelateerde data
SELECT 
    -- Klant informatie
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    c.email,
    c.registration_date,
    
    -- Order informatie
    o.order_id,
    o.order_date,
    o.total_amount AS order_total,
    o.status AS order_status,
    
    -- Product informatie
    p.product_id,
    p.product_name,
    p.category,
    
    -- Order item details
    oi.quantity,
    oi.unit_price,
    -- Bereken regel totaal
    oi.quantity * oi.unit_price AS line_total,
    
    -- Calculated fields
    CASE 
        WHEN o.status = 'Delivered' THEN 'Completed'
        WHEN o.status IN ('Pending', 'Processing') THEN 'In Progress'
        WHEN o.status = 'Cancelled' THEN 'Cancelled'
        ELSE 'Other'
    END AS order_category,
    
    -- Customer segmentation based on spending
    CASE 
        WHEN SUM(o.total_amount) OVER (PARTITION BY c.customer_id) > 1000 THEN 'VIP Customer'
        WHEN SUM(o.total_amount) OVER (PARTITION BY c.customer_id) > 500 THEN 'Regular Customer'
        ELSE 'New Customer'
    END AS customer_segment
    
FROM customers c
INNER JOIN orders o 
    ON c.customer_id = o.customer_id
INNER JOIN order_items oi 
    ON o.order_id = oi.order_id
INNER JOIN products p 
    ON oi.product_id = p.product_id
    
WHERE o.order_date >= '2025-01-01'
    AND o.status != 'Cancelled'
    
ORDER BY 
    o.order_date DESC,
    c.last_name,
    c.first_name;

-- Geavanceerde sales rapport met meerdere joins en aggregaties
WITH CustomerSales AS (
    SELECT 
        c.customer_id,
        CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
        c.registration_date,
        COUNT(DISTINCT o.order_id) AS total_orders,
        SUM(o.total_amount) AS total_spent,
        MAX(o.order_date) AS last_order_date,
        AVG(o.total_amount) AS avg_order_value,
        DATEDIFF(DAY, MAX(o.order_date), GETDATE()) AS days_since_last_order
    FROM customers c
    LEFT JOIN orders o 
        ON c.customer_id = o.customer_id
    GROUP BY 
        c.customer_id, 
        c.first_name, 
        c.last_name, 
        c.registration_date
),
ProductPerformance AS (
    SELECT 
        p.product_id,
        p.product_name,
        p.category,
        p.price,
        SUM(oi.quantity) AS total_units_sold,
        SUM(oi.quantity * oi.unit_price) AS total_revenue,
        COUNT(DISTINCT o.order_id) AS times_ordered,
        COUNT(DISTINCT o.customer_id) AS unique_customers
    FROM products p
    LEFT JOIN order_items oi 
        ON p.product_id = oi.product_id
    LEFT JOIN orders o 
        ON oi.order_id = o.order_id
    GROUP BY 
        p.product_id, 
        p.product_name, 
        p.category, 
        p.price
)
SELECT 
    cs.customer_name,
    cs.total_orders,
    cs.total_spent,
    cs.avg_order_value,
    cs.days_since_last_order,
    -- Customer status based on activity
    CASE 
        WHEN cs.days_since_last_order < 30 THEN 'Active'
        WHEN cs.days_since_last_order < 90 THEN 'At Risk'
        ELSE 'Inactive'
    END AS customer_status,
    
    -- Top product gekocht door deze klant
    (
        SELECT TOP 1 pp.product_name
        FROM orders o2
        INNER JOIN order_items oi2 
            ON o2.order_id = oi2.order_id
        INNER JOIN products pp 
            ON oi2.product_id = pp.product_id
        WHERE o2.customer_id = cs.customer_id
        GROUP BY pp.product_name
        ORDER BY SUM(oi2.quantity) DESC
    ) AS favorite_product,
    
    -- Recente order details
    (
        SELECT TOP 1 CONCAT(
            'Order #', 
            CAST(o3.order_id AS VARCHAR), 
            ' - ', 
            FORMAT(o3.order_date, 'dd/MM/yyyy'), 
            ' - €', 
            CAST(o3.total_amount AS VARCHAR(10))
        )
        FROM orders o3
        WHERE o3.customer_id = cs.customer_id
        ORDER BY o3.order_date DESC
    ) AS latest_order
    
FROM CustomerSales cs
WHERE cs.total_orders > 0
ORDER BY cs.total_spent DESC;

-- Creëer indexes voor veelgebruikte join columns
-- 1. Primary key indexes (meestal automatisch gecreëerd)
CREATE INDEX idx_customers_id ON customers(customer_id);
CREATE INDEX idx_orders_id ON orders(order_id);
CREATE INDEX idx_products_id ON products(product_id);

-- 2. Foreign key indexes (cruciaal voor join performance)
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- 3. Composite indexes voor vaak samen gebruikte columns
CREATE INDEX idx_orders_date_status ON orders(order_date, status);
CREATE INDEX idx_products_category_price ON products(category, price);
CREATE INDEX idx_customers_name_email ON customers(last_name, first_name, email);

-- 4. Covering indexes voor vaak uitgevoerde queries
CREATE INDEX idx_customer_orders_summary ON orders(customer_id, order_date, total_amount, status)
INCLUDE (order_id);  -- SQL Server syntax

-- Voor PostgreSQL/Mysql zou het zijn:
-- CREATE INDEX idx_customer_orders_summary ON orders(customer_id, order_date, total_amount, status);

-- 5. Monitor index usage
-- SQL Server
SELECT 
    OBJECT_NAME(s.object_id) AS table_name,
    i.name AS index_name,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates,
    s.last_user_seek,
    s.last_user_scan
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i 
    ON s.object_id = i.object_id 
    AND s.index_id = i.index_id
WHERE OBJECT_NAME(s.object_id) = 'orders'
ORDER BY s.user_seeks + s.user_scans DESC;

-- ❌ SLECHTE PRACTICE: Gebruik van functions in join conditions
SELECT c.*, o.*
FROM customers c
INNER JOIN orders o 
    ON UPPER(c.email) = UPPER(o.customer_email)  -- Function op join column
WHERE YEAR(o.order_date) = 2025;  -- Function in WHERE clause

-- ✅ GOEDE PRACTICE: Vermijd functions, gebruik indexed columns
SELECT c.*, o.*
FROM customers c
INNER JOIN orders o 
    ON c.email = o.customer_email  -- Direct column match
WHERE o.order_date >= '2025-01-01' 
    AND o.order_date < '2026-01-01';  -- Range query op indexed column

-- ❌ SLECHTE PRACTICE: Gebruik van SELECT *
SELECT *
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id;

-- ✅ GOEDE PRACTICE: SELECT alleen nodig columns
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    o.order_date,
    p.product_name,
    oi.quantity,
    oi.unit_price
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2025-01-01';

-- Complete sales dashboard query voor management rapportage
WITH MonthlySales AS (
    SELECT 
        -- Maand aggregatie
        FORMAT(o.order_date, 'yyyy-MM') AS sales_month,
        DATEPART(YEAR, o.order_date) AS sales_year,
        DATEPART(MONTH, o.order_date) AS sales_month_num,
        
        -- Sales metrics
        COUNT(DISTINCT o.order_id) AS total_orders,
        COUNT(DISTINCT o.customer_id) AS unique_customers,
        SUM(o.total_amount) AS total_revenue,
        AVG(o.total_amount) AS avg_order_value,
        
        -- Product metrics
        COUNT(DISTINCT oi.product_id) AS unique_products_sold,
        SUM(oi.quantity) AS total_units_sold,
        
        -- Customer metrics
        COUNT(DISTINCT CASE WHEN c.registration_date >= DATEADD(MONTH, -1, o.order_date) 
            THEN c.customer_id END) AS new_customers
        
    FROM orders o
    INNER JOIN order_items oi 
        ON o.order_id = oi.order_id
    LEFT JOIN customers c 
        ON o.customer_id = c.customer_id
    WHERE o.status = 'Delivered'
        AND o.order_date >= DATEADD(YEAR, -1, GETDATE())
    GROUP BY 
        FORMAT(o.order_date, 'yyyy-MM'),
        DATEPART(YEAR, o.order_date),
        DATEPART(MONTH, o.order_date)
),
CustomerLifetimeValue AS (
    SELECT 
        c.customer_id,
        CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
        c.registration_date,
        COUNT(DISTINCT o.order_id) AS lifetime_orders,
        SUM(o.total_amount) AS lifetime_value,
        DATEDIFF(DAY, c.registration_date, GETDATE()) AS customer_age_days,
        -- Bereken Customer Lifetime Value per dag
        CASE 
            WHEN DATEDIFF(DAY, c.registration_date, GETDATE()) > 0 
            THEN SUM(o.total_amount) / DATEDIFF(DAY, c.registration_date, GETDATE())
            ELSE 0
        END AS clv_per_day
    FROM customers c
    LEFT JOIN orders o 
        ON c.customer_id = o.customer_id
    WHERE o.status = 'Delivered' OR o.status IS NULL
    GROUP BY 
        c.customer_id, 
        c.first_name, 
        c.last_name, 
        c.registration_date
),
TopProducts AS (
    SELECT 
        p.category,
        p.product_name,
        SUM(oi.quantity) AS units_sold,
        SUM(oi.quantity * oi.unit_price) AS revenue,
        RANK() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity) DESC) AS rank_in_category
    FROM products p
    INNER JOIN order_items oi 
        ON p.product_id = oi.product_id
    INNER JOIN orders o 
        ON oi.order_id = o.order_id
    WHERE o.order_date >= DATEADD(MONTH, -3, GETDATE())
        AND o.status = 'Delivered'
    GROUP BY p.category, p.product_name
)
SELECT 
    -- Monthly sales summary
    ms.sales_month,
    ms.total_orders,
    ms.unique_customers,
    ms.total_revenue,
    ms.avg_order_value,
    ms.total_units_sold,
    ms.new_customers,
    
    -- Month-over-month growth
    LAG(ms.total_revenue) OVER (ORDER BY ms.sales_month) AS previous_month_revenue,
    CASE 
        WHEN LAG(ms.total_revenue) OVER (ORDER BY ms.sales_month) > 0
        THEN ((ms.total_revenue - LAG(ms.total_revenue) OVER (ORDER BY ms.sales_month)) / 
               LAG(ms.total_revenue) OVER (ORDER BY ms.sales_month)) * 100
        ELSE NULL
    END AS revenue_growth_percentage,
    
    -- Top 3 customers by CLV
    (
        SELECT STRING_AGG(clv.customer_name, ', ') WITHIN GROUP (ORDER BY clv.lifetime_value DESC)
        FROM (
            SELECT TOP 3 customer_name, lifetime_value
            FROM CustomerLifetimeValue
            WHERE lifetime_value > 0
            ORDER BY lifetime_value DESC
        ) clv
    ) AS top_3_customers,
    
    -- Top product per category
    (
        SELECT STRING_AGG(CONCAT(tp.category, ': ', tp.product_name), ' | ')
        FROM TopProducts tp
        WHERE tp.rank_in_category = 1
    ) AS top_products_by_category
    
FROM MonthlySales ms
ORDER BY ms.sales_month DESC;