"""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."""
    # Wait for DB? Ideally yes, but assuming it's up for the test execution
    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
            )
            """
        )

    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')


def test_lambda_handler_success(seed_data, db_conn):
    """Test successful lambda execution with DB integration."""
    # Patch config to use the test DB credentials
    with patch.dict(
        'config.MYSQL_CONFIG',
        {
            'host': DB_HOST,
            'user': DB_USER,
            'password': DB_PASS,
            'database': DB_NAME,
            'port': DB_PORT,
        },
    ):
        event = {
            'detail-type': 'file_upload.completed',
            'detail': {
                'metadata': {
                    'correlation_id': 'test-correlation-id',
                    'target_id': 123,
                    'target_type': 'file_upload',
                },
                'data': {'upload_type': 'adjustments'},
            },
        }
        context = MagicMock()

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

    # Verify Response
    detail = response['detail']
    assert detail['data']['s3_bucket'] == 'test-bucket'
    assert detail['data']['s3_key'] == 'test-key.csv'
    assert detail['metadata']['correlation_id'] == 'test-correlation-id'
    assert detail['metadata']['target_id'] is not None
    assert detail['metadata']['target_type'] == 'worksheet_flowthrough_batch'
    batch_id = detail['metadata']['target_id']
    assert isinstance(batch_id, int)

    # Verify DB
    with db_conn.cursor() as cursor:
        cursor.execute(
            """
            SELECT *
            FROM worksheet_flowthrough_batch
            WHERE worksheet_flowthrough_batch_id = %s
            LIMIT 1
        """,
            (batch_id,),
        )
        batch = cursor.fetchone()
        assert batch is not None
        assert batch['source_file_upload_id'] == 123
        assert batch['batch_status'] == 'pending'
        assert batch['created_by'] == 'user-test'