# @File: backend/models/bizmail.py
# @Desc: SQLAlchemy ORM models for the Bizmail360 Reseller Manager module.
#        All mailboxes/domains live on the upstream SmarterMail server at
#        https://webmail.bizmail360.com; the tables here mirror local
#        ownership (which DoItEasy user / plan each domain belongs to) and
#        a lightweight cache of mailboxes for fast listing.
#
# Conventions:
#   - `created_at` / `updated_at` are managed by the DB (server_default=now()).
#   - The upstream system of record is SmarterMail — we only persist enough
#     to map a domain to its owning DoItEasy user and its pricing plan.
#   - Secret credentials (SmarterMail admin user/pass) live in
#     `api_hub_credentials` under provider_id="bizmail360_smartermail".
from __future__ import annotations

from core.database import Base
from sqlalchemy import (
    Boolean,
    Column,
    DateTime,
    Index,
    Integer,
    String,
    Text,
    UniqueConstraint,
)
from sqlalchemy.sql import func


class BizmailPlan(Base):
    """An admin-defined Bizmail360 hosting plan sold to clients."""

    __tablename__ = "bizmail_plans"
    __table_args__ = (
        UniqueConstraint("slug", name="uq_bizmail_plans_slug"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    slug = Column(String(64), nullable=False, index=True)
    name = Column(String(128), nullable=False)
    description = Column(Text, nullable=True)

    # Display pricing (the actual charge happens through the site's
    # unified checkout once it lands — this is authoritative for now).
    price_monthly = Column(String(32), nullable=True)
    price_yearly = Column(String(32), nullable=True)
    currency = Column(String(8), nullable=False, default="USD")

    # Quotas
    mailbox_quota = Column(Integer, nullable=False, default=5)
    storage_mb_per_mailbox = Column(Integer, nullable=False, default=1024)
    aliases_included = Column(Integer, nullable=False, default=20)

    # Feature bullets shown on the pricing card. Stored as newline-separated
    # plain text for portability; the UI splits on newlines.
    features = Column(Text, nullable=True)

    is_active = Column(Boolean, nullable=False, default=True)
    is_featured = Column(Boolean, nullable=False, default=False)
    sort_order = Column(Integer, nullable=False, default=0)

    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
    )


class BizmailSubscription(Base):
    """Ties a DoItEasy user to a Bizmail360 domain + plan."""

    __tablename__ = "bizmail_subscriptions"
    __table_args__ = (
        Index("ix_bizmail_subs_user_id", "user_id"),
        Index("ix_bizmail_subs_status", "status"),
        Index("ix_bizmail_subs_domain", "domain_name"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    user_id = Column(String(255), nullable=False)
    user_email = Column(String(255), nullable=True, index=True)

    plan_id = Column(Integer, nullable=False, index=True)

    domain_name = Column(String(255), nullable=False)
    # Mirrors the remote SmarterMail domain — when the domain is created
    # upstream, `provisioned_at` is set. Some SmarterMail deployments
    # don't expose a stable numeric id; we therefore key off the domain
    # name and only keep `smartermail_domain_ref` as a diagnostic hint.
    smartermail_domain_ref = Column(String(255), nullable=True)

    # pending | active | suspended | cancelled
    status = Column(String(16), nullable=False, default="pending")
    billing_cycle = Column(String(16), nullable=False, default="monthly")  # monthly | yearly

    # The primary domain admin (postmaster) mailbox username, e.g.
    # "admin@domain.tld". Set after provisioning succeeds.
    primary_mailbox = Column(String(255), nullable=True)

    provisioned_at = Column(DateTime(timezone=True), nullable=True)
    activated_at = Column(DateTime(timezone=True), nullable=True)
    suspended_at = Column(DateTime(timezone=True), nullable=True)
    cancelled_at = Column(DateTime(timezone=True), nullable=True)
    current_period_end = Column(DateTime(timezone=True), nullable=True)

    notes = Column(Text, nullable=True)

    # Cached counters — refreshed on every write path that touches the
    # upstream SmarterMail server.
    mailboxes_used = Column(Integer, nullable=False, default=0)
    aliases_used = Column(Integer, nullable=False, default=0)

    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
    )


class BizmailMailbox(Base):
    """Local mirror of a mailbox that lives on SmarterMail.

    `username` is the full address (e.g. "user@domain.tld"). Kept as a
    cache for fast listing; the upstream SmarterMail server is the
    authoritative source of truth.
    """

    __tablename__ = "bizmail_mailboxes"
    __table_args__ = (
        UniqueConstraint(
            "subscription_id", "username", name="uq_bizmail_mbox_sub_user"
        ),
        Index("ix_bizmail_mbox_sub", "subscription_id"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    subscription_id = Column(Integer, nullable=False)
    user_id = Column(String(255), nullable=False)

    username = Column(String(255), nullable=False)
    display_name = Column(String(255), nullable=True)
    is_domain_admin = Column(Boolean, nullable=False, default=False)
    disabled = Column(Boolean, nullable=False, default=False)
    storage_mb = Column(Integer, nullable=True)

    last_synced_at = Column(DateTime(timezone=True), nullable=True)

    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
    )


class BizmailAuditLog(Base):
    """One row per Bizmail360 mutation (client or admin)."""

    __tablename__ = "bizmail_audit_log"
    __table_args__ = (
        Index("ix_bizmail_audit_subscription", "subscription_id"),
        Index("ix_bizmail_audit_created", "created_at"),
        {"extend_existing": True},
    )

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    subscription_id = Column(Integer, nullable=True)
    actor_user_id = Column(String(255), nullable=True)
    actor_email = Column(String(255), nullable=True)
    action = Column(String(64), nullable=False)
    target = Column(String(255), nullable=True)
    status = Column(String(16), nullable=False, default="ok")  # ok | error
    payload = Column(Text, nullable=True)  # JSON blob (request / response excerpt)
    error_message = Column(Text, nullable=True)

    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)