#!/usr/bin/env python3
"""
Script 1: Import All External Data (Bolt, ShipHaven, USPS)
Imports payment processor and shipping data from CSV files into database tables

Imports:
- Bolt payment processor fees → payment_processor_charges-Bolt
- UPS ShipHaven shipping → shipping_charges-ups-shiphaven
- USPS Stamps.com shipping → shipping_charges-usps_stampscom

Created: October 2025
For: September 2025 COGS Fix
"""

import mysql.connector
import csv
import os
import logging
from datetime import datetime
from decimal import Decimal

# ============================================================================
# CONFIGURATION
# ============================================================================

DB_CONFIG = {
    'host': '127.0.0.1',
    'port': 3306,
    'database': 'goparts',
    'user': 'root',
    'password': ')V9IR<W:~4=HrqbL'
}

# Date range for September 2025
DATE_START = '2025-09-01'
DATE_END = '2025-10-01'
MONTH_NAME = 'September 2025'

# Data source directories
BASE_DIR = '/home/centralgoparts/public_html/profitability'
# Import data from the original location where CSV files are stored
DATA_SOURCE_BASE = '/home/oldgoparts/www/scripts/cogs_investigation/combined_scripts'
BOLT_DIR = os.path.join(DATA_SOURCE_BASE, 'bolt')
SHIPHAVEN_DIR = os.path.join(DATA_SOURCE_BASE, 'shiphaven')
USPS_DIR = os.path.join(DATA_SOURCE_BASE, 'usps-stamps')

DRY_RUN = False  # Set to True for testing

# Logging directory
LOG_DIR = os.path.join(BASE_DIR, 'logs')
os.makedirs(LOG_DIR, exist_ok=True)

logger = logging.getLogger(__name__)

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

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

# ============================================================================
# UTILITY FUNCTIONS
# ============================================================================

def parse_currency_amount(amount_str):
    """Parse currency string like '$24.18' or '$1,016.52' to Decimal"""
    if not amount_str or amount_str.strip() == '':
        return None
    clean_str = amount_str.replace('$', '').replace(',', '').strip()
    try:
        return Decimal(clean_str)
    except:
        return None

def parse_datetime(date_str):
    """Parse datetime string like '2025-09-11 02:03:19 UTC'"""
    if not date_str or date_str.strip() == '':
        return None
    try:
        clean_str = date_str.replace(' UTC', '').strip()
        return datetime.strptime(clean_str, '%Y-%m-%d %H:%M:%S')
    except:
        return None

def parse_date_mdy(date_str):
    """Parse date string like '09/01/2025' or '9/1/2025 12:00:00 AM'"""
    if not date_str or date_str.strip() == '':
        return None
    try:
        # Handle format with time: "9/1/2025 12:00:00 AM"
        date_part = date_str.split()[0]
        return datetime.strptime(date_part, '%m/%d/%Y').date()
    except:
        try:
            # Try without leading zeros
            return datetime.strptime(date_str, '%m/%d/%Y').date()
        except:
            return None

def parse_decimal(value_str):
    """Parse decimal string, handle quoted values like =""28607""'"""
    if not value_str or value_str.strip() == '':
        return None
    try:
        clean_str = value_str.replace('=""', '').replace('""', '').strip()
        if clean_str == '':
            return None
        return Decimal(clean_str)
    except:
        return None

def clean_string(value_str):
    """Clean string values - remove quotes and equals signs"""
    if not value_str:
        return None
    clean_str = value_str.replace('=""', '').replace('""', '').strip()
    return clean_str if clean_str else None

# ============================================================================
# BOLT FEES IMPORT
# ============================================================================

def import_bolt_fees():
    """Import Bolt payment processor fees from CSV"""

    logger.info(f"\n{'='*80}")
    logger.info(f"IMPORTING BOLT FEES")
    logger.info(f"{'='*80}")

    conn = get_db_connection()
    cursor = conn.cursor()

    # Find Bolt CSV files
    csv_files = [f for f in os.listdir(BOLT_DIR) if f.endswith('.csv')]

    if not csv_files:
        logger.warning(f"No CSV files found in {BOLT_DIR}")
        cursor.close()
        conn.close()
        return {'imported': 0, 'skipped': 0, 'duplicates': 0}

    logger.info(f"Found {len(csv_files)} Bolt CSV file(s):")
    for f in csv_files:
        logger.info(f"  - {f}")

    imported = 0
    skipped = 0
    duplicates = 0

    for csv_file in csv_files:
        file_path = os.path.join(BOLT_DIR, csv_file)
        logger.info(f"\nProcessing: {csv_file}")

        with open(file_path, 'r', encoding='utf-8') as f:
            reader = csv.DictReader(f)
            rows = list(reader)

        logger.info(f"  Total rows: {len(rows)}")

        # Filter for completed transactions
        completed_rows = [row for row in rows if row.get('status') == 'completed']
        logger.info(f"  Completed transactions: {len(completed_rows)}")

        for row in completed_rows:
            reference = row.get('reference', '').strip()
            order_reference = row.get('order_reference', '').strip()

            if not reference or not order_reference:
                skipped += 1
                continue

            # Check for duplicate
            cursor.execute("""
                SELECT COUNT(*) FROM `payment_processor_charges-Bolt`
                WHERE reference = %s
            """, (reference,))

            if cursor.fetchone()[0] > 0:
                duplicates += 1
                continue

            # Parse data
            transaction_date = parse_datetime(row.get('date', ''))
            total_amount = parse_currency_amount(row.get('total_amount', ''))
            processing_fee = parse_currency_amount(row.get('processing_fee_amount', ''))
            bolt_fee = parse_currency_amount(row.get('bolt_fee_amount', ''))
            net_amount = parse_currency_amount(row.get('net_amount', ''))
            shipping_amount = parse_currency_amount(row.get('shipping_amount', ''))
            tax_amount = parse_currency_amount(row.get('tax_amount', ''))
            discount_amount = parse_currency_amount(row.get('discount_amount', ''))
            chargeback_amount = parse_currency_amount(row.get('chargeback_amount', ''))
            chargeback_date = parse_datetime(row.get('chargeback_date_utc', ''))
            chargeback_reply_date = parse_datetime(row.get('chargeback_reply_by_date_utc', ''))

            values = (
                reference,
                row.get('type', ''),
                transaction_date,
                row.get('status', ''),
                row.get('customer', ''),
                row.get('currency', ''),
                total_amount,
                processing_fee,
                bolt_fee,
                net_amount,
                shipping_amount,
                tax_amount,
                discount_amount,
                row.get('discount_description') if row.get('discount_description') else None,
                order_reference,
                row.get('card_type') if row.get('card_type') else None,
                row.get('email', ''),
                row.get('last_four_digits') if row.get('last_four_digits') else None,
                row.get('street') if row.get('street') else None,
                row.get('city') if row.get('city') else None,
                row.get('state') if row.get('state') else None,
                row.get('zip') if row.get('zip') else None,
                chargeback_date,
                chargeback_amount,
                row.get('chargeback_status') if row.get('chargeback_status', '') != 'none' else None,
                chargeback_reply_date
            )

            if not DRY_RUN:
                try:
                    cursor.execute("""
                        INSERT INTO `payment_processor_charges-Bolt` (
                            reference, type, date, status, customer, currency,
                            total_amount, processing_fee_amount, bolt_fee_amount, net_amount,
                            shipping_amount, tax_amount, discount_amount, discount_description,
                            order_reference, card_type, email, last_four_digits,
                            street, city, state, zip,
                            chargeback_date_utc, chargeback_amount, chargeback_status, chargeback_reply_by_date_utc
                        ) VALUES (
                            %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
                        )
                    """, values)
                    imported += 1
                except Exception as e:
                    logger.error(f"  Error importing Bolt reference {reference}: {e}")
                    skipped += 1
            else:
                imported += 1

    if not DRY_RUN:
        conn.commit()

    logger.info(f"\nBolt Import Summary:")
    logger.info(f"  {'Would import' if DRY_RUN else 'Imported'}: {imported}")
    logger.info(f"  Duplicates skipped: {duplicates}")
    logger.info(f"  Errors/Invalid: {skipped}")

    cursor.close()
    conn.close()

    return {'imported': imported, 'skipped': skipped, 'duplicates': duplicates}

# ============================================================================
# UPS SHIPHAVEN IMPORT
# ============================================================================

def import_shiphaven():
    """Import UPS ShipHaven shipping charges from CSV files"""

    logger.info(f"\n{'='*80}")
    logger.info(f"IMPORTING UPS SHIPHAVEN SHIPPING")
    logger.info(f"{'='*80}")

    conn = get_db_connection()
    cursor = conn.cursor()

    # Find ShipHaven CSV files
    csv_files = sorted([f for f in os.listdir(SHIPHAVEN_DIR) if f.endswith('.csv')])

    if not csv_files:
        logger.warning(f"No CSV files found in {SHIPHAVEN_DIR}")
        cursor.close()
        conn.close()
        return {'imported': 0, 'skipped': 0, 'duplicates': 0}

    logger.info(f"Found {len(csv_files)} ShipHaven CSV file(s):")
    for f in csv_files:
        logger.info(f"  - {f}")

    total_imported = 0
    total_skipped = 0
    total_duplicates = 0

    for csv_file in csv_files:
        file_path = os.path.join(SHIPHAVEN_DIR, csv_file)
        logger.info(f"\nProcessing: {csv_file}")

        imported = 0
        skipped = 0
        duplicates = 0

        try:
            with open(file_path, 'r', encoding='utf-8') as f:
                reader = csv.DictReader(f)

                for row in reader:
                    # Clean and extract key fields
                    airbill = clean_string(row.get('AIRBILL #', ''))
                    invoice_date = row.get('INVOICE DATE', '')

                    if not airbill or not invoice_date:
                        skipped += 1
                        continue

                    # Check for duplicate
                    cursor.execute("""
                        SELECT COUNT(*) FROM `shipping_charges-ups-shiphaven`
                        WHERE AIRBILL_NO = %s AND INVOICE_DATE = %s
                    """, (airbill, invoice_date))

                    if cursor.fetchone()[0] > 0:
                        duplicates += 1
                        continue

                    # Prepare all values (clean strings)
                    values = (
                        clean_string(row.get('INVOICE #', '')),
                        invoice_date,
                        airbill,
                        clean_string(row.get('SHIPPER ACCOUNT #', '')),
                        clean_string(row.get('SHIPPER ACCOUNT NAME', '')),
                        clean_string(row.get('SHIPPER ATTENTION', '')),
                        clean_string(row.get('SHIPPER ADDRESS 1', '')),
                        clean_string(row.get('SHIPPER ADDRESS 2', '')),
                        clean_string(row.get('SHIPPER CITY', '')),
                        clean_string(row.get('SHIPPER STATE', '')),
                        clean_string(row.get('SHIPPER ZIP CODE', '')),
                        clean_string(row.get('SHIPPER REFERENCE', '')),
                        clean_string(row.get('CONSIGNEE NAME', '')),
                        clean_string(row.get('CONSIGNEE ATTENTION', '')),
                        clean_string(row.get('CONSIGNEE ADDRESS 1', '')),
                        clean_string(row.get('CONSIGNEE ADDRESS 2', '')),
                        clean_string(row.get('CONSIGNEE CITY', '')),
                        clean_string(row.get('CONSIGNEE STATE', '')),
                        clean_string(row.get('CONSIGNEE ZIP CODE', '')),
                        clean_string(row.get('CONSIGNEE COUNTRY CODE', '')),
                        clean_string(row.get('THIRD PARTY ACCOUNT #', '')),
                        clean_string(row.get('THIRD PARTY ACCOUNT NAME', '')),
                        clean_string(row.get('THIRD PARTY ADDRESS', '')),
                        clean_string(row.get('THIRD PARTY ADDRESS', '')),
                        clean_string(row.get('THIRD PARTY CITY', '')),
                        clean_string(row.get('THIRD PARTY STATE', '')),
                        clean_string(row.get('THIRD PARTY ZIP CODE', '')),
                        row.get('SHIPMENT DATE', ''),
                        clean_string(row.get('PO #', '')),
                        clean_string(row.get('CUST INV #', '')),
                        clean_string(row.get('DEPT #', '')),
                        clean_string(row.get('PRODUCT CODE', '')),
                        clean_string(row.get('ZONE', '')),
                        clean_string(row.get('BILLED WEIGHT', '')),
                        clean_string(row.get('DIMENSIONAL WEIGHT', '')),
                        clean_string(row.get('PIECES', '')),
                        clean_string(row.get('DIMENSIONS', '')),
                        clean_string(row.get('BASE CHARGE TYPE', '')),
                        clean_string(row.get('SHIPMENT TOTAL', '')),
                        clean_string(row.get('BASE CHARGE AMOUNT', '')),
                        clean_string(row.get('CHARGE 1 TYPE', '')),
                        clean_string(row.get('CHARGE 1 AMT', '')),
                        clean_string(row.get('CHARGE 2 TYPE', '')),
                        clean_string(row.get('CHARGE 2 AMT', '')),
                        clean_string(row.get('CHARGE 3 TYPE', '')),
                        clean_string(row.get('CHARGE 3 AMT', '')),
                        clean_string(row.get('CHARGE 4 TYPE', '')),
                        clean_string(row.get('CHARGE 4 AMT', '')),
                        clean_string(row.get('CHARGE 5 TYPE', '')),
                        clean_string(row.get('CHARGE 5 AMT', '')),
                        clean_string(row.get('CHARGE 6 TYPE', '')),
                        clean_string(row.get('CHARGE 6 AMT', '')),
                        clean_string(row.get('CHARGE 7 TYPE', '')),
                        clean_string(row.get('CHARGE 7 AMT', '')),
                        clean_string(row.get('CHARGE 8 TYPE', '')),
                        clean_string(row.get('CHARGE 8 AMT', '')),
                        clean_string(row.get('REFERENCE 2', '')),
                        clean_string(row.get('REFERENCE 3', '')),
                        clean_string(row.get('REFERENCE 4', '')),
                        clean_string(row.get('REFERENCE 5', '')),
                        clean_string(row.get('CUSTOMERID', '')),
                        clean_string(row.get('SCAC', '')),
                        clean_string(row.get('CLASS', '')),
                        clean_string(row.get('CHARGENOTES', '')),
                        clean_string(row.get('RECEIVEDBY', '')),
                        clean_string(row.get('RECEIVEDATE', ''))
                    )

                    if not DRY_RUN:
                        try:
                            cursor.execute("""
                                INSERT INTO `shipping_charges-ups-shiphaven` (
                                    INVOICE_NO, INVOICE_DATE, AIRBILL_NO, SHIPPER_ACCOUNT_NO, SHIPPER_ACCOUNT_NAME,
                                    SHIPPER_ATTENTION, SHIPPER_ADDRESS_1, SHIPPER_ADDRESS_2, SHIPPER_CITY, SHIPPER_STATE,
                                    SHIPPER_ZIP_CODE, SHIPPER_REFERENCE, CONSIGNEE_NAME, CONSIGNEE_ATTENTION, CONSIGNEE_ADDRESS_1,
                                    CONSIGNEE_ADDRESS_2, CONSIGNEE_CITY, CONSIGNEE_STATE, CONSIGNEE_ZIP_CODE, CONSIGNEE_COUNTRY_CODE,
                                    THIRD_PARTY_ACCOUNT_NO, THIRD_PARTY_ACCOUNT_NAME, THIRD_PARTY_ADDRESS, THIRD_PARTY_ADDRESS_2,
                                    THIRD_PARTY_CITY, THIRD_PARTY_STATE, THIRD_PARTY_ZIP_CODE, SHIPMENT_DATE, PO_NO, CUST_INV_NO,
                                    DEPT_NO, PRODUCT_CODE, ZONE, BILLED_WEIGHT, DIMENSIONAL_WEIGHT, PIECES, DIMENSIONS,
                                    BASE_CHARGE_TYPE, SHIPMENT_TOTAL, BASE_CHARGE_AMOUNT, CHARGE_1_TYPE, CHARGE_1_AMT,
                                    CHARGE_2_TYPE, CHARGE_2_AMT, CHARGE_3_TYPE, CHARGE_3_AMT, CHARGE_4_TYPE, CHARGE_4_AMT,
                                    CHARGE_5_TYPE, CHARGE_5_AMT, CHARGE_6_TYPE, CHARGE_6_AMT, CHARGE_7_TYPE, CHARGE_7_AMT,
                                    CHARGE_8_TYPE, CHARGE_8_AMT, REFERENCE_2, REFERENCE_3, REFERENCE_4, REFERENCE_5,
                                    CUSTOMERID, SCAC, CLASS, CHARGENOTES, RECEIVEDBY, RECEIVEDATE
                                ) VALUES (
                                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                                    %s, %s, %s, %s, %s, %s
                                )
                            """, values)
                            imported += 1
                        except Exception as e:
                            logger.error(f"  Error importing airbill {airbill}: {e}")
                            skipped += 1
                    else:
                        imported += 1

            logger.info(f"  {'Would import' if DRY_RUN else 'Imported'}: {imported}, Duplicates: {duplicates}, Skipped: {skipped}")

            total_imported += imported
            total_duplicates += duplicates
            total_skipped += skipped

        except Exception as e:
            logger.error(f"Error processing {csv_file}: {e}")
            continue

    if not DRY_RUN:
        conn.commit()

    logger.info(f"\nShipHaven Import Summary:")
    logger.info(f"  {'Would import' if DRY_RUN else 'Imported'}: {total_imported}")
    logger.info(f"  Duplicates skipped: {total_duplicates}")
    logger.info(f"  Errors/Invalid: {total_skipped}")

    cursor.close()
    conn.close()

    return {'imported': total_imported, 'skipped': total_skipped, 'duplicates': total_duplicates}

# ============================================================================
# USPS STAMPS.COM IMPORT
# ============================================================================

def import_usps_stamps():
    """Import USPS Stamps.com shipping charges from CSV"""

    logger.info(f"\n{'='*80}")
    logger.info(f"IMPORTING USPS STAMPS.COM SHIPPING")
    logger.info(f"{'='*80}")

    conn = get_db_connection()
    cursor = conn.cursor()

    # Find USPS CSV files
    csv_files = [f for f in os.listdir(USPS_DIR) if f.endswith('.csv')]

    if not csv_files:
        logger.warning(f"No CSV files found in {USPS_DIR}")
        cursor.close()
        conn.close()
        return {'imported': 0, 'skipped': 0, 'duplicates': 0}

    logger.info(f"Found {len(csv_files)} USPS CSV file(s):")
    for f in csv_files:
        logger.info(f"  - {f}")

    total_imported = 0
    total_skipped = 0
    total_duplicates = 0

    for csv_file in csv_files:
        file_path = os.path.join(USPS_DIR, csv_file)
        logger.info(f"\nProcessing: {csv_file}")

        imported = 0
        skipped = 0
        duplicates = 0

        try:
            with open(file_path, 'r', encoding='utf-8') as f:
                reader = csv.DictReader(f)
                rows = list(reader)

            logger.info(f"  Total rows: {len(rows)}")

            # Filter for delivered shipments
            delivered_rows = [row for row in rows if row.get('Shipment Status') == 'Delivered']
            logger.info(f"  Delivered shipments: {len(delivered_rows)}")

            for row in delivered_rows:
                # Extract key fields
                tracking_number = clean_string(row.get('Tracking #', ''))
                date_printed = parse_date_mdy(row.get('Date Printed', ''))

                if not tracking_number or not date_printed:
                    skipped += 1
                    continue

                # Check for duplicate
                cursor.execute("""
                    SELECT COUNT(*) FROM `shipping_charges-usps_stampscom`
                    WHERE tracking_number = %s AND date_printed = %s
                """, (tracking_number, date_printed))

                if cursor.fetchone()[0] > 0:
                    duplicates += 1
                    continue

                # Parse dates and amounts
                date_delivered = parse_date_mdy(row.get('Date Delivered', ''))
                ship_date = parse_date_mdy(row.get('Ship Date', ''))
                order_date = parse_date_mdy(row.get('Order Date', ''))
                refund_request_date = parse_date_mdy(row.get('Refund Request Date', ''))

                amount_paid = parse_decimal(row.get('Amount Paid', ''))
                adjusted_amount = parse_decimal(row.get('Adjusted Amount', ''))
                quoted_amount = parse_decimal(row.get('Quoted Amount', ''))
                insured_for = parse_decimal(row.get('Insured For', ''))
                order_total = parse_decimal(row.get('Order Total', ''))
                product_total = parse_decimal(row.get('Product Total', ''))
                shipping_paid = parse_decimal(row.get('Shipping Paid', ''))
                tax_paid = parse_decimal(row.get('Tax Paid', ''))
                duties_and_taxes = parse_decimal(row.get('Duties and Taxes Amount', ''))

                postal_code = clean_string(row.get('Postal Code', ''))

                values = (
                    date_printed,
                    amount_paid,
                    adjusted_amount,
                    quoted_amount,
                    row.get('Payment Type') if row.get('Payment Type') else None,
                    row.get('Shipment Status', ''),
                    tracking_number,
                    date_delivered,
                    row.get('Recipient') if row.get('Recipient') else None,
                    row.get('Name') if row.get('Name') else None,
                    row.get('Address 1') if row.get('Address 1') else None,
                    row.get('Address 2') if row.get('Address 2') else None,
                    row.get('Address 3') if row.get('Address 3') else None,
                    row.get('City') if row.get('City') else None,
                    row.get('State/Province') if row.get('State/Province') else None,
                    postal_code,
                    row.get('Country') if row.get('Country') else None,
                    clean_string(row.get('Origin Zip', '')),
                    row.get('Weight') if row.get('Weight') else None,
                    row.get('Carrier') if row.get('Carrier') else None,
                    row.get('Service') if row.get('Service') else None,
                    row.get('Tracking Confirmation') if row.get('Tracking Confirmation') else None,
                    row.get('Extra Services') if row.get('Extra Services') else None,
                    insured_for,
                    ship_date,
                    row.get('Cost Code') if row.get('Cost Code') else None,
                    row.get('Printed Message') if row.get('Printed Message') else None,
                    row.get('User') if row.get('User') else None,
                    row.get('Refund Type') if row.get('Refund Type') else None,
                    refund_request_date,
                    row.get('Refund Status') if row.get('Refund Status') else None,
                    row.get('Refund Requested') if row.get('Refund Requested') else None,
                    row.get('Reference 1') if row.get('Reference 1') else None,
                    row.get('Order ID') if row.get('Order ID') else None,
                    row.get('Store') if row.get('Store') else None,
                    order_date,
                    order_total,
                    row.get('Item SKUs') if row.get('Item SKUs') else None,
                    row.get('Items') if row.get('Items') else None,
                    product_total,
                    shipping_paid,
                    tax_paid,
                    row.get('Insurance Provider') if row.get('Insurance Provider') else None,
                    duties_and_taxes
                )

                if not DRY_RUN:
                    try:
                        cursor.execute("""
                            INSERT INTO `shipping_charges-usps_stampscom` (
                                date_printed, amount_paid, adjusted_amount, quoted_amount, payment_type,
                                shipment_status, tracking_number, date_delivered, recipient, name,
                                address_1, address_2, address_3, city, state_province,
                                postal_code, country, origin_zip, weight, carrier,
                                service, tracking_confirmation, extra_services, insured_for, ship_date,
                                cost_code, printed_message, user, refund_type, refund_request_date,
                                refund_status, refund_requested, reference_1, order_id, store,
                                order_date, order_total, item_skus, items, product_total,
                                shipping_paid, tax_paid, insurance_provider, duties_and_taxes_amount
                            ) VALUES (
                                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                                %s, %s, %s, %s
                            )
                        """, values)
                        imported += 1
                    except Exception as e:
                        logger.error(f"  Error importing tracking {tracking_number}: {e}")
                        skipped += 1
                else:
                    imported += 1

            logger.info(f"  {'Would import' if DRY_RUN else 'Imported'}: {imported}, Duplicates: {duplicates}, Skipped: {skipped}")

            total_imported += imported
            total_duplicates += duplicates
            total_skipped += skipped

        except Exception as e:
            logger.error(f"Error processing {csv_file}: {e}")
            continue

    if not DRY_RUN:
        conn.commit()

    logger.info(f"\nUSPS Import Summary:")
    logger.info(f"  {'Would import' if DRY_RUN else 'Imported'}: {total_imported}")
    logger.info(f"  Duplicates skipped: {total_duplicates}")
    logger.info(f"  Errors/Invalid: {total_skipped}")

    cursor.close()
    conn.close()

    return {'imported': total_imported, 'skipped': total_skipped, 'duplicates': total_duplicates}

# ============================================================================
# MAIN EXECUTION
# ============================================================================

def main():
    """Main execution function"""

    logger.info("="*80)
    logger.info(f"DATA IMPORT SCRIPT - {MONTH_NAME}")
    logger.info(f"{'DRY RUN MODE' if DRY_RUN else 'LIVE IMPORT MODE'}")
    logger.info("="*80)
    logger.info(f"Date Range: {DATE_START} to {DATE_END}")
    logger.info(f"Base Directory: {BASE_DIR}")

    if DRY_RUN:
        logger.warning("\n⚠️  DRY RUN MODE - No database changes will be made")
        logger.warning("⚠️  Set DRY_RUN = False to actually import data\n")

    # Import all data sources
    bolt_result = import_bolt_fees()
    shiphaven_result = import_shiphaven()
    usps_result = import_usps_stamps()

    # Final summary
    logger.info(f"\n{'='*80}")
    logger.info(f"FINAL IMPORT SUMMARY - {MONTH_NAME}")
    logger.info(f"{'='*80}")
    logger.info(f"Bolt Fees:")
    logger.info(f"  {'Would import' if DRY_RUN else 'Imported'}: {bolt_result['imported']}")
    logger.info(f"  Duplicates: {bolt_result['duplicates']}")
    logger.info(f"  Skipped: {bolt_result['skipped']}")
    logger.info(f"")
    logger.info(f"UPS ShipHaven:")
    logger.info(f"  {'Would import' if DRY_RUN else 'Imported'}: {shiphaven_result['imported']}")
    logger.info(f"  Duplicates: {shiphaven_result['duplicates']}")
    logger.info(f"  Skipped: {shiphaven_result['skipped']}")
    logger.info(f"")
    logger.info(f"USPS Stamps.com:")
    logger.info(f"  {'Would import' if DRY_RUN else 'Imported'}: {usps_result['imported']}")
    logger.info(f"  Duplicates: {usps_result['duplicates']}")
    logger.info(f"  Skipped: {usps_result['skipped']}")
    logger.info(f"")

    total_imported = bolt_result['imported'] + shiphaven_result['imported'] + usps_result['imported']
    total_duplicates = bolt_result['duplicates'] + shiphaven_result['duplicates'] + usps_result['duplicates']
    total_skipped = bolt_result['skipped'] + shiphaven_result['skipped'] + usps_result['skipped']

    logger.info(f"TOTAL:")
    logger.info(f"  {'Would import' if DRY_RUN else 'Imported'}: {total_imported}")
    logger.info(f"  Duplicates: {total_duplicates}")
    logger.info(f"  Skipped: {total_skipped}")
    logger.info(f"{'='*80}\n")

    if DRY_RUN:
        logger.warning("⚠️  This was a DRY RUN - no data was imported")
        logger.warning("⚠️  Set DRY_RUN = False and run again to import\n")

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