from sqlalchemy import Column, Integer, String, DateTime, Text, Boolean, UniqueConstraint
from sqlalchemy.sql import func
from db import Base

class Post(Base):
    __tablename__ = "posts"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String(255), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

class MetaToken(Base):
    __tablename__ = "meta_tokens"

    id = Column(Integer, primary_key=True, index=True)
    access_token = Column(String(4096), nullable=False)
    token_type = Column(String(64), nullable=True)
    expires_in = Column(Integer, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

class MetaPageToken(Base):
    __tablename__ = "meta_page_tokens"

    id = Column(Integer, primary_key=True, index=True)
    page_id = Column(String(64), unique=True, nullable=False, index=True)
    access_token = Column(Text, nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

from sqlalchemy import Column, Integer, String, DateTime, Text
from sqlalchemy.sql import func
from db import Base

class Comment(Base):
    __tablename__ = "comments"

    id = Column(Integer, primary_key=True, index=True)

    platform = Column(String(32), nullable=False)
    platform_post_id = Column(String(128), nullable=False, index=True)  # IG media id

    comment_id = Column(String(128), nullable=False, index=True)  # unique id for this comment or reply
    parent_comment_id = Column(String(128), nullable=True, index=True)  # null for top level, set for replies
    is_reply = Column(Integer, nullable=False, default=0)  # 0 or 1

    username = Column(String(255), nullable=True)
    text = Column(Text, nullable=True)
    timestamp = Column(String(64), nullable=True)
    like_count = Column(Integer, nullable=True)

    created_at = Column(DateTime(timezone=True), server_default=func.now())

from sqlalchemy import Column, Integer, String, DateTime, Text, ForeignKey
from sqlalchemy.sql import func

class CanonicalPost(Base):
    __tablename__ = "canonical_posts"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String(512), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

class PlatformPost(Base):
    __tablename__ = "platform_posts"
    __table_args__ = (
        UniqueConstraint("platform", "platform_post_id", name="uq_platform_platform_post_id"),
    )

    id = Column(Integer, primary_key=True, index=True)
    canonical_post_id = Column(Integer, ForeignKey("canonical_posts.id"), nullable=False, index=True)

    platform = Column(String(32), nullable=False)  # instagram, facebook
    platform_post_id = Column(String(128), nullable=False, index=True)
    permalink = Column(String(1024), nullable=True)

    caption = Column(Text, nullable=True)
    created_time = Column(String(64), nullable=True)

    comment_count_api = Column(Integer, nullable=True)
    reaction_count_api = Column(Integer, nullable=True)
    share_count_api = Column(Integer, nullable=True)
    view_count_api = Column(Integer, nullable=True)

    match_confidence = Column(Integer, nullable=True)  # 0..100 when matched automatically

    created_at = Column(DateTime(timezone=True), server_default=func.now())

from sqlalchemy import UniqueConstraint

class MetricsSnapshot(Base):
    __tablename__ = "metrics_snapshots"

    id = Column(Integer, primary_key=True, index=True)
    canonical_post_id = Column(Integer, ForeignKey("canonical_posts.id"), nullable=False, index=True)

    platform = Column(String(32), nullable=False)
    platform_post_id = Column(String(128), nullable=False, index=True)

    captured_at = Column(DateTime(timezone=True), server_default=func.now())

    comment_count = Column(Integer, nullable=True)
    reaction_count = Column(Integer, nullable=True)
    share_count = Column(Integer, nullable=True)
    view_count = Column(Integer, nullable=True)

    raw_json = Column(Text, nullable=True)


class CommentAnalysis(Base):
    __tablename__ = "comment_analyses"
    __table_args__ = (
        UniqueConstraint("platform", "platform_post_id", "comment_id", name="uq_comment_analysis"),
    )

    id = Column(Integer, primary_key=True, index=True)

    platform = Column(String(32), nullable=False)
    platform_post_id = Column(String(128), nullable=False, index=True)

    comment_id = Column(String(128), nullable=False, index=True)
    parent_comment_id = Column(String(128), nullable=True, index=True)
    is_reply = Column(Integer, nullable=False, default=0)

    language = Column(String(16), nullable=True)

    sentiment_label = Column(String(16), nullable=True)
    sentiment_score = Column(Integer, nullable=True)  # 0..100

    emotion_primary = Column(String(32), nullable=True)
    emotion_confidence = Column(Integer, nullable=True)  # 0..100

    intent_type = Column(String(32), nullable=True)
    intent_confidence = Column(Integer, nullable=True)  # 0..100

    topics_json = Column(Text, nullable=True)  # JSON list

    toxicity_is_toxic = Column(Boolean, nullable=True)
    toxicity_level = Column(Integer, nullable=True)  # 0..100
    toxicity_manual_override = Column(Boolean, nullable=True)  # True = manually marked as toxic, False = manually marked as not toxic, None = no manual override

    purchase_is_signal = Column(Boolean, nullable=True)
    purchase_confidence = Column(Integer, nullable=True)  # 0..100

    summary = Column(Text, nullable=True)
    needs_context = Column(Boolean, nullable=True)
    flags_json = Column(Text, nullable=True)  # JSON list

    model = Column(String(64), nullable=True)
    prompt_version = Column(String(32), nullable=True)
    raw_json = Column(Text, nullable=True)

    created_at = Column(DateTime(timezone=True), server_default=func.now())


class AppSetting(Base):
    __tablename__ = "app_settings"

    key = Column(String(128), primary_key=True)
    value = Column(Text, nullable=True)
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())