"""Canned Response Note model."""

from datetime import datetime, timezone

from owsresponse import status as owsresponse_status
from sqlalchemy import and_, or_

from product_review.api import db
from product_review.constants.error import ERROR_CODE_NOT_FOUND, ERROR_MESSAGE_NOT_FOUND
from product_review.util.exception import raise_exception_for_ows_response


class CannedResponseNote(db.Model):
    """Canned Response Note model."""

    __tablename__ = "canned_response_note"

    note_id = db.Column(
        "id", db.Integer, primary_key=True, autoincrement=True, nullable=False
    )
    canned_response_id = db.Column(
        db.Integer, db.ForeignKey("canned_response.id"), nullable=False
    )
    note_keyword = db.Column(db.String(255), nullable=False)
    note_text = db.Column(db.String(255), nullable=False)
    language_code = db.Column(db.String(36), nullable=False)
    modified_by_user_id = db.Column(db.String(36), nullable=False)
    last_updated = db.Column(
        db.DateTime,
        nullable=False,
        server_default=db.func.current_timestamp(),
    )

    def to_dict(self):
        """Convert the CannedResponseNote object to a dictionary representation."""
        item = {}
        for c in self.__table__.columns:
            column_name = "note_id" if c.name == "id" else c.name
            item[column_name] = getattr(self, column_name)
        return item


def get_canned_response_note(note_id):
    """Get a record from canned_response_note table."""
    entry = db.session.get(CannedResponseNote, note_id)
    return entry.to_dict()


def create_canned_response_note(
    canned_response_id,
    modified_by_user_id,
    note_text,
    note_keyword,
    language_code,
    last_updated=None,
):
    """Add a record to the canned_response_note table."""
    db.session.add(
        CannedResponseNote(
            canned_response_id=canned_response_id,
            modified_by_user_id=modified_by_user_id,
            note_text=note_text,
            note_keyword=note_keyword,
            language_code=language_code,
            last_updated=last_updated or datetime.now(timezone.utc),
        )
    )


def update_canned_response_note(
    note_id, modified_by_user_id, note_text=None, note_keyword=None
):
    """Update a record in the canned_response_note table."""
    entry = db.session.get(CannedResponseNote, note_id)
    if not entry:
        raise_exception_for_ows_response(
            status=owsresponse_status.NOT_FOUND,
            code=ERROR_CODE_NOT_FOUND,
            message=ERROR_MESSAGE_NOT_FOUND,
        )
    if note_text:
        entry.note_text = note_text
    if note_keyword:
        entry.note_keyword = note_keyword
    entry.modified_by_user_id = modified_by_user_id
    entry.last_updated = datetime.now(timezone.utc)


def get_canned_response_notes(keys):
    """Get canned response notes."""
    canned_response_note_query = db.session.query(CannedResponseNote)
    canned_response_note_query = canned_response_note_query.filter(
        or_(
            and_(
                CannedResponseNote.canned_response_id == canned_response_id,
                CannedResponseNote.language_code == language_code,
            )
            for canned_response_id, language_code in keys
        )
    )
    return canned_response_note_query.all()