from datetime import UTC, datetime
from sqlalchemy import BigInteger, Boolean, Column, DateTime, Float, ForeignKey, Integer, String, Text, JSON
from sqlalchemy.orm import declarative_base, relationship

Base = declarative_base()


def utc_now() -> datetime:
    return datetime.now(UTC).replace(tzinfo=None)


class Domain(Base):
    __tablename__ = "domains"
    id = Column(Integer, primary_key=True)
    name = Column(String(255), unique=True, nullable=False)
    is_active = Column(Boolean, default=True, nullable=False)
    max_users = Column(Integer, default=0)
    dkim_selector = Column(String(64), default="default")
    dkim_public_key = Column(Text)
    dkim_private_path = Column(String(512))
    dkim_previous_selector = Column(String(64))
    dkim_previous_public_key = Column(Text)
    dkim_previous_private_path = Column(String(512))
    dkim_rotated_at = Column(DateTime)
    dkim_previous_expires_at = Column(DateTime)
    dkim_auto_rotate = Column(Boolean, default=False, nullable=False)
    dkim_rotation_interval_days = Column(Integer, default=0, nullable=False)
    dkim_overlap_days = Column(Integer, default=0, nullable=False)
    dmarc_policy = Column(String(32), default="reject")
    dns_provider = Column(String(32))
    dns_account_id = Column(String(128))
    dns_zone_id = Column(String(128))
    dns_api_token = Column(Text)
    dns_sync_enabled = Column(Boolean, default=False, nullable=False)
    dns_last_sync_at = Column(DateTime)
    dns_last_sync_status = Column(String(512))
    created_at = Column(DateTime, default=utc_now)

    accounts = relationship("Account", back_populates="domain", cascade="all,delete")
    aliases = relationship("Alias", back_populates="domain", cascade="all,delete")
    redirects = relationship("Redirect", back_populates="domain", cascade="all,delete")

    @property
    def dns_has_api_token(self) -> bool:
        return bool(self.dns_api_token)

class Account(Base):
    __tablename__ = "accounts"
    id = Column(Integer, primary_key=True)
    domain_id = Column(Integer, ForeignKey("domains.id", ondelete="CASCADE"), nullable=False)
    local_part = Column(String(255), nullable=False)
    username = Column(String(320), unique=True, nullable=False)
    password_hash = Column(String(255), nullable=False)
    quota_mb = Column(Integer, nullable=False, default=2048)
    quota_bytes = Column(BigInteger, nullable=False, default=0)
    quota_messages = Column(BigInteger, nullable=False, default=0)
    # 0 means inherit the global service send limits.
    send_rate_burst = Column(Integer, nullable=False, default=0)
    send_rate_per_minute = Column(Integer, nullable=False, default=0)
    send_rate_per_hour = Column(Integer, nullable=False, default=0)
    send_rate_per_day = Column(Integer, nullable=False, default=0)
    send_rate_per_week = Column(Integer, nullable=False, default=0)
    send_rate_per_month = Column(Integer, nullable=False, default=0)
    home_server_id = Column(Integer, ForeignKey("cluster_servers.id", ondelete="SET NULL"), nullable=True)
    sync_version = Column(BigInteger, nullable=False, default=1)
    # When True, Dovecot passdb accepts only mailbox_app_passwords (not primary hash).
    require_app_password = Column(Boolean, default=False, nullable=False)
    # Soft flag: admin recommends app passwords / MFA for this mailbox.
    mfa_hint = Column(Boolean, default=False, nullable=False)
    is_active = Column(Boolean, default=True, nullable=False)
    created_at = Column(DateTime, default=utc_now)

    domain = relationship("Domain", back_populates="accounts")
    home_server = relationship("ClusterServer", foreign_keys=[home_server_id])
    app_passwords = relationship("MailboxAppPassword", back_populates="account", cascade="all,delete")


class ClusterServer(Base):
    __tablename__ = "cluster_servers"
    id = Column(Integer, primary_key=True)
    server_id = Column(String(64), unique=True, nullable=False)
    display_name = Column(String(128), nullable=False)
    hostname = Column(String(255), nullable=False)
    api_base_url = Column(String(512), nullable=False)
    smtp_host = Column(String(255), nullable=False)
    smtp_port = Column(Integer, nullable=False, default=25)
    lmtp_host = Column(String(255))
    lmtp_port = Column(Integer, nullable=False, default=24)
    is_local = Column(Boolean, default=False, nullable=False)
    is_active = Column(Boolean, default=True, nullable=False)
    role = Column(String(16), nullable=False, default="both")
    last_seen_at = Column(DateTime)
    created_at = Column(DateTime, default=utc_now)


class ClusterSyncQueue(Base):
    __tablename__ = "cluster_sync_queue"
    id = Column(Integer, primary_key=True)
    entity_type = Column(String(16), nullable=False)
    entity_key = Column(String(320), nullable=False)
    operation = Column(String(16), nullable=False)
    payload = Column(JSON, nullable=False)
    target_server_id = Column(String(64))
    attempts = Column(Integer, nullable=False, default=0)
    max_attempts = Column(Integer, nullable=False, default=10)
    status = Column(String(16), nullable=False, default="pending")
    last_error = Column(String(512))
    available_at = Column(DateTime, default=utc_now)
    created_at = Column(DateTime, default=utc_now)
    updated_at = Column(DateTime, default=utc_now, onupdate=utc_now)


class ReputationCheck(Base):
    __tablename__ = "reputation_checks"
    id = Column(Integer, primary_key=True)
    entity_type = Column(String(16), nullable=False)
    entity_value = Column(String(255), nullable=False)
    source = Column(String(64), nullable=False)
    listed = Column(Boolean, default=False, nullable=False)
    detail = Column(String(512))
    checked_at = Column(DateTime, default=utc_now)


class MailTrafficEvent(Base):
    """Privacy-safe traffic row: mailbox + size + time + direction only."""

    __tablename__ = "mail_traffic_events"
    id = Column(Integer, primary_key=True, autoincrement=True)
    direction = Column(String(16), nullable=False)  # inbound | outbound
    mailbox = Column(String(320), nullable=False)
    size_bytes = Column(BigInteger, nullable=False, default=0)
    event_at = Column(DateTime, nullable=False)
    event_key = Column(String(64), nullable=False, unique=True)
    source = Column(String(32), nullable=False, default="postfix")
    created_at = Column(DateTime, default=utc_now)


class MailStatsState(Base):
    __tablename__ = "mail_stats_state"
    id = Column(Integer, primary_key=True, default=1)
    last_journal_cursor = Column(String(255))
    last_run_at = Column(DateTime)
    last_status = Column(String(255))


class Alias(Base):
    __tablename__ = "aliases"
    id = Column(Integer, primary_key=True)
    domain_id = Column(Integer, ForeignKey("domains.id", ondelete="CASCADE"), nullable=False)
    source_local = Column(String(255), nullable=False)
    destination = Column(String(255), nullable=False)
    is_active = Column(Boolean, default=True, nullable=False)
    created_at = Column(DateTime, default=utc_now)

    domain = relationship("Domain", back_populates="aliases")

class Redirect(Base):
    __tablename__ = "redirects"
    id = Column(Integer, primary_key=True)
    domain_id = Column(Integer, ForeignKey("domains.id", ondelete="CASCADE"), nullable=False)
    source_local = Column(String(255), nullable=False)
    target_email = Column(String(255), nullable=False)
    is_active = Column(Boolean, default=True, nullable=False)
    created_at = Column(DateTime, default=utc_now)

    domain = relationship("Domain", back_populates="redirects")

class Reputation(Base):
    __tablename__ = "reputations"
    id = Column(Integer, primary_key=True)
    entity_type = Column(String(16), nullable=False)
    entity_value = Column(String(255), nullable=False)
    score = Column(Float, default=0)
    last_event = Column(JSON, nullable=True)
    updated_at = Column(DateTime, default=utc_now, onupdate=utc_now)

class Greylist(Base):
    __tablename__ = "greylist"
    id = Column(Integer, primary_key=True)
    ip = Column(String(64), nullable=False)
    sender = Column(String(255), nullable=False)
    rcpt = Column(String(255), nullable=False)
    passed = Column(Boolean, default=False)
    first_seen = Column(DateTime, default=utc_now)
    last_seen = Column(DateTime, default=utc_now)

class SpamTrap(Base):
    __tablename__ = "spam_traps"
    id = Column(Integer, primary_key=True)
    email = Column(String(255), nullable=False)
    note = Column(String(255), nullable=True)
    created_at = Column(DateTime, default=utc_now)

class TrustedReply(Base):
    __tablename__ = "trusted_replies"
    id = Column(Integer, primary_key=True)
    message_id = Column(String(255), nullable=False)
    from_email = Column(String(255), nullable=False)
    trust_score = Column(Float, default=0)
    seen_at = Column(DateTime, default=utc_now)

class BlocklistCache(Base):
    __tablename__ = "blocklists_cache"
    id = Column(Integer, primary_key=True)
    entity_type = Column(String(16), nullable=False)
    entity_value = Column(String(255), nullable=False)
    status = Column(String(16), nullable=False)
    source = Column(String(255), nullable=False)
    checked_at = Column(DateTime, default=utc_now)


class MfaAdmin(Base):
    """Singleton row (id=1) for panel/API administrator MFA."""

    __tablename__ = "mfa_admin"
    id = Column(Integer, primary_key=True, default=1)
    totp_enabled = Column(Boolean, default=False, nullable=False)
    totp_secret_enc = Column(Text)
    totp_confirmed_at = Column(DateTime)
    sms_enabled = Column(Boolean, default=False, nullable=False)
    sms_phone = Column(String(32))
    sms_provider = Column(String(32), nullable=False, default="crisaleo")
    updated_at = Column(DateTime, default=utc_now, onupdate=utc_now)


class MfaSmsProvider(Base):
    __tablename__ = "mfa_sms_providers"
    id = Column(Integer, primary_key=True)
    code = Column(String(32), unique=True, nullable=False)
    enabled = Column(Boolean, default=False, nullable=False)
    base_url = Column(String(512), nullable=False)
    client_id = Column(String(255))
    client_secret_enc = Column(Text)
    api_key_enc = Column(Text)
    send_path = Column(String(255), nullable=False, default="/api/v1/modules/sms/send")
    from_sender = Column(String(64))
    route = Column(String(32), nullable=False, default="internal")
    updated_at = Column(DateTime, default=utc_now, onupdate=utc_now)


class MailboxAppPassword(Base):
    """Per-mailbox app passwords for SMTP/IMAP/POP when interactive MFA is not possible."""

    __tablename__ = "mailbox_app_passwords"
    id = Column(Integer, primary_key=True)
    account_id = Column(Integer, ForeignKey("accounts.id", ondelete="CASCADE"), nullable=False)
    label = Column(String(128), nullable=False, default="client")
    password_hash = Column(String(255), nullable=False)
    created_at = Column(DateTime, default=utc_now)
    last_used_at = Column(DateTime)
    revoked_at = Column(DateTime)

    account = relationship("Account", back_populates="app_passwords")


class MailboxImportJob(Base):
    """Queued remote IMAP → local Maildir import job with live progress."""

    __tablename__ = "mailbox_import_jobs"
    id = Column(Integer, primary_key=True, autoincrement=True)
    destination_account_id = Column(Integer, ForeignKey("accounts.id", ondelete="CASCADE"), nullable=False)
    destination_username = Column(String(320), nullable=False)
    source_host = Column(String(255), nullable=False)
    source_port = Column(Integer, nullable=False, default=993)
    source_protocol = Column(String(16), nullable=False, default="imaps")
    source_username = Column(String(320), nullable=False)
    source_password_enc = Column(Text, nullable=False)
    source_auth_mode = Column(String(16), nullable=False, default="login")
    status = Column(String(16), nullable=False, default="pending")
    cancel_requested = Column(Boolean, default=False, nullable=False)
    progress_percent = Column(Float, nullable=False, default=0.0)
    bytes_total = Column(BigInteger, nullable=False, default=0)
    bytes_done = Column(BigInteger, nullable=False, default=0)
    messages_total = Column(BigInteger, nullable=False, default=0)
    messages_done = Column(BigInteger, nullable=False, default=0)
    folders_total = Column(Integer, nullable=False, default=0)
    folders_done = Column(Integer, nullable=False, default=0)
    current_folder = Column(String(512))
    speed_bps = Column(BigInteger, nullable=False, default=0)
    eta_seconds = Column(Integer)
    last_error = Column(String(1024))
    status_message = Column(String(512))
    resume_state = Column(JSON)
    created_at = Column(DateTime, default=utc_now)
    started_at = Column(DateTime)
    finished_at = Column(DateTime)
    updated_at = Column(DateTime, default=utc_now, onupdate=utc_now)

    destination_account = relationship("Account")