-- Customer Segmentation Analysis for Trendy Fashion
-- This script implements RFM (Recency, Frequency, Monetary) analysis
-- to create actionable customer segments for marketing campaigns

-- =====================================================================
-- 1. RFM METRICS CALCULATION
-- =====================================================================

-- Create a comprehensive RFM analysis table
CREATE OR REPLACE VIEW customer_rfm_analysis AS
WITH rfm_base AS (
    SELECT 
        c.customer_id,
        c.first_name,
        c.last_name,
        c.email,
        c.registration_date,
        c.state,
        c.marketing_consent,
        
        -- Recency: Days since last order (lower is better)
        COALESCE(
            EXTRACT(DAYS FROM (CURRENT_DATE - MAX(o.order_date::date))), 
            999  -- High value for customers with no orders
        ) as days_since_last_order,
        
        -- Frequency: Number of completed orders (higher is better)
        COUNT(CASE WHEN o.status = 'completed' THEN 1 END) as total_orders,
        
        -- Monetary: Total amount spent on completed orders (higher is better)
        COALESCE(SUM(CASE WHEN o.status = 'completed' THEN o.total_amount END), 0) as total_spent,
        
        -- Additional useful metrics
        MIN(o.order_date) as first_order_date,
        MAX(o.order_date) as last_order_date,
        ROUND(AVG(CASE WHEN o.status = 'completed' THEN o.total_amount END), 2) as avg_order_value,
        
        -- Calculate customer age at registration
        EXTRACT(YEAR FROM AGE(c.registration_date::date, c.birth_date::date)) as age_at_registration,
        
        -- Days as customer
        EXTRACT(DAYS FROM (CURRENT_DATE - c.registration_date::date)) as days_as_customer
        
    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.email, c.registration_date, 
             c.state, c.marketing_consent, c.birth_date
),

-- Calculate quintiles for RFM scoring
rfm_quintiles AS (
    SELECT 
        *,
        -- Recency quintiles (1 = worst/oldest, 5 = best/most recent)
        CASE 
            WHEN days_since_last_order <= 30 THEN 5
            WHEN days_since_last_order <= 60 THEN 4
            WHEN days_since_last_order <= 90 THEN 3
            WHEN days_since_last_order <= 180 THEN 2
            ELSE 1
        END as recency_score,
        
        -- Frequency quintiles (1 = lowest frequency, 5 = highest frequency)
        CASE 
            WHEN total_orders >= 10 THEN 5
            WHEN total_orders >= 6 THEN 4
            WHEN total_orders >= 4 THEN 3
            WHEN total_orders >= 2 THEN 2
            WHEN total_orders >= 1 THEN 1
            ELSE 0
        END as frequency_score,
        
        -- Monetary quintiles (1 = lowest spend, 5 = highest spend)
        CASE 
            WHEN total_spent >= 500 THEN 5
            WHEN total_spent >= 300 THEN 4
            WHEN total_spent >= 150 THEN 3
            WHEN total_spent >= 50 THEN 2
            WHEN total_spent > 0 THEN 1
            ELSE 0
        END as monetary_score
    FROM rfm_base
)

SELECT 
    *,
    -- Combined RFM score
    CAST(recency_score AS VARCHAR) || CAST(frequency_score AS VARCHAR) || CAST(monetary_score AS VARCHAR) as rfm_score,
    
    -- Overall RFM score (simple average)
    ROUND((recency_score + frequency_score + monetary_score) / 3.0, 2) as rfm_avg_score
FROM rfm_quintiles;

-- =====================================================================
-- 2. CUSTOMER SEGMENT ASSIGNMENT
-- =====================================================================

-- Create customer segments based on RFM scores
CREATE OR REPLACE VIEW customer_segments AS
WITH rfm_data AS (
    SELECT * FROM customer_rfm_analysis
)
SELECT 
    *,
    -- Assign segment based on RFM patterns
    CASE 
        -- Champions: High R, F, M
        WHEN recency_score >= 4 AND frequency_score >= 4 AND monetary_score >= 4 
        THEN 'Champions'
        
        -- Loyal Customers: High F and M, medium to high R
        WHEN recency_score >= 3 AND frequency_score >= 4 AND monetary_score >= 3
        THEN 'Loyal Customers'
        
        -- Potential Loyalists: Recent customers with good frequency or monetary
        WHEN recency_score >= 4 AND (frequency_score >= 3 OR monetary_score >= 3)
        THEN 'Potential Loyalists'
        
        -- Big Spenders: High monetary but lower frequency
        WHEN monetary_score >= 4 AND frequency_score <= 3
        THEN 'Big Spenders'
        
        -- At Risk: Were good customers but haven't purchased recently
        WHEN recency_score <= 2 AND frequency_score >= 3 AND monetary_score >= 3
        THEN 'At Risk'
        
        -- Cannot Lose Them: High monetary and frequency but low recency
        WHEN recency_score <= 2 AND frequency_score >= 4 AND monetary_score >= 4
        THEN 'Cannot Lose Them'
        
        -- New Customers: Recent registration, low frequency and monetary
        WHEN days_as_customer <= 90 AND total_orders <= 2
        THEN 'New Customers'
        
        -- Promising: New customers with good initial orders
        WHEN days_as_customer <= 180 AND recency_score >= 3 AND monetary_score >= 3
        THEN 'Promising'
        
        -- Need Attention: Medium on all metrics
        WHEN recency_score = 3 AND frequency_score = 3 AND monetary_score = 3
        THEN 'Need Attention'
        
        -- About to Sleep: Declining engagement
        WHEN recency_score = 2 AND frequency_score <= 3 AND monetary_score <= 3
        THEN 'About to Sleep'
        
        -- Hibernating: Low engagement across the board
        WHEN recency_score <= 2 AND frequency_score <= 2 AND monetary_score <= 2 AND total_orders > 0
        THEN 'Hibernating'
        
        -- Lost: No orders or very old
        WHEN total_orders = 0 OR (recency_score = 1 AND frequency_score <= 1)
        THEN 'Lost'
        
        ELSE 'Others'
    END as segment,
    
    -- Assign segment priority for marketing (1 = highest priority)
    CASE 
        WHEN recency_score >= 4 AND frequency_score >= 4 AND monetary_score >= 4 THEN 1  -- Champions
        WHEN recency_score <= 2 AND frequency_score >= 4 AND monetary_score >= 4 THEN 2  -- Cannot Lose Them
        WHEN recency_score <= 2 AND frequency_score >= 3 AND monetary_score >= 3 THEN 3  -- At Risk
        WHEN recency_score >= 3 AND frequency_score >= 4 AND monetary_score >= 3 THEN 4  -- Loyal Customers
        WHEN monetary_score >= 4 AND frequency_score <= 3 THEN 5  -- Big Spenders
        WHEN recency_score >= 4 AND (frequency_score >= 3 OR monetary_score >= 3) THEN 6  -- Potential Loyalists
        WHEN days_as_customer <= 180 AND recency_score >= 3 AND monetary_score >= 3 THEN 7  -- Promising
        WHEN days_as_customer <= 90 AND total_orders <= 2 THEN 8  -- New Customers
        ELSE 9  -- Others
    END as marketing_priority
    
FROM rfm_data;

-- =====================================================================
-- 3. SEGMENT ANALYSIS AND INSIGHTS
-- =====================================================================

-- Segment summary with key metrics
SELECT 
    segment,
    COUNT(*) as customer_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM customer_segments), 2) as percentage,
    ROUND(AVG(total_spent), 2) as avg_total_spent,
    ROUND(AVG(total_orders), 2) as avg_total_orders,
    ROUND(AVG(avg_order_value), 2) as avg_order_value,
    ROUND(AVG(days_since_last_order), 0) as avg_days_since_last_order,
    ROUND(AVG(age_at_registration), 1) as avg_customer_age,
    SUM(total_spent) as segment_total_revenue,
    ROUND(SUM(total_spent) * 100.0 / (
        SELECT SUM(total_spent) FROM customer_segments WHERE total_spent > 0
    ), 2) as revenue_percentage
FROM customer_segments
GROUP BY segment
ORDER BY customer_count DESC;

-- Top segments by revenue contribution
SELECT 
    segment,
    customer_count,
    segment_total_revenue,
    revenue_percentage,
    ROUND(segment_total_revenue / customer_count, 2) as revenue_per_customer
FROM (
    SELECT 
        segment,
        COUNT(*) as customer_count,
        ROUND(SUM(total_spent), 2) as segment_total_revenue,
        ROUND(SUM(total_spent) * 100.0 / (
            SELECT SUM(total_spent) FROM customer_segments WHERE total_spent > 0
        ), 2) as revenue_percentage
    FROM customer_segments
    GROUP BY segment
) segment_summary
ORDER BY segment_total_revenue DESC;

-- Geographic distribution by segment
SELECT 
    segment,
    state,
    COUNT(*) as customer_count,
    ROUND(AVG(total_spent), 2) as avg_spent
FROM customer_segments
WHERE segment IN ('Champions', 'Loyal Customers', 'At Risk', 'Big Spenders')
GROUP BY segment, state
HAVING COUNT(*) >= 5  -- Only show states with 5+ customers
ORDER BY segment, customer_count DESC;

-- =====================================================================
-- 4. MARKETING CAMPAIGN RECOMMENDATIONS
-- =====================================================================

-- Marketing-ready customer lists with recommendations
CREATE OR REPLACE VIEW marketing_campaign_data AS
SELECT 
    customer_id,
    first_name,
    last_name,
    email,
    state,
    segment,
    marketing_priority,
    total_spent,
    total_orders,
    days_since_last_order,
    avg_order_value,
    marketing_consent,
    
    -- Campaign recommendations
    CASE 
        WHEN segment = 'Champions' THEN 'VIP rewards program, early access to new collections'
        WHEN segment = 'Loyal Customers' THEN 'Loyalty program, personalized recommendations'
        WHEN segment = 'At Risk' THEN 'Win-back campaign, special discount offers'
        WHEN segment = 'Cannot Lose Them' THEN 'Urgent win-back, exclusive offers'
        WHEN segment = 'Big Spenders' THEN 'Premium product recommendations, personal shopping'
        WHEN segment = 'New Customers' THEN 'Welcome series, style guide, first purchase incentive'
        WHEN segment = 'Promising' THEN 'Nurture campaign, product recommendations'
        WHEN segment = 'Potential Loyalists' THEN 'Engagement campaign, loyalty program invitation'
        WHEN segment = 'About to Sleep' THEN 'Re-engagement campaign, interest survey'
        WHEN segment = 'Hibernating' THEN 'Reactivation campaign, significant discount'
        ELSE 'General newsletter, seasonal promotions'
    END as campaign_recommendation,
    
    -- Email frequency recommendation
    CASE 
        WHEN segment IN ('Champions', 'Loyal Customers') THEN 'Weekly'
        WHEN segment IN ('Big Spenders', 'Potential Loyalists', 'Promising') THEN 'Bi-weekly'
        WHEN segment IN ('At Risk', 'Cannot Lose Them') THEN 'Immediate + Weekly'
        WHEN segment = 'New Customers' THEN 'Welcome series (5 emails over 2 weeks)'
        ELSE 'Monthly'
    END as email_frequency,
    
    -- Discount recommendation
    CASE 
        WHEN segment = 'Champions' THEN '10% + Free shipping'
        WHEN segment = 'Loyal Customers' THEN '15% discount'
        WHEN segment = 'At Risk' THEN '20% discount'
        WHEN segment = 'Cannot Lose Them' THEN '25% discount + Free shipping'
        WHEN segment = 'Big Spenders' THEN 'Free shipping on all orders'
        WHEN segment = 'New Customers' THEN '20% first purchase + Free shipping'
        WHEN segment IN ('Promising', 'Potential Loyalists') THEN '15% discount'
        WHEN segment = 'Hibernating' THEN '30% win-back offer'
        ELSE '10% seasonal discount'
    END as discount_recommendation
    
FROM customer_segments
WHERE marketing_consent = true  -- Only include customers who consented to marketing
ORDER BY marketing_priority, total_spent DESC;

-- =====================================================================
-- 5. SEGMENT PERFORMANCE TRACKING
-- =====================================================================

-- Create a view for tracking segment performance over time
CREATE OR REPLACE VIEW segment_monthly_performance AS
WITH monthly_orders AS (
    SELECT 
        DATE_TRUNC('month', order_date::date) as order_month,
        customer_id,
        COUNT(*) as monthly_orders,
        SUM(CASE WHEN status = 'completed' THEN total_amount ELSE 0 END) as monthly_revenue
    FROM orders
    WHERE order_date >= '2022-01-01'
    GROUP BY DATE_TRUNC('month', order_date::date), customer_id
)
SELECT 
    mo.order_month,
    cs.segment,
    COUNT(DISTINCT cs.customer_id) as active_customers,
    SUM(mo.monthly_orders) as total_orders,
    ROUND(SUM(mo.monthly_revenue), 2) as total_revenue,
    ROUND(AVG(mo.monthly_revenue), 2) as avg_revenue_per_customer
FROM monthly_orders mo
JOIN customer_segments cs ON mo.customer_id = cs.customer_id
GROUP BY mo.order_month, cs.segment
ORDER BY mo.order_month, cs.segment;

-- =====================================================================
-- 6. EXPORTABLE CUSTOMER LISTS
-- =====================================================================

-- Champions - VIP customers
SELECT 
    'Champions Export' as export_type,
    customer_id,
    first_name,
    last_name,
    email,
    state,
    total_spent,
    total_orders,
    days_since_last_order,
    'VIP program invitation' as campaign_type
FROM customer_segments
WHERE segment = 'Champions' 
  AND marketing_consent = true
ORDER BY total_spent DESC;

-- At Risk customers - Win-back campaign
SELECT 
    'At Risk Export' as export_type,
    customer_id,
    first_name,
    last_name,
    email,
    state,
    total_spent,
    total_orders,
    days_since_last_order,
    'Win-back 20% discount' as campaign_type
FROM customer_segments
WHERE segment = 'At Risk' 
  AND marketing_consent = true
ORDER BY total_spent DESC;

-- New Customers - Welcome series
SELECT 
    'New Customers Export' as export_type,
    customer_id,
    first_name,
    last_name,
    email,
    state,
    total_spent,
    total_orders,
    days_since_last_order,
    'Welcome series + 20% off first purchase' as campaign_type
FROM customer_segments
WHERE segment = 'New Customers' 
  AND marketing_consent = true
ORDER BY registration_date DESC;

-- =====================================================================
-- 7. VALIDATION AND QUALITY CHECKS
-- =====================================================================

-- Segment assignment validation
SELECT 
    'Segment Validation' as check_type,
    COUNT(*) as total_customers,
    COUNT(CASE WHEN segment IS NOT NULL THEN 1 END) as customers_with_segments,
    ROUND(COUNT(CASE WHEN segment IS NOT NULL THEN 1 END) * 100.0 / COUNT(*), 2) as assignment_rate
FROM customer_segments;

-- RFM score distribution validation
SELECT 
    'RFM Score Distribution' as check_type,
    recency_score,
    frequency_score,
    monetary_score,
    COUNT(*) as customer_count
FROM customer_rfm_analysis
WHERE recency_score BETWEEN 1 AND 5
  AND frequency_score BETWEEN 0 AND 5
  AND monetary_score BETWEEN 0 AND 5
GROUP BY recency_score, frequency_score, monetary_score
ORDER BY customer_count DESC
LIMIT 20;

-- Marketing consent by segment
SELECT 
    segment,
    COUNT(*) as total_customers,
    COUNT(CASE WHEN marketing_consent = true THEN 1 END) as marketable_customers,
    ROUND(COUNT(CASE WHEN marketing_consent = true THEN 1 END) * 100.0 / COUNT(*), 2) as consent_rate
FROM customer_segments
GROUP BY segment
ORDER BY total_customers DESC;

PRINT 'Customer segmentation analysis complete!';
PRINT 'Generated views:';
PRINT '- customer_rfm_analysis: Detailed RFM metrics for each customer';
PRINT '- customer_segments: Customer segments with marketing recommendations';
PRINT '- marketing_campaign_data: Marketing-ready customer lists';
PRINT '- segment_monthly_performance: Performance tracking over time';
PRINT '';
PRINT 'Next steps:';
PRINT '1. Export customer lists for marketing campaigns';
PRINT '2. Set up automated segment refresh (daily/weekly)';
PRINT '3. Create dashboard visualizations';
PRINT '4. Track campaign performance by segment';