"""
CSV migration tools for importing data from the legacy C# system.
Migrates recipes, users, machine configurations, and hardware mappings.
"""

import csv
import logging
from pathlib import Path
from typing import List, Dict, Any, Optional, Tuple
from datetime import datetime

from ..shared.database import db_manager
from ..shared.models.recipe import Recipe
from ..shared.models.machine import MachineConfiguration, HardwareMapping
from ..shared.models.user import User, UserRole
from ..shared.models.system import SystemConfiguration


logger = logging.getLogger(__name__)


class CSVMigrationError(Exception):
    """Exception raised during CSV migration."""
    pass


class CSVMigrator:
    """
    CSV migration tool for importing legacy data.
    Handles validation, data transformation, and database insertion.
    """
    
    def __init__(self, csv_directory: Path):
        self.csv_directory = Path(csv_directory)
        self.migration_stats = {
            "recipes": {"imported": 0, "errors": 0},
            "users": {"imported": 0, "errors": 0},
            "machine_config": {"imported": 0, "errors": 0},
            "hardware_mappings": {"imported": 0, "errors": 0},
            "system_config": {"imported": 0, "errors": 0}
        }
        
        # Expected CSV files from the legacy system
        self.csv_files = {
            "recipes": "Recipe.csv",
            "users": "Users.csv", 
            "machine": "Machine.csv",
            "mapping": "Mapping.csv",
            "configuration": "Configuration.csv",
            "screen": "Screen.csv",
            "error_settings": "ErrorSettings.csv"
        }
    
    async def migrate_all(self, dry_run: bool = False) -> Dict[str, Any]:
        """
        Migrate all CSV files to database.
        
        Args:
            dry_run: If True, validate data without inserting to database
            
        Returns:
            Migration summary with statistics and errors
        """
        logger.info(f"Starting CSV migration from {self.csv_directory}")
        logger.info(f"Dry run: {dry_run}")
        
        migration_results = {
            "started_at": datetime.now(),
            "dry_run": dry_run,
            "files_processed": [],
            "statistics": self.migration_stats.copy(),
            "errors": []
        }
        
        try:
            # Validate CSV directory exists
            if not self.csv_directory.exists():
                raise CSVMigrationError(f"CSV directory not found: {self.csv_directory}")
            
            # Migrate recipes
            await self._migrate_recipes(dry_run)
            migration_results["files_processed"].append("recipes")
            
            # Migrate users
            await self._migrate_users(dry_run)
            migration_results["files_processed"].append("users")
            
            # Migrate machine configuration
            await self._migrate_machine_configuration(dry_run)
            migration_results["files_processed"].append("machine_config")
            
            # Migrate hardware mappings
            await self._migrate_hardware_mappings(dry_run)
            migration_results["files_processed"].append("hardware_mappings")
            
            # Migrate system configuration
            await self._migrate_system_configuration(dry_run)
            migration_results["files_processed"].append("system_config")
            
            migration_results["completed_at"] = datetime.now()
            migration_results["statistics"] = self.migration_stats
            
            logger.info("CSV migration completed successfully")
            
        except Exception as e:
            migration_results["error"] = str(e)
            migration_results["completed_at"] = datetime.now()
            logger.error(f"CSV migration failed: {e}")
            raise
        
        return migration_results
    
    async def _migrate_recipes(self, dry_run: bool = False):
        """Migrate recipe data from Recipe.csv."""
        csv_path = self.csv_directory / self.csv_files["recipes"]
        
        if not csv_path.exists():
            logger.warning(f"Recipe CSV not found: {csv_path}")
            return
        
        logger.info(f"Migrating recipes from {csv_path}")
        
        try:
            recipes = self._read_csv_file(csv_path)
            
            if not dry_run:
                async with db_manager.get_async_session() as session:
                    for row in recipes:
                        try:
                            recipe = Recipe.from_csv_row(row)
                            
                            # Validate recipe
                            if not recipe.validate_temperatures():
                                logger.warning(f"Invalid recipe temperatures: {recipe.name}")
                                self.migration_stats["recipes"]["errors"] += 1
                                continue
                            
                            # Check for existing recipe with same name
                            existing = await session.execute(
                                "SELECT id FROM recipes WHERE name = :name",
                                {"name": recipe.name}
                            )
                            
                            if existing.fetchone():
                                logger.warning(f"Recipe already exists: {recipe.name}")
                                self.migration_stats["recipes"]["errors"] += 1
                                continue
                            
                            session.add(recipe)
                            self.migration_stats["recipes"]["imported"] += 1
                            
                        except Exception as e:
                            logger.error(f"Error migrating recipe {row.get('Name', 'unknown')}: {e}")
                            self.migration_stats["recipes"]["errors"] += 1
                    
                    await session.commit()
            else:
                # Dry run - validate only
                for row in recipes:
                    try:
                        recipe = Recipe.from_csv_row(row)
                        if recipe.validate_temperatures():
                            self.migration_stats["recipes"]["imported"] += 1
                        else:
                            self.migration_stats["recipes"]["errors"] += 1
                    except Exception as e:
                        logger.error(f"Validation error for recipe {row.get('Name', 'unknown')}: {e}")
                        self.migration_stats["recipes"]["errors"] += 1
            
            logger.info(f"Recipe migration completed: {self.migration_stats['recipes']['imported']} imported, {self.migration_stats['recipes']['errors']} errors")
            
        except Exception as e:
            logger.error(f"Error migrating recipes: {e}")
            raise CSVMigrationError(f"Recipe migration failed: {e}")
    
    async def _migrate_users(self, dry_run: bool = False):
        """Migrate user data from Users.csv."""
        csv_path = self.csv_directory / self.csv_files["users"]
        
        if not csv_path.exists():
            logger.warning(f"Users CSV not found: {csv_path}")
            return
        
        logger.info(f"Migrating users from {csv_path}")
        
        try:
            users = self._read_csv_file(csv_path)
            
            if not dry_run:
                async with db_manager.get_async_session() as session:
                    for row in users:
                        try:
                            user = User.from_csv_row(row)
                            
                            # Set a temporary password that requires change
                            user.password_hash = "MIGRATION_REQUIRED"
                            user.require_password_change = True
                            
                            # Check for existing user
                            existing = await session.execute(
                                "SELECT id FROM users WHERE username = :username",
                                {"username": user.username}
                            )
                            
                            if existing.fetchone():
                                logger.warning(f"User already exists: {user.username}")
                                self.migration_stats["users"]["errors"] += 1
                                continue
                            
                            session.add(user)
                            self.migration_stats["users"]["imported"] += 1
                            
                        except Exception as e:
                            logger.error(f"Error migrating user {row.get('Username', 'unknown')}: {e}")
                            self.migration_stats["users"]["errors"] += 1
                    
                    await session.commit()
            else:
                # Dry run - validate only
                for row in users:
                    try:
                        User.from_csv_row(row)
                        self.migration_stats["users"]["imported"] += 1
                    except Exception as e:
                        logger.error(f"Validation error for user {row.get('Username', 'unknown')}: {e}")
                        self.migration_stats["users"]["errors"] += 1
            
            logger.info(f"User migration completed: {self.migration_stats['users']['imported']} imported, {self.migration_stats['users']['errors']} errors")
            
        except Exception as e:
            logger.error(f"Error migrating users: {e}")
            raise CSVMigrationError(f"User migration failed: {e}")
    
    async def _migrate_machine_configuration(self, dry_run: bool = False):
        """Migrate machine configuration from Machine.csv."""
        csv_path = self.csv_directory / self.csv_files["machine"]
        
        if not csv_path.exists():
            logger.warning(f"Machine CSV not found: {csv_path}")
            return
        
        logger.info(f"Migrating machine configuration from {csv_path}")
        
        try:
            machine_configs = self._read_csv_file(csv_path)
            
            if not dry_run:
                async with db_manager.get_async_session() as session:
                    for row in machine_configs:
                        try:
                            config = MachineConfiguration.from_csv_row(row)
                            
                            # Validate parameters
                            if not config.validate_parameters():
                                logger.warning(f"Invalid machine configuration: ID {config.id}")
                                self.migration_stats["machine_config"]["errors"] += 1
                                continue
                            
                            session.add(config)
                            self.migration_stats["machine_config"]["imported"] += 1
                            
                        except Exception as e:
                            logger.error(f"Error migrating machine config {row.get('ID', 'unknown')}: {e}")
                            self.migration_stats["machine_config"]["errors"] += 1
                    
                    await session.commit()
            else:
                # Dry run - validate only
                for row in machine_configs:
                    try:
                        config = MachineConfiguration.from_csv_row(row)
                        if config.validate_parameters():
                            self.migration_stats["machine_config"]["imported"] += 1
                        else:
                            self.migration_stats["machine_config"]["errors"] += 1
                    except Exception as e:
                        logger.error(f"Validation error for machine config {row.get('ID', 'unknown')}: {e}")
                        self.migration_stats["machine_config"]["errors"] += 1
            
            logger.info(f"Machine config migration completed: {self.migration_stats['machine_config']['imported']} imported, {self.migration_stats['machine_config']['errors']} errors")
            
        except Exception as e:
            logger.error(f"Error migrating machine configuration: {e}")
            raise CSVMigrationError(f"Machine configuration migration failed: {e}")
    
    async def _migrate_hardware_mappings(self, dry_run: bool = False):
        """Migrate hardware mappings from Mapping.csv."""
        csv_path = self.csv_directory / self.csv_files["mapping"]
        
        if not csv_path.exists():
            logger.warning(f"Mapping CSV not found: {csv_path}")
            return
        
        logger.info(f"Migrating hardware mappings from {csv_path}")
        
        try:
            mappings = self._read_csv_file(csv_path)
            
            if not dry_run:
                async with db_manager.get_async_session() as session:
                    for row in mappings:
                        try:
                            mapping = HardwareMapping.from_csv_row(row)
                            
                            # Check for existing mapping
                            existing = await session.execute(
                                "SELECT id FROM hardware_mappings WHERE component_name = :name",
                                {"name": mapping.component_name}
                            )
                            
                            if existing.fetchone():
                                logger.warning(f"Hardware mapping already exists: {mapping.component_name}")
                                self.migration_stats["hardware_mappings"]["errors"] += 1
                                continue
                            
                            session.add(mapping)
                            self.migration_stats["hardware_mappings"]["imported"] += 1
                            
                        except Exception as e:
                            logger.error(f"Error migrating hardware mapping {row.get('Name', 'unknown')}: {e}")
                            self.migration_stats["hardware_mappings"]["errors"] += 1
                    
                    await session.commit()
            else:
                # Dry run - validate only
                for row in mappings:
                    try:
                        HardwareMapping.from_csv_row(row)
                        self.migration_stats["hardware_mappings"]["imported"] += 1
                    except Exception as e:
                        logger.error(f"Validation error for hardware mapping {row.get('Name', 'unknown')}: {e}")
                        self.migration_stats["hardware_mappings"]["errors"] += 1
            
            logger.info(f"Hardware mapping migration completed: {self.migration_stats['hardware_mappings']['imported']} imported, {self.migration_stats['hardware_mappings']['errors']} errors")
            
        except Exception as e:
            logger.error(f"Error migrating hardware mappings: {e}")
            raise CSVMigrationError(f"Hardware mapping migration failed: {e}")
    
    async def _migrate_system_configuration(self, dry_run: bool = False):
        """Migrate system configuration from Configuration.csv and Screen.csv."""
        config_files = [
            (self.csv_files["configuration"], "Configuration"),
            (self.csv_files["screen"], "Screen"), 
            (self.csv_files["error_settings"], "ErrorSettings")
        ]
        
        for filename, category in config_files:
            csv_path = self.csv_directory / filename
            
            if not csv_path.exists():
                logger.warning(f"{category} CSV not found: {csv_path}")
                continue
            
            logger.info(f"Migrating {category} configuration from {csv_path}")
            
            try:
                configs = self._read_csv_file(csv_path)
                
                if not dry_run:
                    async with db_manager.get_async_session() as session:
                        for row in configs:
                            try:
                                # Convert CSV row to system configuration
                                for key, value in row.items():
                                    if key and value:
                                        config_key = f"{category.lower()}_{key.lower()}"
                                        
                                        # Determine data type
                                        data_type = "string"
                                        config_value = value
                                        
                                        try:
                                            float_val = float(value)
                                            data_type = "number"
                                            config_value = float_val
                                        except ValueError:
                                            try:
                                                bool_val = value.lower() in ('true', '1', 'yes', 'on')
                                                if value.lower() in ('true', 'false', '1', '0', 'yes', 'no', 'on', 'off'):
                                                    data_type = "boolean"
                                                    config_value = bool_val
                                            except:
                                                pass
                                        
                                        # Check for existing configuration
                                        existing = await session.execute(
                                            "SELECT id FROM system_configurations WHERE key = :key",
                                            {"key": config_key}
                                        )
                                        
                                        if existing.fetchone():
                                            continue
                                        
                                        system_config = SystemConfiguration(
                                            key=config_key,
                                            category=category,
                                            description=f"Migrated from {filename}",
                                            data_type=data_type
                                        )
                                        
                                        # Set appropriate value field
                                        if data_type == "string":
                                            system_config.value_string = str(config_value)
                                        elif data_type == "number":
                                            system_config.value_number = float(config_value)
                                        elif data_type == "boolean":
                                            system_config.value_boolean = bool(config_value)
                                        
                                        session.add(system_config)
                                        self.migration_stats["system_config"]["imported"] += 1
                                
                            except Exception as e:
                                logger.error(f"Error migrating {category} config: {e}")
                                self.migration_stats["system_config"]["errors"] += 1
                        
                        await session.commit()
                else:
                    # Dry run - count entries
                    for row in configs:
                        self.migration_stats["system_config"]["imported"] += len([k for k, v in row.items() if k and v])
                
            except Exception as e:
                logger.error(f"Error migrating {category} configuration: {e}")
                self.migration_stats["system_config"]["errors"] += 1
        
        logger.info(f"System config migration completed: {self.migration_stats['system_config']['imported']} imported, {self.migration_stats['system_config']['errors']} errors")
    
    def _read_csv_file(self, csv_path: Path) -> List[Dict[str, Any]]:
        """Read and parse a CSV file."""
        try:
            with open(csv_path, 'r', encoding='utf-8') as csvfile:
                # Try to detect delimiter
                sample = csvfile.read(1024)
                csvfile.seek(0)
                
                sniffer = csv.Sniffer()
                delimiter = sniffer.sniff(sample).delimiter
                
                reader = csv.DictReader(csvfile, delimiter=delimiter)
                return list(reader)
                
        except Exception as e:
            logger.error(f"Error reading CSV file {csv_path}: {e}")
            raise CSVMigrationError(f"Failed to read CSV file {csv_path}: {e}")
    
    def get_migration_summary(self) -> Dict[str, Any]:
        """Get migration statistics summary."""
        total_imported = sum(stats["imported"] for stats in self.migration_stats.values())
        total_errors = sum(stats["errors"] for stats in self.migration_stats.values())
        
        return {
            "total_imported": total_imported,
            "total_errors": total_errors,
            "success_rate": (total_imported / max(1, total_imported + total_errors)) * 100,
            "details": self.migration_stats.copy()
        }


async def migrate_csv_data(csv_directory: Path, dry_run: bool = False) -> Dict[str, Any]:
    """
    Convenience function to migrate CSV data.
    
    Args:
        csv_directory: Path to directory containing CSV files
        dry_run: If True, validate data without inserting to database
        
    Returns:
        Migration results and statistics
    """
    migrator = CSVMigrator(csv_directory)
    return await migrator.migrate_all(dry_run=dry_run)