import math
import zipfile
from datetime import datetime
from distutils.util import strtobool
from io import BytesIO, StringIO
from typing import Iterable, List, OrderedDict
import boto3
import xlsxwriter
from django.conf import settings
from django.db.models import Q, Count
from django.core.mail import EmailMultiAlternatives
import json
from config import celery_app
from config.settings.base import DEFAULT_FROM_EMAIL, env
from verify_trusted.companies.api.serializers import CompanyExportExcelSerializer
from verify_trusted.companies.models import Company
from verify_trusted.users.api.params import (
    domain_param,
    first_letter_param,
    has_reviews_param,
    name_param,
    platform_param,
    rating_star_param,
    is_verified_param
)


class ExportUserIntoExcelTask(celery_app.Task):
    name = "ExportUserIntoExcelTask"

    def __init__(self, *args, **kwargs) -> None:
        super().__init__(*args, **kwargs)
        self.queryset = Company.objects.all()

    def validate_data_export(self, data):
        existed_email = []
        result = []
        for d in data:
            d['email'] = d['email'].strip()
            d['email'] = d['email'].split(' ')[0]
            d['email'] = d['email'].split(',')[0]
            if d['email'] not in existed_email and '@' in d['email']:
                result.append(d)
                existed_email.append(d['email'])

        return result


    def export(self, query_params):
        print(f"+++++++++++++++++++++++{query_params}")
        queryset = self.queryset
        name_filter = query_params.get(name_param.name, None)
        if name_filter:
            queryset = queryset.filter(search_vector=name_filter)
        
        status = query_params.get(is_verified_param.name, None)
        if status:
            is_verified = json.loads(status)
            print(f"+++++++++++++++++++++++kkkkkkk{is_verified}")
            queryset = queryset.filter(is_verified=is_verified)

        domain_filter = query_params.get(domain_param.name, None)
        if domain_filter:
            queryset = queryset.filter(url=domain_filter)

        platform_filter = query_params.get(platform_param.name, None)
        if platform_filter:
            queryset = queryset.filter(review_sources__platform_id__in=platform_filter.split(','))

        first_letter_filter = query_params.get(first_letter_param.name, None)
        if first_letter_filter:
            first_letter: str = first_letter_filter[:1]
            first_letter_query = Q(first_letter=first_letter.lower()) | Q(
                first_letter=first_letter.upper()
            )  # Do not uses istartswith
            queryset = Company.objects.only(
                'id',
                'name',
                'url',
                'email',
                'url_display',
                'address'
            ).filter(first_letter_query)

        has_reviews_filter = query_params.get(has_reviews_param.name, None)
        if has_reviews_filter:
            has_reviews_filter = bool(
                strtobool(has_reviews_filter)
            )
            if has_reviews_filter is True:
                queryset = queryset.filter(reviews_count__gt=0)
            else:
                queryset = queryset.filter(Q(reviews_count=0) | Q(reviews_count_isnull=True))

        rating_star_filter = query_params.get(rating_star_param.name, None)

        if rating_star_filter:
            if rating_star_filter == 0:
                queryset = queryset.filter(average_rating__lt=3)
            else:
                queryset = queryset.filter(average_rating__gte=3)
        # queryset = queryset.filter(email__isnull=False).exclude(email__exact='').distinct('email')
        queryset = queryset.filter(email__isnull=False)

        company: Iterable[CompanyExportExcelSerializer] = queryset
        data: List[OrderedDict] = CompanyExportExcelSerializer(company, many=True).data
        data = self.validate_data_export(data)
        headers = ['Id', 'Company Name', 'Email', 'Profile URL', 'Domain', 'Address']
        print(f"+++++++++++++++++++++++kkkkkkk{company}")

        # create our spreadsheet.  I will create it in memory with a StringIO
        zip_file = BytesIO()
        filename = 'Company Changelogs {}'.format(str(datetime.today().strftime('%Y-%m-%d %H-%M-%S')))

        record_len = len(data)
        each_record = 1
        if record_len > 50000:
            each_record = int(math.ceil(record_len / 50000))
        zf = zipfile.ZipFile(zip_file, mode='w', compression=zipfile.ZIP_DEFLATED)
        # with zipfile.ZipFile(zip_file, mode='w', compression=zipfile.ZIP_DEFLATED) as zf:
        for i in range(0, each_record):
            new_data = data[:50000]
            del data[:50000]
            new_filename = filename + '_%s' % i + '.xlsx'
            output = BytesIO()

            workbook = xlsxwriter.Workbook(output, options={'strings_to_urls': False})
            worksheet = workbook.add_worksheet()
            for i, field in enumerate(headers):
                worksheet.write(0, i, field)
            for r, company in enumerate(new_data, start=1):
                worksheet.write_row(
                    r,
                    0,
                    [
                        company['id'],
                        company['name'],
                        company['email'],
                        'https://www.verifytrusted.com/reviews/' + company['url'],
                        company['url_display'],
                        company['address'],
                    ],
                )
            workbook.close()

            zf.writestr(new_filename, data=output.getvalue())
        zf.close()
        zip_path = 'export_company/{}.zip'.format(filename.replace(" ", "_"))
        zip_file.seek(0)
        client = boto3.client('s3', aws_access_key_id=settings.AWS_ACCESS_KEY_ID,
                              aws_secret_access_key=settings.AWS_SECRET_ACCESS_KEY)
        client.upload_fileobj(zip_file, settings.AWS_STORAGE_BUCKET_NAME, zip_path)
        if settings.IS_PRODUCTION:
            send_to_email = ['admin@verifytrusted.com', 'doanthanh58863@gmail.com']
        else:
            send_to_email = settings.DEFAULT_SEND_TO_EMAIL
        link_to_download = 'https://%s/%s' % (settings.AWS_S3_CUSTOM_DOMAIN, zip_path)
        msg = EmailMultiAlternatives(
            # title:
            'Export company',
            # message:
            'Export company successful \n Click link to download the file: %s' % link_to_download,
            # from:
            DEFAULT_FROM_EMAIL,
            # to:
            send_to_email,
            bcc=['verifytrusted.support@sphinxjsc.com'],
        )
        # msg.attach('{}.zip'.format(filename), zip_file.getvalue(), 'application/vnd.ms-excel')
        msg.send()