-- ❌ SLECHTE PRACTICE: Slechte formatting
SELECT c.id,c.name,o.date,o.amount FROM customers c JOIN orders o ON c.id=o.customer_id WHERE o.date>'2024-01-01' AND o.status='COMPLETED' ORDER BY o.amount DESC;

-- ✅ GOEDE PRACTICE: Goede formatting
SELECT 
    c.id,
    c.name,
    o.date,
    o.amount
FROM customers c
INNER JOIN orders o 
    ON c.id = o.customer_id
WHERE o.date > '2024-01-01'
    AND o.status = 'COMPLETED'
ORDER BY o.amount DESC;

-- ✅ GOEDE PRACTICE: Gebruik CTEs voor leesbaarheid
WITH MonthlySales AS (
    SELECT 
        customer_id,
        DATE_TRUNC('month', order_date) AS sales_month,
        SUM(total_amount) AS monthly_total
    FROM orders
    WHERE status = 'delivered'
    GROUP BY customer_id, DATE_TRUNC('month', order_date)
),
TopCustomers AS (
    SELECT 
        customer_id,
        AVG(monthly_total) AS avg_monthly_spend
    FROM MonthlySales
    GROUP BY customer_id
    HAVING AVG(monthly_total) > 1000
)
SELECT 
    c.customer_id,
    c.customer_name,
    c.email,
    tc.avg_monthly_spend,
    ms.sales_month,
    ms.monthly_total
FROM customers c
INNER JOIN TopCustomers tc 
    ON c.customer_id = tc.customer_id
INNER JOIN MonthlySales ms 
    ON c.customer_id = ms.customer_id
ORDER BY tc.avg_monthly_spend DESC;

-- ❌ SLECHTE PRACTICE: SELECT * gebruiken
SELECT *
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date > '2024-01-01';

-- ✅ GOEDE PRACTICE: Alleen nodig columns selecteren
SELECT 
    c.id,
    c.first_name,
    c.last_name,
    c.email,
    o.order_id,
    o.order_date,
    o.total_amount
FROM customers c
INNER JOIN orders o 
    ON c.id = o.customer_id
WHERE o.order_date > '2024-01-01';

-- ❌ SLECHTE PRACTICE: Functions in WHERE clause
SELECT *
FROM orders
WHERE YEAR(order_date) = 2024
    AND MONTH(order_date) = 1;

-- ✅ GOEDE PRACTICE: Range queries gebruiken
SELECT *
FROM orders
WHERE order_date >= '2024-01-01'
    AND order_date < '2024-02-01';

-- ✅ GOEDE PRACTICE: Gebruik LIMIT voor development
-- Voor productie, gebruik pagination
SELECT 
    customer_id,
    customer_name,
    email,
    registration_date
FROM customers
WHERE registration_date >= '2024-01-01'
ORDER BY registration_date DESC
LIMIT 100;  -- Voor testing en preview

-- ✅ GOEDE PRACTICE: Pagination voor productie
SELECT 
    customer_id,
    customer_name,
    email,
    registration_date
FROM customers
WHERE registration_date >= '2024-01-01'
ORDER BY registration_date DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY;

-- ✅ GOEDE PRACTICE: Strategische index creatie

-- 1. Primary key index (automatisch in meeste DBs)
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    registration_date DATE
);

-- 2. Foreign key indexes (kritiek 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);

-- 3. Composite indexes voor vaak samen gebruikte columns
CREATE INDEX idx_orders_date_status ON orders(order_date, status);
CREATE INDEX idx_customers_name ON customers(last_name, first_name);

-- 4. Covering indexes (SQL Server syntax)
CREATE INDEX idx_orders_covering ON orders(customer_id, order_date)
INCLUDE (total_amount, status);

-- 5. Partial indexes voor filtered queries (PostgreSQL)
CREATE INDEX idx_active_orders ON orders(order_date)
WHERE status = 'active';

-- ✅ GOEDE PRACTICE: Index usage monitoring

-- Check index usage (PostgreSQL)
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Check unused indexes (SQL Server)
SELECT 
    OBJECT_NAME(s.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
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 s.database_id = DB_ID()
    AND s.user_seeks + s.user_scans + s.user_lookups = 0  -- No reads
    AND s.user_updates > 0  -- But has updates
ORDER BY table_name, index_name;

-- ❌ SLECHTE PRACTICE: Inefficiënte JOIN volgorde
SELECT *
FROM large_table l
JOIN very_large_table v ON l.id = v.large_id
JOIN small_table s ON v.small_id = s.id
WHERE s.category = 'A';

-- ✅ GOEDE PRACTICE: Filter vroeg, kleinere tabellen eerst
SELECT *
FROM small_table s
INNER JOIN very_large_table v 
    ON s.id = v.small_id
INNER JOIN large_table l 
    ON v.large_id = l.id
WHERE s.category = 'A';

-- ❌ SLECHTE PRACTICE: Onnodige OUTER JOIN
SELECT 
    c.customer_name,
    o.order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_date IS NOT NULL;

-- ✅ GOEDE PRACTICE: Gebruik INNER JOIN wanneer mogelijk
SELECT 
    c.customer_name,
    o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- ✅ GOEDE PRACTICE: EXISTS() voor existence checks
SELECT 
    c.customer_name,
    c.email
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
        AND o.order_date >= '2024-01-01'
);

-- ❌ SLECHTE PRACTICE: Correlated subquery in SELECT
SELECT 
    c.customer_name,
    (
        SELECT COUNT(*)
        FROM orders o
        WHERE o.customer_id = c.id
    ) AS order_count
FROM customers c;

-- ✅ GOEDE PRACTICE: Gebruik JOIN met GROUP BY
SELECT 
    c.customer_name,
    COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.customer_name;

-- Scenario: Find customers with recent orders

-- OPTIE 1: IN clause (goed voor kleine lists)
SELECT *
FROM customers
WHERE id IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= '2024-01-01'
);

-- OPTIE 2: EXISTS clause (beter voor grote datasets)
SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
        AND o.order_date >= '2024-01-01'
);

-- OPTIE 3: JOIN (goed wanneer je order data ook nodig hebt)
SELECT DISTINCT c.*
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE o.order_date >= '2024-01-01';

-- ❌ SLECHTE PRACTICE: Te grote data types
CREATE TABLE products (
    id BIGINT,  -- Overkill, verwacht maar 10k producten
    name VARCHAR(500),  -- Te groot, max naam lengte is 100
    price DECIMAL(20, 10),  -- Overkill voor product prijzen
    description TEXT,  -- Niet nodig voor korte beschrijvingen
    category VARCHAR(255)
);

-- ✅ GOEDE PRACTICE: Juiste data type grootte
CREATE TABLE products (
    id INT PRIMARY KEY,  -- Genoeg voor 2+ miljard producten
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,  -- Tot €99,999,999.99
    description VARCHAR(500),  -- Genoeg voor meeste producten
    category VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_category (category),
    INDEX idx_price (price)
);

-- ✅ GOEDE PRACTICE: Consistent NULL handling

-- 1. Gebruik COALESCE voor default values
SELECT 
    customer_name,
    COALESCE(email, 'No email provided') AS email,
    COALESCE(phone, 'N/A') AS phone
FROM customers;

-- 2. Vermijd NULL in aggregations
SELECT 
    AVG(COALESCE(price, 0)) AS avg_price,  -- Beter voor berekeningen
    SUM(COALESCE(quantity, 0)) AS total_quantity
FROM order_items;

-- 3. Gebruik NULLIF voor division protection
SELECT 
    total_sales / NULLIF(total_customers, 0) AS avg_per_customer
FROM sales_summary;

-- 4. Index considerations voor NULL columns
CREATE INDEX idx_email_not_null ON customers(email)
WHERE email IS NOT NULL;  -- Filtered index (PostgreSQL/SQL Server)

-- ❌ SLECHTE PRACTICE: Dynamic SQL zonder parameterization
-- (NEVER DO THIS IN PRODUCTION!)
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM users WHERE username = ''' + @inputUsername + '''';
EXEC sp_executesql @sql;

-- ✅ GOEDE PRACTICE: Parameterized queries
-- SQL Server / .NET
DECLARE @username NVARCHAR(50) = 'john.doe';
SELECT * FROM users WHERE username = @username;

-- Python/Psycopg2
-- cursor.execute("SELECT * FROM users WHERE username = %s", (username,))

-- ✅ GOEDE PRACTICE: Stored procedures met parameters
CREATE PROCEDURE GetUserByUsername
    @Username NVARCHAR(50)
AS
BEGIN
    SELECT 
        user_id,
        username,
        email,
        created_at
    FROM users
    WHERE username = @Username;
END;

-- ✅ GOEDE PRACTICE: Rol-based access control

-- 1. Create application roles
CREATE ROLE app_read_only;
CREATE ROLE app_read_write;
CREATE ROLE app_admin;

-- 2. Grant specific permissions
-- Read-only role
GRANT SELECT ON customers TO app_read_only;
GRANT SELECT ON orders TO app_read_only;
GRANT SELECT ON products TO app_read_only;

-- Read-write role (add INSERT, UPDATE, DELETE)
GRANT SELECT, INSERT, UPDATE ON orders TO app_read_write;
GRANT SELECT, INSERT ON order_items TO app_read_write;

-- 3. Grant execute permissions on specific procedures
GRANT EXECUTE ON GetCustomerOrders TO app_read_only;
GRANT EXECUTE ON CreateNewOrder TO app_read_write;

-- 4. Use views for data masking
CREATE VIEW vw_customer_public AS
SELECT 
    customer_id,
    first_name,
    last_name,
    -- Mask sensitive data
    CONCAT(LEFT(email, 3), '***@***', RIGHT(email, 4)) AS masked_email,
    registration_date
FROM customers;

-- ✅ GOEDE PRACTICE: Migration files organisatie
-- File: 001_create_customers_table.sql
CREATE TABLE customers (
    customer_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- File: 002_create_orders_table.sql
CREATE TABLE orders (
    order_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_customer 
        FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
);

-- File: 003_add_indexes.sql
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
CREATE INDEX idx_orders_status ON orders(status);

-- File: 004_add_order_items_table.sql
CREATE TABLE order_items (
    order_item_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    CONSTRAINT fk_order 
        FOREIGN KEY (order_id) 
        REFERENCES orders(order_id) ON DELETE CASCADE
);

-- ✅ GOEDE PRACTICE: Gedocumenteerde stored procedure

/*
Procedure: GetCustomerOrderSummary
Description: Returns order summary for a specific customer
Parameters: 
    @CustomerID - The ID of the customer
    @StartDate - Start date for filtering orders (optional)
    @EndDate - End date for filtering orders (optional)
Returns: Order count, total amount, and average order value
Author: Data Team
Created: 2024-01-15
Modified: 2024-06-20 - Added date filtering parameters
Example Usage:
    EXEC GetCustomerOrderSummary @CustomerID = 123, @StartDate = '2024-01-01'
*/
CREATE PROCEDURE GetCustomerOrderSummary
    @CustomerID INT,
    @StartDate DATE = NULL,
    @EndDate DATE = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        c.customer_id,
        CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
        COUNT(o.order_id) AS order_count,
        SUM(o.total_amount) AS total_spent,
        AVG(o.total_amount) AS avg_order_value,
        MIN(o.order_date) AS first_order_date,
        MAX(o.order_date) AS last_order_date
    FROM customers c
    LEFT JOIN orders o 
        ON c.customer_id = o.customer_id
        AND o.status = 'completed'
        AND (@StartDate IS NULL OR o.order_date >= @StartDate)
        AND (@EndDate IS NULL OR o.order_date <= @EndDate)
    WHERE c.customer_id = @CustomerID
    GROUP BY 
        c.customer_id, 
        c.first_name, 
        c.last_name;
END;

-- ✅ GOEDE PRACTICE: Query execution plan analysis

-- PostgreSQL: EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS) 
SELECT 
    c.customer_name,
    SUM(o.total_amount) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY c.customer_id, c.customer_name
HAVING SUM(o.total_amount) > 1000;

-- SQL Server: Include actual execution plan
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT 
    c.customer_name,
    SUM(o.total_amount) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY c.customer_id, c.customer_name
HAVING SUM(o.total_amount) > 1000;

-- MySQL: EXPLAIN FORMAT=JSON
EXPLAIN FORMAT=JSON
SELECT 
    c.customer_name,
    SUM(o.total_amount) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY c.customer_id, c.customer_name
HAVING SUM(o.total_amount) > 1000;

-- ✅ GOEDE PRACTICE: Regular performance monitoring

-- 1. Find slow queries (PostgreSQL)
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- 2. 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,
    CASE 
        WHEN s.user_seeks + s.user_scans + s.user_lookups = 0 
        THEN 'UNUSED'
        WHEN s.user_updates > (s.user_seeks + s.user_scans + s.user_lookups) 
        THEN 'HIGH MAINTENANCE'
        ELSE 'HEALTHY'
    END AS index_status
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 s.database_id = DB_ID()
ORDER BY s.user_updates DESC;

-- 3. Table size and growth monitoring
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename) - 
                   pg_relation_size(schemaname || '.' || tablename)) AS index_size,
    n_live_tup AS row_count
FROM pg_tables t
JOIN pg_stat_user_tables s ON t.schemaname = s.schemaname AND t.tablename = s.relname
WHERE t.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;