"""Backfill cover_image_url (and seeker avatar_url) for imported profiles."""

import sqlite3
from pathlib import Path

from django.conf import settings
from django.core.management.base import BaseCommand

from companies.models import Company
from home.cover_images import (
    default_company_cover,
    default_seeker_avatar,
    default_seeker_cover,
    legacy_media_url,
)
from seekers.models import SeekerProfile


class Command(BaseCommand):
    help = 'Assign cover images to companies and seekers missing cover_image_url'

    def add_arguments(self, parser):
        parser.add_argument(
            '--legacy-db',
            type=str,
            help='Optional legacy SQLite path to pull real uploaded images first',
        )
        parser.add_argument(
            '--force',
            action='store_true',
            help='Overwrite existing cover_image_url values',
        )

    def handle(self, *args, **options):
        legacy = None
        legacy_path = options.get('legacy_db')
        if legacy_path:
            path = Path(legacy_path)
            if not path.is_file():
                self.stderr.write(self.style.ERROR(f'Legacy DB not found: {path}'))
                return
            legacy = sqlite3.connect(path)
            legacy.row_factory = sqlite3.Row

        company_count = self._backfill_companies(legacy, options['force'])
        seeker_count = self._backfill_seekers(legacy, options['force'])

        if legacy:
            legacy.close()

        self.stdout.write(
            self.style.SUCCESS(
                f'Backfill complete: {company_count} companies, {seeker_count} seekers updated'
            )
        )

    def _backfill_companies(self, legacy, force):
        legacy_logos = {}
        if legacy:
            for row in legacy.execute('SELECT id, logo FROM business_businessaccount'):
                url = legacy_media_url(row['logo'], settings.MEDIA_URL)
                if url:
                    legacy_logos[row['id']] = url

        updated = 0
        qs = Company.objects.all()
        if not force:
            qs = qs.filter(cover_image_url='')

        for company in qs.iterator():
            url = legacy_logos.get(company.pk) or default_company_cover(company)
            company.cover_image_url = url
            company.save(update_fields=['cover_image_url'])
            updated += 1
        return updated

    def _backfill_seekers(self, legacy, force):
        legacy_covers = {}
        legacy_avatars = {}
        if legacy:
            for row in legacy.execute(
                "SELECT id, cover_image, image FROM accounts_user WHERE account_type='job_seeker'"
            ):
                cover = legacy_media_url(row['cover_image'], settings.MEDIA_URL)
                avatar = legacy_media_url(row['image'], settings.MEDIA_URL)
                if cover:
                    legacy_covers[row['id']] = cover
                if avatar:
                    legacy_avatars[row['id']] = avatar

        updated = 0
        qs = SeekerProfile.objects.select_related('user')
        if not force:
            qs = qs.filter(cover_image_url='')

        for seeker in qs.iterator():
            uid = seeker.user_id
            cover = legacy_covers.get(uid) or default_seeker_cover(seeker)
            fields = ['cover_image_url']
            seeker.cover_image_url = cover
            if not seeker.avatar_url:
                seeker.avatar_url = legacy_avatars.get(uid) or default_seeker_avatar(seeker)
                fields.append('avatar_url')
            seeker.save(update_fields=fields)
            updated += 1
        return updated