#!/usr/bin/env python3
"""
Script 8: Fix Incorrect Buy Prices
Validates and corrects our_buy_price to match the correct formula

Correct Formula:
our_buy_price = supplier_price + supplier_shipping + supplier_handling

This script identifies orders where our_buy_price doesn't match this formula
and corrects them, regardless of the specific error pattern.

Cron-ready: Runs continuously on rolling 120-day window
"""

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 as DATE_RANGE_DAYS ago
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

# Tolerance for floating point comparison (in dollars)
PRICE_TOLERANCE = 0.02  # Allow 2 cent difference due to rounding

logger = logging.getLogger(__name__)

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

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

# ============================================================================
# FIX FUNCTIONS
# ============================================================================

def fix_incorrect_buy_prices():
    """
    Fix orders where our_buy_price doesn't match the correct formula:
    our_buy_price = supplier_price + supplier_shipping + supplier_handling

    This catches all calculation errors regardless of the specific bug pattern.
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    logger.info("\n" + "="*80)
    logger.info("CHECKING BUY PRICE FORMULA ACCURACY")
    logger.info("Formula: our_buy_price = supplier_price + supplier_shipping + handling")
    logger.info("="*80)

    # Calculate date range
    if DATE_START and DATE_END:
        date_filter = "AND date_placed >= %s AND date_placed < %s"
        date_params = [DATE_START, DATE_END]
    else:
        end_date = datetime.now()
        start_date = end_date - timedelta(days=DATE_RANGE_DAYS)
        date_filter = "AND date_placed >= %s AND date_placed < %s"
        date_params = [start_date, end_date]
        logger.info(f"Date range: {start_date.date()} to {end_date.date()}")

    # Find all orders where the formula doesn't match
    # NOTE: We check ALL orders including is_manual_update = 1, because if the
    # formula doesn't match, the our_buy_price is incorrect and needs fixing
    query = f"""
        SELECT
            id,
            order_id,
            external_order_id,
            our_buy_price,
            supplier_price,
            supplier_shipping,
            supplier_handling,
            sales_price,
            buy_price_source,
            is_manual_update
        FROM orders_items_margin_detailed
        WHERE our_buy_price IS NOT NULL
          AND our_buy_price > 0
          AND is_active = 1
          {date_filter}
    """

    cursor.execute(query, date_params)
    all_orders = cursor.fetchall()

    logger.info(f"Checking {len(all_orders)} orders...")

    # Filter to find orders with incorrect our_buy_price
    incorrect_orders = []
    for order in all_orders:
        # Calculate expected buy price
        supplier_price = float(order['supplier_price']) if order['supplier_price'] else 0.0
        supplier_shipping = float(order['supplier_shipping']) if order['supplier_shipping'] else 0.0
        supplier_handling = float(order['supplier_handling']) if order['supplier_handling'] else 0.0

        expected_buy_price = supplier_price + supplier_shipping + supplier_handling
        actual_buy_price = float(order['our_buy_price'])

        # Check if they differ by more than tolerance
        difference = abs(actual_buy_price - expected_buy_price)

        if difference > PRICE_TOLERANCE:
            order['expected_buy_price'] = expected_buy_price
            order['difference'] = difference
            incorrect_orders.append(order)

    if not incorrect_orders:
        logger.info("✓ All orders have correct buy prices")
        cursor.close()
        conn.close()
        return []

    logger.info(f"Found {len(incorrect_orders)} orders with incorrect buy prices")

    # Sort by difference to show worst cases first
    incorrect_orders.sort(key=lambda x: x['difference'], reverse=True)

    # Log examples (top 10 worst cases)
    logger.info(f"\nTop 10 worst cases:")
    for i, order in enumerate(incorrect_orders[:10]):
        logger.info(f"  {i+1}. Order {order['external_order_id']}: "
                   f"current=${order['our_buy_price']:.2f}, "
                   f"should be=${order['expected_buy_price']:.2f}, "
                   f"diff=${order['difference']:.2f}, "
                   f"source={order['buy_price_source']}")

    # Fix the orders
    fixed_count = 0
    fixed_ids = []

    for order in incorrect_orders:
        correct_buy_price = order['expected_buy_price']

        if not DRY_RUN:
            # Fix our_buy_price to match the formula
            # NOTE: We do NOT modify supplier_price, supplier_shipping, or supplier_handling
            # We override is_manual_update because formula correctness is critical
            update_query = """
                UPDATE orders_items_margin_detailed
                SET our_buy_price = %s,
                    modified_at = NOW()
                WHERE id = %s
            """
            cursor.execute(update_query, (correct_buy_price, order['id']))
            fixed_count += 1
            fixed_ids.append(order['id'])
        else:
            fixed_count += 1
            fixed_ids.append(order['id'])

    if not DRY_RUN:
        conn.commit()

    logger.info(f"\n{'[DRY RUN] Would fix' if DRY_RUN else 'Fixed'} {fixed_count} orders")

    cursor.close()
    conn.close()
    return fixed_ids

def recalculate_cogs_for_fixed_orders(fixed_order_ids):
    """
    Recalculate COGS (good_sold_cost) for orders that were just fixed

    COGS Formula:
    good_sold_cost = our_buy_price + commission_and_fee + payment_processor_fee
                     - supplier_credits

    Args:
        fixed_order_ids: List of order IDs that were fixed
    """
    if not fixed_order_ids:
        return 0

    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    logger.info("\n" + "="*80)
    logger.info("RECALCULATING COGS FOR FIXED ORDERS")
    logger.info("="*80)

    # Get all orders that were just fixed
    placeholders = ','.join(['%s'] * len(fixed_order_ids))
    query = f"""
        SELECT
            id,
            external_order_id,
            our_buy_price,
            commission_and_fee,
            payment_processor_fee,
            supplier_credits,
            sales_price,
            current_status,
            good_sold_cost as old_cogs
        FROM orders_items_margin_detailed
        WHERE id IN ({placeholders})
          AND is_active = 1
    """

    cursor.execute(query, fixed_order_ids)
    orders = cursor.fetchall()

    if not orders:
        logger.info("No fixed orders found to recalculate COGS")
        cursor.close()
        conn.close()
        return 0

    logger.info(f"Recalculating COGS for {len(orders)} fixed orders")

    # Define status categories (same as Script 7)
    TOTAL_LOSS_STATUSES = [48]
    REFUND_STATUSES = [24, 27, 34, 41, 52]
    CANCELLATION_STATUSES = [7, 33, 49, 51]

    updated_count = 0
    for order in orders:
        # Extract values
        sales_price = float(order['sales_price']) if order['sales_price'] else 0.0
        our_buy_price = float(order['our_buy_price']) if order['our_buy_price'] else 0.0
        commission = float(order['commission_and_fee']) if order['commission_and_fee'] else 0.0
        processor_fee = float(order['payment_processor_fee']) if order['payment_processor_fee'] else 0.0
        supplier_credits = float(order['supplier_credits']) if order['supplier_credits'] else 0.0
        current_status = int(order['current_status']) if order['current_status'] else 0

        # Calculate COGS and profit based on status
        if current_status in TOTAL_LOSS_STATUSES:
            # Total Loss: full costs, $0 revenue
            new_cogs = our_buy_price + commission + processor_fee - supplier_credits
            gross_profit = 0 - new_cogs
            gross_margin = (gross_profit / sales_price * 100) if sales_price > 0 else -100

        elif current_status in REFUND_STATUSES:
            # Regular Refunds: fees only, $0 revenue
            new_cogs = commission + processor_fee - supplier_credits
            gross_profit = 0 - new_cogs
            gross_margin = (gross_profit / sales_price * 100) if sales_price > 0 else 0

        elif current_status in CANCELLATION_STATUSES:
            # Cancellations: fees only, $0 revenue
            new_cogs = commission + processor_fee
            gross_profit = 0 - new_cogs
            gross_margin = (gross_profit / sales_price * 100) if sales_price > 0 else 0

        else:
            # Normal orders: standard calculation
            new_cogs = our_buy_price + commission + processor_fee - supplier_credits
            gross_profit = sales_price - new_cogs
            gross_margin = (gross_profit / sales_price * 100) if sales_price > 0 else 0

        if not DRY_RUN:
            update_query = """
                UPDATE orders_items_margin_detailed
                SET good_sold_cost = %s,
                    gross_profit = %s,
                    gross_margin = %s,
                    modified_at = NOW()
                WHERE id = %s
            """
            cursor.execute(update_query, (new_cogs, gross_profit, gross_margin, order['id']))
            updated_count += 1

    if not DRY_RUN:
        conn.commit()

    logger.info(f"{'[DRY RUN] Would recalculate' if DRY_RUN else 'Recalculated'} COGS for {updated_count} orders")

    cursor.close()
    conn.close()
    return updated_count

def fix_incorrect_gross_profit_margin():
    """
    Fix ALL orders where gross_profit or gross_margin don't match the correct calculation
    This runs independently of buy_price fixes to catch externally updated orders

    Correct formulas:
    COGS = our_buy_price + commission_and_fee + payment_processor_fee - supplier_credits + loss_amount
    Gross Profit = sales_price - COGS
    Gross Margin % = (Gross Profit / sales_price) × 100
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    logger.info("\n" + "="*80)
    logger.info("CHECKING GROSS PROFIT AND MARGIN ACCURACY FOR ALL ORDERS")
    logger.info("="*80)

    # Calculate date range
    if DATE_START and DATE_END:
        date_filter = "AND date_placed >= %s AND date_placed < %s"
        date_params = [DATE_START, DATE_END]
    else:
        end_date = datetime.now()
        start_date = end_date - timedelta(days=DATE_RANGE_DAYS)
        date_filter = "AND date_placed >= %s AND date_placed < %s"
        date_params = [start_date, end_date]
        logger.info(f"Date range: {start_date.date()} to {end_date.date()}")

    # Get ALL orders in date range (regardless of is_active status)
    query = f"""
        SELECT
            id,
            external_order_id,
            sales_price,
            our_buy_price,
            commission_and_fee,
            payment_processor_fee,
            supplier_credits,
            good_sold_cost,
            gross_profit,
            gross_margin,
            is_active,
            current_status
        FROM orders_items_margin_detailed
        WHERE 1=1
          {date_filter}
    """

    cursor.execute(query, date_params)
    all_orders = cursor.fetchall()

    logger.info(f"Checking {len(all_orders)} orders for correct gross profit and margin...")

    # Define status categories (same as Script 7)
    TOTAL_LOSS_STATUSES = [48]
    REFUND_STATUSES = [24, 27, 34, 41, 52]
    CANCELLATION_STATUSES = [7, 33, 49, 51]

    # Find orders with incorrect gross_profit or gross_margin
    incorrect_orders = []
    for order in all_orders:
        # Extract values
        sales_price = float(order['sales_price']) if order['sales_price'] else 0.0
        our_buy_price = float(order['our_buy_price']) if order['our_buy_price'] else 0.0
        commission = float(order['commission_and_fee']) if order['commission_and_fee'] else 0.0
        processor_fee = float(order['payment_processor_fee']) if order['payment_processor_fee'] else 0.0
        supplier_credits = float(order['supplier_credits']) if order['supplier_credits'] else 0.0
        current_status = int(order['current_status']) if order['current_status'] else 0

        # Calculate expected values based on order status
        if current_status in TOTAL_LOSS_STATUSES:
            # Total Loss: full costs, $0 revenue
            expected_cogs = our_buy_price + commission + processor_fee - supplier_credits
            expected_gross_profit = 0 - expected_cogs
            expected_gross_margin = (expected_gross_profit / sales_price * 100) if sales_price > 0 else -100

        elif current_status in REFUND_STATUSES:
            # Regular Refunds: fees only, $0 revenue
            expected_cogs = commission + processor_fee - supplier_credits
            expected_gross_profit = 0 - expected_cogs
            expected_gross_margin = (expected_gross_profit / sales_price * 100) if sales_price > 0 else 0

        elif current_status in CANCELLATION_STATUSES:
            # Cancellations: fees only, $0 revenue
            expected_cogs = commission + processor_fee
            expected_gross_profit = 0 - expected_cogs
            expected_gross_margin = (expected_gross_profit / sales_price * 100) if sales_price > 0 else 0

        else:
            # Normal orders: standard calculation
            expected_cogs = our_buy_price + commission + processor_fee - supplier_credits
            expected_gross_profit = sales_price - expected_cogs
            expected_gross_margin = (expected_gross_profit / sales_price * 100) if sales_price > 0 else 0

        # Get current values
        current_cogs = float(order['good_sold_cost']) if order['good_sold_cost'] else 0.0
        current_gross_profit = float(order['gross_profit']) if order['gross_profit'] else 0.0
        current_gross_margin = float(order['gross_margin']) if order['gross_margin'] else 0.0

        # Check if any value is incorrect (allowing for small floating point differences)
        cogs_diff = abs(current_cogs - expected_cogs)
        profit_diff = abs(current_gross_profit - expected_gross_profit)
        margin_diff = abs(current_gross_margin - expected_gross_margin)

        if cogs_diff > PRICE_TOLERANCE or profit_diff > PRICE_TOLERANCE or margin_diff > 0.01:
            order['expected_cogs'] = expected_cogs
            order['expected_gross_profit'] = expected_gross_profit
            order['expected_gross_margin'] = expected_gross_margin
            order['cogs_diff'] = cogs_diff
            order['profit_diff'] = profit_diff
            order['margin_diff'] = margin_diff
            incorrect_orders.append(order)

    if not incorrect_orders:
        logger.info("✓ All orders have correct COGS, gross profit, and gross margin")
        cursor.close()
        conn.close()
        return 0

    logger.info(f"Found {len(incorrect_orders)} orders with incorrect COGS/profit/margin")

    # Sort by profit difference to show worst cases first
    incorrect_orders.sort(key=lambda x: x['profit_diff'], reverse=True)

    # Log examples (top 10 worst cases)
    logger.info(f"\nTop 10 worst cases:")
    for i, order in enumerate(incorrect_orders[:10]):
        order_id = order['external_order_id'] if order['external_order_id'] else f"ID:{order['id']}"
        current_profit = order['gross_profit'] if order['gross_profit'] is not None else 0.0
        logger.info(f"  {i+1}. Order {order_id}: "
                   f"current profit=${current_profit:.2f}, "
                   f"should be=${order['expected_gross_profit']:.2f}, "
                   f"diff=${order['profit_diff']:.2f}")

    # Fix the orders
    fixed_count = 0
    for order in incorrect_orders:
        if not DRY_RUN:
            update_query = """
                UPDATE orders_items_margin_detailed
                SET good_sold_cost = %s,
                    gross_profit = %s,
                    gross_margin = %s,
                    modified_at = NOW()
                WHERE id = %s
            """
            cursor.execute(update_query, (
                order['expected_cogs'],
                order['expected_gross_profit'],
                order['expected_gross_margin'],
                order['id']
            ))
            fixed_count += 1
        else:
            fixed_count += 1

    if not DRY_RUN:
        conn.commit()

    logger.info(f"\n{'[DRY RUN] Would fix' if DRY_RUN else 'Fixed'} {fixed_count} orders")

    cursor.close()
    conn.close()
    return fixed_count

# ============================================================================
# MAIN
# ============================================================================

def main():
    """Main execution"""
    start_time = datetime.now()

    # Setup logging
    timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
    log_file = os.path.join(LOG_DIR, f'fix_buy_prices_{timestamp}.log')

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

    logger.info("="*80)
    logger.info("SCRIPT 8: FIX INCORRECT BUY PRICES")
    logger.info(f"{'DRY RUN MODE' if DRY_RUN else 'LIVE MODE'}")
    logger.info("="*80)
    logger.info(f"Log file: {log_file}")
    logger.info(f"Started at: {start_time}")

    # Calculate date range if not specified
    global DATE_START, DATE_END
    if not DATE_START or not DATE_END:
        end_date = datetime.now()
        start_date = end_date - timedelta(days=DATE_RANGE_DAYS)
        DATE_START = start_date
        DATE_END = end_date

    # Fix incorrect buy prices
    fixed_ids = fix_incorrect_buy_prices()
    total_fixed = len(fixed_ids)

    # Recalculate COGS for fixed orders
    if total_fixed > 0:
        cogs_updated = recalculate_cogs_for_fixed_orders(fixed_ids)
    else:
        cogs_updated = 0

    # Fix incorrect gross profit and margin for ALL orders (independent check)
    profit_margin_fixed = fix_incorrect_gross_profit_margin()

    # Summary
    logger.info("\n" + "="*80)
    logger.info("SUMMARY")
    logger.info("="*80)
    logger.info(f"Orders with incorrect buy prices: {total_fixed}")
    logger.info(f"COGS recalculated for buy price fixes: {cogs_updated}")
    logger.info(f"Orders with incorrect COGS/profit/margin: {profit_margin_fixed}")
    logger.info("="*80)

    end_time = datetime.now()
    duration = (end_time - start_time).total_seconds()

    logger.info(f"\nCompleted at: {end_time}")
    logger.info(f"Total duration: {duration:.2f} seconds")

if __name__ == '__main__':
    main()