#!/usr/bin/env python3
"""
Script 6: Update Commission and Fees for All Channels (Monthly .env version)
Reads monthly fee data from .env.{month} files and updates commission_and_fee

Monthly .env files:
- .env.08 = August 2025 values
- .env.09 = September 2025 values
- etc.

For months without .env files:
- Estimates fees proportionally based on previous month
- Proportion = current_month_revenue / previous_month_revenue

Channel Models:
- Web: 0% commission (merchant fees in payment processor) + proportional ad spend
- B2B: Proportional commission from 3 fee totals + 0% ad
- Amazon: 12% commission + proportional ad spend
- eBay: 16.35% commission + proportional ad spend

Updates fields:
- channel_commission: Commission percentage
- channel_advertisement_fee_amount: Dollar amount of ad fees
- commission_and_fee: Total = commission dollars + ad fees
"""

import mysql.connector
import logging
from datetime import datetime, timedelta
from collections import defaultdict
import os
from dotenv import load_dotenv

# ============================================================================
# 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
DATE_END = None    # Will be calculated
DATE_RANGE_DAYS = 120

# Commission percentages (constant across all months)
AMAZON_COMMISSION_PCT = 12.0
EBAY_COMMISSION_PCT = 16.35
WEB_COMMISSION_PCT = 0.0  # Web has NO commission, only ad fees
B2B_COMMISSION_PCT = 3.0  # Fallback if no fees configured

# 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

logger = logging.getLogger(__name__)

# ============================================================================
# MONTHLY CONFIG LOADING
# ============================================================================

def load_monthly_config(year, month):
    """
    Load .env file for specific month

    Args:
        year: Year (e.g., 2025)
        month: Month number (1-12)

    Returns:
        dict with config values, or None if file doesn't exist
    """
    month_str = f"{month:02d}"
    env_file = os.path.join(BASE_DIR, f'.env.{month_str}')

    if not os.path.exists(env_file):
        return None

    # Load the .env file
    load_dotenv(env_file, override=True)

    config = {
        'b2b_ccc_fees': float(os.getenv('CCC_FEES_DOLLAR', '0')),
        'b2b_opstrax_fees': float(os.getenv('OPSTRAX_FEES_DOLLAR', '0')),
        'b2b_parts_trader_fees': float(os.getenv('PARTS_TRADER_FEES_DOLLAR', '0')),
        'amazon_ad_spend': float(os.getenv('AMAZON_AD_SPEND_DOLLAR', '0')),
        'ebay_ad_spend': float(os.getenv('EBAY_AD_SPEND_DOLLAR', '0')),
        'web_ad_spend': float(os.getenv('WEBSITE_AD_SPEND_DOLLAR', '0')),
        'month': f"{year}-{month_str}",
        'source_file': env_file
    }

    config['b2b_total_fees'] = (config['b2b_ccc_fees'] +
                                 config['b2b_opstrax_fees'] +
                                 config['b2b_parts_trader_fees'])

    return config


def get_month_revenue(cursor, year, month):
    """Get total revenue for a specific month"""
    start_date = f"{year}-{month:02d}-01"
    if month == 12:
        end_date = f"{year+1}-01-01"
    else:
        end_date = f"{year}-{month+1:02d}-01"

    cursor.execute("""
        SELECT
            SUM(CASE WHEN sales_channel = 'Web' THEN sales_price ELSE 0 END) as web_revenue,
            SUM(CASE WHEN sales_channel = 'B2B' THEN sales_price ELSE 0 END) as b2b_revenue,
            SUM(CASE WHEN sales_channel = 'Amazon' THEN sales_price ELSE 0 END) as amazon_revenue,
            SUM(CASE WHEN sales_channel = 'eBay' THEN sales_price ELSE 0 END) as ebay_revenue
        FROM orders_items_margin_detailed
        WHERE date_placed >= %s
          AND date_placed < %s
    """, (start_date, end_date))

    result = cursor.fetchone()
    return {
        'web': float(result['web_revenue'] or 0),
        'b2b': float(result['b2b_revenue'] or 0),
        'amazon': float(result['amazon_revenue'] or 0),
        'ebay': float(result['ebay_revenue'] or 0)
    }


def calculate_proportional_config(previous_config, previous_revenue, current_revenue):
    """
    Calculate proportional fees for current month based on previous month

    Args:
        previous_config: Config from previous month
        previous_revenue: Revenue dict from previous month
        current_revenue: Revenue dict from current month

    Returns:
        Config dict with proportional fees
    """
    config = {
        'month': 'estimated',
        'source_file': f"Estimated from {previous_config['month']}"
    }

    # Calculate proportions for each channel
    for channel in ['web', 'b2b', 'amazon', 'ebay']:
        prev_rev = previous_revenue[channel]
        curr_rev = current_revenue[channel]
        proportion = curr_rev / prev_rev if prev_rev > 0 else 1.0

        if channel == 'web':
            config['web_ad_spend'] = previous_config['web_ad_spend'] * proportion
        elif channel == 'b2b':
            config['b2b_ccc_fees'] = previous_config['b2b_ccc_fees'] * proportion
            config['b2b_opstrax_fees'] = previous_config['b2b_opstrax_fees'] * proportion
            config['b2b_parts_trader_fees'] = previous_config['b2b_parts_trader_fees'] * proportion
            config['b2b_total_fees'] = (config['b2b_ccc_fees'] +
                                       config['b2b_opstrax_fees'] +
                                       config['b2b_parts_trader_fees'])
        elif channel == 'amazon':
            config['amazon_ad_spend'] = previous_config['amazon_ad_spend'] * proportion
        elif channel == 'ebay':
            config['ebay_ad_spend'] = previous_config['ebay_ad_spend'] * proportion

    return config


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

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


# ============================================================================
# COMMISSION UPDATE FUNCTIONS
# ============================================================================

def update_channel_fees(channel, year, month, config):
    """
    Update commission and fees for a specific channel and month

    Args:
        channel: 'Web', 'B2B', 'Amazon', or 'eBay'
        year: Year number
        month: Month number (1-12)
        config: Config dict with fee values
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    start_date = f"{year}-{month:02d}-01"
    if month == 12:
        end_date = f"{year+1}-01-01"
    else:
        end_date = f"{year}-{month+1:02d}-01"

    logger.info(f"\n  Processing {channel} - {year}-{month:02d}")
    logger.info(f"  Config source: {config['source_file']}")

    # Determine if fee data is Actual (from .env file) or Estimated (proportional)
    fee_data_source = 'Actual' if not config['source_file'].startswith('Estimated') else 'Estimated'
    logger.info(f"  Fee data source: {fee_data_source}")

    # Get orders for this channel and month (including refunds/cancellations)
    query = """
        SELECT
            id,
            external_order_id,
            sales_price
        FROM orders_items_margin_detailed
        WHERE date_placed >= %s
          AND date_placed < %s
          AND sales_channel = %s
          AND sales_price IS NOT NULL
          AND sales_price > 0
    """

    cursor.execute(query, [start_date, end_date, channel])
    items = cursor.fetchall()

    if len(items) == 0:
        logger.info(f"  No orders found")
        cursor.close()
        conn.close()
        return {'updated': 0, 'total_fees': 0, 'total_revenue': 0}

    total_revenue = sum(float(item['sales_price']) for item in items)
    logger.info(f"  Orders: {len(items)}, Revenue: ${total_revenue:,.2f}")

    updated = 0
    total_fees_added = 0

    if channel == 'Web':
        # Web: 0% commission + proportional ad spend only
        web_ad_spend = config.get('web_ad_spend', 0)
        ad_spend_rate = web_ad_spend / total_revenue if total_revenue > 0 else 0

        logger.info(f"  Commission: 0% (merchant fees in payment processor)")
        logger.info(f"  Ad Spend: ${web_ad_spend:,.2f} ({ad_spend_rate*100:.4f}%)")

        for item in items:
            sales_price = float(item['sales_price'])
            commission_dollars = 0  # No commission on Web
            ad_fee = sales_price * ad_spend_rate
            total_fee = ad_fee  # Only ad fees

            if not DRY_RUN:
                cursor.execute("""
                    UPDATE orders_items_margin_detailed
                    SET channel_commission = 0,
                        channel_advertisement_fee_amount = %s,
                        commission_and_fee = %s,
                        fee_data_source = %s
                    WHERE id = %s
                """, (ad_fee, total_fee, fee_data_source, item['id']))

            updated += 1
            total_fees_added += total_fee

    elif channel == 'B2B':
        # B2B: Proportional commission from fee totals
        b2b_total_fees = config.get('b2b_total_fees', 0)
        fee_rate = b2b_total_fees / total_revenue if total_revenue > 0 else 0

        logger.info(f"  Total Fees: ${b2b_total_fees:,.2f}, Rate: {fee_rate*100:.4f}%")

        for item in items:
            sales_price = float(item['sales_price'])
            commission_dollars = sales_price * fee_rate
            total_fee = commission_dollars

            if not DRY_RUN:
                cursor.execute("""
                    UPDATE orders_items_margin_detailed
                    SET channel_commission = %s,
                        channel_advertisement_fee_amount = 0,
                        commission_and_fee = %s,
                        fee_data_source = %s
                    WHERE id = %s
                """, (fee_rate * 100, total_fee, fee_data_source, item['id']))

            updated += 1
            total_fees_added += total_fee

    elif channel == 'Amazon':
        # Amazon: 12% commission + proportional ad spend
        amazon_ad_spend = config.get('amazon_ad_spend', 0)
        ad_spend_rate = amazon_ad_spend / total_revenue if total_revenue > 0 else 0

        logger.info(f"  Commission: {AMAZON_COMMISSION_PCT}% + Ad Spend: ${amazon_ad_spend:,.2f} ({ad_spend_rate*100:.4f}%)")

        for item in items:
            sales_price = float(item['sales_price'])
            commission_dollars = sales_price * (AMAZON_COMMISSION_PCT / 100)
            ad_fee = sales_price * ad_spend_rate
            total_fee = commission_dollars + ad_fee

            if not DRY_RUN:
                cursor.execute("""
                    UPDATE orders_items_margin_detailed
                    SET channel_commission = %s,
                        channel_advertisement_fee_amount = %s,
                        commission_and_fee = %s,
                        fee_data_source = %s
                    WHERE id = %s
                """, (AMAZON_COMMISSION_PCT, ad_fee, total_fee, fee_data_source, item['id']))

            updated += 1
            total_fees_added += total_fee

    elif channel == 'eBay':
        # eBay: 16.35% commission + proportional ad spend
        ebay_ad_spend = config.get('ebay_ad_spend', 0)
        ad_spend_rate = ebay_ad_spend / total_revenue if total_revenue > 0 else 0

        logger.info(f"  Commission: {EBAY_COMMISSION_PCT}% + Ad Spend: ${ebay_ad_spend:,.2f} ({ad_spend_rate*100:.4f}%)")

        for item in items:
            sales_price = float(item['sales_price'])
            commission_dollars = sales_price * (EBAY_COMMISSION_PCT / 100)
            ad_fee = sales_price * ad_spend_rate
            total_fee = commission_dollars + ad_fee

            if not DRY_RUN:
                cursor.execute("""
                    UPDATE orders_items_margin_detailed
                    SET channel_commission = %s,
                        channel_advertisement_fee_amount = %s,
                        commission_and_fee = %s,
                        fee_data_source = %s
                    WHERE id = %s
                """, (EBAY_COMMISSION_PCT, ad_fee, total_fee, fee_data_source, item['id']))

            updated += 1
            total_fees_added += total_fee

    if not DRY_RUN:
        conn.commit()

    logger.info(f"  Updated {updated} orders, Total fees: ${total_fees_added:,.2f}")

    cursor.close()
    conn.close()

    return {
        'updated': updated,
        'total_fees': total_fees_added,
        'total_revenue': total_revenue
    }


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

def main():
    """Main execution function"""
    global DATE_START, DATE_END

    # Calculate date range
    DATE_END = datetime.now()
    DATE_START = DATE_END - timedelta(days=DATE_RANGE_DAYS)

    logger.info("=" * 80)
    logger.info(f"COMMISSION AND FEE 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.strftime('%Y-%m-%d')} to {DATE_END.strftime('%Y-%m-%d')} ({DATE_RANGE_DAYS} days)")
    logger.info("")

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

    # Get unique months in date range
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute("""
        SELECT DISTINCT
            YEAR(date_placed) as year,
            MONTH(date_placed) as month
        FROM orders_items_margin_detailed
        WHERE date_placed >= %s
          AND date_placed < %s
        ORDER BY year, month
    """, (DATE_START.strftime('%Y-%m-%d'), DATE_END.strftime('%Y-%m-%d')))

    months = cursor.fetchall()
    cursor.close()
    conn.close()

    logger.info(f"Found {len(months)} unique months to process:")
    for m in months:
        logger.info(f"  - {m['year']}-{m['month']:02d}")
    logger.info("")

    # Process each month
    previous_config = None
    previous_revenue = None

    grand_total_orders = 0
    grand_total_revenue = 0
    grand_total_fees = 0

    for month_info in months:
        year = month_info['year']
        month = month_info['month']

        logger.info("=" * 80)
        logger.info(f"PROCESSING MONTH: {year}-{month:02d}")
        logger.info("=" * 80)

        # Try to load monthly config
        config = load_monthly_config(year, month)

        if config is None:
            # No .env file for this month, estimate from previous month
            if previous_config is None:
                logger.warning(f"  No .env file found and no previous month to estimate from - skipping")
                continue

            # Get current month revenue
            conn = get_db_connection()
            cursor = conn.cursor(dictionary=True)
            current_revenue = get_month_revenue(cursor, year, month)
            cursor.close()
            conn.close()

            logger.info(f"  No .env.{month:02d} file found")
            logger.info(f"  Estimating fees from previous month ({previous_config['month']})")
            logger.info(f"  Current month revenue: Web=${current_revenue['web']:,.2f}, B2B=${current_revenue['b2b']:,.2f}, " +
                       f"Amazon=${current_revenue['amazon']:,.2f}, eBay=${current_revenue['ebay']:,.2f}")

            config = calculate_proportional_config(previous_config, previous_revenue, current_revenue)
        else:
            logger.info(f"  Loaded config from: {config['source_file']}")

            # Get current month revenue for reference
            conn = get_db_connection()
            cursor = conn.cursor(dictionary=True)
            current_revenue = get_month_revenue(cursor, year, month)
            cursor.close()
            conn.close()

        # Update each channel
        channels = ['Web', 'B2B', 'Amazon', 'eBay']
        month_total_orders = 0
        month_total_revenue = 0
        month_total_fees = 0

        for channel in channels:
            result = update_channel_fees(channel, year, month, config)
            month_total_orders += result['updated']
            month_total_revenue += result['total_revenue']
            month_total_fees += result['total_fees']

        logger.info(f"\n  Month Summary:")
        logger.info(f"    Orders: {month_total_orders}")
        logger.info(f"    Revenue: ${month_total_revenue:,.2f}")
        logger.info(f"    Total Fees: ${month_total_fees:,.2f}")

        grand_total_orders += month_total_orders
        grand_total_revenue += month_total_revenue
        grand_total_fees += month_total_fees

        # Save for next month
        previous_config = config
        previous_revenue = current_revenue

    # Final summary
    logger.info(f"\n{'=' * 80}")
    logger.info(f"GRAND TOTAL SUMMARY")
    logger.info(f"{'=' * 80}")
    logger.info(f"Total Orders Updated: {grand_total_orders}")
    logger.info(f"Total Revenue: ${grand_total_revenue:,.2f}")
    logger.info(f"Total Fees Applied: ${grand_total_fees:,.2f}")
    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'commission_fees_{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()