"""Governance Database Tables/Models.

Models of the Traction tables for Governance and related data. This includes ledger
related tables for schemas and credential definitions.

"""
import uuid
from datetime import datetime
from typing import List

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

from api.db.models.base import BaseModel

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


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

    This is the model for the Schema table (postgresql specific dialects in use).
    Schemas are registered on the ledger, so there can be only one...
    However, each Tenant can import the same schema for their own purposes. For now,
    there will be redundancy in the Schema data, we are going to wait on usage to
    determine if we need to normalize and have a singular table for Schemas for all and
    then join table for Schema/Tenant.


    There is already a table for v0 API named tenantschema and this is named differently
     to avoid confusion and interference.  When v0 is retired/deleted, perhaps we change
     the name then...

    For a given tenant, the schema can be found by the schema_template_id (Traction id)
    or schema_id (ledger id).

    Attributes:
      schema_template_id: Traction ID
      tenant_id: Traction Tenant ID, owner of this Contact
      schema_id: This will be the ledger schema id - this is not a UUID
      name: a "pretty" name for the schema, this can be different than the name on the
        ledger (schema_name).
      status: Status of the schema as it is being endorsed and registered
      tags: Set by tenant for arbitrary grouping of their Schemas
      deleted: Schema/Tenant "soft" delete indicator.
      imported: When True, this tenant imported the schema, otherwise they created it
      version: version, on ledger
      attributes: list of attribute names, on ledger
      schema_name: name as is appears on the ledger
      transaction_id: id used when schema is being endorsed and registered
      state: The underlying AcaPy endorser state
      created_at: Timestamp when record was created in Traction
      updated_at: Timestamp when record was last modified in Traction
    """

    __tablename__ = "schema_template"
    __table_args__ = (UniqueConstraint("tenant_id", "schema_id"),)
    schema_template_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)
    schema_id: str = Field(nullable=True, index=True)

    name: str = 