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

import os
from unittest.mock import MagicMock, patch

import pymysql
import pytest

from src import app

DB_HOST = os.environ.get('MYSQL_DB_HOST', 'mysql')
DB_USER = os.environ.get('MYSQL_USER', 'royalties')
DB_PASS = os.environ.get('MYSQL_PASSWORD', '1234')
DB_NAME = os.environ.get('MYSQL_DATABASE', 'royalty_accounting')
DB_PORT = int(os.environ.get('MYSQL_DB_PORT', 3306))


@pytest.fixture(scope='module')
def db_conn():
    """Create a database connection."""
    conn = pymysql.connect(
        host=DB_HOST,
        user=DB_USER,
        password=DB_PASS,
        database=DB_NAME,
        port=DB_PORT,
        cursorclass=pymysql.cursors.DictCursor,
        autocommit=True,
    )
    yield conn
    conn.close()


@pytest.fixture(scope='function')
def seed_data(db_conn):
    """Seed data for the test."""
    with db_conn.cursor() as cursor:
        # Clean up
        cursor.execute('DELETE FROM worksheet_flowthrough_batch')
        cursor.execute('DELETE FROM file_upload')
        cursor.execute('DELETE FROM file_upload_config')
        cursor.execute('DELETE FROM statement_period')

        # Insert Statement Period
        cursor.execute(
            """
            INSERT INTO statement_period (
                statement_period_id, statement_period_name, statement_period_status,
                statement_month, statement_year
            ) VALUES (999, '2024-01', 'current', 1, 2024)
            """
        )

        # Insert File Upload Config
        cursor.execute(
            """
            INSERT INTO file_upload_config (
                file_upload_config_id, upload_type, created_by, last_modified_by
            ) VALUES (100, 'adjustments', 'test', 'test')
            """
        )

        # Insert File Upload
        cursor.execute(
            """
            INSERT INTO file_upload (
                file_upload_id, file_upload_config_id, s3_bucket, s3_key,
                upload_status, created_by, last_modified_by, file_key, original_file_name,
                file_size_bytes
            ) VALUES (
                123, 100, 'test-bucket', 'test-key.csv',
                'complete', 'user-test', 'user-test', '123e4567-e89b-12d3-a456-426614174000', 'original.csv',
                1024
            )
            """
        )

        # Insert Worksheet Flowthrough Batch
        cursor.execute(
            """
            INSERT INTO worksheet_flowthrough_batch (
                worksheet_flowthrough_batch_id, statement_period_id, source_file_upload_id,
                batch_status, batch_type, created_by, last_modified_by
            ) VALUES (
                456, 999, 123,
                'pending', 'upload', 'user-test', 'user-test'
            )
            """
        )

    yield

    # Cleanup
    with db_conn.cursor() as cursor:
        cursor.execute('DELETE FROM worksheet_flowthrough_batch')
        cursor.execute('DELETE FROM file_upload')
        cursor.execute('DELETE FROM file_upload_config')
        cursor.execute('DELETE FROM statement_period')


@patch('src.app.get_s3_connector')
def test_lambda_handler_success(mock_get_s3, seed_data, db_conn):
    """Test successful lambda execution with DB integration."""
    # Setup S3 mock with proper file metadata and download/upload
    mock_s3 = MagicMock()

    # Mock download_file to create a test CSV file
    test_csv_content = (
        'account id *,activity month *,activity year *,adjustment type *,amount *,client facing comments *,contract id *,currency *,statement month *,statement year *\n'
        'ACC123,1,2024,manual,100.00,Test comment,CTR123,USD,1,2024\n'
    )

    def mock_download(bucket, key, local_path):
        with open(local_path, 'w') as f:
            f.write(test_csv_content)

    mock_s3.download_file.side_effect = mock_download

    # Mock get_file_metadata to return actual file size and ETag
    mock_metadata = MagicMock()
    mock_metadata.size = len(test_csv_content)  # Match actual file size
    mock_metadata.etag = 'abc123-2'  # Multipart ETag (skips checksum validation)
    mock_s3.get_file_metadata.return_value = mock_metadata

    mock_s3.upload_file.return_value = None
    mock_get_s3.return_value = mock_s3

    # Create test config for integration testing
    from src.connectors.mysql import MySQLConfig

    test_config = MySQLConfig(
        host=DB_HOST,
        user=DB_USER,
        password=DB_PASS,
        database=DB_NAME,
        port=DB_PORT,
        local_infile=True,
    )

    # Patch config to use the test DB credentials
    with patch('config.config.mysql', test_config):
        event = {
            'detail-type': 'adjustment_batch.initialized',
            'detail': {
                'metadata': {
                    'target_type': 'worksheet_flowthrough_batch',
                    'target_id': 456,  # Batch ID from seed data
                    'correlation_id': 'test-correlation-id',
                },
                'data': {
                    's3_bucket': 'test-bucket',
                    's3_key': 'test-key.csv',
                },
            },
        }
        context = MagicMock()

        # Execute Handler
        response = app.handler(event, context)

    # Verify Response
    assert response['detail_type'] == 'adjustment_batch.prepared'
    assert response['detail']['metadata']['correlation_id'] == 'test-correlation-id'
    assert (
        response['detail']['metadata']['target_type'] == 'worksheet_flowthrough_batch'
    )
    assert response['detail']['metadata']['target_id'] == 456

    # batch_id is in metadata.target_id
    batch_id = response['detail']['metadata']['target_id']
    assert batch_id == 456  # Should match seeded batch ID
    assert response['detail']['data']['row_count'] == 1  # One data row in test CSV

    # Verify prepared file was uploaded (s3_key should be different from input)
    prepared_s3_key = response['detail']['data']['s3_key']
    assert prepared_s3_key.startswith('staging/456/')  # Should have batch prefix
    assert prepared_s3_key.endswith('.csv.gz')

    # Verify DB - batch status should be updated to 'validating'
    with db_conn.cursor() as cursor:
        cursor.execute(
            'SELECT * FROM worksheet_flowthrough_batch WHERE worksheet_flowthrough_batch_id = %s',
            (batch_id,),
        )
        batch = cursor.fetchone()
        assert batch is not None
        assert batch['source_file_upload_id'] == 123
        assert batch['batch_status'] == 'validating'  # Updated by processor
        assert batch['created_by'] == 'user-test'


@patch('src.app.get_s3_connector')
def test_lambda_handler_invalid_file_format(mock_get_s3, seed_data, db_conn):
    """Test lambda execution with invalid file format."""
    # Setup S3 mock with invalid file content
    mock_s3 = MagicMock()

    # Mock download_file to create an invalid file
    invalid_content = 'This is not a valid CSV file!'

    def mock_download(bucket, key, local_path):
        with open(local_path, 'w') as f:
            f.write(invalid_content)

    mock_s3.download_file.side_effect = mock_download
    mock_metadata = MagicMock()
    mock_metadata.size = len(invalid_content)
    mock_metadata.etag = 'invalid-1'
    mock_s3.get_file_metadata.return_value = mock_metadata
    mock_get_s3.return_value = mock_s3

    from src.connectors.mysql import MySQLConfig

    test_config = MySQLConfig(
        host=DB_HOST,
        user=DB_USER,
        password=DB_PASS,
        database=DB_NAME,
        port=DB_PORT,
        local_infile=True,
    )

    with patch('config.config.mysql', test_config):
        event = {
            'detail-type': 'adjustment_batch.initialized',
            'detail': {
                'metadata': {
                    'target_type': 'worksheet_flowthrough_batch',
                    'target_id': 456,
                    'correlation_id': 'test-correlation-id-2',
                },
                'data': {
                    's3_bucket': 'test-bucket',
                    's3_key': 'test-key-invalid.csv',
                },
            },
        }
        context = MagicMock()

        # Execute Handler - should handle error gracefully
        try:
            response = app.handler(event, context)
            # If it doesn't raise, check that error was handled
            assert 'error' in str(response).lower() or response is None
        except Exception as e:
            # Error expected for invalid format
            assert e is not None


@patch('src.app.get_s3_connector')
def test_lambda_handler_empty_file(mock_get_s3, seed_data, db_conn):
    """Test lambda execution with empty file."""
    mock_s3 = MagicMock()

    # Mock download_file to create an empty file
    def mock_download(bucket, key, local_path):
        with open(local_path, 'w') as f:
            f.write('')  # Empty file

    mock_s3.download_file.side_effect = mock_download
    mock_metadata = MagicMock()
    mock_metadata.size = 0
    mock_metadata.etag = 'empty-1'
    mock_s3.get_file_metadata.return_value = mock_metadata
    mock_get_s3.return_value = mock_s3

    from src.connectors.mysql import MySQLConfig

    test_config = MySQLConfig(
        host=DB_HOST,
        user=DB_USER,
        password=DB_PASS,
        database=DB_NAME,
        port=DB_PORT,
        local_infile=True,
    )

    with patch('config.config.mysql', test_config):
        event = {
            'detail-type': 'adjustment_batch.initialized',
            'detail': {
                'metadata': {
                    'target_type': 'worksheet_flowthrough_batch',
                    'target_id': 456,
                    'correlation_id': 'test-correlation-id-3',
                },
                'data': {
                    's3_bucket': 'test-bucket',
                    's3_key': 'test-key-empty.csv',
                },
            },
        }
        context = MagicMock()

        # Execute Handler - should handle empty file error
        try:
            response = app.handler(event, context)
            # Check that error was properly handled
            assert response is None or 'error' in str(response).lower()
        except Exception as e:
            # Empty file error expected
            from src.errors import EmptyFileError

            assert isinstance(e, (EmptyFileError, Exception))


@patch('src.app.get_s3_connector')
def test_lambda_handler_missing_required_columns(mock_get_s3, seed_data, db_conn):
    """Test lambda execution with missing required columns."""
    mock_s3 = MagicMock()

    # Mock download_file with CSV missing required columns
    incomplete_csv = 'col1,col2\nval1,val2\n'

    def mock_download(bucket, key, local_path):
        with open(local_path, 'w') as f:
            f.write(incomplete_csv)

    mock_s3.download_file.side_effect = mock_download
    mock_metadata = MagicMock()
    mock_metadata.size = len(incomplete_csv)
    mock_metadata.etag = 'incomplete-1'
    mock_s3.get_file_metadata.return_value = mock_metadata
    mock_get_s3.return_value = mock_s3

    from src.connectors.mysql import MySQLConfig

    test_config = MySQLConfig(
        host=DB_HOST,
        user=DB_USER,
        password=DB_PASS,
        database=DB_NAME,
        port=DB_PORT,
        local_infile=True,
    )

    with patch('config.config.mysql', test_config):
        event = {
            'detail-type': 'adjustment_batch.initialized',
            'detail': {
                'metadata': {
                    'target_type': 'worksheet_flowthrough_batch',
                    'target_id': 456,
                    'correlation_id': 'test-correlation-id-4',
                },
                'data': {
                    's3_bucket': 'test-bucket',
                    's3_key': 'test-key-incomplete.csv',
                },
            },
        }
        context = MagicMock()

        # Execute Handler - should handle missing columns error
        try:
            response = app.handler(event, context)
            assert response is None or 'error' in str(response).lower()
        except Exception as e:
            # Missing headers error expected
            from src.errors import MissingHeadersError

            assert isinstance(e, (MissingHeadersError, Exception))


@patch('src.app.get_s3_connector')
def test_lambda_handler_batch_not_in_pending_status(mock_get_s3, db_conn):
    """Test lambda execution when batch is not in pending status."""
    # Setup batch in wrong status
    with db_conn.cursor() as cursor:
        # Clean up
        cursor.execute('DELETE FROM worksheet_flowthrough_batch')
        cursor.execute('DELETE FROM file_upload')
        cursor.execute('DELETE FROM file_upload_config')
        cursor.execute('DELETE FROM statement_period')

        # Insert Statement Period
        cursor.execute(
            """
            INSERT INTO statement_period (
                statement_period_id, statement_period_name, statement_period_status,
                statement_month, statement_year
            ) VALUES (999, '2024-01', 'current', 1, 2024)
            """
        )

        # Insert File Upload Config
        cursor.execute(
            """
            INSERT INTO file_upload_config (
                file_upload_config_id, upload_type, created_by, last_modified_by
            ) VALUES (100, 'adjustments', 'test', 'test')
            """
        )

        # Insert File Upload
        cursor.execute(
            """
            INSERT INTO file_upload (
                file_upload_id, file_upload_config_id, s3_bucket, s3_key,
                upload_status, created_by, last_modified_by, file_key, original_file_name,
                file_size_bytes
            ) VALUES (
                123, 100, 'test-bucket', 'test-key.csv',
                'complete', 'user-test', 'user-test', '123e4567-e89b-12d3-a456-426614174000', 'original.csv',
                1024
            )
            """
        )

        # Insert Worksheet Flowthrough Batch in PROCESSING status (not PENDING)
        cursor.execute(
            """
            INSERT INTO worksheet_flowthrough_batch (
                worksheet_flowthrough_batch_id, statement_period_id, source_file_upload_id,
                batch_status, batch_type, created_by, last_modified_by
            ) VALUES (
                789, 999, 123,
                'processing', 'upload', 'user-test', 'user-test'
            )
            """
        )

    try:
        mock_s3 = MagicMock()
        mock_get_s3.return_value = mock_s3

        from src.connectors.mysql import MySQLConfig

        test_config = MySQLConfig(
            host=DB_HOST,
            user=DB_USER,
            password=DB_PASS,
            database=DB_NAME,
            port=DB_PORT,
            local_infile=True,
        )

        with patch('config.config.mysql', test_config):
            event = {
                'detail-type': 'adjustment_batch.initialized',
                'detail': {
                    'metadata': {
                        'target_type': 'worksheet_flowthrough_batch',
                        'target_id': 789,
                        'correlation_id': 'test-correlation-id-5',
                    },
                    'data': {
                        's3_bucket': 'test-bucket',
                        's3_key': 'test-key.csv',
                    },
                },
            }
            context = MagicMock()

            # Execute Handler - should fail due to wrong status
            try:
                response = app.handler(event, context)
                assert response is None or 'error' in str(response).lower()
            except Exception as e:
                # UpdateBatchError expected
                from src.errors import UpdateBatchError

                assert isinstance(e, (UpdateBatchError, Exception))
    finally:
        # Cleanup
        with db_conn.cursor() as cursor:
            cursor.execute('DELETE FROM worksheet_flowthrough_batch')
            cursor.execute('DELETE FROM file_upload')
            cursor.execute('DELETE FROM file_upload_config')
            cursor.execute('DELETE FROM statement_period')