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)
