from __future__ import annotations

from datetime import date, datetime, timezone

from sqlalchemy import Boolean, Date, DateTime, ForeignKey, Integer, String, Text, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.core.db.common import CommonBase


class ServiceCategory(CommonBase):
    __tablename__ = "service_categories"
    __table_args__ = (
        UniqueConstraint("code", name="uq_service_categories_code"),
        UniqueConstraint("name", name="uq_service_categories_name"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    code: Mapped[str] = mapped_column(String(50), nullable=False, index=True)
    name: Mapped[str] = mapped_column(String(100), nullable=False, index=True)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=100)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)

    catalogue_items = relationship("ServiceCatalogue", back_populates="service_category")


class ServiceCatalogue(CommonBase):
    __tablename__ = "service_catalogues"
    __table_args__ = (
        UniqueConstraint("service_code", name="uq_service_catalogues_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    service_code: Mapped[str] = mapped_column(String(50), nullable=False, index=True)
    service_name: Mapped[str] = mapped_column(String(200), nullable=False, index=True)
    category: Mapped[str | None] = mapped_column(String(100), nullable=True)
    category_id: Mapped[int | None] = mapped_column(ForeignKey("service_categories.id"), nullable=True, index=True)
    recurrence_type: Mapped[str | None] = mapped_column(String(30), nullable=True, index=True)
    engagement_type: Mapped[str] = mapped_column(String(20), nullable=False, default="non_assurance", index=True)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=100)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)

    applicable_individual: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    applicable_proprietorship: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    applicable_partnership: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    applicable_llp: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    applicable_company: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    applicable_trust: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    applicable_society: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)

    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    is_client_requestable: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    is_consultant_requestable: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)

    created_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )
    updated_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
        nullable=False,
    )

    service_category = relationship("ServiceCategory", back_populates="catalogue_items")
    firm_services = relationship(
        "FirmServiceSelection",
        back_populates="catalogue",
        cascade="all, delete-orphan",
        passive_deletes=True,
    )
    task_templates = relationship(
        "FirmServiceTaskTemplate",
        back_populates="catalogue",
        cascade="all, delete-orphan",
        passive_deletes=True,
    )
    default_task_templates = relationship(
        "ServiceDefaultTaskTemplate",
        back_populates="catalogue",
        cascade="all, delete-orphan",
        passive_deletes=True,
        order_by="ServiceDefaultTaskTemplate.sequence_no.asc()",
    )

    due_date_rules = relationship(
        "ServiceDueDateRule",
        back_populates="catalogue",
        cascade="all, delete-orphan",
        passive_deletes=True,
        order_by="ServiceDueDateRule.sort_order.asc()",
    )


class ServiceDefaultTaskTemplate(CommonBase):
    __tablename__ = "service_default_task_templates"
    __table_args__ = (
        UniqueConstraint("service_catalogue_id", "sequence_no", name="uq_service_default_task_templates_sequence"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    service_catalogue_id: Mapped[int] = mapped_column(
        ForeignKey("service_catalogues.id", ondelete="CASCADE"), nullable=False, index=True
    )
    task_name: Mapped[str] = mapped_column(String(200), nullable=False)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    sequence_no: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
    default_role_name: Mapped[str | None] = mapped_column(String(100), nullable=True)
    is_mandatory: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    requires_review: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)

    catalogue = relationship("ServiceCatalogue", back_populates="default_task_templates")


class FirmServiceSelection(CommonBase):
    __tablename__ = "firm_service_selections"
    __table_args__ = (
        UniqueConstraint("tenant_id", "service_catalogue_id", name="uq_firm_service_selections_tenant_catalogue"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    service_catalogue_id: Mapped[int] = mapped_column(
        ForeignKey("service_catalogues.id", ondelete="CASCADE"), nullable=False, index=True
    )
    is_enabled: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    default_branch_id: Mapped[int | None] = mapped_column(ForeignKey("branches.id"), nullable=True, index=True)
    activated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
    activated_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )
    updated_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
        nullable=False,
    )

    catalogue = relationship("ServiceCatalogue", back_populates="firm_services")


class FirmServiceTaskTemplate(CommonBase):
    __tablename__ = "firm_service_task_templates"
    __table_args__ = (
        UniqueConstraint(
            "tenant_id", "service_catalogue_id", "sequence_no",
            name="uq_firm_service_task_templates_sequence",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    service_catalogue_id: Mapped[int] = mapped_column(
        ForeignKey("service_catalogues.id", ondelete="CASCADE"), nullable=False, index=True
    )

    task_name: Mapped[str] = mapped_column(String(200), nullable=False)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    sequence_no: Mapped[int] = mapped_column(Integer, nullable=False, default=1)

    default_role_name: Mapped[str | None] = mapped_column(String(100), nullable=True)
    is_mandatory: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    requires_review: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)

    created_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )
    updated_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
        nullable=False,
    )

    catalogue = relationship("ServiceCatalogue", back_populates="task_templates")
    document_requirements = relationship(
        "FirmTaskDocumentRequirement",
        back_populates="task_template",
        cascade="all, delete-orphan",
        passive_deletes=True,
        order_by="FirmTaskDocumentRequirement.sort_order.asc()",
    )
    document_templates = relationship(
        "FirmTaskDocumentTemplate",
        back_populates="task_template",
        cascade="all, delete-orphan",
        passive_deletes=True,
        order_by="FirmTaskDocumentTemplate.uploaded_at_utc.desc()",
    )


class FirmTaskDocumentRequirement(CommonBase):
    """Document required at service task-template level.

    These rows define what must/should be collected when engagement task
    instances are generated from a firm task template. Actual uploaded files are
    linked to ClientServiceTaskInstance through EngagementDocument.task_instance_id.
    """

    __tablename__ = "firm_task_document_requirements"
    __table_args__ = (
        UniqueConstraint(
            "tenant_id",
            "firm_task_template_id",
            "document_name",
            name="uq_firm_task_document_requirements_name",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    service_catalogue_id: Mapped[int] = mapped_column(ForeignKey("service_catalogues.id", ondelete="CASCADE"), nullable=False, index=True)
    firm_task_template_id: Mapped[int] = mapped_column(ForeignKey("firm_service_task_templates.id", ondelete="CASCADE"), nullable=False, index=True)

    document_name: Mapped[str] = mapped_column(String(200), nullable=False)
    document_type: Mapped[str] = mapped_column(String(80), nullable=False, default="GENERAL", index=True)
    is_mandatory: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    allowed_file_types: Mapped[str | None] = mapped_column(String(255), nullable=True)
    instructions: Mapped[str | None] = mapped_column(Text, nullable=True)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=100)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    updated_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc), nullable=False)

    task_template = relationship("FirmServiceTaskTemplate", back_populates="document_requirements")
    catalogue = relationship("ServiceCatalogue")


class FirmTaskDocumentTemplate(CommonBase):
    """Reusable uploaded template file attached to a firm task template.

    Example: GST registration NOC format, partnership deed format, agreement
    draft, company incorporation checklist, board resolution format etc.
    """

    __tablename__ = "firm_task_document_templates"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    service_catalogue_id: Mapped[int] = mapped_column(ForeignKey("service_catalogues.id", ondelete="CASCADE"), nullable=False, index=True)
    firm_task_template_id: Mapped[int] = mapped_column(ForeignKey("firm_service_task_templates.id", ondelete="CASCADE"), nullable=False, index=True)

    template_name: Mapped[str] = mapped_column(String(200), nullable=False)
    template_category: Mapped[str | None] = mapped_column(String(100), nullable=True, index=True)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    original_filename: Mapped[str] = mapped_column(String(255), nullable=False)
    stored_filename: Mapped[str] = mapped_column(String(255), nullable=False)
    content_type: Mapped[str | None] = mapped_column(String(150), nullable=True)
    file_size_bytes: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    local_relative_path: Mapped[str] = mapped_column(String(1000), nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False, index=True)

    uploaded_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
    uploaded_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)

    task_template = relationship("FirmServiceTaskTemplate", back_populates="document_templates")
    catalogue = relationship("ServiceCatalogue")


class ServiceDueDateRule(CommonBase):
    """Statutory/compliance due-date rule attached to a service catalogue item."""

    __tablename__ = "service_due_date_rules"
    __table_args__ = (
        UniqueConstraint("service_catalogue_id", "rule_name", name="uq_service_due_date_rules_catalogue_name"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    service_catalogue_id: Mapped[int] = mapped_column(
        ForeignKey("service_catalogues.id", ondelete="CASCADE"), nullable=False, index=True
    )
    rule_name: Mapped[str] = mapped_column(String(150), nullable=False)
    period_type: Mapped[str] = mapped_column(String(30), nullable=False, default="yearly", index=True)
    due_year_basis: Mapped[str] = mapped_column(String(40), nullable=False, default="assessment_year_start")
    due_day: Mapped[int | None] = mapped_column(Integer, nullable=True)
    due_month: Mapped[int | None] = mapped_column(Integer, nullable=True)
    due_month_offset: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    days_offset_after_event: Mapped[int | None] = mapped_column(Integer, nullable=True)
    renewal_days_before_expiry: Mapped[int | None] = mapped_column(Integer, nullable=True)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=100)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False, index=True)
    remarks: Mapped[str | None] = mapped_column(Text, nullable=True)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )
    updated_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
        nullable=False,
    )

    catalogue = relationship("ServiceCatalogue", back_populates="due_date_rules")


class ServiceDueDateExtension(CommonBase):
    """History of statutory due-date extensions for a service/rule/FY/period."""

    __tablename__ = "service_due_date_extensions"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    service_catalogue_id: Mapped[int] = mapped_column(
        ForeignKey("service_catalogues.id", ondelete="CASCADE"), nullable=False, index=True
    )
    due_date_rule_id: Mapped[int | None] = mapped_column(
        ForeignKey("service_due_date_rules.id", ondelete="SET NULL"), nullable=True, index=True
    )
    financial_year: Mapped[str] = mapped_column(String(9), nullable=False, index=True)
    assessment_year: Mapped[str | None] = mapped_column(String(9), nullable=True, index=True)
    period_label: Mapped[str | None] = mapped_column(String(30), nullable=True, index=True)
    previous_due_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    extended_due_date: Mapped[date] = mapped_column(Date, nullable=False, index=True)
    extension_sequence: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
    notification_reference: Mapped[str | None] = mapped_column(String(200), nullable=True)
    notification_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    remarks: Mapped[str | None] = mapped_column(Text, nullable=True)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )
    updated_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
        nullable=False,
    )

    catalogue = relationship("ServiceCatalogue")
    due_rule = relationship("ServiceDueDateRule")


class ClientServiceSubscription(CommonBase):
    """Firm-level subscription of an enabled service to a specific client."""

    __tablename__ = "client_service_subscriptions"
    __table_args__ = (
        UniqueConstraint(
            "tenant_id",
            "client_id",
            "service_catalogue_id",
            "financial_year",
            name="uq_client_service_subscription_tenant_client_service_year",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)

    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int | None] = mapped_column(ForeignKey("branches.id"), nullable=True, index=True)
    client_id: Mapped[int] = mapped_column(ForeignKey("clients.id", ondelete="CASCADE"), nullable=False, index=True)
    service_catalogue_id: Mapped[int] = mapped_column(ForeignKey("service_catalogues.id", ondelete="CASCADE"), nullable=False, index=True)
    firm_service_selection_id: Mapped[int | None] = mapped_column(ForeignKey("firm_service_selections.id", ondelete="SET NULL"), nullable=True, index=True)

    assigned_partner_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True, index=True)
    assigned_manager_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True, index=True)
    assigned_staff_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True, index=True)
    review_partner_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True, index=True)

    financial_year: Mapped[str] = mapped_column(String(9), nullable=False, default="2025-26", index=True)
    assessment_year: Mapped[str | None] = mapped_column(String(9), nullable=True, index=True)
    engagement_type: Mapped[str] = mapped_column(String(20), nullable=False, default="non_assurance", index=True)
    due_date_rule_id: Mapped[int | None] = mapped_column(ForeignKey("service_due_date_rules.id", ondelete="SET NULL"), nullable=True, index=True)
    original_due_date: Mapped[date | None] = mapped_column(Date, nullable=True, index=True)
    current_due_date: Mapped[date | None] = mapped_column(Date, nullable=True, index=True)
    due_date_source: Mapped[str | None] = mapped_column(String(30), nullable=True)
    expiry_date: Mapped[date | None] = mapped_column(Date, nullable=True, index=True)
    start_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    end_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    status: Mapped[str] = mapped_column(String(30), nullable=False, default="active", index=True)
    remarks: Mapped[str | None] = mapped_column(Text, nullable=True)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, index=True)
    is_locked: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, index=True)
    locked_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    locked_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    updated_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc), nullable=False)

    client = relationship("Client")
    catalogue = relationship("ServiceCatalogue")
    due_date_rule = relationship("ServiceDueDateRule", foreign_keys=[due_date_rule_id])
    firm_selection = relationship("FirmServiceSelection")
    assigned_partner = relationship("User", foreign_keys=[assigned_partner_user_id])
    assigned_manager = relationship("User", foreign_keys=[assigned_manager_user_id])
    assigned_staff = relationship("User", foreign_keys=[assigned_staff_user_id])
    review_partner = relationship("User", foreign_keys=[review_partner_user_id])
    locked_by = relationship("User", foreign_keys=[locked_by_user_id])


class ClientServiceTaskInstance(CommonBase):
    """Execution task generated from a firm service task template for a client-service subscription."""

    __tablename__ = "client_service_task_instances"
    __table_args__ = (
        UniqueConstraint(
            "subscription_id",
            "firm_task_template_id",
            "financial_year",
            name="uq_client_service_task_subscription_template_year",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)

    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int | None] = mapped_column(ForeignKey("branches.id"), nullable=True, index=True)
    subscription_id: Mapped[int] = mapped_column(ForeignKey("client_service_subscriptions.id", ondelete="CASCADE"), nullable=False, index=True)
    client_id: Mapped[int] = mapped_column(ForeignKey("clients.id", ondelete="CASCADE"), nullable=False, index=True)
    service_catalogue_id: Mapped[int] = mapped_column(ForeignKey("service_catalogues.id", ondelete="CASCADE"), nullable=False, index=True)
    firm_task_template_id: Mapped[int | None] = mapped_column(ForeignKey("firm_service_task_templates.id", ondelete="SET NULL"), nullable=True, index=True)

    financial_year: Mapped[str] = mapped_column(String(9), nullable=False, default="2025-26", index=True)
    assessment_year: Mapped[str | None] = mapped_column(String(9), nullable=True, index=True)
    task_name: Mapped[str] = mapped_column(String(200), nullable=False)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    sequence_no: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
    default_role_name: Mapped[str | None] = mapped_column(String(100), nullable=True)

    assigned_to_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True, index=True)
    internal_target_date: Mapped[date | None] = mapped_column(Date, nullable=True, index=True)
    status: Mapped[str] = mapped_column(String(30), nullable=False, default="pending", index=True)
    priority: Mapped[str] = mapped_column(String(20), nullable=False, default="normal")
    remarks: Mapped[str | None] = mapped_column(Text, nullable=True)

    started_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    completed_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, index=True)
    is_locked: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, index=True)
    locked_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    locked_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    updated_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc), nullable=False)

    subscription = relationship("ClientServiceSubscription")
    client = relationship("Client")
    catalogue = relationship("ServiceCatalogue")
    template = relationship("FirmServiceTaskTemplate")
    assigned_to = relationship("User", foreign_keys=[assigned_to_user_id])
    locked_by = relationship("User", foreign_keys=[locked_by_user_id])
    documents = relationship(
        "EngagementDocument",
        primaryjoin="ClientServiceTaskInstance.id == foreign(EngagementDocument.task_instance_id)",
        viewonly=True,
        order_by="EngagementDocument.updated_at_utc.desc()",
    )
    comments = relationship(
        "ServiceTaskComment",
        back_populates="task",
        cascade="all, delete-orphan",
        passive_deletes=True,
        order_by="ServiceTaskComment.created_at_utc.desc()",
    )


class ServiceTaskComment(CommonBase):
    """Communication timeline entry linked to a service task instance."""

    __tablename__ = "service_task_comments"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int | None] = mapped_column(ForeignKey("branches.id"), nullable=True, index=True)
    subscription_id: Mapped[int] = mapped_column(ForeignKey("client_service_subscriptions.id", ondelete="CASCADE"), nullable=False, index=True)
    task_instance_id: Mapped[int] = mapped_column(ForeignKey("client_service_task_instances.id", ondelete="CASCADE"), nullable=False, index=True)

    comment_type: Mapped[str] = mapped_column(String(40), nullable=False, default="internal_note", index=True)
    visibility: Mapped[str] = mapped_column(String(30), nullable=False, default="internal", index=True)
    message: Mapped[str] = mapped_column(Text, nullable=False)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), nullable=True, index=True)
    created_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    updated_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc), nullable=False)
    is_deleted: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, index=True)

    task = relationship("ClientServiceTaskInstance", back_populates="comments")
    subscription = relationship("ClientServiceSubscription")
    created_by = relationship("User", foreign_keys=[created_by_user_id])
