import json
from datetime import datetime
from typing import Optional, Dict, Any, Union
from supabase import create_client, Client
import boto3
import traceback
from openpyxl import Workbook
from openpyxl.styles import Font
from io import BytesIO
from email_service import email_service  # Email notifications for cron-based runs

# Initialize AWS SSM client
ssm = boto3.client('ssm')

def get_parameter(name):
    """Get a parameter from AWS SSM Parameter Store"""
    response = ssm.get_parameter(
        Name=name,
        WithDecryption=True
    )
    return response['Parameter']['Value']


def get_timestamp() -> str:
    """
    Generate timestamp string for filename (YYYYMMDD_HHMMSS format).
    
    Returns:
        Timestamp string in format YYYYMMDD_HHMMSS
    """
    now = datetime.utcnow()
    # Format: YYYYMMDD_HHMMSS (remove dashes, colons, T, and everything after seconds)
    return now.strftime('%Y%m%d_%H%M%S')


def extract_params(payload: Dict[str, Any]) -> Dict[str, Optional[Union[str, int]]]:
    """
    Extract report parameters from the request payload.
    Supports both snake_case (customer_id) and camelCase (customerId) formats.
    The kaching daily RPC itself does not take these parameters directly, but we keep:
      - customer_id / customerId: for optional metadata (naming/response)
    
    Args:
        payload: Request payload with customer_id/customerId
        
    Returns:
        Dictionary with customerId
    """
    return {
        'customerId': payload.get('customer_id') or payload.get('customerId')
    }


def lambda_handler(event: Dict[str, Any], context: Any) -> Dict[str, Any]:
    """
    Lambda handler to generate a **Kaching Daily Report** and store it as an Excel file in Supabase Storage.
    
    Data source:
        public.get_active_kachings_with_activity_daily(
            p_offset integer DEFAULT 0,
            p_limit  integer DEFAULT 1000
        )
    
    Payload format (supports both snake_case and camelCase):
    { "customer_id": number } OR { "customerId": number }
    
    Args:
        event: Lambda event containing request payload
        context: Lambda context
        
    Returns:
        Response dictionary with status code and body
    """
    print("Generate Kaching Daily Report function started")
    
    try:
        # Initialize Supabase client (same approach as analyze_image function)
        url: str = get_parameter('/supabase/url')
        key: str = get_parameter('/supabase/anon')
        
        supabase: Client = create_client(url, key)
        
        # Parse payload and extract parameters
        # Handle both direct invocation and API Gateway events
        if isinstance(event, str):
            payload = json.loads(event)
        elif 'body' in event:
            payload = json.loads(event['body']) if isinstance(event['body'], str) else event['body']
        else:
            payload = event

        params = extract_params(payload)
        customer_id = params.get('customerId')
        
        # Debug logging for payload extraction
        print(f'Extracted params - customer_id: {customer_id}')
        print(f'Original payload keys: {list(payload.keys()) if isinstance(payload, dict) else "Not a dict"}')
        
        # Generate filename
        timestamp = get_timestamp()
        customer_part = f'customer{customer_id}' if customer_id else 'all-customers'
        file_name = f'kaching_daily_report_{customer_part}_{timestamp}.xlsx'
        # Sanitize filename (remove invalid characters)
        file_name = ''.join(c if c.isalnum() or c in '._-' else '_' for c in file_name)
        
        # For this daily cron-only report we do not track rows in report_files;
        # we simply generate, upload and (optionally) email the report.
        print(f'Processing Kaching Daily Report for customer {customer_id}')
        
        # Fetch ALL kaching data from the get_active_kachings_with_activity_daily function
        # using simple offset/limit pagination over (p_offset, p_limit).
        print('Fetching kaching daily data from database...')
        all_report_data = []

        batch_size = 1000  # Match function default
        offset = 0

        while True:
            try:
                rpc_response = supabase.rpc(
                    'get_active_kachings_with_activity_daily',
                    {
                        'p_offset': offset,
                        'p_limit': batch_size
                    }
                ).execute()

                batch_data = rpc_response.data or []
                batch_len = len(batch_data)
                print(f'Fetched batch: {batch_len} rows (offset: {offset}, total so far: {len(all_report_data) + batch_len})')

                if batch_len == 0:
                    # No more data
                    break

                all_report_data.extend(batch_data)

                if batch_len < batch_size:
                    # Last partial batch – we reached the end
                    break

                offset += batch_size
            except Exception as batch_error:
                error_msg = f'Error fetching kaching daily batch at offset {offset}: {str(batch_error)}'
                print(f'Kaching daily batch fetch error: {error_msg}')
                print(f'Error details: {traceback.format_exc()}')

                return {
                    'statusCode': 500,
                    'headers': {
                        'Content-Type': 'application/json',
                        'Access-Control-Allow-Origin': '*'
                    },
                    'body': json.dumps({'status': 'failed', 'message': error_msg})
                }

        row_count = len(all_report_data)
        print(f'Total kaching rows collected: {row_count}')
        
        # Handle no data case – still send an informational email, but do not generate/upload a file
        if row_count == 0:
            try:
                email_date = None
                if isinstance(payload, dict):
                    email_date = payload.get('week_start_date') or payload.get('date')
                if not email_date:
                    email_date = datetime.utcnow().strftime('%Y-%m-%d')

                # Inform internal recipients that the job ran but there was no data
                email_service.send_notification(
                    date=str(email_date),
                    status='success',  # Process ran successfully, there was just no data
                    error_message="No Kaching data found to include in today's daily report. No file was generated.",
                    client_email=True,
                    process_name='Kaching Daily Report'
                )
                print('Kaching daily report no-data email sent successfully')
            except Exception as email_err:
                # Email failures must not break the report generation flow
                print(f'Failed to send kaching daily no-data email: {str(email_err)}')

            return {
                'statusCode': 200,
                'headers': {
                    'Content-Type': 'application/json',
                    'Access-Control-Allow-Origin': '*'
                },
                'body': json.dumps({'status': 'no_data', 'message': 'No data found for the specified parameters', 'row_count': 0})
            }
        
        # Now that we have ALL kaching data, build the complete Excel file
        # We are NOT appending - we create a new file with all data at once
        print('Building Kaching Daily Excel file from collected data...')

        # Dynamically infer headers from the first row so we stay aligned with the DB function schema
        first_row = all_report_data[0]
        if not isinstance(first_row, dict):
            error_msg = 'Unexpected data format: expected list[dict] from get_active_kachings_with_activity_daily'
            print(error_msg)

            return {
                'statusCode': 500,
                'headers': {
                    'Content-Type': 'application/json',
                    'Access-Control-Allow-Origin': '*'
                },
                'body': json.dumps({'status': 'failed', 'message': error_msg})
            }

        headers = list(first_row.keys())
        
        # Create Excel workbook and worksheet
        wb = Workbook()
        ws = wb.active
        ws.title = "Kaching Daily Report"
        
        # Add headers with bold formatting
        header_font = Font(bold=True)
        for col_idx, header in enumerate(headers, start=1):
            cell = ws.cell(row=1, column=col_idx, value=header)
            cell.font = header_font
        
        # Add all rows from the complete dataset
        rows_added = 0
        for row_data in all_report_data:
            rows_added += 1
            for col_idx, header in enumerate(headers, start=1):
                value = row_data.get(header)
                # Handle None values
                ws.cell(row=rows_added + 1, column=col_idx, value=value if value is not None else '')
        
        # Save workbook to BytesIO buffer
        excel_buffer = BytesIO()
        wb.save(excel_buffer)
        excel_buffer.seek(0)
        excel_bytes = excel_buffer.getvalue()
        file_size_bytes = len(excel_bytes)
        
        print(f'Excel generation complete: {file_size_bytes} bytes, {rows_added} data rows (plus header)')
        
        # Validate that we processed all rows
        if rows_added != row_count:
            warning_msg = f'Warning: Processed {rows_added} rows but expected {row_count}'
            print(warning_msg)
        
        # Upload the complete Excel file to Supabase Storage
        # This is a NEW file (not appending) - upsert will overwrite if filename exists
        print(f'Uploading complete Excel file to storage: {file_name}')
        try:
            # Upload to Supabase Storage (same approach as analyze_image function)
            print(f"Uploading file to Reports/{file_name} with upsert")
            upload_response = supabase.storage.from_('Reports').upload(
                file_name,
                excel_bytes,
                {"contentType": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "upsert": "true"}  # Excel MIME type
            )
            print(f"Upload successful")
                
        except Exception as upload_error:
            error_msg = f'Upload failed: {str(upload_error)}'
            print(f'Error uploading file: {error_msg}')
            
            return {
                'statusCode': 500,
                'headers': {
                    'Content-Type': 'application/json',
                    'Access-Control-Allow-Origin': '*'
                },
                'body': json.dumps({'status': 'failed', 'message': error_msg, 'row_count': row_count})
            }
        
        # Get public URL for the uploaded file
        file_url = supabase.storage.from_('Reports').get_public_url(file_name)
        # Remove trailing query parameters if present
        if isinstance(file_url, str):
            file_url = file_url.rstrip('?')
        
        print(f'Report uploaded successfully: {file_url}')

        # This Lambda is triggered only by cron, so always attempt to send an email.
        # Email failures must not break the report generation flow.
        try:
            # Prefer an explicit date from the payload if provided (e.g., week_start_date), otherwise use today
            email_date = None
            if isinstance(payload, dict):
                email_date = payload.get('week_start_date') or payload.get('date')
            if not email_date:
                email_date = datetime.utcnow().strftime('%Y-%m-%d')

            # Send internal notification email with the public report link
            email_service.send_notification(
                date=str(email_date),
                status='success',
                # Reuse error_message as a generic "details" field to keep the API surface small
                error_message=f"Kaching daily report generated successfully.\n\nDownload link: {file_url}",
                client_email=True,
                process_name='Kaching Daily Report'
            )
            print('Kaching daily report email sent successfully')
        except Exception as email_err:
            # Email failures must not break the report generation flow
            print(f'Failed to send kaching daily report email: {str(email_err)}')
        
        return {
            'statusCode': 200,
            'headers': {
                'Content-Type': 'application/json',
                'Access-Control-Allow-Origin': '*'
            },
            'body': json.dumps({
                'status': 'done',
                'message': 'Report generated and uploaded successfully',
                'file_name': file_name,
                'file_url': file_url,
                'row_count': row_count,
                'file_size_bytes': file_size_bytes,
                'parameters': {
                    'customer_id': customer_id
                }
            })
        }
        
    except Exception as err:
        error_msg = f'Unexpected error: {str(err)}'
        print(f'Error: {error_msg}')
        print(f'Traceback: {traceback.format_exc()}')
        
        return {
            'statusCode': 500,
            'headers': {
                'Content-Type': 'application/json',
                'Access-Control-Allow-Origin': '*'
            },
            'body': json.dumps({
                'status': 'failed',
                'message': error_msg if isinstance(err, Exception) else 'Unknown error'
            })
        }