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

_logger = logging.getLogger(__name__)


class SaleMonitoringDetail(models.Model):
    _name = 'sale.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_po = fields.Integer(string="Qty PO")
    qty_po_received = fields.Integer(string="Qty PO Received")
    qty_so_delivered = fields.Integer(string="Qty SO Delivered")
    qty_so_invoiced = fields.Integer(string="Qty SO Invoiced")
    date_order = fields.Datetime(string="Date Order")
    status = fields.Char(string="Status")

    def init(self):
        self._drop_view()
        self.env.cr.execute("SELECT matviewname from pg_matviews where schemaname = 'public' and matviewname = '%s'" % self._table)
        materialized_view = self.env.cr.fetchone()
        if materialized_view is None:
            self._init_materialized_view()

    def action_refresh(self):
        _logger.info("Refresh %s View Starting..." % self._table)
        self.env.cr.execute("REFRESH MATERIALIZED VIEW %s" % self._table)
        _logger.info("Refresh %s View Success" % self._table)

    def _drop_view(self):
        self.env.cr.execute("SELECT viewname from pg_views where schemaname = 'public' and viewname = '%s'" % self._table)
        standard_view = self.env.cr.fetchone()
        if standard_view is not None:
            self.env.cr.execute("DROP VIEW %s CASCADE" % self._table)

    def _init_materialized_view(self):
        self.env.cr.execute("""
            CREATE MATERIALIZED VIEW %s AS (
            SELECT 
                *, 
                CASE
                    WHEN qty_po < qty_so AND qty_po <= 0 THEN 'Belum PO sama sekali'
                    WHEN qty_po < qty_so THEN 'Belum PO full'
                    WHEN qty_po_received < qty_so and qty_po_received <= 0 THEN 'Belum Received sama sekali'
                    WHEN qty_po_received < qty_so THEN 'Belum Received full'
                    WHEN qty_to_delivered = qty_so THEN 'SIAP KIRIM'
                    WHEN qty_to_delivered < qty_so and qty_to_delivered > 0 THEN 'KIRIM SISANYA'
                    ELSE 'Belum Invoiced'
                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, 
                    sol.qty_delivered AS qty_so_delivered, 
                    get_qty_to_delivered(sol.id) as qty_to_delivered,
                    sol.qty_invoiced AS qty_so_invoiced,
                    so.date_order AS date_order,
                    get_qty_po(so.id, sol.product_id) AS qty_po, 
                    get_qty_received(so.id, sol.product_id) AS qty_po_received
                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'
            ) a 
            WHERE 
            a.qty_so_delivered > a.qty_so_invoiced
                or a.qty_to_delivered > 0
            )
        """ % self._table)