import sys
import os
import pandas as pd
import boto3
import traceback
import time
from datetime import datetime
from dotenv import load_dotenv
import shutil


from core_utilities.mail_service import send_mail_with_attachment
from core_utilities import cf_encrypt_decrypt
from utils import execute_query, get_cloudwatch_logger
from scripts.mysql_queries import merchant_collection_report_query

load_dotenv()

stream_name = 'merchant_collection_report'
logger = get_cloudwatch_logger(stream_name=stream_name)


def generate_merchant_collection_report():
    try:
        logger.send_log_event(f"[{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}] INFO: Starting Merchant Collection Report Script")
        
        df = execute_query(merchant_collection_report_query)
        if df.empty:
            logger.send_log_event(f"[{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}] INFO: No cases found matching the criteria.")
            return

        # Decrypt contact fields for all rows
        secret_key = os.environ.get('CF_ENCRYPT_DECRYPT_KEY')
        cols_to_decrypt = [
            'merchant_email', 'contact_number', 'rcp_mobile', 
            'sales_head_mobile', 'finance_head_mobile', 'mobile_number'
        ]
        
        def safe_decrypt(val):
            if pd.isna(val) or not str(val).strip() or str(val).strip() == 'None':
                return val
            return cf_encrypt_decrypt.wall_decrypt(str(val), key=secret_key)
            
        for col in cols_to_decrypt:
            if col in df.columns:
                df[col] = df[col].apply(safe_decrypt)

        emails_sent = 0
        total_merchants = 0
        summary_data = []

        # Create output directory for excel files
        output_dir = "/tmp/collection_reports"
        if not os.path.exists(output_dir):
            os.makedirs(output_dir)

        for merchant_id, group_df in df.groupby('merchant_id'):
            total_merchants += 1
            
            merchant_name = str(group_df['merchant_name'].iloc[0])
            merchant_email = str(group_df['merchant_email'].iloc[0])
            
            # Log the decrypted fields for this merchant
            logger.send_log_event(f"[{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}] INFO: Merchant {merchant_name} Decrypted Info - Email: {merchant_email}, Contact: {group_df['contact_number'].iloc[0] if 'contact_number' in group_df else ''}, RCP: {group_df['rcp_mobile'].iloc[0] if 'rcp_mobile' in group_df else ''}")
            
            # If email is null or missing, skip
            if pd.isna(merchant_email) or not merchant_email.strip() or merchant_email.strip() == 'None':
                logger.send_log_event(f"[{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}] WARNING: Skipping merchant {merchant_name} (ID: {merchant_id}) due to missing email.")
                continue

            # Drop internal and contact columns for the Excel report
            columns_to_drop = [
                'merchant_email', 'contact_number', 'rcp_mobile', 
                'sales_head_mobile', 'finance_head_mobile', 'merchant_id', 'lender_id',
                'application_id', 'total_delay_days', 'merchant_name', 'lender_name'
            ]
            report_df = group_df.drop(columns=[col for col in columns_to_drop if col in group_df.columns])

            # Rename columns to user-friendly names
            rename_mapping = {
                'loan_application_no': 'Loan Application No',
                'customer_name': 'Customer Name',
                'mobile_number': 'Contact',
                'overdue_amount': 'Overdue Amount',
                'emi': 'Installment Amount',
                'last_payment_date': 'Last Payment Date'
            }
            report_df = report_df.rename(columns=rename_mapping)
            
            # Reorder columns exactly as requested
            desired_order = [
                'Loan Application No', 
                'Customer Name', 
                'Contact', 
                'Overdue Amount', 
                'Installment Amount', 
                'Last Payment Date'
            ]
            # Ensure we only include columns that exist to prevent KeyErrors
            final_columns = [col for col in desired_order if col in report_df.columns]
            report_df = report_df[final_columns]

            # Generate Excel File securely naming it
            safe_name = "".join([c for c in merchant_name if c.isalpha() or c.isdigit() or c==' ']).rstrip()
            file_name = f"{output_dir}/Collection_Report_{safe_name.replace(' ', '_')}_{datetime.now().strftime('%d_%b_%Y')}.xlsx"
            report_df.to_excel(file_name, index=False)
            
            # Send Email
            case_count = len(report_df)
            
            # --- TEST LOGIC OVERRIDE ---
            if os.environ.get('TEST_MERCHANT_REPORT') == '1':
                merchant_email = "ashish.gupta@creditfair.in"
                cc_list = []
            else:
                cc_list = ['imran@creditfair.in', 'ketan@creditfair.in', 'amol@creditfair.in']
                
            # If the database contains multiple comma-separated emails, extract the first
            # as the 'To' address, and append the rest to the 'CC' list.
            if isinstance(merchant_email, str) and ',' in merchant_email:
                emails = [e.strip() for e in merchant_email.split(',') if e.strip()]
                if emails:
                    merchant_email = emails[0]
                    cc_list.extend(emails[1:])
            
            html_body = f"""
            <p>Dear {merchant_name},</p>
            <p>Please find your pending collection cases attached. We need your support to clear these cases as soon as possible.</p>
            <p>For discussing please call us on 8850077108 / 8928769161.</p>
            """
            
            if case_count < 6:
                html_body += "<br><br>" + report_df.to_html(index=False, border=1, justify='center')
                
            success = send_mail_with_attachment(
                customer_mail=merchant_email,
                message=html_body,
                subject="Pending Collection Cases for more than 5 days",
                attachment_path=file_name,
                cc_list=cc_list,
                bcc_list=[]
            )
            
            if success:
                emails_sent += 1
                logger.send_log_event(f"[{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}] INFO: Successfully sent report to {merchant_email} for {merchant_name}. Total applications: {case_count}")
                summary_data.append({'Merchant Name': merchant_name, 'Total Applications': case_count, 'Mail Status': 'Success'})
            else:
                logger.send_log_event(f"[{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}] ERROR: Failed to send report to {merchant_email} for {merchant_name}.")
                summary_data.append({'Merchant Name': merchant_name, 'Total Applications': case_count, 'Mail Status': 'Failed'})
                
            if os.environ.get('TEST_MERCHANT_REPORT') == '1':
                time.sleep(3)
        
        # Send Summary Email
        if summary_data:
            summary_df = pd.DataFrame(summary_data)
            failed_count = len(summary_df[summary_df['Mail Status'] == 'Failed'])
            
            # Generate summary Excel file
            summary_file_name = f"{output_dir}/Summary_Report_{datetime.now().strftime('%d_%b_%Y')}.xlsx"
            summary_df.to_excel(summary_file_name, index=False)
            
            summary_html_body = f"""
            <p>Dear Team,</p>
            <p>Please find the summary of the Merchant Collection Report execution below.</p>
            <ul>
                <li>Total Merchants Processed: {total_merchants}</li>
                <li>Total Emails Sent Successfully: {emails_sent}</li>
                <li>Total Emails Failed: {failed_count}</li>
            </ul>
            <br>
            <p>Please find the detailed summary attached.</p>
            <br>
            <p>Regards,<br>Creditfair Tech</p>
            """
            
            if os.environ.get('TEST_MERCHANT_REPORT') == '1':
                summary_to = "ashish.gupta@creditfair.in"
                summary_cc = []
            else:
                summary_to = ['abhishek.shetty@creditfair.in', 'imran@creditfair.in', 'ketan@creditfair.in', 'amol@creditfair.in', 'ashish.gupta@creditfair.in']
                summary_cc = []

            summary_success = send_mail_with_attachment(
                customer_mail=summary_to,
                message=summary_html_body,
                subject="Summary: Merchant Collection Report",
                attachment_path=summary_file_name,
                cc_list=summary_cc
            )
            if summary_success:
                logger.send_log_event(f"[{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}] INFO: Successfully sent summary report.")
            else:
                logger.send_log_event(f"[{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}] ERROR: Failed to send summary report.")

        logger.send_log_event(f"[{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}] INFO: Finished execution. Total Merchants processed: {total_merchants}. Emails sent: {emails_sent}.")
        print(f"Finished execution. Total Merchants processed: {total_merchants}. Emails sent: {emails_sent}.")
        
        # Cleanup temporary files
        if os.path.exists(output_dir):
            shutil.rmtree(output_dir)
            logger.send_log_event(f"[{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}] INFO: Cleaned up temporary directory {output_dir}")
        
    except Exception as e:
        logger.send_log_event(f"[{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}] ERROR: Error in main execution: {str(e)}")
        traceback.print_exc()

if __name__ == "__main__":
    generate_merchant_collection_report()