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
from scripts.mysql_queries import HISTORICAL_LOANS_DATA_QUERY

def get_cloudwatch_logger():
    log_group = os.environ.get('CLOUDWATCH_GROUP')
    log_stream = "daily_metrics_cron"
    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 process_daily_metrics(date_str=None):
    if not date_str:
        # Default to yesterday if run at midnight
        date_str = (datetime.now() - timedelta(days=1)).strftime("%Y-%m-%d")
        
    logger.send_log_event(f"INFO: Starting daily metrics cron for {date_str}")
    
    # 1. Get all active loan ids
    loans_sql = "SELECT DISTINCT loan_id FROM co_lending_loans WHERE deleted_at IS NULL"
    loans_df = execute_query(loans_sql)
    if loans_df is None or loans_df.empty:
        logger.send_log_event("INFO: No active loans found.")
        return
        
    loan_ids = [str(lid) for lid in loans_df['loan_id'].tolist()]
    
    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()
    """)
    
    total_processed = 0
    # Process in batches
    for i in range(0, len(loan_ids), 1000):
        batch = loan_ids[i:i+1000]
        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'] = date_str
        
        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": date_str,
                    "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)
                    total_processed += len(upsert_params)
                    
    logger.send_log_event(f"INFO: Successfully processed daily metrics for {total_processed} rows.")

if __name__ == "__main__":
    from dotenv import load_dotenv
    load_dotenv()
    process_daily_metrics()