#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""Dashboard KPI and chart data API"""
import json

result = {'success': False, 'rows': [], 'total': 0}

try:
    kpi_type = params_kw.get('type', '').strip()
    
    if kpi_type == 'sales_funnel':
        dbname = get_module_dbname('opportunity_management')
        async with DBPools().sqlorContext(dbname) as sor:
            stages = await sor.sqlExe("SELECT id, stage_name, stage_order FROM sales_stages ORDER BY stage_order ASC")
            funnel_data = []
            for stage in stages:
                rows = await sor.sqlExe("SELECT COUNT(*) as cnt, COALESCE(SUM(estimated_amount),0) as amt FROM opportunities WHERE current_stage = ${stage_id}$", {'stage_id': stage['id']})
                funnel_data.append({
                    'stage': stage['stage_name'],
                    'count': rows[0]['cnt'] if rows else 0,
                    'amount': float(rows[0]['amt'] or 0) if rows else 0
                })
            result['rows'] = funnel_data
            result['total'] = len(funnel_data)
            result['success'] = True
    
    elif kpi_type == 'recent_opportunities':
        dbname = get_module_dbname('opportunity_management')
        async with DBPools().sqlorContext(dbname) as sor:
            rows = await sor.sqlExe("SELECT customer_name, estimated_amount, current_stage FROM opportunities ORDER BY created_at DESC LIMIT 10")
            result['rows'] = [dict(r) for r in (rows or [])]
            result['total'] = len(result['rows'])
            result['success'] = True
    
    elif kpi_type == 'kpi_summary':
        # Aggregate KPIs from all modules
        kpis = {}
        
        # Customer count
        try:
            dbname = get_module_dbname('customer_management')
            async with DBPools().sqlorContext(dbname) as sor:
                rows = await sor.sqlExe("SELECT COUNT(*) as cnt FROM customers")
                kpis['total_customers'] = rows[0]['cnt'] if rows else 0
        except:
            kpis['total_customers'] = 0
        
        # Active opportunities
        try:
            dbname = get_module_dbname('opportunity_management')
            async with DBPools().sqlorContext(dbname) as sor:
                rows = await sor.sqlExe("SELECT COUNT(*) as cnt FROM opportunities WHERE status = 'active'")
                kpis['active_opportunities'] = rows[0]['cnt'] if rows else 0
        except:
            kpis['active_opportunities'] = 0
        
        # Contract count
        try:
            dbname = get_module_dbname('contract_management')
            async with DBPools().sqlorContext(dbname) as sor:
                rows = await sor.sqlExe("SELECT COUNT(*) as cnt FROM contract")
                kpis['total_contracts'] = rows[0]['cnt'] if rows else 0
        except:
            kpis['total_contracts'] = 0
        
        # Total receivables
        try:
            dbname = get_module_dbname('financial_management')
            async with DBPools().sqlorContext(dbname) as sor:
                rows = await sor.sqlExe("SELECT COALESCE(SUM(receivable_amount),0) as total FROM receivables")
                kpis['total_receivables'] = float(rows[0]['total'] or 0) if rows else 0
        except:
            kpis['total_receivables'] = 0
        
        result['success'] = True
        result['data'] = kpis
    
    else:
        result['error'] = f'Unknown KPI type: {kpi_type}'

except Exception as e:
    result['error'] = str(e)

return json.dumps(result, ensure_ascii=False, default=str)