scripts/state_db.py - master - davydov/NGPowers

scripts/state_db.py
1 052 строки43 KB
Roman Davydov
feat(ngpowers): implement pre-archival safety gate and task auto-archiving safety
11 авг 2026, 01:33
11 авг 2026, 01:33add1f7e
#!/usr/bin/env python3
import os
import sys
import sqlite3
import json
import argparse

def resolve_db_path(db_arg=None):
    if db_arg:
        return os.path.abspath(db_arg)
    
    script_dir = os.path.dirname(os.path.abspath(__file__))
    parent_dir_name = os.path.basename(os.path.abspath(os.path.join(script_dir, ".."))) # '.ngpowers' or '.agents'
    if parent_dir_name.lower() in ("ngpowers", ".ngpowers"):
        parent_dir_name = ".ngpowers"
    elif parent_dir_name.lower() in ("agents", ".agents"):
        parent_dir_name = ".agents"
    
    # Dynamic root resolution
    curr = os.getcwd()
    if os.environ.get("PROJECT_ROOT") and os.path.exists(os.environ["PROJECT_ROOT"]):
        project_root = os.path.abspath(os.environ["PROJECT_ROOT"])
    else:
        project_root = None
        # Check script-relative root
        script_root = os.path.abspath(os.path.join(script_dir, "..", ".."))
        if (os.path.exists(os.path.join(script_root, ".ngpowers")) or 
            os.path.exists(os.path.join(script_root, ".agents")) or 
            os.path.exists(os.path.join(script_root, "service_spec.md"))):
            project_root = script_root
        else:
            # Walk up from CWD
            while curr != os.path.dirname(curr):
                if (os.path.exists(os.path.join(curr, ".ngpowers")) or 
                    os.path.exists(os.path.join(curr, ".agents")) or 
                    os.path.exists(os.path.join(curr, "service_spec.md")) or 
                    os.path.exists(os.path.join(curr, ".git"))):
                    project_root = curr
                    break
                curr = os.path.dirname(curr)
            if not project_root:
                project_root = script_root

    return os.path.join(project_root, parent_dir_name, "state.db")

class StateDB:
    def __init__(self, db_path):
        self.db_path = db_path
        self.conn = None
        self.init_db()

    def connect(self):
        self.conn = sqlite3.connect(self.db_path)
        self.conn.execute("PRAGMA foreign_keys = ON;")

    def close(self):
        if self.conn:
            self.conn.close()

    def init_db(self):
        db_dir = os.path.dirname(self.db_path)
        if db_dir:
            os.makedirs(db_dir, exist_ok=True)
            
        self.connect()
        cursor = self.conn.cursor()
        
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS tasks (
            task_id TEXT PRIMARY KEY,
            branch_name TEXT,
            current_phase INTEGER DEFAULT 0,
            status TEXT DEFAULT 'active',
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        );""")
        
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS handoffs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            task_id TEXT,
            sender TEXT,
            recipient TEXT,
            action TEXT,
            payload TEXT,
            timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(task_id) REFERENCES tasks(task_id) ON DELETE CASCADE
        );""")
        
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS defects (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            task_id TEXT,
            file_path TEXT,
            line_number INTEGER,
            description TEXT,
            status TEXT DEFAULT 'open',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            resolved_at DATETIME,
            FOREIGN KEY(task_id) REFERENCES tasks(task_id) ON DELETE CASCADE
        );""")
        
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS vulnerabilities (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            task_id TEXT,
            severity TEXT,
            file_path TEXT,
            line_number INTEGER,
            description TEXT,
            status TEXT DEFAULT 'open',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            mitigated_at DATETIME,
            FOREIGN KEY(task_id) REFERENCES tasks(task_id) ON DELETE CASCADE
        );""")

        cursor.execute("""
        CREATE TABLE IF NOT EXISTS task_phase_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            task_id TEXT,
            phase INTEGER,
            entered_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            exited_at DATETIME,
            FOREIGN KEY(task_id) REFERENCES tasks(task_id) ON DELETE CASCADE
        );""")

        cursor.execute("""
        CREATE TABLE IF NOT EXISTS task_artifacts (
            task_id TEXT PRIMARY KEY,
            artifact_dir TEXT NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        );""")

        self.conn.commit()
        self.close()

    def run_query(self, query, params=(), commit=False, fetch_all=True):
        self.connect()
        cursor = self.conn.cursor()
        cursor.execute(query, params)
        res = None
        if commit:
            self.conn.commit()
        else:
            res = cursor.fetchall() if fetch_all else cursor.fetchone()
        self.close()
        return res

    def init_task(self, task_id, branch_name):
        query = """
        INSERT INTO tasks (task_id, branch_name, updated_at)
        VALUES (?, ?, CURRENT_TIMESTAMP)
        ON CONFLICT(task_id) DO UPDATE SET
            branch_name = excluded.branch_name,
            updated_at = CURRENT_TIMESTAMP;
        """
        self.run_query(query, (task_id, branch_name), commit=True)
        return {"status": "success", "message": f"Task {task_id} initialized in DB: {self.db_path}"}

    def update_phase(self, task_id, phase):
        query = "UPDATE tasks SET current_phase = ?, updated_at = CURRENT_TIMESTAMP WHERE task_id = ?;"
        self.run_query(query, (phase, task_id), commit=True)
        return {"status": "success", "message": f"Task {task_id} moved to phase {phase}."}

    def write_handoff(self, task_id, sender, recipient, action, payload_data):
        payload_str = json.dumps(payload_data)
        query = """
        INSERT INTO handoffs (task_id, sender, recipient, action, payload)
        VALUES (?, ?, ?, ?, ?);
        """
        self.run_query(query, (task_id, sender, recipient, action, payload_str), commit=True)
        return {"status": "success", "message": "Handoff transaction logged."}

    def add_defect(self, task_id, file_path, line_number, description):
        query = """
        INSERT INTO defects (task_id, file_path, line_number, description)
        VALUES (?, ?, ?, ?);
        """
        self.run_query(query, (task_id, file_path, line_number, description), commit=True)
        return {"status": "success", "message": "Defect added."}

    def resolve_defect(self, defect_id):
        query = "UPDATE defects SET status = 'resolved', resolved_at = CURRENT_TIMESTAMP WHERE id = ?;"
        self.run_query(query, (defect_id,), commit=True)
        return {"status": "success", "message": f"Defect {defect_id} resolved."}

    def mitigate_vulnerability(self, vuln_id):
        query = "UPDATE vulnerabilities SET status = 'mitigated', mitigated_at = CURRENT_TIMESTAMP WHERE id = ?;"
        self.run_query(query, (vuln_id,), commit=True)
        return {"status": "success", "message": f"Vulnerability {vuln_id} marked as mitigated."}

    def get_feature_report(self, task_id):
        """Generate focused Single-Feature Report for CI/CD and PR gating."""
        task_info = self.get_task_status(task_id)
        if task_info.get("status") == "error":
            return task_info

        defects = self.get_defects(task_id)
        vulnerabilities = self.get_vulnerabilities(task_id)
        handoffs = self.get_handoffs(task_id)
        art_dir = self.get_artifact_dir(task_id).get("artifact_dir", "N/A")

        open_vulns = [v for v in vulnerabilities if v["status"] == "open"]
        mitigated_vulns = [v for v in vulnerabilities if v["status"] == "mitigated"]
        open_defects = [d for d in defects if d["status"] == "open"]
        resolved_defects = [d for d in defects if d["status"] == "resolved"]

        is_passed = (len(open_vulns) == 0) and (len(open_defects) == 0)

        return {
            "status": "success",
            "feature_report": {
                "task_id": task_id,
                "branch_name": task_info.get("branch_name"),
                "current_phase": task_info.get("current_phase"),
                "artifact_dir": art_dir,
                "gate_verdict": "PASSED" if is_passed else "BLOCKED",
                "ciso_vulnerabilities": {
                    "open_count": len(open_vulns),
                    "mitigated_count": len(mitigated_vulns),
                    "details": vulnerabilities
                },
                "qa_defects": {
                    "open_count": len(open_defects),
                    "resolved_count": len(resolved_defects),
                    "details": defects
                },
                "handoff_timeline": handoffs
            }
        }

    def export_feature_html_report(self, task_id, output_path=None):
        """Generate focused Single-Feature HTML Report for a specific task_id (sdlc_report_<task_id>.html)."""
        rep = self.get_feature_report(task_id)
        if rep.get("status") == "error":
            return rep

        f = rep["feature_report"]

        if not output_path:
            db_dir = os.path.dirname(self.db_path)
            output_path = os.path.join(db_dir, "reports", f"sdlc_report_{task_id}.html")

        os.makedirs(os.path.dirname(output_path), exist_ok=True)

        verdict_color = "#4ade80" if f["gate_verdict"] == "PASSED" else "#f87171"

        html_content = f"""<!DOCTYPE html>
<html lang="ru">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>NGPowers Single Feature Report: {task_id}</title>
    <style>
        body {{
            font-family: 'Segoe UI', system-ui, -apple-system, sans-serif;
            background-color: #0f172a;
            color: #f8fafc;
            margin: 0;
            padding: 24px;
        }}
        .card {{
            background: #1e293b;
            border-radius: 12px;
            padding: 20px;
            margin-bottom: 20px;
            border: 1px solid #334155;
        }}
        .verdict-banner {{
            padding: 16px 24px;
            border-radius: 8px;
            font-size: 24px;
            font-weight: bold;
            text-align: center;
            background: {verdict_color}22;
            color: {verdict_color};
            border: 2px solid {verdict_color};
            margin-bottom: 24px;
        }}
        code {{ background: #0f172a; padding: 2px 6px; border-radius: 4px; color: #38bdf8; }}
        table {{ width: 100%; border-collapse: collapse; margin-top: 12px; }}
        th, td {{ padding: 10px 14px; text-align: left; border-bottom: 1px solid #334155; }}
        th {{ background: #0f172a; color: #94a3b8; }}
    </style>
</head>
<body>
    <h1>🚀 NGPowers Feature Pipeline Report: {task_id}</h1>
    
    <div class="verdict-banner">
        GATE VERDICT: {f['gate_verdict']}
    </div>

    <div class="card">
        <h3>📋 Сведения о задаче</h3>
        <p><strong>Feature ID:</strong> {f['task_id']}</p>
        <p><strong>Git Branch:</strong> <code>{f['branch_name']}</code></p>
        <p><strong>Текущая Фаза SDLC:</strong> Phase {f['current_phase']}</p>
        <p><strong>Артефакты:</strong> <code>{f['artifact_dir']}</code></p>
    </div>

    <div class="card">
        <h3>🛡️ CISO Security Gate ({f['ciso_vulnerabilities']['open_count']} Open / {f['ciso_vulnerabilities']['mitigated_count']} Mitigated)</h3>
        <table>
            <thead><tr><th>Severity</th><th>File</th><th>Description</th><th>Status</th></tr></thead>
            <tbody>
"""
        for v in f["ciso_vulnerabilities"]["details"]:
            sev_color = "#f87171" if v["severity"] in ("high", "critical") else "#fbbf24"
            html_content += f"""<tr>
                <td><span style="color:{sev_color}; font-weight:bold;">{v['severity'].upper()}</span></td>
                <td><code>{v['file_path']}:{v['line_number']}</code></td>
                <td>{v['description']}</td>
                <td><b>{v['status']}</b></td>
            </tr>"""

        html_content += """
            </tbody>
        </table>
    </div>

    <div class="card">
        <h3>🐞 QA Defects Gate</h3>
        <table>
            <thead><tr><th>File</th><th>Description</th><th>Status</th></tr></thead>
            <tbody>
"""
        for d in f["qa_defects"]["details"]:
            html_content += f"""<tr>
                <td><code>{d['file_path']}:{d['line_number']}</code></td>
                <td>{d['description']}</td>
                <td><b>{d['status']}</b></td>
            </tr>"""

        html_content += """
            </tbody>
        </table>
    </div>

    <div class="card">
        <h3>⏳ Handoff Timeline (Передача ответственности)</h3>
        <ul>
"""
        for h in f["handoff_timeline"]:
            html_content += f"<li><b>{h['timestamp']}</b>: <code>{h['sender']}</code> ➔ <code>{h['recipient']}</code> (Action: <i>{h['action']}</i>)</li>"

        html_content += """
        </ul>
    </div>
</body>
</html>
"""
        with open(output_path, "w", encoding="utf-8") as file:
            file.write(html_content)

        return {"status": "success", "message": f"Single Feature HTML report exported to: {output_path}", "output_path": output_path}

    def get_task_status(self, task_id):
        task_data = self.run_query("SELECT task_id, branch_name, current_phase, status FROM tasks WHERE task_id = ?;", (task_id,), fetch_all=False)
        if not task_data:
            return {"status": "error", "message": f"Task {task_id} not found."}
            
        defects_count = self.run_query("SELECT COUNT(*) FROM defects WHERE task_id = ? AND status = 'open';", (task_id,), fetch_all=False)[0]
        vulns_count = self.run_query("SELECT COUNT(*) FROM vulnerabilities WHERE task_id = ? AND status = 'open';", (task_id,), fetch_all=False)[0]
        
        return {
            "task_id": task_data[0],
            "branch_name": task_data[1],
            "current_phase": task_data[2],
            "status": task_data[3],
            "open_defects": defects_count,
            "open_vulnerabilities": vulns_count
        }

    def get_defects(self, task_id, status=None):
        if status:
            rows = self.run_query("SELECT id, file_path, line_number, description, status FROM defects WHERE task_id = ? AND status = ?;", (task_id, status))
        else:
            rows = self.run_query("SELECT id, file_path, line_number, description, status FROM defects WHERE task_id = ?;", (task_id,))
        return [{
            "id": r[0],
            "file_path": r[1],
            "line_number": r[2],
            "description": r[3],
            "status": r[4]
        } for r in rows]

    def get_vulnerabilities(self, task_id, status=None):
        if status:
            rows = self.run_query("SELECT id, severity, file_path, line_number, description, status FROM vulnerabilities WHERE task_id = ? AND status = ?;", (task_id, status))
        else:
            rows = self.run_query("SELECT id, severity, file_path, line_number, description, status FROM vulnerabilities WHERE task_id = ?;", (task_id,))
        return [{
            "id": r[0],
            "severity": r[1],
            "file_path": r[2],
            "line_number": r[3],
            "description": r[4],
            "status": r[5]
        } for r in rows]

    def get_handoffs(self, task_id):
        rows = self.run_query("SELECT id, sender, recipient, action, payload, timestamp FROM handoffs WHERE task_id = ? ORDER BY timestamp ASC;", (task_id,))
        return [{
            "id": r[0],
            "sender": r[1],
            "recipient": r[2],
            "action": r[3],
            "payload": json.loads(r[4]),
            "timestamp": r[5]
        } for r in rows]

    def set_artifact_dir(self, task_id, artifact_dir):
        """Save artifact directory for a task."""
        self.run_query(
            "INSERT INTO task_artifacts (task_id, artifact_dir) VALUES (?, ?) " 
            "ON CONFLICT(task_id) DO UPDATE SET artifact_dir = excluded.artifact_dir;",
            (task_id, artifact_dir),
            commit=True
        )
        return {"status": "success", "message": f"Artifact dir for {task_id}: {artifact_dir}"}

    def get_artifact_dir(self, task_id):
        """Get artifact directory for a task."""
        row = self.run_query(
            "SELECT artifact_dir FROM task_artifacts WHERE task_id = ?;",
            (task_id,),
            fetch_all=False
        )
        if row:
            return {"status": "success", "artifact_dir": row[0]}
        return {"status": "error", "message": f"No artifact dir for {task_id}"}

    def get_next_task_id(self):
        """Calculate the next free TASK-<N> identifier scanning SQLite DB and docs/active/ filesystem."""
        import re
        max_num = 0
        
        # 1. Scan DB tasks and task_artifacts
        rows_db = self.run_query("SELECT task_id FROM tasks UNION SELECT task_id FROM task_artifacts;")
        for r in rows_db:
            if r[0]:
                match = re.search(r'TASK[-_](\d+)', r[0], re.IGNORECASE)
                if match:
                    max_num = max(max_num, int(match.group(1)))
                    
        # 2. Scan active tasks filesystem dynamically
        db_dir = os.path.dirname(self.db_path)
        project_root = os.path.abspath(os.path.join(db_dir, ".."))
        try:
            from load_config import get_docs_path
            active_docs_dir = get_docs_path("active", project_root)
        except Exception:
            active_docs_dir = os.path.join(project_root, "docs", "active")
        
        if os.path.exists(active_docs_dir):
            for folder in os.listdir(active_docs_dir):
                match = re.search(r'TASK[-_](\d+)', folder, re.IGNORECASE)
                if match:
                    max_num = max(max_num, int(match.group(1)))
                    
        next_num = max_num + 1
        next_id = f"TASK-{next_num}"
        return {"status": "success", "next_task_id": next_id, "next_number": next_num}

    def get_analytics_stats(self):
        """Calculate SDLC performance, CISO security and defect analytics from SQLite database."""
        total_tasks = self.run_query("SELECT COUNT(*) FROM tasks;", fetch_all=False)[0]
        completed_tasks = self.run_query("SELECT COUNT(*) FROM tasks WHERE current_phase = 4 OR status = 'completed';", fetch_all=False)[0]
        open_defects = self.run_query("SELECT COUNT(*) FROM defects WHERE status = 'open';", fetch_all=False)[0]
        resolved_defects = self.run_query("SELECT COUNT(*) FROM defects WHERE status = 'resolved';", fetch_all=False)[0]
        open_vulnerabilities = self.run_query("SELECT COUNT(*) FROM vulnerabilities WHERE status = 'open';", fetch_all=False)[0]
        mitigated_vulnerabilities = self.run_query("SELECT COUNT(*) FROM vulnerabilities WHERE status = 'mitigated';", fetch_all=False)[0]
        total_handoffs = self.run_query("SELECT COUNT(*) FROM handoffs;", fetch_all=False)[0]
        
        # Hotspot files calculation
        hotspots = self.run_query("""
            SELECT file_path, COUNT(*) as cnt 
            FROM (
                SELECT file_path FROM defects 
                UNION ALL 
                SELECT file_path FROM vulnerabilities
            ) 
            GROUP BY file_path ORDER BY cnt DESC LIMIT 5;
        """)

        return {
            "status": "success",
            "sdlc_analytics": {
                "total_tasks": total_tasks,
                "completed_tasks": completed_tasks,
                "completion_rate_percent": round((completed_tasks / total_tasks * 100) if total_tasks > 0 else 100.0, 1),
                "open_defects": open_defects,
                "resolved_defects": resolved_defects,
                "open_vulnerabilities": open_vulnerabilities,
                "mitigated_vulnerabilities": mitigated_vulnerabilities,
                "total_agent_handoffs": total_handoffs,
                "hotspot_files": [{"file": h[0], "issues": h[1]} for h in hotspots]
            }
        }

    def export_html_dashboard(self, output_path=None):
        """Generate interactive Enterprise SDLC Analytics Dashboard with Drill-Down Explorer & Chart.js."""
        stats = self.get_analytics_stats()["sdlc_analytics"]
        
        # Fetch full tasks with defect and vuln counters
        raw_tasks = self.run_query("""
            SELECT t.task_id, t.branch_name, t.current_phase, t.status, t.updated_at,
                   (SELECT COUNT(*) FROM defects d WHERE d.task_id = t.task_id AND d.status = 'open') as open_def,
                   (SELECT COUNT(*) FROM defects d WHERE d.task_id = t.task_id AND d.status = 'resolved') as res_def,
                   (SELECT COUNT(*) FROM vulnerabilities v WHERE v.task_id = t.task_id AND v.status = 'open') as open_vuln,
                   (SELECT COUNT(*) FROM vulnerabilities v WHERE v.task_id = t.task_id AND v.status = 'mitigated') as mit_vuln
            FROM tasks t ORDER BY t.updated_at DESC;
        """)

        all_tasks_json = []
        for r in raw_tasks:
            t_id = r[0]
            defects = self.get_defects(t_id)
            vulns = self.get_vulnerabilities(t_id)
            handoffs = self.get_handoffs(t_id)
            art_dir = self.get_artifact_dir(t_id).get("artifact_dir", "N/A")
            
            all_tasks_json.append({
                "task_id": t_id,
                "branch_name": r[1],
                "current_phase": r[2],
                "status": r[3],
                "updated_at": r[4],
                "open_defects": r[5],
                "resolved_defects": r[6],
                "open_vulns": r[7],
                "mitigated_vulns": r[8],
                "defects_list": defects,
                "vulns_list": vulns,
                "handoffs_list": handoffs,
                "artifact_dir": art_dir
            })

        if not output_path:
            db_dir = os.path.dirname(self.db_path)
            output_path = os.path.join(db_dir, "reports", "sdlc_dashboard.html")
            
        os.makedirs(os.path.dirname(output_path), exist_ok=True)
        
        tasks_data_serialized = json.dumps(all_tasks_json, ensure_ascii=False)
        hotspots_serialized = json.dumps(stats["hotspot_files"], ensure_ascii=False)

        html_content = f"""<!DOCTYPE html>
<html lang="ru">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>NGPowers Enterprise SDLC Intelligence Explorer</title>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <style>
        :root {{
            --bg-color: #0f172a;
            --card-bg: #1e293b;
            --text-main: #f8fafc;
            --text-sub: #94a3b8;
            --accent-blue: #38bdf8;
            --accent-green: #4ade80;
            --accent-red: #f87171;
            --accent-purple: #c084fc;
            --accent-yellow: #fbbf24;
        }}
        body {{
            font-family: 'Segoe UI', system-ui, -apple-system, sans-serif;
            background-color: var(--bg-color);
            color: var(--text-main);
            margin: 0;
            padding: 24px;
        }}
        .header {{
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 24px;
            border-bottom: 1px solid #334155;
            padding-bottom: 16px;
        }}
        .header h1 {{ margin: 0; font-size: 24px; color: var(--accent-blue); }}
        .metrics-grid {{
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
            gap: 16px;
            margin-bottom: 24px;
        }}
        .card {{
            background-color: var(--card-bg);
            border-radius: 12px;
            padding: 16px;
            border: 1px solid #334155;
        }}
        .card-title {{ font-size: 13px; color: var(--text-sub); margin-bottom: 6px; }}
        .card-value {{ font-size: 28px; font-weight: bold; }}
        .val-blue {{ color: var(--accent-blue); }}
        .val-green {{ color: var(--accent-green); }}
        .val-red {{ color: var(--accent-red); }}
        .val-purple {{ color: var(--accent-purple); }}
        .val-yellow {{ color: var(--accent-yellow); }}
        .controls-bar {{
            display: flex;
            gap: 12px;
            margin-bottom: 20px;
            background: var(--card-bg);
            padding: 12px;
            border-radius: 8px;
            border: 1px solid #334155;
        }}
        .controls-bar input, .controls-bar select {{
            background: #0f172a;
            color: #fff;
            border: 1px solid #475569;
            padding: 8px 12px;
            border-radius: 6px;
            font-size: 14px;
        }}
        table {{
            width: 100%;
            border-collapse: collapse;
            background: var(--card-bg);
            border-radius: 8px;
            overflow: hidden;
            border: 1px solid #334155;
        }}
        th, td {{
            padding: 12px 16px;
            text-align: left;
            border-bottom: 1px solid #334155;
        }}
        th {{ background: #0f172a; color: var(--text-sub); font-size: 13px; }}
        tr:hover {{ background: #26334d; cursor: pointer; }}
        .badge {{
            padding: 4px 8px;
            border-radius: 4px;
            font-size: 12px;
            font-weight: bold;
        }}
        .badge-phase {{ background: #0284c7; color: #fff; }}
        .badge-open {{ background: #991b1b; color: #fca5a5; }}
        .badge-resolved {{ background: #166534; color: #86efac; }}
        
        /* Modal Styles */
        .modal-backdrop {{
            display: none;
            position: fixed;
            top: 0; left: 0; width: 100%; height: 100%;
            background: rgba(0,0,0,0.7);
            justify-content: center;
            align-items: center;
            z-index: 1000;
        }}
        .modal {{
            background: var(--card-bg);
            width: 80%;
            max-width: 900px;
            max-height: 85vh;
            border-radius: 12px;
            border: 1px solid #475569;
            display: flex;
            flex-direction: column;
            overflow: hidden;
        }}
        .modal-header {{
            padding: 16px 24px;
            background: #0f172a;
            border-bottom: 1px solid #334155;
            display: flex;
            justify-content: space-between;
            align-items: center;
        }}
        .modal-body {{
            padding: 24px;
            overflow-y: auto;
        }}
        .close-btn {{
            cursor: pointer;
            font-size: 20px;
            color: var(--text-sub);
        }}
        .tabs {{
            display: flex;
            gap: 12px;
            border-bottom: 1px solid #334155;
            margin-bottom: 16px;
        }}
        .tab {{
            padding: 8px 16px;
            cursor: pointer;
            color: var(--text-sub);
            border-bottom: 2px solid transparent;
        }}
        .tab.active {{
            color: var(--accent-blue);
            border-bottom-color: var(--accent-blue);
            font-weight: bold;
        }}
    </style>
</head>
<body>
    <div class="header">
        <h1>🚀 NGPowers Enterprise SDLC Intelligence Explorer</h1>
        <div style="color: var(--text-sub);">NGPowers SDLC Framework</div>
    </div>

    <div class="metrics-grid">
        <div class="card">
            <div class="card-title">Всего задач SDLC</div>
            <div class="card-value val-blue">{stats['total_tasks']}</div>
        </div>
        <div class="card">
            <div class="card-title">Завершено задач</div>
            <div class="card-value val-green">{stats['completed_tasks']} ({stats['completion_rate_percent']}%)</div>
        </div>
        <div class="card">
            <div class="card-title">Дефекты QA (Открытые / Закрытые)</div>
            <div class="card-value val-red">{stats['open_defects']} <span style="font-size: 20px; color: var(--accent-green);">/ {stats['resolved_defects']}</span></div>
        </div>
        <div class="card">
            <div class="card-title">CISO Уязвимости (Открытые / Смягченные)</div>
            <div class="card-value val-purple">{stats['open_vulnerabilities']} <span style="font-size: 20px; color: var(--accent-green);">/ {stats['mitigated_vulnerabilities']}</span></div>
        </div>
        <div class="card">
            <div class="card-title">Передачи Handoffs</div>
            <div class="card-value val-yellow">{stats['total_agent_handoffs']}</div>
        </div>
    </div>

    <div class="controls-bar">
        <input type="text" id="searchInput" placeholder="🔍 Поиск по ID задачи или ветке..." style="flex: 1;" onkeyup="filterTasks()">
        <select id="phaseFilter" onchange="filterTasks()">
            <option value="all">Все фазы (Phase 0-4)</option>
            <option value="0">Phase 0 (Purpose)</option>
            <option value="1">Phase 1 (PRD / Design)</option>
            <option value="2">Phase 2 (Plan)</option>
            <option value="3">Phase 3 (Coding)</option>
            <option value="4">Phase 4 (Verify / QA)</option>
        </select>
        <select id="statusFilter" onchange="filterTasks()">
            <option value="all">Все статусы</option>
            <option value="active">Active (В работе)</option>
            <option value="completed">Completed (Завершено)</option>
        </select>
    </div>

    <h2>📋 Реестр задач SDLC (Кликните для деталей)</h2>
    <table id="tasksTable">
        <thead>
            <tr>
                <th>Feature ID</th>
                <th>Git Branch</th>
                <th>Текущая Фаза</th>
                <th>Статус</th>
                <th>Дефекты QA</th>
                <th>CISO Уязвимости</th>
                <th>Действия</th>
            </tr>
        </thead>
        <tbody id="tasksTableBody">
            <!-- Dynamic rows -->
        </tbody>
    </table>

    <div style="margin-top: 32px;">
        <h2>🔥 Hotspot File Radar (Проблемные файлы кодовой базы)</h2>
        <table>
            <thead>
                <tr>
                    <th>Файл кодовой базы</th>
                    <th>Всего найдов QA & CISO</th>
                </tr>
            </thead>
            <tbody id="hotspotsTableBody">
                <!-- Dynamic hotspots -->
            </tbody>
        </table>
    </div>

    <!-- Modal for Task Details -->
    <div class="modal-backdrop" id="modalBackdrop">
        <div class="modal">
            <div class="modal-header">
                <h2 id="modalTitle" style="margin:0; font-size: 18px;">Детали задачи</h2>
                <span class="close-btn" onclick="closeModal()">&times;</span>
            </div>
            <div class="modal-body">
                <div class="tabs">
                    <div class="tab active" onclick="switchTab('timeline')">Handoff Timeline</div>
                    <div class="tab" onclick="switchTab('ciso')">CISO Security</div>
                    <div class="tab" onclick="switchTab('qa')">QA Defects</div>
                    <div class="tab" onclick="switchTab('artifacts')">Artifacts</div>
                </div>
                <div id="tabContent"></div>
            </div>
        </div>
    </div>

    <script>
        const allTasks = {tasks_data_serialized};
        const hotspots = {hotspots_serialized};
        let currentModalTask = null;
        let activeTab = 'timeline';

        function renderTable(tasks) {{
            const tbody = document.getElementById('tasksTableBody');
            tbody.innerHTML = '';
            tasks.forEach(t => {{
                const tr = document.createElement('tr');
                tr.onclick = () => openModal(t);
                tr.innerHTML = `
                    <td><strong>${{t.task_id}}</strong></td>
                    <td><code>${{t.branch_name}}</code></td>
                    <td><span class="badge badge-phase">Phase ${{t.current_phase}}</span></td>
                    <td>${{t.status === 'completed' ? '<span class="badge badge-resolved">Completed</span>' : '<span class="badge badge-phase">Active</span>'}}</td>
                    <td>${{t.open_defects > 0 ? `<span class="badge badge-open">${{t.open_defects}} Open</span>` : '<span style="color:#86efac;">0 Open</span>'}} (${{t.resolved_defects}} Fixed)</td>
                    <td>${{t.open_vulns > 0 ? `<span class="badge badge-open">${{t.open_vulns}} Open</span>` : '<span style="color:#86efac;">0 Open</span>'}} (${{t.mitigated_vulns}} Mitigated)</td>
                    <td><button style="background:#0284c7; color:#fff; border:none; padding:4px 8px; border-radius:4px; cursor:pointer;">Details</button></td>
                `;
                tbody.appendChild(tr);
            }});
        }}

        function renderHotspots() {{
            const tbody = document.getElementById('hotspotsTableBody');
            tbody.innerHTML = '';
            hotspots.forEach(h => {{
                const tr = document.createElement('tr');
                tr.innerHTML = `<td><code>${{h.file}}</code></td><td><strong style="color:#f87171;">${{h.issues}} уязвимостей/багов</strong></td>`;
                tbody.appendChild(tr);
            }});
        }}

        function filterTasks() {{
            const search = document.getElementById('searchInput').value.toLowerCase();
            const phase = document.getElementById('phaseFilter').value;
            const status = document.getElementById('statusFilter').value;

            const filtered = allTasks.filter(t => {{
                const matchSearch = t.task_id.toLowerCase().includes(search) || t.branch_name.toLowerCase().includes(search);
                const matchPhase = phase === 'all' || t.current_phase.toString() === phase;
                const matchStatus = status === 'all' || t.status === status;
                return matchSearch && matchPhase && matchStatus;
            }});

            renderTable(filtered);
        }}

        function openModal(task) {{
            currentModalTask = task;
            document.getElementById('modalTitle').innerText = `Детали задачи: ${{task.task_id}} (${{task.branch_name}})`;
            document.getElementById('modalBackdrop').style.display = 'flex';
            switchTab('timeline');
        }}

        function closeModal() {{
            document.getElementById('modalBackdrop').style.display = 'none';
        }}

        function switchTab(tabName) {{
            activeTab = tabName;
            document.querySelectorAll('.tab').forEach(t => t.classList.remove('active'));
            event.target.classList.add('active');

            const content = document.getElementById('tabContent');
            if (!currentModalTask) return;

            if (tabName === 'timeline') {{
                let html = '<h4>Лента передачи контекста между ИИ-агентами (Handoff Timeline)</h4><ul>';
                currentModalTask.handoffs_list.forEach(h => {{
                    html += `<li><strong>${{h.timestamp}}</strong>: <code>${{h.sender}}</code> ➔ <code>${{h.recipient}}</code> (Action: <i>${{h.action}}</i>)</li>`;
                }});
                html += '</ul>';
                content.innerHTML = html;
            }} else if (tabName === 'ciso') {{
                let html = '<h4>Статус CISO Уязвимостей Безопасности</h4>';
                if (currentModalTask.vulns_list.length === 0) {{
                    html += '<p style="color:#86efac;">Уязвимостей не обнаружено.</p>';
                }} else {{
                    html += '<ul>';
                    currentModalTask.vulns_list.forEach(v => {{
                        html += `<li><strong style="color:#f87171;">[${{v.severity.toUpperCase()}}]</strong> <code>${{v.file_path}}:${{v.line_number}}</code> - ${{v.description}} (Status: <b>${{v.status}}</b>)</li>`;
                    }});
                    html += '</ul>';
                }}
                content.innerHTML = html;
            }} else if (tabName === 'qa') {{
                let html = '<h4>Реестр дефектов QA</h4>';
                if (currentModalTask.defects_list.length === 0) {{
                    html += '<p style="color:#86efac;">Дефектов не найдено.</p>';
                }} else {{
                    html += '<ul>';
                    currentModalTask.defects_list.forEach(d => {{
                        html += `<li><code>${{d.file_path}}:${{d.line_number}}</code> - ${{d.description}} (Status: <b>${{d.status}}</b>)</li>`;
                    }});
                    html += '</ul>';
                }}
                content.innerHTML = html;
            }} else if (tabName === 'artifacts') {{
                content.innerHTML = `<h4>Артефакты задачи</h4><p>Директория артефактов: <code>${{currentModalTask.artifact_dir}}</code></p>`;
            }}
        }}

        // Initial Render
        renderTable(allTasks);
        renderHotspots();
    </script>
</body>
</html>
"""
        with open(output_path, "w", encoding="utf-8") as f:
            f.write(html_content)
            
        return {"status": "success", "message": f"Enterprise Dashboard exported to: {output_path}", "output_path": output_path}



def main():
    parser = argparse.ArgumentParser(description="Local State Manager")
    parser.add_argument("--db", default=None, help="Path to SQLite database file")
    
    subparsers = parser.add_subparsers(dest="command", required=True)
    
    p_init = subparsers.add_parser("init-task")
    p_init.add_argument("--task", required=True)
    p_init.add_argument("--branch", default=None)
    
    p_phase = subparsers.add_parser("update-phase")
    p_phase.add_argument("--task", required=True)
    p_phase.add_argument("--phase", type=int, required=True)
    
    p_hand = subparsers.add_parser("write-handoff")
    p_hand.add_argument("--task", required=True)
    p_hand.add_argument("--from-role", required=True)
    p_hand.add_argument("--to-role", required=True)
    p_hand.add_argument("--action", required=True)
    p_hand.add_argument("--payload", required=True)

    p_def = subparsers.add_parser("add-defect")
    p_def.add_argument("--task", required=True)
    p_def.add_argument("--file", required=True)
    p_def.add_argument("--line", type=int, required=True)
    p_def.add_argument("--desc", required=True)
    
    p_redef = subparsers.add_parser("resolve-defect")
    p_redef.add_argument("--defect-id", type=int, required=True)

    p_vuln = subparsers.add_parser("add-vulnerability")
    p_vuln.add_argument("--task", required=True)
    p_vuln.add_argument("--severity", choices=["high", "medium", "low"], default="medium")
    p_vuln.add_argument("--file", required=True)
    p_vuln.add_argument("--line", type=int, required=True)
    p_vuln.add_argument("--desc", required=True)
    
    p_stat = subparsers.add_parser("get-status")
    p_stat.add_argument("--task", required=True)
    
    p_getdef = subparsers.add_parser("get-defects")
    p_getdef.add_argument("--task", required=True)
    p_getdef.add_argument("--status", choices=["open", "resolved"])

    p_getvuln = subparsers.add_parser("get-vulnerabilities")
    p_getvuln.add_argument("--task", required=True)
    p_getvuln.add_argument("--status", choices=["open", "mitigated"])

    p_gethand = subparsers.add_parser("get-handoffs")
    p_gethand.add_argument("--task", required=True)

    p_artdir = subparsers.add_parser("set-artifact-dir")
    p_artdir.add_argument("--task", required=True)
    p_artdir.add_argument("--dir", required=True)

    p_getartdir = subparsers.add_parser("get-artifact-dir")
    p_getartdir.add_argument("--task", required=True)

    p_nexttask = subparsers.add_parser("get-next-task-id")
    
    p_report = subparsers.add_parser("report")
    p_report.add_argument("--task", required=True)
    
    p_featreport = subparsers.add_parser("export-feature-report")
    p_featreport.add_argument("--task", required=True)
    p_featreport.add_argument("--output", default=None, help="Output path for HTML report")
    
    p_mitvuln = subparsers.add_parser("mitigate-vulnerability")
    p_mitvuln.add_argument("--id", type=int, required=True)
    
    p_stats = subparsers.add_parser("stats")
    
    p_dash = subparsers.add_parser("export-dashboard")
    p_dash.add_argument("--output", default=None, help="Output path for HTML report")
    
    args = parser.parse_args()
    resolved_db = resolve_db_path(args.db)
    db = StateDB(resolved_db)
    
    try:
        if args.command == "init-task":
            branch = args.branch
            if not branch:
                try:
                    from load_config import get_target_branch
                    branch = get_target_branch()
                except Exception:
                    branch = "main"
            res = db.init_task(args.task, branch)
        elif args.command == "update-phase":
            res = db.update_phase(args.task, args.phase)
        elif args.command == "write-handoff":
            if os.path.exists(args.payload):
                with open(args.payload, 'r', encoding='utf-8') as f:
                    payload_data = json.load(f)
            else:
                payload_data = json.loads(args.payload)
            res = db.write_handoff(args.task, args.from_role, args.to_role, args.action, payload_data)
        elif args.command == "add-defect":
            res = db.add_defect(args.task, args.file, args.line, args.desc)
        elif args.command == "resolve-defect":
            res = db.resolve_defect(args.defect_id)
        elif args.command == "add-vulnerability":
            res = db.add_vulnerability(args.task, args.severity, args.file, args.line, args.desc)
        elif args.command == "mitigate-vulnerability":
            res = db.mitigate_vulnerability(args.id)
        elif args.command == "get-status":
            res = db.get_task_status(args.task)
        elif args.command == "get-defects":
            res = db.get_defects(args.task, args.status)
        elif args.command == "get-vulnerabilities":
            res = db.get_vulnerabilities(args.task, args.status)
        elif args.command == "get-handoffs":
            res = db.get_handoffs(args.task)
        elif args.command == "set-artifact-dir":
            res = db.set_artifact_dir(args.task, args.dir)
        elif args.command == "get-artifact-dir":
            res = db.get_artifact_dir(args.task)
        elif args.command == "get-next-task-id":
            res = db.get_next_task_id()
        elif args.command == "report":
            res = db.get_feature_report(args.task)
        elif args.command == "export-feature-report":
            res = db.export_feature_html_report(args.task, args.output)
        elif args.command == "stats":
            res = db.get_analytics_stats()
        elif args.command == "export-dashboard":
            res = db.export_html_dashboard(args.output)
            
        print(json.dumps(res, indent=2, ensure_ascii=False))
    except Exception as e:
        print(json.dumps({"status": "error", "message": str(e)}), file=sys.stderr)
        sys.exit(1)

if __name__ == "__main__":
    main()