"""Subaccount Note model."""

from datetime import datetime, timezone

from product_review.api import db


class SubaccountNote(db.Model):
    """Subaccount Note model."""

    __tablename__ = "subaccount_note"

    subaccount_id = db.Column(db.Integer, primary_key=True, nullable=False)
    note = db.Column(db.String(1000), nullable=False)
    updated_timestamp = db.Column(
        db.DateTime,
        nullable=False,
        server_default=db.func.current_timestamp(),
    )
    updated_by = db.Column(db.String, nullable=False)

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


def get_items():
    """Get all subaccount_note entries."""
    return [item.to_dict() for item in SubaccountNote.query.all()]


def get_subaccount_note(subaccount_id):
    """Get a record from subacccount_note table."""
    entry = (
        db.session.query(SubaccountNote)
        .filter(SubaccountNote.subaccount_id == subaccount_id)
        .first()
    )
    if not entry:
        return None
    return entry.to_dict()


def get_subaccount_notes_by_subaccount_ids(subaccount_ids):
    """Get account notes by vendor ids."""
    return (
        db.session.query(SubaccountNote)
        .filter(SubaccountNote.subaccount_id.in_(subaccount_ids))
        .all()
    )


def create_subaccount_note(subaccount_id, note, updated_by):
    """Add a record to subaccount_note table."""
    new_entry = SubaccountNote(
        subaccount_id=subaccount_id,
        note=note,
        updated_by=updated_by,
        updated_timestamp=datetime.now(timezone.utc),
    )
    db.session.add(new_entry)


def update_subaccount_note(subaccount_id, note, updated_by):
    """Update a record in subaccount_note table."""
    entry = (
        db.session.query(SubaccountNote)
        .filter(SubaccountNote.subaccount_id == subaccount_id)
        .first()
    )
    entry.note = note
    entry.updated_by = updated_by
    entry.updated_timestamp = datetime.now(timezone.utc)