import uuid from datetime import datetime, timezone from sqlalchemy import ( Column, String, Integer, BigInteger, Boolean, Text, DateTime, ForeignKey, UniqueConstraint, JSON ) from sqlalchemy.orm import relationship from sqlalchemy.sql import func from app.database import Base, GUID class Channel(Base): __tablename__ = "channels" id = Column(GUID, primary_key=True, default=uuid.uuid4) telegram_handle = Column(String(128), unique=True, nullable=False, index=True) title = Column(String(255), nullable=True) niche = Column(String(64), nullable=False, index=True) subscriber_count = Column(Integer, default=0) is_active = Column(Boolean, default=True, nullable=False) last_scraped_at = Column(DateTime(timezone=True), nullable=True) created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False) raw_posts = relationship("RawPost", back_populates="channel", cascade="all, delete-orphan") class RawPost(Base): __tablename__ = "raw_posts" id = Column(GUID, primary_key=True, default=uuid.uuid4) channel_id = Column(GUID, ForeignKey("channels.id", ondelete="CASCADE"), nullable=False, index=True) message_id = Column(BigInteger, nullable=False) original_text = Column(Text, nullable=True) post_url = Column(Text, nullable=False) media_type = Column(String(32), default="none", nullable=False) media_url = Column(Text, nullable=True) views = Column(Integer, default=0, index=True) reactions = Column(Integer, default=0) posted_at = Column(DateTime(timezone=True), nullable=False, index=True) created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False) __table_args__ = ( UniqueConstraint("channel_id", "message_id", name="uq_channel_message"), ) channel = relationship("Channel", back_populates="raw_posts") drafts = relationship("ContentDraft", back_populates="raw_post", cascade="all, delete-orphan") class ContentDraft(Base): __tablename__ = "content_drafts" id = Column(GUID, primary_key=True, default=uuid.uuid4) raw_post_id = Column(GUID, ForeignKey("raw_posts.id", ondelete="CASCADE"), nullable=False, index=True) ai_generated_text = Column(Text, nullable=False) current_text = Column(Text, nullable=False) status = Column(String(32), default="pending", nullable=False, index=True) # pending, approved, edited, rejected, published brand_target = Column(String(64), default="@NetVibe_VPN", nullable=False) banner_image_url = Column(String(512), nullable=True) created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False, index=True) updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False) raw_post = relationship("RawPost", back_populates="drafts") feedback_logs = relationship("FeedbackLog", back_populates="draft", cascade="all, delete-orphan") queue_item = relationship("PublishQueue", back_populates="draft", uselist=False, cascade="all, delete-orphan") class FeedbackLog(Base): __tablename__ = "feedback_logs" id = Column(GUID, primary_key=True, default=uuid.uuid4) draft_id = Column(GUID, ForeignKey("content_drafts.id", ondelete="CASCADE"), nullable=False, index=True) original_ai_text = Column(Text, nullable=False) final_human_text = Column(Text, nullable=False) diff_analysis = Column(JSON, default=dict, nullable=False) rejection_reason = Column(Text, nullable=True) created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False, index=True) draft = relationship("ContentDraft", back_populates="feedback_logs") class PublishQueue(Base): __tablename__ = "publish_queue" id = Column(GUID(), primary_key=True, default=uuid.uuid4) draft_id = Column(GUID(), ForeignKey("content_drafts.id", ondelete="CASCADE"), nullable=False, unique=True) scheduled_time = Column(DateTime(timezone=True), nullable=False, default=lambda: datetime.now(timezone.utc), index=True) status = Column(String(32), default="queued", nullable=False, index=True) # queued, publishing, success, failed telegram_msg_id = Column(BigInteger, nullable=True) retry_count = Column(Integer, default=0, nullable=False) error_log = Column(Text, 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) draft = relationship("ContentDraft", back_populates="queue_item")