import pandas as pd
from datetime import datetime
from utils import execute_query, get_cloudwatch_logger, local_tz, log_exception, engine
from scripts.mysql_queries import blacksoil_soft_delete_query, blacksoil_insert_query

logger = get_cloudwatch_logger(stream_name="blacksoil_rps_upload")

def validate_func(file_path):
    if not file_path.endswith(('.xlsx', '.csv')):
        return {"status": "FAILED", "message": "Please upload file in CSV or Excel file format."}
    
    try:
        # Handle CSV with comma separator (default)
        df = pd.read_excel(file_path) if file_path.endswith('.xlsx') else pd.read_csv(file_path)
    except Exception as e:
        log_exception(logger, f"Error reading file: {str(e)}")
        return {"status": "FAILED", "message": f"Error reading file: {str(e)}"}

    required_columns = [
        'Cust ID', 'Loan ID', 'EMI Date', 'Interest Due', 'Principal Due', 
        'EMI Amount', 'Opening Principal Outstanding', 'Closing Principal Outstanding'
    ]
    
    for col in required_columns:
        if col not in df.columns:
            logger.send_log_event(f"Incorrect file format uploaded. Missing column: {col}")
            return {"status": "FAILED", "message": "Incorrect file format uploaded"}
            
    if df.isnull().values.any():
        logger.send_log_event("Incorrect file format uploaded .No blank cells are allowed to be uploaded.")
        return {"status": "FAILED", "message": "Incorrect file format uploaded .No blank cells are allowed to be uploaded."}
        
    numeric_cols = df.select_dtypes(include=['number']).columns
    if (df[numeric_cols] < 0).values.any():
        logger.send_log_event("Incorrect file format uploaded.Negative value are not allowed")
        return {"status": "FAILED", "message": "Incorrect file format uploaded.Negative value are not allowed"}
        
    try:
        pd.to_datetime(df['EMI Date'], format='%d-%m-%Y')
    except ValueError:
        logger.send_log_event("Incorrect file format uploaded. Date should be in dd-mm-yyyy format.")
        return {"status": "FAILED", "message": "Incorrect file format uploaded. Date should be in dd-mm-yyyy format."}
        
    return "success"

def update_tables(file_path, data):
    try:
        # Get current time in local timezone
        now = datetime.now(local_tz).strftime('%Y-%m-%d %H:%M:%S')
        
        df = pd.read_excel(file_path) if file_path.endswith('.xlsx') else pd.read_csv(file_path)
        
        if df.empty:
            logger.send_log_event("Uploaded file is empty.")
            return {"status": "FAILED", "message": "Uploaded file is empty."}
            
        # Collect unique Loan IDs from the file for soft deletion
        unique_loan_ids = df['Loan ID'].unique().tolist()
        
        # Soft delete existing records using bulk IN clause
        batch_size = 1000
        for i in range(0, len(unique_loan_ids), batch_size):
            chunk = unique_loan_ids[i:i+batch_size]
            in_clause = ','.join([str(int(lid)) for lid in chunk])
            delete_query = f"UPDATE blacksoil_emi_payments SET deleted_at = '{now}' WHERE loan_id IN ({in_clause}) AND deleted_at IS NULL"
            execute_query(delete_query)
            
        logger.send_log_event(f"INFO: Soft deleted existing records for {len(unique_loan_ids)} unique loan IDs.")
        
        # Convert EMI Date to YYYY-MM-DD
        df['EMI Date'] = pd.to_datetime(df['EMI Date'], dayfirst=True).dt.strftime('%Y-%m-%d')
        
        # Rename columns to valid identifiers for itertuples efficiency
        df.columns = [
            'cust_id', 'loan_id', 'emi_date', 'interest_due', 'principal_due', 
            'emi_amount', 'opening_pos', 'closing_pos'
        ]
        
        # Prepare parameters for batch insert using itertuples
        params_list = []
        for row in df.itertuples(index=False):
            params_list.append({
                "cust_id": int(row.cust_id),
                "loan_id": int(row.loan_id),
                "emi_date": row.emi_date,
                "interest_due": float(row.interest_due),
                "principal_due": float(row.principal_due),
                "emi_amount": float(row.emi_amount),
                "opening_pos": float(row.opening_pos),
                "closing_pos": float(row.closing_pos),
                "created_at": now,
                "updated_at": now
            })
            
        # True Bulk Insert using pandas multi-row insertion for high performance
        insert_df = pd.DataFrame(params_list)
        if not insert_df.empty:
            with engine.begin() as conn:
                insert_df.to_sql(
                    name='blacksoil_emi_payments', 
                    con=conn, 
                    if_exists='append', 
                    index=False, 
                    method='multi', 
                    chunksize=1000
                )
        logger.send_log_event(f"INFO: Successfully inserted {len(params_list)} records for {len(unique_loan_ids)} unique loans.")
        
        return {"status": "SUCCESS", "message": f"Successfully updated RPS for {len(unique_loan_ids)} unique loans."}
        
    except Exception as e:
        log_exception(logger, f"Exception in update_tables: {str(e)}")
        return {"status": "FAILED", "message": f"Error updating tables: {str(e)}"}

async def function1(data):
    file_path = data.get("uploaded_file_path")
    is_verify_flag = data.get("is_verify")
    logger.send_log_event(f"INFO: BlackSoil RPS upload request received: {data}")
    
    if is_verify_flag is None:
        return {"status": "FAILED", "message": "Verify flag not provided."}
    
    validate_check = validate_func(file_path)
    if validate_check != "success":
        return validate_check
        
    if int(is_verify_flag) == 1:
        return update_tables(file_path, data)
    else:
        return {"status": "SUCCESS", "message": "Validation Successful."}