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)
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)
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")