from odoo import models, fields, api, _
from odoo.exceptions import UserError
from datetime import date, datetime, timedelta
import requests
import logging
import pytz
from pytz import timezone
import base64
import xlrd, xlwt
import io
from collections import defaultdict

_logger = logging.getLogger(__name__)


class SourcingJobOrder(models.Model):
    _name = 'sourcing.job.order'
    _description = 'Sourcing Job Order MD'
    _rec_name = 'name'
    _inherit = ['mail.thread', 'mail.activity.mixin']
    _order = 'is_priority desc, state asc, create_date desc'

    name = fields.Char(string='Job Number', default='New', copy=False, readonly=True)
    leads_id = fields.Many2one('crm.lead', string='Leads Number')
    md_user_id = fields.Many2many('res.users', string='MD Persons', compute="_compute_md_persons")
    so_id = fields.Many2one('sale.order', string='SO Number', tracking=True)
    state = fields.Selection([
        ('draft', 'Untaken'),
        ('taken', 'On Sourcing'),
        ('partial', 'Partial Complete'),
        ('done', 'Complete'),
        ('cancel', 'Cancelled')
    ], string='Status', default='draft', tracking=True)
    approval_sales = fields.Selection([
        ('draft', 'Requested'),
        ('approve', 'Approved'),
        ('reject', 'Rejected'),
    ], string='Approval Sales', tracking=True)
    takeover_request = fields.Many2one(
        'res.users',
        string='Takeover Requested By',
        readonly=True,
        tracking=True,
        help='MD yang meminta takeover'
    )
    is_priority = fields.Boolean(
        string="Priority",
        default=False,
        tracking=True,
        help="Otomatis aktif jika request approval ditolak oleh sales."
    )
    eta_sales = fields.Date(string='Expected Ready')
    eta_complete = fields.Date(string='Completed Date')
    cancel_reason = fields.Text(string="Reason for Cancel", tracking=True)
    line_ids = fields.One2many('sourcing.job.order.line', 'order_id', string='Products')
    line_sales_input_ids = fields.One2many(
        'sourcing.job.order.line', 'order_id',
        string='Sales Input Lines',
    )
    line_sales_view_ids = fields.One2many(
        'sourcing.job.order.line', 'order_id',
        string='Sales View Lines',
    )
    converted_product_ids = fields.One2many(
        "product.product",
        "sourcing_job_id",
        string="Converted Products",
        readonly=True,
    )
    converted_product_count = fields.Integer(
        compute="_compute_converted_product_count",
        string="Converted Product Count",
    )
    progress_status = fields.Char(
        string='Progress Status',
        compute='_compute_progress_status',
        default=''
    )

    has_price_in_lines = fields.Boolean(
        string='Has Line with Price',
        compute='_compute_has_price_in_lines',
    )

    def _get_jakarta_today(self):
        jakarta_tz = pytz.timezone('Asia/Jakarta')
        now_jakarta = datetime.now(jakarta_tz)
        return now_jakarta.date()

    @api.depends('eta_sales', 'eta_complete', 'create_date', 'state')
    def _compute_progress_status(self):
        for rec in self:

            if rec.state == 'cancel':
                rec.progress_status = '⚫ Cancelled'
                continue

            if rec.state == 'done':
                if rec.eta_sales and rec.eta_complete:
                    delta = (rec.eta_complete - rec.eta_sales).days
                    if delta < 0:
                        rec.progress_status = f'🟢 Early {abs(delta)} hari'
                    elif delta == 0:
                        rec.progress_status = '🔵 Ontime'
                    else:
                        rec.progress_status = f'🔴 Delay {delta} hari'
                elif rec.create_date and rec.eta_complete:
                    durasi = (rec.eta_complete - rec.create_date.date()).days
                    rec.progress_status = f'✅ Selesai dalam {durasi} hari'
                else:
                    rec.progress_status = '✅ Selesai'
                continue

            if rec.state in ['taken', 'partial', 'draft']:
                rec.progress_status = '🟡 On Track'

    @api.depends('line_ids.price', 'line_ids.vendor_id')
    def _compute_has_price_in_lines(self):
        for rec in self:
            # Cek apakah ada minimal satu line yang sudah punya price > 0 dan vendor_id
            has_price = any(
                (line.price and line.price > 0 and line.vendor_id)
                for line in rec.line_ids
            )
            rec.has_price_in_lines = bool(has_price)

    @api.depends('line_ids.md_person_ids')
    def _compute_md_persons(self):
        for rec in self:
            md_users = rec.line_ids.mapped('md_person_ids').filtered(lambda x: x)
            rec.md_user_id = [(6, 0, md_users.ids)]

    @api.depends("converted_product_ids")
    def _compute_converted_product_count(self):
        for rec in self:
            rec.converted_product_count = len(rec.converted_product_ids)

    @api.model
    def create(self, vals):
        """Hanya Sales & Merchandiser yang boleh membuat job."""
        if not (self.env.user.has_group('indoteknik_custom.group_role_sales') or
                self.env.user.has_group('indoteknik_custom.group_role_merchandiser')):
            raise UserError("❌ Hanya Sales dan Merchandiser yang boleh membuat Sourcing Job.")

        if vals.get('name', 'New') == 'New':
            vals['name'] = self.env['ir.sequence'].next_by_code('sourcing.job.order') or 'New'

        rec = super().create(vals)

        return rec
        

    def write(self, vals):
        if self.env.uid != self.create_uid.id and not self.env.user.has_group('indoteknik_custom.group_role_merchandiser'):
            raise UserError("❌ Hanya Sales dan Merchandiser yang boleh mengedit Sourcing Job.")

        old_data = {}
        for rec in self:
            old_data[rec.id] = {
                'state': rec.state,
                'approval_sales': rec.approval_sales,
                'line_data': {
                    line.id: {
                        'state': line.state,
                        'vendor_id': line.vendor_id.id if line.vendor_id else False,
                        'price': line.price,
                    }
                    for line in rec.line_ids
                },
            }

        res = super().write(vals)
        if vals.get('product_assets'):
            for rec in self:
                rec._log_product_assets_upload()

        for rec in self:
            changes = []
            old = old_data.get(rec.id, {})

            if old.get('state') != rec.state:
                changes.append(f"State: <b>{old.get('state')}</b> → <b>{rec.state}</b>")
            if old.get('approval_sales') != rec.approval_sales:
                changes.append(f"Approval Status: <b>{old.get('approval_sales')}</b> → <b>{rec.approval_sales}</b>")

            old_lines = old.get('line_data', {})
            for line in rec.line_ids:
                old_line = old_lines.get(line.id)
                if not old_line:
                    continue  

                if (
                    old_line['vendor_id'] != (line.vendor_id.id if line.vendor_id else False)
                    and old_line['price'] == line.price
                ):
                    raise UserError(
                        f"⚠️ Harga untuk produk {line.product_name} belum diperbarui setelah mengganti Vendor."
                    )

                sub_changes = []

                if old_line['state'] != line.state:
                    sub_changes.append(f"- state: <b>{old_line['state']}</b> → <b>{line.state}</b>")

                if old_line['vendor_id'] != (line.vendor_id.id if line.vendor_id else False):
                    old_vendor = self.env['res.partner'].browse(old_line['vendor_id']).name if old_line['vendor_id'] else '-'
                    sub_changes.append(f"- vendor: <b>{old_vendor}</b> → <b>{line.vendor_id.name if line.vendor_id else '-'}</b>")

                if old_line['price'] != line.price:
                    sub_changes.append(f"- price: <b>{old_line['price']}</b> → <b>{line.price}</b>")

                if sub_changes:
                    joined = "<br/>".join(sub_changes)
                    changes.append(f"<b>{line.product_name}</b>:<br/>{joined}")

            if changes:
                message = "<br/><br/>".join(changes)
                rec.message_post(
                    body=f"Perubahan pada Sourcing Job:<br/>{message}",
                    subtype_xmlid="mail.mt_comment",
                )

        return res

    def action_cancel(self):
        for rec in self:
            if not self.env.user.has_group('indoteknik_custom.group_role_sales'):
                raise UserError("❌ Hanya Sales yang dapat mengcancel Sourcing Job.")

            if not rec.cancel_reason:
                raise UserError("⚠️ Isi alasan pembatalan terlebih dahulu.")

            rec.write({'state': 'cancel'})
            rec.message_post(body=("Job <b>%s</b> dibatalkan oleh %s<br/>Alasan: %s") %
                                  (rec.name, self.env.user.name, rec.cancel_reason))

    def action_open_converted_products(self):
        """Open converted products related to this SJO."""
        self.ensure_one()
        return {
            'name': 'Converted Products',
            'type': 'ir.actions.act_window',
            'view_mode': 'tree,form',
            'res_model': 'product.product',
            'domain': [('id', 'in', self.converted_product_ids.ids)],
            'context': {'default_sourcing_job_id': self.id},
        }

    def action_open_export_wizard(self):
        self.ensure_one()

        return {
            'type': 'ir.actions.act_window',
            'name': 'Export Produk ke SO',
            'res_model': 'wizard.export.sjo.to.so',
            'view_mode': 'form',
            'target': 'new',
            'context': {
                'default_sjo_id': self.id,
            }
        }

    def action_send_untaken_to_telegram(self):
        bot_sjo = '8335015210:AAGbObP0jQf7ptyqJhYdBYn5Rm0CWOd_yIM'
        chat_group_sjo = '-5081839952'
        # chat_group_sjo = '-5147961921' 
        api_base = f'https://api.telegram.org/bot{bot_sjo}'

        lines = self.env['sourcing.job.order.line'].search(
            [('state', '=', 'draft')],
            order='create_date asc'
        )

        if not lines:
            text = "✅ Tidak ada Sourcing Job Line yang berstatus Untaken saat ini."
        else:
            text = "⚠️ *Daftar SJO Line yang masih Untaken:*\n\n"

            line_counter = defaultdict(int)

            for line in lines:
                sjo_id = line.order_id.id
                line_counter[sjo_id] += 1

                sjo_number = line.order_id.name if line.order_id else '-'
                line_no = line_no = line_counter[sjo_id]
                product_name = line.product_name or '-'
                salesperson = line.show_salesperson.user_id.name if line.show_salesperson.user_id else '-'

                text += f"{sjo_number} | Line {line_no} | {product_name} | {salesperson}\n"

        payload = {
            'chat_id': chat_group_sjo,
            'text': text,
            'parse_mode': 'Markdown'
        }

        try:
            response = requests.post(f"{api_base}/sendMessage", data=payload, timeout=20)
            if response.status_code == 200:
                _logger.info("✅ Telegram notification sent successfully")
            else:
                _logger.error(f"❌ Failed to send Telegram message: {response.text}")
        except Exception as e:
            _logger.error(f"⚠️ Error while sending Telegram message: {str(e)}")

        return True


class SourcingJobOrderLine(models.Model):
    _name = 'sourcing.job.order.line'
    _description = 'Sourcing Job Order Line'
    _inherit = ['mail.thread', 'mail.activity.mixin']

    order_id = fields.Many2one('sourcing.job.order', string='Job Order', ondelete='cascade')
    product_id = fields.Many2one('product.product', string='Product', ondelete='cascade')
    md_person_ids = fields.Many2one('res.users', string='MD Person', ondelete='cascade')
    brand_id = fields.Many2one('x_manufactures', string='Manufactures', ondelete='cascade')
    so_id = fields.Many2one('sale.order', string='SO Number', tracking=True)
    product_name_md = fields.Char(string='Nama Barang')
    descriptions_md = fields.Text(string='Deskripsi Barang')

    product_name = fields.Char(string='Nama Barang')
    brand = fields.Char(string='Brand')
    code = fields.Char(string='SKU')
    budget = fields.Char(string='Expected Price')
    note = fields.Text(string='Note Sourcing')
    attachment_type = fields.Selection([
        ('none', 'None'),
        ('pdf', '.PDF'),
        ('img', '.IMG'),
        ('other', 'Lainnya'),
    ], default='none') 
    product_attachment_pdf = fields.Binary(string="Product Attachment")
    product_attachment_img = fields.Binary(string="Product Attachment")
    product_attachment_other = fields.Binary(string="Product Attachment")
    product_attachment_filename = fields.Char(string="Filename")

    descriptions = fields.Text(string='Deskripsi / Spesifikasi')
    reason = fields.Text(string='Reason Unavailable')
    sla = fields.Char(string='SLA Product')
    quantity = fields.Float(string='Quantity Product', required=True, default=1)
    price = fields.Float(string='Purchase Price')
    now_price = fields.Float(string='Current Purchase Price', readonly=True)
    last_updated_price = fields.Datetime(string='Last Update Price', readonly=True)
    tax_id = fields.Many2one('account.tax', string='Tax', domain=[('active', '=', True), ('type_tax_use', '=', 'purchase')])
    vendor_id = fields.Many2one('res.partner', string="Vendor")
    uom_id = fields.Many2one('uom.uom', string="Unit of Measure")
    web_tax_id = fields.Many2one('account.tax', string="Website Tax")
    product_category = fields.Many2one('product.category', string="Product Category")
    product_class = fields.Many2many('product.public.category', string="Categories")
    exported_to_so = fields.Boolean(string="Exported to SO", default=False)
    state = fields.Selection([
        ('draft', 'Unsource'),
        ('sourcing', 'On Sourcing'),
        ('sent', 'Approval Sent'),
        ('approve', 'Done Sourcing'),
        ('cancel', 'Unavailable')
    ], default='draft', tracking=True)
    product_type = fields.Selection([
        ('consu', 'Consumable'),
        ('servis', 'Service'),
        ('product', 'Storable Product'),
    ], default='product')
    subtotal = fields.Float(string='Subtotal', compute='_compute_subtotal')
    show_for_sales = fields.Boolean(
        string="Show for Sales",
        compute="_compute_show_for_sales",
    )
    show_salesperson = fields.Many2one(
        'res.users',
        string="Salesperson",
    )

    so_state = fields.Selection(
        [
            ('draft', 'Quotation'),
            ('cancel', 'Cancel'),
            ('sale', 'Sale Order')
        ],
        string="Status SO",
        compute="_compute_so_data"
    )

    so_name = fields.Char(
        string="SO Number",
        compute="_compute_so_data"
    )
    is_md_person = fields.Boolean(
        string="Is MD Person",
        compute="_compute_is_md_person"
    )
    is_receiver = fields.Boolean(
        string="Is MD Receiver",
        compute="_compute_is_md_person"
    )

    is_given = fields.Boolean(string='Is Given', tracking=True)
    given_to_id = fields.Many2one('res.users', string='Given To')
    previous_md_id = fields.Many2one('res.users', string='Previous MD')

    @api.depends('quantity', 'price', 'tax_id')
    def _compute_subtotal(self):
        for line in self:
            subtotal = (line.quantity or 0.0) * (line.price or 0.0)

            if line.tax_id:
                tax = line.tax_id.amount / 100

                if line.tax_id.price_include:
                    subtotal = subtotal / (1 + tax)

            line.subtotal = subtotal

    @api.depends('order_id.so_id.user_id', 'order_id.so_id.state', 'order_id.so_id.name')
    def _compute_so_data(self):
        for rec in self:
            so = rec.order_id.so_id
            if so:
                rec.so_state = so.state if so.state in ['draft', 'sale'] else False
                rec.so_name = so.name
            else:
                rec.so_state = False
                rec.so_name = False

    def _compute_is_md_person(self):
        current_user = self.env.user
        for rec in self:
            rec.is_md_person = bool(rec.md_person_ids == current_user)
            rec.is_receiver = bool(rec.given_to_id == current_user)

    @api.constrains('product_type', 'product_category', 'product_class')
    def _check_required_fields_for_md(self):
        for rec in self:
            if rec.state == 'cancel':
                continue
            is_md = self.env.user.has_group('indoteknik_custom.group_role_merchandiser')
            if is_md and (not rec.product_type or rec.product_category == False or rec.product_class == False):
                raise UserError("MD wajib mengisi SKU, Product Type, Product Category, dan Categories!")

    @api.depends('price', 'vendor_id', 'order_id')
    def _compute_show_for_sales(self):
        for rec in self:
            rec.show_for_sales = bool(
                rec.order_id and rec.price not in (None, 0) and rec.vendor_id
            )

    @api.model
    def create(self, vals):
        order_id = vals.get('order_id')
        if order_id:
            order = self.env['sourcing.job.order'].browse(order_id)
            if order.state == 'taken' and order.line_ids.md_person_ids != self.env.user:
                raise UserError("❌ SJO sudah taken. Tidak boleh tambah line.")
            if order.so_id:
                vals['so_id'] = order.so_id.id
                vals['show_salesperson'] = order.so_id.user_id.id

        rec = super().create(vals)
        return rec

    def write(self, vals):
        bypass_md_check = self.env.context.get('bypass_md_check')
        for rec in self:
            if (
                rec.md_person_ids 
                and self.env.uid != rec.md_person_ids.id 
                and rec.order_id.create_uid != self.env.user
                and not bypass_md_check
            ):
                raise UserError("❌ Hanya MD yang memegang job yang boleh mengedit Sourcing Job.")
        
        res = super().write(vals)
        if 'state' in vals:
            self._update_parent_state()
        return res

    def _update_parent_state(self):
        for rec in self:
            order = rec.order_id
            if not order:
                continue

            lines = order.line_ids
            if not lines:
                continue

            total = len(lines)

            if total == 1:
                line = lines[0]
                if line.state == 'approve':
                    order.state = 'done'
                elif line.state == 'cancel':
                    order.state = 'cancel'
                else:
                    order.state = 'taken'
                continue

            states = lines.mapped('state')

            all_cancel = all(s == 'cancel' for s in states)
            all_done_or_cancel = all(s in ['approve', 'cancel'] for s in states)
            any_done = any(s == 'approve' for s in states)
            any_progress = any(s not in ['approve', 'cancel', 'draft'] for s in states)

            if all_cancel:
                order.state = 'cancel'
                continue

            if all_done_or_cancel:
                order.state = 'done'
                continue

            if any_done and not all_done_or_cancel:
                order.state = 'partial'
                continue

            if any_progress:
                order.state = 'taken'
                continue
    
    def action_take(self):
        for rec in self:
            if not self.env.user.has_group('indoteknik_custom.group_role_merchandiser'):
                raise UserError("❌ Hanya Merchandiser yang dapat mengambil Sourcing Job.")
            if rec.state != 'draft':
                continue
            rec.state = 'sourcing'
            rec.md_person_ids = self.env.uid
            rec.order_id.state = 'taken'

            line_no = 1
            if rec.order_id:
                all_lines = self.search(
                    [('order_id', '=', rec.order_id.id)],
                    order='id asc'
                )
                for i, r in enumerate(all_lines, start=1):
                    if r.id == rec.id:
                        line_no = i
                        break

            rec.message_post(
                body=("Line <b>%s</b> dari Order <b>%s</b> diambil oleh %s")
                % (line_no, rec.order_id.name or '-', self.env.user.name)
            )

    def action_multi_take(self):
        if not self.env.user.has_group('indoteknik_custom.group_role_merchandiser'):
                raise UserError("❌ Hanya Merchandiser yang dapat mengambil Sourcing Job.")

        unsource = self.filtered(lambda r: r.state == 'draft')
        if not unsource:
            raise UserError("Tidak ada record Unsource untuk diambil.")

        unsource.write({
            'state': 'sourcing',
            'md_person_ids': self.env.uid
        })

        for rec in unsource:
            if rec.order_id.state == 'draft':
                rec.order_id.state = 'taken'

                line_no = self.search_count([
                    ('order_id', '=', rec.order_id.id),
                    ('id', '<=', rec.id)
                ]) 

                rec.message_post(
                    body=("Line <b>%s</b> dari Order <b>%s</b> diambil oleh %s")
                    % (line_no, rec.order_id.name or '-', self.env.user.name)
                )

            if rec.order_id.state != 'draft':
                continue

    # def action_multi_ask_approval(self):
    #     bot_sjo = '8335015210:AAGbObP0jQf7ptyqJhYdBYn5Rm0CWOd_yIM'
    #     chat_sjo = '6076436058'
    #     api_base = f'https://api.telegram.org/bot{bot_sjo}/sendMessage'

    #     order_ids = self.mapped('order_id')
    #     if len(order_ids) != 1:
    #         raise UserError("❌ Semua line harus berasal dari Sourcing Job yang sama.")

    #     order_ids = self.mapped('order_id')
    #     if len(order_ids) != 1:
    #         raise UserError("❌ Semua line harus berasal dari Sourcing Job yang sama.")

    #     job = order_ids[0]

    #     md_users = self.mapped('md_person_ids')
    #     if len(md_users) != 1 or md_users[0] != self.env.user:
    #         raise UserError("❌ Hanya MD yang memegang semua line ini yang bisa request approval.")

    #     for line in self:
    #         if line.state != 'sourcing':
    #             raise UserError(f"⚠️ Produk '{line.product_name_md}' bukan status Sourcing.")

    #         if (
    #             not line.vendor_id
    #             or not line.product_name_md
    #             or not brand_id
    #             or not line.price or line.price <= 0
    #             or not line.tax_id
    #             or not line.subtotal or line.subtotal <= 0
    #             or not line.product_type
    #             or not line.product_category
    #             or not line.product_class
    #         ):
    #             raise UserError(f"❌ Data produk '{line.product_name_md}' belum lengkap.")

    #     activity_type = self.env.ref('mail.mail_activity_data_todo')

    #     approved_lines_text = ""

    #     for line in self:
    #         line.state = 'sent'

    #         line.activity_schedule(
    #             activity_type_id=activity_type.id,
    #             user_id=line.show_salesperson.id,
    #             note=f"{self.env.user.name} meminta approval untuk produk '{line.product_name_md}' di SJO '{job.name}'.",
    #         )

    #         approved_lines_text += f"<li>{line.product_name_md} - {line.price or 0}</li>"

    #         line.message_post(
    #             body=f"📤 <b>Request approval dikirim (Multi)</b>",
    #             subtype_xmlid="mail.mt_comment",
    #         )

    #     job.message_post(
    #         body=(
    #             f"📤 <b>Multi Request Approval</b><br/>"
    #             f"<ul>{approved_lines_text}</ul>"
    #             f"<b>MD:</b> {self.env.user.name}"
    #         ),
    #         subtype_xmlid="mail.mt_comment",
    #     )

    #     self.env.user.notify_success(
    #         message=f"{len(self)} produk berhasil dikirim untuk approval.",
    #         title="Multi Request Sent"
    #     )

    #     # return {'type': 'ir.actions.client', 'tag': 'reload'}
    

    def action_ask_approval(self):
        if len(self.mapped('order_id')) > 1:
            raise UserError("❌ Multi Ask Approval hanya boleh untuk line dengan Sourcing Job yang sama.")

        bot_sjo = '8335015210:AAGbObP0jQf7ptyqJhYdBYn5Rm0CWOd_yIM'
        # chat_sjo = self.show_salesperson.partner_id.chat_id_telegram or False
        chat_sjo = '6076436058'
        api_base = f'https://api.telegram.org/bot{bot_sjo}/sendMessage'

        for line in self:
            job = line.order_id

            if line.md_person_ids != self.env.user:
                raise UserError("❌ Hanya MD pada line ini yang dapat Request Approval.")

            if line.state != 'sourcing':
                raise UserError("⚠️ Hanya line status 'Sourcing' yang bisa minta approval.")

            missing_fields = []

            if not line.vendor_id:
                missing_fields.append("Vendor")

            if not line.product_name_md:
                missing_fields.append("Product Name")

            if not line.web_tax_id:
                missing_fields.append("Website Tax")

            if not line.uom_id:
                missing_fields.append("Unit of Measure")

            if not line.brand_id:
                missing_fields.append("Manufactures")

            if not line.price or line.price <= 0:
                missing_fields.append("Price")

            if not line.tax_id:
                missing_fields.append("Tax")

            if not line.subtotal or line.subtotal <= 0:
                missing_fields.append("Subtotal")

            if not line.product_type:
                missing_fields.append("Product Type")

            if not line.product_category:
                missing_fields.append("Product Category")

            if not line.product_class:
                missing_fields.append("Product Class")

            if missing_fields:
                raise UserError(
                    "❌ Lengkapi data berikut sebelum Ask Approval Sales:\n- " +
                    "\n- ".join(missing_fields)
                )

            line.state = 'sent'

            activity_type = self.env.ref('mail.mail_activity_data_todo')

            line.activity_schedule(
                activity_type_id=activity_type.id,
                user_id=line.show_salesperson.id,
                note=f"{self.env.user.name} meminta approval untuk produk '{line.product_name_md}' di SJO '{job.name}'.",
            )

            line.message_post(
                body=(
                    f"📤 <b>Request approval dikirim</b><br/>"
                    f"Kepada: <b>{line.show_salesperson.name}</b><br/>"
                    f"Produk: <b>{line.product_name_md}</b>"
                ),
                subtype_xmlid="mail.mt_comment"
            )

            job.message_post(
                body=(
                    f"📤 <b>Request approval line</b><br/>"
                    f"<ul>"
                    f"<li><b>Produk:</b> {line.product_name_md}</li>"
                    f"<li><b>MD:</b> {self.env.user.name}</li>"
                    f"<li><b>Vendor:</b> {line.vendor_id.display_name if line.vendor_id else '-'}</li>"
                    f"<li><b>Harga:</b> {line.price or 0}</li>"
                    f"</ul>"
                ),
                subtype_xmlid="mail.mt_comment"
            )

            self.env.user.notify_success(
                message=f"Request approval untuk '{line.product_name_md}' dikirim ke {line.show_salesperson.name}",
                title="Request Sent",
            )

            base_url = self.env['ir.config_parameter'].sudo().get_param('web.base.url')
            url = f"{base_url}/web#id={job.id}&model=sourcing.job.order&view_type=form"
            
            if chat_sjo:
                try:
                    msg_text = (
                        f"📢 <b>Request Approval Produk</b>\n\n"
                        f"🧾 <b>Sourcing Job:</b> <a href='{url}'>📎 {job.name}</a>\n"
                        f"📦 <b>Produk:</b> {line.product_name_md}\n"
                        f"👤 <b>MD:</b> {self.env.user.name}\n"
                        f"💰 <b>Harga:</b> {line.price or 0}\n"
                        f"📅 <b>Tanggal:</b> {fields.Datetime.now().strftime('%d-%m-%Y %H:%M')}\n\n"
                        f"Silakan review di Odoo."
                    )

                    payload = {
                        'chat_id': chat_sjo,
                        'text': msg_text,
                        'parse_mode': 'HTML'
                    }

                    response = requests.post(api_base, data=payload, timeout=10)
                    response.raise_for_status()

                except Exception as e:
                    _logger.warning(f"Gagal kirim telegram approval line: {e}")

        return {'type': 'ir.actions.client', 'tag': 'reload'}

    def action_approve_approval(self):
        ProductProduct = self.env['product.product']
        PurchasePricelist = self.env['purchase.pricelist']
        SaleOrderLine = self.env['sale.order.line']

        for rec in self:
            job = rec.order_id

            if not rec.show_salesperson or rec.show_salesperson.id != self.env.uid:
                raise UserError("❌ Hanya Salesperson Sale Order yang bisa approve.")

            rec.write({'state': 'approve'})

            product = False
            if rec.code:
                product = ProductProduct.search([
                    ('default_code', '=', rec.code),
                    ('active', '=', True)
                ], limit=1)

            if product:
                rec.product_id = product.id

                self.env.user.notify_warning(
                    message=f"SKU {rec.code} sudah ada. Tidak dibuat ulang.",
                    title="SKU Exists"
                )

            else:
                type_map = {
                    'servis': 'service',
                    'product': 'product',
                    'consu': 'consu',
                }

                product = ProductProduct.with_context(from_sourcing_approval=True).create({
                    'name': rec.product_name_md,
                    'default_code': rec.code or False,
                    'description': rec.descriptions_md or '',
                    'web_tax_id': rec.web_tax_id.id or False,
                    'uom_id': rec.uom_id.id or False,
                    'uom_po_id': rec.uom_id.id or False,
                    'type': type_map.get(rec.product_type, 'product'),
                    'categ_id': rec.product_category.id if rec.product_category else False,
                    'x_manufacture': rec.brand_id.id if rec.brand_id else False,
                    'standard_price': rec.price or 0,
                    'public_categ_ids': [(6, 0, rec.product_class.ids)] if rec.product_class else False,
                    'active': True,
                    'sourcing_job_id': job.id if job else False,
                })

                if not rec.code:
                    padded_id = str(product.id).zfill(7)
                    sku_auto = f"IT.{padded_id}"
                    product.default_code = sku_auto
                    rec.code = sku_auto

                if product.categ_id and product.categ_id.id == 34:
                    product.unpublished = True

                rec.product_id = product.id

                self.env.user.notify_success(
                    message=f"Produk baru '{product.name}' berhasil dibuat dengan SKU {product.default_code}.",
                    title="Product Created"
                )

            jakarta_tz = rec.order_id._get_jakarta_today()

            purchase_price = PurchasePricelist.search([
                ('product_id', '=', product.id),
                ('vendor_id', '=', rec.vendor_id.id),
            ], order="human_last_update desc", limit=1)

            pricelist_vals = {
                'product_id': product.id,
                'vendor_id': rec.vendor_id.id,
                'product_price': rec.price or 0,
                'include_price': rec.price or 0,
                'taxes_product_id': rec.tax_id.id if rec.tax_id else False,
                'brand_id': product.x_manufacture.id if product.x_manufacture else False,
                'human_last_update': jakarta_tz,
            }

            if not purchase_price and product.categ_id and product.categ_id.id != 34:
                PurchasePricelist.create(pricelist_vals)

            elif purchase_price.product_price != (rec.price or 0):
                purchase_price.write(pricelist_vals)

            if rec.so_id and not rec.exported_to_so:
                so = rec.so_id

                so_line_new = SaleOrderLine.new({
                    "order_id": so.id,
                    "product_id": product.id,
                    "product_uom_qty": rec.quantity or 1,
                    "purchase_price": rec.price or 0,
                    "purchase_tax_id": rec.tax_id.id or 0,
                    "name": rec.product_name_md,
                    "vendor_id":  rec.vendor_id.id,
                })

                so_line_new._onchange_vendor_id_custom()
                vals = SaleOrderLine._convert_to_write(so_line_new._cache)
                SaleOrderLine.create(vals)

                rec.exported_to_so = True

            activities = self.env['mail.activity'].search([
                ('res_model', '=', rec._name),
                ('res_id', '=', rec.id),
            ])
            activities.unlink()

            rec.message_post(
                body=(
                    f"✅ <b>Approval disetujui oleh {self.env.user.name}</b><br/>"
                    f"Produk siap untuk proses selanjutnya (convert / PO / SO)."
                ),
                subtype_xmlid="mail.mt_comment"
            )

            job.message_post(
                body=(
                    f"✅ <b>Approval produk disetujui</b><br/>"
                    f"<ul>"
                    f"<li><b>Produk:</b> {rec.product_name_md}</li>"
                    f"<li><b>Vendor:</b> {rec.vendor_id.display_name if rec.vendor_id else '-'}</li>"
                    f"<li><b>Harga:</b> {rec.price or 0}</li>"
                    f"<li><b>Disetujui oleh:</b> {self.env.user.name}</li>"
                    f"</ul>"
                ),
                subtype_xmlid="mail.mt_comment"
            )

            self.env.user.notify_success(
                message=f"Produk '{rec.product_name_md}' berhasil di-approve.",
                title="Approved"
            )

        so = self.mapped('so_id')[:1]
        if so:
            return {
                'type': 'ir.actions.act_window',
                'name': 'Sales Order',
                'res_model': 'sale.order',
                'view_mode': 'form',
                'res_id': so.id,
                'target': 'current',
            }

        return {'type': 'ir.actions.client', 'tag': 'reload'}

    def action_multi_approve(self):
        so_ids = self.mapped('so_id').ids

        if len(set(so_ids)) > 1:
            raise UserError("❌ Multi approve hanya bisa dilakukan jika semua line berasal dari Sales Order yang sama.")
        self.action_approve_approval()

    def action_reject_approval(self):
        self.ensure_one()

        job = self.order_id
        if job.create_uid != self.env.user:
            raise UserError("❌ Hanya pembuat Sourcing Job yang bisa reject approval.")
        
        return {
            'name': 'Reason Reject',
            'type': 'ir.actions.act_window',
            'res_model': 'sourcing.reject.wizard',
            'view_mode': 'form',
            'target': 'new',
            'context': {
                'default_line_id': self.id
            }
        }

    def action_cancel(self):
        for rec in self:
            if self.env.user != rec.md_person_ids:
                raise UserError("Hanya MD Person Job ini yang bisa Cancel.")

            if not rec.reason:
                raise UserError("Isi Reason untuk Cancel Job.")
                
            line_no = 1
            if rec.order_id:
                all_lines = self.search(
                    [('order_id', '=', rec.order_id.id)],
                    order='id asc'
                )
                for i, r in enumerate(all_lines, start=1):
                    if r.id == rec.id:
                        line_no = i
                        break

            rec.write({'state': 'cancel'})

            rec.message_post(
                body=(
                    "Line <b>%s</b> dari Order <b>%s</b> di Cancel oleh <b>%s</b><br/>"
                    "Reason: %s"
                ) % (
                    line_no,
                    rec.order_id.name or '-',
                    self.env.user.name,
                    rec.reason or '-'
                )
            )
            if rec.show_salesperson:
                rec.message_notify(
                    partner_ids=[rec.show_salesperson.partner_id.id],
                    subject="SJO Line Cancelled",
                    body=(
                        f"⚠️ Line <b>{line_no}</b> dari SJO <b>{rec.order_id.name}</b> "
                        f"telah di Cancel oleh <b>{self.env.user.name}</b>.<br/>"
                        f"<b>Reason:</b> {rec.reason}"
                    )
                )

    @api.onchange('product_id')
    def _oncange_code(self):
        for rec in self:
            if not rec.product_id:
                continue

            product = rec.product_id
            if not product:
                return
            template = product.product_tmpl_id
            attribute_values = product.product_template_attribute_value_ids.mapped(
                'product_attribute_value_id.name'
            )
            attribute_values_str = ', '.join(attribute_values) if attribute_values else ''

            # generate line name
            line_name = (
                ('[' + product.default_code + '] ' if product.default_code else '') +
                (product.name or '') +
                (' (' + attribute_values_str + ')' if attribute_values_str else '') +
                (' ' + product.short_spesification if product.short_spesification else '')
            )
            
            rec.code = product.default_code or rec.code
            rec.product_name_md = product.name or rec.product_name_md
            rec.descriptions_md = line_name.strip() or rec.descriptions_md
            rec.product_type = template.type or rec.product_type
            rec.brand_id = product.x_manufacture.id or rec.brand_id
            rec.product_category = template.categ_id.id or rec.product_category
            rec.web_tax_id = template.web_tax_id.id or rec.web_tax_id
            rec.uom_id = template.uom_id.id or rec.uom_id
            rec.product_class = [(6, 0, template.public_categ_ids.ids)] if template.public_categ_ids else []

            pricelist = self.env['purchase.pricelist'].search([('product_id', '=', product.id), ('is_winner', '=', True)], limit=1)
            if pricelist:
                rec.vendor_id = pricelist.vendor_id.id or False
                rec.price = pricelist.include_price or 0.0
                rec.now_price = pricelist.include_price or 0.0
                rec.last_updated_price = pricelist.write_date or 0.0
                rec.tax_id = pricelist.taxes_product_id.id or pricelist.taxes_system_id.id or False

    @api.onchange('vendor_id')
    def _onchange_vendor_id_custom(self):
        self._update_purchase_info()

    def _update_purchase_info(self):
        if not self.product_id or self.product_id.type == 'service':
            return

        if self.product_id.categ_id.id == 34:
            self.price = self.product_id.standard_price
            self.tax_id = False
            self.now_price = 0
            self.last_updated_price = False
        elif self.product_id.x_manufacture.override_vendor_id:
            # Memanggil fungsi yang sudah kita perbaiki pengecekannya
            price, taxes, vendor_id, last_updated = self._get_purchase_price_by_vendor(self.product_id, self.vendor_id)
            
            # Nilai ini akan mengosongkan field jika data tidak ditemukan (0.0 atau False)
            self.price = price
            self.now_price = price
            self.tax_id = taxes
            self.last_updated_price = last_updated

    def _get_purchase_price_by_vendor(self, product_id, vendor_id):
        purchase_price = self.env['purchase.pricelist'].search([
            ('product_id', '=', product_id.id),
            ('vendor_id', '=', vendor_id.id),
        ], limit=1)
        
        # Jika tidak ketemu di pricelist, kembalikan nilai kosong (reset)
        if not purchase_price:
            return 0.0, False, False, False
            
        return self._get_valid_purchase_price(purchase_price)

    def _get_valid_purchase_price(self, purchase_price):
        current_time = datetime.now()
        delta_time = current_time - timedelta(days=365)
        
        # Inisialisasi default kosong
        price = 0.0
        taxes = False
        vendor_id = False
        last_updated = False

        human_last_update = purchase_price.human_last_update or False
        system_last_update = purchase_price.system_last_update or False

        # Logika pengecekan Human Update
        if human_last_update and human_last_update > delta_time:
            price = purchase_price.product_price
            taxes = purchase_price.taxes_product_id.id
            vendor_id = purchase_price.vendor_id.id
            last_updated = human_last_update

        # Logika pengecekan System Update (Timpa jika lebih baru)
        if system_last_update and system_last_update > delta_time:
            if not last_updated or system_last_update > last_updated:
                price = purchase_price.system_price
                taxes = purchase_price.taxes_system_id.id
                vendor_id = purchase_price.vendor_id.id
                last_updated = system_last_update

        return price, taxes, vendor_id, last_updated
    
    @api.onchange('attachment_type')
    def _onchange_attachment_type(self):
        for rec in self:
            if rec.attachment_type == 'pdf':
                rec.product_attachment_img = False
                rec.product_attachment_other = False

            elif rec.attachment_type == 'img':
                rec.product_attachment_pdf = False
                rec.product_attachment_other = False

            elif rec.attachment_type == 'other':
                rec.product_attachment_pdf = False
                rec.product_attachment_img = False

            else:
                rec.product_attachment_pdf = False
                rec.product_attachment_img = False
                rec.product_attachment_other = False

    @api.onchange(
        'product_attachment_pdf',
        'product_attachment_img',
        'product_attachment_other',
        'attachment_type'
    )
    def _onchange_set_filename(self):
        for rec in self:
            sjo_number = rec.order_id.name if rec.order_id and rec.order_id.name else 'SJO'

            if rec.attachment_type == 'pdf' and rec.product_attachment_pdf:
                rec.product_attachment_filename = f"{sjo_number}.pdf"

            elif rec.attachment_type == 'img' and rec.product_attachment_img:
                rec.product_attachment_filename = f"{sjo_number}.png"

            elif rec.attachment_type == 'other' and rec.product_attachment_other:
                rec.product_attachment_filename = f"{sjo_number}_file"

    def action_reopen_cancel(self):
        self.ensure_one()

        if self.state != 'cancel':
            raise UserError("Cuma line cancel yang bisa direopen.")
        if self.order_id.create_uid != self.env.user:
            raise UserError("Cuma Pemilik SJO yang bisa Re-Open.")

        return {
            'name': 'Reason Reopen',
            'type': 'ir.actions.act_window',
            'res_model': 'reopen.cancel.line.wizard',
            'view_mode': 'form',
            'target': 'new',
            'context': {
                'default_line_id': self.id
            }
        }

    def action_open_give_wizard(self):
        self.ensure_one()
        return {
            'type': 'ir.actions.act_window',
            'name': 'Give To MD',
            'res_model': 'sjo.give.wizard',
            'view_mode': 'form',
            'target': 'new',
            'context': {
                'default_line_id': self.id,
            }
        }

    def action_open_reject_given_wizard(self):
        self.ensure_one()
        return {
            'type': 'ir.actions.act_window',
            'name': 'Reject Request Give SJO Line',
            'res_model': 'sjo.reject.give.wizard',
            'view_mode': 'form',
            'target': 'new',
            'context': {
                'default_line_id': self.id,
            }
        }

    def action_take_given(self):
        for rec in self:
            if self.env.user != rec.given_to_id:
                raise UserError("Hanya MD yang diberikan Request yang bisa Take Sourcing")

            old_owner = rec.previous_md_id.name
            new_owner = rec.given_to_id.name
            receiver = rec.given_to_id 

            rec.with_context(bypass_md_check=True).write({
                'md_person_ids': rec.given_to_id.id,
                'given_to_id': False,
                'previous_md_id': False,
                'is_given': False,
            })

            rec._unlink_give_activity(receiver)
            rec.message_post(
                body=f"<b>{new_owner}<b/> Menerima Request Sourcing dari <b>{old_owner}<b/>"
            )

    def _unlink_give_activity(self, user):
        activity_type = self.env.ref('mail.mail_activity_data_todo')

        activities = self.activity_ids.filtered(
            lambda a: a.activity_type_id.id == activity_type.id
            and a.user_id.id == user.id
        )
        activities.unlink()

class SjoGiveWizard(models.TransientModel):
    _name = 'sjo.give.wizard'
    _description = 'Give SJO Line Wizard'

    line_id = fields.Many2one('sourcing.job.order.line')
    md_id = fields.Many2one('res.users', string='Give To', required=True, domain=lambda self: [
        ('groups_id', 'in', self.env.ref('base.group_user').ids),
        ('groups_id', 'in', self.env.ref('indoteknik_custom.group_role_merchandiser').ids),
        ('active', '=', True)
    ])

    def action_confirm(self):
        self.ensure_one()

        line = self.line_id

        if self.env.user != line.md_person_ids:
            raise UserError("Hanya Md Target yang bisa Confirm Give Sourcing")

        old_owner = line.md_person_ids.name
        new_owner = self.md_id.name

        line.write({
            'previous_md_id': line.md_person_ids.id,
            'given_to_id': self.md_id.id,
            'is_given': True,
        })

        activity_type = self.env.ref('mail.mail_activity_data_todo')
        line.activity_schedule(
            activity_type_id=activity_type.id,
            user_id=self.md_id.id,
            note="SJO Line diberikan ke Anda. Silakan Take atau Reject.",
        )

        line.message_post(
            body=f"""
                <b>MD {old_owner}</b> Mengirim Request Peralihan Sourcing Ke <b>{new_owner}</b>
            """,
            subtype_xmlid="mail.mt_comment"
        )

class SjoRejectGiveWizard(models.TransientModel):
    _name = 'sjo.reject.give.wizard'
    _description = 'Reject Given SJO Line Wizard'

    line_id = fields.Many2one('sourcing.job.order.line', required=True)
    reason = fields.Text(string="Reject Reason", required=True)

    def action_confirm(self):
        self.ensure_one()
        line = self.line_id

        if self.env.user != line.given_to_id:
            raise UserError("Hanya Penerima Request yang bisa Reject Give")

        from_md = line.previous_md_id.name or "-"
        receiver = line.given_to_id
        rejector = self.env.user.name

        line._unlink_give_activity(receiver)

        line.with_context(bypass_md_check=True).write({
            'given_to_id': False,
            'is_given': False,
        })

        line.message_post(
            body=f"""
                Request Peralihan dari <b>{from_md}</b> Rejected by <b>{rejector}</b><br/>
                Alasan: {self.reason}
            """,
            subtype_xmlid="mail.mt_comment"
        )   

class WizardExportSJOtoSO(models.TransientModel):
    _name = "wizard.export.sjo.to.so"
    _description = "Wizard Export SJO Products to SO"

    sjo_id = fields.Many2one("sourcing.job.order", string="Sourcing Job ID")
    line_ids = fields.Many2many("sourcing.job.order.line", string="SJO Lines")
    product_ids = fields.Many2many(
        "product.product",
        string="Products",
        compute="_compute_products",
        store=False,
    )

    @api.model
    def default_get(self, fields):
        res = super().default_get(fields)
        sjo_id = self.env.context.get("default_sjo_id")

        if sjo_id:
            # ambil line yg punya product & belum di export
            lines = self.env["sourcing.job.order.line"].search([
                ("order_id", "=", sjo_id),
                ("product_id", "!=", False),
                ("exported_to_so", "=", False),
                ("state", "=", "approve"),  # optional: cuma yg done
            ])
            res["line_ids"] = [(6, 0, lines.ids)]

        return res

    @api.depends("line_ids")
    def _compute_products(self):
        for rec in self:
            rec.product_ids = rec.line_ids.mapped("product_id")

    def action_confirm(self):
        self.ensure_one()
        sjo = self.sjo_id

        if not sjo.so_id:
            raise UserError("Sales Order belum dipilih di SJO!")

        so = sjo.so_id
        SaleOrderLine = self.env["sale.order.line"]

        for line in self.line_ids:
            if not line.product_id:
                continue

            # bikin SOL dari product
            so_line_new = SaleOrderLine.new({
                "order_id": so.id,
                "product_id": line.product_id.id,
                "product_uom_qty": line.quantity or 1,
                "price_unit": line.price or 0,
                "name": line.product_name,
            })

            so_line_new.product_id_change()
            vals = SaleOrderLine._convert_to_write(so_line_new._cache)
            new_line = SaleOrderLine.create(vals)

            # tandai sudah export
            line.exported_to_so = True

        return {
            'type': 'ir.actions.act_window',
            'res_model': 'sale.order',
            'view_mode': 'form',
            'res_id': so.id,
            'target': 'current',
        }

class SourcingJobOrderLineImportWizard(models.TransientModel):
    _name = 'sourcing.job.order.line.import.wizard'
    _description = 'Import SJO Line from Excel'

    excel_file = fields.Binary("Excel File", required=True)
    filename = fields.Char("Filename")
    order_id = fields.Many2one('sourcing.job.order', string="Sourcing Job Order", required=True)

    def action_import_excel(self):
        if not self.excel_file:
            raise UserError(_("⚠️ Harap upload file Excel terlebih dahulu."))

        try:
            data = base64.b64decode(self.excel_file)
            book = xlrd.open_workbook(file_contents=data)
            sheet = book.sheet_by_index(0)
        except:
            raise UserError(_("❌ Format Excel tidak valid atau rusak."))

        header = [str(sheet.cell(0, col).value).strip() for col in range(sheet.ncols)]
        required_headers = [
            'Nama Barang', 'SKU', 'Expected Price', 'Note Sourcing', 'Brand',
            'Deskripsi / Spesifikasi', 'SLA Product', 'Quantity Product',
            'Purchase Price', 'Tax', 'Vendor', 'Product Category',
            'Categories', 'Product Type'
        ]

        for req in required_headers:
            if req not in header:
                raise UserError(_("❌ Kolom '%s' tidak ditemukan di file Excel.") % req)

        header_map = {h: idx for idx, h in enumerate(header)}
        lines_created = 0
        ProductLine = self.env['sourcing.job.order.line']
        Tax = self.env['account.tax']
        Vendor = self.env['res.partner']
        Category = self.env['product.category']
        PublicCategory = self.env['product.public.category']

        for row_idx in range(1, sheet.nrows):
            row = sheet.row(row_idx)
            def val(field):
                return str(sheet.cell(row_idx, header_map[field]).value).strip()

            if not val('Nama Barang'):
                continue  # skip kosong

            # Relations
            tax = Tax.search([('name', 'ilike', val('Tax'))], limit=1)
            vendor = Vendor.search([('name', 'ilike', val('Vendor'))], limit=1)
            category = Category.search([('name', 'ilike', val('Product Category'))], limit=1)

            # Many2many: Categories
            class_names = val('Categories').split(';')
            class_ids = []
            for name in class_names:
                name = name.strip()
                if name:
                    pc = PublicCategory.search([('name', 'ilike', name)], limit=1)
                    if pc:
                        class_ids.append(pc.id)

            # Build values
            vals = {
                'order_id': self.order_id.id,
                'product_name': val('Nama Barang'),
                'code': val('SKU'),
                'budget': val('Expected Price'),
                'note': val('Note Sourcing'),
                'brand': val('Brand'),
                'descriptions': val('Deskripsi / Spesifikasi'),
                'sla': val('SLA Product'),
                'quantity': float(val('Quantity Product') or 0),
                'price': float(val('Purchase Price') or 0),
                'tax_id': tax.id if tax else False,
                'vendor_id': vendor.id if vendor else False,
                'product_category': category.id if category else False,
                'product_type': val('Product Type') or 'product',
                'product_class': [(6, 0, class_ids)],
            }

            ProductLine.create(vals)
            lines_created += 1

        return {
            'type': 'ir.actions.client',
            'tag': 'display_notification',
            'params': {
                'title': _('✅ Import Selesai'),
                'message': _('%s baris berhasil diimport.') % lines_created,
                'type': 'success',
                'sticky': False,
            }
        }

class SourcingJobOrderLineExportWizard(models.TransientModel):
    _name = 'sourcing.job.order.line.export.wizard'
    _description = 'Export SJO Line Wizard'

    order_id = fields.Many2one('sourcing.job.order', string="Sourcing Job Order", required=True)
    file = fields.Binary("CSV File", readonly=True)
    filename = fields.Char("Filename", readonly=True)

    def action_export(self):
        if not self.order_id:
            raise UserError("Silakan pilih Sourcing Job Order terlebih dahulu.")

        lines = self.env['sourcing.job.order.line'].search([('order_id', '=', self.order_id.id)])
        wb = xlwt.Workbook()
        sheet = wb.add_sheet("SJO Lines")

        headers = [
            'Nama Barang', 'SKU', 'Expected Price', 'Note Sourcing', 'Brand',
            'Deskripsi / Spesifikasi', 'SLA Product', 'Quantity Product',
            'Purchase Price', 'Tax', 'Vendor', 'Product Category',
            'Categories', 'Product Type'
        ]

        # Write header
        for col, header in enumerate(headers):
            sheet.write(0, col, header)

        for row_idx, line in enumerate(lines, start=1):
            categories = '; '.join(line.product_class.mapped('name')) or ''
            values = [
                line.product_name or '',
                line.code or '',
                line.budget or '',
                line.note or '',
                line.brand or '',
                line.descriptions or '',
                line.sla or '',
                line.quantity or 0,
                line.price or 0,
                line.tax_id.name if line.tax_id else '',
                line.vendor_id.name if line.vendor_id else '',
                line.product_category.name if line.product_category else '',
                categories,
                line.product_type or '',
            ]
            for col_idx, value in enumerate(values):
                sheet.write(row_idx, col_idx, value)

        # Save to binary
        fp = io.BytesIO()
        wb.save(fp)
        fp.seek(0)
        data = fp.read()
        fp.close()

        self.file = base64.b64encode(data)
        self.filename = f"SJO_{self.order_id.name}_lines.xls"  # Note: xlwt hanya mendukung .xls

        return {
            'type': 'ir.actions.act_window',
            'res_model': self._name,
            'view_mode': 'form',
            'res_id': self.id,
            'target': 'new',
        }


class SourcingJobOrderLineTemplateWizard(models.TransientModel):
    _name = 'sourcing.job.order.line.template.wizard'
    _description = 'Download & Import Template SJO Line'

    file = fields.Binary("Template", readonly=True)
    filename = fields.Char("Filename", readonly=True)

    order_id = fields.Many2one(
        'sourcing.job.order',
        string="Sourcing Job Order",
        required=True,
        domain="[('state', '=', 'taken')]",
        default=lambda self: self.env.context.get('active_id')
    )

    excel_file = fields.Binary("Upload Excel")
    excel_filename = fields.Char("Excel Filename")

    def action_generate_template(self):
        output = io.BytesIO()
        wb = xlwt.Workbook()
        ws = wb.add_sheet('Template')

        headers = [
            'Nama Barang', 'SKU', 'Expected Price', 'Note Sourcing', 'Brand',
            'Deskripsi / Spesifikasi', 'SLA Product', 'Quantity Product',
            'Purchase Price', 'Tax', 'Vendor', 'Product Category',
            'Categories', 'Product Type'
        ]

        for col, header in enumerate(headers):
            ws.write(0, col, header)

        wb.save(output)
        output.seek(0)

        self.file = base64.b64encode(output.read())
        self.filename = "SJO_import_template.xls"

        return {
            'type': 'ir.actions.act_window',
            'res_model': self._name,
            'view_mode': 'form',
            'res_id': self.id,
            'target': 'new',
        }

    def action_import_excel(self):
        if not self.excel_file:
            raise UserError(_("⚠️ Harap upload file Excel terlebih dahulu."))

        if not self.order_id:
            raise UserError(_("⚠️ Pilih Sourcing Job Order dulu."))

        try:
            data = base64.b64decode(self.excel_file)
            book = xlrd.open_workbook(file_contents=data)
            sheet = book.sheet_by_index(0)
        except:
            raise UserError(_("❌ Format Excel tidak valid atau rusak."))

        header = [str(sheet.cell(0, col).value).strip() for col in range(sheet.ncols)]
        required_headers = [
            'Nama Barang', 'SKU', 'Expected Price', 'Note Sourcing', 'Brand',
            'Deskripsi / Spesifikasi', 'SLA Product', 'Quantity Product',
            'Purchase Price', 'Tax', 'Vendor', 'Product Category',
            'Categories', 'Product Type'
        ]

        for req in required_headers:
            if req not in header:
                raise UserError(_("❌ Kolom '%s' tidak ditemukan di file Excel.") % req)

        header_map = {h: idx for idx, h in enumerate(header)}
        lines_created = 0

        ProductLine = self.env['sourcing.job.order.line']
        Tax = self.env['account.tax']
        Vendor = self.env['res.partner']
        Category = self.env['product.category']
        PublicCategory = self.env['product.public.category']

        for row_idx in range(1, sheet.nrows):

            def val(field):
                return str(sheet.cell(row_idx, header_map[field]).value).strip()

            if not val('Nama Barang'):
                continue

            tax = Tax.search([('name', 'ilike', val('Tax'))], limit=1)
            vendor = Vendor.search([('name', 'ilike', val('Vendor'))], limit=1)
            category = Category.search([('name', 'ilike', val('Product Category'))], limit=1)

            # many2many categories
            class_names = val('Categories').split(';')
            class_ids = []
            for name in class_names:
                name = name.strip()
                if name:
                    pc = PublicCategory.search([('name', 'ilike', name)], limit=1)
                    if pc:
                        class_ids.append(pc.id)

            vals = {
                'order_id': self.order_id.id,
                'product_name': val('Nama Barang'),
                'code': val('SKU'),
                'budget': float(val('Expected Price') or 0),
                'note': val('Note Sourcing'),
                'brand': val('Brand'),
                'descriptions': val('Deskripsi / Spesifikasi'),
                'sla': val('SLA Product'),
                'quantity': float(val('Quantity Product') or 0),
                'price': float(val('Purchase Price') or 0),
                'tax_id': tax.id if tax else False,
                'vendor_id': vendor.id if vendor else False,
                'product_category': category.id if category else False,
                'product_type': val('Product Type') or 'product',
                'product_class': [(6, 0, class_ids)],
                'state': 'sourcing',
                'md_person_id': self.env.user,
            }

            ProductLine.create(vals)
            lines_created += 1

        return {
            'type': 'ir.actions.client',
            'tag': 'display_notification',
            'params': {
                'title': _('✅ Import Selesai'),
                'message': _('%s baris berhasil diimport.') % lines_created,
                'type': 'success',
                'sticky': False,
            }
        }

class SourcingRejectWizard(models.TransientModel):
    _name = 'sourcing.reject.wizard'
    _description = 'Wizard alasan reject produk sourcing oleh sales'

    line_id = fields.Many2one('sourcing.job.order.line', string='Sourcing Line', required=True)
    reason = fields.Text(string='Alasan Penolakan', required=True)

    def action_confirm_reject(self):
        self.ensure_one()
        bot_sjo = '8335015210:AAGbObP0jQf7ptyqJhYdBYn5Rm0CWOd_yIM'
        chat_sjo = '-5081839952'
        api_base = f'https://api.telegram.org/bot{bot_sjo}/sendMessage'

        line = self.line_id
        job = line.order_id

        line.state = 'sourcing'  

        base_url = self.env['ir.config_parameter'].sudo().get_param('web.base.url')
        url = f"{base_url}/web#id={job.id}&model=sourcing.job.order&view_type=form"

        try:
            msg_text = (
                f"🚫 <b>Approval Sourcing Ditolak</b>\n\n"
                f"🧾 <b>Sourcing Job:</b> <a href='{url}'>📎 {job.name}</a>\n"
                f"📦 <b>Produk:</b> {line.product_name}\n"
                f"👤 <b>Sales:</b> {line.show_salesperson.name if line.show_salesperson else '-'}\n"
                f"👤 <b>MD:</b> {line.md_person_ids.name if line.md_person_ids else '-'}\n"
                f"❌ <b>Ditolak Oleh:</b> {self.env.user.name}\n"
                f"📝 <b>Alasan Reject:</b>\n{self.reason}\n\n"
                f"📅 <b>Tanggal:</b> {fields.Datetime.now().strftime('%d-%m-%Y %H:%M')}"
            )

            payload = {
                'chat_id': chat_sjo,
                'text': msg_text,
                'parse_mode': 'HTML'
            }

            response = requests.post(api_base, data=payload, timeout=10)
            response.raise_for_status()

        except Exception as e:
            _logger.warning(f"Gagal kirim telegram reject line: {e}")

        activities = self.env['mail.activity'].search([
            ('res_model', '=', line._name),
            ('res_id', '=', line.id),
        ])

        activities.unlink()
        line.message_post(
            body=(
                f"❌ <b>Approval ditolak oleh {self.env.user.name}</b><br/>"
                f"<b>Produk:</b> {line.product_name}<br/>"
                f"<b>Alasan:</b><br/>{self.reason}"
            ),
            subtype_xmlid="mail.mt_comment"
        )

        job.message_post(
            body=(
                f"❌ <b>Approval produk ditolak</b><br/>"
                f"<ul>"
                f"<li><b>Produk:</b> {line.product_name}</li>"
                f"<li><b>Ditolak oleh:</b> {self.env.user.name}</li>"
                f"<li><b>Alasan:</b> {self.reason}</li>"
                f"</ul>"
            ),
            subtype_xmlid="mail.mt_comment"
        )

        if line.md_person_ids:
            line.md_person_ids.notify_warning(
                message=f"Produk '{line.product_name}' direject sales. Silakan sourcing ulang.",
                title="Approval Ditolak"
            )

        self.env.user.notify_info(
            message=f"Produk '{line.product_name}' berhasil direject.",
            title="Rejected"
        )

        return {'type': 'ir.actions.client', 'tag': 'reload'}

class ReopenCancelLineWizard(models.TransientModel):
    _name = 'reopen.cancel.line.wizard'
    _description = 'Reopen Cancel Line Reason'

    line_id = fields.Many2one('sourcing.job.order.line', required=True)
    reason = fields.Text(required=True, string="Reason Reopen")

    def action_confirm(self):
        self.ensure_one()
        line = self.line_id

        if line.order_id.create_uid != self.env.user:
            raise UserError("Line ini bukan bagian dari SJO anda.")

        # post message dulu
        line.message_post(
            body=(
                "Line <b>%s</b> di REOPEN oleh <b>%s</b><br/>"
                "<b>Reason:</b> %s"
            ) % (
                line.product_id.display_name or '-',
                self.env.user.name,
                self.reason
            )
        )

        # reset field
        line.write({
            'state': 'draft',
            'md_person_ids': False,
            'reason': False,
        })