"""Integration tests for lambda function."""

import re
from io import BytesIO

import boto3
import pandas as pd
import pytest
from abacus_common_logic.adjustments_validation.constants import VALIDATION_ERRORS
from conftest import dockerized_lambda_api_client, get_db_field_value


@pytest.mark.jira('ACC-6307')
def test_invoke_lambda(basic_headers):
    """Assert lambda action."""
    dockerized_lambda = dockerized_lambda_api_client(basic_headers)

    value = get_db_field_value(
        'statement_period_adjustment_file',
        'error_type',
        'statement_period_adjustment_file_id = 1',
    )
    assert value is None

    value = get_db_field_value(
        'statement_period_adjustment_file',
        'invalid_file_location',
        'statement_period_adjustment_file_id = 1',
    )
    assert value is None

    body = {
        'abacus_event_id': 1,
        'target_type': 'statement_period_adjustment_file',
        'target_id': '1',
        'event_name': 'adjustment_file_upload',
        'event_date': '2021-07-11T11:20:35.45+01:00',
        'statement_period_id': 282,
    }

    res = dockerized_lambda.post_lambda('2015-03-31', body)

    assert res.status_code == 200
    assert res.json() == {'status': 'OK'}

    value = get_db_field_value(
        'statement_period_adjustment_file',
        'error_type',
        'statement_period_adjustment_file_id = 1',
    )
    assert value == 'content_error'

    value = get_db_field_value(
        'statement_period_adjustment_file',
        'invalid_file_location',
        'statement_period_adjustment_file_id = 1',
    )
    assert value == 's3://qa-abacus-adjustments/1/1-error.xlsx'


@pytest.mark.jira('ACC-7641')
def test_product_considered_invalid_for_a_contract_it_is_not_on(basic_headers):
    """Assert lambda action."""
    # fixture adjustments file for this scenario lives in:
    # s3://qa-abacus-adjustments/test_fixtures/upc-validation.xlsx

    dockerized_lambda = dockerized_lambda_api_client(basic_headers)

    value = get_db_field_value(
        'statement_period_adjustment_file',
        'error_type',
        'statement_period_adjustment_file_id = 2',
    )
    assert value is None

    value = get_db_field_value(
        'statement_period_adjustment_file',
        'invalid_file_location',
        'statement_period_adjustment_file_id = 2',
    )
    assert value is None

    body = {
        'abacus_event_id': 1,
        'target_type': 'statement_period_adjustment_file',
        'target_id': '2',
        'event_name': 'adjustment_file_upload',
        'event_date': '2021-07-11T11:20:35.45+01:00',
        'statement_period_id': 282,
    }

    res = dockerized_lambda.post_lambda('2015-03-31', body)
    assert res.status_code == 200
    assert res.json() == {'status': 'OK'}

    value = get_db_field_value(
        'statement_period_adjustment_file',
        'error_type',
        'statement_period_adjustment_file_id = 2',
    )
    assert value == 'content_error'

    s3_path = get_db_field_value(
        'statement_period_adjustment_file',
        'invalid_file_location',
        'statement_period_adjustment_file_id = 2',
    )
    assert s3_path == 's3://qa-abacus-adjustments/2/2-error.xlsx'

    search_str = VALIDATION_ERRORS.UPC_MISSING
    assert search_string_in_excel(s3_path, search_str, 'Template', 2)
    assert search_string_in_excel(s3_path, search_str, 'Template', 3)
    assert search_string_in_excel(s3_path, search_str, 'Template', 4)
    assert not search_string_in_excel(s3_path, search_str, 'Template', 5)
    assert search_string_in_excel(s3_path, search_str, 'Template', 6)

    # ACC-8296: UPCs are validating for two different accounts during an adjustment
    # this row uses a UPC not belonging to a label
    assert search_string_in_excel(s3_path, search_str, 'Template', 7)

    # ACC-8296: UPCs are validating for two different accounts during an adjustment
    # these 4 rows uses original details from the ticket (account_id, upc, etc.)
    assert search_string_in_excel(s3_path, search_str, 'Template', 8)
    assert search_string_in_excel(s3_path, search_str, 'Template', 9)
    assert not search_string_in_excel(s3_path, search_str, 'Template', 10)
    assert not search_string_in_excel(s3_path, search_str, 'Template', 11)


@pytest.mark.jira('ACC-7791')
def test_NA_values_being_treated(basic_headers):
    """Assert lambda action."""
    dockerized_lambda = dockerized_lambda_api_client(basic_headers)

    value = get_db_field_value(
        'statement_period_adjustment_file',
        'error_type',
        'statement_period_adjustment_file_id = 3',
    )
    assert value is None

    value = get_db_field_value(
        'statement_period_adjustment_file',
        'invalid_file_location',
        'statement_period_adjustment_file_id = 3',
    )
    assert value is None

    body = {
        'abacus_event_id': 1,
        'target_type': 'statement_period_adjustment_file',
        'target_id': '3',
        'event_name': 'adjustment_file_upload',
        'event_date': '2021-07-11T11:20:35.45+01:00',
        'statement_period_id': 282,
    }

    res = dockerized_lambda.post_lambda('2015-03-31', body)
    assert res.status_code == 200
    assert res.json() == {'status': 'OK'}

    value = get_db_field_value(
        'statement_period_adjustment_file',
        'error_type',
        'statement_period_adjustment_file_id = 3',
    )
    assert value == 'content_error'

    s3_path = get_db_field_value(
        'statement_period_adjustment_file',
        'invalid_file_location',
        'statement_period_adjustment_file_id = 3',
    )
    assert s3_path == 's3://qa-abacus-adjustments/3/3-error.xlsx'

    search_strs = [
        VALIDATION_ERRORS.ACCOUNT_INVALID,
        VALIDATION_ERRORS.CONTRACT_MISSING,
    ]
    for s in search_strs:
        assert search_string_in_excel(s3_path, s, 'amount_invalid', 2)

    search_strs = [
        VALIDATION_ERRORS.STATEMENT_MONTH_INVALID,
    ]
    for s in search_strs:
        assert search_string_in_excel(s3_path, s, 'amount_invalid', 3)

    search_strs = [
        VALIDATION_ERRORS.AMOUNT_INVALID,
        VALIDATION_ERRORS.ACTIVITY_MONTH_INVALID,
        VALIDATION_ERRORS.ADJUSTMENT_TYPE_UNSUPPORTED,
    ]
    for s in search_strs:
        assert search_string_in_excel(s3_path, s, 'amount_invalid', 4)

    search_strs = [
        VALIDATION_ERRORS.AMOUNT_INVALID,
        VALIDATION_ERRORS.CURRENCY_INVALID,
        VALIDATION_ERRORS.ACTIVITY_MONTH_INVALID,
        VALIDATION_ERRORS.STATEMENT_MONTH_INVALID,
    ]
    for s in search_strs:
        assert search_string_in_excel(s3_path, s, 'amount_invalid', 5)

    search_strs = [
        VALIDATION_ERRORS.ACTIVITY_MONTH_INVALID,
        VALIDATION_ERRORS.STATEMENT_MONTH_INVALID,
        VALIDATION_ERRORS.ADJUSTMENT_TYPE_REQUIRED,
        VALIDATION_ERRORS.DISTRIBUTION_TYPE_UNSUPPORTED,
    ]
    for s in search_strs:
        assert search_string_in_excel(s3_path, s, 'amount_invalid', 6)

    search_strs = [
        VALIDATION_ERRORS.CONTRACT_INVALID,
        VALIDATION_ERRORS.UPC_INVALID,
        VALIDATION_ERRORS.AMOUNT_INVALID,
        VALIDATION_ERRORS.ACTIVITY_MONTH_INVALID,
        VALIDATION_ERRORS.STATEMENT_MONTH_INVALID,
        VALIDATION_ERRORS.ADJUSTMENT_TYPE_UNSUPPORTED,
        VALIDATION_ERRORS.DISTRIBUTION_TYPE_UNSUPPORTED,
    ]
    for s in search_strs:
        assert search_string_in_excel(s3_path, s, 'amount_invalid', 7)

    search_strs = [
        VALIDATION_ERRORS.ACCOUNT_INVALID,
        VALIDATION_ERRORS.CONTRACT_INVALID,
        VALIDATION_ERRORS.AMOUNT_INVALID,
        VALIDATION_ERRORS.CURRENCY_UNSUPPORTED,
        VALIDATION_ERRORS.ACTIVITY_MONTH_INVALID,
        VALIDATION_ERRORS.ADJUSTMENT_TYPE_UNSUPPORTED,
    ]
    for s in search_strs:
        assert search_string_in_excel(s3_path, s, 'amount_invalid', 8)

    search_strs = [
        VALIDATION_ERRORS.CONTRACT_INVALID,
        VALIDATION_ERRORS.UPC_INVALID,
        VALIDATION_ERRORS.CURRENCY_INVALID,
        VALIDATION_ERRORS.ACTIVITY_YEAR_INVALID,
        VALIDATION_ERRORS.STATEMENT_MONTH_INVALID,
        VALIDATION_ERRORS.ADJUSTMENT_TYPE_REQUIRED,
        VALIDATION_ERRORS.DISTRIBUTION_TYPE_UNSUPPORTED,
    ]
    for s in search_strs:
        assert search_string_in_excel(s3_path, s, 'amount_invalid', 9)

    search_strs = [VALIDATION_ERRORS.STATEMENT_PERIOD_MISSING]
    for s in search_strs:
        assert search_string_in_excel(s3_path, s, 'amount_invalid', 10)

    search_strs = [
        'Account Id is not valid',
        'Error when validating contract: no account ID.',
        'Error when validating closed balance: no account ID.',
    ]
    search_strs = [
        VALIDATION_ERRORS.ACCOUNT_INVALID,
        VALIDATION_ERRORS.STATEMENT_PERIOD_MISSING,
    ]
    for s in search_strs:
        assert search_string_in_excel(s3_path, s, 'amount_invalid', 11)


def search_string_in_excel(
    s3_path, search_str, sheet_name, row_number, column_name='Validation Errors'
):
    """Search for a string in a specific cell of an Excel file stored in S3."""
    match = re.match(r's3://([^/]+)/(.+)', s3_path)
    if not match:
        raise ValueError(f'Invalid S3 path: {s3_path}')

    s3_bucket, s3_key = match.groups()

    s3_object = boto3.client('s3').get_object(Bucket=s3_bucket, Key=s3_key)
    file_stream = BytesIO(s3_object['Body'].read())

    excel_file = pd.ExcelFile(file_stream)
    available_sheets = excel_file.sheet_names

    if sheet_name not in available_sheets:
        raise KeyError(
            f"Sheet '{sheet_name}' not found. Available sheets: {available_sheets}"
        )

    df = pd.read_excel(file_stream, sheet_name=sheet_name)

    if column_name not in df.columns:
        raise ValueError(
            f"Column '{column_name}' not found. "
            f'Available columns: {df.columns.tolist()}'
        )

    zero_based_index = row_number - 2
    if zero_based_index < 0 or zero_based_index >= len(df):
        raise ValueError(
            f"Row number {row_number} is out of range in sheet '{sheet_name}'"
        )

    cell_value = df.at[zero_based_index, column_name]
    return search_str in str(cell_value)