"""SQLModel table definitions for Ten31Portal."""

import enum
from datetime import date, datetime
from decimal import Decimal
from typing import Optional

from sqlmodel import Field, SQLModel, Column, String, JSON, UniqueConstraint


# --- Enums ---

class UserRole(str, enum.Enum):
    approver = "approver"
    cfo = "cfo"
    fund_admin = "fund_admin"
    viewer = "viewer"


class EntityType(str, enum.Enum):
    fund = "fund"
    spv = "spv"
    gp = "gp"
    mgmt_co = "mgmt_co"


class EntityStatus(str, enum.Enum):
    active = "active"
    closed = "closed"


class RoundStatus(str, enum.Enum):
    draft = "draft"
    submitted = "submitted"
    approved = "approved"
    returned = "returned"


# --- Tables ---

class User(SQLModel, table=True):
    __tablename__ = "users"

    id: int | None = Field(default=None, primary_key=True)
    name: str
    email: str = Field(sa_column=Column(String, unique=True, nullable=False))
    password_hash: str
    role: UserRole
    is_active: bool = Field(default=True)
    created_at: datetime = Field(default_factory=datetime.utcnow)


class Entity(SQLModel, table=True):
    __tablename__ = "entities"

    id: int | None = Field(default=None, primary_key=True)
    name: str
    type: EntityType
    vintage_year: int | None = None
    fund_size_cents: int | None = None
    status: EntityStatus = Field(default=EntityStatus.active)
    created_at: datetime = Field(default_factory=datetime.utcnow)


class Holding(SQLModel, table=True):
    __tablename__ = "holdings"

    id: int | None = Field(default=None, primary_key=True)
    entity_id: int = Field(foreign_key="entities.id")
    company_name: str
    created_at: datetime = Field(default_factory=datetime.utcnow)


class Position(SQLModel, table=True):
    __tablename__ = "positions"

    id: int | None = Field(default=None, primary_key=True)
    holding_id: int = Field(foreign_key="holdings.id")
    security_name: str
    investment_date: date
    shares: str | None = None  # Decimal stored as string
    cost_cents: int
    created_at: datetime = Field(default_factory=datetime.utcnow)


class ValuationRound(SQLModel, table=True):
    __tablename__ = "valuation_rounds"
    __table_args__ = (
        UniqueConstraint("entity_id", "quarter_end", name="uq_round_entity_quarter"),
    )

    id: int | None = Field(default=None, primary_key=True)
    entity_id: int = Field(foreign_key="entities.id")
    quarter_end: date
    status: RoundStatus = Field(default=RoundStatus.draft)
    submitted_by: int | None = Field(default=None, foreign_key="users.id")
    submitted_at: datetime | None = None
    approved_by: int | None = Field(default=None, foreign_key="users.id")
    approved_at: datetime | None = None
    return_note: str | None = None
    is_seed: bool = Field(default=False)
    created_at: datetime = Field(default_factory=datetime.utcnow)


class Valuation(SQLModel, table=True):
    __tablename__ = "valuations"
    __table_args__ = (
        UniqueConstraint("round_id", "position_id", name="uq_valuation_round_position"),
    )

    id: int | None = Field(default=None, primary_key=True)
    round_id: int = Field(foreign_key="valuation_rounds.id")
    position_id: int = Field(foreign_key="positions.id")
    value_cents: int
    created_at: datetime = Field(default_factory=datetime.utcnow)


class AuditLog(SQLModel, table=True):
    __tablename__ = "audit_log"

    id: int | None = Field(default=None, primary_key=True)
    actor_user_id: int | None = Field(default=None, foreign_key="users.id")
    action: str
    object_type: str
    object_id: int | None = None
    detail: str | None = Field(default=None, sa_column=Column(JSON, nullable=True))
    created_at: datetime = Field(default_factory=datetime.utcnow)