from odoo import models, fields, api
from odoo import tools
from odoo.exceptions import UserError

class ReorderingRule(models.Model):
    _name = 'reordering.rule'
    _description = 'Buffer Stock'
    _auto = False  # Karena ini dari SQL view

    product_id = fields.Many2one('product.product', string='Product', readonly=True)
    min_stock = fields.Float(string='Min Stock', readonly=True)
    buffer_stock = fields.Float(string='Buffer Stock', readonly=True)
    vendor_id = fields.Many2one('res.partner', string='Vendor', readonly=True)
    qty_onhand = fields.Float(string='Qty Onhand', readonly=True)
    qty_incoming = fields.Float(string='Qty Incoming')
    qty_outgoing = fields.Float(string='Qty Outgoing')
    stock_status = fields.Char('Stock Status',readonly=True)
    diff_stock = fields.Float(string='Diff Stock')

    def create_automatic_purchase(self):
        if not self:
            raise UserError("No stock records selected.")

        automatic_purchase = self.env['automatic.purchase'].create({
            'apo_type': 'reordering',
        })

        lines_to_create = []

        for stock in self:
            location_id = 55

            quant_records = self.env['stock.quant'].search([
                ('product_id', '=', stock.product_id.id),
                ('location_id', '=', location_id)
            ])

            total_available = quant_records.quantity or 0.0

            qty_incoming = stock.product_id.incoming_qty or 0.0

            qty_purchase = stock.buffer_stock - (total_available + qty_incoming)

            qty_purchase = max(qty_purchase, 0.0)

            pricelist = self.env['product.supplierinfo'].search([
                ('product_tmpl_id', '=', stock.product_id.product_tmpl_id.id),
                ('name', '=', stock.vendor_id.id)
            ], limit=1)

            if not pricelist:
                raise UserError("No pricelist found for product %s and vendor %s" % (stock.product_id.name, stock.vendor_id.name))

            price = pricelist.price if pricelist else 0.0
            subtotal = qty_purchase * price

            lines_to_create.append({
                'automatic_purchase_id': automatic_purchase.id,
                'product_id': stock.product_id.id,
                'qty_purchase': qty_purchase,
                'qty_min': stock.min_stock,
                'qty_buffer': stock.buffer_stock,
                'partner_id': stock.vendor_id.id,
                'taxes_id': stock.vendor_id.tax_id.id if stock.vendor_id.tax_id else False,
                'price': price,
                'product_public_category_id': stock.product_id.product_public_category_id.id,
                'brand_id': stock.product_id.brand_id.id,
            })

        self.env['automatic.purchase.line'].create(lines_to_create)

        return {
            'type': 'ir.actions.act_window',
            'res_model': 'automatic.purchase',
            'view_mode': 'form',
            'res_id': automatic_purchase.id,
            'target': 'current',
        }

    def init(self):
        tools.drop_view_if_exists(self.env.cr, self._table)
        self.env.cr.execute(f"""
            CREATE OR REPLACE VIEW {self._table} AS (
                SELECT
                    vmsbm.id AS id,
                    vmsbm.product_id,
                    vmsbm.min_stock,
                    vmsbm.buffer_stock,
                    vmsbm.vendor_id,

                    -- FINAL ONHAND (boleh override di sini)
                    COALESCE(SUM(sq.quantity), 0.0) AS qty_onhand,

                    vmsbm.incoming_qty AS qty_incoming,
                    vmsbm.outgoing_qty AS qty_outgoing,

                    -- DIFF STOCK FINAL
                    (
                        COALESCE(SUM(sq.quantity), 0.0)
                        + vmsbm.incoming_qty
                        - vmsbm.outgoing_qty
                        - vmsbm.buffer_stock
                    ) AS diff_stock,

                    -- FINAL STOCK STATUS
                    CASE
                        WHEN (
                            COALESCE(SUM(sq.quantity), 0.0)
                            + vmsbm.incoming_qty - vmsbm.outgoing_qty
                        ) <= vmsbm.min_stock
                            THEN 'BUY'

                        WHEN (
                                COALESCE(SUM(sq.quantity), 0.0)
                                + vmsbm.incoming_qty
                                - vmsbm.outgoing_qty
                            ) >= vmsbm.min_stock
                        AND (
                                COALESCE(SUM(sq.quantity), 0.0)
                                + vmsbm.incoming_qty
                                - vmsbm.outgoing_qty
                            ) < vmsbm.buffer_stock
                        THEN 'HOLD'

                        WHEN (
                            COALESCE(SUM(sq.quantity), 0.0)
                            + vmsbm.incoming_qty - vmsbm.outgoing_qty
                        ) > vmsbm.buffer_stock
                            THEN 'READY STOCK'

                        ELSE 'READY STOCK'
                    END AS stock_status

                FROM view_buffer_stock_below_min vmsbm
                LEFT JOIN stock_quant sq
                    ON sq.product_id = vmsbm.product_id
                AND sq.location_id = 55

                GROUP BY
                    vmsbm.id,
                    vmsbm.product_id,
                    vmsbm.min_stock,
                    vmsbm.buffer_stock,
                    vmsbm.vendor_id,
                    vmsbm.incoming_qty,
                    vmsbm.outgoing_qty
            )
        """)