from odoo import fields, models, api, tools
import logging

_logger = logging.getLogger(__name__)


class SaleMonitoringDetail(models.Model):
    _name = 'procurement.monitoring.detail'
    _auto = False
    _rec_name = 'sale_order_id'

    id = fields.Integer()
    sale_order_id = fields.Many2one("sale.order", string="Sale Order")
    partner_id = fields.Many2one("res.partner", string="Customer")
    user_id = fields.Many2one("res.users", string="Salesperson")
    product_id = fields.Many2one("product.product", string="Product")
    qty_so = fields.Integer(string="Qty SO")
    qty_reserved = fields.Integer(string="Qty Reserved")
    qty_available = fields.Integer(string="Qty Available")
    qty_suggest = fields.Integer(string="Qty Suggest")
    qty_po = fields.Integer(string='Qty PO')
    date_order = fields.Datetime(string="Date Order")
    status = fields.Char(string="Status")
    po_ids = fields.Many2many('purchase.order', string='PO', compute='_compute_po')
    note = fields.Char(string="Note")

    def _compute_po(self):
        for line in self:
            query = [
                ('order_id.sale_order_id', '=', line.sale_order_id.id),
                ('order_id.approval_status', '=', 'approved'),
                ('product_id', '=', line.product_id.id),
            ]
            pos = self.env['purchase.order.line'].search(query)
            po_ids = []
            for po in pos:
                po_ids.append(po.order_id.id)
            line.po_ids = po_ids

    def init(self):
        tools.drop_view_if_exists(self.env.cr, self._table)
        self.env.cr.execute("""
        CREATE OR REPLACE VIEW %s AS (
            SELECT 
                *,
                a.qty_so-a.qty_reserved as qty_suggest,
                case when a.qty_po >= a.qty_so then 'cukup'
                when a.qty_so-a.qty_reserved > 0 then 'harus beli'
                else 'cukup' end as status
            FROM
            (
                SELECT 
                    sol.id AS id,
                    so.id AS sale_order_id,
                    so.partner_id as partner_id,
                    so.user_id,
                    p.id AS product_id,
                    sol.product_uom_qty AS qty_so,
                    get_qty_available(sol.product_id) as qty_available,
                    get_qty_reserved(so.id, sol.product_id) as qty_reserved,
                    get_qty_po(so.id, sol.product_id) AS qty_po,
                    so.date_order AS date_order,
                    sol.note_procurement as note
                FROM sale_order so
                    JOIN sale_order_line sol ON sol.order_id = so.id
                    JOIN product_product p ON p.id = sol.product_id
                    JOIN product_template pt ON pt.id = p.product_tmpl_id
                WHERE pt.type IN ('consu','product') 
                    AND so.state IN ('sale','done') 
                    AND so.create_date >= '2022-08-10'
                    and so.so_status not in('terproses')
            ) a
            --where a.qty_po < a.qty_so
        )
        """ % self._table)