from django.core.management.base import BaseCommand

from establishment.models import Establishment, EstablishmentType
from transfer import models as legacy
from tag.models import Tag, TagCategory
from tqdm import tqdm
from django.db import connections
from collections import namedtuple


def namedtuplefetchall(cursor):
    "Return all rows from a cursor as a namedtuple"
    desc = cursor.description
    nt_result = namedtuple('Result', [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall()]


def metadata_category_sql():
    with connections['legacy'].cursor() as cursor:
        cursor.execute(
            '''SELECT
                                `key`,
                                establishments.type,
                                key_value_metadata.`value_type`,
                                public,
                                key_value_metadata.id as 'old_id'
                            FROM metadata
                                LEFT JOIN establishments
                                          ON metadata.establishment_id=establishments.id
                                LEFT JOIN key_value_metadata
                                          ON metadata.key=key_value_metadata.key_name
                            GROUP BY
                                establishments.type,
                                `key`,
                                key_value_metadata.`value_type`,
                                public, old_id;'''
        )
        return namedtuplefetchall(cursor)


def metadata_tags_sql():
    with connections['legacy'].cursor() as cursor:
        cursor.execute(
            """
            SELECT
                value,
                `key` as category,
                establishment_id
            FROM metadata
            WHERE establishment_id is not null"""
        )
        return namedtuplefetchall(cursor)


class Command(BaseCommand):
    help = 'Add tags values from old db to new db'

    def get_type(self, meta):
        meta_type = meta.value_type
        if not meta.value_type:
            if meta.key == 'wineyard_visits':
                meta_type = 'list'
            elif meta.key in ['private_room', 'outside_sits']:
                meta_type = 'bool'
        return meta_type

    def get_label(self, text):
        sp = text.split('_')
        label = ' '.join([sp[0].capitalize()] + sp[1:])
        return label

    def handle(self, *args, **kwargs):
        existing_establishment = Establishment.objects.filter(
            old_id__isnull=False
        )
        MAPPER = {
            'Restaurant': EstablishmentType.RESTAURANT,
            'Wineyard': EstablishmentType.PRODUCER,
            'Shop': EstablishmentType.ARTISAN
        }
        # remove old black category
        for establishment_tag in tqdm(EstablishmentType.objects.all()):
                establishment_tag.tag_categories.remove(*list(
                    establishment_tag.tag_categories.exclude(
                        tags__establishments__isnull=False).distinct()))

        # created Tag Category
        for meta in tqdm(metadata_category_sql()):
            category, _ = TagCategory.objects.update_or_create(
                index_name=meta.key,
                defaults={
                    "public": True if meta.public == 1 else False,
                    "value_type": self.get_type(meta),
                    "label": {"en-GB": self.get_label(meta.key)}
                }
            )

            # add to EstablishmentType
            est_type = EstablishmentType.objects.get(index_name=MAPPER[meta.type])
            if category not in est_type.tag_categories.all():
                est_type.tag_categories.add(category)

        count = 0
        for meta_tag in tqdm(metadata_tags_sql()):

            tag, _ = Tag.objects.update_or_create(
                category=TagCategory.objects.get(index_name=meta_tag.category),
                value=meta_tag.value,
                defaults={
                    "label": {"en-GB": self.get_label(meta_tag.value)}
                }
            )
            establishment = existing_establishment.filter(old_id=meta_tag.establishment_id).first()
            if establishment:
                if tag not in establishment.tags.all():
                    establishment.tags.add(tag)
                    count += 1
        self.stdout.write(self.style.WARNING(f'Created {count} tags to Establishment'))
