from odoo import models, fields, api, _
from datetime import datetime
import base64
import xlrd
from odoo.exceptions import ValidationError


class UploadCancelPicking(models.Model):
    _name = "upload.cancel.picking"
    _description = "Upload Cancel Picking"
    _order = "create_date desc"
    _rec_name = "number"

    picking_lines = fields.One2many(
        'upload.cancel.picking.line',
        'upload_cancel_picking_id',
        string='Lines',
        copy=False
    )
    number = fields.Char('Number', copy=False)
    date_upload = fields.Datetime('Cancel Date', copy=False)
    user_id = fields.Many2one(
        'res.users',
        'Created By',
        default=lambda self: self.env.user
    )
    excel_file = fields.Binary('Excel File', attachment=True)
    filename = fields.Char('File Name')

    @api.model
    def create(self, vals):
        vals['number'] = self.env['ir.sequence'].next_by_code(
            'upload.cancel.picking'
        ) or '/'
        return super().create(vals)

    def action_import_excel(self):
        self.ensure_one()

        if not self.excel_file:
            raise ValidationError(_("Please upload an Excel file first."))

        # === Load Excel ===
        try:
            file_content = base64.b64decode(self.excel_file)
            workbook = xlrd.open_workbook(file_contents=file_content)
            sheet = workbook.sheet_by_index(0)
        except Exception:
            raise ValidationError(_("Invalid Excel file format."))

        # === Validate Header ===
        header = [
            str(sheet.cell(0, col).value).strip().lower()
            for col in range(sheet.ncols)
        ]

        if 'invoice' not in header:
            raise ValidationError(
                _("Invalid Excel format. Expected column: Invoice")
            )

        invoice_col = header.index('invoice')

        # === Read Rows ===
        rows_data = []
        for row_idx in range(1, sheet.nrows):
            invoice_marketplace = str(
                sheet.cell(row_idx, invoice_col).value
            ).strip()

            if not invoice_marketplace:
                raise ValidationError(
                    _("Invoice kosong di baris Excel %s") % (row_idx + 1)
                )

            rows_data.append((row_idx + 1, invoice_marketplace))

        if not rows_data:
            raise ValidationError(_("Excel tidak berisi data."))

        # === Validate Duplicate in Excel ===
        seen = set()
        duplicate_excel_rows = []

        for row_num, invoice_marketplace in rows_data:
            if invoice_marketplace in seen:
                duplicate_excel_rows.append(str(row_num))
            seen.add(invoice_marketplace)

        if duplicate_excel_rows:
            raise ValidationError(
                _("Duplicate Invoice di file Excel pada baris: %s")
                % ", ".join(duplicate_excel_rows)
            )

        # === Validate Duplicate in System ===
        invoice_to_check = [inv for _, inv in rows_data]
        existing_invoices = set()

        chunk_size = 500
        for i in range(0, len(invoice_to_check), chunk_size):
            chunk = invoice_to_check[i:i + chunk_size]
            records = self.env['upload.cancel.picking.line'].search([
                ('invoice_marketplace', 'in', chunk)
            ])
            existing_invoices.update(records.mapped('invoice_marketplace'))

        duplicate_system_rows = []
        for row_num, invoice_marketplace in rows_data:
            if invoice_marketplace in existing_invoices:
                duplicate_system_rows.append(str(row_num))

        if duplicate_system_rows:
            raise ValidationError(
                _("Invoice Marketplace sudah ada di sistem. "
                  "Ditemukan di baris: %s")
                % ", ".join(duplicate_system_rows)
            )

        # === Create Lines ===
        line_vals = [
            (0, 0, {
                'invoice_marketplace': invoice_marketplace,
                'upload_cancel_picking_id': self.id,
            })
            for _, invoice_marketplace in rows_data
        ]

        # Clear old lines (safe way)
        self.picking_lines = [(5, 0, 0)]
        self.write({'picking_lines': line_vals})
        self.picking_lines.get_order_id()

        return {
            'type': 'ir.actions.client',
            'tag': 'display_notification',
            'params': {
                'title': _('Success'),
                'message': _('Imported %s lines from Excel.')
                           % len(line_vals),
                'sticky': False,
                'next': {'type': 'ir.actions.act_window_close'},
            }
        }

    def action_cancel_picking(self):
        self.date_upload = datetime.utcnow()
        for line in self.picking_lines:
            queue_job = self.env['queue.job'].search([('res_id', '=', line.id), ('method_name', '=', 'cancel_picking'), ('state', '!=', 'error')], limit=1)
            if queue_job:
                continue
            self.env['queue.job'].create({
                'name': f'Cancel Picking {line.picking_id.name}',
                'model_name': 'upload.cancel.picking.line',
                'method_name': 'cancel_picking',
                'res_id': line.id,
            })


class UploadCancelPickingLine(models.Model):
    _name = "upload.cancel.picking.line"
    _description = "Upload Cancel Picking Line"
    _inherit = ['mail.thread']

    upload_cancel_picking_id = fields.Many2one(
        'upload.cancel.picking',
        string='Upload'
    )
    invoice_marketplace = fields.Char(
        'Invoice Marketplace',
        required=True
    )
    picking_id = fields.Many2one(
        'stock.picking',
        'Picking Reference'
    )
    status_picking = fields.Selection([
        ('done', 'Done'),
        ('cancel', 'Cancel'),
        ('assigned', 'Ready'),
        ('confirmed', 'Waiting'),
        ('waiting', 'Waiting Another Operation'),
    ], related='picking_id.state')
    message_error = fields.Text('Error Message')
    is_grouped = fields.Boolean('Is Grouped', default=False)
    group_key = fields.Char('Group Key')


    def get_order_id(self):
        StockPicking = self.env['stock.picking']

        invoices = self.mapped('invoice_marketplace')

        if not invoices:
            return

        # Ambil semua picking yang matching invoice_mp
        pickings = StockPicking.search([
            ('invoice_mp', 'in', invoices)
        ])

        picking_map = {
            p.invoice_mp: p.id
            for p in pickings
            if p.invoice_mp
        }

        for line in self:
            picking_id = picking_map.get(line.invoice_marketplace)

            if picking_id:
                line.picking_id = picking_id
                line.message_error = False
            else:
                line.picking_id = False
                line.message_error = _(
                    "Stock Picking tidak ditemukan untuk invoice %s"
                ) % line.invoice_marketplace

    def cancel_picking(self):
        self.picking_id.action_cancel()