"""Statement Period model."""

from typing import Any

from abacus_common_logic.connectors.database import db
from abacus_common_logic.db.adapters import get_dialect
from abacus_common_logic.models import NormalizedDateTime
from sqlalchemy import Enum, select
from sqlalchemy.dialects.mysql import insert as mysql_insert
from sqlalchemy.dialects.sqlite import insert as sqlite_insert

from abacus_event.constants.constants import STATEMENT_PERIOD_STATUSES


class StatementPeriod(db.Model):
    """Statement Period model."""

    __tablename__ = 'statement_period'

    statement_period_id = db.Column(db.Integer, primary_key=True)
    statement_period_name = db.Column(db.String(180), nullable=False)
    statement_period_status = db.Column(
        Enum(
            *STATEMENT_PERIOD_STATUSES,
            name='statement_period_status',
            create_type=False,
        ),
        default=STATEMENT_PERIOD_STATUSES.OPEN,
        nullable=False,
    )
    statement_month = db.Column(db.SmallInteger, nullable=True)
    statement_year = db.Column(db.SmallInteger, nullable=True)
    closed_date = db.Column(NormalizedDateTime, nullable=True)
    closed_by = db.Column(db.String(180), nullable=True)

    @classmethod
    def get_current(cls) -> 'StatementPeriod | None':
        """Get the current statement period, or None if not found."""
        stmt = select(cls).where(cls.statement_period_status == 'current')
        return db.session.execute(stmt).scalars().first()

    @classmethod
    def upsert(cls, **kwargs: Any) -> None:
        """Insert or update a statement period."""
        if not kwargs:
            raise ValueError('kwargs cannot be empty')

        tbl = cls.__table__
        session = db.session

        # Build the upsert
        update_dict = dict()
        for key, value in kwargs.items():
            if key != 'statement_period_id':
                update_dict[key] = value

        if get_dialect(session) == 'sqlite':
            stmt = sqlite_insert(tbl).values(**kwargs)
            stmt = stmt.on_conflict_do_update(
                index_elements=['statement_period_id'], set_=update_dict
            )
        else:
            stmt = mysql_insert(tbl).values(**kwargs)
            stmt = stmt.on_duplicate_key_update(**update_dict)

        session.execute(stmt)