### transfer_mysql_to_mongodb.py
from django.core.management.base import BaseCommand
from django.conf import settings
import pymongo
import time
from dotenv import load_dotenv
load_dotenv()

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

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

        # 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

        try:
            self.stdout.write(self.style.SUCCESS('Transferring data from Properties Nested Data to MongoDB...'))
            mongo_collection_name = "v1_properties_nested"
                
            # Drop the collection if it already exists
            try:
                mongo_db.drop_collection(mongo_collection_name)
                self.stdout.write(self.style.SUCCESS(f'Dropped collection {mongo_collection_name} successfully!'))
            except Exception as e:
                self.stdout.write(self.style.ERROR(f'An error occurred dropping collection {mongo_collection_name}: {str(e)}'))
                
            ### Create the collection
            mongo_collection = mongo_db[mongo_collection_name]
                
            from datetime import datetime
            pipeline = [
                    {
                        '$unionWith':
                        {
                            'coll': "v1_properties",
                        },
                    },
                    {
                        '$match': {
                            'property_id': {
                                '$exists': True
                            }
                        }
                    },
                    {
                        '$lookup':
                        {
                            'from': "v1_types",
                            'localField': "type_id",
                            'foreignField': "type_id",
                            'as': "type",
                            'pipeline': [
                            {
                                '$project': {
                                '_id': 0,
                                },
                            },
                            ],
                        },
                    },
                    # {
                    #     '$unwind': 
                    #     {
                    #         'path': '$type_id',
                    #         'preserveNullAndEmptyArrays': True
                    #     }
                    # },
                    {
                        '$lookup':
                        {
                            'from': "v1_usages",
                            'localField': "usage_id",
                            'foreignField': "usage_id",
                            'as': "usage",
                            'pipeline': [
                            {
                                '$project': {
                                '_id': 0,
                                },
                            },
                            ],
                        },
                    },
                    # {
                    #     '$unwind': 
                    #     {
                    #         'path': '$usage_id',
                    #         'preserveNullAndEmptyArrays': True
                    #     }
                    # },
                    {
                        '$lookup':
                        {
                            'from': "v1_offices",
                            'localField': "office_id",
                            'foreignField': "office_id",
                            'as': "office",
                            'pipeline': [
                            {
                                '$project': {
                                '_id': 0,
                                },
                            },
                            ],
                        },
                    },
                    # {
                    #     '$unwind': 
                    #     {
                    #         'path': '$office_id',
                    #         'preserveNullAndEmptyArrays': True
                    #     }
                    # },
                    {
                        '$lookup':
                        {
                            'from': "v1_agents",
                            'localField': "agent_id",
                            'foreignField': "agent_id",
                            'as': "agent",
                            'pipeline': [
                            {
                                '$project': {
                                '_id': 0,
                                },
                            },
                            ],
                        },
                    },
                    # {
                    #     '$unwind': 
                    #     {
                    #         'path': '$agent_id',
                    #         'preserveNullAndEmptyArrays': True
                    #     }
                    # },
                    {
                        '$lookup':
                        {
                            'from': "v1_properties_location",
                            'localField': "location_id",
                            'foreignField': "property_location_id",
                            'as': "location",
                            'pipeline': [
                            {
                                '$project': {
                                '_id': 0,
                                },
                            },
                            ],
                        },
                    },
                    # {
                    #     '$unwind': 
                    #     {
                    #         'path': '$location_id',
                    #         'preserveNullAndEmptyArrays': True
                    #     }
                    # },
                    ### get all the medias from v1_properties_media of a property_id
                    {
                        '$lookup': {
                            'from': "v1_properties_media",
                            'localField': "property_id",
                            'foreignField': "property_id",
                            'as': "medias",
                            'pipeline': [
                            {
                                '$project': {
                                '_id': 0,
                                'property_id':0,
                                },
                            },
                            ],                            
                        }
                    },
                    # {
                    #     '$unset': 'medias.property_id'
                    # },
                    {
                        '$lookup': {
                            'from': "v1_properties_feature",
                            'localField': "property_id",
                            'foreignField': "property_id",
                            'as': "features",
                             'pipeline': [
                            {
                                '$project': {
                                '_id': 0,
                                'property_id':0
                                },
                            },
                            ],                           
                        }
                    },
                    # {
                    #     '$unset': 'features.property_id'
                    # },
                    {
                        '$lookup': {
                            'from': "v1_features",
                            'localField': "features.feature_id",
                            'foreignField': "feature_id",
                            'as': "features",
                            'pipeline': [
                            {
                                '$project': {
                                '_id': 0,
                                },
                            },
                            ],                       
                        }
                    },
                    {
                       '$unset': 'type_id'
  					},
                    {
                       '$unset': 'usage_id'
  					},
                    {
                       '$unset': 'office_id'
  					},
                    {
                      '$unset': 'agent_id'
  					},
                    {
                      '$unset': 'location_id'
  					},
                    {
                        '$addFields': {
                            'transfer_date': datetime.now()
                        }
                    }
            ]
                
            result = mongo_collection.aggregate(pipeline)
            mongo_db[mongo_collection_name].insert_many(result)
            try:
                mongo_db[mongo_collection_name].create_index([("property_id", pymongo.ASCENDING)], unique=True)
                self.stdout.write(self.style.SUCCESS(f'Index created successfully!'))
            except Exception as e:
                self.stdout.write(self.style.ERROR(f'An error occurred creating index: {str(e)}'))
            ## List the first document
            #print(list(mongo_collection.find().limit(1)))                
            self.stdout.write(self.style.SUCCESS(f'Data transfer from Properties to {mongo_collection_name} completed successfully!'))
        except Exception as e:
            self.stdout.write(self.style.ERROR(f'An error occurred: {str(e)}'))

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