mydatascraper/competitor_compare

1from sqlalchemy import (
2    Column, String, Float, Integer, DateTime, Boolean,
3    ForeignKey, Index, Text, Enum as SAEnum, UniqueConstraint,
4    BigInteger
5)
6from sqlalchemy.orm import relationship
7from sqlalchemy.sql import func
8from app.database import Base
9import enum
10from datetime import datetime
11
12
13class RetailerEnum(str, enum.Enum):
14    WALMART = "walmart"
15    ALDI = "aldi"
16    TARGET = "target"
17    WEGMANS = "wegmans"
18    # Phase 2 chains
19    SAMS_CLUB = "sams_club"
20    COSTCO = "costco"
21    LIDL = "lidl"
22    TRADER_JOES = "trader_joes"
23    WHOLE_FOODS = "whole_foods"
24    GIANT_EAGLE = "giant_eagle"
25    GIANT_MARTINS = "giant_martins"
26    WEIS = "weis"
27    SHOPRITE = "shoprite"
28    ACME = "acme"
29
30
31class CategoryEnum(str, enum.Enum):
32    PRODUCE = "produce"
33    DAIRY = "dairy"
34    MEAT = "meat"
35    PANTRY = "pantry"
36    HOUSEHOLD = "household"
37    PERSONAL_CARE = "personal_care"
38    BABY = "baby"
39
40
41class Store(Base):
42    __tablename__ = "stores"
43
44    id = Column(Integer, primary_key=True, autoincrement=True)
45    retailer = Column(String(50), nullable=False, index=True)
46    store_id = Column(String(50), nullable=False)  # Retailer's own store ID
47    name = Column(String(255), nullable=False)
48    address = Column(String(500))
49    city = Column(String(100))
50    state = Column(String(2), default="PA")
51    zip_code = Column(String(10))
52    latitude = Column(Float)
53    longitude = Column(Float)
54    is_active = Column(Boolean, default=True)
55    created_at = Column(DateTime, server_default=func.now())
56    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())
57
58    prices = relationship("Price", back_populates="store")
59
60    __table_args__ = (
61        UniqueConstraint("retailer", "store_id", name="uq_retailer_store"),
62        Index("ix_store_retailer_state", "retailer", "state"),
63    )
64
65
66class Product(Base):
67    __tablename__ = "products"
68
69    id = Column(Integer, primary_key=True, autoincrement=True)
70    upc = Column(String(14), unique=True, index=True)  # Universal Product Code
71    name = Column(String(500), nullable=False)
72    brand = Column(String(255))
73    category = Column(String(50), nullable=False, index=True)
74    subcategory = Column(String(100))
75    size = Column(String(100))  # e.g., "18 oz", "1 gal"
76    unit = Column(String(50))   # e.g., "oz", "lb", "count"
77    size_value = Column(Float)  # Numeric portion of size for unit price calc
78    description = Column(Text)
79    image_url = Column(String(1000))
80    is_active = Column(Boolean, default=True)
81    created_at = Column(DateTime, server_default=func.now())
82    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())
83
84    prices = relationship("Price", back_populates="product")
85    retailer_skus = relationship("RetailerSKU", back_populates="product")
86
87    __table_args__ = (
88        Index("ix_product_category_brand", "category", "brand"),
89        Index("ix_product_name_trgm", "name"),
90    )
91
92
93class RetailerSKU(Base):
94    """Maps retailer-specific product IDs to universal UPCs"""
95    __tablename__ = "retailer_skus"
96
97    id = Column(Integer, primary_key=True, autoincrement=True)
98    product_id = Column(Integer, ForeignKey("products.id"), nullable=False)
99    retailer = Column(String(50), nullable=False)
100    retailer_sku = Column(String(100), nullable=False)
101    retailer_product_name = Column(String(500))
102    retailer_url = Column(String(1000))
103    created_at = Column(DateTime, server_default=func.now())
104
105    product = relationship("Product", back_populates="retailer_skus")
106
107    __table_args__ = (
108        UniqueConstraint("retailer", "retailer_sku", name="uq_retailer_sku"),
109        Index("ix_retailer_sku_lookup", "retailer", "retailer_sku"),
110    )
111
112
113class Price(Base):
114    __tablename__ = "prices"
115
116    id = Column(BigInteger, primary_key=True, autoincrement=True)
117    product_id = Column(Integer, ForeignKey("products.id"), nullable=False)
118    store_id = Column(Integer, ForeignKey("stores.id"), nullable=False)
119    retailer = Column(String(50), nullable=False, index=True)
120
121    regular_price = Column(Float, nullable=False)
122    sale_price = Column(Float)
123    unit_price = Column(Float)  # Price per unit (oz, lb, etc.)
124    unit_price_unit = Column(String(20))  # The unit for unit_price
125    currency = Column(String(3), default="USD")
126
127    in_stock = Column(Boolean, default=True)
128    is_on_sale = Column(Boolean, default=False)
129    promo_description = Column(String(500))
130
131    observed_at = Column(DateTime, nullable=False, server_default=func.now())
132    created_at = Column(DateTime, server_default=func.now())
133
134    product = relationship("Product", back_populates="prices")
135    store = relationship("Store", back_populates="prices")
136
137    __table_args__ = (
138        Index("ix_price_product_retailer", "product_id", "retailer"),
139        Index("ix_price_observed", "observed_at"),
140        Index("ix_price_product_store_observed", "product_id", "store_id", "observed_at"),
141        Index("ix_price_retailer_category", "retailer"),
142    )
143
144
145class PriceHistory(Base):
146    """Aggregated daily price snapshots for historical analysis"""
147    __tablename__ = "price_history"
148
149    id = Column(BigInteger, primary_key=True, autoincrement=True)
150    product_id = Column(Integer, ForeignKey("products.id"), nullable=False)
151    store_id = Column(Integer, ForeignKey("stores.id"), nullable=False)
152    retailer = Column(String(50), nullable=False)
153    date = Column(DateTime, nullable=False)
154
155    min_price = Column(Float)
156    max_price = Column(Float)
157    avg_price = Column(Float)
158    was_on_sale = Column(Boolean, default=False)
159
160    created_at = Column(DateTime, server_default=func.now())
161
162    __table_args__ = (
163        UniqueConstraint("product_id", "store_id", "date", name="uq_price_history_daily"),
164        Index("ix_price_history_lookup", "product_id", "retailer", "date"),
165    )
166
167
168class DataRefreshLog(Base):
169    __tablename__ = "data_refresh_logs"
170
171    id = Column(Integer, primary_key=True, autoincrement=True)
172    retailer = Column(String(50), nullable=False)
173    refresh_type = Column(String(20))  # "full" or "incremental"
174    status = Column(String(20))  # "started", "completed", "failed"
175    records_processed = Column(Integer, default=0)
176    records_failed = Column(Integer, default=0)
177    started_at = Column(DateTime, server_default=func.now())
178    completed_at = Column(DateTime)
179    error_message = Column(Text)
180
181
182class ExportLog(Base):
183    __tablename__ = "export_logs"
184
185    id = Column(Integer, primary_key=True, autoincrement=True)
186    export_type = Column(String(20))  # "daily", "monthly"
187    destination = Column(String(20))  # "s3", "gcs", "local"
188    file_path = Column(String(1000))
189    file_format = Column(String(10))  # "parquet", "csv"
190    record_count = Column(Integer)
191    file_size_bytes = Column(BigInteger)
192    status = Column(String(20))
193    started_at = Column(DateTime, server_default=func.now())
194    completed_at = Column(DateTime)
195    error_message = Column(Text)