dashboard.py

#!/usr/bin/env python3
"""
Simple Dashboard for Mini Data Warehouse
Creates HTML dashboard with key metrics and visualizations
"""

import psycopg2
import json
from datetime import datetime, timedelta
import logging
import base64
import io
import matplotlib
matplotlib.use('Agg')  # Use non-interactive backend
import matplotlib.pyplot as plt
import pandas as pd

# Setup logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

class DashboardGenerator:
    def __init__(self, connection_params=None):
        if connection_params is None:
            self.connection_params = {
                'host': 'localhost',
                'port': '5432',
                'database': 'warehouse',
                'user': 'admin',
                'password': 'secret'
            }
        else:
            self.connection_params = connection_params
        
        self.metrics = {}
        
    def connect_db(self):
        """Connect to PostgreSQL database"""
        try:
            conn = psycopg2.connect(**self.connection_params)
            return conn
        except psycopg2.Error as e:
            logger.error(f"Database connection failed: {e}")
            return None
    
    def get_key_metrics(self):
        """Get key business metrics"""
        conn = self.connect_db()
        if not conn:
            return {}
        
        try:
            with conn.cursor() as cur:
                # Total customers
                cur.execute("SELECT COUNT(*) FROM customers")
                total_customers = cur.fetchone()[0]
                
                # Total products
                cur.execute("SELECT COUNT(*) FROM products")
                total_products = cur.fetchone()[0]
                
                # Total orders
                cur.execute("SELECT COUNT(*) FROM orders")
                total_orders = cur.fetchone()[0]
                
                # Total revenue
                cur.execute("SELECT SUM(total_amount) FROM orders")
                total_revenue = cur.fetchone()[0] or 0
                
                # Average order value
                cur.execute("SELECT AVG(total_amount) FROM orders")
                avg_order_value = cur.fetchone()[0] or 0
                
                # Top category by revenue
                cur.execute("""
                    SELECT p.category, SUM(oi.quantity * oi.unit_price) as revenue
                    FROM products p
                    JOIN order_items oi ON p.product_id = oi.product_id
                    GROUP BY p.category
                    ORDER BY revenue DESC
                    LIMIT 1
                """)
                top_category_result = cur.fetchone()
                top_category = top_category_result[0] if top_category_result else 'N/A'
                top_category_revenue = top_category_result[1] if top_category_result else 0
                
                # Recent orders (last 7 days)
                cur.execute("""
                    SELECT COUNT(*) FROM orders 
                    WHERE order_date >= CURRENT_DATE - INTERVAL '7 days'
                """)
                recent_orders = cur.fetchone()[0]
                
                return {
                    'total_customers': total_customers,
                    'total_products': total_products,
                    'total_orders': total_orders,
                    'total_revenue': float(total_revenue),
                    'avg_order_value': float(avg_order_value),
                    'top_category': top_category,
                    'top_category_revenue': float(top_category_revenue),
                    'recent_orders': recent_orders
                }
        except psycopg2.Error as e:
            logger.error(f"Error getting key metrics: {e}")
            return {}
        finally:
            conn.close()
    
    def get_sales_trends(self):
        """Get sales trends by month"""
        conn = self.connect_db()
        if not conn:
            return [], []
        
        try:
            with conn.cursor() as cur:
                cur.execute("""
                    SELECT 
                        DATE_TRUNC('month', order_date) as month,
                        COUNT(*) as order_count,
                        SUM(total_amount) as revenue
                    FROM orders
                    GROUP BY DATE_TRUNC('month', order_date)
                    ORDER BY month
                """)
                
                results = cur.fetchall()
                months = [row[0].strftime('%Y-%m') for row in results]
                revenues = [float(row[2]) for row in results]
                
                return months, revenues
        except psycopg2.Error as e:
            logger.error(f"Error getting sales trends: {e}")
            return [], []
        finally:
            conn.close()
    
    def get_category_performance(self):
        """Get performance by product category"""
        conn = self.connect_db()
        if not conn:
            return [], []
        
        try:
            with conn.cursor() as cur:
                cur.execute("""
                    SELECT 
                        p.category,
                        COUNT(oi.order_item_id) as items_sold,
                        SUM(oi.quantity * oi.unit_price) as revenue
                    FROM products p
                    JOIN order_items oi ON p.product_id = oi.product_id
                    GROUP BY p.category
                    ORDER BY revenue DESC
                """)
                
                results = cur.fetchall()
                categories = [row[0] for row in results]
                revenues = [float(row[2]) for row in results]
                
                return categories, revenues
        except psycopg2.Error as e:
            logger.error(f"Error getting category performance: {e}")
            return [], []
        finally:
            conn.close()
    
    def get_top_customers(self, limit=10):
        """Get top customers by total spending"""
        conn = self.connect_db()
        if not conn:
            return []
        
        try:
            with conn.cursor() as cur:
                cur.execute("""
                    SELECT 
                        c.first_name || ' ' || c.last_name as customer_name,
                        c.country,
                        COUNT(o.order_id) as order_count,
                        SUM(o.total_amount) as total_spent
                    FROM customers c
                    JOIN orders o ON c.customer_id = o.customer_id
                    GROUP BY c.customer_id, c.first_name, c.last_name, c.country
                    ORDER BY total_spent DESC
                    LIMIT %s
                """, (limit,))
                
                return cur.fetchall()
        except psycopg2.Error as e:
            logger.error(f"Error getting top customers: {e}")
            return []
        finally:
            conn.close()
    
    def create_chart(self, chart_type, data, title, xlabel='', ylabel=''):
        """Create a chart and return as base64 encoded string"""
        plt.figure(figsize=(10, 6))
        plt.style.use('default')
        
        if chart_type == 'line':
            labels, values = data
            plt.plot(labels, values, marker='o', linewidth=2, markersize=6)
            plt.xticks(rotation=45)
        elif chart_type == 'bar':
            labels, values = data
            bars = plt.bar(labels, values, color=['#FF6B6B', '#4ECDC4', '#45B7D1', '#96CEB4', '#FECA57'])
            # Add value labels on bars
            for bar, value in zip(bars, values):
                height = bar.get_height()
                plt.text(bar.get_x() + bar.get_width()/2., height,
                        f'${value:,.0f}', ha='center', va='bottom')
            plt.xticks(rotation=45)
        elif chart_type == 'pie':
            labels, values = data
            colors = ['#FF6B6B', '#4ECDC4', '#45B7D1', '#96CEB4', '#FECA57']
            plt.pie(values, labels=labels, autopct='%1.1f%%', colors=colors)
        
        plt.title(title, fontsize=14, fontweight='bold')
        plt.xlabel(xlabel)
        plt.ylabel(ylabel)
        plt.tight_layout()
        
        # Convert to base64
        buffer = io.BytesIO()
        plt.savefig(buffer, format='png', dpi=100, bbox_inches='tight')
        buffer.seek(0)
        image_base64 = base64.b64encode(buffer.getvalue()).decode()
        plt.close()
        
        return image_base64
    
    def generate_html_dashboard(self):
        """Generate complete HTML dashboard"""
        logger.info("Generating dashboard...")
        
        # Get data
        metrics = self.get_key_metrics()
        months, revenues = self.get_sales_trends()
        categories, cat_revenues = self.get_category_performance()
        top_customers = self.get_top_customers()
        
        # Create charts
        revenue_chart = self.create_chart(
            'line', 
            (months, revenues),
            'Monthly Revenue Trends',
            'Month',
            'Revenue ($)'
        )
        
        category_chart = self.create_chart(
            'bar',
            (categories, cat_revenues),
            'Revenue by Category',
            'Category',
            'Revenue ($)'
        )
        
        category_pie_chart = self.create_chart(
            'pie',
            (categories, cat_revenues),
            'Revenue Distribution by Category'
        )
        
        # Generate HTML
        html_template = """
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Mini Data Warehouse Dashboard</title>
    <style>
        body {{
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            margin: 0;
            padding: 0;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            min-height: 100vh;
        }}
        
        .container {{
            max-width: 1200px;
            margin: 0 auto;
            padding: 20px;
        }}
        
        .header {{
            background: rgba(255, 255, 255, 0.95);
            border-radius: 15px;
            padding: 20px;
            text-align: center;
            margin-bottom: 30px;
            box-shadow: 0 8px 32px rgba(0, 0, 0, 0.1);
        }}
        
        .header h1 {{
            color: #333;
            margin: 0;
            font-size: 2.5em;
            font-weight: 300;
        }}
        
        .header p {{
            color: #666;
            margin: 10px 0 0 0;
            font-size: 1.1em;
        }}
        
        .metrics-grid {{
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(250px, 1fr));
            gap: 20px;
            margin-bottom: 30px;
        }}
        
        .metric-card {{
            background: rgba(255, 255, 255, 0.95);
            border-radius: 15px;
            padding: 25px;
            text-align: center;
            box-shadow: 0 8px 32px rgba(0, 0, 0, 0.1);
            transition: transform 0.3s ease;
        }}
        
        .metric-card:hover {{
            transform: translateY(-5px);
        }}
        
        .metric-value {{
            font-size: 2.5em;
            font-weight: bold;
            color: #4ECDC4;
            margin: 0;
        }}
        
        .metric-label {{
            font-size: 1em;
            color: #666;
            margin: 5px 0 0 0;
            text-transform: uppercase;
            letter-spacing: 1px;
        }}
        
        .charts-section {{
            display: grid;
            grid-template-columns: 1fr 1fr;
            gap: 30px;
            margin-bottom: 30px;
        }}
        
        .chart-card {{
            background: rgba(255, 255, 255, 0.95);
            border-radius: 15px;
            padding: 25px;
            box-shadow: 0 8px 32px rgba(0, 0, 0, 0.1);
        }}
        
        .chart-card h3 {{
            margin: 0 0 20px 0;
            color: #333;
            text-align: center;
        }}
        
        .chart-img {{
            width: 100%;
            border-radius: 10px;
        }}
        
        .full-width {{
            grid-column: 1 / -1;
        }}
        
        .customers-table {{
            background: rgba(255, 255, 255, 0.95);
            border-radius: 15px;
            padding: 25px;
            box-shadow: 0 8px 32px rgba(0, 0, 0, 0.1);
            margin-top: 30px;
        }}
        
        .customers-table h3 {{
            margin: 0 0 20px 0;
            color: #333;
            text-align: center;
        }}
        
        table {{
            width: 100%;
            border-collapse: collapse;
        }}
        
        th, td {{
            padding: 12px;
            text-align: left;
            border-bottom: 1px solid #ddd;
        }}
        
        th {{
            background-color: #f8f9fa;
            font-weight: 600;
            color: #333;
        }}
        
        .footer {{
            text-align: center;
            color: rgba(255, 255, 255, 0.8);
            margin-top: 40px;
            padding: 20px;
        }}
        
        @media (max-width: 768px) {{
            .charts-section {{
                grid-template-columns: 1fr;
            }}
            
            .metric-value {{
                font-size: 2em;
            }}
            
            .header h1 {{
                font-size: 2em;
            }}
        }}
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            <h1>📊 Mini Data Warehouse</h1>
            <p>Business Intelligence Dashboard • Generated on {timestamp}</p>
        </div>
        
        <div class="metrics-grid">
            <div class="metric-card">
                <div class="metric-value">{total_customers:,}</div>
                <div class="metric-label">Total Customers</div>
            </div>
            <div class="metric-card">
                <div class="metric-value">{total_products:,}</div>
                <div class="metric-label">Products</div>
            </div>
            <div class="metric-card">
                <div class="metric-value">{total_orders:,}</div>
                <div class="metric-label">Total Orders</div>
            </div>
            <div class="metric-card">
                <div class="metric-value">${total_revenue:,.0f}</div>
                <div class="metric-label">Total Revenue</div>
            </div>
            <div class="metric-card">
                <div class="metric-value">${avg_order_value:.0f}</div>
                <div class="metric-label">Avg Order Value</div>
            </div>
            <div class="metric-card">
                <div class="metric-value">{recent_orders:,}</div>
                <div class="metric-label">Recent Orders (7d)</div>
            </div>
        </div>
        
        <div class="charts-section">
            <div class="chart-card">
                <img src="data:image/png;base64,{revenue_chart}" alt="Revenue Trends" class="chart-img">
            </div>
            <div class="chart-card">
                <img src="data:image/png;base64,{category_chart}" alt="Category Performance" class="chart-img">
            </div>
        </div>
        
        <div class="chart-card full-width">
            <img src="data:image/png;base64,{category_pie_chart}" alt="Revenue Distribution" class="chart-img">
        </div>
        
        <div class="customers-table">
            <h3>🏆 Top Customers</h3>
            <table>
                <thead>
                    <tr>
                        <th>Customer Name</th>
                        <th>Country</th>
                        <th>Orders</th>
                        <th>Total Spent</th>
                    </tr>
                </thead>
                <tbody>
                    {customer_rows}
                </tbody>
            </table>
        </div>
        
        <div class="footer">
            <p>Mini Data Warehouse Dashboard • PostgreSQL + Python • Star Schema Analytics</p>
        </div>
    </div>
</body>
</html>
        """
        
        # Generate customer table rows
        customer_rows = ""
        for customer in top_customers:
            customer_rows += f"""
                    <tr>
                        <td>{customer[0]}</td>
                        <td>{customer[1]}</td>
                        <td>{customer[2]}</td>
                        <td>${customer[3]:,.2f}</td>
                    </tr>"""
        
        # Fill template
        html_content = html_template.format(
            timestamp=datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
            total_customers=metrics.get('total_customers', 0),
            total_products=metrics.get('total_products', 0),
            total_orders=metrics.get('total_orders', 0),
            total_revenue=metrics.get('total_revenue', 0),
            avg_order_value=metrics.get('avg_order_value', 0),
            recent_orders=metrics.get('recent_orders', 0),
            revenue_chart=revenue_chart,
            category_chart=category_chart,
            category_pie_chart=category_pie_chart,
            customer_rows=customer_rows
        )
        
        return html_content
    
    def save_dashboard(self, filename='dashboard.html'):
        """Save dashboard to HTML file"""
        html_content = self.generate_html_dashboard()
        
        with open(filename, 'w') as f:
            f.write(html_content)
        
        logger.info(f"Dashboard saved to {filename}")
        return filename

def main():
    """Main function to generate dashboard"""
    import argparse
    
    parser = argparse.ArgumentParser(description='Generate dashboard for Mini Data Warehouse')
    parser.add_argument('--output', '-o', default='dashboard.html', help='Output HTML file')
    
    args = parser.parse_args()
    
    generator = DashboardGenerator()
    
    try:
        filename = generator.save_dashboard(args.output)
        print(f"✅ Dashboard generated successfully: {filename}")
        print(f"📝 Open {filename} in your web browser to view the dashboard")
        return 0
    except Exception as e:
        logger.error(f"Error generating dashboard: {e}")
        return 1

if __name__ == "__main__":
    exit(main())