#!/usr/bin/env python3
"""
Copy manually updated supplier prices from orders_items_margin to orders_items_margin_detailed
"""
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

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

def copy_manual_prices():
    """Copy manual supplier prices to detailed table"""

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

    print(f"\n{'='*80}")
    print(f"COPYING MANUAL SUPPLIER PRICES TO DETAILED TABLE")
    print(f"{'='*80}\n")

    # First, get all order_item_ids that need updating
    cursor.execute("""
        SELECT
            oim.order_item_id,
            oim.supplier_price,
            oim.supplier_shipping,
            oim.supplier_handling,
            oimd.usauto_rebate
        FROM orders_items_margin oim
        JOIN orders_items_margin_detailed oimd ON oim.order_item_id = oimd.order_item_id
        WHERE oim.is_manual_update = 1
          AND oim.supplier_price > 0
          AND (oimd.supplier_price IS NULL OR oimd.supplier_price = 0)
    """)

    records = cursor.fetchall()
    total = len(records)

    print(f"Found {total} records to update\n")

    if total == 0:
        print("No records need updating")
        cursor.close()
        conn.close()
        return

    # Update in batches
    updated = 0
    batch_size = 100

    for i in range(0, total, batch_size):
        batch = records[i:i+batch_size]

        for record in batch:
            order_item_id = record['order_item_id']
            supplier_price = record['supplier_price']
            supplier_shipping = record['supplier_shipping'] or 0
            supplier_handling = record['supplier_handling'] or 0
            usauto_rebate = record['usauto_rebate'] or 0

            our_buy_price = supplier_price + supplier_shipping + supplier_handling - usauto_rebate

            cursor.execute("""
                UPDATE orders_items_margin_detailed
                SET
                    supplier_price = %s,
                    supplier_shipping = %s,
                    supplier_handling = %s,
                    our_buy_price = %s,
                    buy_price_source = 'Manual Entry',
                    our_shipping_source = 'Manual Entry',
                    is_manual_update = 1
                WHERE order_item_id = %s
            """, (supplier_price, supplier_shipping, supplier_handling, our_buy_price, order_item_id))

            updated += 1

        conn.commit()
        print(f"Progress: {updated}/{total} records updated ({(updated/total)*100:.1f}%)")

    print(f"\n{'='*80}")
    print(f"SUMMARY")
    print(f"{'='*80}")
    print(f"Total records updated: {updated}")
    print(f"{'='*80}\n")

    cursor.close()
    conn.close()

if __name__ == '__main__':
    copy_manual_prices()