"""
ExportMailer functionality for rateloader.

Implements CSV export and email functionality as specified in PRD §2.1 and Phase 3.
Downloads SQL files from URLs, executes queries, generates CSV files, and sends emails.
"""

import os
import urllib.request
import urllib.error
import zipfile
import tempfile
from typing import Optional, Tuple
from core.config import get_config
from core.logger import get_hotellabexport_logger
from core.oracle import create_oracle_wrapper
from core.mailer import create_mailer


class ExportMailer:
    """Handles CSV export and email functionality for the rateloader application."""

    def __init__(self):
        """Initialize ExportMailer with configuration and logger."""
        self.config = get_config()
        self.logger = get_hotellabexport_logger()
        self.oracle_wrapper = create_oracle_wrapper()
        self.mailer = create_mailer()

    def download_sql_file(self, url: str, local_filename: str) -> bool:
        """
        Download SQL file from URL with error handling.

        On error, logs and reuses previous local copy if available.
        If auto_update_sql is disabled, skips download and uses local copy.

        Args:
            url: URL to download SQL file from
            local_filename: Local filename to save the SQL content

        Returns:
            True if download successful or fallback available, False otherwise
        """
        # Check if auto-update is disabled
        auto_update_enabled = self.config.get_bool("auto_update_sql", fallback=True)
        if not auto_update_enabled:
            if os.path.exists(local_filename):
                self.logger.info(f"Auto-update disabled, using existing local SQL file: {local_filename}")
                return True
            else:
                self.logger.error(f"Auto-update disabled but no local SQL file found: {local_filename}")
                return False

        try:
            self.logger.info(f"Downloading SQL file from: {url}")
            urllib.request.urlretrieve(url, local_filename)
            self.logger.info(f"SQL file downloaded successfully: {local_filename}")
            return True
        except urllib.error.URLError as e:
            self.logger.error(f"Failed to download SQL file from {url}: {str(e)}")
            if os.path.exists(local_filename):
                self.logger.info(f"Using existing local copy: {local_filename}")
                return True
            else:
                self.logger.error(f"No local copy available for: {local_filename}")
                return False
        except Exception as e:
            self.logger.error(f"Unexpected error downloading SQL file: {str(e)}")
            if os.path.exists(local_filename):
                self.logger.info(f"Using existing local copy: {local_filename}")
                return True
            else:
                self.logger.error(f"No local copy available for: {local_filename}")
                return False

    def read_sql_file(self, filename: str) -> Optional[str]:
        """
        Read SQL content from file.

        Args:
            filename: Path to SQL file

        Returns:
            SQL content as string, or None if file cannot be read
        """
        try:
            with open(filename, "r", encoding="utf-8") as f:
                sql_content = f.read()
            self.logger.info(f"SQL file read successfully: {filename}")
            return sql_content
        except Exception as e:
            self.logger.error(f"Failed to read SQL file {filename}: {str(e)}")
            return None

    def get_hotel_key(self) -> Optional[str]:
        """
        Get hotel key from configuration settings.

        Returns:
            Hotel key as string from settings.ini, or None if not configured
        """
        try:
            hotel_key = self.config.get("hotel_key")
            
            if hotel_key:
                self.logger.info(f"Hotel key retrieved from settings: {hotel_key}")
                return str(hotel_key)
            else:
                self.logger.error("Hotel key not found in settings.ini")
                return None

        except Exception as e:
            self.logger.error(f"Error reading hotel key from settings: {str(e)}")
            return None

    def execute_query_to_csv(self, sql: str, csv_filename: str) -> bool:
        """
        Execute SQL query and write results to CSV file.

        CSV format: only first column of each row, one line per row.

        Args:
            sql: SQL query to execute
            csv_filename: Output CSV filename

        Returns:
            True if successful, False otherwise
        """
        try:
            if (
                not self.oracle_wrapper.is_connected()
                and not self.oracle_wrapper.connect()
            ):
                self.logger.error("Failed to connect to Oracle database")
                return False

            # Log hotel name verification for export process
            hotel_name = self.oracle_wrapper.get_hotel_name()
            if hotel_name:
                self.logger.info(f"Connected to database for hotel: {hotel_name}")
            else:
                self.logger.warning("Hotel name verification failed during export, continuing with process")

            self.logger.info(f"Executing query for CSV: {csv_filename}")
            self.logger.debug(f"SQL: {sql}")

            # Get file encoding from config
            file_encoding = self.config.get("file_encoding", "cp1251")

            # Execute query and write CSV
            with open(csv_filename, "w", encoding=file_encoding) as f:
                for chunk in self.oracle_wrapper.stream_query(sql):
                    for item in chunk:
                        f.write(f"{item[0]}\n")

            self.logger.info(f"CSV file created successfully: {csv_filename}")
            return True

        except Exception as e:
            self.logger.error(f"Error executing query to CSV {csv_filename}: {str(e)}")
            return False

    def run_export(self) -> Tuple[bool, Optional[str], Optional[str]]:
        """
        Run the complete export process.

        Downloads SQL files, executes queries, and generates CSV files.

        Returns:
            Tuple of (success, main_csv_path, blocks_csv_path)
        """
        try:
            # Get URLs from config
            main_url = self.config.get("url")
            block_url = self.config.get("block-url")

            if not main_url or not block_url:
                self.logger.error("URLs not found in configuration")
                return False, None, None

            # Download SQL files
            main_sql_file = "select.sql"
            block_sql_file = "selectBlocks.sql"

            if not self.download_sql_file(main_url, main_sql_file):
                self.logger.error("Failed to download main SQL file")
                return False, None, None

            if not self.download_sql_file(block_url, block_sql_file):
                self.logger.error("Failed to download blocks SQL file")
                return False, None, None

            # Read SQL content
            main_sql = self.read_sql_file(main_sql_file)
            block_sql = self.read_sql_file(block_sql_file)

            if not main_sql or not block_sql:
                self.logger.error("Failed to read SQL files")
                return False, None, None

            # Get hotel key
            hotel_key = self.get_hotel_key()
            if not hotel_key:
                self.logger.error("Failed to get hotel key")
                return False, None, None

            # Generate CSV filenames
            main_csv = f"{hotel_key}.csv"
            blocks_csv = f"BLOCKS-{hotel_key}.csv"

            # Execute queries and generate CSV files
            self.logger.info("Starting MAIN export process")
            main_success = self.execute_query_to_csv(main_sql, main_csv)
            if main_success:
                self.logger.info("MAIN export completed successfully")
            else:
                self.logger.error("MAIN export failed")

            self.logger.info("Starting BLOCKS export process")
            blocks_success = self.execute_query_to_csv(block_sql, blocks_csv)
            if blocks_success:
                self.logger.info("BLOCKS export completed successfully")
            else:
                self.logger.error("BLOCKS export failed")

            if main_success and blocks_success:
                self.logger.info("Export process completed successfully")
                return True, main_csv, blocks_csv
            else:
                self.logger.error("Export process partially failed")
                return (
                    False,
                    main_csv if main_success else None,
                    blocks_csv if blocks_success else None,
                )

        except Exception as e:
            self.logger.error(f"Unexpected error in export process: {str(e)}")
            return False, None, None

    def send_emails(self, main_csv: str, blocks_csv: str, hotel_key: str) -> bool:
        """
        Send emails with CSV attachments using the exact legacy signature.

        Args:
            main_csv: Path to main CSV file
            blocks_csv: Path to blocks CSV file
            hotel_key: Hotel key for subject and body

        Returns:
            True if both emails sent successfully, False otherwise
        """
        try:
            # Reload config to get latest settings (fixes GUI config changes)
            from core.config import reload_config
            reload_config()
            self.config = get_config()

            # Compress files if needed
            main_csv_to_send = self.compress_file_if_needed(main_csv)
            blocks_csv_to_send = self.compress_file_if_needed(blocks_csv)
            
            # Send main export email
            sendfrom = self.config.get("sendfrom")
            sendto = self.config.get("sendto")
            self.logger.info(f"Sending main export email for hotel key: {hotel_key}")
            self.logger.info(f"Email details - From: {sendfrom}, To: {sendto}, Subject: {hotel_key}, Attachment: {main_csv_to_send}")
            main_success = self.mailer.send_email_with_attachment(
                subject=hotel_key, body=hotel_key, attachment_path=main_csv_to_send
            )

            if main_success:
                self.logger.info("Main export email sent successfully")
            else:
                self.logger.error("Failed to send main export email")

            # Send blocks export email
            blocks_key = f"BLOCKS-{hotel_key}"
            self.logger.info(f"Sending blocks export email for hotel key: {blocks_key}")
            self.logger.info(f"Email details - From: {sendfrom}, To: {sendto}, Subject: {blocks_key}, Attachment: {blocks_csv_to_send}")
            blocks_success = self.mailer.send_email_with_attachment(
                subject=blocks_key, body=blocks_key, attachment_path=blocks_csv_to_send
            )

            if blocks_success:
                self.logger.info("Blocks export email sent successfully")
            else:
                self.logger.error("Failed to send blocks export email")

            # Clean up compressed files if they were created
            if main_csv_to_send != main_csv and os.path.exists(main_csv_to_send):
                os.remove(main_csv_to_send)
                self.logger.info(f"Cleaned up compressed file: {main_csv_to_send}")
                
            if blocks_csv_to_send != blocks_csv and os.path.exists(blocks_csv_to_send):
                os.remove(blocks_csv_to_send)
                self.logger.info(f"Cleaned up compressed file: {blocks_csv_to_send}")

            return main_success and blocks_success

        except Exception as e:
            self.logger.error(f"Error sending emails: {str(e)}")
            return False

    def compress_file_if_needed(self, file_path: str, max_size_mb: int = 20) -> str:
        """
        Compress file if it exceeds the maximum size limit and compression is enabled.

        Args:
            file_path: Path to the file to check/compress
            max_size_mb: Maximum file size in MB before compression

        Returns:
            Path to the compressed file if compression was needed, original path otherwise
        """
        try:
            # Check if compression is enabled in config
            compress_enabled = self.config.get_bool("compress_attachments", fallback=False)
            if not compress_enabled:
                self.logger.info("Attachment compression disabled in settings")
                return file_path

            file_size_mb = os.path.getsize(file_path) / (1024 * 1024)

            if file_size_mb <= max_size_mb:
                self.logger.info(f"File size {file_size_mb:.2f}MB is within limit, no compression needed")
                return file_path

            self.logger.info(f"File size {file_size_mb:.2f}MB exceeds limit, compressing...")

            # Create compressed file
            compressed_path = f"{file_path}.zip"

            with zipfile.ZipFile(compressed_path, 'w', zipfile.ZIP_DEFLATED) as zipf:
                zipf.write(file_path, os.path.basename(file_path))

            compressed_size_mb = os.path.getsize(compressed_path) / (1024 * 1024)
            self.logger.info(f"File compressed to {compressed_size_mb:.2f}MB")

            return compressed_path

        except Exception as e:
            self.logger.error(f"Error compressing file {file_path}: {str(e)}")
            return file_path  # Return original path if compression fails

    def run_export_mailer(self) -> bool:
        """
        Run the complete ExportMailer process: export and email.

        Returns:
            True if entire process successful, False otherwise
        """
        try:
            # Run export process
            success, main_csv, blocks_csv = self.run_export()

            if not success:
                self.logger.error("Export process failed, skipping email sending")
                return False

            if not main_csv or not blocks_csv:
                self.logger.error("CSV files not generated, skipping email sending")
                return False

            # Extract hotel key from main CSV filename
            hotel_key = os.path.splitext(os.path.basename(main_csv))[0]

            # Send emails
            email_success = self.send_emails(main_csv, blocks_csv, hotel_key)

            if email_success:
                self.logger.info("ExportMailer process completed successfully")
                return True
            else:
                self.logger.error("Email sending failed, but CSV files were generated")
                return False

        except Exception as e:
            self.logger.error(f"Unexpected error in ExportMailer process: {str(e)}")
            return False

    def cleanup(self) -> None:
        """Clean up resources."""
        try:
            if self.oracle_wrapper:
                self.oracle_wrapper.disconnect()
                self.logger.info("Oracle connection closed")
        except Exception as e:
            self.logger.error(f"Error during cleanup: {str(e)}")


def run_export_mailer() -> bool:
    """
    Convenience function to run the complete ExportMailer process.

    Returns:
        True if entire process successful, False otherwise
    """
    export_mailer = ExportMailer()
    try:
        return export_mailer.run_export_mailer()
    finally:
        export_mailer.cleanup()


def run_export_only() -> Tuple[bool, Optional[str], Optional[str]]:
    """
    Convenience function to run only the export part (no email).

    Returns:
        Tuple of (success, main_csv_path, blocks_csv_path)
    """
    export_mailer = ExportMailer()
    try:
        return export_mailer.run_export()
    finally:
        export_mailer.cleanup()
