import pandas as pd
from datetime import date
#import shutil

#from openpyxl import load_workbook
#from openpyxl.utils.dataframe import dataframe_to_rows
#from openpyxl.utils import get_column_letter, column_index_from_string 
#from openpyxl.styles import PatternFill
#from openpyxl.formatting.rule import DataBarRule, ColorScaleRule
#from openpyxl.styles import Border,Side , Font, Alignment, Color, GradientFill
import os
import shutil


def __remove_recreate_output_files(output_file):
    try:
        os.remove(output_file)
    except FileNotFoundError:
        pass             
    finally:
        with open(output_file, "w") as f:
            f.close()
    


def screen (data_csv, SEPARATOR, w_outputfile_csv, m_outputfile_csv, delivery_directory, extracted_data_history_directory, cap_quantile, vol_quantile, weekly_analysis=True, monthly_analysis=True, max_listed=25):
    
    error_w=None
    error_m=None

    try:
        df_data = pd.read_csv(data_csv, sep=SEPARATOR)
    except FileNotFoundError:
        print(f"File {data_csv} doesn't exist.")
        return None, f"File {data_csv} doesn't exist."
    except Exception as e:
        print(f"Error: {e} when reading file {data_csv}")
        return None, f"Error: {e} when reading file {data_csv}"
    
    actualdate = date.today().strftime("%Y.%m.%d")    

    # Convertir les colonnes en numérique et  en booléen
    numeric_cols = ['Market Cap', 'Volat. 30M annual.', 'PriceW', 'SMA40W', 'ROC13W', 'PriceM', 'SMA10M', 'ROC3M']
    df_data[numeric_cols] = df_data[numeric_cols].replace(',', '.', regex=True).apply(pd.to_numeric, errors='coerce')
  
    if weekly_analysis == True:
        df_filtered = df_data[(df_data['Market Cap'] <= df_data['Market Cap'].quantile(cap_quantile))
                            & (df_data['Volat. 30M annual.'] <= df_data['Volat. 30M annual.'].quantile(vol_quantile))
                            & (df_data['over SMA40W'] == True)]
        
        df_w_filtered = df_filtered[['Ticker', 'Market Cap', 'Volat. 30M annual.', 
                                'PriceW', 'SMA40W', 'ROC13W']]

        df_w_filtered = df_w_filtered.sort_values(by='ROC13W', ascending=False)

        df_w_filtered = df_w_filtered.head(max_listed)

        df_w_renamed = df_w_filtered.rename(columns={'Market Cap': 'Cap (MUSD)', 'Volat. 30M annual.':'Volat. 30 mois',
                                                     'PriceW': 'Prix', 'SMA40W': 'MM40 sem.', 'ROC13W': 'Var. 13 sem.'})
        __remove_recreate_output_files(w_outputfile_csv)
        df_w_renamed.to_csv(w_outputfile_csv, index=False,  sep=SEPARATOR)
        
        delivered_file = delivery_directory + actualdate + "_SCLV_HEBDO.csv"
        shutil.copy(w_outputfile_csv, delivered_file) 
        
        all_data_file_backup = extracted_data_history_directory + actualdate + "_SCLV-Data-retrieved.csv"
        shutil.copy(data_csv, all_data_file_backup)
        
        if  df_w_renamed.shape[0] <= 0:
            error_w=-1

    if monthly_analysis == True:
        
        df_filtered = df_data[(df_data['Market Cap'] <= df_data['Market Cap'].quantile(cap_quantile))
                            & (df_data['Volat. 30M annual.'] <= df_data['Volat. 30M annual.'].quantile(vol_quantile))
                            & (df_data['over SMA10M'] == True)]

        df_m_filtered = df_filtered[['Ticker', 'Market Cap', 'Volat. 30M annual.', 
                                     'PriceM', 'SMA10M', 'ROC3M']]

        #df_m_filtered['Date']=pd.Timestamp('now')  

        df_m_filtered = df_m_filtered.sort_values(by='ROC3M', ascending=False)

        df_m_filtered = df_m_filtered.head(max_listed)

        df_m_renamed = df_m_filtered.rename(columns={'Market Cap': 'Cap (MUSD)', 'Volat. 30M annual.':'Volat. 30 mois', 
                                                     'PriceM': 'Prix', 'SMA10M': 'MM10 mois', 'ROC3M': 'Var. 3 mois'})

        __remove_recreate_output_files(m_outputfile_csv)
        df_m_renamed.to_csv(m_outputfile_csv, index=False, sep=SEPARATOR)
        
        delivered_file = delivery_directory + actualdate + "_SCLV_MENSUEL.csv"         
        shutil.copy(m_outputfile_csv, delivered_file)
        
        all_data_file_backup = extracted_data_history_directory + actualdate + "_SCLV-Data-retrieved.csv"
        shutil.copy(data_csv, all_data_file_backup)
        
        if  df_m_renamed.shape[0] <= 0:
            error_m=-1
         
    
    return df_filtered, error_w, error_m