-- 1. Top customers with data masking
CREATE VIEW top_customers_masked AS
SELECT 
    customer_id,
    CONCAT(LEFT(customer_name, 2), REPEAT('*', LENGTH(customer_name) - 4), 
           RIGHT(customer_name, 2)) AS customer_name,
    CONCAT(LEFT(email, 3), '****@****.***') AS email,
    total_orders,
    total_revenue,
    CASE 
        WHEN total_revenue > 10000 THEN 'Platinum'
        WHEN total_revenue > 5000 THEN 'Gold'
        WHEN total_revenue > 1000 THEN 'Silver'
        ELSE 'Bronze'
    END AS tier
FROM (
    SELECT 
        c.customer_id,
        c.customer_name,
        c.email,
        COUNT(o.order_id) AS total_orders,
        SUM(o.amount) AS total_revenue
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    WHERE o.status = 'completed'
    GROUP BY c.customer_id
    ORDER BY total_revenue DESC
    LIMIT 100
) AS customer_summary;

-- 2. Updatable product management view
CREATE VIEW product_management AS
SELECT 
    product_id,
    sku,
    product_name,
    category,
    price,
    is_active,
    last_updated
FROM products
WHERE is_active = TRUE
    AND price > 0
WITH CHECK OPTION;

-- Test updates
UPDATE product_management 
SET price = 99.99 
WHERE product_id = 1;

-- 3. Role-based views
-- Admin view
CREATE VIEW admin_dashboard AS
SELECT 
    DATE(order_date) AS date,
    COUNT(*) AS orders,
    SUM(amount) AS revenue,
    AVG(amount) AS avg_order,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
GROUP BY DATE(order_date);

-- Sales view
CREATE VIEW sales_dashboard AS
SELECT 
    product_name,
    category,
    SUM(quantity) AS units_sold,
    SUM(quantity * unit_price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_id;

-- Customer service view
CREATE VIEW customer_service_view AS
SELECT 
    c.customer_id,
    c.customer_name,
    c.email,
    COUNT(o.order_id) AS total_orders,
    MAX(o.order_date) AS last_order,
    COUNT(r.review_id) AS reviews_written
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN reviews r ON c.customer_id = r.user_id
GROUP BY c.customer_id;

-- 4. Complex reporting view
CREATE VIEW sales_report AS
SELECT 
    YEAR(o.order_date) AS year,
    QUARTER(o.order_date) AS quarter,
    MONTH(o.order_date) AS month,
    p.category,
    COUNT(DISTINCT o.order_id) AS order_count,
    COUNT(DISTINCT o.customer_id) AS unique_customers,
    SUM(oi.quantity) AS units_sold,
    SUM(oi.quantity * oi.unit_price) AS gross_revenue,
    SUM(oi.quantity * (oi.unit_price - p.cost)) AS gross_profit,
    AVG(oi.unit_price) AS avg_selling_price
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'completed'
GROUP BY 
    YEAR(o.order_date),
    QUARTER(o.order_date),
    MONTH(o.order_date),
    p.category;

-- 5. Materialized view simulation
-- Create table to store view results
CREATE TABLE mv_product_performance (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(200),
    total_quantity_sold INT,
    total_revenue DECIMAL(10,2),
    avg_rating DECIMAL(3,2),
    review_count INT,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Procedure to refresh
DELIMITER $$
CREATE PROCEDURE refresh_product_performance()
BEGIN
    TRUNCATE TABLE mv_product_performance;
    
    INSERT INTO mv_product_performance
    SELECT 
        p.product_id,
        p.product_name,
        COALESCE(SUM(oi.quantity), 0),
        COALESCE(SUM(oi.quantity * oi.unit_price), 0),
        COALESCE(AVG(r.rating), 0),
        COUNT(DISTINCT r.review_id),
        NOW()
    FROM products p
    LEFT JOIN order_items oi ON p.product_id = oi.product_id
    LEFT JOIN reviews r ON p.product_id = r.product_id
    GROUP BY p.product_id;
END$$
DELIMITER ;

-- 6. Nested views for hierarchy
CREATE VIEW category_base AS
SELECT 
    category_id,
    category_name,
    parent_category_id
FROM categories;

CREATE VIEW category_with_products AS
SELECT 
    c.*,
    COUNT(p.product_id) AS product_count
FROM category_base c
LEFT JOIN products p ON c.category_id = p.category_id
GROUP BY c.category_id;

CREATE VIEW category_hierarchy AS
SELECT 
    c1.category_name AS main_category,
    c2.category_name AS sub_category,
    c2.product_count
FROM category_with_products c1
JOIN category_with_products c2 ON c1.category_id = c2.parent_category_id;

-- 7. Security view
CREATE VIEW employee_safe_view AS
SELECT 
    employee_id,
    first_name,
    last_name,
    department,
    hire_date,
    CASE 
        WHEN USER() LIKE '%hr%' THEN salary
        ELSE 'RESTRICTED'
    END AS salary_info,
    CASE 
        WHEN USER() LIKE '%hr%' THEN ssn
        ELSE 'XXX-XX-' || RIGHT(ssn, 4)
    END AS ssn_masked
FROM employees;

-- 8. Performance-optimized dashboard view
CREATE VIEW dashboard_metrics AS
SELECT 
    (SELECT COUNT(*) FROM orders WHERE DATE(order_date) = CURDATE()) AS orders_today,
    (SELECT SUM(amount) FROM orders WHERE DATE(order_date) = CURDATE()) AS revenue_today,
    (SELECT COUNT(*) FROM customers WHERE DATE(registration_date) = CURDATE()) AS new_customers_today,
    (SELECT COUNT(*) FROM products WHERE is_active = TRUE) AS active_products,
    (SELECT AVG(rating) FROM reviews WHERE DATE(created_at) = CURDATE()) AS avg_rating_today;