#!/usr/bin/env python3
"""
Script 2: Update Supplier Costs from All Supplier Tables
Continuously updates our_buy_price from supplier charge tables for all suppliers

Handles:
- USAuto (dropshipper)
- Meyer (dropshipper)
- DEPO MaxZone (dropshipper)
- LKQ/Keystone (dropshipper)
- TYC (dropshipper)
- ExpressParts (regular supplier)
- JCAuto (regular supplier)
- PBI (regular supplier)
- RegionMax/ELT (regular supplier)

buy_price_source values:
- Dropshippers: "<Supplier> Included" (e.g., "USAuto Included")
- Regular suppliers: "Supplier e-mail"

Cron-ready: Runs continuously on rolling 60-day window, updates all supplier prices
"""

import mysql.connector
import logging
from datetime import datetime, timedelta
from decimal import Decimal
import os

# ============================================================================
# CONFIGURATION
# ============================================================================

DB_CONFIG = {
    'host': '127.0.0.1',
    'port': 3306,
    'database': 'goparts',
    'user': 'root',
    'password': ')V9IR<W:~4=HrqbL'
}

# Date range - set to None to process all dates
# Using rolling window to catch older orders as supplier data arrives
DATE_START = None  # Will be calculated as DATE_RANGE_DAYS ago
DATE_END = None    # Will be calculated as today
DATE_RANGE_DAYS = 120  # Number of days to look back (includes August data)
DATE_TOLERANCE_DAYS = 7

# Logging
BASE_DIR = '/home/centralgoparts/public_html/profitability'
LOG_DIR = os.path.join(BASE_DIR, 'logs')
os.makedirs(LOG_DIR, exist_ok=True)

DRY_RUN = False  # Set to True for testing

# ============================================================================
# SUPPLIER CONFIGURATION
# ============================================================================

SUPPLIER_CONFIG = {
    'usauto': {
        'table': 'supplier_charges-usauto',
        'supplier_ids': [11, 16, 17, 45, 46, 48, 49, 54],
        'type': 'dropshipper',  # Sets buy_price_source to "USAuto Included"
        'fields': {
            'order_no': 'cross_reference_no',  # USAuto stores our order ID here!
            'part_no': 'part_no',
            'unit_price': 'unit_price',
            'quantity': 'quantity',
            'discount': 'discount',
            'shipping': 'shipping',
            'handling': 'handling',
            'order_date': 'order_date'
        }
    },
    'meyer': {
        'table': 'supplier_charges-meyer',
        'supplier_ids': [61, 62, 63, 64, 65, 66, 67, 68, 69],
        'type': 'dropshipper',
        'fields': {
            'order_no': 'order_no',
            'part_no': 'part_no',
            'unit_price': 'unit_price',
            'quantity': 'qty_shipped',
            'discount': None,
            'shipping': 'shipping',
            'handling': 'handling',
            'order_date': 'order_date'
        }
    },
    'depo': {
        'table': 'supplier_charges-depo',
        'supplier_ids': [3, 51, 52, 53, 72],
        'type': 'dropshipper',
        'fields': {
            'order_no': 'order_no',  # DEPO order_no is empty
            'part_no': 'part_no',  # Match on DEPO's actual part_no via parts_suppliers lookup
            'unit_price': 'unit_price',
            'quantity': 'shipped_qty',
            'discount': None,
            'shipping': None,
            'handling': None,
            'order_date': 'invoice_date'
        },
        'use_parts_suppliers_lookup': True  # Special flag for DEPO to use parts_suppliers
    },
    'lkq': {
        'table': 'supplier_charges-lkq',
        'supplier_ids': [8],
        'type': 'dropshipper',
        'fields': {
            'order_no': 'customer_po_number',
            'part_no': 'part_no',
            'unit_price': 'unit_price',
            'quantity': 'qty_shipped',
            'discount': None,
            'shipping': 'shipping',
            'handling': 'handling',
            'order_date': 'order_date'
        }
    },
    'tyc': {
        'table': 'supplier_charges-tyc',
        'supplier_ids': [1, 55, 71],
        'type': 'dropshipper',
        'fields': {
            'order_no': 'sales_order_no',
            'part_no': 'part_no',
            'unit_price': 'unit_price',
            'quantity': 'qty',
            'discount': None,
            'shipping': None,
            'handling': None,
            'order_date': 'invoice_date'
        }
    },
    'expressparts': {
        'table': 'supplier_charges-expressparts',
        'supplier_ids': [57, 58],  # Express Parts Miami, Orlando
        'type': 'regular',  # Sets buy_price_source to "Supplier e-mail"
        'fields': {
            'order_no': 'po_number',
            'part_no': 'part_no',
            'unit_price': 'unit_price',
            'quantity': 'shipped_qty',
            'discount': None,
            'shipping': 'shipping_charge',
            'handling': None,
            'order_date': 'invoice_date'
        }
    },
    'jcauto': {
        'table': 'supplier_charges-jcauto',
        'supplier_ids': [56],
        'type': 'regular',
        'fields': {
            'order_no': 'invoice_no',
            'part_no': 'part_no',
            'unit_price': 'unit_price',
            'quantity': 'sold_qty',
            'discount': None,
            'shipping': None,
            'handling': None,
            'order_date': 'invoice_date'
        }
    },
    'pbi': {
        'table': 'supplier_charges-pbi',
        'supplier_ids': [12, 21, 43],  # PBI CA, VA, TX
        'type': 'regular',
        'fields': {
            'order_no': 'po_no',
            'part_no': 'part_no',
            'unit_price': 'unit_price',
            'quantity': 'qty',
            'discount': None,
            'shipping': None,
            'handling': None,  # PBI handling is already factored into unit_price
            'order_date': 'delivery_date'
        }
    },
    'elt': {
        'table': 'supplier_charges-regionmax-elt',
        'supplier_ids': [2, 18],  # ELT-WC CA, NJ
        'type': 'regular',
        'fields': {
            'order_no': 'invoice_number',
            'part_no': 'material',
            'unit_price': 'unit_price',
            'quantity': 'qty',
            'discount': None,
            'shipping': None,
            'handling': 'handling_fee',
            'order_date': 'bill_date'
        }
    }
}

logger = logging.getLogger(__name__)

# ============================================================================
# STATUS SYNC CONFIGURATION
# ============================================================================

# Statuses that indicate order is ACTIVE (is_active = 1)
ACTIVE_STATUSES = [
    1,   # Needs Order
    3,   # Ordered
    6,   # Shipped
    31,  # Ordered Ebay/Amazon
    35,  # En-route to GA
    43,  # Waiting For Payment Info
    50,  # Local Delivered
    53   # Local Ordered
]

# Statuses that indicate order is INACTIVE (is_active = 0)
INACTIVE_STATUSES = [
    4,   # Sent to SS
    7,   # Cancelled
    12,  # Pending Return
    24,  # Return Refunded
    27,  # Backorder Refunded
    29,  # Dead Return
    32,  # Awaiting Customer Reply
    33,  # Please Cancel
    34,  # Refunded
    39,  # Credit Memo Sent
    41,  # For Refund
    42,  # Payment Declined
    44,  # Order Voided
    45,  # Voided Payment
    48,  # Total Loss Refund
    49,  # Cancelled - Not Purchased
    51,  # Local Cancelled
    52   # Local Refunded
]

# ============================================================================
# DATABASE CONNECTION
# ============================================================================

def get_db_connection():
    """Get MySQL database connection"""
    return mysql.connector.connect(**DB_CONFIG)

# ============================================================================
# STATUS SYNC FUNCTIONS
# ============================================================================

def sync_order_status():
    """
    Sync is_active status in orders_items_margin_detailed based on current_status in orders_items

    Updates:
    - Sets is_active = 1 for orders with ACTIVE_STATUSES
    - Sets is_active = 0 for orders with INACTIVE_STATUSES
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    logger.info("\n" + "="*80)
    logger.info("SYNCING ORDER STATUS")
    logger.info("="*80)

    # Sync ACTIVE orders (should be is_active = 1 but currently 0)
    active_query = f"""
        SELECT
            oim.id,
            oim.external_order_id,
            oi.current_status,
            oim.is_active
        FROM orders_items_margin_detailed oim
        JOIN orders_items oi ON oim.order_item_id = oi.order_item_id
        WHERE oi.current_status IN ({','.join(['%s'] * len(ACTIVE_STATUSES))})
          AND oim.is_active = 0
          AND oim.is_manual_update = 0
    """

    params = ACTIVE_STATUSES.copy()
    if DATE_START and DATE_END:
        active_query += " AND oim.date_placed >= %s AND oim.date_placed < %s"
        params.extend([DATE_START, DATE_END])

    cursor.execute(active_query, params)
    active_items = cursor.fetchall()

    if active_items:
        logger.info(f"Found {len(active_items)} orders with ACTIVE status but is_active=0")

        if not DRY_RUN:
            for item in active_items:
                cursor.execute("""
                    UPDATE orders_items_margin_detailed
                    SET is_active = 1
                    WHERE id = %s
                      AND is_manual_update = 0
                """, (item['id'],))

        logger.info(f"  {'[DRY RUN] Would update' if DRY_RUN else 'Updated'} {len(active_items)} orders to is_active=1")

        if len(active_items) <= 10:
            for item in active_items:
                logger.info(f"    Order {item['external_order_id']}: status {item['current_status']} -> is_active=1")
    else:
        logger.info("All ACTIVE orders correctly marked (is_active=1)")

    # Sync INACTIVE orders (should be is_active = 0 but currently 1)
    inactive_query = f"""
        SELECT
            oim.id,
            oim.external_order_id,
            oi.current_status,
            oim.is_active
        FROM orders_items_margin_detailed oim
        JOIN orders_items oi ON oim.order_item_id = oi.order_item_id
        WHERE oi.current_status IN ({','.join(['%s'] * len(INACTIVE_STATUSES))})
          AND oim.is_active = 1
          AND oim.is_manual_update = 0
    """

    params = INACTIVE_STATUSES.copy()
    if DATE_START and DATE_END:
        inactive_query += " AND oim.date_placed >= %s AND oim.date_placed < %s"
        params.extend([DATE_START, DATE_END])

    cursor.execute(inactive_query, params)
    inactive_items = cursor.fetchall()

    if inactive_items:
        logger.info(f"\nFound {len(inactive_items)} orders with INACTIVE status but is_active=1")

        if not DRY_RUN:
            for item in inactive_items:
                cursor.execute("""
                    UPDATE orders_items_margin_detailed
                    SET is_active = 0
                    WHERE id = %s
                      AND is_manual_update = 0
                """, (item['id'],))

        logger.info(f"  {'[DRY RUN] Would update' if DRY_RUN else 'Updated'} {len(inactive_items)} orders to is_active=0")

        if len(inactive_items) <= 10:
            for item in inactive_items:
                logger.info(f"    Order {item['external_order_id']}: status {item['current_status']} -> is_active=0")
    else:
        logger.info("\nAll INACTIVE orders correctly marked (is_active=0)")

    if not DRY_RUN:
        conn.commit()

    total_synced = len(active_items) + len(inactive_items)
    logger.info(f"\nStatus Sync Summary:")
    logger.info(f"  Total orders synced: {total_synced}")
    logger.info(f"  Set to active (1): {len(active_items)}")
    logger.info(f"  Set to inactive (0): {len(inactive_items)}")
    logger.info("="*80)

    cursor.close()
    conn.close()

    return {
        'active_synced': len(active_items),
        'inactive_synced': len(inactive_items),
        'total_synced': total_synced
    }


def fix_missing_sales_data():
    """
    Fix missing sales_price and item_shipping in orders_items_margin_detailed
    Pulls from orders_items.sale_price and orders.shipping

    For item_shipping:
    - Gets total shipping from orders table
    - Divides proportionally by number of items in the order
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    logger.info(f"\n{'='*80}")
    logger.info(f"FIXING MISSING SALES DATA (sales_price & item_shipping)")
    logger.info(f"{'='*80}")

    # Find orders with missing or zero sales_price
    cursor.execute("""
        SELECT
            oim.id,
            oim.order_id,
            oim.order_item_id,
            oim.external_order_id,
            oim.sales_price as current_sales_price,
            oim.item_shipping as current_item_shipping,
            oi.sale_price as items_sale_price,
            o.shipping as orders_shipping,
            (SELECT COUNT(*) FROM orders_items WHERE order_id = oim.order_id) as item_count
        FROM orders_items_margin_detailed oim
        LEFT JOIN orders_items oi ON oim.order_item_id = oi.order_item_id
        LEFT JOIN orders o ON oim.order_id = o.order_id
        WHERE (oim.sales_price IS NULL OR oim.sales_price = 0 OR oim.item_shipping IS NULL)
          AND oi.sale_price IS NOT NULL
          AND oi.sale_price > 0
          AND oim.is_manual_update = 0
    """ + (f" AND oim.date_placed >= '{DATE_START}' AND oim.date_placed < '{DATE_END}'" if DATE_START and DATE_END else ""))

    items = cursor.fetchall()

    if not items:
        logger.info("No items with missing sales data found")
        cursor.close()
        conn.close()
        return {'fixed': 0}

    logger.info(f"Found {len(items)} items with missing sales_price or item_shipping")

    fixed = 0

    for item in items:
        # Get values
        items_sale_price = float(item['items_sale_price']) if item['items_sale_price'] else 0
        orders_shipping = float(item['orders_shipping']) if item['orders_shipping'] else 0
        item_count = int(item['item_count']) if item['item_count'] else 1

        # Calculate item_shipping (proportional share of order shipping)
        item_shipping = orders_shipping / item_count if item_count > 0 else 0

        # Calculate sales_price (sale_price + item_shipping)
        sales_price = items_sale_price + item_shipping

        # Update
        if not DRY_RUN:
            cursor.execute("""
                UPDATE orders_items_margin_detailed
                SET sales_price = %s,
                    item_sale_price = %s,
                    item_shipping = %s
                WHERE id = %s
                  AND is_manual_update = 0
            """, (sales_price, items_sale_price, item_shipping, item['id']))

        fixed += 1

        if fixed <= 20:
            logger.info(f"  {'[DRY RUN] ' if DRY_RUN else ''}Order {item['external_order_id']}: "
                       f"sale_price=${items_sale_price:.2f} + shipping=${item_shipping:.2f} = "
                       f"sales_price=${sales_price:.2f} (was ${item['current_sales_price'] or 0:.2f})")

    if fixed > 20:
        logger.info(f"  ... and {fixed - 20} more items fixed")

    if not DRY_RUN:
        conn.commit()

    logger.info(f"\nFixed {fixed} items with missing sales data")
    logger.info(f"{'='*80}\n")

    cursor.close()
    conn.close()

    return {'fixed': fixed}

# ============================================================================
# SUPPLIER ID SYNC FUNCTIONS
# ============================================================================

def sync_supplier_id():
    """
    Sync supplier_id from orders_items to orders_items_margin_detailed

    This ensures that orders_items_margin_detailed has the correct supplier_id
    from the source orders_items table, which is the authoritative source.
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    logger.info("\n" + "="*80)
    logger.info("SYNCING SUPPLIER_ID FROM orders_items TO orders_items_margin_detailed")
    logger.info("="*80)

    # Find items with mismatched supplier_id
    query = """
        SELECT
            oim.id,
            oim.external_order_id,
            oim.supplier_id as current_supplier_id,
            oim.supplier as current_supplier,
            oi.supplier_id as correct_supplier_id
        FROM orders_items_margin_detailed oim
        JOIN orders_items oi ON oim.order_item_id = oi.order_item_id
        WHERE oi.supplier_id != oim.supplier_id
          AND oim.is_active = 1
          AND oim.is_manual_update = 0
    """

    params = []
    if DATE_START and DATE_END:
        query += " AND oim.date_placed >= %s AND oim.date_placed < %s"
        params.extend([DATE_START, DATE_END])

    cursor.execute(query, params)
    mismatched_items = cursor.fetchall()

    if not mismatched_items:
        logger.info("No supplier_id mismatches found")
        logger.info("="*80 + "\n")
        cursor.close()
        conn.close()
        return {'synced': 0}

    logger.info(f"Found {len(mismatched_items)} items with mismatched supplier_id")

    # Get supplier name mapping
    cursor.execute("SELECT supplier_id, name FROM suppliers")
    supplier_names = {row['supplier_id']: row['name'] for row in cursor.fetchall()}

    synced = 0
    for item in mismatched_items:
        correct_supplier_name = supplier_names.get(item['correct_supplier_id'], 'Unknown')

        if not DRY_RUN:
            cursor.execute("""
                UPDATE orders_items_margin_detailed
                SET supplier_id = %s,
                    supplier = %s
                WHERE id = %s
                  AND is_manual_update = 0
            """, (item['correct_supplier_id'], correct_supplier_name, item['id']))

        synced += 1

        if synced <= 20:
            logger.info(f"  {'[DRY RUN] ' if DRY_RUN else ''}Order {item['external_order_id']}: "
                       f"{item['current_supplier_id']} ({item['current_supplier']}) -> "
                       f"{item['correct_supplier_id']} ({correct_supplier_name})")

    if synced > 20:
        logger.info(f"  ... and {synced - 20} more items synced")

    if not DRY_RUN:
        conn.commit()

    logger.info(f"\n{'[DRY RUN] Would sync' if DRY_RUN else 'Synced'} {synced} items")
    logger.info("="*80 + "\n")

    cursor.close()
    conn.close()

    return {'synced': synced}

def populate_missing_supplier_partnumber():
    """
    Populate supplier_partNumber from parts_suppliers when it's NULL

    This handles cases where orders_items_margin_detailed doesn't have supplier part numbers
    but they exist in parts_suppliers table. Also handles kit/set items.
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    logger.info("\n" + "="*80)
    logger.info("POPULATING MISSING SUPPLIER PART NUMBERS")
    logger.info("="*80)

    # Find items with NULL supplier_partNumber
    query = """
        SELECT
            oim.id,
            oim.external_order_id,
            oim.order_item_id,
            oim.supplier_id,
            oi.part_num,
            oi.product_id,
            oi.is_kit_set
        FROM orders_items_margin_detailed oim
        JOIN orders_items oi ON oim.order_item_id = oi.order_item_id
        WHERE oim.supplier_partnumber IS NULL
          AND oim.is_active = 1
          AND oim.is_manual_update = 0
    """

    params = []
    if DATE_START and DATE_END:
        query += " AND oim.date_placed >= %s AND oim.date_placed < %s"
        params.extend([DATE_START, DATE_END])

    cursor.execute(query, params)
    items = cursor.fetchall()

    if not items:
        logger.info("No items with NULL supplier_partNumber found")
        logger.info("="*80 + "\n")
        cursor.close()
        conn.close()
        return {'populated': 0}

    logger.info(f"Found {len(items)} items with NULL supplier_partNumber")

    populated = 0
    for item in items:
        part_num = item['part_num']
        product_id = item['product_id']
        supplier_id = item['supplier_id']
        is_kit_set = item.get('is_kit_set', 0)

        # If part_num is NULL, try to get partslink from products table
        lookup_partslink = part_num
        if not lookup_partslink and product_id:
            cursor.execute("""
                SELECT partslink
                FROM products
                WHERE product_id = %s
                LIMIT 1
            """, (product_id,))
            product_row = cursor.fetchone()
            if product_row:
                lookup_partslink = product_row['partslink']
                if lookup_partslink:
                    logger.info(f"  Found partslink {lookup_partslink} from products table for order {item['external_order_id']}")

        # Look up in parts_suppliers
        ps_row = None
        if lookup_partslink:
            cursor.execute("""
                SELECT supplier_partnumber, partslink
                FROM parts_suppliers
                WHERE partslink = %s AND supplier_id = %s
                LIMIT 1
            """, (lookup_partslink, supplier_id))

            ps_row = cursor.fetchone()

        if ps_row:
            supplier_partnumber = ps_row['supplier_partnumber']
            partslink = ps_row['partslink']

            # For kit/sets, also try to extract individual part number
            # Format: "SET-PARTNUM-QTY" → extract "PARTNUM"
            extracted_part = None
            if is_kit_set and part_num and part_num.startswith('SET-'):
                parts = part_num.split('-')
                if len(parts) >= 2:
                    # Extract middle part(s), excluding SET prefix and possible quantity suffix
                    extracted_part = '-'.join(parts[1:-1]) if len(parts) > 2 else parts[1]

            if not DRY_RUN:
                # Update with the found values
                cursor.execute("""
                    UPDATE orders_items_margin_detailed
                    SET supplier_partnumber = %s,
                        partslink = %s
                    WHERE id = %s
                      AND is_manual_update = 0
                """, (supplier_partnumber, partslink, item['id']))

            populated += 1

            if populated <= 20:
                kit_info = f" (kit/set, extracted: {extracted_part})" if is_kit_set else ""
                logger.info(f"  {'[DRY RUN] ' if DRY_RUN else ''}Order {item['external_order_id']}: "
                           f"part_num={part_num} → supplier_part={supplier_partnumber}{kit_info}")

    if populated > 20:
        logger.info(f"  ... and {populated - 20} more items populated")

    if not DRY_RUN:
        conn.commit()

    logger.info(f"\n{'[DRY RUN] Would populate' if DRY_RUN else 'Populated'} {populated} items")
    logger.info("="*80 + "\n")

    cursor.close()
    conn.close()

    return {'populated': populated}

def update_loss_rates():
    """
    Update loss_rate for orders that have NULL values
    Uses category lookup from products table → profitability_total_loss_rate
    B2B channel uses loss_rate_b2b, all other channels use loss_rate_b2c
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    logger.info(f"\n{'='*80}")
    logger.info(f"UPDATING LOSS RATES")
    logger.info(f"  Strategy: Category lookup via products table (excluding OGP)")
    logger.info(f"  B2B → loss_rate_b2b, Others (Amazon/eBay/Web) → loss_rate_b2c")
    logger.info(f"{'='*80}")

    # Query to find orders needing loss rate update with category lookup
    query = """
        SELECT DISTINCT
            oim.id,
            oim.external_order_id,
            oim.sales_channel,
            oim.partslink,
            oim.supplier_partnumber,
            p.category_id,
            CASE
                WHEN oim.sales_channel = 'B2B' THEN lr.loss_rate_b2b
                ELSE lr.loss_rate_b2c
            END as applicable_loss_rate
        FROM orders_items_margin_detailed oim
        INNER JOIN products p
            ON p.partslink = oim.partslink
            AND p.category_id != 'OGP'
        INNER JOIN profitability_total_loss_rate lr
            ON lr.category_id = p.category_id
        WHERE oim.is_active = 1
          AND oim.is_manual_update = 0
          AND oim.loss_rate IS NULL
    """

    params = []

    # Add date filter if specified
    if DATE_START and DATE_END:
        query += " AND oim.date_placed >= %s AND oim.date_placed < %s"
        params.extend([DATE_START, DATE_END])

    cursor.execute(query, params)
    items = cursor.fetchall()

    logger.info(f"Found {len(items)} orders with NULL loss_rate that can be populated")

    if len(items) == 0:
        cursor.close()
        conn.close()
        return {'total': 0, 'updated': 0}

    updated = 0
    update_query = """
        UPDATE orders_items_margin_detailed
        SET loss_rate = %s
        WHERE id = %s
          AND is_manual_update = 0
    """

    for item in items:
        loss_rate = float(item['applicable_loss_rate']) if item['applicable_loss_rate'] is not None else None

        if loss_rate is None:
            continue

        if not DRY_RUN:
            cursor.execute(update_query, (loss_rate, item['id']))

        updated += 1

        # Log first 20 updates for verification
        if updated <= 20:
            channel_type = "B2B" if item['sales_channel'] == 'B2B' else "B2C"
            logger.info(f"  {'[DRY RUN] ' if DRY_RUN else ''}Order {item['external_order_id']}: "
                       f"category={item['category_id']}, channel={channel_type}, "
                       f"loss_rate={loss_rate:.2f}%")

    if updated > 20:
        logger.info(f"  ... and {updated - 20} more orders updated")

    if not DRY_RUN:
        conn.commit()

    logger.info(f"\n{'[DRY RUN] Would update' if DRY_RUN else 'Updated'} {updated} orders with loss rates")
    logger.info("="*80 + "\n")

    cursor.close()
    conn.close()

    return {'total': len(items), 'updated': updated}

# ============================================================================
# SUPPLIER COST UPDATE FUNCTIONS
# ============================================================================

def update_supplier_costs(supplier_name, config):
    """
    Update supplier costs for a specific supplier

    Args:
        supplier_name: Name of the supplier (e.g., 'usauto', 'meyer')
        config: Configuration dictionary for this supplier
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    supplier_ids = config['supplier_ids']
    table = config['table']
    fields = config['fields']
    supplier_type = config['type']

    # Determine buy_price_source value
    if supplier_type == 'dropshipper':
        buy_price_source = f"{supplier_name.upper()} Included" if supplier_name != 'lkq' else "Keystone Included"
    else:
        buy_price_source = "Supplier e-mail"

    logger.info(f"\n{'='*80}")
    logger.info(f"Processing: {supplier_name.upper()}")
    logger.info(f"  Table: {table}")
    logger.info(f"  Supplier IDs: {supplier_ids}")
    logger.info(f"  Type: {supplier_type}")
    logger.info(f"  buy_price_source: {buy_price_source}")
    logger.info(f"{'='*80}")

    # Build query to get items needing cost updates
    # NOTE: Removed is_active filter - sync costs for ALL orders regardless of status
    query = f"""
        SELECT
            oim.id,
            oim.order_id,
            oim.external_order_id,
            oim.order_item_id,
            oim.supplier_id,
            oim.supplier,
            oim.date_placed,
            oim.supplier_partnumber,
            oim.partslink,
            oi.part_num
        FROM orders_items_margin_detailed oim
        JOIN orders_items oi ON oim.order_item_id = oi.order_item_id
        WHERE oim.is_manual_update = 0
            AND oim.supplier_id IN ({','.join(['%s'] * len(supplier_ids))})
    """

    params = supplier_ids.copy()

    # Add date filter if specified
    if DATE_START and DATE_END:
        query += " AND oim.date_placed >= %s AND oim.date_placed < %s"
        params.extend([DATE_START, DATE_END])

    cursor.execute(query, params)
    items = cursor.fetchall()

    logger.info(f"Found {len(items)} items to update")

    if len(items) == 0:
        cursor.close()
        conn.close()
        return {'total': 0, 'matched': 0, 'date_matched': 0, 'not_matched': 0}

    matched = 0
    date_matched = 0
    not_matched = 0

    for item in items:
        # Build query to match supplier charge
        order_no_field = fields['order_no']
        date_field = fields['order_date']

        # Determine which field to match on (order_item_id or external_order_id)
        match_on = config.get('match_on', 'external_order_id')
        match_value = item.get(match_on)

        # Get the part number to match (prefer supplier_partnumber, then partslink, then part_num from orders_items)
        item_part_number = item.get('supplier_partnumber')
        if not item_part_number or str(item_part_number).strip() == '':
            item_part_number = item.get('partslink')
        if not item_part_number or str(item_part_number).strip() == '':
            item_part_number = item.get('part_num')  # Fallback to part_num from orders_items

        # Convert to string and strip, handling None case
        if item_part_number:
            item_part_number = str(item_part_number).strip()

        # Build base query SELECT clause (used for both exact and date matching)
        base_select = f"""
            SELECT
                `{fields['order_no']}` as order_no,
                `{fields['part_no']}` as part_no,
                `{fields['unit_price']}` as unit_price,
                `{fields['quantity']}` as quantity
        """

        if fields.get('discount'):
            base_select += f", `{fields['discount']}` as discount"
        if fields.get('shipping'):
            base_select += f", `{fields['shipping']}` as shipping"
        if fields.get('handling'):
            base_select += f", `{fields['handling']}` as handling"

        base_select += f" FROM `{table}` WHERE transaction_type = 'purchase'"

        # Initialize supplier_charge
        supplier_charge = None

        # SPECIAL HANDLING FOR SET/KIT ITEMS:
        # For SET/KIT items, combine ALL supplier charges with matching order number
        is_set_kit = item_part_number and (item_part_number.startswith('SET-') or item_part_number.startswith('KIT-'))

        if is_set_kit:
            # Query for ALL charges with this order number (no part number filter)
            combine_query = base_select + f" AND `{order_no_field}` = %s"
            cursor.execute(combine_query, (match_value,))
            all_charges = cursor.fetchall()

            if all_charges and len(all_charges) > 0:
                # Combine all charges into one total
                # For each part, calculate net price (unit_price + discount), then sum
                combined_net_price = Decimal('0')
                combined_shipping = Decimal('0')
                combined_handling = Decimal('0')

                for charge in all_charges:
                    unit_price = Decimal(str(charge.get(fields['unit_price'], 0)))
                    discount = Decimal(str(charge.get(fields['discount'], 0))) if fields.get('discount') else Decimal('0')
                    combined_net_price += (unit_price + discount)  # discount is negative, so this subtracts

                    if fields.get('shipping'):
                        combined_shipping += Decimal(str(charge.get(fields['shipping'], 0)))
                    if fields.get('handling'):
                        combined_handling += Decimal(str(charge.get(fields['handling'], 0)))

                # Create combined supplier_charge dictionary
                # Set quantity=1 since this is ONE SET/KIT item, and discount=0 since we already applied it
                supplier_charge = {
                    'order_no': match_value,
                    'part_no': item_part_number,  # Use the SET/KIT part number
                    'unit_price': combined_net_price,  # Already includes discount
                    'quantity': 1,  # One SET/KIT item
                    'discount': Decimal('0'),  # Already applied above
                    'shipping': combined_shipping,
                    'handling': combined_handling
                }

                part_list = ', '.join([charge.get(fields['part_no'], 'N/A') for charge in all_charges])
                logger.info(f"  SET/KIT combined {len(all_charges)} parts for {item_part_number}: [{part_list}]")

        # Try exact match on order number AND part number (only if we have a part number)
        if not supplier_charge and item_part_number:
            exact_match_query = base_select + f"""
                AND `{order_no_field}` = %s
                AND `{fields['part_no']}` = %s
                LIMIT 1
            """

            # Strategy 1: Try direct match with supplier_partnumber from orders_items_margin_detailed
            cursor.execute(exact_match_query, (match_value, item_part_number))
            supplier_charge = cursor.fetchone()

            # Strategy 2: If no match, look up correct supplier_partnumber via parts_suppliers using partslink
            if not supplier_charge and item.get('partslink'):
                cursor.execute("""
                    SELECT supplier_partnumber
                    FROM parts_suppliers
                    WHERE partslink = %s
                      AND supplier_id = %s
                    LIMIT 1
                """, (str(item['partslink']).strip(), item['supplier_id']))
                ps_lookup = cursor.fetchone()

                if ps_lookup and ps_lookup.get('supplier_partnumber'):
                    ps_part = str(ps_lookup['supplier_partnumber']).strip()
                    if ps_part != item_part_number:  # Only try if different
                        cursor.execute(exact_match_query, (match_value, ps_part))
                        supplier_charge = cursor.fetchone()
                        if supplier_charge:
                            logger.info(f"  Partslink lookup match: {item_part_number} → {ps_part} for order {item.get('external_order_id')}")

            # Strategy 3: Try removing common suffixes (Q, (CAPA), (NSF), etc.)
            if not supplier_charge:
                # Remove "Q" suffix at the end before parenthesis
                cleaned_part = item_part_number
                if 'Q (' in cleaned_part:
                    cleaned_part = cleaned_part.replace('Q (', '(')
                # Remove parenthesized suffixes like " (CAPA)", " (NSF)"
                if ' (' in cleaned_part:
                    cleaned_part = cleaned_part.split(' (')[0].strip()

                if cleaned_part != item_part_number:
                    cursor.execute(exact_match_query, (match_value, cleaned_part))
                    supplier_charge = cursor.fetchone()
                    if supplier_charge:
                        logger.info(f"  Suffix removal match: {item_part_number} → {cleaned_part} for order {item.get('external_order_id')}")

            # Strategy 4: Try adding Q suffix (USAuto often appends Q to part numbers)
            if not supplier_charge:
                part_with_q = item_part_number + 'Q'
                cursor.execute(exact_match_query, (match_value, part_with_q))
                supplier_charge = cursor.fetchone()
                if supplier_charge:
                    logger.info(f"  Q suffix match: {item_part_number} → {part_with_q} for order {item.get('external_order_id')}")

            # Strategy 5: If this is a kit/set, try extracting individual part number
            if not supplier_charge and (item_part_number.startswith('SET-') or item_part_number.startswith('KIT-')):
                parts = item_part_number.split('-')
                if len(parts) >= 2:
                    # Extract middle part(s), excluding SET/KIT prefix and possible quantity suffix
                    extracted_part = '-'.join(parts[1:-1]) if len(parts) > 2 else parts[1]

                    cursor.execute(exact_match_query, (match_value, extracted_part))
                    supplier_charge = cursor.fetchone()

                    if supplier_charge:
                        logger.info(f"  Kit/set match: {item_part_number} → {extracted_part} for order {item.get('external_order_id')}")

        # Track match type for this item
        item_is_date_match = False

        # If no exact match, try date range matching with part number filtering
        if not supplier_charge and date_field:
            date_from = item['date_placed'] - timedelta(days=DATE_TOLERANCE_DAYS)
            date_to = item['date_placed'] + timedelta(days=DATE_TOLERANCE_DAYS)

            # Get order identifiers - prefer supplier_partnumber over partslink
            order_supplier_part = item.get('supplier_partnumber')
            order_partslink = item.get('partslink')

            # Determine which part number(s) to search for
            search_part_numbers = []

            # Approach 1: Direct supplier_partnumber (PREFERRED)
            if order_supplier_part and str(order_supplier_part).strip() != '':
                raw_part = str(order_supplier_part).strip()
                search_part_numbers.append(raw_part)

                # For ELT, also try cleaned part number (remove suffixes like "(CAPA)")
                if supplier_name == 'elt':
                    # Remove common suffixes: " (CAPA)", " (NSF)", etc.
                    cleaned_part = raw_part.split(' (')[0].strip()
                    if cleaned_part != raw_part and cleaned_part not in search_part_numbers:
                        search_part_numbers.append(cleaned_part)

            # Approach 2: For DEPO, lookup via parts_suppliers table (fallback)
            if supplier_name == 'depo' and order_partslink and str(order_partslink).strip() != '':
                cursor.execute("""
                    SELECT supplier_partnumber
                    FROM parts_suppliers
                    WHERE partslink = %s
                      AND supplier_id IN (3, 51, 52, 53, 72)
                    LIMIT 1
                """, (str(order_partslink).strip(),))
                parts_supplier = cursor.fetchone()

                if parts_supplier and parts_supplier.get('supplier_partnumber'):
                    supplier_part = str(parts_supplier['supplier_partnumber']).strip()
                    if supplier_part and supplier_part not in search_part_numbers:
                        search_part_numbers.append(supplier_part)

            # Approach 3: For non-DEPO suppliers, try direct partslink
            if supplier_name != 'depo' and order_partslink and str(order_partslink).strip() != '':
                partslink_val = str(order_partslink).strip()
                if partslink_val not in search_part_numbers:
                    search_part_numbers.append(partslink_val)

            # Try to find a match with each part number
            for search_part in search_part_numbers:
                date_match_query = base_select + f"""
                    AND `{date_field}` >= %s
                    AND `{date_field}` <= %s
                    AND `{fields['part_no']}` = %s
                    ORDER BY ABS(DATEDIFF(`{date_field}`, %s))
                    LIMIT 1
                """

                cursor.execute(date_match_query, (date_from, date_to, search_part, item['date_placed']))
                supplier_charge = cursor.fetchone()

                if supplier_charge:
                    date_matched += 1
                    item_is_date_match = True
                    item_identifier = match_value if match_on == 'order_item_id' else item['external_order_id']
                    logger.info(f"  {supplier_name.upper()} matched via part number: {search_part}")
                    logger.info(f"  Date match with part verification for {item_identifier}: part {search_part}")
                    break  # Found a match, stop searching

            # If no match found with part numbers, log it for debugging
            if not supplier_charge and search_part_numbers:
                item_identifier = match_value if match_on == 'order_item_id' else item['external_order_id']
                logger.debug(f"  No date match found for {item_identifier} with parts: {', '.join(search_part_numbers)}")

        # FALLBACK: If no part numbers available, try matching by order number alone
        # This handles orders where supplier_partnumber, partslink, and part_num are all NULL
        if not supplier_charge and not search_part_numbers:
            fallback_query = base_select + f" AND `{order_no_field}` = %s LIMIT 1"
            cursor.execute(fallback_query, (match_value,))
            supplier_charge = cursor.fetchone()

            if supplier_charge:
                matched += 1
                item_identifier = match_value if match_on == 'order_item_id' else item['external_order_id']
                logger.info(f"  Order-only match (no part numbers available) for {item_identifier}")
        elif supplier_charge:
            matched += 1

        if supplier_charge:
            # Calculate costs - PER UNIT basis for individual order items
            # When supplier ships multiple quantities of same part in one order,
            # we need to split costs across individual order items
            unit_price = float(supplier_charge['unit_price']) if supplier_charge['unit_price'] else 0
            quantity = int(supplier_charge['quantity']) if supplier_charge['quantity'] else 1

            # Per-unit calculations
            discount = float(supplier_charge.get('discount', 0)) if supplier_charge.get('discount') else 0
            shipping_total = float(supplier_charge.get('shipping', 0)) if supplier_charge.get('shipping') else 0
            handling_total = float(supplier_charge.get('handling', 0)) if supplier_charge.get('handling') else 0

            # Divide shipping and handling by quantity to get per-unit cost
            shipping_per_unit = shipping_total / quantity if quantity > 0 else shipping_total
            handling_per_unit = handling_total / quantity if quantity > 0 else handling_total
            discount_per_unit = discount / quantity if quantity > 0 else discount

            # Discount is stored as negative number (e.g., -1.10), so ADD it to get net price
            # Example: unit_price=27.58, discount=-1.10 → net_price=27.58+(-1.10)=26.48
            net_price_per_unit = unit_price + discount_per_unit
            total_buy_price_per_unit = net_price_per_unit + shipping_per_unit + handling_per_unit

            # For dropshippers, also set our_shipping_source
            if supplier_type == 'dropshipper':
                if item_is_date_match:
                    update_query = """
                        UPDATE orders_items_margin_detailed
                        SET
                            supplier_price = %s,
                            supplier_shipping = %s,
                            supplier_handling = %s,
                            our_buy_price = %s,
                            buy_price_source = %s,
                            our_shipping_source = %s,
                            remarks = 'DATE_PART_MATCH'
                        WHERE id = %s
                          AND is_manual_update = 0
                    """
                else:
                    update_query = """
                        UPDATE orders_items_margin_detailed
                        SET
                            supplier_price = %s,
                            supplier_shipping = %s,
                            supplier_handling = %s,
                            our_buy_price = %s,
                            buy_price_source = %s,
                            our_shipping_source = %s,
                            remarks = 'EXACT_MATCH'
                        WHERE id = %s
                          AND is_manual_update = 0
                    """
                shipping_source = f"Dropship: {supplier_name.upper()}"  # e.g., "Dropship: USAUTO"
            else:
                # Regular suppliers - don't set our_shipping_source (Script 5 will handle it)
                if item_is_date_match:
                    update_query = """
                        UPDATE orders_items_margin_detailed
                        SET
                            supplier_price = %s,
                            supplier_shipping = %s,
                            supplier_handling = %s,
                            our_buy_price = %s,
                            buy_price_source = %s,
                            remarks = 'DATE_PART_MATCH'
                        WHERE id = %s
                          AND is_manual_update = 0
                    """
                else:
                    update_query = """
                        UPDATE orders_items_margin_detailed
                        SET
                            supplier_price = %s,
                            supplier_shipping = %s,
                            supplier_handling = %s,
                            our_buy_price = %s,
                            buy_price_source = %s,
                            remarks = 'EXACT_MATCH'
                        WHERE id = %s
                          AND is_manual_update = 0
                    """
                shipping_source = None

            if not DRY_RUN:
                if supplier_type == 'dropshipper':
                    cursor.execute(update_query, (
                        net_price_per_unit,
                        shipping_per_unit,
                        handling_per_unit,
                        total_buy_price_per_unit,
                        buy_price_source,
                        shipping_source,
                        item['id']
                    ))
                else:
                    cursor.execute(update_query, (
                        net_price_per_unit,
                        shipping_per_unit,
                        handling_per_unit,
                        total_buy_price_per_unit,
                        buy_price_source,
                        item['id']
                    ))

            if (matched + date_matched) <= 10:  # Log first 10
                qty_note = f" (split from qty={quantity})" if quantity > 1 else ""
                logger.info(f"  {'[DRY RUN] ' if DRY_RUN else ''}Updated item {item['id']} (order: {item['external_order_id']}): "
                           f"price=${net_price_per_unit:.2f}, ship=${shipping_per_unit:.2f}, handling=${handling_per_unit:.2f}, "
                           f"total=${total_buy_price_per_unit:.2f}, source={buy_price_source}{qty_note}")
        else:
            not_matched += 1
            # Mark unmatched orders and clear buy price
            if not DRY_RUN:
                cursor.execute("""
                    UPDATE orders_items_margin_detailed
                    SET our_buy_price = 0,
                        supplier_price = 0,
                        supplier_shipping = 0,
                        supplier_handling = 0,
                        buy_price_source = NULL,
                        remarks = 'NO_SUPPLIER_DATA'
                    WHERE id = %s
                      AND is_manual_update = 0
                """, (item['id'],))

    if not DRY_RUN:
        conn.commit()

    logger.info(f"\n{supplier_name.upper()} Summary:")
    logger.info(f"  Items processed: {len(items)}")
    logger.info(f"  Exact matches: {matched}")
    logger.info(f"  Date-range matches: {date_matched}")
    logger.info(f"  Not matched: {not_matched}")
    if len(items) > 0:
        logger.info(f"  Success rate: {((matched + date_matched) / len(items) * 100):.1f}%")

    cursor.close()
    conn.close()

    return {
        'total': len(items),
        'matched': matched,
        'date_matched': date_matched,
        'not_matched': not_matched
    }

# ============================================================================
# MAIN EXECUTION
# ============================================================================

def main():
    """Main execution function"""

    # Calculate date range dynamically
    global DATE_START, DATE_END
    # DISABLED: Process ALL dates to sync all available supplier cost data
    # if DATE_END is None:
    #     DATE_END = datetime.now().strftime('%Y-%m-%d')
    # if DATE_START is None:
    #     DATE_START = (datetime.now() - timedelta(days=DATE_RANGE_DAYS)).strftime('%Y-%m-%d')

    logger.info("="*80)
    logger.info(f"SUPPLIER COST UPDATE - {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
    logger.info(f"{'DRY RUN MODE' if DRY_RUN else 'LIVE MODE'}")
    logger.info("="*80)

    if DATE_START and DATE_END:
        logger.info(f"Date Range: {DATE_START} to {DATE_END} ({DATE_RANGE_DAYS} days)")
    else:
        logger.info("Date Range: ALL DATES")

    if DRY_RUN:
        logger.warning("\n⚠️  DRY RUN MODE - No database changes will be made\n")

    # Sync order status first (ensure is_active is correct before processing)
    try:
        sync_order_status()
    except Exception as e:
        logger.error(f"Error syncing order status: {e}")

    # Fix missing sales_price and item_shipping
    try:
        fix_missing_sales_data()
    except Exception as e:
        logger.error(f"Error fixing missing sales data: {e}")

    # Sync supplier_id from orders_items to orders_items_margin_detailed
    try:
        sync_supplier_id()
    except Exception as e:
        logger.error(f"Error syncing supplier_id: {e}")

    # Populate missing supplier_partnumber from parts_suppliers
    try:
        populate_missing_supplier_partnumber()
    except Exception as e:
        logger.error(f"Error populating supplier_partnumber: {e}")

    # REMOVED: Loss rates are no longer used in profitability calculations
    # # Update loss rates from category lookup
    # try:
    #     update_loss_rates()
    # except Exception as e:
    #     logger.error(f"Error updating loss rates: {e}")

    # Process all suppliers
    total_stats = {
        'total': 0,
        'matched': 0,
        'date_matched': 0,
        'not_matched': 0
    }

    for supplier_name, config in SUPPLIER_CONFIG.items():
        try:
            result = update_supplier_costs(supplier_name, config)
            total_stats['total'] += result['total']
            total_stats['matched'] += result['matched']
            total_stats['date_matched'] += result['date_matched']
            total_stats['not_matched'] += result['not_matched']
        except Exception as e:
            logger.error(f"Error processing {supplier_name}: {e}")
            continue

    # Final summary
    logger.info(f"\n{'='*80}")
    logger.info(f"OVERALL SUMMARY")
    logger.info(f"{'='*80}")
    logger.info(f"Total items processed: {total_stats['total']}")
    logger.info(f"Exact matches: {total_stats['matched']}")
    logger.info(f"Date-range matches: {total_stats['date_matched']}")
    logger.info(f"Not matched: {total_stats['not_matched']}")
    if total_stats['total'] > 0:
        success_rate = ((total_stats['matched'] + total_stats['date_matched']) / total_stats['total'] * 100)
        logger.info(f"Overall success rate: {success_rate:.1f}%")
    logger.info(f"{'='*80}\n")

    if DRY_RUN:
        logger.warning("⚠️  This was a DRY RUN - no data was updated\n")

if __name__ == "__main__":
    # Configure logging
    log_filename = os.path.join(LOG_DIR, f'supplier_costs_{datetime.now().strftime("%Y%m%d_%H%M%S")}.log')

    logging.basicConfig(
        level=logging.INFO,
        format='%(asctime)s - %(levelname)s - %(message)s',
        handlers=[
            logging.FileHandler(log_filename),
            logging.StreamHandler()
        ]
    )

    logger.info(f"Log file: {log_filename}")
    main()