"""
Management command to create sandbox schemas for tenants.

This command creates a sandbox PostgreSQL schema for each tenant that doesn't
already have one. The sandbox schema provides complete data isolation for
test/development purposes.

Usage:
    # Create sandbox schemas for all tenants
    python manage.py create_sandbox_schemas

    # Create sandbox schema for a specific tenant
    python manage.py create_sandbox_schemas --tenant=demo

    # Run migrations on sandbox schemas after creation
    python manage.py create_sandbox_schemas --migrate
"""

from django.core.management.base import BaseCommand
from django.core.management import call_command
from django.db import connection
from smoothschedule.identity.core.models import Tenant


class Command(BaseCommand):
    help = 'Create sandbox schemas for tenants'

    def add_arguments(self, parser):
        parser.add_argument(
            '--tenant',
            type=str,
            help='Specific tenant schema name to create sandbox for',
        )
        parser.add_argument(
            '--migrate',
            action='store_true',
            help='Run migrations on sandbox schemas after creation',
        )
        parser.add_argument(
            '--dry-run',
            action='store_true',
            help='Show what would be done without making changes',
        )

    def handle(self, *args, **options):
        tenant_filter = options.get('tenant')
        run_migrations = options.get('migrate', False)
        dry_run = options.get('dry_run', False)

        # Get tenants to process
        queryset = Tenant.objects.exclude(schema_name='public')
        if tenant_filter:
            queryset = queryset.filter(schema_name=tenant_filter)

        tenants = list(queryset)
        if not tenants:
            self.stdout.write(
                self.style.WARNING('No tenants found to process')
            )
            return

        self.stdout.write(f'Processing {len(tenants)} tenant(s)...')

        created_count = 0
        skipped_count = 0
        error_count = 0

        for tenant in tenants:
            # Generate sandbox schema name if not set
            if not tenant.sandbox_schema_name:
                tenant.sandbox_schema_name = f"{tenant.schema_name}_sandbox"
                if not dry_run:
                    tenant.save(update_fields=['sandbox_schema_name'])

            sandbox_schema = tenant.sandbox_schema_name

            # Check if schema already exists
            with connection.cursor() as cursor:
                cursor.execute(
                    "SELECT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = %s)",
                    [sandbox_schema]
                )
                schema_exists = cursor.fetchone()[0]

            if schema_exists:
                self.stdout.write(
                    f'  {tenant.name}: Schema "{sandbox_schema}" already exists, skipping'
                )
                skipped_count += 1
                continue

            if dry_run:
                self.stdout.write(
                    f'  {tenant.name}: Would create schema "{sandbox_schema}"'
                )
                created_count += 1
                continue

            # Create the sandbox schema
            try:
                with connection.cursor() as cursor:
                    cursor.execute(f'CREATE SCHEMA "{sandbox_schema}"')

                self.stdout.write(
                    self.style.SUCCESS(
                        f'  {tenant.name}: Created schema "{sandbox_schema}"'
                    )
                )
                created_count += 1

                # Run migrations on the new schema if requested
                if run_migrations:
                    self.stdout.write(
                        f'    Running migrations on "{sandbox_schema}"...'
                    )
                    try:
                        call_command(
                            'migrate_schemas',
                            schema_name=sandbox_schema,
                            verbosity=0,
                        )
                        self.stdout.write(
                            self.style.SUCCESS('    Migrations complete')
                        )
                    except Exception as e:
                        self.stdout.write(
                            self.style.ERROR(f'    Migration error: {e}')
                        )

            except Exception as e:
                self.stdout.write(
                    self.style.ERROR(
                        f'  {tenant.name}: Error creating schema: {e}'
                    )
                )
                error_count += 1

        # Summary
        self.stdout.write('')
        self.stdout.write('Summary:')
        self.stdout.write(f'  Created: {created_count}')
        self.stdout.write(f'  Skipped (already exist): {skipped_count}')
        if error_count:
            self.stdout.write(
                self.style.ERROR(f'  Errors: {error_count}')
            )

        if dry_run:
            self.stdout.write('')
            self.stdout.write(
                self.style.WARNING('Dry run - no changes were made')
            )
