import os import certifi os.environ['REQUESTS_CA_BUNDLE'] = certifi.where() import requests import mysql.connector from datetime import datetime import time import urllib3 urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning) # Shopify API configuration STORE_NAME = "former-fatty-meals" ADMIN_ACCESS_TOKEN = "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" API_VERSION = "2023-04" HEADERS = { "Content-Type": "application/json", "X-Shopify-Access-Token": ADMIN_ACCESS_TOKEN } # MySQL configuration DB_CONFIG = { 'user': 'shopifyuser', 'password': 'FitFoodie247!', 'host': '127.0.0.1', 'database': 'fitfoodiedb' } def get_us_market_price(product_gid): query = """ query GetContextualPriceAndCost($id: ID!, $context: ContextualPricingContext!) { product(id: $id) { variants(first: 1) { edges { node { contextualPricing(context: $context) { price { amount } } } } } } } """ variables = { "id": product_gid, "context": { "country": "US" } } url = f"https://{STORE_NAME}.myshopify.com/admin/api/{API_VERSION}/graphql.json" try: response = requests.post(url, headers=HEADERS, json={"query": query, "variables": variables}, verify=False) data = response.json() variant_node = data["data"]["product"]["variants"]["edges"][0]["node"] market_price_str = variant_node["contextualPricing"]["price"]["amount"] cost_str = variant_node.get("cost", None) market_price = float(market_price_str) market_cost = float(cost_str) if cost_str is not None else None return market_price, market_cost except Exception as e: print(f"⚠️ Error fetching market price for {product_gid}: {e}") return None, None import time def get_variant_cost(variant_id): def fetch_with_retry(url, label, max_attempts=3): for attempt in range(max_attempts): try: response = requests.get(url, headers=HEADERS, verify=False) if response.status_code == 200: return response elif response.status_code == 429: retry_after = int(response.headers.get("Retry-After", 2)) print(f"⏳ Rate limited while fetching {label}. Waiting {retry_after}s...") time.sleep(retry_after) else: print(f"❌ Failed to fetch {label}: HTTP {response.status_code}") break except Exception as e: print(f"⚠️ Exception fetching {label}: {e}") time.sleep(0.5) # throttle after each attempt return None variant_url = f"https://{STORE_NAME}.myshopify.com/admin/api/{API_VERSION}/variants/{variant_id}.json" variant_resp = fetch_with_retry(variant_url, f"variant {variant_id}") if not variant_resp: return None variant = variant_resp.json().get("variant", {}) inventory_item_id = variant.get('inventory_item_id') if not inventory_item_id: print(f"⚠️ No inventory_item_id found for variant {variant_id}") return None inv_url = f"https://{STORE_NAME}.myshopify.com/admin/api/{API_VERSION}/inventory_items/{inventory_item_id}.json" inv_resp = fetch_with_retry(inv_url, f"inventory_item {inventory_item_id}") if not inv_resp: return None inventory_item = inv_resp.json().get('inventory_item', {}) print(f"Inventory Item JSON: {inventory_item}") raw_cost = inventory_item.get('cost') try: return float(raw_cost) if raw_cost is not None else None except (TypeError, ValueError): print(f"⚠️ Invalid cost format for inventory item {inventory_item_id}: {raw_cost}") return None # Helper to make paginated requests def get_all(endpoint): results = [] if endpoint == "orders": url = f"https://{STORE_NAME}.myshopify.com/admin/api/{API_VERSION}/orders.json?status=any&limit=250" else: url = f"https://{STORE_NAME}.myshopify.com/admin/api/{API_VERSION}/{endpoint}.json?limit=250" while url: print("Fetching:", url) response = requests.get(url, headers=HEADERS, verify=False) if response.status_code != 200: print("Error:", response.status_code, response.text) break data = response.json() key = endpoint.split("?")[0] items = data.get(key, []) results.extend(items) url = response.links.get("next", {}).get("url") time.sleep(0.5) # Respect Shopify rate limit (2 req/sec) return results def sync_customers(cursor, customers): for c in customers: cursor.execute(""" INSERT INTO shopify_customers (id, email, first_name, last_name, phone, created_at, updated_at, state, total_spent, orders_count, tags) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE email = VALUES(email), first_name = VALUES(first_name), last_name = VALUES(last_name), phone = VALUES(phone), created_at = VALUES(created_at), updated_at = VALUES(updated_at), state = VALUES(state), total_spent = VALUES(total_spent), orders_count = VALUES(orders_count), tags = VALUES(tags) """, ( c['id'], c.get('email'), c.get('first_name'), c.get('last_name'), c.get('phone'), c.get('created_at'), c.get('updated_at'), c.get('state'), c.get('total_spent'), c.get('orders_count'), c.get('tags') )) def sync_order_items(cursor, order_id, line_items): for item in line_items: cursor.execute(""" INSERT INTO shopify_order_items (order_id, product_id, variant_id, title, variant_title, quantity, price, sku) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) """, ( order_id, item.get('product_id'), item.get('variant_id'), item.get('title'), item.get('variant_title'), item.get('quantity'), item.get('price'), item.get('sku') )) def sync_orders(cursor, orders): for o in orders: customer = o.get('customer') if not customer or not customer.get('id'): continue anon_id = None for attr in o.get('note_attributes', []): if attr.get('name') == 'anon_id': anon_id = attr.get('value') break customer_id = customer['id'] order_id = o['id'] cursor.execute(""" INSERT INTO shopify_orders ( id, order_number, customer_id, created_at, updated_at, total_price, subtotal_price, total_tax, currency, financial_status, fulfillment_status, note, tags, anon_id ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE order_number = VALUES(order_number), customer_id = VALUES(customer_id), created_at = VALUES(created_at), updated_at = VALUES(updated_at), total_price = VALUES(total_price), subtotal_price = VALUES(subtotal_price), total_tax = VALUES(total_tax), currency = VALUES(currency), financial_status = VALUES(financial_status), fulfillment_status = VALUES(fulfillment_status), note = VALUES(note), tags = VALUES(tags), anon_id = VALUES(anon_id) """, ( order_id, o.get('order_number'), customer_id, o.get('created_at'), o.get('updated_at'), o.get('total_price'), o.get('subtotal_price'), o.get('total_tax'), o.get('currency'), o.get('financial_status'), o.get('fulfillment_status'), o.get('note'), o.get('tags'), anon_id )) # Clean up and insert fresh line items cursor.execute("DELETE FROM shopify_order_items WHERE order_id = %s", (order_id,)) sync_order_items(cursor, order_id, o.get('line_items', [])) # === NEW: Log referral reward code usage === for discount in o.get("discount_codes", []): code = discount.get("code", "") if code.startswith("REFREWARD-"): cursor.execute(""" INSERT IGNORE INTO reward_code_usage (order_id, customer_id, discount_code, created_at) VALUES (%s, %s, %s, %s) """, ( order_id, customer_id, code, o.get('created_at') )) # === Mark code as consumed in reward_codes table === cursor.execute(""" UPDATE reward_codes SET consumed_at = NOW() WHERE reward_code = %s """, (code,)) def sync_products(cursor, products): for p in products: vendor = p.get('vendor', '') if vendor not in ["Fit Foodie Meals", "Former Fatty Meals"]: continue product_id = p['id'] product_gid = f"gid://shopify/Product/{product_id}" # Get market price via GraphQL market_price, _ = get_us_market_price(product_gid) # Get cost from first variant using REST API data # Get cost from first variant using REST Admin API (detailed endpoint) base_cost = None if p.get('variants'): variant_id = p['variants'][0].get('id') base_cost = get_variant_cost(variant_id) # Write to DB try: print(f"Updating product {product_id} with price={market_price}, cost={base_cost}") cursor.execute(""" INSERT INTO shopify_products (id, title, body_html, vendor, product_type, created_at, updated_at, tags, price, cost) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE title = VALUES(title), body_html = VALUES(body_html), vendor = VALUES(vendor), product_type = VALUES(product_type), created_at = VALUES(created_at), updated_at = VALUES(updated_at), tags = VALUES(tags), price = VALUES(price), cost = VALUES(cost) """, ( product_id, p.get('title'), p.get('body_html'), p.get('vendor'), p.get('product_type'), p.get('created_at'), p.get('updated_at'), p.get('tags'), market_price, base_cost )) except mysql.connector.Error as err: print(f"❌ MySQL error: {err}") def main(): conn = mysql.connector.connect(**DB_CONFIG) cursor = conn.cursor() print("Syncing customers...") customers = get_all("customers") sync_customers(cursor, customers) print("Syncing orders...") orders = get_all("orders") sync_orders(cursor, orders) print("Syncing products (filtered by vendor)...") products = get_all("products") sync_products(cursor, products) conn.commit() cursor.close() conn.close() print("✅ Sync complete.") if __name__ == "__main__": main()