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

_logger = logging.getLogger(__name__)


class LeadsMonitoring(models.Model):
    _name = 'leads.monitoring'
    _auto = False
    _rec_name = 'lead_id'

    id = fields.Integer()
    lead_id = fields.Many2one('crm.lead', string='Leads')
    create_date = fields.Datetime(string='Created')
    lead_name = fields.Char(string='Lead')
    contact_name = fields.Char(string='Contact Name')
    partner_name = fields.Char(string='Partner Name')
    email = fields.Char(string='Email')
    phone = fields.Char(string='Phone')
    mobile = fields.Char(string='Mobile')
    salesperson_id = fields.Many2one('res.partner', string='Salesperson')
    salesperson = fields.Char(string='Sales Name')
    type = fields.Char(string='Type')
    priority = fields.Char(string='Priority')
    date_last_stage_update = fields.Datetime(string='Last Stage Updated')
    stage_status = fields.Char(string='Status')
    total_quotation = fields.Float(string='Quotation')
    total_sales = fields.Float(string='Sales')

    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 Materialized View..." % 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 cl.id, cl.id as lead_id, cl.create_date,cl.email_from as email, cl.phone, cl.mobile, cl.name as lead_name, 
                u.partner_id as salesperson_id, p.name as salesperson, cl.type, cl.priority, cl.date_last_stage_update,
                cl.contact_name, cl.partner_name,
                case 
                    when cl.stage_id = 2 then '3-Proses Quotation'
                    when cl.stage_id = 1 then '1-Lead / Potensi Baru'
                    when cl.stage_id = 4 then '5-Proses Berhasil'
                    when cl.stage_id = 3 then '2-Proses Lain, Visit, Etc'
                    when cl.stage_id = 5 then '4-Proses Negosiasi'
                    else '6-Undefined'
                end as stage_status,
                (select sum(so.amount_total) from sale_order so where so.opportunity_id = cl.id and so.state in ('draft', 'sent')) as total_quotation,
                (select sum(so.amount_total) from sale_order so where so.opportunity_id = cl.id and so.state in ('sale')) as total_sales
                from crm_lead cl
                left join res_users u on u.id = cl.user_id
                left join res_partner p on p.id = u.partner_id
                where cl.active = 'true' and cl.stage_id not in(7)
                order by cl.create_date desc
            )
        """ % self._table)