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
from decimal import Decimal
import logging

logger = logging.getLogger(__name__)

class PositionVar(ModelSQL, ModelView):
    "Position var"
    __name__ = 'risk.position.var'

    var = fields.Many2One('risk.var',"Var")
    position = fields.Many2One('risk.position',"Position")
    d_var = fields.Numeric("VaR")
    d_cvar = fields.Numeric("CVaR")
    d_cdar = fields.Numeric("CDaR")
    whatif = fields.Numeric("Whatif")

    @classmethod
    def default_whatif(cls):
        return 1


class Position(ModelSQL, ModelView):
    "Position"
    __name__ = 'risk.position'

    commodity = fields.Char("Commodity")
    type = fields.Char("Type")
    trader = fields.Char("Trader")
    reference = fields.Char("Reference")
    product = fields.Char("Product")
    origin = fields.Char("Origin")
    future_market = fields.Char("Future market")
    currency = fields.Char("Currency")
    month = fields.Char("Month")
    ct_type = fields.Char("Ct type")
    quantity = fields.Numeric("Quantity")
    basis = fields.Numeric("Basis")
    price = fields.Numeric("Price")
    pricing_type = fields.Char("Pricing type")
    var = fields.Numeric("VaR")
    cvar = fields.Numeric("CVaR")
    cdar = fields.Numeric("CDaR")
    whatif = fields.Numeric("Whatif")

    @classmethod
    def default_whatif(cls):
        return 0

    @classmethod
    def search_rec_name(cls, name, clause):
        _, operator, operand, *extra = clause
        if operator.startswith('!') or operator.startswith('not '):
            bool_op = 'AND'
        else:
            bool_op = 'OR'
        code_value = operand
        if operator.endswith('like') and is_full_text(operand):
            code_value = lstrip_wildcard(operand)
        return [bool_op,
            ('commodity', operator, operand, *extra),
            ('trader', operator, operand, *extra),
            ('product', operator, operand, *extra),
            ('origin', operator, operand, *extra),
            ]

class PositionReport(ModelSQL, ModelView):
    "Position report"
    __name__ = 'risk.position.report'

    commodity = fields.Char("Commodity")
    type = fields.Char("Type")
    trader = fields.Char("Trader")
    reference = fields.Char("Reference")
    product = fields.Char("Product")
    origin = fields.Char("Origin")
    future_market = fields.Char("Future market")
    currency = fields.Char("Currency")
    month = fields.Char("Month")
    ct_type = fields.Char("Ct type")
    quantity = fields.Numeric("Quantity")
    basis = fields.Numeric("Basis")
    price = fields.Numeric("Price")
    pricing_type = fields.Char("Pricing type")
    var = fields.Numeric("VaR")
    cvar = fields.Numeric("CVaR")
    cdar = fields.Numeric("CDaR")
    var_amount = fields.Numeric("VaR amt")
    cvar_amount = fields.Numeric("CVaR amt")
    cdar_amount = fields.Numeric("CDaR amt")
    whatif = fields.Numeric("Whatif")

    @classmethod
    def table_query(cls):
        pool = Pool()
        Pos = pool.get('risk.position')
        pos = Pos.__table__()
        Pos_var = pool.get('risk.position.var')
        pos2 = Pos_var.__table__()
        context = Transaction().context
        trader = context.get('trader')
        product = context.get('product')
        month = context.get('month')
        market = context.get('market')
        asof = context.get('asof')
        todate = context.get('todate')
        var = context.get('var')
        groupby = context.get('groupby')
        wh = (((pos.quantity >= 0) & (pos.create_date >= asof) & ((pos.create_date-datetime.timedelta(1)) <= todate)))
        if trader:
            wh &= (pos.trader == trader)
        if product:
            wh &= (pos.product == product)
        if month:
            wh &= (pos.month == month)
        if market:
            wh &= (pos.future_market == market)
        if var:
            wh &= (pos2.var == var)
        
        if groupby == None:
            query = pos.join(pos2,'LEFT',condition=pos.id == pos2.position).select(
                Literal(0).as_('create_uid'),
                CurrentTimestamp().as_('create_date'),
                Literal(None).as_('write_uid'),
                Literal(None).as_('write_date'),
                pos.id.as_('id'),
                pos.commodity.as_('commodity'),
                pos.type.as_('type'),
                pos.trader.as_('trader'),
                pos.reference.as_('reference'),
                pos.product.as_('product'),
                pos.origin.as_('origin'),
                pos.future_market.as_('future_market'),
                pos.currency.as_('currency'),
                pos.month.as_('month'),
                pos.ct_type.as_('ct_type'),
                pos.quantity.as_('quantity'),
                pos.basis.as_('basis'),
                pos.price.as_('price'),
                pos.pricing_type.as_('pricing_type'),
                pos2.d_var.as_('var'),
                pos2.d_cvar.as_('cvar'),
                pos2.d_cdar.as_('cdar'),
                (pos2.d_var * pos.quantity * pos.price * pos2.whatif).as_('var_amount'),
                (pos2.d_cvar * pos.quantity * pos.price).as_('cvar_amount'),
                (pos2.d_cdar * pos.quantity * pos.price).as_('cdar_amount'),
                (pos.whatif).as_('whatif'),
                where=wh)
        elif groupby == 'commo':
            query = pos.join(pos2,'LEFT',condition=pos.id == pos2.position).select(
                Literal(0).as_('create_uid'),
                CurrentTimestamp().as_('create_date'),
                Literal(None).as_('write_uid'),
                Literal(None).as_('write_date'),
                Max(pos.id).as_('id'),
                pos.commodity.as_('commodity'),
                Max(pos.type).as_('type'),
                Max(pos.trader).as_('trader'),
                Max(pos.reference).as_('reference'),
                Max(pos.product).as_('product'),
                Max(pos.origin).as_('origin'),
                Max(pos.future_market).as_('future_market'),
                Max(pos.currency).as_('currency'),
                Max(pos.month).as_('month'),
                Max(pos.ct_type).as_('ct_type'),
                Sum(pos.quantity).as_('quantity'),
                Avg(pos.basis).as_('basis'),
                Avg(pos.price).as_('price'),
                Max(pos.pricing_type).as_('pricing_type'),
                Avg(pos2.d_var).as_('var'),
                Avg(pos2.d_cvar).as_('cvar'),
                Avg(pos2.d_cdar).as_('cdar'),
                Sum(pos2.d_var * pos.quantity * pos.price * pos2.whatif).as_('var_amount'),
                Sum(pos2.d_cvar * pos.quantity * pos.price).as_('cvar_amount'),
                Sum(pos2.d_cdar * pos.quantity * pos.price).as_('cdar_amount'),
                Avg(pos.whatif).as_('whatif'),
                where=wh,
                group_by=[pos.commodity])
        elif groupby == 'market':
            query = pos.join(pos2,'LEFT',condition=pos.id == pos2.position).select(
                Literal(0).as_('create_uid'),
                CurrentTimestamp().as_('create_date'),
                Literal(None).as_('write_uid'),
                Literal(None).as_('write_date'),
                Max(pos.id).as_('id'),
                Max(pos.commodity).as_('commodity'),
                Max(pos.type).as_('type'),
                Max(pos.trader).as_('trader'),
                Max(pos.reference).as_('reference'),
                Max(pos.product).as_('product'),
                Max(pos.origin).as_('origin'),
                pos.future_market.as_('future_market'),
                Max(pos.currency).as_('currency'),
                Max(pos.month).as_('month'),
                Max(pos.ct_type).as_('ct_type'),
                Sum(pos.quantity).as_('quantity'),
                Avg(pos.basis).as_('basis'),
                Avg(pos.price).as_('price'),
                Max(pos.pricing_type).as_('pricing_type'),
                Avg(pos2.d_var).as_('var'),
                Avg(pos2.d_cvar).as_('cvar'),
                Avg(pos2.d_cdar).as_('cdar'),
                Sum(pos2.d_var * pos.quantity * pos.price * pos2.whatif).as_('var_amount'),
                Sum(pos2.d_cvar * pos.quantity * pos.price).as_('cvar_amount'),
                Sum(pos2.d_cdar * pos.quantity * pos.price).as_('cdar_amount'),
                Avg(pos.whatif).as_('whatif'),
                where=wh,
                group_by=[pos.future_market])
        elif groupby == 'month':
            query = pos.join(pos2,'LEFT',condition=pos.id == pos2.position).select(
                Literal(0).as_('create_uid'),
                CurrentTimestamp().as_('create_date'),
                Literal(None).as_('write_uid'),
                Literal(None).as_('write_date'),
                Max(pos.id).as_('id'),
                Max(pos.commodity).as_('commodity'),
                Max(pos.type).as_('type'),
                Max(pos.trader).as_('trader'),
                Max(pos.reference).as_('reference'),
                Max(pos.product).as_('product'),
                Max(pos.origin).as_('origin'),
                pos.future_market.as_('future_market'),
                Max(pos.currency).as_('currency'),
                pos.month.as_('month'),
                Max(pos.ct_type).as_('ct_type'),
                Sum(pos.quantity).as_('quantity'),
                Avg(pos.basis).as_('basis'),
                Avg(pos.price).as_('price'),
                Max(pos.pricing_type).as_('pricing_type'),
                Avg(pos2.d_var).as_('var'),
                Avg(pos2.d_cvar).as_('cvar'),
                Avg(pos2.d_cdar).as_('cdar'),
                Sum(pos2.d_var * pos.quantity * pos.price * pos2.whatif).as_('var_amount'),
                Sum(pos2.d_cvar * pos.quantity * pos.price).as_('cvar_amount'),
                Sum(pos2.d_cdar * pos.quantity * pos.price).as_('cdar_amount'),
                Avg(pos.whatif).as_('whatif'),
                where=wh,
                group_by=[pos.future_market,pos.month])
        return query
    
    @classmethod
    def view_attributes(cls):
        return super().view_attributes() + [
            ('/tree/field[@name="trader"]', 'tree_invisible',
                (Eval('groupby')!=None)),
            ('/tree/field[@name="product"]', 'tree_invisible',
                (Eval('groupby')!=None)),
            ('/tree/field[@name="future_market"]', 'tree_invisible',
                (Eval('groupby')=='commo')),
            ('/tree/field[@name="month"]', 'tree_invisible',
                (Eval('groupby').in_(['market', 'commo']))),
            ]

class PositionProcess(ModelSQL, ModelView):
    "Position process"
    __name__ = 'risk.position.process'

    future_market = fields.Char("Future market")
    month = fields.Char("Month")
    quantity = fields.Numeric("Quantity")
    
    @classmethod
    def table_query(cls):
        pool = Pool()
        Pos = pool.get('risk.position')
        pos = Pos.__table__()
        wh = ((pos.pricing_type != 'Priced') & (pos.ct_type != 'Inventory') & (pos.quantity > 0))
        query = pos.select(
            Literal(0).as_('create_uid'),
            CurrentTimestamp().as_('create_date'),
            Literal(None).as_('write_uid'),
            Literal(None).as_('write_date'),
            Max(pos.id).as_('id'),
            pos.future_market.as_('future_market'),
            pos.month.as_('month'),
            Sum(pos.quantity).as_('quantity'),
            where=wh,
            group_by=[pos.future_market,pos.month])
        return query

class PositionContext(ModelView):
    "Lot Context"
    __name__ = 'risk.position.context'

    asof = fields.Date("As of")
    todate = fields.Date("To")
    trader =  fields.Selection([
        (None, ''),
        ("Simon Bourqui", "Simon Bourqui"),
        ("Jean-Christophe Mani", "Jean-Christophe Mani"),
        ("Gregoire Davidoff", "Gregoire Davidoff"),
        ("Pablo Rapun","Pablo Rapun"),
        ("Riccardo Zanin","Riccardo Zanin"),
        ("Antoine Bonnot","Antoine Bonnot"),
        ("Eric Bourgeois","Eric Bourgeois")
        ],"Trader")
    product =  fields.Selection([
        (None, ''),
        ("Coffee", "Coffee"),
        ("Cocoa", "Cocoa"),
        ],"Product")
    month = fields.Selection([
        (None, ''),
        ("Mar-24", "Mar-24"),
        ("Jul-24", "Jul-24"),
        ("Mar-26", "Mar-26"),
        ("Dec-24","Dec-24"),
        ("Dec-23","Dec-23"),
        ("Nov-24","Nov-24"),
        ("Dec-25","Dec-25"),
        ("Sep-24", "Sep-24"),
        ("Jul-25", "Jul-25"),
        ("Mar-25", "Mar-25"),
        ("May-25","May-25"),
        ("May-24","May-24"),
        ("Sep-25","Sep-25")
        ],"Month")
    market = fields.Selection([
        (None, ''),
        ("Coffee ICE US", "Coffee ICE US"),
        ("Cocoa ICE US", "Cocoa ICE US"),
        ("Coffee ICE EU", "Coffee ICE EU"),
        ("Cocoa ICE EU", "Cocoa ICE EU")
        ],"Market")
    var = fields.Many2One('risk.var',"VaR template",domain=[('execute','=',True)])
    groupby = fields.Selection([
        (None, ''),
        ("commo", "Commodity"),
        ("market", "Market"),
        ("month", "Month"),
    ],"Group by")
    
    @classmethod
    def default_asof(cls):
        pool = Pool()
        Date = pool.get('ir.date')
        return Date.today().replace(day=1,month=1,year=1999)

    @classmethod
    def default_todate(cls):
        pool = Pool()
        Date = pool.get('ir.date')
        return Date.today()

    @classmethod
    def default_var(cls):
        Var = Pool().get('risk.var')
        var = Var.search([('execute','=',True)])
        if var:
            return var[0].id