#!/usr/bin/env python3
"""
Script 7: Calculate Final COGS (Cost of Goods Sold)
Calculates the final good_sold_cost, gross profit, and gross margin using status-aware formulas.

COGS FORMULA (matches frontend DetailedProfitability.php):
  COGS = our_buy_price
       + (sales_price * channel_commission / 100)   -- commission calculated from %
       + channel_advertisement_fee_amount           -- ad fees
       + payment_processor_fee                      -- payment processing
       + repayment_processor_fees                   -- refund processing fees
       + additional_cost                            -- replacement parts, repairs, etc.
       + amazon_additional_cost (Amazon only)       -- Amazon-specific costs
       + label_cost                                 -- return labels
       - supplier_credits                           -- credits reduce COGS
       - carrier_credits                            -- credits reduce COGS

NORMAL ORDERS (Shipped, Ordered, etc.):
  Gross Profit = sales_price - COGS
  Gross Margin % = (Gross Profit / sales_price) × 100

TOTAL LOSS REFUND (Status 48):
  We lose the part AND refund the customer - full costs, no revenue
  Gross Profit = $0 (refunded revenue) - COGS = negative (pure loss)

REGULAR REFUNDS (Status 24, 27, 34, 41, 52):
  We keep the part but refund customer - only fee costs, no revenue
  COGS excludes our_buy_price (we got the part back)

CANCELLATIONS (Status 7, 33, 49, 51):
  Just pay the fee - no product cost, no revenue

Cron-ready: Runs on rolling 120-day window, always recalculates COGS
"""

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 60-day window
DATE_START = None  # Will be calculated as 60 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

logger = logging.getLogger(__name__)

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

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

# ============================================================================
# COGS CALCULATION FUNCTION
# ============================================================================

def calculate_final_cogs():
    """
    Calculate final COGS for all orders in the date range

    Formula: our_buy_price + commission_and_fee + payment_processor_fee +
             supplier_shipping - supplier_credits + loss_amount
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    logger.info("=" * 80)
    logger.info("Processing: FINAL COGS CALCULATION (matches frontend formula)")
    logger.info("  Formula: our_buy_price + (sales_price * commission%)")
    logger.info("           + ad_fees + payment_processor_fee + repayment_fees")
    logger.info("           + additional_cost + amazon_additional + label_cost")
    logger.info("           - supplier_credits - carrier_credits")
    logger.info("  Note: our_buy_price already includes shipping & handling")
    logger.info("=" * 80)

    # Get all orders in date range
    query = """
        SELECT
            id,
            external_order_id,
            sales_channel,
            sales_price,
            our_buy_price,
            channel_commission,
            channel_advertisement_fee_amount,
            commission_and_fee,
            payment_processor_fee,
            repayment_processor_fees,
            amazon_additional_cost,
            supplier_shipping,
            supplier_handling,
            supplier_credits,
            carrier_credits,
            additional_cost,
            label_cost,
            current_status,
            item_status,
            good_sold_cost as current_cogs,
            gross_profit as current_gross_profit,
            gross_margin as current_gross_margin
        FROM orders_items_margin_detailed
        WHERE date_placed >= %s
          AND date_placed < %s
        ORDER BY sales_channel, id
    """

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

    logger.info(f"Found {len(items)} orders to calculate COGS")

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

    updated = 0
    unchanged = 0
    missing_data = 0
    by_channel = {}

    update_query = """
        UPDATE orders_items_margin_detailed
        SET good_sold_cost = %s,
            gross_profit = %s,
            gross_margin = %s
        WHERE id = %s
    """

    for item in items:
        channel = item['sales_channel']
        if channel not in by_channel:
            by_channel[channel] = {
                'count': 0,
                'revenue': 0,
                'total_cogs': 0,
                'buy_price': 0,
                'commission': 0,
                'ad_fees': 0,
                'processor_fee': 0,
                'repayment_fee': 0,
                'amazon_additional': 0,
                'shipping': 0,
                'handling': 0,
                'additional_cost': 0,
                'label_cost': 0,
                'supplier_credits': 0,
                'carrier_credits': 0
            }

        # Extract values (handle NULL as 0)
        our_buy_price = float(item['our_buy_price']) if item['our_buy_price'] is not None else 0
        sales_price = float(item['sales_price']) if item['sales_price'] is not None else 0
        channel_commission = float(item['channel_commission']) if item['channel_commission'] is not None else 0
        channel_advertisement_fee_amount = float(item['channel_advertisement_fee_amount']) if item['channel_advertisement_fee_amount'] is not None else 0
        payment_processor_fee = float(item['payment_processor_fee']) if item['payment_processor_fee'] is not None else 0
        repayment_processor_fees = float(item['repayment_processor_fees']) if item['repayment_processor_fees'] is not None else 0
        amazon_additional_cost = float(item['amazon_additional_cost']) if item['amazon_additional_cost'] is not None else 0
        supplier_shipping = float(item['supplier_shipping']) if item['supplier_shipping'] is not None else 0
        supplier_handling = float(item['supplier_handling']) if item['supplier_handling'] is not None else 0
        supplier_credits = float(item['supplier_credits']) if item['supplier_credits'] is not None else 0
        carrier_credits = float(item['carrier_credits']) if item['carrier_credits'] is not None else 0
        additional_cost = float(item['additional_cost']) if item['additional_cost'] is not None else 0
        label_cost = float(item['label_cost']) if item['label_cost'] is not None else 0
        current_status = int(item['current_status']) if item['current_status'] is not None else 0

        # Calculate commission from percentage (like frontend does)
        calculated_commission = sales_price * (channel_commission / 100)

        # Amazon additional cost only applies to Amazon channel
        amazon_cost = amazon_additional_cost if item['sales_channel'] and item['sales_channel'].lower() == 'amazon' else 0

        # Define status categories
        # Total Loss: We lose the part AND refund customer - no revenue, full costs
        TOTAL_LOSS_STATUSES = [48]  # Total Loss Refund

        # Regular Refunds: We keep the part, but still pay fees - no revenue
        REFUND_STATUSES = [24, 27, 34, 41, 52]  # Return Refunded, Backorder Refunded, Refunded, For Refund, Local Refunded

        # Cancellations: We just pay the fee - no product cost or revenue
        CANCELLATION_STATUSES = [7, 33, 49, 51]  # Cancelled, Please Cancel, Cancelled - Not Purchased, Local Cancelled

        # Calculate COGS based on order status
        # NOTE: our_buy_price already includes supplier_shipping and supplier_handling
        # Do NOT add them separately to avoid double-counting

        if current_status in TOTAL_LOSS_STATUSES:
            # Total Loss: We lose part + pay all fees, customer gets refund
            # COGS = product cost + all fees + additional costs - credits
            new_cogs = (
                our_buy_price +
                calculated_commission +
                channel_advertisement_fee_amount +
                payment_processor_fee +
                repayment_processor_fees +
                additional_cost +
                amazon_cost +
                label_cost -
                supplier_credits -
                carrier_credits
            )
            # Gross profit = $0 revenue - COGS (negative, pure loss)
            gross_profit = 0 - new_cogs
            # Gross margin as percentage of original sale price (to show impact)
            gross_margin = (gross_profit / sales_price * 100) if sales_price > 0 else -100

        elif current_status in REFUND_STATUSES:
            # Regular Refunds: We keep the part but pay fees, customer gets refund
            # COGS = fees + additional costs - credits (no product cost - we got the part back)
            new_cogs = (
                calculated_commission +
                channel_advertisement_fee_amount +
                payment_processor_fee +
                repayment_processor_fees +
                additional_cost +
                amazon_cost +
                label_cost -
                supplier_credits -
                carrier_credits
            )
            # Gross profit = $0 revenue - COGS (negative, the fees we paid)
            gross_profit = 0 - new_cogs
            # Gross margin as percentage of original sale price
            gross_margin = (gross_profit / sales_price * 100) if sales_price > 0 else 0

        elif current_status in CANCELLATION_STATUSES:
            # Cancellations: Just pay the fee (no product cost, no shipping, customer gets refund)
            # COGS = fees + additional costs - carrier credits (no supplier credits for cancellations)
            new_cogs = (
                calculated_commission +
                channel_advertisement_fee_amount +
                payment_processor_fee +
                repayment_processor_fees +
                additional_cost +
                amazon_cost +
                label_cost -
                carrier_credits
            )
            # Gross profit = $0 revenue - COGS (negative, the fees we paid)
            gross_profit = 0 - new_cogs
            # Gross margin as percentage of original sale price
            gross_margin = (gross_profit / sales_price * 100) if sales_price > 0 else 0

        else:
            # Normal orders: Standard COGS calculation (matches frontend formula)
            new_cogs = (
                our_buy_price +
                calculated_commission +
                channel_advertisement_fee_amount +
                payment_processor_fee +
                repayment_processor_fees +
                additional_cost +
                amazon_cost +
                label_cost -
                supplier_credits -
                carrier_credits
            )
            # Calculate Gross Profit and Gross Margin
            gross_profit = sales_price - new_cogs
            gross_margin = (gross_profit / sales_price * 100) if sales_price > 0 else 0

        current_cogs = float(item['current_cogs']) if item['current_cogs'] is not None else 0
        current_gross_profit = float(item['current_gross_profit']) if item['current_gross_profit'] is not None else None
        current_gross_margin = float(item['current_gross_margin']) if item['current_gross_margin'] is not None else None

        # Check if needs update: COGS changed OR legacy fields are NULL OR legacy fields are incorrect
        # OR if it's a refund/cancellation status (always recalculate these)
        cogs_changed = abs(new_cogs - current_cogs) >= 0.01
        legacy_fields_null = (current_gross_profit is None or current_gross_margin is None)
        legacy_fields_incorrect = False
        if current_gross_profit is not None and current_gross_margin is not None:
            legacy_fields_incorrect = (abs(gross_profit - current_gross_profit) >= 0.01 or
                                       abs(gross_margin - current_gross_margin) >= 0.01)

        # Force update for all refund/cancellation status orders
        is_special_status = current_status in (TOTAL_LOSS_STATUSES + REFUND_STATUSES + CANCELLATION_STATUSES)

        if cogs_changed or legacy_fields_null or legacy_fields_incorrect or is_special_status:
            if not DRY_RUN:
                cursor.execute(update_query, (new_cogs, gross_profit, gross_margin, item['id']))

            updated += 1

            # Show first 10 updates per channel
            if by_channel[channel]['count'] < 10:
                # Determine status type for logging
                status_type = "Normal"
                if current_status in TOTAL_LOSS_STATUSES:
                    status_type = f"Total Loss ({item['item_status']})"
                elif current_status in REFUND_STATUSES:
                    status_type = f"Refund ({item['item_status']})"
                elif current_status in CANCELLATION_STATUSES:
                    status_type = f"Cancelled ({item['item_status']})"

                logger.info(f"  {'[DRY RUN] ' if DRY_RUN else ''}{channel} - Order {item['external_order_id']} [{status_type}]:")
                logger.info(f"    Sales: ${sales_price:.2f}")
                logger.info(f"    Buy Price (incl. shipping): ${our_buy_price:.2f}")
                logger.info(f"    Commission ({channel_commission}%): ${calculated_commission:.2f}")
                logger.info(f"    Ad Fees: ${channel_advertisement_fee_amount:.2f}")
                logger.info(f"    Payment Processor: ${payment_processor_fee:.2f}")
                logger.info(f"    Repayment Fees: ${repayment_processor_fees:.2f}")
                if amazon_cost > 0:
                    logger.info(f"    Amazon Additional: ${amazon_cost:.2f}")
                logger.info(f"    Additional Costs: +${additional_cost:.2f}")
                logger.info(f"    Label Costs: +${label_cost:.2f}")
                logger.info(f"    Supplier Credits: -${supplier_credits:.2f}")
                logger.info(f"    Carrier Credits: -${carrier_credits:.2f}")
                logger.info(f"    → COGS: ${new_cogs:.2f} (was ${current_cogs:.2f})")
                logger.info(f"    → Gross Profit: ${gross_profit:.2f} ({gross_margin:.2f}%)")
        else:
            unchanged += 1

        # Track by channel
        by_channel[channel]['count'] += 1
        by_channel[channel]['revenue'] += sales_price
        by_channel[channel]['total_cogs'] += new_cogs
        by_channel[channel]['buy_price'] += our_buy_price
        by_channel[channel]['commission'] += calculated_commission
        by_channel[channel]['ad_fees'] += channel_advertisement_fee_amount
        by_channel[channel]['processor_fee'] += payment_processor_fee
        by_channel[channel]['repayment_fee'] += repayment_processor_fees
        by_channel[channel]['amazon_additional'] += amazon_cost
        by_channel[channel]['shipping'] += supplier_shipping
        by_channel[channel]['handling'] += supplier_handling
        by_channel[channel]['additional_cost'] += additional_cost
        by_channel[channel]['label_cost'] += label_cost
        by_channel[channel]['supplier_credits'] += supplier_credits
        by_channel[channel]['carrier_credits'] += carrier_credits

        # Check for missing critical data
        if our_buy_price == 0:
            missing_data += 1

    if not DRY_RUN:
        conn.commit()

    # Summary by channel
    logger.info(f"\n{'=' * 80}")
    logger.info(f"COGS CALCULATION SUMMARY BY CHANNEL")
    logger.info(f"{'=' * 80}")

    total_revenue = 0
    total_cogs = 0
    total_margin = 0

    for channel, stats in sorted(by_channel.items()):
        margin = stats['revenue'] - stats['total_cogs']
        margin_pct = (margin / stats['revenue'] * 100) if stats['revenue'] > 0 else 0

        logger.info(f"\n{channel}:")
        logger.info(f"  Orders: {stats['count']}")
        logger.info(f"  Revenue: ${stats['revenue']:,.2f}")
        logger.info(f"  COGS Breakdown:")
        logger.info(f"    Buy Price (incl. shipping): ${stats['buy_price']:,.2f}")
        logger.info(f"    Commission: ${stats['commission']:,.2f}")
        logger.info(f"    Ad Fees: ${stats['ad_fees']:,.2f}")
        logger.info(f"    Payment Processor: ${stats['processor_fee']:,.2f}")
        logger.info(f"    Repayment Fees: ${stats['repayment_fee']:,.2f}")
        if stats['amazon_additional'] > 0:
            logger.info(f"    Amazon Additional: ${stats['amazon_additional']:,.2f}")
        logger.info(f"    Additional Costs: +${stats['additional_cost']:,.2f}")
        logger.info(f"    Label Costs: +${stats['label_cost']:,.2f}")
        logger.info(f"    Supplier Credits: -${stats['supplier_credits']:,.2f}")
        logger.info(f"    Carrier Credits: -${stats['carrier_credits']:,.2f}")
        logger.info(f"  Total COGS: ${stats['total_cogs']:,.2f}")
        logger.info(f"  Gross Margin: ${margin:,.2f} ({margin_pct:.2f}%)")

        total_revenue += stats['revenue']
        total_cogs += stats['total_cogs']
        total_margin += margin

    overall_margin_pct = (total_margin / total_revenue * 100) if total_revenue > 0 else 0

    logger.info(f"\n{'=' * 80}")
    logger.info(f"OVERALL TOTALS")
    logger.info(f"{'=' * 80}")
    logger.info(f"Total Orders: {len(items)}")
    logger.info(f"Updated: {updated}")
    logger.info(f"Unchanged: {unchanged}")
    logger.info(f"Missing buy price: {missing_data}")
    logger.info(f"\nTotal Revenue: ${total_revenue:,.2f}")
    logger.info(f"Total COGS: ${total_cogs:,.2f}")
    logger.info(f"Total Gross Margin: ${total_margin:,.2f} ({overall_margin_pct:.2f}%)")
    logger.info(f"{'=' * 80}\n")

    if missing_data > 0:
        logger.warning(f"⚠️  {missing_data} orders have NULL/zero buy price - COGS may be understated")

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

    cursor.close()
    conn.close()

    return {
        'total_orders': len(items),
        'updated': updated,
        'unchanged': unchanged,
        'missing_data': missing_data,
        'total_revenue': total_revenue,
        'total_cogs': total_cogs,
        'total_margin': total_margin,
        'margin_pct': overall_margin_pct,
        'by_channel': by_channel
    }

# ============================================================================
# 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"FINAL COGS CALCULATION - {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")

    result = calculate_final_cogs()

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

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