"""Core processor for adjustment file validation and preparation pipeline.

Orchestrates multi-stage pipeline: downloads files from S3, validates business rules via DuckDB,
normalizes data, and stages validated results to MySQL with comprehensive error handling.

Workflow:
    1. Update batch status to VALIDATING (optimistic locking)
    2. Download and verify file from S3
    3. Load file into DuckDB (auto-detect CSV/XLSX/Parquet/gzip)
    4. Load reference data and validate business rules via DAG
    5. Export validated data as CSV to S3
    6. Load into MySQL staging table
    7. Return validation results (row counts, amounts)

Error Handling:
    - PermanentError: Sets batch status to ERROR with error code
    - TransientError: Propagated for Lambda retry
    - Cleanup: Temp files deleted, S3 files retained for auditing
"""

from __future__ import annotations

import os
from pathlib import Path

from abacus_common_logic.utils.profiling import profile

from config import config
from src.connectors.duckdb import DuckDBConnection
from src.connectors.duckdb.utils import create_csv_from_table, upload_csv_from_table
from src.constants import AdjustmentStagingSchema
from src.enums import BatchErrorCode, BatchStatus, DuckDBTable, EventType, TargetType
from src.errors import (
    PermanentError,
    UpdateBatchError,
    get_error_code,
)
from src.infra.log import logger
from src.repositories.royalty_accounting import RoyaltyAccountingClient
from src.schemas import (
    AdjustmentFilePrepareEvent,
    AdjustmentFilePrepareResponse,
    AdjustmentFilePrepareResponseData,
    AdjustmentFilePrepareResponseDetail,
    AdjustmentFilePrepareResponseMetadata,
)
from src.services.file_loader import AdjustmentFileLoader
from src.services.reference_loader import ReferenceLoader
from src.services.s3_downloader import S3Downloader
from src.services.validator import DuckDBValidator
from src.utils.file_utils import create_temp_file


class AdjustmentFilePrepareProcessor:
    """Orchestrates adjustment file validation pipeline from S3 download to MySQL staging.

    Coordinates file loading, reference data loading, business rule validation via DuckDB,
    and result staging with comprehensive error handling and batch status management.
    """

    def __init__(
        self,
        adjustment_file_loader: AdjustmentFileLoader,
        adjustment_file_validator: DuckDBValidator,
        reference_data_service: ReferenceLoader,
        duck_conn: DuckDBConnection,
        royalty_accounting_client: RoyaltyAccountingClient,
        s3_file_downloader: S3Downloader,
    ):
        """Initialize processor.

        Args:
            adjustment_file_loader: Loader for adjustment file ingestion and normalization.
            adjustment_file_validator: Validator for adjustment file business rules.
            reference_data_service: Service for loading reference data.
            duck_conn: DuckDB connection for in-memory data processing and validation.
            royalty_accounting_client: Client for database operations (batch status, staging data).
            s3_file_downloader: Downloader for retrieving files from S3 with integrity verification.
        """
        self._file_loader = adjustment_file_loader
        self._validator = adjustment_file_validator
        self._reference_data_service = reference_data_service
        self._duck_conn = duck_conn
        self._ra_client = royalty_accounting_client
        self._s3_file_downloader = s3_file_downloader

    @profile(logger=logger)
    def process(
        self,
        event: AdjustmentFilePrepareEvent,
    ) -> AdjustmentFilePrepareResponse:
        """Process adjustment file through validation, normalization, and staging pipeline.

        Args:
            event: Event containing batch_id, s3_bucket, s3_key, and correlation_id.

        Returns:
            Response with prepared file location and row count.

        Raises:
            PermanentError: For validation failures, missing data, or unsupported file types.
            TransientError: For temporary failures (DB, S3, disk space).
        """
        batch_id = event.detail.metadata.target_id
        correlation_id = event.detail.metadata.correlation_id
        s3_bucket = event.detail.data.s3_bucket
        s3_key = event.detail.data.s3_key

        self._set_batch_validating(batch_id)

        file_path: Path | None = None
        s3_staging_key: str | None = None
        try:
            logger.info('--- Downloading file ---')
            file_path = create_temp_file()
            self._s3_file_downloader.download(s3_bucket, s3_key, file_path)

            logger.info('--- Processing file ---')
            self._file_loader.load(file_path)

            logger.info('--- Loading reference data ---')
            self._reference_data_service.load_all()

            logger.info('--- Validating file ---')
            result = self._validator.validate(batch_id)
            logger.info(result.model_dump())

            logger.info('--- Clearing destination ---')
            logger.info(f'Clearing batch {batch_id} from destination')
            deleted_rows = self._ra_client.delete_batch_from_staging(batch_id)
            logger.info(f'Deleted {deleted_rows} rows')

            if config.env.is_local:
                logger.info('--- Exporting results ---')
                self._export_table_to_file(file_path)
                logger.info('--- Staging results ---')
                self._ra_client.stage_batch_from_file(file_path)
            else:
                logger.info('--- Exporting results ---')
                s3_staging_key = config.storage.S3_STAGING_FORMAT.format(
                    batch_id=batch_id
                )
                self._export_table_to_s3(s3_bucket, s3_staging_key)
                logger.info('--- Staging results ---')
                self._ra_client.stage_batch_from_s3(s3_bucket, s3_staging_key)

            return AdjustmentFilePrepareResponse(
                detail_type=EventType.ADJUSTMENT_BATCH_PREPARED,
                detail=AdjustmentFilePrepareResponseDetail(
                    metadata=AdjustmentFilePrepareResponseMetadata(
                        correlation_id=correlation_id,
                        target_id=batch_id,
                        target_type=TargetType.WORKSHEET_ADJUSTMENT_BATCH,
                    ),
                    data=AdjustmentFilePrepareResponseData(
                        invalid_row_count=result.invalid_rows,
                        s3_bucket=s3_bucket,
                        s3_key=s3_staging_key,
                        total_file_amount_multicurrency=result.total_amount_raw,
                        total_rounded_amount_multicurrency=result.total_amount,
                        valid_row_count=result.valid_rows,
                    ),
                ),
            )
        except PermanentError as e:
            logger.error(f'Permanent error processing batch: {str(e)}')
            try:
                self._set_batch_error(batch_id, [get_error_code(e)])
            except Exception as db_err:
                logger.error(f'Failed to update batch status: {db_err}')
                raise e from db_err
            else:
                raise e

        except Exception as e:
            logger.error(f'Unexpected error processing batch: {str(e)}')
            raise

        finally:
            logger.info('--- Cleanup ---')
            self._cleanup_file(file_path)

    def _cleanup_file(self, file_path: str | Path | None) -> None:
        """Delete temporary file, suppressing errors if file doesn't exist.

        Args:
            file_path: Path to the file to be deleted.
        """
        if not file_path:
            return

        try:
            logger.info(f'Deleting file: {file_path}')
            os.remove(file_path)
        except FileNotFoundError:
            logger.debug('File already removed or does not exist')
        except OSError as e:
            logger.error(f'OS error while deleting file {file_path}: {e}')
        except Exception as e:
            logger.error(f'Unexpected error while deleting file {file_path}: {e}')

    def _set_batch_validating(self, batch_id: int) -> None:
        """Update batch status to VALIDATING using optimistic locking.

        Args:
            batch_id: The ID of the batch to update.

        Raises:
            UpdateBatchError: If batch is not found or not in PENDING status.
        """
        status = BatchStatus.VALIDATING
        logger.info(f'Updating batch status to: {status}')
        updated_rows = self._ra_client.update_batch_status(
            batch_id,
            status=status,
            expected_status=BatchStatus.PENDING,
        )

        if updated_rows < 1:
            raise UpdateBatchError(
                f'Batch {batch_id} could not be updated, '
                'batch not found or in unexpected status. '
                f'Expected status={BatchStatus.PENDING}'
            )

        logger.info('Batch status updated')

    def _set_batch_error(self, batch_id: int, errors: list[BatchErrorCode]) -> None:
        """Update batch status to ERROR using optimistic locking.

        Args:
            batch_id: The ID of the batch to update.
            errors: List of error codes to record.

        Raises:
            UpdateBatchError: If batch is not found or not in VALIDATING status.
        """
        status = BatchStatus.ERROR
        logger.error(f'Updating batch status to: {status}')
        updated_rows = self._ra_client.update_batch_status(
            batch_id,
            status=status,
            expected_status=BatchStatus.VALIDATING,
            errors=errors,
        )

        if updated_rows < 1:
            raise UpdateBatchError(
                f'Batch {batch_id} could not be updated, '
                'batch not found or in unexpected status. '
                f'Expected status={BatchStatus.VALIDATING}'
            )

        logger.error('Batch status updated')

    def _export_table_to_s3(self, s3_bucket: str, s3_key: str) -> None:
        """Export DuckDB staging table as CSV to S3 with schema-defined column ordering.

        Args:
            s3_bucket: S3 bucket name.
            s3_key: S3 key for the destination file.
        """
        logger.info(f'Uploading CSV to: s3://{s3_bucket}/{s3_key}')
        with self._duck_conn.cursor() as cursor:
            upload_csv_from_table(
                cursor,
                DuckDBTable.STAGING_ADJUSTMENT_DETAIL,
                s3_bucket,
                s3_key,
                columns=AdjustmentStagingSchema.COLUMNS,
            )
        logger.info('File uploaded')

    def _export_table_to_file(self, file_path: str | Path) -> None:
        """Export DuckDB staging table as CSV to local file with schema-defined column ordering.

        Args:
            file_path: Path to the destination CSV file.
        """
        logger.info(f'Writing CSV to: {file_path}')
        with self._duck_conn.cursor() as cursor:
            create_csv_from_table(
                cursor,
                DuckDBTable.STAGING_ADJUSTMENT_DETAIL,
                file_path,
                columns=AdjustmentStagingSchema.COLUMNS,
            )
        logger.info('File created')