from odoo import models, fields, tools, api

class ProductMonitoring(models.Model):
    _name = 'product.monitoring'
    _auto = False
    _rec_name = 'product_id'

    id = fields.Integer()
    product_id = fields.Many2one('product.product', string='Product')
    outgoing_qty = fields.Float(string="Outgoing", related='product_id.outgoing_qty')
    incoming_qty = fields.Float(string="Incoming", related='product_id.incoming_qty')
    qty_available = fields.Float(string="On Hand", related='product_id.qty_available')
    qty_upcoming = fields.Float(string="Upcoming", related='product_id.qty_upcoming')
    status_stock = fields.Selection([
        ('outgoing_gt_stock', 'Outgoing > Stock'),
        ('outgoing_lt_stock', 'Outgoing < Stock'),
    ], string="Status Stock", compute='_compute_status_stock')

    def _compute_status_stock(self):
        for product in self:
            product.status_stock = 'outgoing_lt_stock' if product.outgoing_qty <= product.qty_upcoming else 'outgoing_gt_stock'

    def init(self):
        tools.drop_view_if_exists(self.env.cr, self._table)
        self.env.cr.execute("""
            CREATE OR REPLACE VIEW %s AS (
                SELECT 
                    sm.product_id AS id,
                    sm.product_id AS product_id
                FROM stock_move sm
                LEFT JOIN stock_picking sp ON sm.picking_id = sp.id
                WHERE sp.state NOT IN ('cancel', 'done') AND sm.product_id IS NOT null
                GROUP BY sm.product_id
            )
        """ % self._table)