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