storage.py

import json
import os
from dataclasses import asdict
from datetime import datetime

from models import Product


def save_json(products: list[Product], path: str):
    with open(path, "w", encoding="utf-8") as f:
        json.dump([asdict(p) for p in products], f, ensure_ascii=False, indent=2)
    print(f"Saved → {path}")


def save_csv(products: list[Product], path: str):
    import pandas as pd
    df = pd.DataFrame([asdict(p) for p in products])
    df.to_csv(path, index=False, encoding="utf-8-sig")
    print(f"Saved → {path}")


def sql_path_from(data_path: str) -> str:
    """'products/2026-05-06/Foods_Organic.json' → 'products/2026-05-06/sql/Foods_Organic.sql'"""
    dirname = os.path.dirname(data_path)
    stem = os.path.splitext(os.path.basename(data_path))[0]
    return os.path.join(dirname, "sql", f"{stem}.sql")


def _sql_val(v) -> str:
    if v is None:
        return "NULL"
    if isinstance(v, str):
        return "'" + v.replace("'", "''") + "'"
    return str(v)


def save_sql(products: list[Product], path: str):
    os.makedirs(os.path.dirname(path), exist_ok=True)
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    cols = "(name, quantity, price, rating, review_count, image_url, product_url, created_at)"
    updates = ", ".join([
        "name=VALUES(name)",
        "quantity=VALUES(quantity)",
        "price=VALUES(price)",
        "rating=VALUES(rating)",
        "review_count=VALUES(review_count)",
        "image_url=VALUES(image_url)",
        "created_at=VALUES(created_at)",
    ])
    with open(path, "w", encoding="utf-8") as f:
        for p in products:
            vals = ", ".join([
                _sql_val(p.name),
                str(p.quantity),
                _sql_val(p.price),
                _sql_val(p.rating),
                _sql_val(p.review_count),
                _sql_val(p.image_url),
                _sql_val(p.product_url),
                f"'{now}'",
            ])
            f.write(
                f"INSERT INTO `products` {cols} VALUES ({vals}) "
                f"ON DUPLICATE KEY UPDATE {updates};\n"
            )
    print(f"Saved → {path}")