import base64
import io
from datetime import timedelta

from odoo import _, fields, models
from odoo.exceptions import UserError

try:
    import xlsxwriter
except ImportError:  # pragma: no cover
    xlsxwriter = None


class MailQuickTemplateExportWizard(models.TransientModel):
    _name = 'mail.quick.template.export.wizard'
    _description = 'Chatter Quick Reply Template Usage Export'

    date_from = fields.Date(string='From')
    date_to = fields.Date(string='To', default=fields.Date.context_today)
    category_ids = fields.Many2many(
        'mail.quick.template.category',
        relation='mqt_export_wizard_category_rel',
        column1='wizard_id', column2='category_id', string='Categories',
    )
    company_ids = fields.Many2many(
        'res.company',
        relation='mqt_export_wizard_company_rel',
        column1='wizard_id', column2='company_id', string='Companies',
        default=lambda self: self.env.company,
    )
    file_data = fields.Binary(string='File', readonly=True, attachment=False)
    file_name = fields.Char(string='File Name', readonly=True)

    def _get_templates(self):
        domain = []
        if self.category_ids:
            domain.append(('category_id', 'in', self.category_ids.ids))
        if self.company_ids:
            domain.append(('company_id', 'in', self.company_ids.ids))
        return self.env['mail.quick.template'].with_context(
            active_test=False
        ).search(domain, order='category_id, name')

    def _count_logs(self, template, date_from=None, date_to=None):
        Log = self.env['mail.quick.template.log']
        domain = [('template_id', '=', template.id)]
        if date_from:
            domain.append(('used_date', '>=', fields.Datetime.to_datetime(date_from)))
        if date_to:
            end = fields.Datetime.to_datetime(date_to) + timedelta(days=1)
            domain.append(('used_date', '<', end))
        return Log.search_count(domain)

    def _top_user(self, template):
        Log = self.env['mail.quick.template.log']
        groups = Log._read_group(
            [('template_id', '=', template.id), ('user_id', '!=', False)],
            groupby=['user_id'], aggregates=['__count'],
            order='__count desc', limit=1,
        )
        return groups[0][0].name if groups else _('N/A')

    def action_export(self):
        self.ensure_one()
        if xlsxwriter is None:
            raise UserError(_(
                "The 'xlsxwriter' Python library is required to export to "
                "Excel but is not available in this Odoo environment."
            ))
        templates = self._get_templates()
        today = fields.Date.context_today(self)
        d30 = today - timedelta(days=30)
        d90 = today - timedelta(days=90)

        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output, {'in_memory': True})
        sheet = workbook.add_worksheet(_('Template Usage'))
        bold = workbook.add_format({'bold': True, 'bg_color': '#D9E1F2'})
        date_fmt = workbook.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'})

        headers = [
            _('Template Name'), _('Category'), _('Applicable Model'),
            _('Total Uses (all time)'), _('Uses (Last 30 days)'),
            _('Uses (Last 90 days)'), _('Last Used Date'),
            _('Most Active User'), _('Company'),
        ]
        for col, header in enumerate(headers):
            sheet.write(0, col, header, bold)
        widths = [30, 20, 24, 16, 18, 18, 20, 22, 20]
        for col, width in enumerate(widths):
            sheet.set_column(col, col, width)

        row = 1
        for tmpl in templates:
            sheet.write(row, 0, tmpl.name or '')
            sheet.write(row, 1, tmpl.category_id.name or '')
            sheet.write(row, 2, tmpl.model_id.name or _('Global'))
            sheet.write(row, 3, tmpl.use_count)
            sheet.write(row, 4, self._count_logs(tmpl, d30, today))
            sheet.write(row, 5, self._count_logs(tmpl, d90, today))
            if tmpl.last_used_date:
                sheet.write_datetime(row, 6, tmpl.last_used_date, date_fmt)
            else:
                sheet.write(row, 6, _('Never'))
            sheet.write(row, 7, self._top_user(tmpl))
            sheet.write(row, 8, tmpl.company_id.name or '')
            row += 1

        workbook.close()
        output.seek(0)
        self.file_data = base64.b64encode(output.read())
        self.file_name = 'template_usage_%s.xlsx' % today.strftime('%Y%m%d')

        return {
            'type': 'ir.actions.act_url',
            'url': '/web/content/?model=%s&id=%s&field=file_data&filename_field=file_name&download=true' % (
                self._name, self.id,
            ),
            'target': 'self',
        }
