from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from django.http import HttpResponse, JsonResponse
from datetime import datetime
import requests
import io
from django.views.decorators.csrf import csrf_exempt
from .helpers import get_access_token, get_P_access_token
access_token = get_P_access_token()
# access_token = get_access_token()

@csrf_exempt
def account_statements(request, member_id):
    # Get the date range from the request
    start_date = request.GET.get('start_date')
    end_date = request.GET.get('end_date')
    
    # Fetch savings account data
    try:
        url = f"https://api.kwara.com/members/{member_id}/savings?include=transactions"
        headers = {
            "accept": "application/json",
            "Authorization": f"Bearer {access_token}"
        }

        response = requests.get(url, headers=headers)
        response.raise_for_status()
        savings_data = response.json().get('data', [])
        transactions_included = response.json().get('included', [])
    except requests.RequestException as e:
        return JsonResponse({'error': str(e)}, status=500)

    # Filter savings accounts by the "updated_at" date range
    filtered_savings = [
        account for account in savings_data
        if start_date <= account['attributes']['updated_at'][:10] <= end_date
    ]

    # Create a workbook and add savings account data to each sheet
    workbook = Workbook()
    
    for account in filtered_savings:
        sheet = workbook.create_sheet(title=account['attributes']['product_name'])

        # Add savings account details at the top of the sheet
        sheet.append(["Savings Account Details"])
        sheet.append(["Account Name:", account['attributes']['name']])
        sheet.append(["Product Name:", account['attributes']['product_name']])
        sheet.append(["Balance:", account['attributes']['balance']])
        sheet.append(["Available Balance:", account['attributes']['available_balance']])
        sheet.append(["Accrued Interest:", account['attributes']['accrued_interest']])
        sheet.append(["State:", account['attributes']['state']])
        sheet.append(["Updated At:", account['attributes']['updated_at']])
        sheet.append([])  # Add an empty row before transactions

        # Add headers for transaction details
        headers = ["Transaction ID", "Type", "Amount", "Balance", "Payment Method", "Notes", "Booking Date", "Value Date"]
        sheet.append(headers)
        
        # Filter and add transactions related to the savings account
        transaction_ids = [t['id'] for t in account['relationships']['transactions']['data']]
        filtered_transactions = [
            t for t in transactions_included
            if t['id'] in transaction_ids and start_date <= t['attributes']['created_at'][:10] <= end_date
        ]
        
        for transaction in filtered_transactions:
            row = [
                transaction['id'],
                transaction['attributes']['type'],
                transaction['attributes']['amount'],
                transaction['attributes']['balance'],
                transaction['attributes']['payment_method'],
                transaction['attributes']['notes'],
                transaction['attributes']['booking_date'],
                transaction['attributes']['value_date']
            ]
            sheet.append(row)

        # Auto-size columns
        for col in range(1, len(headers) + 1):
            sheet.column_dimensions[get_column_letter(col)].auto_size = True

    # Remove the default sheet created by openpyxl
    if 'Sheet' in workbook.sheetnames:
        del workbook['Sheet']

    # Prepare the file for download
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = f'attachment; filename="account_statements_{member_id}_{datetime.now().strftime("%Y%m%d%H%M%S")}.xlsx"'

    output = io.BytesIO()
    workbook.save(output)
    response.write(output.getvalue())
    return response

@csrf_exempt
def another_account_statements(request, member_id):
    start_date = request.GET.get('start_date')
    end_date = request.GET.get('end_date')

    if request.method == 'GET':
        try:
            url = f"https://api.kwara.com/members/{member_id}/savings?include=transactions"
            headers = {
                "accept": "application/json",
                "Authorization": f"Bearer {access_token}"
            }

            response = requests.get(url, headers=headers)
            response.raise_for_status()
            data = response.json().get('data', [])

            # Filter savings accounts by updated_at date
            filtered_accounts = [
                account for account in data
                if start_date <= account['attributes']['updated_at'][:10] <= end_date
            ]

            if not filtered_accounts:
                return JsonResponse({"error": "No data found within the specified date range"}, status=404)

            # Create an Excel workbook
            wb = Workbook()
            for account in filtered_accounts:
                product_name = account['attributes']['product_name']
                ws = wb.create_sheet(title=product_name)

                # Add headers
                headers = ["Transaction ID", "Type", "Amount", "Balance", "Payment Method", "Notes", "Date"]
                for col_num, header in enumerate(headers, 1):
                    ws.cell(row=1, column=col_num, value=header)

                # Fetch and add transaction data
                transactions = account['relationships']['transactions']['data']
                transaction_details = response.json().get('included', [])
                row_num = 2

                for transaction in transactions:
                    transaction_info = next(
                        (item for item in transaction_details if item['id'] == transaction['id']), None
                    )
                    if transaction_info:
                        attributes = transaction_info['attributes']
                        ws.cell(row=row_num, column=1, value=transaction['id'])
                        ws.cell(row=row_num, column=2, value=attributes['type'])
                        ws.cell(row=row_num, column=3, value=attributes['amount'])
                        ws.cell(row=row_num, column=4, value=attributes['balance'])
                        ws.cell(row=row_num, column=5, value=attributes['payment_method'])
                        ws.cell(row=row_num, column=6, value=attributes.get('notes', ''))
                        ws.cell(row=row_num, column=7, value=attributes['created_at'])
                        row_num += 1

                # Adjust column widths
                for col_num in range(1, len(headers) + 1):
                    ws.column_dimensions[get_column_letter(col_num)].width = 20

            # Remove the default sheet created by openpyxl
            if 'Sheet' in wb.sheetnames:
                wb.remove(wb['Sheet'])

            # Save the workbook to an in-memory buffer
            buffer = io.BytesIO()
            wb.save(buffer)
            buffer.seek(0)

            # Return the file as a downloadable response
            response = HttpResponse(
                buffer, content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            )
            response['Content-Disposition'] = f'attachment; filename=Account_Statement_{start_date}_to_{end_date}.xlsx'
            return response

        except requests.RequestException as e:
            return JsonResponse({'error': str(e)}, status=500)
    else:
        return JsonResponse({'error': 'Invalid request method'}, status=405)