import os
import json
from sqlalchemy import create_engine, text
from dotenv import load_dotenv

# Load env from backend
load_dotenv("backend/.env")
db_url = os.getenv("DATABASE_URL")

if not db_url:
    print("[ERROR] DATABASE_URL not found in backend/.env")
    exit(1)

# Handle potential psql prefix
if db_url.startswith("postgres://"):
    db_url = db_url.replace("postgres://", "postgresql://", 1)

engine = create_engine(db_url)

def check_db():
    with engine.connect() as conn:
        # Check Products
        p_res = conn.execute(text("SELECT count(*) FROM products"))
        p_count = p_res.scalar()
        
        # Check Categories
        c_res = conn.execute(text("SELECT count(*) FROM categories"))
        c_count = c_res.scalar()
        
        # Check Images
        i_res = conn.execute(text("SELECT count(*) FROM product_images"))
        i_count = i_res.scalar()
        
        # Check Sample Product
        sample_res = conn.execute(text("SELECT name_en, price FROM products LIMIT 5"))
        samples = sample_res.fetchall()
        
        print(f"--- Database Report ---")
        print(f"Products: {p_count}")
        print(f"Categories: {c_count}")
        print(f"Product Images: {i_count}")
        print(f"Sample Products:")
        for s in samples:
            print(f"   - {s[0]} ({s[1]} SAR)")

if __name__ == "__main__":
    check_db()