backend/app/database.py

from sqlalchemy import create_engine, Column, Integer, String, Text, DateTime, JSON
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime
import os
from pathlib import Path

# SQLite database path (Hugging Face Spaces provides persistent storage)
DATA_DIR = Path("/data")
DATA_DIR.mkdir(parents=True, exist_ok=True)
DB_PATH = os.getenv("HF_DATABASE_PATH", str(DATA_DIR / "emailout.db"))

engine = create_engine(f"sqlite:///{DB_PATH}", connect_args={"check_same_thread": False})
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()


class UploadedFile(Base):
    __tablename__ = "uploaded_files"
    
    id = Column(Integer, primary_key=True, index=True)
    file_id = Column(String, unique=True, index=True)
    filename = Column(String)
    contact_count = Column(Integer)
    file_path = Column(String)
    created_at = Column(DateTime, default=datetime.utcnow)


class Prompt(Base):
    __tablename__ = "prompts"
    
    id = Column(Integer, primary_key=True, index=True)
    file_id = Column(String, index=True)
    product_name = Column(String)
    prompt_template = Column(Text)
    created_at = Column(DateTime, default=datetime.utcnow)


class GeneratedSequence(Base):
    __tablename__ = "generated_sequences"
    
    id = Column(Integer, primary_key=True, index=True)
    file_id = Column(String, index=True)
    sequence_id = Column(Integer)  # Contact sequence number
    email_number = Column(Integer, default=1)  # Email number in sequence (1-10)
    first_name = Column(String)
    last_name = Column(String)
    email = Column(String)
    company = Column(String)
    title = Column(String)
    product = Column(String)
    subject = Column(String)
    email_content = Column(Text)
    created_at = Column(DateTime, default=datetime.utcnow)


class SmartleadRun(Base):
    __tablename__ = "smartlead_runs"
    
    id = Column(Integer, primary_key=True, index=True)
    file_id = Column(String, index=True)
    run_id = Column(String, unique=True, index=True)
    campaign_id = Column(String, index=True)
    campaign_name = Column(String)
    mode = Column(String)  # 'existing' or 'new'
    steps_count = Column(Integer)
    dry_run = Column(Integer, default=0)  # 0 = false, 1 = true
    total_leads = Column(Integer)
    added_leads = Column(Integer, default=0)
    skipped_leads = Column(Integer, default=0)
    failed_leads = Column(Integer, default=0)
    error_details = Column(Text)  # JSON string of errors
    status = Column(String)  # 'pending', 'completed', 'failed'
    created_at = Column(DateTime, default=datetime.utcnow)
    completed_at = Column(DateTime, nullable=True)


# Create tables
Base.metadata.create_all(bind=engine)


def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()