import pandas as pd
from openpyxl import load_workbook

DB_FILE = 'database.xlsx'

def migrate_uppercase():
    try:
        wb = load_workbook(DB_FILE)
        save_needed = False
        
        # 1. Update Sales Sheet
        if 'Sales' in wb.sheetnames:
            ws = wb['Sales']
            headers = [cell.value for cell in ws[1]]
            try:
                veh_idx = headers.index('Vehicle No')
                # Iterate rows (1-indexed, skip header)
                for row in ws.iter_rows(min_row=2):
                    cell = row[veh_idx]
                    if cell.value and isinstance(cell.value, str):
                        original = cell.value
                        upper = original.upper()
                        if original != upper:
                            cell.value = upper
                            save_needed = True
                            print(f"Sales: Updated {original} -> {upper}")
            except ValueError:
                print("Vehicle No column not found in Sales")

        # 2. Update Replacements Sheet
        if 'Replacements' in wb.sheetnames:
            ws = wb['Replacements']
            headers = [cell.value for cell in ws[1]]
            try:
                veh_idx = headers.index('Vehicle No')
                for row in ws.iter_rows(min_row=2):
                    cell = row[veh_idx]
                    if cell.value and isinstance(cell.value, str):
                        original = cell.value
                        upper = original.upper()
                        if original != upper:
                            cell.value = upper
                            save_needed = True
                            print(f"Replacements: Updated {original} -> {upper}")
            except ValueError:
                print("Vehicle No column not found in Replacements")
                
        if save_needed:
            wb.save(DB_FILE)
            print("Database updated successfully.")
        else:
            print("No changes needed. All vehicle numbers are already uppercase.")
            
    except Exception as e:
        print(f"Migration Failed: {e}")

if __name__ == "__main__":
    migrate_uppercase()