import stdnum.exceptions
from sql import Column, Literal
import datetime
from sql.aggregate import Count, Max, Min, Sum, Avg, BoolOr
from sql.conditionals import Case
from stdnum import get_cc_module
from sql.functions import CharLength, CurrentTimestamp, DateTrunc, Extract
from trytond.i18n import gettext
from trytond.model import (
    DeactivableMixin, Index, ModelSQL, ModelView, MultiValueMixin, Unique,
    ValueMixin, convert_from, fields, sequence_ordered)
from trytond.model.exceptions import AccessError
from trytond.pool import Pool
from trytond.pyson import Bool, Eval
from trytond.tools import is_full_text, lstrip_wildcard
from trytond.transaction import Transaction, inactive_records
from trytond.wizard import Button, StateTransition, StateView, Wizard


class PriceValue(
        DeactivableMixin, ModelSQL, ModelView,
        MultiValueMixin):
    "Price Value"
    __name__ = 'price.price_value'

    price = fields.Many2One(
        'price.price', "Price", required=True, ondelete='CASCADE',
        states={
            'readonly': Eval('id', 0) > 0,
        })

    price_index = fields.Function(fields.Char("Price index",
        states={
            'readonly': Eval('id', 0) > 0,
    }), 'get_price_index')

    price_date = fields.Date("Price date")
    price_value = fields.Float("Price value")
    open_price = fields.Float("Open price")
    low_price = fields.Float("Low price")
    high_price = fields.Float("High price")

    def get_price_index(self, name):
        if self.price:
            return self.price.price_index
        return None

class PriceValueReport(
        ModelSQL, ModelView):
    "Price Value Report"
    __name__ = 'price.price_value.report'

    r_price = fields.Many2One('price.price', "Price")
    r_price_index = fields.Char("Price index")
    r_price_desc = fields.Char("Description")
    r_price_date = fields.Date("Price date")
    r_price_value = fields.Float("Price")
    r_open_price = fields.Float("Open price")
    r_low_price = fields.Float("Low price")
    r_high_price = fields.Float("High price")

    @classmethod
    def table_query(cls):
        pool = Pool()
        PriceValue = pool.get('price.price_value')
        Price = pool.get('price.price')
        pp = Price.__table__()
        pv = PriceValue.__table__()
        
        context = Transaction().context
        dt = context.get('dt')
        user = context.get('user')

        wh = ((pp.price_area == 'platts') & (pv.price_date == dt - datetime.timedelta(1)))
        #if user:
        #    wh &= ()
        
        query = pv.join(pp, condition=pv.price == pp.id).select(
            Literal(0).as_('create_uid'),
            CurrentTimestamp().as_('create_date'),
            Literal(None).as_('write_uid'),
            Literal(None).as_('write_date'),
            pv.id.as_('id'),
            pv.price.as_('r_price'),
            pp.price_index.as_('r_price_index'),
            pp.price_desc.as_('r_price_desc'),
            pv.price_date.as_('r_price_date'),
            pv.price_value.as_('r_price_value'),
            pv.open_price.as_('r_open_price'),
            pv.low_price.as_('r_low_price'),
            pv.high_price.as_('r_high_price'),
            where=wh)

        return query

class PVRContext(ModelView):
    "Price Value Report Context"
    __name__ = 'price.price_value.report.context'

    dt = fields.Date("Value date")
    user =  fields.Many2One('res.user', "User")
    
    @classmethod
    def default_dt(cls):
        pool = Pool()
        Date = pool.get('ir.date')
        return Date.today()#.replace(day=1,month=1,year=1999)

class PriceValueReport2(
        ModelSQL, ModelView):
    "Price Value Report"
    __name__ = 'price.price_value.report2'

    r_price = fields.Many2One('price.price', "Price")
    r_price_index = fields.Char("Price index")
    r_price_desc = fields.Char("Description")
    r_price_date = fields.Date("Price date")
    r_price_value = fields.Float("Price")
    r_open_price = fields.Float("Open price")
    r_low_price = fields.Float("Low price")
    r_high_price = fields.Float("High price")
    r_naphta_fob_low = fields.Float("Naph F Med l", digits=(1,2), help="Naphta Fob PAAAI00 low")
    r_naphta_fob_high = fields.Float("Naph F Med h", digits=(1,2), help="Naphta Fob PAAAI00 high")
    r_naphta_fob_mid = fields.Float("Naph F Med m", digits=(1,2), help="Naphta Fob PAAAI00 mid")
    r_jet_fob_low = fields.Float("Jet F Ara l", digits=(1,2), help="Jet Fob AAIDL00 low")
    r_jet_fob_high = fields.Float("Jet F Ara h", digits=(1,2), help="Jet Fob AAIDL00 high")
    r_jet_fob_mid = fields.Float("Jet F Ara m", digits=(1,2), help="Jet Fob AAIDL00 mid")
    r_naphta_cif_low = fields.Float("Naph C Med l", digits=(1,2), help="Naphta Cif PAAAH00 low")
    r_naphta_cif_high = fields.Float("Naph C Med h", digits=(1,2), help="Naphta Cif PAAAH00 high")
    r_naphta_cif_mid = fields.Float("Naph C Med m", digits=(1,2), help="Naphta Cif PAAAH00 mid")
    r_jet_cif_low = fields.Float("Jet C Ara l", digits=(1,2), help="Naphta Cif AAZBN00 low")
    r_jet_cif_high = fields.Float("Jet C Ara h", digits=(1,2), help="Naphta Cif AAZBN00 high")
    r_jet_cif_mid = fields.Float("Jet C Ara m", digits=(1,2), help="Naphta Cif AAZBN00 mid")
    r_eur_fob_low = fields.Float("Ebob F Rdm l", digits=(1,2), help="Eurobob Fob AAQZV00 low")
    r_eur_fob_high = fields.Float("Ebob F Rdm h", digits=(1,2), help="Eurobob Fob AAQZV00 high")
    r_eur_fob_mid = fields.Float("Ebob F Rdm m", digits=(1,2), help="Eurobob Fob AAQZV00 mid")
    r_gaz_cif_low = fields.Float("Gas10 C Ara l", digits=(1,2), help="Gasoline Cif AAXFQ00 low")
    r_gaz_cif_high = fields.Float("Gas10 C Ara h", digits=(1,2), help="Gasoline Cif AAXFQ00 high")
    r_gaz_cif_mid = fields.Float("Gas10 C Ara m", digits=(1,2), help="Gasoline Cif AAXFQ00 mid")
    r_brent_1 = fields.Float("Brent1 m", digits=(1,2), help="Brent AAYES00 mid")
    r_brent_2 = fields.Float("Brent2 m", digits=(1,2), help="Brent AAYET00 mid")
    
    @classmethod
    def __setup__(cls):
       super(PriceValueReport2, cls).__setup__()
       cls._order.insert(0, ('r_price_date', 'DESC'))

    @classmethod
    def table_query(cls):
        pool = Pool()
        PriceValue = pool.get('price.price_value')
        Price = pool.get('price.price')
        pp = Price.__table__()
        pv = PriceValue.__table__()
        
        context = Transaction().context
        dtfrom = context.get('dtfrom')
        dtto = context.get('dtto')
        #user = context.get('user')

        wh = ((pp.price_area == 'platts') & (pv.price_date <= dtto) & (pv.price_date >= dtfrom))
        
        query = pv.join(pp, condition=pv.price == pp.id).select(
            Literal(0).as_('create_uid'),
            CurrentTimestamp().as_('create_date'),
            Literal(None).as_('write_uid'),
            Literal(None).as_('write_date'),
            Max(pv.id).as_('id'),
            Max(pv.price).as_('r_price'),
            Max(pp.price_index).as_('r_price_index'),
            Max(pp.price_desc).as_('r_price_desc'),
            pv.price_date.as_('r_price_date'),
            Max(pv.price_value).as_('r_price_value'),
            Max(pv.open_price).as_('r_open_price'),
            Max(pv.low_price).as_('r_low_price'),
            Max(pv.high_price).as_('r_high_price'),
            Max(Case((pv.price==15, pv.low_price),else_=0)).as_('r_naphta_fob_low'),
            Max(Case((pv.price==15, pv.high_price),else_=0)).as_('r_naphta_fob_high'),
            Max(Case((pv.price==15, (pv.high_price-pv.low_price)/2 + pv.low_price),else_=0)).as_('r_naphta_fob_mid'),
            Max(Case((pv.price==9, pv.low_price),else_=0)).as_('r_jet_fob_low'),
            Max(Case((pv.price==9, pv.high_price),else_=0)).as_('r_jet_fob_high'),
            Max(Case((pv.price==9, (pv.high_price-pv.low_price)/2 + pv.low_price),else_=0)).as_('r_jet_fob_mid'),
            Max(Case((pv.price==16, pv.low_price),else_=0)).as_('r_naphta_cif_low'),
            Max(Case((pv.price==16, pv.high_price),else_=0)).as_('r_naphta_cif_high'),
            Max(Case((pv.price==16, (pv.high_price-pv.low_price)/2 + pv.low_price),else_=0)).as_('r_naphta_cif_mid'),
            Max(Case((pv.price==17, pv.low_price),else_=0)).as_('r_jet_cif_low'),
            Max(Case((pv.price==17, pv.high_price),else_=0)).as_('r_jet_cif_high'),
            Max(Case((pv.price==17, (pv.high_price-pv.low_price)/2 + pv.low_price),else_=0)).as_('r_jet_cif_mid'),
            Max(Case((pv.price==11, pv.low_price),else_=0)).as_('r_eur_fob_low'),
            Max(Case((pv.price==11, pv.high_price),else_=0)).as_('r_eur_fob_high'),
            Max(Case((pv.price==11, (pv.high_price-pv.low_price)/2 + pv.low_price),else_=0)).as_('r_eur_fob_mid'),
            Max(Case((pv.price==18, pv.low_price),else_=0)).as_('r_gaz_cif_low'),
            Max(Case((pv.price==18, pv.high_price),else_=0)).as_('r_gaz_cif_high'),
            Max(Case((pv.price==18, (pv.high_price-pv.low_price)/2 + pv.low_price),else_=0)).as_('r_gaz_cif_mid'),
            Max(Case((pv.price==19, (pv.high_price-pv.low_price)/2 + pv.low_price),else_=0)).as_('r_brent_1'),
            Max(Case((pv.price==20, (pv.high_price-pv.low_price)/2 + pv.low_price),else_=0)).as_('r_brent_2'),
            
            where=wh,
            group_by=[pv.price_date])

        return query

class PVRContext2(ModelView):
    "Price Value Report Context"
    __name__ = 'price.price_value.report.context2'

    dtfrom = fields.Date("From")
    dtto = fields.Date("To")
    user =  fields.Many2One('res.user', "User")
    
    @classmethod
    def default_dtto(cls):
        pool = Pool()
        Date = pool.get('ir.date')
        return Date.today() - datetime.timedelta(1)#.replace(day=1,month=1,year=1999)

    @classmethod
    def default_dtfrom(cls):
        pool = Pool()
        Date = pool.get('ir.date')
        return Date.today() - datetime.timedelta(30)#.replace(day=1,month=1,year=1999)