database.py

from sqlalchemy import create_engine, Column, Integer, String, DateTime, Boolean, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime

Base = declarative_base()

class Prospect(Base):
    __tablename__ = 'prospects'

    id = Column(Integer, primary_key=True)
    username = Column(String, unique=True, nullable=False)
    user_id = Column(String)
    dm_sent = Column(Boolean, default=False)
    dm_sent_at = Column(DateTime)
    message_sent = Column(Text)
    read_receipt = Column(Boolean, default=False)
    read_at = Column(DateTime)
    replied = Column(Boolean, default=False)
    replied_at = Column(DateTime)
    reply_message = Column(Text)
    status = Column(String, default='pending')  # pending, sent, read, replied, failed
    notes = Column(Text)
    created_at = Column(DateTime, default=datetime.utcnow)

class AccountHealth(Base):
    __tablename__ = 'account_health'

    id = Column(Integer, primary_key=True)
    timestamp = Column(DateTime, default=datetime.utcnow)
    dms_sent_today = Column(Integer, default=0)
    dms_sent_this_hour = Column(Integer, default=0)
    action_blocked = Column(Boolean, default=False)
    last_error = Column(Text)
    followers_count = Column(Integer)
    following_count = Column(Integer)

class Activity(Base):
    __tablename__ = 'activity'

    id = Column(Integer, primary_key=True)
    timestamp = Column(DateTime, default=datetime.utcnow)
    action = Column(String)  # dm_sent, read_detected, reply_received
    username = Column(String)
    details = Column(Text)
    success = Column(Boolean, default=True)

# Initialize database
engine = create_engine('sqlite:///instagram_outreach.db')
Base.metadata.create_all(engine)
SessionLocal = sessionmaker(bind=engine)

def get_db():
    db = SessionLocal()
    try:
        return db
    finally:
        pass

def close_db(db):
    db.close()