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

class Command(BaseCommand):
    help = 'Transfer data from MySQL to MongoDB'

    def handle(self, *args, **options):
        # Get MongoDB configuration from settings.py
        mongo_settings = settings.DATABASES['mongodb']

        # Define a dictionary to map MySQL tables to MongoDB collections
        table_to_collection = {
            'vw_agents': 'agents',
            'vw_features': 'features',
            'vw_medias': 'medias',
            'vw_offices': 'offices',
            'vw_properties': 'properties',
            'vw_properties_feature': 'properties_feature',
            'vw_properties_location': 'properties_location',
            'vw_properties_media': 'properties_media',
            'vw_types': 'types',
            'vw_usages': 'usages',
        }

        # 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['default']
        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:
                    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()