"""Country Model.

This Country model uses sqlalchemy.
"""

from owsresponse import response
from sqlalchemy import Column, Integer, String

from account.connectors import mysql


class Country(mysql.BaseModel):
    """Country DB Model."""

    __tablename__ = 'country'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    country_code = Column(String)
    iso3166a3 = Column(String)


def get_countries():
    """Get list of countries from country table."""
    with mysql.session_scope(read_only=True) as session:
        result = session.query(Country.id, Country.name).order_by(Country.name).all()

        if result:
            return response.Response([r._asdict() for r in result])

    return response.create_not_found_response()


def get_country_id_by_code(country_code: str) -> int | None:
    """Get the country id for an ISO alpha-3 country code.

    Args:
        country_code (str): ISO alpha-3 country code.
    Returns:
        int | None: country id, or None if not found.
    """
    with mysql.session_scope(read_only=True) as session:
        result = session.query(Country.id).filter(Country.iso3166a3 == country_code.upper()).first()
        return result.id if result else None