import streamlit as st
import pandas as pd
import json
from datetime import datetime
import io
import os
import pytz # Assicurati che pytz sia nel requirements.txt

# --- Inizializzazione Session State ---
if 'file_uploader_key_json' not in st.session_state:
    st.session_state.file_uploader_key_json = 0
if 'file_uploader_key_customers' not in st.session_state:
    st.session_state.file_uploader_key_customers = 0

# --- CSS personalizzato ---
st.markdown(
    """
    <style>
    .stApp {
        background-color: #FFFFCC; /* Giallo pastello */
        background-attachment: fixed;
    }
    h1, h2 {
        color: #1E90FF; /* Blu acceso */
    }
    p, label {
        color: #000080; /* Blu scuro */
    }
    .stButton>button {
        color: white;
        background-color: #1E90FF; /* Blu acceso */
        border: none;
        border-radius: 5px;
        padding: 10px 20px;
        font-size: 16px;
    }
    .stButton>button:hover {
        background-color: #4169E1; /* Blu medio al passaggio del mouse */
    }
    </style>
    """,
    unsafe_allow_html=True
)

# Definizione del fuso orario per l'Italia (per coerenza, anche se le date Outbound sembrano solo data)
ITALY_TIMEZONE = pytz.timezone("Europe/Rome")
UTC_TIMEZONE = pytz.utc

# Funzione di conversione per Outbound
def convert_json_to_excel_data_outbound(json_file_content: str, customers_df: pd.DataFrame = None):
    try:
        json_data = json.loads(json_file_content)

        # Campi a livello radice
        site_code_json = json_data.get('siteCode')
        company_code_json = json_data.get('companyCode')

        # Campi sotto 'outbound'
        outbound_info = json_data.get('outbound', {})
        
        order_number = outbound_info.get('orderNumber')
        order_type_json = outbound_info.get('orderType')
        delivery_type_fixed = "DELIVERY" # Valore fisso

        order_doc_date_str = outbound_info.get('orderDocDate')
        order_date_detail = outbound_info.get('orderDateDetail')
        due_date_str = outbound_info.get('orderDate') # Corrisponde a 'dueDate'

        reason_code = outbound_info.get('reasonCode')
        service_fixed = "PDB" # Valore fisso
        service_type_fixed = "Standard" # Valore fisso

        payment_info_value = outbound_info.get('paymentInfo', {}).get('value')
        
        delivery_customer = outbound_info.get('deliveryCustomer', {})
        customer_code = delivery_customer.get('code')
        customer_mail = delivery_customer.get('refMail') # customer.mail dal JSON

        # Consignee data (from JSON)
        consignee_name = delivery_customer.get('name')
        consignee_address = delivery_customer.get('address')
        consignee_city = delivery_customer.get('city')
        consignee_province = delivery_customer.get('province')
        consignee_zip_code = delivery_customer.get('zipCode')
        consignee_country = delivery_customer.get('country')

        # Customer data (from Excel lookup)
        customer_name = ""
        customer_address = ""
        customer_city = ""
        customer_province = ""
        customer_zip_code = ""
        customer_country = ""
        
        # Esegui la lookup solo se customers_df è fornito e non vuoto
        if customers_df is not None and not customers_df.empty and customer_code and company_code_json:
            # Assicurati che le colonne di join siano dello stesso tipo
            # customers_df['Code'] = customers_df['Code'].astype(str)
            # customers_df['company.code'] = customers_df['company.code'].astype(str)
            
            # Filtra per Code e company.code
            customer_data = customers_df[
                (customers_df['Code'].astype(str) == str(customer_code)) &
                (customers_df['company.code'].astype(str) == str(company_code_json))
            ]

            if not customer_data.empty:
                # Prendiamo la prima riga se ce ne sono più di una
                customer_row = customer_data.iloc[0]
                customer_name = customer_row.get('partner.name', '')
                customer_address = customer_row.get('partner.address', '')
                customer_city = customer_row.get('partner.address.city', '')
                customer_province = customer_row.get('partner.address.province', '')
                customer_zip_code = customer_row.get('partner.address.zipCode', '')
                customer_country = customer_row.get('partner.address.country', '')
            else:
                st.warning(f"Attenzione: Dettagli cliente non trovati nell'Excel per Code: '{customer_code}' e Company: '{company_code_json}'.")

        # Note fields
        note_ddt = outbound_info.get('noteDdt')
        note_warehouse = outbound_info.get('noteWarehouse')
        note_delivery = outbound_info.get('noteDelivery')
        note_shipment = outbound_info.get('noteShipment')
        reason_code_shp = outbound_info.get('reasonCodeShp')
        info_delivery = outbound_info.get('infoDelivery')
        booking_info = outbound_info.get('bookingInfo')

        # Formattazione delle date (Outbound sembra avere solo date, senza ora)
        order_doc_date_formatted = None
        if order_doc_date_str:
            try:
                # Rimuovi 'Z' se presente e converti a datetime senza info fuso orario, poi formatta
                dt_object = datetime.fromisoformat(order_doc_date_str.replace('Z', ''))
                order_doc_date_formatted = dt_object.strftime('%d/%m/%Y')
            except ValueError:
                order_doc_date_formatted = order_doc_date_str
                st.warning(f"Attenzione: Impossibile parsare 'orderDocDate' '{order_doc_date_str}'. Verrà usata la stringa originale.")
        
        due_date_formatted = None
        if due_date_str: # Questo è il campo 'orderDate' del JSON che diventa 'dueDate'
            try:
                dt_object = datetime.fromisoformat(due_date_str.replace('Z', ''))
                due_date_formatted = dt_object.strftime('%d/%m/%Y')
            except ValueError:
                due_date_formatted = due_date_str
                st.warning(f"Attenzione: Impossibile parsare 'orderDate' (dueDate) '{due_date_str}'. Verrà usata la stringa originale.")

        details = outbound_info.get('details', [])

        processed_data_for_this_file = []
        for item in details:
            row = {
                'siteCode': site_code_json,
                'companyCode': company_code_json,
                'orderNumber': order_number,
                'orderType': order_type_json, 
                'deliveryType': delivery_type_fixed, 
                'orderDocDate': order_doc_date_formatted,
                'orderDateDetail': order_date_detail,
                'dueDate': due_date_formatted,
                'reasonCode': reason_code,
                'service': service_fixed, 
                'serviceType': service_type_fixed, 
                'paymentInfo.value': payment_info_value,
                'customer.code': customer_code,
                'customer.name': customer_name, # Da Excel
                'customer.address': customer_address, # Da Excel
                'customer.city': customer_city, # Da Excel
                'customer.province': customer_province, # Da Excel
                'customer.zipCode': customer_zip_code, # Da Excel
                'customer.country': customer_country, # Da Excel
                'consignee.name': consignee_name, # Dal JSON
                'consignee.address': consignee_address, # Dal JSON
                'consignee.city': consignee_city, # Dal JSON
                'consignee.province': consignee_province, # Dal JSON
                'consignee.zipCode': consignee_zip_code, # Dal JSON
                'consignee.country': consignee_country, # Dal JSON
                'customer.mail': customer_mail, # Dal JSON
                'details.positionId': item.get('positionId'),
                'details.itemNumber': item.get('itemCode'), # Rinominato da itemCode
                'details.requestedQt': item.get('quantity'), # Rinominato da quantity
                'details.lotId': item.get('lotId'),
                'details.stockOwner': item.get('stockOwner'),
                'details.magCli': item.get('warehouse'), # Rinominato da warehouse
                'details.availability': item.get('availability'),
                'details.accountingStatus': item.get('accountingStatus'),
                'details.refOrder': item.get('refOrder'),
                'noteDdt': note_ddt,
                'noteWarehouse': note_warehouse,
                'noteDelivery': note_delivery,
                'noteShipment': note_shipment,
                'reasonCodeShp': reason_code_shp,
                'infoDelivery': info_delivery,
                'bookingInfo': booking_info
            }
            processed_data_for_this_file.append(row)

        if not processed_data_for_this_file:
            return pd.DataFrame() 

        return pd.DataFrame(processed_data_for_this_file)

    except json.JSONDecodeError as e:
        st.error(f"Errore nel file JSON: {e}. Assicurati che il contenuto sia un JSON valido.")
        return None
    except KeyError as ke:
        st.error(f"Errore: Chiave JSON mancante o struttura inattesa: {ke}. Assicurati che il JSON abbia la struttura attesa.")
        return None
    except Exception as e:
        st.error(f"Si è verificato un errore generico durante la conversione di un file JSON: {e}")
        return None

# --- Interfaccia Streamlit ---
st.title("Convertitore JSON Outbound a Template Excel Outbound")
st.write("Carica i tuoi file JSON degli ordini Outbound e un file Excel con i dati dei clienti.")

st.header("1. Carica il file Excel dei Clienti")
st.info("Questo file Excel deve contenere le colonne: `Code`, `company.code`, `partner.name`, `partner.address`, `partner.address.city`, `partner.address.province`, `partner.address.zipCode`, `partner.address.country`.")
uploaded_customers_file = st.file_uploader(
    "Scegli il file Excel con i dettagli dei Clienti",
    type=["xlsx", "xls"],
    key=st.session_state.file_uploader_key_customers
)

customers_df = pd.DataFrame()
if uploaded_customers_file:
    try:
        customers_df = pd.read_excel(uploaded_customers_file)
        # Rinomina le colonne per facilitare l'accesso, se necessario, o assicurati che i nomi corrispondano
        # Es: customers_df.rename(columns={'Codice': 'Code', 'Nome': 'partner.name'}, inplace=True)
        # Assicurati che le colonne per il merge siano stringhe per evitare problemi di tipo
        customers_df['Code'] = customers_df['Code'].astype(str)
        customers_df['company.code'] = customers_df['company.code'].astype(str)
        st.success("File Excel Clienti caricato con successo.")
        # Debug: st.dataframe(customers_df.head())
    except Exception as e:
        st.error(f"Errore nel caricamento del file Excel Clienti: {e}. Assicurati che sia un file Excel valido e che le colonne siano corrette.")
        customers_df = pd.DataFrame() # Reset in caso di errore

st.header("2. Carica i tuoi file JSON degli Ordini Outbound")
uploaded_json_files = st.file_uploader(
    "Scegli uno o più file JSON di tipo 'Ordine_Outbound'",
    type=["json"],
    accept_multiple_files=True,
    key=st.session_state.file_uploader_key_json
)

all_processed_data = []

if uploaded_json_files and not customers_df.empty:
    st.info(f"Caricati {len(uploaded_json_files)} file JSON. Elaborazione in corso...")
    
    conversion_successful = True
    
    for uploaded_file in uploaded_json_files:
        file_name = uploaded_file.name
        
        if not file_name.lower().startswith("ordine_outbound"):
            st.error(f"Errore: Il file '{file_name}' non inizia con 'Ordine_Outbound'. Verrà ignorato.")
            conversion_successful = False
            continue
        
        try:
            json_content_bytes = uploaded_file.read()
            json_content_string = json_content_bytes.decode('utf-8')

            # Passa customers_df alla funzione di conversione
            df_this_file = convert_json_to_excel_data_outbound(json_content_string, customers_df)
            
            if df_this_file is not None and not df_this_file.empty: 
                all_processed_data.append(df_this_file)
            elif df_this_file is None:
                conversion_successful = False
                st.error(f"Impossibile convertire il file '{file_name}'. Controlla i log per i dettagli.")
                    
        except Exception as e:
            st.error(f"Si è verificato un errore imprevisto durante la lettura o l'elaborazione del file '{file_name}': {e}")
            conversion_successful = False

    if all_processed_data:
        final_df = pd.concat(all_processed_data, ignore_index=True)
        st.success(f"Elaborazione di {len(all_processed_data)} file JSON completata! Pronto per il download.")
        
        # --- Post-processing per la formattazione Excel ---
        
        # Definizione delle colonne di testata
        header_columns = [
            'siteCode', 'companyCode', 'orderNumber', 'orderType', 'deliveryType',
            'orderDocDate', 'orderDateDetail', 'dueDate', 'reasonCode',
            'service', 'serviceType', 'paymentInfo.value', 
            'customer.code', 'customer.name', 'customer.address', 'customer.city',
            'customer.province', 'customer.zipCode', 'customer.country',
            'consignee.name', 'consignee.address', 'consignee.city', 'consignee.province',
            'consignee.zipCode', 'consignee.country', 'customer.mail',
            'noteDdt', 'noteWarehouse', 'noteDelivery', 'noteShipment',
            'reasonCodeShp', 'infoDelivery', 'bookingInfo'
        ]

        # Ordina il DataFrame per orderNumber e details.positionId per raggruppare gli ordini
        final_df = final_df.sort_values(by=['orderNumber', 'details.positionId']).reset_index(drop=True)

        # Crea una maschera booleana per identificare le righe duplicate per orderNumber
        rows_to_clear_headers = final_df.duplicated(subset=['orderNumber'], keep='first')
        
        # Azzerare le colonne di testata per le righe identificate
        final_df.loc[rows_to_clear_headers, header_columns] = ""

        # --- Creazione file Excel ---
        output = io.BytesIO()
        writer = pd.ExcelWriter(output, engine='xlsxwriter')
        
        sheet_name_excel = 'OutboundOrder'

        workbook = writer.book
        worksheet = writer.sheets.get(sheet_name_excel)
        if worksheet is None:
            worksheet = workbook.add_worksheet(sheet_name_excel)

        # Lista delle colonne finali nell'ordine desiderato
        final_columns_order = [
            'siteCode', 'companyCode', 'orderNumber', 'orderType', 'deliveryType',
            'orderDocDate', 'orderDateDetail', 'dueDate', 'reasonCode',
            'service', 'serviceType', 'paymentInfo.value', 
            'customer.code', 'customer.name', 'customer.address', 'customer.city',
            'customer.province', 'customer.zipCode', 'customer.country',
            'consignee.name', 'consignee.address', 'consignee.city', 'consignee.province',
            'consignee.zipCode', 'consignee.country', 'customer.mail',
            'details.positionId', 'details.itemNumber', 'details.requestedQt',
            'details.lotId', 'details.stockOwner', 'details.magCli', 'details.availability',
            'details.accountingStatus', 'details.refOrder',
            'noteDdt', 'noteWarehouse', 'noteDelivery', 'noteShipment',
            'reasonCodeShp', 'infoDelivery', 'bookingInfo'
        ]
        
        # Assicurati che il DataFrame abbia tutte le colonne nell'ordine desiderato
        for col in final_columns_order:
            if col not in final_df.columns:
                final_df[col] = "" 
        final_df = final_df[final_columns_order]

        # Larghezze delle colonne aggiornate (adattate per Outbound)
        col_widths = {
            'siteCode': 10, 'companyCode': 15, 'orderNumber': 15, 'orderType': 10, 'deliveryType': 12,
            'orderDocDate': 12, 'orderDateDetail': 15, 'dueDate': 12, 'reasonCode': 12, # Date solo gg/mm/aaaa
            'service': 10, 'serviceType': 12, 'paymentInfo.value': 15, 
            'customer.code': 15, 'customer.name': 30, 'customer.address': 30, 'customer.city': 15, 
            'customer.province': 10, 'customer.zipCode': 10, 'customer.country': 10,
            'consignee.name': 30, 'consignee.address': 30, 'consignee.city': 15, 'consignee.province': 10,
            'consignee.zipCode': 10, 'consignee.country': 10, 'customer.mail': 25,
            'details.positionId': 12, 'details.itemNumber': 20, 'details.requestedQt': 15,
            'details.lotId': 10, 'details.stockOwner': 15, 'details.magCli': 15, 'details.availability': 15,
            'details.accountingStatus': 18, 'details.refOrder': 15,
            'noteDdt': 20, 'noteWarehouse': 20, 'noteDelivery': 20, 'noteShipment': 20,
            'reasonCodeShp': 15, 'infoDelivery': 20, 'bookingInfo': 20
        }
        
        # Formato data per le colonne di data (solo data, senza ora)
        date_format = workbook.add_format({'num_format': 'dd/mm/yyyy'})

        # Scrivi i nomi delle colonne nella riga 2 (indice 1 in xlsxwriter)
        for col_num, col_name in enumerate(final_df.columns.values):
            worksheet.write(1, col_num, col_name) 

            # Imposta le larghezze delle colonne
            if col_name in col_widths:
                worksheet.set_column(col_num, col_num, col_widths[col_name])
            else:
                worksheet.set_column(col_num, col_num, 15) 
            
            # Applica il formato data alle colonne 'orderDocDate' e 'dueDate'
            if col_name in ['orderDocDate', 'dueDate']:
                worksheet.set_column(col_num, col_num, col_widths[col_name], date_format)

        # Scrivi i dati a partire dalla riga 3 (indice 2 in xlsxwriter)
        final_df.to_excel(writer, sheet_name=sheet_name_excel, index=False, header=False, startrow=2)

        writer.close()
        output.seek(0)

        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        output_file_name = f"OutboundOrders_{timestamp}.xlsx"

        st.download_button(
            label="Scarica file Excel Unificato",
            data=output.getvalue(),
            file_name=output_file_name,
            mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        )
    elif uploaded_json_files and customers_df.empty:
        st.warning("Per elaborare i file JSON, devi prima caricare un file Excel con i dettagli dei clienti.")
    elif not conversion_successful:
        st.error("Nessun file valido è stato elaborato con successo. Controlla i messaggi di errore sopra.")
    else:
        st.info("Carica i tuoi file JSON per iniziare.")

# --- Funzione per il reset ---
def clear_inputs():
    st.session_state.file_uploader_key_json += 1 
    st.session_state.file_uploader_key_customers += 1

st.markdown("---")
st.button("Pulisci Input (RESET)", on_click=clear_inputs)

st.markdown("Sviluppato con Streamlit e Python.")