### transfer_mysql_to_mongodb.py
from django.core.management.base import BaseCommand
from django.conf import settings
import pymongo
import pymysql
import decimal
import time

class Command(BaseCommand):
    help = 'Transfer data from MySQL to MongoDB'
    
    def add_arguments(self, parser):
        parser.add_argument('limit', type=int, nargs='?', default=None, help='Limit the number of records to transfer')

    def handle(self, *args, **options):
        #Start time
        start_time = time.time()
        
        ## Get the limit from the command line arguments
        limit = options['limit']

        
        # Get MongoDB configuration from settings.py
        mongo_settings = settings.DATABASES['mongodb']

        # Define a dictionary to map MySQL tables to MongoDB collections
        table_to_collection = {
            'dCanais': 'dCanais',
            'dCategorias': 'dCategorias',
            'dConsultores': 'dConsultores',
            'dEmpreendimentos': 'dEmpreendimentos',
            'dEstadosCivis': 'dEstadosCivis',
            'dEtapasFunil': 'dEtapasFunil',
            'dFaixasIdade': 'dFaixasIdade',
            'dFechamentoDias': 'dFechamentoDias',
            'dInteresses': 'dInteresses',
            'dMidias': 'dMidias',
            'dMotivosPerdaPausa': 'dMotivosPerdaPausa',
            'dProfissoes': 'dProfissoes',
            'dQuartos': 'dQuartos',
            'dRegioes': 'dRegioes',
            'dRendas': 'dRendas',
            'dSexos': 'dSexos',
            'dStatus': 'dStatus',
            'dStatusAtividade': 'dStatusAtividade',
            'dStatusProposta': 'dStatusProposta',
            'dSuites': 'dSuites',
            'dTemperaturas': 'dTemperaturas',
            'dTipos': 'dTipos',
            'dTiposAtividade': 'dTiposAtividade',
            'dUnidades': 'dUnidades',
            'dUnidadesEmpreendimento': 'dUnidadesEmpreendimento',
            'dUTMs': 'dUTMs',
            'dVagas': 'dVagas',
            'fAcessos': 'fAcessos',
            'fAcoesFunil': 'fAcoesFunil',
            'fAtividades': 'fAtividades',
            'fOportunidades': 'fOportunidades',
            'fPropostas': 'fPropostas',
            'fVendas': 'fVendas',
        }

        # Configure MongoDB connection using settings
        mongo_client = pymongo.MongoClient(
            host=mongo_settings['CLIENT']['host'],
            port=mongo_settings['CLIENT']['port'],
            username=mongo_settings['CLIENT']['username'],
            password=mongo_settings['CLIENT']['password'],
            authSource=mongo_settings['CLIENT']['authSource'],
            authMechanism=mongo_settings['CLIENT']['authMechanism'],
        )

        mongo_db = mongo_client[mongo_settings['NAME']]  # Use the database name from settings

        # Connect to MySQL using pymysql
        mysql_settings = settings.DATABASES['mysql']
        mysql_conn = pymysql.connect(
            host=mysql_settings['HOST'],
            port=mysql_settings['PORT'],
            user=mysql_settings['USER'],
            password=mysql_settings['PASSWORD'],
            db=mysql_settings['NAME'],
        )

        mysql_cursor = mysql_conn.cursor()

        try:
    # Loop through the table-to-collection mapping and transfer data
            for mysql_table, mongo_collection_name in table_to_collection.items():
                mongo_db.drop_collection(mongo_collection_name)
                try:
                    
                    if limit is not None:
                        mysql_cursor.execute(f"SELECT * FROM {mysql_table} LIMIT {limit}")
                    else:
                        mysql_cursor.execute(f"SELECT * FROM {mysql_table}")
                    table_data = mysql_cursor.fetchall()

                    # Fetch column names separately
                    column_names = [desc[0] for desc in mysql_cursor.description]

                    # Create a list to store the converted data
                    converted_data = []

                    # Iterate through each row of data and convert it to a dictionary
                    for row in table_data:
                        row_dict = dict(zip(column_names, row))
                        
                         # Convert Decimal objects to float before inserting into MongoDB
                        for key, value in row_dict.items():
                            if isinstance(value, decimal.Decimal):
                                row_dict[key] = float(value)                           
                        
                        converted_data.append(row_dict)

                    # Insert the converted data into the MongoDB collection
                    mongo_collection = mongo_db[mongo_collection_name]
                    mongo_collection.insert_many(converted_data)
                    self.stdout.write(self.style.SUCCESS(f'Data transfer from {mysql_table} to {mongo_collection_name} completed successfully!'))

                except Exception as e:
                    self.stdout.write(self.style.ERROR(f'An error occurred: {str(e)}'))  

        except Exception as e:
            self.stdout.write(self.style.ERROR(f'An error occurred: {str(e)}'))


        finally:
            # Close database connections
            mysql_cursor.close()
            mysql_conn.close()
            mongo_client.close()
            total_time = round((time.time() - start_time),2)
            self.stdout.write(self.style.SUCCESS(
                    f'Total Time: {total_time}'
                    )
                )