from odoo import models, fields, tools
from odoo.exceptions import AccessError, UserError, ValidationError
from datetime import timedelta, date

class PurchasingJob(models.Model):
    _name = 'purchasing.job'
    _description = 'Procurement Monitoring by Product'
    _auto = False
    _rec_name = 'product'

    id = fields.Integer(string='ID', readonly=True)
    item_code = fields.Char(string='Item Code')
    product = fields.Char(string='Product')
    onhand = fields.Float(string='On Hand')
    incoming = fields.Float(string='Incoming')
    outgoing = fields.Float(string='Outgoing')
    action = fields.Char(string='Action')
    product_id = fields.Many2one('product.product', string='Product')
    vendor_id = fields.Many2one('res.partner', string='Vendor')

    def create_automatic_purchase(self):
        if not self:
            raise UserError("No Purchasing Job selected.")

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

        lines_to_create = []

        for stock in self:
            manage_stock = self.env['manage.stock'].search([
                ('product_id', '=', stock.product_id.id)
            ], limit=1)

            qty_purchase = stock.outgoing - (stock.onhand + stock.incoming) 

            qty_purchase = max(qty_purchase, 0.0)

            pricelist = self.env['purchase.pricelist'].search([
                ('product_id', '=', stock.product_id.id),
                ('vendor_id', '=', stock.vendor_id.id)
            ], limit=1)

            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': manage_stock.min_stock,
                'qty_buffer': manage_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,
                'subtotal': subtotal,
            })

        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("""
            CREATE OR REPLACE VIEW %s AS
            SELECT
                row_number() OVER () AS id,
                a.item_code,
                a.product,
                a.onhand,
                a.incoming,
                a.outgoing,
                CASE
                    WHEN (a.incoming + a.onhand) < a.outgoing THEN 'kurang'
                    ELSE 'cukup'
                END AS action,
                a.product_id,
                pp2.vendor_id
            FROM (
                SELECT 
                    COALESCE(pp.default_code, pt.default_code) AS item_code,
                    pt.name AS product,
                    get_qty_onhand(pp.id::numeric) AS onhand,
                    get_qty_incoming(pp.id::numeric) AS incoming,
                    get_qty_outgoing(pp.id::numeric) AS outgoing,
                    pp.id AS product_id
                FROM stock_move sm
                JOIN stock_picking sp ON sp.id = sm.picking_id
                JOIN product_product pp ON pp.id = sm.product_id
                JOIN product_template pt ON pt.id = pp.product_tmpl_id
                WHERE sp.state IN ('draft', 'waiting', 'confirmed', 'assigned')
                AND sp.name LIKE '%%OUT%%'
                AND sm.location_id = 55
                GROUP BY pp.id, pp.default_code, pt.default_code, pt.name
            ) a
            LEFT JOIN LATERAL (
                SELECT vendor_id 
                FROM purchase_pricelist 
                WHERE product_id = a.product_id
                ORDER BY id ASC 
                LIMIT 1
            ) pp2 ON true
        """ % self._table)