import json

import clickhouse_connect


class ClickHouseRepository:
    def __init__(self, host: str, port: int, username: str, password: str, database: str):
        self.host = host
        self.port = port
        self.username = username
        self.password = password
        self.database = database
        self.client = None

    def _get_client(self):
        if self.client is None:
            self.client = clickhouse_connect.get_client(
                host=self.host,
                port=self.port,
                username=self.username,
                password=self.password,
                database=self.database,
            )
        return self.client

    def insert_score_event(self, tenant_id: str, call_id: str, agent_id: str, quality_score: float, compliance_score: float, issues: list[str]):
        self._get_client().insert(
            'agent_score_events',
            [[tenant_id, call_id, agent_id, float(quality_score), float(compliance_score), json.dumps(issues)]],
            column_names=['tenant_id', 'call_id', 'agent_id', 'quality_score', 'compliance_score', 'issues_json'],
        )

    def get_agent_report(self, tenant_id: str, agent_id: str):
        rows = self._get_client().query(
            """
            SELECT
                count() AS total_calls_scored,
                avg(quality_score) AS avg_quality_score,
                avg(compliance_score) AS compliance_rate
            FROM agent_score_events
            WHERE tenant_id = %(tenant_id)s AND agent_id = %(agent_id)s
            """,
            parameters={'tenant_id': tenant_id, 'agent_id': agent_id},
        ).result_rows

        if not rows:
            return 0, 0.0, 0.0
        total, avg_q, comp = rows[0]
        return int(total), float(avg_q or 0.0), float(comp or 0.0)

    def get_recurring_issues(self, tenant_id: str, agent_id: str, limit: int = 10):
        rows = self._get_client().query(
            """
            SELECT issues_json
            FROM agent_score_events
            WHERE tenant_id = %(tenant_id)s AND agent_id = %(agent_id)s
            ORDER BY event_time DESC
            LIMIT 200
            """,
            parameters={'tenant_id': tenant_id, 'agent_id': agent_id},
        ).result_rows

        freq: dict[str, int] = {}
        for (issues_json,) in rows:
            try:
                issues = json.loads(issues_json)
            except Exception:
                issues = []
            for issue in issues:
                key = str(issue).strip().lower()
                if not key:
                    continue
                freq[key] = freq.get(key, 0) + 1

        ordered = sorted(freq.items(), key=lambda x: x[1], reverse=True)
        return [item[0] for item in ordered[:limit]]