"""
SDLC Dashboard Data API
Returns real-time stats for the dashboard widgets
"""
from sqlor.dbpools import DBPools

async def main():
    dbname = get_module_dbname('pipeline-sdlc')
    
    async with DBPools().sqlorContext(dbname) as sor:
        # 1) Project stats
        projects = await sor.sqlExe(
            "SELECT COUNT(*) as total, SUM(CASE WHEN status='active' THEN 1 ELSE 0 END) as active FROM sd_projects", {})
        p = projects[0] if projects else None
        
        # 2) Active iterations
        iters = await sor.sqlExe(
            "SELECT COUNT(*) as cnt FROM sd_iterations WHERE status='in_progress'", {})
        active_iters = iters[0].cnt if iters else 0
        
        # 3) Open bugs
        bugs = await sor.sqlExe(
            "SELECT COUNT(*) as cnt FROM sd_bugs WHERE status!='closed' AND status!='resolved'", {})
        open_bugs = bugs[0].cnt if bugs else 0
        
        # 4) Pending reviews
        reviews = await sor.sqlExe(
            "SELECT COUNT(*) as cnt FROM pipeline_human_tasks WHERE status='pending' AND task_type='review'", {})
        pending_reviews = reviews[0].cnt if reviews else 0
        
        # 5) Recent iterations with progress
        recent_iters = await sor.sqlExe("""
            SELECT i.name, i.status, i.start_date, i.end_date,
                   COALESCE(tc.total,0) as total_tasks,
                   COALESCE(tc.completed,0) as completed_tasks
            FROM sd_iterations i
            LEFT JOIN (
                SELECT iteration_id, COUNT(*) as total,
                       SUM(CASE WHEN status='completed' THEN 1 ELSE 0 END) as completed
                FROM sd_test_plans GROUP BY iteration_id
            ) tc ON tc.iteration_id = i.id
            WHERE i.status != 'closed'
            ORDER BY i.start_date DESC LIMIT 5
        """, {})
        
        iter_list = []
        for r in recent_iters:
            pct = round(r.completed_tasks / r.total_tasks * 100) if r.total_tasks > 0 else 0
            iter_list.append({
                'name': r.name,
                'status': r.status,
                'progress': pct,
                'total': r.total_tasks,
                'completed': r.completed_tasks
            })
        
        # 6) Bug by severity
        bug_by_sev = await sor.sqlExe("""
            SELECT COALESCE(severity,'unset') as severity, COUNT(*) as cnt
            FROM sd_bugs WHERE status!='closed'
            GROUP BY severity ORDER BY cnt DESC
        """, {})
        
        severity_map = {
            'critical': {'label': '严重', 'color': '#FF4444'},
            'major': {'label': '主要', 'color': '#FF8800'},
            'minor': {'label': '次要', 'color': '#FFAA00'},
            'trivial': {'label': '轻微', 'color': '#888888'},
            'unset': {'label': '未分类', 'color': '#666666'}
        }
        bugs_data = []
        for r in bug_by_sev:
            sev = severity_map.get(r.severity, {'label': r.severity, 'color': '#888'})
            bugs_data.append({'label': sev['label'], 'count': r.cnt, 'color': sev['color']})
        
        return {
            'projects_total': p.total if p else 0,
            'projects_active': p.active if p else 0,
            'active_iterations': active_iters,
            'open_bugs': open_bugs,
            'pending_reviews': pending_reviews,
            'recent_iterations': iter_list,
            'bugs_by_severity': bugs_data
        }