from django.core.management.base import BaseCommand
from django.db import connections, transaction
from boulevarddosipes.models import *
from django.conf import settings
from django.utils import timezone
import pytz
from django.apps import apps
import pymysql

import logging
from logging.handlers import RotatingFileHandler

logger = logging.getLogger(__name__)
logger.setLevel(logging.INFO)

# Define the file path for the log file
log_file = f'{settings.BASE_DIR}/logs/sync_data_boulevarddosipes.log'

# Create a file handler and set its properties
file_handler = RotatingFileHandler(log_file, maxBytes=1024*1024, backupCount=5)
file_handler.setLevel(logging.INFO)

# Define the log format
formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
file_handler.setFormatter(formatter)

# Add the file handler to the logger
logger.addHandler(file_handler)


class Command(BaseCommand):
    help = 'Synchronizes opportunities data from the source database'

    missing_value = "Não Informado"

    def handle(self, *args, **options): 
        # Assuming 'default' is your target database and 'source_db' is your source database alias in settings.py
        self.stdout.write(self.style.SUCCESS('Starting synchronization process...'))

        try:
            import time
            start_time = time.time()
            # Step 1: Truncate the tables:
            # - bi_acessos
            # - bi_acoes_funil
            # - bi_atividades
            # - bi_oportunidades
            # - bi_propostas
            # - bi_vendas
            self.truncate_table()
            
            # Step 2: Fetch data from the source_db
            self.sync_data()
            
            # Step 3: Refresh Tables
            self.refresh_tables()
            
            end_time = time.time()
            elapsed_time = end_time - start_time

            self.stdout.write(self.style.SUCCESS(f'Synchronization process completed successfully in {elapsed_time} seconds.'))
            logger.info(f'Synchronization process completed successfully in {elapsed_time} seconds.')
        except Exception as e:
            self.stdout.write(self.style.ERROR(f'Synchronization process failed: {e}'))
            logger.error(f'Synchronization process failed: {e}')

    
    def truncate_table(self):
        with transaction.atomic():
            with connections['db_boulevarddosipes'].cursor() as cursor:
                tables_to_truncate = [
                    'bi_acessos',
                    'bi_acoes_funil',
                    'bi_atividades',
                    'bi_oportunidades',
                    'bi_propostas',
                    'bi_vendas',
                    'bi_utms',
                ]

                self.stdout.write(self.style.NOTICE('Truncating tables...'))
                logger.info('Truncating tables...')
                try:
                    with connections['db_boulevarddosipes'].cursor() as cursor:
                        for table in tables_to_truncate:
                            try:
                                self.stdout.write(self.style.NOTICE(f'Truncating table {table}...'))
                                logger.info(f'Truncating table {table}...')
                                cursor.execute(f"TRUNCATE TABLE {table}")
                            except Exception as e:
                                self.stdout.write(self.style.ERROR(f'Error truncating table {table}: {e}'))
                                logger.error(f'Error truncating table {table}: {e}')
                                raise e
                except Exception as e:
                    self.stdout.write(self.style.ERROR(f'Error truncating tables: {e}'))
                    logger.error(f'Error truncating tables: {e}')
                    raise e
   
        self.stdout.write(self.style.SUCCESS('Truncation completed.'))
        logger.info('Truncation completed.')
 
    
    def sync_data(self):
        self.stdout.write(self.style.NOTICE('Fetching data from source database...'))
        logger.info('Fetching data from source database...')

        # Choose the correct connection based on DEBUG setting
        connection_name = 'source_db_development' if settings.DEBUG else 'source_db_production'
        source_connection = connections[connection_name]

        # Create a dictionary to map source tables to target tables
        table_mapping = {
            'vw_pixcrm_boulevarddosipes_acessos': 'bi_acessos',
            'vw_pixcrm_boulevarddosipes_acoes_funil': 'bi_acoes_funil',
            'vw_pixcrm_boulevarddosipes_atividades': 'bi_atividades',
            'vw_pixcrm_boulevarddosipes_oportunidades': 'bi_oportunidades',
            'vw_pixcrm_boulevarddosipes_propostas': 'bi_propostas',
            'vw_pixcrm_boulevarddosipes_vendas': 'bi_vendas',
            'vw_pixcrm_boulevarddosipes_utms': 'bi_utms'
        }

        try:
            with source_connection.cursor() as source_cursor:
                for source_table, target_table in table_mapping.items():
                    source_cursor.execute(f"SELECT * FROM {source_table}")
                    columns = [col[0] for col in source_cursor.description]

                    rows_to_create = []
                    for row in source_cursor.fetchall():
                        processed_row = {
                            col: ("Não Informado" if (value is None or value == "") and isinstance(value, str) else
                                   0 if (value is None or value == "") and isinstance(value, (int, float)) else
                                   value)
                            for col, value in zip(columns, row)
                        }
                        for field, value in processed_row.items():
                            if (value is None or value == "" or value == "Não informada") and isinstance(value, str):
                                processed_row[field] = self.missing_value
                        rows_to_create.append(processed_row)

                    with connections['db_boulevarddosipes'].cursor() as target_cursor:
                        for row in rows_to_create:
                            placeholders = ', '.join(['%s'] * len(row))
                            columns = ', '.join(row.keys())
                            values = tuple(row.values())
                            target_cursor.execute(f"INSERT INTO {target_table} ({columns}) VALUES ({placeholders})", values)

            self.stdout.write(self.style.SUCCESS('Data fetched and processed. Records inserted into target tables.'))
            logger.info('Data fetched and processed. Records inserted into target tables.')
        except Exception as e:
            self.stdout.write(self.style.ERROR(f'Error fetching and processing data: {e}'))
            logger.error(f'Error fetching and processing data: {e}')
            raise e


    def refresh_tables(self):
        self.stdout.write(self.style.NOTICE('Refreshing tables...'))
        logger.info('Refreshing tables...')
        try:
            with transaction.atomic():
                with connections['db_boulevarddosipes'].cursor() as cursor:
                    cursor.execute("CALL RefreshTables()")
            self.stdout.write(self.style.SUCCESS('Tables refreshed.'))
            logger.info('Tables refreshed.')
        except Exception as e:
            self.stdout.write(self.style.ERROR(f'Error refreshing tables: {e}'))
            logger.error(f'Error refreshing tables: {e}')
            raise e