#!/usr/bin/env python3
"""
Script 3: Update Payment Processor Fees
Applies payment processor fees for Bolt, Credit Card, and eBay transactions

Handles:
- Bolt: Actual transaction fees from payment_processor_charges-Bolt table
- Credit Card: Calculated 2.69% processing fee for all CC payment methods
- eBay Managed Payments: Calculated 2.9% + $0.30 per transaction for eBay orders

Cron-ready: Runs continuously on rolling 60-day window, updates all fees
"""

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

# Payment processor configuration
CREDIT_CARD_FEE_RATE = 0.0269  # 2.69%

# Credit card payment methods
CREDIT_CARD_METHODS = [
    'authorizenet',
    'authorizenet_directp',
    'boltpay',
    'ccsave',
    'payflow_advanced',
    'payflow_link',
    'verisign',
    'moneybookers_acc',
    'amazon_payments'
]

# eBay Managed Payments configuration
EBAY_FEE_RATE = 0.029  # 2.9%
EBAY_FIXED_FEE = 0.30  # $0.30 per transaction

# eBay payment methods
EBAY_PAYMENT_METHODS = [
    'ebay',
    'sellbrite'
]

# 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)

# ============================================================================
# PAYMENT PROCESSOR FEE UPDATE FUNCTIONS
# ============================================================================

def update_bolt_fees():
    """
    Apply Bolt payment processor fees from actual transaction data
    Returns: dict with stats
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    logger.info("="*80)
    logger.info("Processing: BOLT PAYMENT PROCESSOR")
    logger.info("  Source: payment_processor_charges-Bolt table")
    logger.info("="*80)

    # Get all items that have Bolt transaction data
    query = """
    SELECT
        oim.id,
        oim.order_item_id,
        oim.external_order_id,
        oim.sales_price,
        oim.payment_processor_fee as current_processor_fee,
        bf.bolt_fee_amount,
        bf.processing_fee_amount
    FROM orders_items_margin_detailed oim
    JOIN `payment_processor_charges-Bolt` bf ON oim.external_order_id COLLATE utf8mb4_unicode_ci = bf.order_reference COLLATE utf8mb4_unicode_ci
    WHERE oim.date_placed >= %s
        AND oim.date_placed < %s
        AND oim.is_active = 1
        AND bf.status = 'completed'
    """

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

    logger.info(f"Found {len(items)} Bolt items to update")

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

    updated = 0
    total_fees = 0

    update_query = """
    UPDATE orders_items_margin_detailed
    SET payment_processor_fee = %s
    WHERE id = %s
    """

    for item in items:
        bolt_fee = float(item['bolt_fee_amount']) if item['bolt_fee_amount'] else 0
        total_fees += bolt_fee

        if not DRY_RUN:
            cursor.execute(update_query, (bolt_fee, item['id']))

        updated += 1

        if updated <= 10:
            logger.info(f"  {'[DRY RUN] ' if DRY_RUN else ''}Updated item {item['order_item_id']} "
                       f"(Order {item['external_order_id']}): Bolt fee=${bolt_fee:.2f}")

    if not DRY_RUN:
        conn.commit()

    logger.info(f"\nBolt Summary:")
    logger.info(f"  Items updated: {updated}")
    logger.info(f"  Total Bolt fees: ${total_fees:,.2f}")
    if len(items) > 0:
        logger.info(f"  Average fee: ${total_fees/len(items):.2f}")

    cursor.close()
    conn.close()

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


def update_credit_card_fees():
    """
    Apply calculated credit card processing fees (2.69% of sales price)
    Returns: dict with stats
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    logger.info("\n" + "="*80)
    logger.info("Processing: CREDIT CARD PAYMENT PROCESSOR")
    logger.info(f"  Fee Rate: {CREDIT_CARD_FEE_RATE * 100:.2f}% of sales price")
    logger.info("="*80)

    # Get all orders paid via credit card
    query = """
    SELECT
        oim.id,
        oim.order_id,
        oim.external_order_id,
        oim.sales_channel,
        oim.sales_price,
        oim.payment_processor_fee as current_processor_fee,
        o.payment_method_id,
        pm.name as payment_method_name
    FROM orders_items_margin_detailed oim
    JOIN orders o ON oim.order_id = o.order_id
    LEFT JOIN payment_methods pm ON o.payment_method_id = pm.code
    WHERE oim.date_placed >= %s
        AND oim.date_placed < %s
        AND oim.is_active = 1
        AND o.payment_method_id IN ({})
        AND oim.sales_price IS NOT NULL
        AND oim.sales_price > 0
    """.format(','.join(['%s'] * len(CREDIT_CARD_METHODS)))

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

    logger.info(f"Found {len(items)} credit card items to update")

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

    updated = 0
    total_fees = 0
    by_channel = {}

    update_query = """
    UPDATE orders_items_margin_detailed
    SET payment_processor_fee = %s
    WHERE id = %s
    """

    for item in items:
        item_price = float(item['sales_price'])
        cc_fee = item_price * CREDIT_CARD_FEE_RATE
        total_fees += cc_fee

        channel = item['sales_channel']
        if channel not in by_channel:
            by_channel[channel] = {'items': 0, 'fees': 0, 'revenue': 0}
        by_channel[channel]['items'] += 1
        by_channel[channel]['fees'] += cc_fee
        by_channel[channel]['revenue'] += item_price

        if not DRY_RUN:
            cursor.execute(update_query, (cc_fee, item['id']))

        updated += 1

        if updated <= 10:
            logger.info(f"  {'[DRY RUN] ' if DRY_RUN else ''}Updated item {item['id']} "
                       f"({item['sales_channel']}, {item['payment_method_name'] or item['payment_method_id']}): "
                       f"sales=${item_price:.2f}, cc_fee=${cc_fee:.2f}")

    if not DRY_RUN:
        conn.commit()

    logger.info(f"\nCredit Card Summary:")
    logger.info(f"  Items updated: {updated}")
    logger.info(f"  Total CC fees: ${total_fees:,.2f}")
    logger.info(f"\n  Breakdown by Channel:")
    for channel, stats in sorted(by_channel.items()):
        logger.info(f"    {channel}: {stats['items']} items, "
                   f"${stats['revenue']:,.2f} revenue, ${stats['fees']:,.2f} fees")

    cursor.close()
    conn.close()

    return {
        'updated': updated,
        'total_fees': total_fees,
        'by_channel': by_channel
    }


def update_ebay_fees():
    """
    Apply eBay Managed Payments processing fees (2.9% + $0.30 per transaction)
    Returns: dict with stats
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    logger.info("\n" + "="*80)
    logger.info("Processing: EBAY MANAGED PAYMENTS PROCESSOR")
    logger.info(f"  Fee Rate: {EBAY_FEE_RATE * 100:.2f}% + ${EBAY_FIXED_FEE:.2f} per transaction")
    logger.info("="*80)

    # Get all orders paid via eBay payment methods
    query = """
    SELECT
        oim.id,
        oim.order_id,
        oim.external_order_id,
        oim.sales_channel,
        oim.sales_price,
        oim.payment_processor_fee as current_processor_fee,
        o.payment_method_id,
        pm.name as payment_method_name
    FROM orders_items_margin_detailed oim
    JOIN orders o ON oim.order_id = o.order_id
    LEFT JOIN payment_methods pm ON o.payment_method_id = pm.code
    WHERE oim.date_placed >= %s
        AND oim.date_placed < %s
        AND oim.is_active = 1
        AND o.payment_method_id IN ({})
        AND oim.sales_price IS NOT NULL
        AND oim.sales_price > 0
    """.format(','.join(['%s'] * len(EBAY_PAYMENT_METHODS)))

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

    logger.info(f"Found {len(items)} eBay payment items to update")

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

    updated = 0
    total_fees = 0
    by_channel = {}

    update_query = """
    UPDATE orders_items_margin_detailed
    SET payment_processor_fee = %s
    WHERE id = %s
    """

    for item in items:
        item_price = float(item['sales_price'])
        # eBay fee: 2.9% + $0.30 per transaction
        ebay_fee = (item_price * EBAY_FEE_RATE) + EBAY_FIXED_FEE
        total_fees += ebay_fee

        channel = item['sales_channel']
        if channel not in by_channel:
            by_channel[channel] = {'items': 0, 'fees': 0, 'revenue': 0}
        by_channel[channel]['items'] += 1
        by_channel[channel]['fees'] += ebay_fee
        by_channel[channel]['revenue'] += item_price

        if not DRY_RUN:
            cursor.execute(update_query, (ebay_fee, item['id']))

        updated += 1

        if updated <= 10:
            logger.info(f"  {'[DRY RUN] ' if DRY_RUN else ''}Updated item {item['id']} "
                       f"({item['sales_channel']}, {item['payment_method_name'] or item['payment_method_id']}): "
                       f"sales=${item_price:.2f}, eBay fee=${ebay_fee:.2f}")

    if not DRY_RUN:
        conn.commit()

    logger.info(f"\neBay Managed Payments Summary:")
    logger.info(f"  Items updated: {updated}")
    logger.info(f"  Total eBay payment fees: ${total_fees:,.2f}")
    logger.info(f"\n  Breakdown by Channel:")
    for channel, stats in sorted(by_channel.items()):
        logger.info(f"    {channel}: {stats['items']} items, "
                   f"${stats['revenue']:,.2f} revenue, ${stats['fees']:,.2f} fees")

    cursor.close()
    conn.close()

    return {
        'updated': updated,
        'total_fees': total_fees,
        '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"PAYMENT PROCESSOR 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} to {DATE_END} ({DATE_RANGE_DAYS} days)")

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

    # Process Bolt fees first
    bolt_result = update_bolt_fees()

    # Then process credit card fees
    cc_result = update_credit_card_fees()

    # Then process eBay payment fees
    ebay_result = update_ebay_fees()

    # Final summary
    logger.info(f"\n{'='*80}")
    logger.info(f"OVERALL SUMMARY")
    logger.info(f"{'='*80}")
    logger.info(f"Bolt:")
    logger.info(f"  Items updated: {bolt_result['updated']}")
    logger.info(f"  Total fees: ${bolt_result['total_fees']:,.2f}")
    logger.info(f"\nCredit Card:")
    logger.info(f"  Items updated: {cc_result['updated']}")
    logger.info(f"  Total fees: ${cc_result['total_fees']:,.2f}")
    logger.info(f"\neBay Managed Payments:")
    logger.info(f"  Items updated: {ebay_result['updated']}")
    logger.info(f"  Total fees: ${ebay_result['total_fees']:,.2f}")
    logger.info(f"\nGrand Total:")
    logger.info(f"  Items updated: {bolt_result['updated'] + cc_result['updated'] + ebay_result['updated']}")
    logger.info(f"  Total payment processor fees: ${bolt_result['total_fees'] + cc_result['total_fees'] + ebay_result['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'payment_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()