import random
from faker import Faker
from faker_commerce import Provider

from Controllers.Postgres.engine import get_db, engine, Base
from Controllers.Postgres.schema import (
    User, 
    Order, 
    Product, 
    Supplier, 
    ShippingAddress, 
    Category,
)

fake = Faker('en_US')
fake.add_provider(Provider)

num_rows = 1000

# Base.metadata.create_all(engine)

def commit_session_or_rollback(session, list_of_appends):
    try:
        session.add_all(list_of_appends)
        session.commit()
    except Exception as err:
        print('Alchemy', str(err)[:113])
        session.rollback()
        raise

with get_db() as session:
    # Generate and add unique Products
    products = []
    for _ in range(num_rows):
        products.append(Product(name=fake.unique.ecommerce_name() + str(_), price=round(random.uniform(10, 2000), 2)))
    commit_session_or_rollback(session, products)
    product_ids = [p.id for p in session.query(Product).all()]

    # Generate and add unique Categories
    categories = []
    for _ in range(num_rows):
        categories.append(Category(name=fake.unique.domain_name() + str(_)))
    
    commit_session_or_rollback(session, categories)
    category_ids = [c.id for c in session.query(Category).all()]

    # Generate and add unique Suppliers
    suppliers = []
    for _ in range(num_rows):
        suppliers.append(Supplier(name=fake.unique.free_email() + str(_), rating=round(random.uniform(2.5, 5.0), 1)))
    commit_session_or_rollback(session, suppliers)
    supplier_ids = [s.id for s in session.query(Supplier).all()]

    # Generate and add unique Shipping Addresses
    shipping_addresses = []
    for _ in range(num_rows):
        shipping_addresses.append(ShippingAddress(
            address_line_1=fake.unique.address(),
            city=fake.unique.city(),
            zip_code=fake.unique.zipcode()
        ))
    commit_session_or_rollback(session, shipping_addresses)
    shipping_address_ids = [sa.id for sa in session.query(ShippingAddress).all()]

    # Generate and add unique Users
    users = []
    for _ in range(num_rows):
        users.append(User(
            username=fake.unique.user_name() + str(_),
            account_balance=round(random.uniform(0, 5000), 2),
            preferred_category_id=random.choice(category_ids),
            last_ordered_product_id=random.choice(product_ids),
            supplier_rating_id=random.choice(supplier_ids)
        ))
    commit_session_or_rollback(session, users)
    user_ids = [u.id for u in session.query(User).all()]

    # Generate and add unique Orders
    orders = []
    for _ in range(num_rows):
        orders.append(Order(
            order_date=fake.unique.date(),
            total_amount=round(random.uniform(10, 3000), 2),
            user_id=random.choice(user_ids),
            shipping_address_id=random.choice(shipping_address_ids)
        ))
    commit_session_or_rollback(session, orders)