# -*- coding: utf-8 -*-
# Generated by Django 1.11.1 on 2018-11-30 13:40
from __future__ import unicode_literals

from django.db import migrations


def uniqueify_tags_table(apps, schema_editor):
    Tag = apps.get_model('dashboard', 'Tag')
    Post = apps.get_model('dashboard', 'Post')
    Comment = apps.get_model('dashboard', 'Comment')
    ShortList = apps.get_model('dashboard', 'ShortList')
    unique_tags = {}

    for tag in Tag.objects.all():
        lower_case_name = tag.name.lower()
        if lower_case_name not in unique_tags:
            # This tag is not (yet) a duplicate. Keep it for use
            # as the singular tag should a duplicate be found
            unique_tags[lower_case_name] = tag
        else:
            # Tag is a duplicate, replace it's usages with the singular tag
            # we are choosing to use instead.
            singular_tag = unique_tags[lower_case_name]

            # Replace all uses of the duplicate tag from posts
            posts_using_tag = Post.objects.filter(tags__pk=tag.id)
            for post in posts_using_tag:
                post.tags.remove(tag)
                if not post.tags.filter(pk=singular_tag.id).exists():
                    post.tags.add(singular_tag)
                date_modified = post.date_modified
                post.save()
                Post.objects.filter(pk=post.id).update(date_modified=date_modified)

            # Replace all uses of the duplicate tag from comments
            comments_using_tag = Comment.objects.filter(tags__pk=tag.id)
            for comment in comments_using_tag:
                comment.tags.remove(tag)
                if not comment.tags.filter(pk=singular_tag.id).exists():
                    comment.tags.add(singular_tag)
                date_modified = comment.date_modified
                comment.save()
                Comment.objects.filter(pk=comment.id).update(date_modified=date_modified)

            # Replace all uses of the duplicate tag from short lists
            short_lists_using_tag = ShortList.objects.filter(tags__pk=tag.id)
            for short_list in short_lists_using_tag:
                short_list.tags.remove(tag)
                if not short_list.tags.filter(pk=singular_tag.id).exists():
                    short_list.tags.add(singular_tag)
                date_modified = short_list.date_modified
                short_list.save()
                ShortList.objects.filter(pk=short_list.id).update(date_modified=date_modified)

    return unique_tags


def merge_user_defined_tags_into_the_tag_table(apps, schema_editor, unique_tags):
    Tag = apps.get_model('dashboard', 'Tag')
    UserDefinedTag = apps.get_model('dashboard', 'UserDefinedTag')
    Post = apps.get_model('dashboard', 'Post')
    Comment = apps.get_model('dashboard', 'Comment')
    ShortList = apps.get_model('dashboard', 'ShortList')

    for user_defined_tag in UserDefinedTag.objects.all():
        lower_case_name = user_defined_tag.name.lower()
        
        # Get or create the tag we need to use in place of this user_define_tag
        singular_tag = None
        if lower_case_name not in unique_tags:
            singular_tag = Tag()
            singular_tag.name = user_defined_tag.name
            singular_tag.date_added = user_defined_tag.date_added
            singular_tag.lft = 1
            singular_tag.rght = 2
            singular_tag.tree_id = 1
            singular_tag.level = 0
            singular_tag.parent_id = None
            singular_tag.save()
            unique_tags[lower_case_name] = singular_tag
        else:
            singular_tag = unique_tags[lower_case_name]
        
        # Replace all uses of the user_defined_tag from posts
        posts_using_user_defined_tag = Post.objects.filter(user_defined_tags__pk=user_defined_tag.id)
        for post in posts_using_user_defined_tag:
            post.user_defined_tags.remove(user_defined_tag)
            if not post.tags.filter(pk=singular_tag.id).exists():
                post.tags.add(singular_tag)
            date_modified = post.date_modified
            post.save()
            Post.objects.filter(pk=post.id).update(date_modified=date_modified)


        # Replace all uses of the user_defined_tag from comments
        comments_using_user_defined_tag = Comment.objects.filter(user_defined_tags__pk=user_defined_tag.id)
        for comment in comments_using_user_defined_tag:
            comment.user_defined_tags.remove(user_defined_tag)
            if not comment.tags.filter(pk=singular_tag.id).exists():
                comment.tags.add(singular_tag)
            date_modified = comment.date_modified
            comment.save()
            Comment.objects.filter(pk=comment.id).update(date_modified=date_modified)

        # Replace all uses of the user_defined_tag from short lists
        short_lists_using_user_defined_tag = ShortList.objects.filter(user_defined_tags__pk=user_defined_tag.id)
        for short_list in short_lists_using_user_defined_tag:
            short_list.user_defined_tags.remove(user_defined_tag)
            if not short_list.tags.filter(pk=singular_tag.id).exists():
                short_list.tags.add(singular_tag)
            date_modified = short_list.date_modified
            short_list.save()
            ShortList.objects.filter(pk=short_list.id).update(date_modified=date_modified)


def merge_tag_tables(apps, schema_editor):
    unique_tags = uniqueify_tags_table(apps, schema_editor)
    merge_user_defined_tags_into_the_tag_table(apps, schema_editor, unique_tags)


class Migration(migrations.Migration):

    dependencies = [
        ('dashboard', '0055_auto_20181029_1241')
    ]

    operations = [
        # Normalize all tag names to lower case
        migrations.RunSQL([
            ("UPDATE dashboard_tag SET name = TRIM(LOWER(name))"),
            ("UPDATE dashboard_userdefinedtag SET name = TRIM(LOWER(name))"),
            # Delete use of empty tags
            ("DELETE FROM dashboard_post_user_defined_tags WHERE id IN ( SELECT PUDT.id FROM dashboard_post_user_defined_tags AS PUDT LEFT JOIN dashboard_userdefinedtag AS UDT ON PUDT.userdefinedtag_id = UDT.id WHERE UDT.name='' )"),
            ("DELETE FROM dashboard_comment_user_defined_tags WHERE id IN ( SELECT PUDT.id FROM dashboard_comment_user_defined_tags AS PUDT LEFT JOIN dashboard_userdefinedtag AS UDT ON PUDT.userdefinedtag_id = UDT.id WHERE UDT.name='' )"),
            ("DELETE FROM dashboard_shortlist_user_defined_tags WHERE id IN ( SELECT PUDT.id FROM dashboard_shortlist_user_defined_tags AS PUDT LEFT JOIN dashboard_userdefinedtag AS UDT ON PUDT.userdefinedtag_id = UDT.id WHERE UDT.name='' )"),
            ("DELETE FROM dashboard_userdefinedtag WHERE name=''"),
            ("DELETE FROM dashboard_post_tags WHERE id IN ( SELECT PT.id FROM dashboard_post_tags AS PT LEFT JOIN dashboard_tag AS T ON PT.tag_id = T.id WHERE T.name='' )"),
            ("DELETE FROM dashboard_comment_tags WHERE id IN ( SELECT PT.id FROM dashboard_comment_tags AS PT LEFT JOIN dashboard_tag AS T ON PT.tag_id = T.id WHERE T.name='' )"),
            ("DELETE FROM dashboard_shortlist_tags WHERE id IN ( SELECT PT.id FROM dashboard_shortlist_tags AS PT LEFT JOIN dashboard_tag AS T ON PT.tag_id = T.id WHERE T.name='' )"),
            ("DELETE FROM dashboard_tag WHERE name=''")
        ]),

        # Remap used tags 
        migrations.RunPython(merge_tag_tables),

        #Delete duplicate tags
        migrations.RunSQL(
            # Remove hierarchy configurations (no longer used)
            [("UPDATE dashboard_tag SET parent_id = NULL, is_user_generated = FALSE, level = 0, lft = 0, rght = 0, tree_id = 0")]
        ),

        migrations.RunSQL(
            # This script finds all the unused duplicate tags in the system and deletes them
            [
                ("""
                    -- *********** SECTION 3 ***********
                    -- Delete the duplicate tags from the tags table
                    DELETE
                    FROM dashboard_tag
                    WHERE dashboard_tag.id IN
                    (
                        -- *********** SECTION 2 ***********
                        -- Reduce the duplicate tags back down to only those that are not used
                        SELECT tags.id
                        FROM dashboard_tag AS tags
                        JOIN 
                        (

                            -- *********** SECTION 1 ***********
                            -- Reduce the tags to only those that are duplicates matching by
                            -- name match. This will give us back some tags that are referenced
                            -- We'll remove them above
                            SELECT 
                                COUNT(name),
                                name
                            FROM dashboard_tag
                            GROUP BY(name)
                            HAVING COUNT(name) > 1
                            -- *********** END SECTION 1 ***********

                        ) AS tag_names_duplicated
                        ON tags.name = tag_names_duplicated.name
                        WHERE 
                            tags.id NOT IN(
                                SELECT tag_id FROM dashboard_post_tags
                            ) 
                            AND tags.id NOT IN(
                                SELECT tag_id FROM dashboard_comment_tags
                            )
                            AND tags.id NOT IN(
                                SELECT tag_id FROM dashboard_shortlist_tags
                            ) 
                        -- *********** END SECTION 2 ***********
                    )
                    -- *********** END SECTION 3 ***********
                """),
                
                ("DELETE FROM dashboard_userdefinedtag")
            ]
        )
    ]
