"""
Sync the language catalog into every school's own database.

For each active school:
  1. Points the 'school' connection at that school's DB.
  2. Creates the school_admin_schoollanguage table if missing.
  3. Upserts a row per active master-catalog language.
  4. Carries over any old master-DB SchoolLanguageAccess enablement
     (from before languages moved into school DBs), when present.

Run with: python manage.py sync_school_languages
"""
from django.core.management.base import BaseCommand
from django.db import connections

from master_admin.models import School
from master_admin.language_views import sync_school_languages


class Command(BaseCommand):
    help = "Create/sync the languages table in every school database"

    def handle(self, *args, **options):
        # Old enablement (master DB) — may already be gone after cleanup
        legacy = {}
        try:
            with connections["default"].cursor() as cursor:
                cursor.execute(
                    """
                    SELECT a.school_id, l.code, a.is_enabled
                    FROM master_admin_schoollanguageaccess a
                    JOIN master_admin_language l ON l.id = a.language_id
                    """
                )
                for school_id, code, is_enabled in cursor.fetchall():
                    legacy.setdefault(school_id, {})[code] = is_enabled
            self.stdout.write(f"Found legacy access rows for {len(legacy)} school(s)")
        except Exception:
            self.stdout.write("No legacy SchoolLanguageAccess table (already removed)")

        from school_admin.models import SchoolLanguage

        for school in School.objects.all():
            try:
                connections["school"].settings_dict.update({
                    "NAME": school.db_name,
                    "USER": school.db_user,
                    "PASSWORD": school.db_password,
                    "HOST": school.db_host,
                    "PORT": str(school.db_port),
                })
                connections["school"].close()

                rows = sync_school_languages()

                # Apply legacy enablement once
                overrides = legacy.get(school.id, {})
                for row in rows:
                    if row.is_default:
                        continue
                    if row.code in overrides and row.is_enabled != overrides[row.code]:
                        row.is_enabled = overrides[row.code]
                        row.save(using="school")

                enabled = list(
                    SchoolLanguage.objects.using("school")
                    .filter(is_enabled=True)
                    .values_list("code", flat=True)
                )
                self.stdout.write(self.style.SUCCESS(
                    f"OK  {school.name} ({school.db_name}): enabled={enabled}"
                ))
            except Exception as e:
                self.stdout.write(self.style.ERROR(
                    f"FAIL {school.name} ({school.db_name}): {e}"
                ))