A R R R Associates
236 lines
16 KiB
Python
236 lines
16 KiB
Python
from __future__ import annotations
|
|
|
|
from datetime import datetime, timezone
|
|
|
|
from sqlalchemy import Boolean, DateTime, ForeignKey, Integer, String, Text, UniqueConstraint
|
|
from sqlalchemy.orm import Mapped, mapped_column
|
|
|
|
from app.core.db.common import CommonBase
|
|
|
|
|
|
class EmailSetting(CommonBase):
|
|
"""Firm/branch email configuration for outgoing SMTP and later IMAP use.
|
|
|
|
Phase 7S.1 uses SMTP for outgoing notifications. IMAP fields are included
|
|
now so Hostinger/Dovecot mailbox credentials can be stored once and reused
|
|
in Phase 7S.2 without changing the settings screen again.
|
|
"""
|
|
|
|
__tablename__ = "email_settings"
|
|
__table_args__ = (
|
|
UniqueConstraint("tenant_id", "branch_id", name="uq_email_settings_tenant_branch"),
|
|
)
|
|
|
|
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", ondelete="SET NULL"), nullable=True, index=True)
|
|
|
|
smtp_host: Mapped[str | None] = mapped_column(String(255), nullable=True)
|
|
smtp_port: Mapped[int | None] = mapped_column(Integer, nullable=True)
|
|
smtp_username: Mapped[str | None] = mapped_column(String(255), nullable=True)
|
|
smtp_password: Mapped[str | None] = mapped_column(String(500), nullable=True)
|
|
smtp_security: Mapped[str] = mapped_column(String(20), nullable=False, default="SSL") # SSL|STARTTLS|NONE
|
|
smtp_timeout_seconds: Mapped[int] = mapped_column(Integer, nullable=False, default=20)
|
|
|
|
from_email: Mapped[str | None] = mapped_column(String(255), nullable=True)
|
|
from_name: Mapped[str | None] = mapped_column(String(255), nullable=True)
|
|
reply_to_email: Mapped[str | None] = mapped_column(String(255), nullable=True)
|
|
|
|
imap_host: Mapped[str | None] = mapped_column(String(255), nullable=True)
|
|
imap_port: Mapped[int | None] = mapped_column(Integer, nullable=True)
|
|
imap_username: Mapped[str | None] = mapped_column(String(255), nullable=True)
|
|
imap_password: Mapped[str | None] = mapped_column(String(500), nullable=True)
|
|
imap_security: Mapped[str] = mapped_column(String(20), nullable=False, default="SSL") # SSL|STARTTLS|NONE
|
|
|
|
send_auth_emails: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
|
|
send_alert_emails: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
|
|
send_billing_emails: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
|
|
send_task_emails: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
|
|
|
|
# Phase 7S.1C - granular notification preferences. These are firm/branch
|
|
# level switches used by business-event email hooks. Authentication emails
|
|
# remain separately controlled by send_auth_emails and may still be forced
|
|
# for security-critical OTP flows.
|
|
send_invoice_emails: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
|
|
send_payment_emails: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
|
|
send_client_emails: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
|
|
send_document_emails: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
|
|
send_consultant_emails: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
|
|
send_partner_review_emails: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
|
|
send_leave_attendance_emails: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
|
|
send_online_payment_emails: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
|
|
|
|
is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, index=True)
|
|
|
|
created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
|
|
updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), 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)
|
|
|
|
|
|
class PlatformEmailSetting(CommonBase):
|
|
"""Platform-level SMTP settings controlled by System Admin.
|
|
|
|
Used before a tenant/firm is operational, for example Firm Creation
|
|
Wizard invites, platform password/reset notices and system security emails.
|
|
Firm/branch SMTP remains in EmailSetting and is not changed by this table.
|
|
"""
|
|
|
|
__tablename__ = "platform_email_settings"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
|
|
|
|
smtp_host: Mapped[str | None] = mapped_column(String(255), nullable=True)
|
|
smtp_port: Mapped[int | None] = mapped_column(Integer, nullable=True)
|
|
smtp_username: Mapped[str | None] = mapped_column(String(255), nullable=True)
|
|
smtp_password: Mapped[str | None] = mapped_column(String(500), nullable=True)
|
|
smtp_security: Mapped[str] = mapped_column(String(20), nullable=False, default="SSL") # SSL|STARTTLS|NONE
|
|
smtp_timeout_seconds: Mapped[int] = mapped_column(Integer, nullable=False, default=20)
|
|
|
|
from_email: Mapped[str | None] = mapped_column(String(255), nullable=True)
|
|
from_name: Mapped[str | None] = mapped_column(String(255), nullable=True)
|
|
reply_to_email: Mapped[str | None] = mapped_column(String(255), nullable=True)
|
|
|
|
send_auth_emails: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
|
|
is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, index=True)
|
|
|
|
created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
|
|
updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), 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)
|
|
|
|
|
|
class EmailTemplate(CommonBase):
|
|
__tablename__ = "email_templates"
|
|
__table_args__ = (
|
|
UniqueConstraint("tenant_id", "branch_id", "template_code", name="uq_email_templates_scope_code"),
|
|
)
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
|
|
tenant_id: Mapped[int | None] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=True, index=True)
|
|
branch_id: Mapped[int | None] = mapped_column(ForeignKey("branches.id", ondelete="SET NULL"), nullable=True, index=True)
|
|
|
|
template_code: Mapped[str] = mapped_column(String(80), nullable=False, index=True)
|
|
template_name: Mapped[str] = mapped_column(String(160), nullable=False)
|
|
subject_template: Mapped[str] = mapped_column(String(500), nullable=False)
|
|
body_template: Mapped[str] = mapped_column(Text, nullable=False)
|
|
is_html: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
|
|
is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=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)
|
|
|
|
|
|
class EmailLog(CommonBase):
|
|
__tablename__ = "email_logs"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
|
|
tenant_id: Mapped[int | None] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=True, index=True)
|
|
branch_id: Mapped[int | None] = mapped_column(ForeignKey("branches.id", ondelete="SET NULL"), nullable=True, index=True)
|
|
|
|
recipient_email: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
|
|
subject: Mapped[str] = mapped_column(String(500), nullable=False)
|
|
body: Mapped[str | None] = mapped_column(Text, nullable=True)
|
|
status: Mapped[str] = mapped_column(String(30), nullable=False, default="PENDING", index=True) # SENT|FAILED|SKIPPED
|
|
error_message: Mapped[str | None] = mapped_column(Text, nullable=True)
|
|
sent_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True, index=True)
|
|
|
|
related_module: Mapped[str | None] = mapped_column(String(80), nullable=True, index=True)
|
|
related_id: Mapped[int | None] = mapped_column(Integer, nullable=True, index=True)
|
|
template_code: Mapped[str | None] = mapped_column(String(80), nullable=True, index=True)
|
|
provider_message_id: Mapped[str | None] = mapped_column(String(255), nullable=True)
|
|
|
|
# Phase 7S.1E - queue/retry metadata. Existing callers may still attempt
|
|
# immediate sending, but failed/pending emails can now be retried safely.
|
|
attempt_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
|
|
max_attempts: Mapped[int] = mapped_column(Integer, nullable=False, default=3)
|
|
queue_priority: Mapped[int] = mapped_column(Integer, nullable=False, default=100, index=True)
|
|
is_retryable: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, index=True)
|
|
queued_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True, index=True)
|
|
next_retry_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True, index=True)
|
|
last_attempt_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True, index=True)
|
|
processing_started_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True, index=True)
|
|
|
|
created_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False, index=True)
|
|
|
|
|
|
class EmailIncomingMessage(CommonBase):
|
|
"""Incoming IMAP message fetched from the configured firm mailbox.
|
|
|
|
Phase 7S.2 stores metadata/body locally so replies can later be mapped to
|
|
clients, engagements, tasks, notices and invoices. Attachments are recorded
|
|
separately and are saved to a safe local email upload folder for now.
|
|
"""
|
|
|
|
__tablename__ = "email_incoming_messages"
|
|
__table_args__ = (
|
|
UniqueConstraint(
|
|
"tenant_id",
|
|
"branch_id",
|
|
"mailbox_email",
|
|
"folder_name",
|
|
"provider_uid",
|
|
name="uq_email_incoming_scope_mailbox_folder_uid",
|
|
),
|
|
)
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
|
|
tenant_id: Mapped[int | None] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=True, index=True)
|
|
branch_id: Mapped[int | None] = mapped_column(ForeignKey("branches.id", ondelete="SET NULL"), nullable=True, index=True)
|
|
|
|
mailbox_email: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
|
|
folder_name: Mapped[str] = mapped_column(String(120), nullable=False, default="INBOX", index=True)
|
|
provider_uid: Mapped[str] = mapped_column(String(120), nullable=False, index=True)
|
|
provider_message_id: Mapped[str | None] = mapped_column(String(500), nullable=True, index=True)
|
|
|
|
sender_email: Mapped[str | None] = mapped_column(String(255), nullable=True, index=True)
|
|
sender_name: Mapped[str | None] = mapped_column(String(255), nullable=True)
|
|
recipient_emails: Mapped[str | None] = mapped_column(Text, nullable=True)
|
|
cc_emails: Mapped[str | None] = mapped_column(Text, nullable=True)
|
|
subject: Mapped[str | None] = mapped_column(String(500), nullable=True, index=True)
|
|
body_text: Mapped[str | None] = mapped_column(Text, nullable=True)
|
|
body_html: Mapped[str | None] = mapped_column(Text, nullable=True)
|
|
raw_headers: Mapped[str | None] = mapped_column(Text, nullable=True)
|
|
received_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True, index=True)
|
|
|
|
status: Mapped[str] = mapped_column(String(30), nullable=False, default="NEW", index=True) # NEW|MATCHED|PROCESSED|ERROR
|
|
matched_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)
|
|
matched_client_id: Mapped[int | None] = mapped_column(ForeignKey("clients.id", ondelete="SET NULL"), nullable=True, index=True)
|
|
matched_consultant_id: Mapped[int | None] = mapped_column(ForeignKey("consultant_profiles.id", ondelete="SET NULL"), nullable=True, index=True)
|
|
related_module: Mapped[str | None] = mapped_column(String(80), nullable=True, index=True)
|
|
related_id: Mapped[int | None] = mapped_column(Integer, nullable=True, index=True)
|
|
|
|
# Phase 7S.3 - email-to-work mapping. Tracking codes in the subject/body
|
|
# such as [AF-ENG-123], [AF-TASK-123] and [AF-INV-123] are resolved to
|
|
# the relevant engagement/task/invoice while still preserving the generic
|
|
# related_module/related_id fields for older screens and future modules.
|
|
tracking_code: Mapped[str | None] = mapped_column(String(80), nullable=True, index=True)
|
|
matched_engagement_id: Mapped[int | None] = mapped_column(ForeignKey("client_service_subscriptions.id", ondelete="SET NULL"), nullable=True, index=True)
|
|
matched_task_id: Mapped[int | None] = mapped_column(ForeignKey("client_service_task_instances.id", ondelete="SET NULL"), nullable=True, index=True)
|
|
matched_invoice_id: Mapped[int | None] = mapped_column(ForeignKey("billing_invoices.id", ondelete="SET NULL"), nullable=True, index=True)
|
|
mapping_status: Mapped[str] = mapped_column(String(30), nullable=False, default="UNMAPPED", index=True) # UNMAPPED|AUTO_MAPPED|MANUAL_MAPPED|NO_MATCH|ERROR
|
|
mapping_notes: Mapped[str | None] = mapped_column(Text, nullable=True)
|
|
mapped_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True, index=True)
|
|
|
|
has_attachments: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, index=True)
|
|
attachment_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
|
|
error_message: Mapped[str | None] = mapped_column(Text, nullable=True)
|
|
fetched_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False, 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)
|
|
|
|
|
|
class EmailIncomingAttachment(CommonBase):
|
|
__tablename__ = "email_incoming_attachments"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
|
|
incoming_message_id: Mapped[int] = mapped_column(ForeignKey("email_incoming_messages.id", ondelete="CASCADE"), nullable=False, index=True)
|
|
tenant_id: Mapped[int | None] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=True, index=True)
|
|
branch_id: Mapped[int | None] = mapped_column(ForeignKey("branches.id", ondelete="SET NULL"), nullable=True, index=True)
|
|
|
|
filename: Mapped[str | None] = mapped_column(String(255), nullable=True)
|
|
content_type: Mapped[str | None] = mapped_column(String(120), nullable=True)
|
|
size_bytes: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
|
|
storage_path: Mapped[str | None] = mapped_column(String(1000), nullable=True)
|
|
created_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
|