import datetime

from sqlalchemy import NullPool, create_engine, text
from sqlalchemy.orm import Session


class TrinoClient:
    def __init__(self, url: str) -> None:
        self.engine = create_engine(url, poolclass=NullPool)

    def get_nat_translation(
        self, ip: str, port: int, time_from: datetime.datetime, time_to: datetime.datetime
    ) -> list[str]:
        with Session(bind=self.engine) as session:
            return list(
                session.execute(
                    text(
                        """SELECT DISTINCT
                    iana__sourceipv4address,
                    COUNT(iana__sourceipv4address) OVER(PARTITION BY iana__sourceipv4address) as ct
                    FROM default.cz_muni_csirt_nsel_1_0
                    WHERE
                    (ts > from_unixtime(:time_from)) AND
                    (ts < from_unixtime(:time_to)) AND
                    (netflow__postnatsourceipv4address = :ip) AND
                    (netflow__postnaptsourcetransportport = :port)
                    ORDER BY ct DESC"""
                    ),
                    {"time_from": time_from.timestamp(), "time_to": time_to.timestamp(), "ip": ip, "port": int(port)},
                )
                .scalars()
                .all()
            )
