import duckdb
import pandas as pd
from datetime import datetime, timedelta
from jinja2 import Template
import json
import os
from pathlib import Path

class DataProductEngine:
    """
    Multi-tenant automated report generator.
    
    Core philosophy:
    - Each client lives in its own directory — data isolation
    - DuckDB in-memory mode — zero ops, zero config
    - Template-driven — change the template, not the code
    """
    
    def __init__(self, base_dir: str = "./clients"):
        self.base_dir = Path(base_dir)
        self.base_dir.mkdir(exist_ok=True)
    
    def load_client_config(self, client_id: str) -> dict:
        """Load client configuration from JSON."""
        config_path = self.base_dir / client_id / "config.json"
        with open(config_path, 'r') as f:
            return json.load(f)
    
    def scan_data_files(self, client_id: str, date_str: str) -> list:
        """Find the latest data files for a client."""
        data_dir = self.base_dir / client_id / "data"
        pattern = f"*{date_str}*.csv"
        return list(data_dir.glob(pattern))
    
    def run_report(self, client_id: str, date_str: str = None):
        """
        Generate a report for one client on a given date.
        
        Pipeline:
        1. Load client config
        2. Find data files for the day
        3. DuckDB loads and aggregates
        4. SQL computes core KPIs
        5. Jinja2 renders HTML report
        6. Save report file
        """
        if date_str is None:
            date_str = (datetime.now() - timedelta(days=1)).strftime('%Y-%m-%d')
        
        config = self.load_client_config(client_id)
        data_files = self.scan_data_files(client_id, date_str)
        
        if not data_files:
            print(f"⚠️ Client {client_id}: no data files for {date_str}")
            return
        
        # Spin up DuckDB — in-memory, zero config
        con = duckdb.connect()
        
        # Load all CSV files into a table
        file_paths = [str(f) for f in data_files]
        con.execute(f"""
            CREATE TABLE raw_data AS 
            SELECT * FROM read_csv_auto({file_paths})
        """)
        
        # Execute client's custom report SQL
        report_sql = config.get("report_sql", """
            SELECT 
                date,
                COUNT(*) AS total_records,
                COUNT(DISTINCT customer_id) AS unique_customers,
                SUM(amount) AS total_revenue,
                AVG(amount) AS avg_value
            FROM raw_data
            GROUP BY date
        """)
        
        report_df = con.execute(report_sql).df()
        
        # Execute dimensional analysis queries
        dimension_sqls = config.get("dimension_sqls", {})
        dimensions = {}
        for dim_name, dim_sql in dimension_sqls.items():
            try:
                dimensions[dim_name] = con.execute(dim_sql).df()
            except Exception as e:
                print(f"  Dimension '{dim_name}' failed: {e}")
                dimensions[dim_name] = pd.DataFrame()
        
        con.close()
        
        # Render HTML with Jinja2
        template_str = config.get("html_template", self._default_template())
        template = Template(template_str)
        html_content = template.render(
            client_name=config.get("name", client_id),
            date=date_str,
            report=report_df,
            dimensions=dimensions,
            config=config
        )
        
        # Save the report
        output_dir = self.base_dir / client_id / "reports"
        output_dir.mkdir(exist_ok=True)
        output_path = output_dir / f"report_{date_str}.html"
        
        with open(output_path, 'w', encoding='utf-8') as f:
            f.write(html_content)
        
        print(f"✅ [{client_id}] Report saved: {output_path}")
        return str(output_path)
    
    def run_all_clients(self, date_str: str = None):
        """Generate reports for every client."""
        for client_dir in self.base_dir.iterdir():
            if client_dir.is_dir() and (client_dir / "config.json").exists():
                client_id = client_dir.name
                print(f"\n📋 Processing: {client_id}")
                self.run_report(client_id, date_str)
    
    def _default_template(self) -> str:
        return """
        <html>
        <head>
            <meta charset='utf-8'>
            <title>{{ client_name }} - Report {{ date }}</title>
            <style>
                body { font-family: -apple-system, BlinkMacSystemFont, sans-serif;
                       max-width: 960px; margin: 0 auto; padding: 20px;
                       background: #f5f7fa; color: #333; }
                .header { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
                          color: white; padding: 30px; border-radius: 12px; margin-bottom: 24px; }
                .header h1 { margin: 0 0 8px 0; font-size: 28px; }
                .header p { margin: 0; opacity: 0.9; }
                .kpi-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
                             gap: 16px; margin-bottom: 24px; }
                .kpi-card { background: white; padding: 20px; border-radius: 10px;
                            box-shadow: 0 2px 8px rgba(0,0,0,0.06); }
                .kpi-label { font-size: 13px; color: #888; margin-bottom: 6px; }
                .kpi-value { font-size: 28px; font-weight: 700; color: #333; }
                table { width: 100%; border-collapse: collapse; background: white;
                        border-radius: 10px; overflow: hidden;
                        box-shadow: 0 2px 8px rgba(0,0,0,0.06); }
                th { background: #f0f2f5; padding: 12px 16px; text-align: left;
                     font-size: 13px; color: #666; text-transform: uppercase; }
                td { padding: 12px 16px; border-top: 1px solid #f0f2f5; }
                tr:hover td { background: #f8f9ff; }
            </style>
        </head>
        <body>
            <div class="header">
                <h1>{{ client_name }}</h1>
                <p>Daily Report · {{ date }}</p>
            </div>
            
            {% if not report.empty %}
            <div class="kpi-grid">
                {% for col in report.columns %}
                <div class="kpi-card">
                    <div class="kpi-label">{{ col }}</div>
                    <div class="kpi-value">{{ report[col].iloc[0] }}</div>
                </div>
                {% endfor %}
            </div>
            {% endif %}
            
            {% for dim_name, dim_df in dimensions.items() %}
                {% if not dim_df.empty %}
                <h2>{{ dim_name }}</h2>
                <table>
                    <tr>
                        {% for col in dim_df.columns %}
                        <th>{{ col }}</th>
                        {% endfor %}
                    </tr>
                    {% for _, row in dim_df.iterrows() %}
                    <tr>
                        {% for col in dim_df.columns %}
                        <td>{{ row[col] }}</td>
                        {% endfor %}
                    </tr>
                    {% endfor %}
                </table>
                <br>
                {% endif %}
            {% endfor %}
            
            <p style="text-align:center; color:#999; font-size:12px; margin-top:40px;">
                Generated by DataProductEngine · DuckDB Backend
            </p>
        </body>
        </html>
        """


# ========== Usage Example ==========

if __name__ == "__main__":
    engine = DataProductEngine()
    
    # Generate yesterday's reports for all clients
    engine.run_all_clients()

{
    "name": "ACME E-commerce - Daily Ops Report",
    "data_source": "sftp://192.168.1.100/data/sales_2026-06-03.csv",
    "report_sql": "SELECT date, COUNT(*) AS total_orders, ROUND(SUM(amount),2) AS revenue, ROUND(AVG(amount),2) AS avg_order, COUNT(DISTINCT user_id) AS buyers FROM raw_data WHERE status = 'completed' GROUP BY date",
    "dimension_sqls": {
        "Top 5 Categories": "SELECT category, SUM(amount) AS revenue, COUNT(*) AS orders FROM raw_data WHERE status = 'completed' GROUP BY category ORDER BY revenue DESC LIMIT 5",
        "Daily Trend": "SELECT date, COUNT(*) AS orders, SUM(amount) AS revenue FROM raw_data WHERE status = 'completed' GROUP BY date ORDER BY date"
    },
    "schedule": "0 7 * * *",
    "delivery": {
        "type": "email",
        "recipients": ["[email protected]", "[email protected]"]
    }
}

#!/bin/bash
# deploy.sh - One-command data product backend deployment

# 1. Install dependencies — just three packages
pip install duckdb pandas jinja2

# 2. Create client directory structure
mkdir -p clients/{client_a,client_b,client_c}/data
mkdir -p clients/{client_a,client_b,client_c}/reports

# 3. Create client config files
cat > clients/client_a/config.json << 'CONFIG'
{
    "name": "Client A - E-commerce",
    "report_sql": "SELECT ...",
    "schedule": "0 7 * * *"
}
CONFIG

# 4. Install cron job for daily auto-run
(crontab -l 2>/dev/null; echo "0 7 * * * cd /home/ubuntu/data-product && python engine.py") | crontab -

echo "✅ Data product backend deployed"