"""PostgreSQL reads for the durable Agent registry."""

import psycopg
from psycopg.rows import dict_row

from app.config import settings


def default_connection_factory():
    return psycopg.connect(
        host=settings.postgres_host,
        port=settings.postgres_port,
        dbname=settings.postgres_db,
        user=settings.postgres_user,
        password=settings.postgres_password,
        row_factory=dict_row,
    )


class AgentRegistryRepository:
    def __init__(self, connection_factory=default_connection_factory):
        self.connection_factory = connection_factory

    def list_registry(self) -> dict[str, list[dict]]:
        connection = self.connection_factory()

        try:
            with connection.cursor() as cur:
                cur.execute(
                    """
                    SELECT
                        site.id,
                        site.name
                    FROM sites AS site
                    WHERE EXISTS (
                        SELECT 1
                        FROM agent_zones AS zone
                        WHERE zone.site_id = site.id
                    )
                    ORDER BY lower(site.name), site.id
                    """
                )
                sites = cur.fetchall()

                cur.execute(
                    """
                    SELECT
                        zone.id,
                        zone.site_id,
                        site.name AS site_name,
                        zone.name,
                        zone.description,
                        zone.is_enabled,
                        assigned.agent_id AS assigned_agent_id,
                        COALESCE(
                            array_agg(
                                network.network::text
                                ORDER BY network.network::text
                            ) FILTER (WHERE network.id IS NOT NULL),
                            ARRAY[]::text[]
                        ) AS networks
                    FROM agent_zones AS zone
                    JOIN sites AS site
                      ON site.id = zone.site_id
                    LEFT JOIN minerops_site_agents AS assigned
                      ON assigned.agent_zone_id = zone.id
                    LEFT JOIN agent_zone_network_ranges AS network
                      ON network.agent_zone_id = zone.id
                    GROUP BY
                        zone.id,
                        zone.site_id,
                        site.name,
                        zone.name,
                        zone.description,
                        zone.is_enabled,
                        assigned.agent_id
                    ORDER BY
                        lower(site.name),
                        lower(zone.name),
                        zone.id
                    """
                )
                zones = cur.fetchall()

                cur.execute(
                    """
                    SELECT
                        agent.agent_id,
                        agent.display_name,
                        agent.site_id,
                        COALESCE(site.name, agent.site_name) AS site_name,
                        agent.agent_zone_id,
                        zone.name AS agent_zone_name,
                        zone.is_enabled AS agent_zone_enabled,
                        agent.hostname,
                        agent.version,
                        agent.ip_address,
                        agent.last_checkin_at,
                        agent.credential_created_at,
                        agent.credential_revoked_at,
                        CASE
                            WHEN agent.credential_revoked_at IS NOT NULL
                                THEN 'revoked'
                            WHEN agent.agent_secret_hash IS NOT NULL
                                THEN 'active'
                            WHEN retirement.retired_at IS NOT NULL
                                THEN 'retired'
                            ELSE 'legacy'
                        END AS credential_state
                    FROM minerops_site_agents AS agent
                    LEFT JOIN agent_legacy_auth_retirements AS retirement
                      ON retirement.agent_id = agent.agent_id
                    LEFT JOIN sites AS site
                      ON site.id = agent.site_id
                    LEFT JOIN agent_zones AS zone
                      ON zone.id = agent.agent_zone_id
                    ORDER BY
                        lower(COALESCE(site.name, agent.site_name)),
                        lower(COALESCE(agent.display_name, agent.agent_id)),
                        agent.agent_id
                    """
                )
                agents = cur.fetchall()

            return {
                "sites": sites,
                "zones": zones,
                "agents": agents,
            }
        finally:
            connection.close()