import os

import environ
from django.db.models import Q

from communicator.models import CommunicatorAuthentication
from django.core.management.base import BaseCommand
from django.db import transaction

import MySQLdb

from customers.models import EnrollWizard
from freeswitch.models import Channels
from kamailio.models import Location
from provisioning.models import Dss, ProvisioningAccount, ProvisioningDevice, ProvisioningMultiCellDevice, Repeater
from rest_framework.authtoken.models import Token
from voip_platform.models import (
    Account,
    CalendarPreset,
    CallFlow,
    ConferenceRoom,
    DidRouting,
    Extension,
    ExtensionGroup,
    ExtensionGroupMember,
    Fax,
    FifoQueue,
    FifoQueueChime,
    FifoQueueMember,
    Group,
    MediaFile,
    Phonebook,
    PhonebookContact,
    RateCallBundle,
    RateDiscount,
    RingGroup,
    RingGroupMember,
    Site,
    VoicemailBox,
    VoicemailMessage,
    WakeupCall,
    DidRoutingHistory,
)

from telsy_v3.users.models import User, UserProfile

env = environ.Env()
env.read_env(".env")


class Command(BaseCommand):
    help = "Clear all redundant data in DB."

    KAMAILIO = "kamailio"
    FREESWITCH = "freeswitch"

    kamailio_exclude_tables = ["location"]
    freeswitch_exclude_tables = ["channels"]

    @transaction.atomic
    def handle(self, *args, **options):
        if "production" not in os.environ.get("DJANGO_SETTINGS_MODULE"):
            telsmart_group_ids = [583, 585, 587]
            platform_domains = ["g583.ctx.phoneserver.io", "g585.ctx.phoneserver.io", "g587.ctx.phoneserver.io"]
            telsmarts = Group.objects.filter(pk__in=telsmart_group_ids)
            account_ids = [4, 235]
            did_routings = DidRouting.objects.exclude(account_id__in=account_ids, group__in=telsmarts)
            did_histories = DidRoutingHistory.objects.filter(did__in=did_routings)
            did_histories.delete()
            did_routings.delete()
            call_flows = CallFlow.objects.exclude(group__in=telsmarts)
            call_flows.delete()
            conference_rooms = ConferenceRoom.objects.exclude(group__in=telsmarts)
            conference_rooms.delete()

            extensions = Extension.objects.exclude(group__in=telsmarts)
            media_files = MediaFile.objects.filter(
                recorded_by_extension__in=list(extensions.values_list("id", flat=True))
            )
            wakeup_calls = WakeupCall.objects.filter(extension__in=list(extensions.values_list("id", flat=True)))
            wakeup_calls.delete()
            fifo_queue_chimes = FifoQueueChime.objects.filter(
                media_file__in=list(media_files.values_list("id", flat=True))
            )
            fifo_queue_chimes.delete()
            media_files.delete()

            ring_groups = RingGroup.objects.exclude(group__in=telsmarts)
            phonebooks = Phonebook.objects.exclude(group__in=telsmarts)
            contacts = PhonebookContact.objects.filter(phonebook__in=list(phonebooks.values_list("id", flat=True)))
            fifo_queue_members = FifoQueueMember.objects.filter(
                extension__in=list(extensions.values_list("id", flat=True))
            )
            fifo_queue_members.delete()
            fifo_queue_members = FifoQueueMember.objects.filter(contact__in=list(contacts.values_list("id", flat=True)))
            fifo_queue_members.delete()
            fifo_queue_members = FifoQueueMember.objects.filter(
                ring_group__in=list(ring_groups.values_list("id", flat=True))
            )
            fifo_queues = FifoQueue.objects.exclude(group__in=telsmarts)
            fifo_queue_members = FifoQueueMember.objects.filter(
                queue__in=list(fifo_queues.values_list("id", flat=True))
            )
            fifo_queue_members.delete()
            fifo_queue_chimes = FifoQueueChime.objects.filter(
                fifo_queue__in=list(fifo_queues.values_list("id", flat=True))
            )
            fifo_queue_chimes.delete()
            fifo_queues.delete()

            ring_group_members = RingGroupMember.objects.filter(
                extension__in=list(extensions.values_list("id", flat=True))
            )
            ring_group_members.delete()
            ring_groups.delete()
            contacts.delete()
            provisioning_accounts = ProvisioningAccount.objects.filter(
                extension__in=list(extensions.values_list("id", flat=True))
            )
            provisioning_devices = ProvisioningDevice.objects.filter(
                phonebook__in=list(phonebooks.values_list("id", flat=True))
            )
            provisioning_dss = Dss.objects.filter(device__in=list(provisioning_devices.values_list("id", flat=True)))
            provisioning_repeaters = Repeater.objects.filter(
                device__in=list(provisioning_devices.values_list("id", flat=True))
            )
            provisioning_repeaters.delete()
            provisioning_dss.delete()
            provisioning_accounts.delete()
            multicells = ProvisioningMultiCellDevice.objects.filter(
                device__in=list(provisioning_devices.values_list("id", flat=True))
            )
            multicells.delete()
            provisioning_devices.delete()
            phonebooks.delete()
            extension_group_members = ExtensionGroupMember.objects.filter(
                extension__in=list(extensions.values_list("id", flat=True))
            )
            extension_group_members.delete()
            extension_groups = ExtensionGroup.objects.exclude(group__in=telsmarts)
            extension_groups.delete()

            user_ids = list(UserProfile.objects.filter(group_id__in=telsmarts).values_list("user_id", flat=True))

            # delete all enroll wizard data since it's only customer data
            EnrollWizard.objects.all().delete()

            telsy_commauths = CommunicatorAuthentication.objects.exclude(user_id__in=user_ids)
            telsy_commauths.delete()

            tokens = Token.objects.exclude(user_id__in=user_ids)
            tokens.delete()
            UserProfile.objects.exclude(user_id__in=user_ids).delete()
            User.objects.exclude(pk__in=user_ids).delete()

            extensions.delete()

            faxes = Fax.objects.exclude(group__in=telsmarts)
            faxes.delete()
            accounts = Account.objects.exclude(pk__in=account_ids)
            rate_discounts = RateDiscount.objects.filter(account__in=list(accounts.values_list("id", flat=True)))
            rate_discounts.delete()
            rate_call_bundles = RateCallBundle.objects.filter(account__in=list(accounts.values_list("id", flat=True)))
            rate_call_bundles.delete()
            accounts.delete()
            vm_boxes = VoicemailBox.objects.exclude(group__in=telsmarts)
            devices = ProvisioningDevice.objects.filter(voicemail_box__in=list(vm_boxes.values_list("id", flat=True)))
            devices.update(voicemail_box=None)
            VoicemailMessage.objects.exclude(voicemail_box__group__in=telsmarts).delete()
            vm_boxes.delete()

            groups = Group.objects.exclude(id__in=telsmart_group_ids)
            calendar_presets = CalendarPreset.objects.filter(group__in=list(groups.values_list("id", flat=True)))
            calendar_presets.delete()
            media_files = MediaFile.objects.exclude(group__in=telsmarts)
            media_files.delete()

            sites = Site.objects.exclude(group__in=telsmarts)
            sites.delete()
            groups.delete()

            locations = Location.objects.using(self.KAMAILIO).exclude(domain__in=platform_domains)
            locations.delete()

            channels = Channels.objects.using(self.FREESWITCH).exclude(
                Q(name__contains=platform_domains[0]) | Q(name__contains=platform_domains[1])
            )
            channels.delete()

            self.delete_useless_tables(exclude_tables=self.kamailio_exclude_tables, database=self.KAMAILIO)
            self.delete_useless_tables(exclude_tables=self.freeswitch_exclude_tables, database=self.FREESWITCH)

            self.delete_useless_views(database=self.FREESWITCH)

            self.stdout.write(self.style.SUCCESS("Done"))

    def delete_useless_tables(self, exclude_tables=[], database=""):
        tables = []
        cnx = MySQLdb.connect(
            user=env("LOCAL_DB_USER"), password=env("LOCAL_DB_PASSWORD"), host=env("LOCAL_DB_HOST"), database=database
        )
        cursor = cnx.cursor()
        query = "SHOW TABLES;"
        cursor.execute(query)
        for t in cursor:
            table_name = ",".join(t)

            if table_name not in exclude_tables:
                tables.append(table_name)

        drop_list = ", ".join(tables)
        if drop_list:
            drop_query = f"DROP TABLE IF EXISTS {drop_list};"
            cursor.execute(drop_query)

        cursor.close()
        cnx.close()

    def delete_useless_views(self, exclude_views=[], database=""):
        views = []
        cnx = MySQLdb.connect(
            user=env("LOCAL_DB_USER"), password=env("LOCAL_DB_PASSWORD"), host=env("LOCAL_DB_HOST"), database=database
        )
        cursor = cnx.cursor()
        query = f"SELECT TABLE_NAME FROM information_schema.VIEWS WHERE TABLE_SCHEMA LIKE '{database}';"
        cursor.execute(query)
        for t in cursor:
            view_name = ",".join(t)

            if view_name not in exclude_views:
                views.append(view_name)

        drop_list = ", ".join(views)
        if drop_list:
            drop_query = f"DROP VIEW IF EXISTS {drop_list};"
            cursor.execute(drop_query)

        cursor.close()
        cnx.close()