import psycopg2

# --- Database connection ---
conn = psycopg2.connect(
    dbname="WarehouseStockManagement",
    user="appuser",
    password="warehouse123",
    host="localhost",
    port="5432"
)
cur = conn.cursor()

# --- Helper to parse the items column ---
def parse_items(cell):
    # If it's already a list (from text[]), join it into one string
    if isinstance(cell, list):
        cell = ",".join(cell)

    # Now safely parse the string
    cell = cell.strip("{}")
    parts = [p.strip() for p in cell.split(",") if p.strip()]
    parsed = []
    for part in parts:
        fields = {}
        chunks = part.split("|")
        # first element may be product_id (e.g. PROD-0376)
        if "=" not in chunks[0]:
            fields["product_id"] = chunks[0].strip()
            chunks = chunks[1:]
        for c in chunks:
            if "=" in c:
                k, v = c.split("=", 1)
                fields[k.strip()] = v.strip()
        parsed.append(fields)
    return parsed

# --- Fetch orders with items ---
# --- Fetch orders with items ---
cur.execute("SELECT order_id, items FROM public.orders WHERE items IS NOT NULL;")
rows = cur.fetchall()

print(f"Found {len(rows)} orders with items.")

insert_count = 0

for order_id, items_text in rows:
    print(f"Processing order {order_id}...")
    items = parse_items(items_text)
    for item in items:
        product_id = item.get("product_id")
        sku = item.get("sku")
        qty = int(float(item.get("qty", 0)))
        price = float(item.get("price", 0))

        cur.execute("""
            INSERT INTO public.order_items (order_id, product_id, sku, qty, price)
            VALUES (%s, %s, %s, %s, %s)
        """, (order_id, product_id, sku, qty, price))
        insert_count += 1

conn.commit()
cur.close()
conn.close()

print(f"✅ Backfill complete: {insert_count} order_items inserted.")