database.py

"""Database models and setup for Tickle.ro"""
import datetime
import uuid
from sqlalchemy import (
    Column, String, Integer, Float, DateTime, JSON, Boolean,
    ForeignKey, Text, create_engine, event
)
from sqlalchemy.orm import declarative_base, relationship, sessionmaker

Base = declarative_base()

def generate_uuid():
    return str(uuid.uuid4())

def utcnow():
    return datetime.datetime.now(datetime.timezone.utc)


class Brand(Base):
    __tablename__ = "brands"
    id = Column(String, primary_key=True, default=generate_uuid)
    name = Column(String, nullable=False)
    logo_url = Column(String)
    website = Column(String)
    primary_color = Column(String, default="#E31837")
    secondary_color = Column(String, default="#FFFFFF")
    contact_email = Column(String)
    created_at = Column(DateTime, default=utcnow)
    campaigns = relationship("Campaign", back_populates="brand")


class Campaign(Base):
    __tablename__ = "campaigns"
    id = Column(String, primary_key=True, default=generate_uuid)
    brand_id = Column(String, ForeignKey("brands.id"), nullable=False)
    name = Column(String, nullable=False)
    description = Column(Text)
    offer_title = Column(String, nullable=False)
    offer_details = Column(Text)
    offer_code = Column(String)
    fine_print = Column(Text)
    pass_type = Column(String, default="coupon")
    bg_color = Column(String, default="#E31837")
    fg_color = Column(String, default="#FFFFFF")
    barcode_format = Column(String, default="PKBarcodeFormatQR")
    starts_at = Column(DateTime)
    expires_at = Column(DateTime)
    is_active = Column(Boolean, default=True)
    locations = Column(JSON, default=list)
    push_enabled = Column(Boolean, default=True)
    push_schedule = Column(JSON, default=list)
    ad_formats = Column(JSON, default=lambda: ["scan"])
    goal = Column(String, default="offer_redemption")
    created_at = Column(DateTime, default=utcnow)
    updated_at = Column(DateTime, default=utcnow, onupdate=utcnow)
    brand = relationship("Brand", back_populates="campaigns")
    passes = relationship("WalletPass", back_populates="campaign")
    analytics = relationship("AnalyticsEvent", back_populates="campaign")


class WalletPass(Base):
    __tablename__ = "wallet_passes"
    id = Column(String, primary_key=True, default=generate_uuid)
    serial_number = Column(String, unique=True, index=True, default=generate_uuid)
    campaign_id = Column(String, ForeignKey("campaigns.id"), nullable=False)
    pass_platform = Column(String, default="apple")
    customer_id = Column(String, index=True)
    status = Column(String, default="active")
    auth_token = Column(String, default=generate_uuid)
    pass_data = Column(JSON)
    issued_at = Column(DateTime, default=utcnow)
    expires_at = Column(DateTime)
    last_updated = Column(DateTime, default=utcnow)
    campaign = relationship("Campaign", back_populates="passes")


class DeviceRegistration(Base):
    __tablename__ = "device_registrations"
    id = Column(String, primary_key=True, default=generate_uuid)
    device_library_id = Column(String, index=True)
    push_token = Column(String)
    pass_type_id = Column(String)
    serial_number = Column(String, ForeignKey("wallet_passes.serial_number"), index=True)
    registered_at = Column(DateTime, default=utcnow)


class AnalyticsEvent(Base):
    __tablename__ = "analytics_events"
    id = Column(String, primary_key=True, default=generate_uuid)
    campaign_id = Column(String, ForeignKey("campaigns.id"), index=True)
    serial_number = Column(String, index=True)
    event_type = Column(String, index=True)
    event_data = Column(JSON, default=dict)
    ip_address = Column(String)
    user_agent = Column(String)
    occurred_at = Column(DateTime, default=utcnow)
    campaign = relationship("Campaign", back_populates="analytics")


DATABASE_URL = "sqlite:///./tickle_ro.db"
engine = create_engine(DATABASE_URL, echo=False)

@event.listens_for(engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA journal_mode=WAL")
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

def init_db():
    Base.metadata.create_all(bind=engine)

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