"""Lambda upload_hfa_request_files function module."""

import os
import csv
import hashlib
from datetime import datetime
import tempfile

from paramiko.ssh_exception import SSHException
import pysftp
import pandas as pd
from lambdacommon.common_config import logger
from lambdacommon.aws import s3, ses
from lambdacommon import util
from config import (
    EMAIL_RECIPIENTS,
    EMAIL_SENDER,
    ENVIRONMENT,
    FTP_CREDENTIALS,
    FTP_PATH,
    PB_DB_CREDENTIALS,
    PROD_ENVIRONMENT,
    PUBLISHING_DB_URL,
    S3_BUCKET_NAME,
    S3_REQUEST_FILE_DIR,
    S3_REQUEST_ZIP_DIR,
    SUCCESS_EMAIL_RECIPIENTS,
)
from sqlalchemy import create_engine
from src.constants import common, file_fields, mail
from src.sql_queries import UPDATE_HFA_ORCHARD_TRACK_LICENSES
from sentry_sdk import capture_exception

from zipfile import ZIP_DEFLATED, ZipFile


util.init_sentry_for_lambda()

engine = create_engine(PUBLISHING_DB_URL, echo=False)
pd.options.mode.copy_on_write = True


def download_file_from_s3(s3_key: str, local_file_path: str) -> None:
    """Download file from S3 and save to local path.

    Args:
        s3_key (str): S3 object key.
        local_file_path (str): Destination local file path.

    Raises:
        Exception: If the file could not be downloaded.
    """
    try:
        with open(local_file_path, 'wb') as data:
            s3.download_file_object(
                S3_BUCKET_NAME,
                s3_key,
                data
            )
        logger.info(f'Successfully downloaded {s3_key} to {local_file_path}.')

    except Exception as e:
        logger.error(f'Unexpected error occurred while downloading {s3_key}: {str(e)}')
        raise


def create_zip(zip_path: str, source_path: str, archive_name: str) -> None:
    """Create a ZIP file containing a specified file.

    Args:
        zip_path (str): Full path where the ZIP file will be created.
        source_path (str): Full path to the source file to be archived.
        archive_name (str): File name to use inside the archive (no path).
    """
    try:
        with ZipFile(zip_path, 'w', ZIP_DEFLATED) as zip:
            zip.write(source_path, arcname=archive_name)

        logger.info(f'Successfully created ZIP file at {zip_path} containing {archive_name}.')
    except Exception as e:
        logger.error(f'Failed to create zip: {e}')
        raise


def upload_file_to_ftp(local_file_path, remote_file_path):
    """Upload a local file to an FTP server via SFTP.

    Args:
        local_file_path (str): Path to the local file.
        remote_file_path (str): Destination path on the remote FTP server.

    Returns:
        bool: True if upload succeeds, False otherwise.
    """
    try:
        cnopts = pysftp.CnOpts()
        cnopts.hostkeys = None

        with pysftp.Connection(
            host=FTP_CREDENTIALS['host'],
            username=FTP_CREDENTIALS['username'],
            password=FTP_CREDENTIALS['password'],
            port=FTP_CREDENTIALS['port'],
            cnopts=cnopts
        ) as sftp:
            logger.info('Connection successfully established.')

            directory_name = FTP_PATH.split('/')
            if directory_name[1] not in sftp.listdir():
                sftp.makedirs(FTP_PATH)

            sftp.put(local_file_path, remote_file_path)

            return sftp.exists(remote_file_path)

    except SSHException as e:
        logger.error(f'SFTP connection failed due to SSH error: {e}')
        raise RuntimeError(
            f'SSHException during FTP upload: {str(e)}\n\n{mail.HFA_SFTP_CONTACT_INFO}'
        ) from e

    except Exception as e:
        logger.error(f'FTP upload failed: {e}')
        raise


def upload_zip_to_s3(file_name: str, file_path: str) -> None:
    """Upload a ZIP file to S3.

    Args:
        file_name (str): The ZIP file name to use in S3.
        file_path (str): Local path to the ZIP file.
    """
    try:
        s3_key = f'{S3_REQUEST_ZIP_DIR}{file_name}'
        full_s3_path = f's3://{S3_BUCKET_NAME}/{s3_key}'

        s3.upload_file_to_s3(S3_BUCKET_NAME, file_path, s3_key)
        logger.info(f'Uploaded {file_name} to S3: {full_s3_path}')

    except Exception as e:
        logger.error(f'Failed to upload {file_name} to S3: {e}')
        raise


def transform_license_data(file_path: str, file_name: str) -> pd.DataFrame:
    """Read and transform license data from a csv txt file.

    Args:
        file_path (str): Path to the csv txt file containing raw license data (no header).
        filename (str): Name of the source file, added as a new column.

    Returns:
        pd.DataFrame: Transformed DataFrame with selected fields, no NA values,
                      and boolean values converted to 'TRUE'/'FALSE' strings.
    """
    try:
        logger.info(f'Reading file: {file_path}')
        hfa_orchard_track_licenses_df = pd.read_csv(
            file_path,
            sep='\t',
            names=file_fields.FIELDS,
            quoting=csv.QUOTE_NONE,
            escapechar='\\'
        )

        cleaned_df = hfa_orchard_track_licenses_df[file_fields.SELECTED_FIELDS]
        cleaned_df['request_file_name'] = file_name
        cleaned_df.fillna('', inplace=True)
        mask = cleaned_df.map(type) != bool
        d = {True: 'TRUE', False: 'FALSE'}

        cleaned_df = cleaned_df.where(
            mask, cleaned_df.replace(d)
        )

        logger.info('Transformation complete')
        return cleaned_df

    except Exception as e:
        logger.error(f'Unexpected error during license data transformation for {file_path} - {e}')
        raise


def update_hfa_orchard_track_licenses_state(hfa_orchard_track_licenses_df: pd.DataFrame) -> None:
    """
    Update the state of records in the `hfa_orchard_track_licenses` table.

    Args:
        hfa_orchard_track_licenses_df (pd.DataFrame): DataFrame containing 'manufacturer_request_number' column.
    """
    try:
        unique_hfa_orchard_track_licenses_id = list(
            set(hfa_orchard_track_licenses_df['manufacturer_request_number'].tolist())
        )
        placeholders = ', '.join(['%s'] * len(unique_hfa_orchard_track_licenses_id))
        query = UPDATE_HFA_ORCHARD_TRACK_LICENSES.format(
            placeholders=placeholders
        )

        logger.info(f'Updating {len(unique_hfa_orchard_track_licenses_id)} track licenses state in the database.')
        with util.mysql_connection(**PB_DB_CREDENTIALS) as conn:
            with conn.cursor() as cursor:
                cursor.execute(query, unique_hfa_orchard_track_licenses_id)
                updated_count = cursor.rowcount
            conn.commit()

        logger.info(f'Updated state for {updated_count} track license(s).')

    except Exception as e:
        logger.error(f'Failed to update hfa_orchard_track_licenses state: {e}')
        raise


def insert_hfa_license_request(hfa_orchard_track_licenses_df: pd.DataFrame) -> int:
    """
    Insert pending HFA license requests into the `hfa_license_requests` table.

    Args:
        hfa_orchard_track_licenses_df (pd.DataFrame): DataFrame containing request data.

    Returns:
        int: Number of records inserted.
    """
    try:
        logger.info(f'Inserting {len(hfa_orchard_track_licenses_df)} records into hfa_license_requests.')
        inserted_count = hfa_orchard_track_licenses_df.to_sql(
            'hfa_license_requests',
            con=engine,
            if_exists='append',
            index=False
        )
        logger.info(f'Inserted {inserted_count} records into hfa_license_requests.')
        return len(hfa_orchard_track_licenses_df)
    except Exception as e:
        logger.error(f'Failed to insert records into hfa_license_requests: {e}')
        raise


def send_success_email(filepath: str, filename: str, number_of_rows: int) -> None:
    """
    Send a success email with file delivery details.

    Args:
        filepath (str): Full path to the delivered file.
        filename (str): Name of the delivered file.
        number_of_rows (int): Number of rows in the data file.
    """
    try:
        file_size = f'{os.path.getsize(filepath) / float(1 << 10):,.0f} KB'
        md5 = hashlib.md5(filename.encode('utf-8')).hexdigest()
        current_time = datetime.now().astimezone().strftime('%a %b %d %H:%M:%S %Z %Y')

        message = mail.MESSAGE_BODY.format(
            filename=filename,
            number_of_rows=number_of_rows,
            md5=md5,
            file_size=file_size,
            current_time=current_time
        )

        subject = (
            mail.SUCCESS_EMAIL_SUBJECT
            if ENVIRONMENT == PROD_ENVIRONMENT
            else f'{mail.SUCCESS_EMAIL_SUBJECT} ({ENVIRONMENT})'
        )

        ses.send_email(
            recipients=SUCCESS_EMAIL_RECIPIENTS,
            sender=EMAIL_SENDER,
            subject=subject,
            message=message
        )
        logger.info(f'Success email sent for file: {filename}')
    except Exception as e:
        logger.error(f'Failed to send success email for file: {filename} due to: {e}')
        raise


# handler
def handler(event, context):
    """Lambda entry point."""
    try:
        logger.info('Lambda execution started')

        result = {
            common.STATUS: event.get(common.STATUS, common.OK),
            common.GENERATE_HFA_TMP_FILES: event.get(common.GENERATE_HFA_TMP_FILES, {}),
            common.GENERATE_HFA_TMP_FILES_LENGTH: event.get(common.GENERATE_HFA_TMP_FILES_LENGTH, 0),
            common.CREATE_HFA_REQUEST_FILES: event.get(common.CREATE_HFA_REQUEST_FILES, {}),
            common.CREATE_HFA_REQUEST_FILES_LENGTH: event.get(common.CREATE_HFA_REQUEST_FILES_LENGTH, 0),
            common.FILES_TO_CLEANUP: event.get(common.FILES_TO_CLEANUP, [])
        }

        if not result[common.CREATE_HFA_REQUEST_FILES]:
            logger.warning('No HFA request files provided. Skipping uploading.')
            return result

        with tempfile.TemporaryDirectory() as tmpdir:
            for key_name, filename in result[common.CREATE_HFA_REQUEST_FILES].items():
                file_type = common.SSA_RGT_MAPPING[key_name]
                logger.info(f'Processing {file_type} Request file: {filename}')
                s3_key = f'{S3_REQUEST_FILE_DIR}{filename}'
                local_path = os.path.join(tmpdir, filename)

                download_file_from_s3(s3_key, local_path)

                zip_file_name = f"{filename.split('.')[0]}.zip"
                local_zip_path = os.path.join(tmpdir, zip_file_name)
                create_zip(local_zip_path, local_path, filename)

                hfa_orchard_track_licenses_df = transform_license_data(local_path, filename)

                remote_file_path = f'{FTP_PATH}/{filename}'
                uploaded_to_hfa = upload_file_to_ftp(local_path, remote_file_path)

                if not uploaded_to_hfa:
                    logger.warning(f'File not uploaded to HFA server: {filename}')
                    continue

                logger.info(f'Successfully uploaded file to HFA FTP: {filename}')
                update_hfa_orchard_track_licenses_state(
                    hfa_orchard_track_licenses_df
                )

                number_of_rows_inserted = insert_hfa_license_request(
                    hfa_orchard_track_licenses_df
                )
                if number_of_rows_inserted:
                    logger.info(f'Sending success email for: {filename}')
                    send_success_email(
                        local_path,
                        filename,
                        number_of_rows_inserted
                    )

                upload_zip_to_s3(zip_file_name, local_zip_path)
                logger.info(f'Completed processing and uploading for {file_type} Request file: {filename}')

        logger.info('Lambda execution completed successfully.')
        return result

    except Exception as e:
        capture_exception(e)
        logger.exception(f'Error in upload_hfa_request_files Lambda: {str(e)}')
        subject = (
            mail.FAILURE_EMAIL_SUBJECT
            if ENVIRONMENT == PROD_ENVIRONMENT
            else f'{mail.FAILURE_EMAIL_SUBJECT} ({ENVIRONMENT})'
        )

        message = f'Lambda failed with exception:\n{str(e)}'
        ses.send_email(
            recipients=EMAIL_RECIPIENTS,
            sender=EMAIL_SENDER,
            subject=subject,
            message=message
        )
        raise e