"""SQLAlchemy models — full schema for BulkGmailMailer.

All OAuth secrets are stored encrypted (Fernet). Sensitive columns:
  oauth_tokens.access_token / refresh_token
  gmail_accounts.client_secret (per-account imported config)
"""
from datetime import datetime, timezone
from sqlalchemy import (JSON, Boolean, Column, DateTime, Enum, Float, ForeignKey,
                        Index, Integer, LargeBinary, String, Text, UniqueConstraint)
from sqlalchemy.orm import DeclarativeBase, relationship

from app.core import encryption


def utcnow():
    return datetime.now(timezone.utc)


class Base(DeclarativeBase):
    pass


class TimestampMixin:
    created_at = Column(DateTime(timezone=True), default=utcnow, nullable=False, index=True)
    updated_at = Column(DateTime(timezone=True), default=utcnow, onupdate=utcnow, nullable=False)


class User(Base, TimestampMixin):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    email = Column(String(320), unique=True, nullable=False, index=True)
    password_hash = Column(String(255), nullable=False)
    full_name = Column(String(200), default="")
    is_admin = Column(Boolean, default=False, nullable=False)
    is_active = Column(Boolean, default=True, nullable=False)
    last_login_at = Column(DateTime(timezone=True))

    gmail_accounts = relationship("GmailAccount", back_populates="user", cascade="all, delete-orphan")
    audit_logs = relationship("AuditLog", back_populates="user")


class OAuthClientConfig(Base, TimestampMixin):
    """An imported Google OAuth client configuration (from client_secret JSON).
    Secrets stored encrypted. Multiple configs may exist (e.g. per project)."""
    __tablename__ = "oauth_client_configs"
    id = Column(Integer, primary_key=True)
    name = Column(String(200), nullable=False)
    client_id = Column(String(300), nullable=False)
    client_secret_enc = Column(String(600), nullable=False)  # encrypted
    redirect_uri = Column(String(500), default="")
    project_id = Column(String(200), default="")
    is_active = Column(Boolean, default=True, nullable=False)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"))

    @property
    def client_secret(self) -> str:
        return encryption.decrypt(self.client_secret_enc)

    @client_secret.setter
    def client_secret(self, value: str) -> None:
        self.client_secret_enc = encryption.encrypt(value)


class GmailAccount(Base, TimestampMixin):
    __tablename__ = "gmail_accounts"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True)
    email = Column(String(320), nullable=False)
    display_name = Column(String(200), default="")
    status = Column(String(30), default="pending")  # pending|authorized|expired|revoked|error
    sending_method = Column(String(20), default="api")  # api|smtp
    auth_provider = Column(String(60), default="google")  # google|smtp_password
    google_user_id = Column(String(120), default="")
    # Encrypted plain SMTP password (Gmail "app password") for accounts added
    # via the "Bulk Add via SMTP" flow (auth_provider=smtp_password). Never
    # stored in plaintext, never echoed back, never logged. Not used for
    # OAuth ("google") accounts, which authenticate via oauth_tokens instead.
    smtp_password_enc = Column(String(600), default="")
    # Per-account config (administrator-configurable)
    daily_limit = Column(Integer, default=200)          # provider/configured daily cap
    campaign_limit = Column(Integer, default=200)       # per-account per-campaign cap
    sends_today = Column(Integer, default=0)
    last_send_date = Column(DateTime(timezone=True))
    cooldown_until = Column(DateTime(timezone=True))
    last_success_at = Column(DateTime(timezone=True))
    last_error = Column(Text, default="")
    error_count = Column(Integer, default=0)
    token_expiry = Column(DateTime(timezone=True))
    # Imported client config reference (optional; may use server-level config)
    oauth_config_id = Column(Integer, ForeignKey("oauth_client_configs.id", ondelete="SET NULL"))
    # Encrypted SMTP OAuth2 credentials mirror (kept in oauth_tokens; this is metadata only)
    is_active = Column(Boolean, default=True, nullable=False)
    revoked_by_user = Column(Boolean, default=False)
    revoked_at = Column(DateTime(timezone=True))

    user = relationship("User", back_populates="gmail_accounts")
    # Eager-loaded (joined) so async endpoints/workers can read account.oauth_token
    # without triggering a lazy-load IO (which raises MissingGreenlet in async sessions).
    oauth_token = relationship("OAuthToken", back_populates="account", uselist=False,
                               cascade="all, delete-orphan", lazy="joined")
    oauth_config = relationship("OAuthClientConfig")

    @property
    def smtp_password(self) -> str:
        """Decrypted SMTP app-password (only set for auth_provider=smtp_password)."""
        return encryption.decrypt(self.smtp_password_enc) if self.smtp_password_enc else ""

    @smtp_password.setter
    def smtp_password(self, value: str) -> None:
        self.smtp_password_enc = encryption.encrypt(value) if value else ""

    __table_args__ = (UniqueConstraint("user_id", "email", name="uq_gmail_account_user_email"),
                      Index("ix_gmail_status", "status"),)

    def usage_summary(self):
        return {"daily_limit": self.daily_limit, "sends_today": self.sends_today,
                "campaign_limit": self.campaign_limit,
                "last_success_at": self.last_success_at.isoformat() if self.last_success_at else None,
                "errors": self.error_count, "status": self.status}


class OAuthToken(Base, TimestampMixin):
    __tablename__ = "oauth_tokens"
    id = Column(Integer, primary_key=True)
    account_id = Column(Integer, ForeignKey("gmail_accounts.id", ondelete="CASCADE"), nullable=False, index=True)
    access_token_enc = Column(String(1200), nullable=False)  # encrypted
    refresh_token_enc = Column(String(1200), default="")     # encrypted; may be empty if not granted
    token_type = Column(String(50), default="Bearer")
    scope = Column(Text, default="")
    expires_at = Column(DateTime(timezone=True))
    granted_scopes = Column(Text, default="")

    account = relationship("GmailAccount", back_populates="oauth_token")

    @property
    def access_token(self) -> str:
        return encryption.decrypt(self.access_token_enc)

    @access_token.setter
    def access_token(self, value: str) -> None:
        self.access_token_enc = encryption.encrypt(value)

    @property
    def refresh_token(self) -> str:
        return encryption.decrypt(self.refresh_token_enc or "")

    @refresh_token.setter
    def refresh_token(self, value: str) -> None:
        self.refresh_token_enc = encryption.encrypt(value or "")

    def to_safe_dict(self):
        return {"id": self.id, "account_id": self.account_id, "token_type": self.token_type,
                "scope": self.scope, "expires_at": self.expires_at.isoformat() if self.expires_at else None,
                "has_refresh_token": bool(self.refresh_token_enc)}


class RecipientList(Base, TimestampMixin):
    __tablename__ = "recipient_lists"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True)
    name = Column(String(200), nullable=False)
    description = Column(Text, default="")
    source = Column(String(30), default="manual")  # manual|csv|txt
    row_count = Column(Integer, default=0)

    recipients = relationship("Recipient", back_populates="list", cascade="all, delete-orphan")


class Recipient(Base, TimestampMixin):
    __tablename__ = "recipients"
    id = Column(Integer, primary_key=True)
    list_id = Column(Integer, ForeignKey("recipient_lists.id", ondelete="CASCADE"), nullable=False, index=True)
    email = Column(String(320), nullable=False, index=True)
    first_name = Column(String(150), default="")
    last_name = Column(String(150), default="")
    company_name = Column(String(250), default="")
    company_phone = Column(String(60), default="")
    customer_email = Column(String(320), default="")
    custom_fields = Column(JSON, default=dict)
    # validation / status
    status = Column(String(30), default="valid")  # valid|invalid|duplicate|suppressed|bounced
    validation_error = Column(Text, default="")
    is_valid = Column(Boolean, default=True, nullable=False)
    # unsubscribe token (unique secret per recipient)
    unsub_token = Column(String(64), unique=True, nullable=False)

    list = relationship("RecipientList", back_populates="recipients")

    __table_args__ = (Index("ix_recipient_list_email", "list_id", "email"),)


class SuppressionEntry(Base, TimestampMixin):
    __tablename__ = "suppression_list"
    id = Column(Integer, primary_key=True)
    email = Column(String(320), unique=True, nullable=False, index=True)
    reason = Column(String(50), default="unsubscribed")  # unsubscribed|bounced|complaint|manual
    source = Column(String(200), default="")
    campaign_id = Column(Integer, ForeignKey("campaigns.id", ondelete="SET NULL"))
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"))


class SubjectLine(Base, TimestampMixin):
    __tablename__ = "subject_lines"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True)
    subject = Column(String(500), nullable=False)
    tags = Column(JSON, default=list)
    use_count = Column(Integer, default=0)


class EmailTemplate(Base, TimestampMixin):
    __tablename__ = "html_templates"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True)
    name = Column(String(250), nullable=False)
    subject_hint = Column(String(500), default="")
    body_html = Column(Text, nullable=False)
    body_text = Column(Text, default="")
    source = Column(String(20), default="paste")  # paste|upload
    is_active = Column(Boolean, default=True, nullable=False)
    sequence_order = Column(Integer, default=0)


class Campaign(Base, TimestampMixin):
    __tablename__ = "campaigns"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True)
    name = Column(String(250), nullable=False)
    status = Column(String(30), default="draft")  # draft|queued|running|paused|completed|cancelled|failed
    # selections
    recipient_list_id = Column(Integer, ForeignKey("recipient_lists.id", ondelete="SET NULL"))
    subject_set_ids = Column(JSON, default=list)   # list of subject line ids
    template_ids = Column(JSON, default=list)      # ordered template ids for sequencing
    message_type = Column(String(40), default="html")
    # attachment behaviour
    attach_pdf = Column(Boolean, default=False)
    attach_pdf_template_id = Column(Integer, ForeignKey("html_templates.id", ondelete="SET NULL"))
    attachment_id = Column(Integer, ForeignKey("attachments.id", ondelete="SET NULL"))
    attach_mode = Column(String(40), default="none")  # none|pdf|file|image
    # dynamic variables config
    variable_config = Column(JSON, default=dict)   # invoice date, due date, ranges etc.
    # rotation / limits
    account_ids = Column(JSON, default=list)
    rotation_strategy = Column(String(30), default="sequential")  # sequential|weighted
    rotation_weights = Column(JSON, default=dict)  # account_id -> weight
    subject_rotation = Column(String(30), default="round_robin")  # round_robin|random|fixed_first
    template_sequence_mode = Column(String(30), default="repeat")  # repeat|stop|random
    phone_selection = Column(String(30), default="round_robin")   # round_robin|random|fixed
    max_per_account = Column(Integer, default=200)
    send_interval_seconds = Column(Float, default=1.0)
    rate_per_minute = Column(Integer, default=60)
    max_retries = Column(Integer, default=3)
    retry_delay_seconds = Column(Integer, default=60)
    # counters
    total_recipients = Column(Integer, default=0)
    sent_count = Column(Integer, default=0)
    failed_count = Column(Integer, default=0)
    skipped_count = Column(Integer, default=0)
    suppressed_count = Column(Integer, default=0)
    bounced_count = Column(Integer, default=0)
    queued_count = Column(Integer, default=0)
    started_at = Column(DateTime(timezone=True))
    finished_at = Column(DateTime(timezone=True))
    preview_sample_size = Column(Integer, default=5)
    # compliance
    unsubscribe_enabled = Column(Boolean, default=True, nullable=False)

    recipient_list = relationship("RecipientList")
    send_jobs = relationship("SendJob", back_populates="campaign", cascade="all, delete-orphan")


class CampaignRecipient(Base, TimestampMixin):
    """Materialized campaign×recipient rows with generated values & audit info."""
    __tablename__ = "campaign_recipients"
    id = Column(Integer, primary_key=True)
    campaign_id = Column(Integer, ForeignKey("campaigns.id", ondelete="CASCADE"), nullable=False, index=True)
    recipient_id = Column(Integer, ForeignKey("recipients.id", ondelete="CASCADE"), nullable=False)
    email = Column(String(320), nullable=False, index=True)
    status = Column(String(30), default="pending")  # pending|queued|processing|sent|temp_failed|perm_failed|suppressed|skipped
    generated_invoice_number = Column(String(100), default="")
    generated_amount = Column(Float)
    generated_shipping = Column(Float)
    generated_subject_id = Column(Integer, ForeignKey("subject_lines.id", ondelete="SET NULL"))
    generated_template_id = Column(Integer, ForeignKey("html_templates.id", ondelete="SET NULL"))
    generated_phone = Column(String(60), default="")
    assigned_account_id = Column(Integer, ForeignKey("gmail_accounts.id", ondelete="SET NULL"))
    attempt_count = Column(Integer, default=0)
    last_error = Column(Text, default="")
    msg_id = Column(String(200), default="")       # Gmail message id
    thread_id = Column(String(200), default="")    # Gmail thread id
    sent_at = Column(DateTime(timezone=True))
    seq = Column(Integer, default=0)               # ordering within campaign

    campaign = relationship("Campaign")
    recipient = relationship("Recipient")
    subject = relationship("SubjectLine")
    template = relationship("EmailTemplate")
    account = relationship("GmailAccount")

    __table_args__ = (UniqueConstraint("campaign_id", "recipient_id", name="uq_campaign_recipient"),
                      Index("ix_campaign_recipient_status", "campaign_id", "status"),)


class Attachment(Base, TimestampMixin):
    __tablename__ = "attachments"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True)
    name = Column(String(300), nullable=False)
    filename = Column(String(300), nullable=False)
    stored_path = Column(String(500), nullable=False)  # relative path under uploads/
    mime_type = Column(String(120), default="application/octet-stream")
    size_bytes = Column(Integer, default=0)
    kind = Column(String(30), default="file")  # text|html|pdf|image|file
    description = Column(Text, default="")
    is_html_attachment = Column(Boolean, default=False)
    html_attachment_body = Column(Text, default="")   # for 'html' kind attachments (variable-substituted)
    html_source_template_id = Column(Integer, ForeignKey("html_templates.id", ondelete="SET NULL"))
    file_sha256 = Column(String(64), default="")

    user = relationship("User")


class CompanyPhoneNumber(Base, TimestampMixin):
    __tablename__ = "company_phone_numbers"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True)
    label = Column(String(150), default="")
    phone = Column(String(60), nullable=False)
    is_active = Column(Boolean, default=True, nullable=False)
    use_count = Column(Integer, default=0)


class SendJob(Base, TimestampMixin):
    __tablename__ = "send_jobs"
    id = Column(Integer, primary_key=True)
    campaign_id = Column(Integer, ForeignKey("campaigns.id", ondelete="CASCADE"), nullable=False, index=True)
    campaign_recipient_id = Column(Integer, ForeignKey("campaign_recipients.id", ondelete="CASCADE"), nullable=False, index=True)
    status = Column(String(30), default="queued")  # queued|processing|sent|failed|retrying|cancelled|dead
    attempts = Column(Integer, default=0)
    max_attempts = Column(Integer, default=3)
    retry_after = Column(DateTime(timezone=True))
    last_error = Column(Text, default="")
    locked_at = Column(DateTime(timezone=True))
    priority = Column(Integer, default=0)
    scheduled_at = Column(DateTime(timezone=True), default=utcnow, index=True)

    campaign = relationship("Campaign", back_populates="send_jobs")
    campaign_recipient = relationship("CampaignRecipient")


class SendAttempt(Base, TimestampMixin):
    __tablename__ = "send_attempts"
    id = Column(Integer, primary_key=True)
    send_job_id = Column(Integer, ForeignKey("send_jobs.id", ondelete="CASCADE"), nullable=False, index=True)
    campaign_id = Column(Integer, ForeignKey("campaigns.id", ondelete="CASCADE"), nullable=False, index=True)
    gmail_account_id = Column(Integer, ForeignKey("gmail_accounts.id", ondelete="SET NULL"))
    recipient_email = Column(String(320), nullable=False)
    subject = Column(String(500), default="")
    template_name = Column(String(250), default="")
    attachment_names = Column(JSON, default=list)
    method = Column(String(20), default="api")  # api|smtp
    outcome = Column(String(30), nullable=False)  # submitted|accepted|temp_failed|perm_failed|skipped
    provider_response = Column(Text, default="")   # sanitized provider response
    message_id = Column(String(200), default="")
    thread_id = Column(String(200), default="")
    retry_no = Column(Integer, default=0)
    failure_reason = Column(Text, default="")
    error_class = Column(String(60), default="")  # temporary|permanent|auth|quota|unknown

    send_job = relationship("SendJob")


class EmailEvent(Base, TimestampMixin):
    __tablename__ = "email_events"
    id = Column(Integer, primary_key=True)
    campaign_id = Column(Integer, ForeignKey("campaigns.id", ondelete="CASCADE"), index=True)
    campaign_recipient_id = Column(Integer, ForeignKey("campaign_recipients.id", ondelete="CASCADE"), index=True)
    gmail_account_id = Column(Integer, ForeignKey("gmail_accounts.id", ondelete="SET NULL"))
    event_type = Column(String(40), nullable=False)  # sent|failed|bounced|suppressed|unsubscribed|complained
    detail = Column(Text, default="")
    message_id = Column(String(200), default="")

    __table_args__ = (Index("ix_email_events_campaign", "campaign_id", "event_type"),)


class AuditLog(Base, TimestampMixin):
    __tablename__ = "audit_logs"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"), index=True)
    action = Column(String(120), nullable=False, index=True)
    entity = Column(String(60), default="")
    entity_id = Column(Integer)
    detail = Column(JSON, default=dict)
    ip_address = Column(String(64), default="")

    user = relationship("User")


class ApplicationSettings(Base, TimestampMixin):
    __tablename__ = "application_settings"
    id = Column(Integer, primary_key=True)
    key = Column(String(150), unique=True, nullable=False, index=True)
    value = Column(JSON, nullable=False)
    description = Column(String(300), default="")
    category = Column(String(60), default="general")