backend/app/models.py

from sqlalchemy import Column, Integer, String, DateTime, Boolean, JSON, Text, ForeignKey
from sqlalchemy.orm import relationship
from datetime import datetime
from app.database import Base

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True)
    name = Column(String)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    integrations = relationship("Integration", back_populates="user")
    assets = relationship("Asset", back_populates="user")
    posts = relationship("Post", back_populates="user")

class Integration(Base):
    __tablename__ = "integrations"
    
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    provider = Column(String)  # 'linkedin' or 'canva'
    access_token = Column(Text)
    refresh_token = Column(Text)
    expires_at = Column(DateTime)
    account_info = Column(JSON)
    connected = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    user = relationship("User", back_populates="integrations")

class Asset(Base):
    __tablename__ = "assets"
    
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    name = Column(String)
    file_path = Column(String)
    file_type = Column(String)  # 'image', 'document', 'video'
    product_category = Column(String)  # 'ocr', 'p2p', 'o2c'
    sub_category = Column(String, nullable=True)
    size = Column(Integer)  # in bytes
    extra_metadata = Column(JSON, nullable=True)  # Renamed from 'metadata' to avoid SQLAlchemy conflict
    extracted_content = Column(JSON, nullable=True)  # OCR/extracted content from document parsing API
    analysis_status = Column(String, default="pending")  # 'pending', 'processing', 'completed', 'failed'
    analyzed_at = Column(DateTime, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    user = relationship("User", back_populates="assets")

class Post(Base):
    __tablename__ = "posts"
    
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    title = Column(String)
    content = Column(Text)
    post_type = Column(String)  # 'carousel', 'cover_content', 'content_only', 'webinar'
    product_category = Column(String)
    scheduled_date = Column(DateTime)
    status = Column(String)  # 'draft', 'scheduled', 'published', 'failed'
    linkedin_post_id = Column(String, nullable=True)
    canva_design_id = Column(String, nullable=True)
    assets = Column(JSON)  # List of asset IDs
    extra_metadata = Column(JSON, nullable=True)  # Renamed from 'metadata' to avoid SQLAlchemy conflict
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    user = relationship("User", back_populates="posts")

class Campaign(Base):
    __tablename__ = "campaigns"
    
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    name = Column(String)
    date_range_start = Column(DateTime)
    date_range_end = Column(DateTime)
    products = Column(JSON)  # List of product IDs
    post_types = Column(JSON)  # List of post type IDs
    posts_per_week = Column(Integer)
    status = Column(String)  # 'active', 'paused', 'completed'
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)