#!/usr/bin/env python3
"""
Script 5: Update Customer Shipping Costs
Matches customer shipping charges to orders using multiple sources:

Priority Order:
1. UPS ShipHaven: shipping_charges-ups-shiphaven (matches by tracking number)
2. USPS Stamps: shipping_charges-usps_stampscom (matches by tracking number)
3. Shipments table: shipments (fallback via orders_items_shipments link)
4. Meyer Dropship: supplier_charges-meyer (matches by order_no)
5. Keystone/LKQ Dropship: supplier_charges-lkq (matches by customer_po_number)

IMPORTANT: This script updates orders where shipping cost is missing
- Updates supplier_shipping and our_buy_price
- Sets our_shipping_source to track the data source

Cron-ready: Runs continuously on rolling 120-day window, updates all customer shipping costs
"""

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

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

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

# Date range - rolling window
DATE_START = None  # Will be calculated dynamically
DATE_END = None    # Will be calculated as today
DATE_RANGE_DAYS = 120

# 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

logger = logging.getLogger(__name__)

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

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

# ============================================================================
# HELPER FUNCTIONS
# ============================================================================

def clean_decimal(value):
    """Clean and convert text to decimal"""
    if not value or (isinstance(value, str) and value.strip() == ''):
        return 0.0
    try:
        # Remove any currency symbols, commas
        clean_value = str(value).replace('$', '').replace(',', '').strip()
        return float(clean_value)
    except:
        return 0.0

# ============================================================================
# CUSTOMER SHIPPING UPDATE FUNCTIONS
# ============================================================================

def update_shipping_from_all_sources():
    """
    Match shipping costs to orders from multiple sources.
    Priority: ShipHaven -> Stamps -> Shipments -> Meyer -> Keystone
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    logger.info("="*80)
    logger.info("Processing: CUSTOMER SHIPPING (All Sources)")
    logger.info("  Sources:")
    logger.info("    1. UPS ShipHaven: shipping_charges-ups-shiphaven")
    logger.info("    2. USPS Stamps: shipping_charges-usps_stampscom")
    logger.info("    3. Shipments table: shipments (via orders_items_shipments)")
    logger.info("    4. Meyer Dropship: supplier_charges-meyer")
    logger.info("    5. Keystone/LKQ Dropship: supplier_charges-lkq")
    logger.info("="*80)

    # Get all orders that need shipping cost
    # Include orders with tracking OR Meyer/Keystone suppliers
    query = """
        SELECT
            oim.id,
            oim.order_item_id,
            oim.external_order_id,
            oim.sales_channel,
            oim.sales_price,
            oim.supplier_shipping,
            oim.supplier_handling,
            oim.our_buy_price,
            oim.our_shipping_source,
            oim.buy_price_source,
            oim.supplier,
            oi.tracking,
            oi.order_id
        FROM orders_items_margin_detailed oim
        JOIN orders_items oi ON oim.order_item_id = oi.order_item_id
        WHERE oim.date_placed >= %s
          AND oim.date_placed < %s
          AND oim.is_active = 1
          AND (oim.our_shipping_source IS NULL
               OR oim.our_shipping_source = 'Snapshot'
               OR oim.our_shipping_source NOT IN ('Invoice: UPS', 'Invoice: USPS', 'Shipments Table', 'Dropship: MEYER', 'Dropship: LKQ'))
          AND (oim.supplier_shipping IS NULL OR oim.supplier_shipping = 0)
    """

    cursor.execute(query, [DATE_START, DATE_END])
    orders = cursor.fetchall()

    logger.info(f"Found {len(orders)} orders needing shipping cost")

    if len(orders) == 0:
        cursor.close()
        conn.close()
        return {
            'shiphaven_updated': 0, 'stamps_updated': 0, 'shipments_updated': 0,
            'meyer_updated': 0, 'keystone_updated': 0,
            'no_match': 0, 'total_shipping': 0
        }

    # Stats tracking
    shiphaven_updated = 0
    stamps_updated = 0
    shipments_updated = 0
    meyer_updated = 0
    keystone_updated = 0
    no_match = 0
    total_shipping_added = 0
    by_channel = {}
    by_source = {'ShipHaven': 0, 'Stamps': 0, 'Shipments': 0, 'Meyer': 0, 'Keystone': 0}

    update_query = """
        UPDATE orders_items_margin_detailed
        SET supplier_shipping = %s,
            supplier_handling = %s,
            our_buy_price = COALESCE(our_buy_price, 0) - COALESCE(supplier_shipping, 0) - COALESCE(supplier_handling, 0) + %s + %s,
            our_shipping_source = %s
        WHERE id = %s
    """

    # Group orders by external_order_id to handle multi-item orders
    order_groups = {}
    for order in orders:
        ext_order_id = order['external_order_id']
        if ext_order_id not in order_groups:
            order_groups[ext_order_id] = []
        order_groups[ext_order_id].append(order)

    sample_updates = []

    # Process each external_order_id once
    for ext_order_id, group_orders in order_groups.items():
        # Get supplier info from first order in group
        supplier = group_orders[0]['supplier'] or ''
        tracking = group_orders[0]['tracking']

        total_ship_cost = 0
        total_handling = 0
        source_used = None

        # =====================================================================
        # SOURCE 1: Try UPS ShipHaven (if has tracking)
        # =====================================================================
        if tracking and total_ship_cost <= 0:
            cursor.execute("""
                SELECT SHIPMENT_TOTAL
                FROM `shipping_charges-ups-shiphaven`
                WHERE AIRBILL_NO = %s
                LIMIT 1
            """, (tracking,))

            ups_record = cursor.fetchone()
            if ups_record:
                ship_cost = clean_decimal(ups_record['SHIPMENT_TOTAL'])
                if ship_cost > 0:
                    total_ship_cost = ship_cost
                    source_used = 'Invoice: UPS'

        # =====================================================================
        # SOURCE 2: Try USPS Stamps (if has tracking)
        # =====================================================================
        if tracking and total_ship_cost <= 0:
            cursor.execute("""
                SELECT amount_paid
                FROM `shipping_charges-usps_stampscom`
                WHERE tracking_number = %s
                LIMIT 1
            """, (tracking,))

            usps_record = cursor.fetchone()
            if usps_record:
                ship_cost = clean_decimal(usps_record['amount_paid'])
                if ship_cost > 0:
                    total_ship_cost = ship_cost
                    source_used = 'Invoice: USPS'

        # =====================================================================
        # SOURCE 3: Fallback to shipments table (if has tracking)
        # =====================================================================
        if tracking and total_ship_cost <= 0:
            order_item_ids = [o['order_item_id'] for o in group_orders]
            if order_item_ids:
                placeholders = ','.join(['%s'] * len(order_item_ids))
                cursor.execute(f"""
                    SELECT DISTINCT s.shipment_id, s.tracking_number, s.shipping_cost, s.carrier
                    FROM shipments s
                    JOIN orders_items_shipments ois ON s.shipment_id = ois.shipment_id
                    WHERE ois.order_item_id IN ({placeholders})
                      AND s.shipping_cost > 0
                """, order_item_ids)

                shipment_records = cursor.fetchall()
                for shipment in shipment_records:
                    ship_cost = float(shipment['shipping_cost']) if shipment['shipping_cost'] else 0
                    if ship_cost > 0:
                        total_ship_cost += ship_cost
                        source_used = 'Shipments Table'

        # =====================================================================
        # SOURCE 4: Meyer Dropship (supplier_charges-meyer)
        # =====================================================================
        if total_ship_cost <= 0 and 'meyer' in supplier.lower():
            cursor.execute("""
                SELECT SUM(shipping) as total_shipping, SUM(handling) as total_handling
                FROM `supplier_charges-meyer`
                WHERE order_no = %s
            """, (ext_order_id,))

            meyer_record = cursor.fetchone()
            if meyer_record and meyer_record['total_shipping'] is not None:
                total_ship_cost = float(meyer_record['total_shipping']) if meyer_record['total_shipping'] else 0
                total_handling = float(meyer_record['total_handling']) if meyer_record['total_handling'] else 0
                if total_ship_cost > 0 or total_handling > 0:
                    source_used = 'Dropship: MEYER'

        # =====================================================================
        # SOURCE 5: Keystone/LKQ Dropship (supplier_charges-lkq)
        # =====================================================================
        if total_ship_cost <= 0 and ('keystone' in supplier.lower() or 'lkq' in supplier.lower() or 'ksi' in supplier.lower()):
            cursor.execute("""
                SELECT SUM(shipping) as total_shipping, SUM(handling) as total_handling
                FROM `supplier_charges-lkq`
                WHERE customer_po_number = %s
            """, (ext_order_id,))

            lkq_record = cursor.fetchone()
            if lkq_record and lkq_record['total_shipping'] is not None:
                total_ship_cost = float(lkq_record['total_shipping']) if lkq_record['total_shipping'] else 0
                total_handling = float(lkq_record['total_handling']) if lkq_record['total_handling'] else 0
                if total_ship_cost > 0 or total_handling > 0:
                    source_used = 'Dropship: LKQ'

        # =====================================================================
        # Update orders if shipping cost found
        # =====================================================================
        if (total_ship_cost <= 0 and total_handling <= 0) or not source_used:
            no_match += len(group_orders)
            continue

        # Divide total shipping cost among ALL items in the order
        num_items = len(group_orders)
        ship_cost_per_item = total_ship_cost / num_items
        handling_per_item = total_handling / num_items
        total_per_item = ship_cost_per_item + handling_per_item

        # Update each item with its share of shipping
        for order in group_orders:
            if not DRY_RUN:
                cursor.execute(update_query, (
                    ship_cost_per_item,
                    handling_per_item,
                    ship_cost_per_item,
                    handling_per_item,
                    source_used,
                    order['id']
                ))

            total_shipping_added += total_per_item

            # Track by source
            if source_used == 'Invoice: UPS':
                shiphaven_updated += 1
                by_source['ShipHaven'] += total_per_item
            elif source_used == 'Invoice: USPS':
                stamps_updated += 1
                by_source['Stamps'] += total_per_item
            elif source_used == 'Shipments Table':
                shipments_updated += 1
                by_source['Shipments'] += total_per_item
            elif source_used == 'Dropship: MEYER':
                meyer_updated += 1
                by_source['Meyer'] += total_per_item
            elif source_used == 'Dropship: LKQ':
                keystone_updated += 1
                by_source['Keystone'] += total_per_item

            # Track by channel
            channel = order['sales_channel']
            if channel not in by_channel:
                by_channel[channel] = {'count': 0, 'total': 0}
            by_channel[channel]['count'] += 1
            by_channel[channel]['total'] += total_per_item

            # Collect sample updates
            if len(sample_updates) < 15:
                multi_note = f" (split {num_items} items)" if num_items > 1 else ""
                handling_note = f" + ${handling_per_item:.2f} handling" if handling_per_item > 0 else ""
                sample_updates.append(
                    f"  {'[DRY RUN] ' if DRY_RUN else ''}{channel} order {order['external_order_id']}: "
                    f"${ship_cost_per_item:.2f}{handling_note} from {source_used}{multi_note}"
                )

    if not DRY_RUN:
        conn.commit()

    # Log sample updates
    logger.info("\nSample Updates:")
    for sample in sample_updates:
        logger.info(sample)

    total_updated = shiphaven_updated + stamps_updated + shipments_updated + meyer_updated + keystone_updated
    if total_updated > 15:
        logger.info(f"  ... and {total_updated - 15} more orders updated")

    # Summary
    logger.info(f"\n{'='*80}")
    logger.info(f"SHIPPING UPDATE SUMMARY")
    logger.info(f"{'='*80}")
    logger.info(f"Orders checked: {len(orders)}")
    logger.info(f"\nBy Source:")
    logger.info(f"  ShipHaven (UPS):    {shiphaven_updated:,} orders, ${by_source['ShipHaven']:,.2f}")
    logger.info(f"  Stamps (USPS):      {stamps_updated:,} orders, ${by_source['Stamps']:,.2f}")
    logger.info(f"  Shipments Table:    {shipments_updated:,} orders, ${by_source['Shipments']:,.2f}")
    logger.info(f"  Meyer Dropship:     {meyer_updated:,} orders, ${by_source['Meyer']:,.2f}")
    logger.info(f"  Keystone Dropship:  {keystone_updated:,} orders, ${by_source['Keystone']:,.2f}")
    logger.info(f"\nTotal Updated: {total_updated:,} orders")
    logger.info(f"Total Shipping Added: ${total_shipping_added:,.2f}")
    logger.info(f"No Match Found: {no_match:,} orders")

    if by_channel:
        logger.info(f"\nBy Channel:")
        for channel, stats in sorted(by_channel.items()):
            logger.info(f"  {channel}: {stats['count']:,} orders, ${stats['total']:,.2f}")

    cursor.close()
    conn.close()

    return {
        'shiphaven_updated': shiphaven_updated,
        'stamps_updated': stamps_updated,
        'shipments_updated': shipments_updated,
        'meyer_updated': meyer_updated,
        'keystone_updated': keystone_updated,
        'no_match': no_match,
        'total_shipping': total_shipping_added,
        'by_channel': by_channel,
        'by_source': by_source
    }


def get_orders_without_shipping():
    """Get orders that still don't have shipping costs after all sources checked"""
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    query = """
        SELECT
            oim.external_order_id,
            oim.sales_channel,
            oim.supplier,
            oim.our_shipping_source,
            oim.supplier_shipping,
            oim.buy_price_source,
            oi.tracking,
            oim.date_placed
        FROM orders_items_margin_detailed oim
        JOIN orders_items oi ON oim.order_item_id = oi.order_item_id
        WHERE oim.date_placed >= %s
          AND oim.date_placed < %s
          AND oim.is_active = 1
          AND (oim.our_shipping_source IS NULL OR oim.our_shipping_source = 'Snapshot')
          AND (oim.supplier_shipping IS NULL OR oim.supplier_shipping = 0)
        ORDER BY oim.date_placed DESC
    """

    cursor.execute(query, [DATE_START, DATE_END])
    orders = cursor.fetchall()

    cursor.close()
    conn.close()

    return orders


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

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

    # Calculate date range dynamically
    global DATE_START, DATE_END
    if DATE_START is None and DATE_END is None:
        DATE_END = datetime.now().strftime('%Y-%m-%d')
        DATE_START = (datetime.now() - timedelta(days=DATE_RANGE_DAYS)).strftime('%Y-%m-%d')

    logger.info("="*80)
    logger.info(f"CUSTOMER SHIPPING 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)
    logger.info(f"Date Range: {DATE_START} to {DATE_END} ({DATE_RANGE_DAYS} days)")

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

    # Process all shipping sources
    result = update_shipping_from_all_sources()

    # Get remaining orders without shipping
    remaining = get_orders_without_shipping()

    logger.info(f"\n{'='*80}")
    logger.info(f"ORDERS STILL WITHOUT SHIPPING COST: {len(remaining)}")
    logger.info(f"{'='*80}")

    if remaining:
        logger.info("\nSample of orders without shipping (up to 10):")
        for i, order in enumerate(remaining[:10]):
            tracking_str = order['tracking'][:20] + '...' if order['tracking'] and len(order['tracking']) > 20 else (order['tracking'] or 'N/A')
            logger.info(f"  {i+1}. {order['external_order_id']} | {order['sales_channel']} | "
                       f"{order['supplier'] or 'Unknown'} | tracking: {tracking_str}")

    logger.info(f"\n{'='*80}\n")

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

    return result, remaining


if __name__ == "__main__":
    # Configure logging
    log_filename = os.path.join(LOG_DIR, f'customer_shipping_{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}")
    result, remaining = main()