"""(Basic) Message Tables/Models.

Models of the Traction tables for Messages (layer over AcaPy basic messaging).

"""
import uuid
from datetime import datetime
from typing import List, Optional

from sqlalchemy.orm import selectinload
from sqlmodel import Field, Relationship
from sqlalchemy import (
    Column,
    func,
    String,
    select,
    desc,
    text,
)
from sqlalchemy.dialects.postgresql import UUID, TIMESTAMP, ARRAY
from sqlmodel.ext.asyncio.session import AsyncSession

from api.db.models.base import BaseModel
from api.db.models.v1.contact import Contact

from api.endpoints.models.v1.errors import (
    NotFoundError,
)


class Message(BaseModel, table=True):
    """Message.

    Model for the Message table (postgresql specific dialects in use).
    This will track Messages for the Tenants (between contacts).

    Attributes:
      message_id: Traction ID for message OR when receiving, it is the AcaPy message_id
      tenant_id: Traction Tenant ID
      contact_id: Traction Contact ID
      status: Business and Tenant indicator for Credential state; independent of AcaPy
        Basic Message Exchange state
      role: sender or recipient
      deleted: Issuer Credential "soft" delete indicator.
      tags: Set by tenant for arbitrary grouping of Credentials
      content: actual content of the message
      state: The underlying AcaPy message exchange state
      sent_time: sent_time data in AcaPy payload
      created_at: Timestamp when record was created in Traction
      updated_at: Timestamp when record was last modified in Traction
    """

    message_id: uuid.UUID = Field(
        sa_column=Column(
            UUID(as_uuid=True),
            primary_key=True,
            server_default=text("gen_random_uuid()"),
        )
    )
    tenant_id: uuid.UUID = Field(foreign_key="tenant.id", index=True)
    contact_id: uuid.UUID = Field(foreign_key="contact.contact_id", index=True)
    status: str = Field(nullable=False)
    role: str = Field(nullable=False)
    deleted: bool = Field(nullable=False, default=False)
    tags: List[str] = Field(sa_column=Column(ARRAY(String)))
    content: str = 