from django.core.management.base import BaseCommand, CommandError
from django.db import connection, transaction


LEGACY_COLUMNS = {
    'table_companies': [
        'company_members_id',
        'company_projects_id',
        'company_addresses_id',
    ],
    'table_projects': [
        'project_sales_document',
    ],
    'table_interactions': [
        'interaction_company_id',
    ],
}


class Command(BaseCommand):
    help = "Supprime les anciennes colonnes relationnelles legacy après validation de la refonte CRM."

    def add_arguments(self, parser):
        parser.add_argument(
            '--execute',
            action='store_true',
            help='Applique réellement les ALTER TABLE. Sans cette option, la commande fait seulement un dry-run.',
        )

    def _get_database_name(self):
        return connection.settings_dict.get('NAME')

    def _column_exists(self, cursor, database_name, table_name, column_name):
        cursor.execute(
            """
            SELECT COUNT(*)
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = %s
              AND TABLE_NAME = %s
              AND COLUMN_NAME = %s
            """,
            [database_name, table_name, column_name],
        )
        return cursor.fetchone()[0] > 0

    def _quote_name(self, name):
        return '`' + str(name).replace('`', '``') + '`'

    def handle(self, *args, **options):
        execute = options['execute']
        database_name = self._get_database_name()

        self.stdout.write('Suppression colonnes legacy relationnelles CRM')
        self.stdout.write('------------------------------------------------')
        self.stdout.write(f"Base de données : {database_name}")
        self.stdout.write('')

        if not database_name:
            raise CommandError("Nom de base de données introuvable dans la configuration Django.")

        planned_operations = []
        missing_columns = []

        with connection.cursor() as cursor:
            for table_name, column_names in LEGACY_COLUMNS.items():
                existing_columns = []
                for column_name in column_names:
                    if self._column_exists(cursor, database_name, table_name, column_name):
                        existing_columns.append(column_name)
                    else:
                        missing_columns.append((table_name, column_name))

                if existing_columns:
                    drops = ', '.join(f"DROP COLUMN {self._quote_name(column_name)}" for column_name in existing_columns)
                    sql = f"ALTER TABLE {self._quote_name(table_name)} {drops};"
                    planned_operations.append((table_name, existing_columns, sql))

        if not planned_operations:
            self.stdout.write(self.style.SUCCESS('Aucune colonne legacy relationnelle à supprimer : elles sont déjà absentes.'))
            return

        self.stdout.write('Colonnes détectées à supprimer :')
        for table_name, column_names, sql in planned_operations:
            self.stdout.write(f"- {table_name}: {', '.join(column_names)}")
        self.stdout.write('')

        if missing_columns:
            self.stdout.write('Colonnes déjà absentes :')
            for table_name, column_name in missing_columns:
                self.stdout.write(f"- {table_name}.{column_name}")
            self.stdout.write('')

        self.stdout.write('SQL préparé :')
        for _table_name, _column_names, sql in planned_operations:
            self.stdout.write(sql)
        self.stdout.write('')

        if not execute:
            self.stdout.write(self.style.WARNING('DRY-RUN uniquement : aucune colonne supprimée.'))
            self.stdout.write('Pour appliquer réellement :')
            self.stdout.write('python manage.py drop_legacy_relation_columns --execute')
            return

        self.stdout.write(self.style.WARNING('Application réelle des ALTER TABLE...'))

        try:
            with transaction.atomic():
                with connection.cursor() as cursor:
                    for _table_name, _column_names, sql in planned_operations:
                        cursor.execute(sql)
        except Exception as exc:
            raise CommandError(f"Erreur pendant la suppression des colonnes legacy : {exc}")

        self.stdout.write(self.style.SUCCESS('Colonnes legacy relationnelles supprimées avec succès.'))
        self.stdout.write('Contrôle conseillé maintenant :')
        self.stdout.write('python manage.py check')
        self.stdout.write('python manage.py check_new_tables_integrity')
        self.stdout.write('python manage.py check_legacy_column_removal_ready')