import os
import pandas as pd
from datetime import datetime, timedelta
from sqlalchemy import text
from utils import engine, execute_query
from core_utilities.cloudwatch_service import CloudWatchService

def get_cloudwatch_logger():
    log_group = os.environ.get('CLOUDWATCH_GROUP')
    log_stream = "historical_recalc"
    aws_access_key = os.environ.get('CLOUDWATCH_ACCESS_KEY')
    aws_secret_key = os.environ.get('CLOUDWATCH_SECRET_KEY')
    
    return CloudWatchService(
        log_group_name=log_group, 
        log_stream_name=log_stream,
        aws_access_key_id=aws_access_key,
        aws_secret_access_key=aws_secret_key
    )

logger = get_cloudwatch_logger()

def recalculate_historical_metrics(loan_ids, from_date_str, to_date_str=None):
    from scripts.mysql_queries import HISTORICAL_LOANS_DATA_QUERY
    
    if not loan_ids:
        return

    if not to_date_str:
        to_date_str = datetime.now().strftime("%Y-%m-%d")
        
    from_date = datetime.strptime(from_date_str, "%Y-%m-%d").date()
    to_date = datetime.strptime(to_date_str, "%Y-%m-%d").date()
    
    if from_date > to_date:
        return
        
    delta = (to_date - from_date).days
    dates_to_run = [from_date + timedelta(days=i) for i in range(delta + 1)]
    
    upsert_sql = text("""
        INSERT INTO co_lending_daily_metrics
        (loan_id, rps_type, as_on_date, dpd, principal_outstanding, interest_due, interest_outstanding,
         total_bounce_due, total_penal_due, amount_overdue, total_outstanding, created_at, updated_at)
        VALUES 
        (:loan_id, :rps_type, :as_on_date, :dpd, :principal_outstanding, :interest_due, :interest_outstanding,
         :total_bounce_due, :total_penal_due, :amount_overdue, :total_outstanding, NOW(), NOW())
        ON DUPLICATE KEY UPDATE 
            dpd = VALUES(dpd),
            principal_outstanding = VALUES(principal_outstanding),
            interest_due = VALUES(interest_due),
            interest_outstanding = VALUES(interest_outstanding),
            total_bounce_due = VALUES(total_bounce_due),
            total_penal_due = VALUES(total_penal_due),
            amount_overdue = VALUES(amount_overdue),
            total_outstanding = VALUES(total_outstanding),
            updated_at = NOW()
    """)
    
    for as_on_date in dates_to_run:
        for i in range(0, len(loan_ids), 500):
            batch = loan_ids[i:i+500]
            format_strings = ','.join([':l_' + str(j) for j in range(len(batch))])
            params = {f'l_{j}': str(lid) for j, lid in enumerate(batch)}
            params['as_on_date'] = as_on_date.strftime("%Y-%m-%d")
            
            modified_query = HISTORICAL_LOANS_DATA_QUERY.replace(
                "WHERE ep.deleted_at IS NULL",
                f"WHERE ep.deleted_at IS NULL AND ep.loan_id IN ({format_strings})"
            )
            
            df = execute_query(modified_query, params=params)
            
            if df is not None and not df.empty:
                upsert_params = []
                for _, row in df.iterrows():
                    upsert_params.append({
                        "loan_id": str(row['loan_id']),
                        "rps_type": row['rps_type'],
                        "as_on_date": as_on_date.strftime("%Y-%m-%d"),
                        "dpd": int(row['dpd']) if pd.notnull(row['dpd']) else 0,
                        "principal_outstanding": float(row['principal_outstanding']) if pd.notnull(row['principal_outstanding']) else 0.0,
                        "interest_due": float(row['interest_due']) if pd.notnull(row['interest_due']) else 0.0,
                        "interest_outstanding": float(row['interest_outstanding']) if pd.notnull(row['interest_outstanding']) else 0.0,
                        "total_bounce_due": float(row['total_bounce_due']) if pd.notnull(row['total_bounce_due']) else 0.0,
                        "total_penal_due": float(row['total_penal_due']) if pd.notnull(row['total_penal_due']) else 0.0,
                        "amount_overdue": float(row['amount_overdue']) if pd.notnull(row['amount_overdue']) else 0.0,
                        "total_outstanding": float(row['total_outstanding']) if pd.notnull(row['total_outstanding']) else 0.0
                    })
                
                if upsert_params:
                    with engine.begin() as conn:
                        conn.execute(upsert_sql, upsert_params)
                    
    logger.send_log_event(f"INFO: Completed historical recalculation for {len(loan_ids)} loans from {from_date_str} to {to_date_str}")