#!/usr/bin/env python3
"""
Script 9: Sync Additional Costs from orders_additional_costs to orders_items_margin_detailed

This script syncs costs and credits from the orders_additional_costs table to the
appropriate columns in orders_items_margin_detailed:

COSTS (increase COGS - stored as positive values):
- additional_cost: Replacement Part, Repair fees, AMZ Adj, Rerouted fees, etc.
- label_cost: Return Label Cost

CREDITS (reduce COGS - stored as positive values, subtracted in COGS formula):
- supplier_credits: Supplier Credit
- carrier_credits: Carrier Credit

Cost Type Mapping:
─────────────────────────────────────────────────────────────────────────────
cost_type                      → Column              → Impact on COGS
─────────────────────────────────────────────────────────────────────────────
Return Label Cost              → label_cost          → ADDS to COGS
Supplier Credit                → supplier_credits    → SUBTRACTS from COGS
Carrier Credit                 → carrier_credits     → SUBTRACTS from COGS
Replacement Part               → additional_cost     → ADDS to COGS
Repair Fee (all variations)    → additional_cost     → ADDS to COGS
AMZ Adj (all variations)       → additional_cost     → ADDS to COGS
Amazon Adjustment (all)        → additional_cost     → ADDS to COGS
Rerouted/Reconsignment fees    → additional_cost     → ADDS to COGS
All other costs                → additional_cost     → ADDS to COGS
─────────────────────────────────────────────────────────────────────────────

Note: Multiple costs per order_item_id are summed together.
"""

import sys
import os
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

from db_config import DB_CONFIG
import mysql.connector
from datetime import datetime, timedelta
import logging

# Setup logging
log_filename = f"logs/additional_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 = logging.getLogger(__name__)

# Cost type mappings
LABEL_COST_TYPES = [
    'Return Label Cost',
]

SUPPLIER_CREDIT_TYPES = [
    'Supplier Credit',
]

CARRIER_CREDIT_TYPES = [
    'Carrier Credit',
]

# Everything else goes to additional_cost (including variations of repair fees, AMZ adj, etc.)


def get_db_connection():
    return mysql.connector.connect(**DB_CONFIG['central'])


def classify_cost_type(cost_type):
    """
    Classify a cost_type string into the appropriate column.
    Returns: ('column_name', 'impact') where impact is 'add' or 'subtract'
    """
    if cost_type is None:
        return ('additional_cost', 'add')

    cost_type_lower = cost_type.lower().strip()

    # Check for label cost
    if 'label' in cost_type_lower:
        return ('label_cost', 'add')

    # Check for supplier credit
    if 'supplier' in cost_type_lower and 'credit' in cost_type_lower:
        return ('supplier_credits', 'subtract')

    # Check for carrier credit
    if 'carrier' in cost_type_lower and 'credit' in cost_type_lower:
        return ('carrier_credits', 'subtract')

    # Everything else is additional cost
    return ('additional_cost', 'add')


def sync_additional_costs():
    """Sync all additional costs from orders_additional_costs to orders_items_margin_detailed"""

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

    logger.info("=" * 80)
    logger.info("SCRIPT 9: SYNC ADDITIONAL COSTS")
    logger.info("=" * 80)
    logger.info(f"Log file: {log_filename}")
    logger.info("")

    # First, get all unique cost types to show mapping
    cursor.execute("SELECT DISTINCT cost_type FROM orders_additional_costs ORDER BY cost_type")
    all_types = cursor.fetchall()

    logger.info("Cost Type Mapping:")
    logger.info("-" * 60)
    for row in all_types:
        cost_type = row['cost_type']
        column, impact = classify_cost_type(cost_type)
        logger.info(f"  '{cost_type}' → {column} ({impact})")
    logger.info("-" * 60)
    logger.info("")

    # Get all additional costs grouped by item_id and classified column
    cursor.execute("""
        SELECT
            oac.item_id,
            oac.cost_type,
            CAST(oac.cost AS DECIMAL(10,2)) as cost_amount
        FROM orders_additional_costs oac
        WHERE oac.item_id IS NOT NULL
        ORDER BY oac.item_id
    """)

    all_costs = cursor.fetchall()
    logger.info(f"Found {len(all_costs)} total records in orders_additional_costs")

    # Group costs by item_id and column
    item_costs = {}
    for row in all_costs:
        item_id = str(row['item_id'])
        cost_type = row['cost_type']
        cost_amount = float(row['cost_amount']) if row['cost_amount'] else 0

        column, impact = classify_cost_type(cost_type)

        if item_id not in item_costs:
            item_costs[item_id] = {
                'additional_cost': 0,
                'label_cost': 0,
                'supplier_credits': 0,
                'carrier_credits': 0
            }

        item_costs[item_id][column] += cost_amount

    logger.info(f"Aggregated costs for {len(item_costs)} unique order items")
    logger.info("")

    # Summary by column
    total_additional = sum(v['additional_cost'] for v in item_costs.values())
    total_label = sum(v['label_cost'] for v in item_costs.values())
    total_supplier_credits = sum(v['supplier_credits'] for v in item_costs.values())
    total_carrier_credits = sum(v['carrier_credits'] for v in item_costs.values())

    logger.info("Total Amounts to Sync:")
    logger.info(f"  additional_cost:   ${total_additional:,.2f}")
    logger.info(f"  label_cost:        ${total_label:,.2f}")
    logger.info(f"  supplier_credits:  ${total_supplier_credits:,.2f}")
    logger.info(f"  carrier_credits:   ${total_carrier_credits:,.2f}")
    logger.info("")

    # Update orders_items_margin_detailed
    logger.info("Updating orders_items_margin_detailed...")

    updated = 0
    not_found = 0
    batch_size = 100
    item_ids = list(item_costs.keys())

    for i in range(0, len(item_ids), batch_size):
        batch = item_ids[i:i+batch_size]

        for item_id in batch:
            costs = item_costs[item_id]

            # Check if the order exists in detailed table
            cursor.execute("""
                SELECT order_item_id FROM orders_items_margin_detailed
                WHERE order_item_id = %s
            """, (item_id,))

            if cursor.fetchone():
                cursor.execute("""
                    UPDATE orders_items_margin_detailed
                    SET
                        additional_cost = %s,
                        label_cost = %s,
                        supplier_credits = %s,
                        carrier_credits = %s
                    WHERE order_item_id = %s
                """, (
                    costs['additional_cost'],
                    costs['label_cost'],
                    costs['supplier_credits'],
                    costs['carrier_credits'],
                    item_id
                ))
                updated += 1
            else:
                not_found += 1

        conn.commit()
        logger.info(f"Progress: {min(i + batch_size, len(item_ids))}/{len(item_ids)} items processed")

    logger.info("")
    logger.info("=" * 80)
    logger.info("SUMMARY")
    logger.info("=" * 80)
    logger.info(f"Total records in orders_additional_costs: {len(all_costs)}")
    logger.info(f"Unique order items with costs: {len(item_costs)}")
    logger.info(f"Updated in orders_items_margin_detailed: {updated}")
    logger.info(f"Not found in detailed table: {not_found}")
    logger.info("")
    logger.info("Amounts Synced:")
    logger.info(f"  additional_cost (adds to COGS):   ${total_additional:,.2f}")
    logger.info(f"  label_cost (adds to COGS):        ${total_label:,.2f}")
    logger.info(f"  supplier_credits (reduces COGS):  ${total_supplier_credits:,.2f}")
    logger.info(f"  carrier_credits (reduces COGS):   ${total_carrier_credits:,.2f}")
    logger.info("=" * 80)

    cursor.close()
    conn.close()

    return {
        'updated': updated,
        'not_found': not_found,
        'additional_cost': total_additional,
        'label_cost': total_label,
        'supplier_credits': total_supplier_credits,
        'carrier_credits': total_carrier_credits
    }


if __name__ == '__main__':
    result = sync_additional_costs()
    print(f"\nCompleted! Updated {result['updated']} orders.")