MySQL Views: Your Database's Secret Weapon for Clean, Smart Queries 🎯

-- The query that haunts your dreams
SELECT 
    u.id,
    u.name,
    u.email,
    p.company_name,
    COUNT(o.id) as total_orders,
    SUM(o.total_amount) as total_spent,
    CASE 
        WHEN SUM(o.total_amount) > 10000 THEN 'VIP'
        WHEN SUM(o.total_amount) > 5000 THEN 'Premium'
        ELSE 'Regular'
    END as customer_tier
FROM users u
LEFT JOIN profiles p ON u.id = p.user_id
LEFT JOIN orders o ON u.id = o.user_id 
    AND o.status = 'completed'
    AND o.created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY u.id, u.name, u.email, p.company_name;

-- Create the view once
CREATE VIEW customer_summary AS (
    -- Insert that monster query here
);

-- Use it anywhere, anytime
SELECT * FROM customer_summary WHERE customer_tier = 'VIP';

-- Basic view creation
CREATE VIEW active_users AS
SELECT 
    id,
    CONCAT(first_name, ' ', last_name) as full_name,
    email,
    created_at,
    last_login
FROM users 
WHERE status = 'active' 
    AND deleted_at IS NULL;

-- Now use it like any table
SELECT * FROM active_users WHERE last_login > DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Hide sensitive columns and add computed fields
CREATE VIEW public_user_info AS
SELECT 
    id,
    username,
    CONCAT(first_name, ' ', SUBSTRING(last_name, 1, 1), '.') as display_name,
    DATE_FORMAT(created_at, '%M %Y') as member_since,
    CASE 
        WHEN last_login > DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 'Recently Active'
        WHEN last_login > DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 'Active'
        ELSE 'Inactive'
    END as activity_status
FROM users
WHERE status != 'banned';

-- Product performance dashboard view
CREATE VIEW product_performance AS
SELECT 
    p.id,
    p.name,
    p.category,
    COUNT(DISTINCT o.user_id) as unique_customers,
    COUNT(oi.id) as total_sold,
    SUM(oi.quantity * oi.price) as total_revenue,
    AVG(r.rating) as avg_rating,
    COUNT(r.id) as review_count
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.id AND o.status = 'completed'
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name, p.category;

-- Simple updatable view
CREATE VIEW current_inventory AS
SELECT 
    id,
    product_name,
    quantity_in_stock,
    reorder_level,
    last_updated
FROM inventory 
WHERE active = 1;

-- You can actually update through this view!
UPDATE current_inventory 
SET quantity_in_stock = quantity_in_stock - 5 
WHERE id = 123;

CREATE VIEW premium_customers AS
SELECT * FROM customers 
WHERE customer_tier = 'premium'
WITH CHECK OPTION;

-- This will fail because it violates the view's WHERE condition
UPDATE premium_customers SET customer_tier = 'regular' WHERE id = 1;

CREATE VIEW category_hierarchy AS
WITH RECURSIVE cte AS (
    -- Base case: top-level categories
    SELECT id, name, parent_id, 0 as level, name as path
    FROM categories 
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive case: child categories
    SELECT c.id, c.name, c.parent_id, cte.level + 1,
           CONCAT(cte.path, ' > ', c.name) as path
    FROM categories c
    JOIN cte ON c.parent_id = cte.id
)
SELECT * FROM cte;

-- HR can see salary info
CREATE VIEW hr_employee_view AS
SELECT 
    id, name, department, position, salary, hire_date
FROM employees;

-- Managers can see team info but not salaries
CREATE VIEW manager_employee_view AS
SELECT 
    id, name, department, position, hire_date, performance_rating
FROM employees;

-- General staff can see basic directory info
CREATE VIEW directory_view AS
SELECT 
    name, department, position, email, phone
FROM employees 
WHERE status = 'active';

-- See all views in your database
SELECT 
    table_name as view_name,
    view_definition,
    definer,
    security_type
FROM information_schema.views 
WHERE table_schema = 'your_database';

-- Check if a view is updatable
SELECT 
    table_name,
    is_updatable
FROM information_schema.views 
WHERE table_schema = 'your_database';

-- Replace an existing view (safer than DROP + CREATE)
CREATE OR REPLACE VIEW customer_summary AS
SELECT 
    id,
    name,
    email,
    -- Added new computed column
    DATEDIFF(NOW(), created_at) as days_as_customer,
    total_orders,
    total_spent
FROM (
    -- Your complex subquery here
) as base_query;

-- Drop a view when you're done with it
DROP VIEW IF EXISTS old_unused_view;

-- This view might be slow if you have millions of records
CREATE VIEW slow_customer_analysis AS
SELECT 
    u.id,
    u.name,
    COUNT(DISTINCT o.id) as total_orders,
    COUNT(DISTINCT p.id) as products_bought,
    AVG(r.rating) as avg_rating_given,
    -- ... many more complex calculations
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
LEFT JOIN reviews r ON u.id = r.user_id
GROUP BY u.id, u.name;

-- Create a table to store materialized view data
CREATE TABLE mv_daily_sales (
    sale_date DATE PRIMARY KEY,
    total_orders INT,
    total_revenue DECIMAL(10,2),
    avg_order_value DECIMAL(8,2),
    unique_customers INT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Populate it with a stored procedure
DELIMITER //
CREATE PROCEDURE RefreshDailySales()
BEGIN
    DELETE FROM mv_daily_sales WHERE sale_date = CURDATE();

    INSERT INTO mv_daily_sales (sale_date, total_orders, total_revenue, avg_order_value, unique_customers)
    SELECT 
        DATE(created_at) as sale_date,
        COUNT(*) as total_orders,
        SUM(total_amount) as total_revenue,
        AVG(total_amount) as avg_order_value,
        COUNT(DISTINCT user_id) as unique_customers
    FROM orders 
    WHERE DATE(created_at) = CURDATE()
        AND status = 'completed'
    GROUP BY DATE(created_at);
END //
DELIMITER ;

-- Set up a scheduled event to refresh it
CREATE EVENT refresh_daily_sales
ON SCHEDULE EVERY 1 HOUR
DO CALL RefreshDailySales();

-- Good view: Explicit, documented, purposeful
CREATE VIEW monthly_revenue_summary AS
/* Business Logic: Calculate monthly revenue metrics for dashboard
   Updated: 2024-01-15
   Owner: Finance Team */
SELECT 
    YEAR(order_date) as revenue_year,
    MONTH(order_date) as revenue_month,
    MONTHNAME(order_date) as month_name,
    COUNT(*) as total_orders,
    SUM(total_amount) as total_revenue,
    AVG(total_amount) as avg_order_value
FROM orders 
WHERE status = 'completed' 
    AND order_date >= DATE_SUB(CURDATE(), INTERVAL 24 MONTH)
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY revenue_year DESC, revenue_month DESC;

-- Clean API response format
CREATE VIEW api_user_profile AS
SELECT 
    id,
    username,
    email,
    JSON_OBJECT(
        'firstName', first_name,
        'lastName', last_name,
        'avatar', avatar_url,
        'memberSince', DATE_FORMAT(created_at, '%Y-%m-%d')
    ) as profile_data,
    status
FROM users 
WHERE status = 'active' AND deleted_at IS NULL;

-- Executive dashboard view
CREATE VIEW executive_dashboard AS
SELECT 
    'Today' as period,
    COUNT(*) as new_orders,
    SUM(total_amount) as revenue,
    COUNT(DISTINCT user_id) as active_customers
FROM orders 
WHERE DATE(created_at) = CURDATE()

UNION ALL

SELECT 
    'This Week' as period,
    COUNT(*) as new_orders,
    SUM(total_amount) as revenue,
    COUNT(DISTINCT user_id) as active_customers
FROM orders 
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

-- GDPR-compliant customer data view
CREATE VIEW gdpr_customer_export AS
SELECT 
    id,
    email,
    created_at,
    last_login,
    CASE 
        WHEN gdpr_consent = 1 THEN 'Consented'
        ELSE 'No Consent'
    END as consent_status,
    -- Anonymized or aggregated data only
    JSON_OBJECT(
        'totalOrders', (SELECT COUNT(*) FROM orders WHERE user_id = users.id),
        'accountAge', DATEDIFF(NOW(), created_at)
    ) as summary_data
FROM users;

-- Fix: Update the definer
ALTER DEFINER = CURRENT_USER VIEW your_view_name AS
SELECT * FROM your_table;

-- Debug with EXPLAIN
EXPLAIN SELECT * FROM your_slow_view WHERE some_condition = 'value';

-- Check underlying table indexes
SHOW INDEX FROM underlying_table;

-- See view dependencies
SELECT 
    table_name,
    view_definition 
FROM information_schema.views 
WHERE view_definition LIKE '%your_table_name%';