import os
import certifi
os.environ['REQUESTS_CA_BUNDLE'] = certifi.where()

import json
import requests
import mysql.connector
from datetime import datetime
import time
import urllib3
import random
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
}

BASE_DELAY = 0.8   # steady delay after every REST call (seconds)
MAX_RETRIES = 6

GRAPHQL_URL = f"https://{STORE_NAME}.myshopify.com/admin/api/{API_VERSION}/graphql.json"

# MySQL configuration
DB_CONFIG = {
    'user': 'shopifyuser',
    'password': 'FitFoodie247!',
    'host': '127.0.0.1',
    'database': 'fitfoodiedb'
}

def rest_request(method, url, *, label="", params=None, headers=None, timeout=30):
    """
    Throttled REST request with retries on 429/5xx and steady pacing.
    """
    attempt = 0
    while True:
        resp = requests.request(method, url, headers=headers or HEADERS, params=params, verify=False, timeout=timeout)
        # Success
        if 200 <= resp.status_code < 300:
            time.sleep(BASE_DELAY)
            return resp

        # Throttle or transient errors -> retry
        if resp.status_code in (429, 500, 502, 503, 504):
            if attempt >= MAX_RETRIES:
                print(f"❌ {label or url} failed after {MAX_RETRIES} retries: {resp.status_code} {resp.text[:200]}")
                return resp
            ra = resp.headers.get("Retry-After")
            if ra:
                try:
                    delay = max(float(ra), BASE_DELAY)
                except ValueError:
                    delay = BASE_DELAY
            else:
                # exponential backoff with jitter
                delay = (BASE_DELAY * (1.6 ** attempt)) + random.uniform(0, 0.3)
            print(f"⏳ {label or url} -> {resp.status_code}. Backing off {delay:.2f}s...")
            time.sleep(delay)
            attempt += 1
            continue

        # Other errors -> return
        print(f"❌ {label or url} returned {resp.status_code}: {resp.text[:200]}")
        return resp

def graphql_request(query: str, variables: dict):
    """Minimal wrapper for Shopify Admin GraphQL."""
    resp = requests.post(GRAPHQL_URL, headers=HEADERS, json={"query": query, "variables": variables}, verify=False, timeout=30)
    resp.raise_for_status()
    data = resp.json()
    if "errors" in data:
        raise RuntimeError(f"GraphQL errors: {data['errors']}")
    return data.get("data", {})

def get_product_mealinfo(product_gid: str):
    """
    Fetch product metafield mealinfo.ingredients (type=json).
    Returns a canonical JSON string (sorted keys, no spaces) or None if missing.
    """
    query = """
    query GetMealinfo($id: ID!) {
      product(id: $id) {
        metafield(namespace: "mealinfo", key: "ingredients") {
          type
          value
        }
      }
    }
    """
    try:
        data = graphql_request(query, {"id": product_gid})
        mf = (data.get("product") or {}).get("metafield")
        if not mf or not mf.get("value"):
            return None
        raw = mf["value"]
        # Shopify returns JSON metafield values as a string; normalize it
        try:
            parsed = json.loads(raw)
            # Compact canonical JSON for DB storage (stable diffs)
            return json.dumps(parsed, ensure_ascii=False, separators=(",", ":"), sort_keys=True)
        except Exception:
            # If it wasn't valid JSON for some reason, store raw for inspection
            return raw
    except Exception as e:
        print(f"⚠️ mealinfo fetch failed for {product_gid}: {e}")
        return None

def get_product_financial(product_gid: str):
    """
    Fetch product metafield financials.gravity.
    For number_decimal, return a Python float. Return None if missing/invalid.
    """
    query = """
    query GetFinancialinfo($id: ID!) {
      product(id: $id) {
        metafield(namespace: "financials", key: "gravity") {
          type
          value
        }
      }
    }
    """
    try:
        data = graphql_request(query, {"id": product_gid})
        mf = (data.get("product") or {}).get("metafield")
        if not mf:
            return None
        mtype = (mf.get("type") or "").lower()
        raw = mf.get("value")
        if raw is None:
            return None

        # Common cases:
        # - "number_decimal": value is a string like "0.76"
        # - "number_integer": value is "1" etc.
        # - "json": value is a JSON string (not expected here)
        if mtype in ("number_decimal", "number_integer"):
            try:
                return float(raw)
            except (TypeError, ValueError):
                return None

        # Fallbacks for unexpected types
        # Try to parse as JSON, else as float, else None
        try:
            parsed = json.loads(raw)
            if isinstance(parsed, (int, float)):
                return float(parsed)
        except Exception:
            pass
        try:
            return float(raw)
        except (TypeError, ValueError):
            return None

    except Exception as e:
        print(f"⚠️ financials.gravity fetch failed for {product_gid}: {e}")
        return None

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)  # not present in this query; kept for compatibility
        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

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')
                ))

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 Admin API
        base_cost = None
        if p.get('variants'):
            variant_id = p['variants'][0].get('id')
            base_cost = get_variant_cost(variant_id)

        # NEW: Get mealinfo JSON metafield (as canonical JSON string)
        mealinfo_json = get_product_mealinfo(product_gid)
        time.sleep(0.05)  # small delay to avoid hitting rate limits
        financial_gravity = get_product_financial(product_gid)

        # Write to DB
        try:
            print(f"Updating product {product_id} price={market_price}, cost={base_cost}, mealinfo_json={'present' if mealinfo_json else 'None'}")
            print(f"Product {product_id}: gravity={financial_gravity} (type={type(financial_gravity).__name__})")
            cursor.execute("""
                INSERT INTO shopify_products
                    (id, title, body_html, vendor, product_type, created_at, updated_at, tags, price, cost, mealinfo_json, financial_gravity)
                VALUES
                    (%s, %s, %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),
                    mealinfo_json = VALUES(mealinfo_json),
                    financial_gravity = VALUES(financial_gravity)
            """, (
                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,
                mealinfo_json,  # can be None
                financial_gravity  # can be None
            ))
        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()