from django.core.management.base import BaseCommand
from django.db import connections
from establishment.management.commands.add_position import namedtuplefetchall
from tag.models import Tag, TagCategory
from product.models import Product, ProductType
from tqdm import tqdm


class Command(BaseCommand):
    help = '''Add product tags  networks from old db to new db.
              Run after add_product!!!'''

    def category_sql(self):
        with connections['legacy'].cursor() as cursor:
            cursor.execute('''
                    select 	
                    DISTINCT                
                        trim(CONVERT(v.key_name USING utf8)) as category,                        
                        trim(CONVERT(v.value_type USING utf8)) as value_type	
                    FROM product_metadata m 
                    JOIN product_key_value_metadata v on v.id = m.product_key_value_metadatum_id
                        
            ''')
            return namedtuplefetchall(cursor)

    def add_category_tag(self):
        objects = []
        for c in tqdm(self.category_sql(), desc='Add category tags'):
            categories = TagCategory.objects.filter(index_name=c.category)
            if not categories.exists():
                objects.append(
                    TagCategory(label={"en-GB": c.category},
                                value_type=c.value_type,
                                index_name=c.category,
                                public=True
                                )
                )
            else:
                categories.update(public=True)
        TagCategory.objects.bulk_create(objects)
        self.stdout.write(self.style.WARNING(f'Add or get tag category objects.'))

    def product_type_category_sql(self):
        with connections['legacy'].cursor() as cursor:
            cursor.execute('''
                        select 	
                            DISTINCT                         
                            trim(CONVERT(v.key_name USING utf8)) as tag_category                         
                        FROM product_metadata m 
                        join product_key_value_metadata v on v.id = m.product_key_value_metadatum_id   
                        join products p on p.id = m.product_id 
                        where UPPER(trim(p.type)) = 'WINE'                   
                ''')
            return namedtuplefetchall(cursor)

    def add_type_product_category(self):
        for c in tqdm(self.product_type_category_sql(), desc='Add type product category'):
            type = ProductType.objects.get(index_name=ProductType.WINE)
            category = TagCategory.objects.get(index_name=c.tag_category)
            if category not in type.tag_categories.all():
                type.tag_categories.add(category)

        self.stdout.write(self.style.WARNING(f'Add type product category objects.'))

    def tag_sql(self):
        with connections['legacy'].cursor() as cursor:
            cursor.execute('''                                            
                    select 	
                        DISTINCT                                                                         
                        trim(CONVERT(m.value USING utf8)) as tag_value, 
                        trim(CONVERT(v.key_name USING utf8)) as tag_category	
                    FROM product_metadata m 
                    join product_key_value_metadata v on v.id = m.product_key_value_metadatum_id
            ''')
            return namedtuplefetchall(cursor)

    def add_tag(self):
        objects = []
        for t in tqdm(self.tag_sql(), desc='Add tags'):
            category = TagCategory.objects.get(index_name=t.tag_category)

            tags = Tag.objects.filter(
                                     category=category,
                                     value=t.tag_value
                                    )

            if not tags.exists():
                objects.append(Tag(label={"en-GB": t.tag_value},
                                   category=category,
                                   value=t.tag_value)
                               )


        Tag.objects.bulk_create(objects)
        self.stdout.write(self.style.WARNING(f'Add or get tag objects.'))

    def remove_tags_product(self):
        print('Begin clear tags product')
        products = Product.objects.all()
        for p in tqdm(products, desc='Clear tags product'):
            p.tags.clear()
        print('End clear tags product')

    def remove_tags(self):
        print('Begin delete many tags')
        Tag.objects.\
            filter(news__isnull=True, establishments__isnull=True).delete()
        print('End delete many tags')

    def product_sql(self):
        with connections['legacy'].cursor() as cursor:
            cursor.execute('''
                    select 	
                    DISTINCT                        
                        m.product_id,
                        trim(CONVERT(m.value USING utf8)) as tag_value, 
                        trim(CONVERT(v.key_name USING utf8)) as tag_category	
                    FROM product_metadata m 
                    JOIN product_key_value_metadata v on v.id = m.product_key_value_metadatum_id
            ''')
            return namedtuplefetchall(cursor)

    def add_product_tag(self):
        for t in tqdm(self.product_sql(), desc='Add product tag'):
            category = TagCategory.objects.get(index_name=t.tag_category)

            tags = Tag.objects.filter(
                                     category=category,
                                     value=t.tag_value
                                    )
            product = Product.objects.get(old_id=t.product_id)
            for tag in tags:
                if tag not in product.tags.all():
                    product.tags.add(tag)

        self.stdout.write(self.style.WARNING(f'Add or get tag objects.'))

    def check_tag(self):
        tags = Tag.objects.filter(value__contains='_').all()
        for tag in tqdm(tags, desc='Check label for tag'):
            new_label = {}
            for k, v in tag.label.items():
                if isinstance(v, str) and '_' in v:
                    sp = v.split('_')
                    v = ' '.join([sp[0].capitalize()] + sp[1:])
                    new_label[k] = v
                    tag.label = new_label
            tag.save()

    def handle(self, *args, **kwargs):
        self.remove_tags_product()
        self.remove_tags()
        self.add_category_tag()
        self.add_type_product_category()
        self.add_tag()
        self.check_tag()
        self.add_product_tag()
