# Import necessary libraries
import pandas as pd 
import streamlit as st
import csv
import io
import matplotlib.pyplot as plt
import numpy as np
from pre import preprocess_uploaded_file

# Main function to process 2 uploaded CSV files 
def double_main(uploaded_file1,uploaded_file2):

  # Check if both files are uploaded
  if uploaded_file1 is not None and uploaded_file2 is not None:

    # Preprocess the uploaded CSV files
    data_1 = preprocess_uploaded_file(uploaded_file1) 
    data_2 = preprocess_uploaded_file(uploaded_file2)

    # Determine which file is older and newer
    if data_1['Start datetime'].min() < data_2['Start datetime'].min():
      older_df = data_1  
      newer_df = data_2
    else:
      older_df = data_2
      newer_df = data_1

    # Convert time columns to MM:SS format  
    older_df['Time spent'] = pd.to_datetime(older_df['Time spent'], unit='s').dt.strftime('%M:%S')
    newer_df['Time spent'] = pd.to_datetime(newer_df['Time spent'], unit='s').dt.strftime('%M:%S')
  
    # Get start datetime of each file  
    older_datetime = older_df['Start datetime'].min()
    newer_datetime = newer_df['Start datetime'].min()
    
    # Display start datetime of each file
    st.write(f"The older csv started on {older_datetime}") 
    st.write(f"The newer csv started on {newer_datetime}")

    # Merge dataframes on 'scenario name' 
    merged_df = pd.merge(older_df, newer_df, on=['Functional area', 'Scenario name'], suffixes=('_old', '_new'))

    # Filter scenarios that were failing and are still failing
    fail_to_fail_scenarios = merged_df[(merged_df['Status_old'] == 'FAILED') & (merged_df['Status_new'] == 'FAILED')]
    
    # Display Consistent Failures section
    st.markdown("### Consistent Failures(previously failing, now failing)")

    # Get failing scenarios count
    fail_count = len(fail_to_fail_scenarios)
    st.write(f"Failing scenarios Count: {fail_count}")

    # Display filtered dataframe 
    columns_to_display1 = ['Functional area', 'Scenario name', 'Error message_old', 'Error message_new']
    st.write(fail_to_fail_scenarios[columns_to_display1])

    # Filter scenarios that were passing and now failing
    pass_to_fail_scenarios = merged_df[(merged_df['Status_old'] == 'PASSED') & (merged_df['Status_new'] == 'FAILED')]

    # Display New Failures section 
    st.markdown("### New Failures(previously passing, now failing)")

    # Get failing scenarios count
    pass_fail_count = len(pass_to_fail_scenarios)
    st.write(f"Failing scenarios Count: {pass_fail_count}")

    # Display filtered dataframe
    columns_to_display2 = ['Functional area', 'Scenario name', 'Error message_new', 'Time spent_old','Time spent_new',] 
    st.write(pass_to_fail_scenarios[columns_to_display2])

    # Filter scenarios that were failing and now passing
    fail_to_pass_scenarios = merged_df[(merged_df['Status_old'] == 'FAILED') & (merged_df['Status_new'] == 'PASSED')]

    # Display New Passes section
    st.markdown("### New Passes(previously failing, now passing)")
    
    # Get passing scenarios count
    pass_count = len(fail_to_pass_scenarios)
    st.write(f"Passing scenarios Count: {pass_count}")

    # Display filtered dataframe
    columns_to_display3 = ['Functional area', 'Scenario name', 'Error message_old', 'Time spent_old','Time spent_new',]
    st.write(fail_to_pass_scenarios[columns_to_display3])


    # Create a Pandas Excel writer using XlsxWriter as the engine
    excel_writer = pd.ExcelWriter('comparison_results.xlsx', engine='xlsxwriter')
    
    # Write each section to a separate sheet
    fail_to_fail_scenarios.loc[:, columns_to_display1].to_excel(excel_writer, sheet_name='Consistent Failures', index=False)
    pass_to_fail_scenarios.loc[:, columns_to_display2].to_excel(excel_writer, sheet_name='New Failures', index=False)
    fail_to_pass_scenarios.loc[:, columns_to_display3].to_excel(excel_writer, sheet_name='New Passes', index=False)



    # Add a sheet to store information about CSV versions
    csv_version_sheet = excel_writer.book.add_worksheet('CSV Details')
    
    # Write the CSV version information
    csv_version_sheet.write('A1', 'Older CSV:')
    csv_version_sheet.write('B1', 'Newer CSV:')
    csv_version_sheet.write('A2', older_df['Start datetime'].min().strftime('%Y-%m-%d %H:%M:%S'))
    csv_version_sheet.write('B2', newer_df['Start datetime'].min().strftime('%Y-%m-%d %H:%M:%S'))
    
    # Save the Excel file
    excel_writer.save()

# Create a Download Excel button
    st.markdown("### Download Excel Report")
    st.markdown("Click below to download the comparison results in Excel format:")
    with open('comparison_results.xlsx', 'rb') as excel_file:
        excel_bytes = excel_file.read()
    st.download_button(label='Download Excel Report', data=excel_bytes, file_name='comparison_results.xlsx', key='excel-download')