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 (report_id, customer_id) and camelCase (reportId, customerId) formats.
    
    Args:
        payload: Request payload with customer_id/customerId and optional report_id/reportId
        
    Returns:
        Dictionary with reportId and customerId
    """
    return {
        'reportId': payload.get('report_id') or payload.get('reportId'),
        'customerId': payload.get('customer_id') or payload.get('customerId')
    }


def is_cron_trigger(event: Any, payload: Optional[Dict[str, Any]] = None) -> bool:
    """
    Determine if this invocation was triggered by a scheduled (cron) event.
    We only send emails when this is True to avoid emailing on manual/API calls.
    """
    try:
        base_event = json.loads(event) if isinstance(event, str) else (event or {})
    except Exception:
        base_event = {}

    is_cron = False

    # EventBridge / CloudWatch scheduled events typically have source = 'aws.events'
    if isinstance(base_event, dict) and base_event.get('source') == 'aws.events':
        is_cron = True

    # Allow an explicit flag in the payload for cron-only executions, e.g. {"is_cron": true}
    if isinstance(payload, dict):
        if payload.get('is_cron') or payload.get('isCron') or payload.get('send_email') or payload.get('sendEmail'):
            is_cron = True

    return is_cron


def lambda_handler(event: Dict[str, Any], context: Any) -> Dict[str, Any]:
    """
    Lambda handler to generate combined week report and store as Excel file in Supabase Storage.
    
    Matches the logic from get_combined_week_report_step_by_step.sql exactly.
    Uses CURRENT_DATE - 7 days, includes fixed tasks without images.
    
    Payload format (supports both snake_case and camelCase):
    { "customer_id": number, "report_id": string (optional) }
    OR
    { "customerId": number, "reportId": string (optional) }
    
    Args:
        event: Lambda event containing request payload
        context: Lambda context
        
    Returns:
        Response dictionary with status code and body
    """
    print("Generate Combined Week Report function started")
    
    try:
        # Initialize Supabase client. Use service_role key so RPC runs with full access;
        # anon key runs as role 'anon' and RLS can return 0 rows for the report function.
        url: str = get_parameter('/supabase/url')
        try:
            key = get_parameter('/supabase/service_role')
        except Exception:
            key = get_parameter('/supabase/anon')
            print('WARNING: Using anon key; if report returns 0 rows, add /supabase/service_role to SSM for this Lambda.')
        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

        # Determine if this run was triggered by a cron/event rule (for conditional emailing)
        cron_triggered = is_cron_trigger(event, payload if isinstance(payload, dict) else None)

        params = extract_params(payload)
        
        report_id = params.get('reportId')
        customer_id = params.get('customerId')
        
        # Debug logging for payload extraction
        print(f'Extracted params - report_id: {report_id}, 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'combined_week_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)
        
        # Update status to 'processing' immediately if we have a report_id
        if report_id:
            print(f'Updating report {report_id} to processing status')
            supabase.table('report_files').update({
                'status': 'processing',
                'file_name': file_name
            }).eq('id', report_id).execute()
        else:
            # Create new tracking record if no report_id provided (direct API call)
            print('Creating new report_files record')
            insert_response = supabase.table('report_files').insert({
                'file_name': file_name,
                'status': 'processing',
                'report_type': 'combined_week_report_step_by_step',
                'customer_id': customer_id
            }).execute()
            
            if insert_response.data and len(insert_response.data) > 0:
                report_id = insert_response.data[0].get('id')
        
        print(f'Processing report {report_id} for customer {customer_id}')
        
        # Fetch ALL data from the database function with pagination support
        # We collect all records before building the Excel file to ensure complete data
        # IMPORTANT: We MUST use pagination to get ALL records - no limits allowed
        print('Fetching all report data from database...')
        all_report_data = []
        
        # Pagination parameters - use reasonable batch size to avoid memory issues
        # NOTE: Match database function signature: get_combined_week_report_step_by_step(p_customer_id, page_offset, limit_count)
        # We MUST always send page_offset and limit_count so we call the correct function (no overload/fallback).
        batch_size = 10000  # Fetch 10,000 records per batch
        page_offset = 0
        
        # Ensure integer types for RPC (PostgREST/DB can be strict; JSON may give strings)
        p_customer_id = int(customer_id) if customer_id is not None else None
        first_batch_data = None
        try:
            rpc_params = {'p_customer_id': p_customer_id, 'page_offset': page_offset, 'limit_count': batch_size}
            rpc_response = supabase.rpc(
                'get_combined_week_report_step_by_step',
                rpc_params
            ).execute()
            first_batch_data = rpc_response.data
            n = len(first_batch_data) if first_batch_data else 0
            print(f'RPC first batch: params={rpc_params}, rows={n}')
        except Exception as rpc_error:
            first_batch_data = None
            error_str = str(rpc_error).lower()
            if 'does not exist' in error_str or 'unknown parameter' in error_str or 'invalid parameter' in error_str:
                print(f'RPC failed (wrong signature?): {rpc_error}')
            else:
                raise
        
        if first_batch_data is not None:
            # Process first batch (may be empty list [] when same call returns rows in DB client)
            if first_batch_data:
                print('Fetching all data in batches (no limit)...')
                all_report_data.extend(first_batch_data)
                print(f'Fetched first batch: {len(first_batch_data)} rows (page_offset: {page_offset}, total so far: {len(all_report_data)})')
                
                # If first batch is smaller than batch_size, we're done
                if len(first_batch_data) < batch_size:
                    print(f'All data fetched in first batch - total: {len(all_report_data)} rows')
                else:
                    # Continue fetching remaining batches - always send page_offset and limit_count
                    page_offset += batch_size
                    while True:
                        try:
                            rpc_response = supabase.rpc(
                                'get_combined_week_report_step_by_step',
                                {'p_customer_id': p_customer_id, 'page_offset': page_offset, 'limit_count': batch_size}
                            ).execute()
                            
                            if rpc_response.data:
                                batch_data = rpc_response.data
                                all_report_data.extend(batch_data)
                                print(f'Fetched batch: {len(batch_data)} rows (page_offset: {page_offset}, total so far: {len(all_report_data)})')
                                
                                # If we got fewer rows than requested, we've reached the end
                                if len(batch_data) < batch_size:
                                    print(f'Reached end of data - last batch had {len(batch_data)} rows')
                                    break
                                page_offset += batch_size
                            else:
                                print('No more data returned - reached end')
                                break
                                
                        except Exception as batch_error:
                            error_msg = f'Error fetching batch at page_offset {page_offset}: {str(batch_error)}'
                            print(f'Batch fetch error: {error_msg}')
                            print(f'Error details: {traceback.format_exc()}')
                            
                            if report_id:
                                supabase.table('report_files').update({
                                    'status': 'failed',
                                    'error_message': error_msg,
                                    'completed_at': datetime.utcnow().isoformat()
                                }).eq('id', report_id).execute()
                            
                            return {
                                'statusCode': 500,
                                'headers': {
                                    'Content-Type': 'application/json',
                                    'Access-Control-Allow-Origin': '*'
                                },
                                'body': json.dumps({
                                    'status': 'failed',
                                    'message': error_msg,
                                    'report_id': report_id
                                })
                            }
            else:
                # RPC returned [] - same params may return rows in DB client (e.g. session timezone / CURRENT_DATE)
                print('No data returned in first batch (RPC returned []). '
                      'If the same call returns rows in the DB client, check: '
                      '1) Session timezone (PostgREST uses UTC; function may use CURRENT_DATE). '
                      '2) Param types sent (p_customer_id should be int).')
        else:
            # RPC failed or returned None - function must accept (p_customer_id, page_offset, limit_count)
            error_msg = 'The database function get_combined_week_report_step_by_step must accept (p_customer_id, page_offset, limit_count) ' \
                       'to export all records. Without pagination, only a limited number of records can be retrieved.'
            print(f'ERROR: {error_msg}')
            
            if report_id:
                supabase.table('report_files').update({
                    'status': 'failed',
                    'error_message': error_msg,
                    'completed_at': datetime.utcnow().isoformat()
                }).eq('id', report_id).execute()
            
            return {
                'statusCode': 500,
                'headers': {
                    'Content-Type': 'application/json',
                    'Access-Control-Allow-Origin': '*'
                },
                'body': json.dumps({
                    'status': 'failed',
                    'message': error_msg,
                    'report_id': report_id
                })
            }
        
        row_count = len(all_report_data)
        print(f'Total rows collected: {row_count} (ALL data fetched with no limits)')
        
        # Handle no data case
        if row_count == 0:
            if report_id:
                supabase.table('report_files').update({
                    'status': 'no_data',
                    'row_count': 0,
                    'completed_at': datetime.utcnow().isoformat()
                }).eq('id', report_id).execute()
            
            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',
                    'report_id': report_id,
                    'row_count': 0
                })
            }
        
        # Now that we have ALL data, build the complete Excel file
        # We are NOT appending - we create a new file with all data at once
        print('Building complete Excel file from all collected data...')
        
        # Generate Excel content - matching exact column order from step_by_step.sql
        headers = [
            'item_id', 'store_code', 'store_name', 'channel', 'customer_id', 'product_id',
            'issue_for_feedback', 'week_start_date', 'created_at', 'updated_at', 'note',
            'image_url', 'fixed_at', 'rep_full_name', 'gps', 'article', 'dc_soh',
            'last_ordered', 'last_received', 'store_soh', 'system_dros', 'scoring_system',
            'last_sold', 'category', 'brand', 'product', 'variant', 'size', 'status',
            'genai_position', 'genai_facing_count', 'genai_has_label', 'genai_confidence',
            'source_table'
        ]
        
        # Create Excel workbook and worksheet
        wb = Workbook()
        ws = wb.active
        ws.title = "Combined Week 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}')
            
            if report_id:
                supabase.table('report_files').update({
                    'status': 'failed',
                    'row_count': row_count,
                    'error_message': error_msg,
                    'completed_at': datetime.utcnow().isoformat()
                }).eq('id', report_id).execute()
            
            return {
                'statusCode': 500,
                'headers': {
                    'Content-Type': 'application/json',
                    'Access-Control-Allow-Origin': '*'
                },
                'body': json.dumps({
                    'status': 'failed',
                    'message': error_msg,
                    'report_id': report_id,
                    '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('?')
        
        # Update tracking record with success (status = 'done')
        if report_id:
            supabase.table('report_files').update({
                'status': 'done',
                'file_url': file_url,
                'row_count': row_count,
                'file_size_bytes': file_size_bytes,
                'completed_at': datetime.utcnow().isoformat()
            }).eq('id', report_id).execute()
        
        print(f'Report uploaded successfully: {file_url}')

        # Only send email when the report was generated by a scheduled (cron) event
        if cron_triggered:
            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 client-facing 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"Combined weekly report generated successfully.\n\nDownload link: {file_url}",
                    client_email=True,
                    process_name='Combined Weekly Report'
                )
                print('Cron-triggered combined weekly report email sent successfully')
            except Exception as email_err:
                # Email failures must not break the report generation flow
                print(f'Failed to send cron-based 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',
                'report_id': report_id,
                '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'
            })
        }