from trytond.model import fields
from trytond.report import Report
from trytond.pool import Pool, PoolMeta
from trytond.pyson import Bool, Eval, Id, If
from trytond.model import (ModelSQL, ModelView)
from trytond.tools import is_full_text, lstrip_wildcard
from trytond.transaction import Transaction, inactive_records
from decimal import getcontext, Decimal, ROUND_HALF_UP
from sql.aggregate import Count, Max, Min, Sum, Avg, BoolOr
from sql.conditionals import Case
from sql import Column, Literal
from sql.functions import CurrentTimestamp, DateTrunc
from trytond.wizard import Button, StateTransition, StateView, Wizard
from itertools import chain, groupby
from operator import itemgetter
import datetime
import logging
from collections import defaultdict
from trytond.exceptions import UserWarning, UserError

logger = logging.getLogger(__name__)

VALTYPE = [
    ('priced', 'Price'),
    ('pur. priced', 'Pur. price'),
    ('pur. efp', 'Pur. efp'),
    ('sale priced', 'Sale price'),
    ('sale efp', 'Sale efp'),
    ('line fee', 'Line fee'),
    ('pur. fee', 'Pur. fee'),
    ('sale fee', 'Sale fee'),
    ('shipment fee', 'Shipment fee'),
    ('market', 'Market'),
    ('derivative', 'Derivative'),
]

class ValuationBase(ModelSQL):
    purchase = fields.Many2One('purchase.purchase',"Purchase")
    line = fields.Many2One('purchase.line',"Purch. Line")
    date = fields.Date("Date")
    type = fields.Selection(VALTYPE, "Type")
    reference = fields.Char("Reference")
    counterparty = fields.Many2One('party.party',"Counterparty")
    product = fields.Many2One('product.product',"Product")
    state = fields.Char("State")
    price = fields.Numeric("Price",digits=(16,4))
    currency = fields.Many2One('currency.currency',"Cur")
    quantity = fields.Numeric("Quantity",digits=(16,5))
    unit = fields.Many2One('product.uom',"Unit")
    amount = fields.Numeric("Amount",digits=(16,2))
    mtm = fields.Numeric("Mtm",digits=(16,2))
    strategy = fields.Many2One('mtm.strategy',"Strategy")
    lot = fields.Many2One('lot.lot',"Lot")
    base_amount = fields.Numeric("Base Amount",digits=(16,2))
    rate = fields.Numeric("Rate", digits=(16,6))

    @classmethod
    def _base_pnl(cls, *, line, lot, pnl_type, sale=None):
        Date = Pool().get('ir.date')

        values = {
            'purchase': line.purchase.id,
            'line': line.id,
            'type': pnl_type,
            'date': Date.today(),
            'lot': lot.id,
        }

        if sale:
            values['sale'] = sale.id

        return values
    
    @classmethod
    def _build_basis_pnl(cls, *, line, lot, sale_line, pc, sign):
        Currency = Pool().get('currency.currency')
        Date = Pool().get('ir.date')
        values = cls._base_pnl(
            line=line,
            lot=lot,
            sale=sale_line.sale if sale_line else None,
            pnl_type='sale priced' if sale_line else 'pur. priced'
        )

        qty = lot.get_current_quantity_converted()

        price = pc.price
        logger.info("TERMS:%s",line.terms)
        if line.terms:
            c = [t for t in line.terms if t.component == pc.price_component]
            logger.info("COMPONENTS:%s",c)
            if c:
                price = c[0].manual_price

        values.update({
            'reference': f"{pc.get_name()} / {pc.ratio}%",
            'price': round(price, 4),
            'counterparty': sale_line.sale.party.id if sale_line else line.purchase.party.id,
            'product': sale_line.product.id if sale_line else line.product.id,
        })

        # State
        if pc.unfixed_qt == 0:
            values['state'] = 'fixed'
        elif pc.fixed_qt == 0:
            values['state'] = 'unfixed'
        else:
            base = sale_line.quantity_theorical if sale_line else line.quantity_theorical
            values['state'] = f"part. fixed {round(pc.fixed_qt / Decimal(base) * 100, 0)}%"

        if price != None:
            amount = round(price * qty * Decimal(sign), 2)
            base_amount = amount
            currency = sale_line.sale.currency.id if sale_line else line.purchase.currency.id
            rate = Decimal(1)
            if line.purchase.company.currency != currency:
                with Transaction().set_context(date=Date.today()):
                    base_amount = Currency.compute(currency,amount, line.purchase.company.currency)
                    rate = round(amount / (base_amount if base_amount else 1),6)
            last_price = pc.get_last_price()
            # mtm = round(Decimal(last_price) * qty * Decimal(sign), 2) if last_price else Decimal(0)
            values.update({
                'quantity': round(qty, 5),
                'amount': amount,
                'base_amount': base_amount,
                'rate': rate,
                'mtm': None, #round(amount - (mtm * pc.ratio / 100), 2),
                'unit': sale_line.unit.id if sale_line else line.unit.id,
                'currency': currency,
            })

            return values

    @classmethod
    def _build_simple_pnl(cls, *, line, lot, sale_line, price, state, sign, pnl_type):
        Currency = Pool().get('currency.currency')
        Date = Pool().get('ir.date')
        values = cls._base_pnl(
            line=line,
            lot=lot,
            sale=sale_line.sale if sale_line else None,
            pnl_type=pnl_type
        )

        qty = lot.get_current_quantity_converted()
        amount = round(price * qty * Decimal(sign), 2)
        base_amount = amount
        currency = sale_line.sale.currency.id if sale_line else line.purchase.currency.id
        company_currency = sale_line.sale.company.currency if sale_line else line.purchase.company.currency
        rate = Decimal(1)
        if line.purchase.company.currency != currency:
            with Transaction().set_context(date=Date.today()):
                base_amount = Currency.compute(currency,amount, company_currency)
                if base_amount and amount:
                    rate = round(amount / base_amount,6)

        values.update({
            'price': round(price, 4),
            'quantity': round(qty, 5),
            'amount': amount,
            'base_amount': base_amount,
            'rate': rate,
            'mtm': Decimal(0),
            'state': state,
            'unit': sale_line.unit.id if sale_line else line.unit.id,
            'currency': currency,
            'counterparty': sale_line.sale.party.id if sale_line else line.purchase.party.id,
            'product': sale_line.product.id if sale_line else line.product.id,
            'reference': (
                'Sale/Physic' if lot.lot_type == 'physic'
                else 'Sale/Open' if sale_line
                else 'Purchase/Physic'
            ),
        })

        return values

    @classmethod
    def create_pnl_price_from_line(cls, line):
        price_lines = []
        LotQt = Pool().get('lot.qt')

        for lot in line.lots:

            if line.price_type == 'basis':
                for pc in line.price_summary or []:
                    values = cls._build_basis_pnl(line=line, lot=lot, sale_line=None, pc=pc, sign=-1)
                    if line.mtm:
                        for strat in line.mtm:
                            values['mtm'] = strat.get_mtm(line,values['quantity'])
                            values['strategy'] = strat
                
                            if values:
                                price_lines.append(values)
                    else:
                        if values:
                            price_lines.append(values)

            elif line.price_type in ('priced', 'efp') and lot.lot_price:
                values = cls._build_simple_pnl(
                        line=line,
                        lot=lot,
                        sale_line=None,
                        price=lot.lot_price,
                        state='fixed' if line.price_type == 'priced' else 'not fixed',
                        sign=-1,
                        pnl_type=f'pur. {line.price_type}'
                    )
                if line.mtm:
                    for strat in line.mtm:
                        values['mtm'] = strat.get_mtm(line,values['quantity'])
                        values['strategy'] = strat
            
                        if values:
                            price_lines.append(values)
                else:
                    if values:
                        price_lines.append(values)

            sale_lots = [lot] if lot.sale_line else [
                lqt.lot_s for lqt in LotQt.search([
                    ('lot_p', '=', lot.id),
                    ('lot_s', '>', 0),
                    ('lot_quantity', '>', 0),
                ])
            ]

            for sl in sale_lots:
                sl_line = sl.sale_line
                if not sl_line:
                    continue

                if sl_line.price_type == 'basis':
                    for pc in sl_line.price_summary or []:
                        values = cls._build_basis_pnl(line=line, lot=sl, sale_line=sl_line, pc=pc, sign=+1)
                        if sl_line.mtm:
                            for strat in line.mtm:
                                values['mtm'] = strat.get_mtm(sl_line,values['quantity'])
                                values['strategy'] = strat
                    
                                if values:
                                    price_lines.append(values)
                        else:
                            if values:
                                price_lines.append(values)

                elif sl_line.price_type in ('priced', 'efp'):
                    values = cls._build_simple_pnl(
                            line=line,
                            lot=sl,
                            sale_line=sl_line,
                            price=sl.lot_price_sale,
                            state='fixed' if sl_line.price_type == 'priced' else 'not fixed',
                            sign=+1,
                            pnl_type=f'sale {sl_line.price_type}'
                        )
                    if sl_line.mtm:
                        for strat in sl_line.mtm:
                            values['mtm'] = strat.get_mtm(sl_line,values['quantity'])
                            values['strategy'] = strat
                
                            if values:
                                price_lines.append(values)
                    else:
                        if values:
                            price_lines.append(values)

        return price_lines

    @classmethod
    def group_fees_by_type_supplier(cls,line,fees):
        grouped = defaultdict(list)

        # Regrouper par (type, supplier)
        for fee in fees:
            key = (fee.product, fee.supplier)
            grouped[key].append(fee)
        result = []
        for key, fee_list in grouped.items():
            ordered_fees = [f for f in fee_list if f.type == 'ordered']
            if ordered_fees:
                result.extend(ordered_fees)
            else:
                budgeted_fees = [f for f in fee_list if f.type == 'budgeted']
                result.extend(budgeted_fees)
        return result
    
    @classmethod
    def create_pnl_fee_from_line(cls, line):
        fee_lines = []
        Date = Pool().get('ir.date')
        Currency = Pool().get('currency.currency')
        FeeLots = Pool().get('fee.lots')
        #if line is matched with sale_line we should add the open sale side
        sale_lines = line.get_matched_lines() or []
        sale_open_lots = tuple(s.lot_s for s in sale_lines if s.lot_s)
        all_lots = (line.lots or ()) + sale_open_lots
        for lot in all_lots:
            fl = FeeLots.search([('lot', '=', lot.id)])
            if not fl:
                continue

            fees = [e.fee for e in fl]
            for sf in cls.group_fees_by_type_supplier(line, fees):
                sign = -1 if sf.p_r == 'pay' else 1
                qty = round(lot.get_current_quantity_converted(), 5)
                if sf.mode == 'ppack' or sf.mode == 'rate':
                    price = sf.price
                    amount = sf.amount * sign
                elif sf.mode == 'lumpsum':
                    price = sf.price
                    amount = sf.price * sign
                    qty = 1
                else:
                    price = Decimal(sf.get_price_per_qt())
                    amount = round(price * lot.get_current_quantity_converted() * sign, 2)
                if sf.currency != line.purchase.currency:
                    with Transaction().set_context(date=Date.today()):
                        price = Currency.compute(sf.currency, price, line.purchase.currency)
                if line.mtm:
                    for strat in line.mtm:
                        fee_lines.append({
                            'lot': lot.id,
                            'sale': lot.sale_line.sale.id if lot.sale_line else None,
                            'purchase': line.purchase.id,
                            'line': line.id,
                            'type': (
                                'shipment fee' if sf.shipment_in
                                else 'sale fee' if sf.sale_line
                                else 'pur. fee'
                            ),
                            'date': Date.today(),
                            'price': price,
                            'counterparty': sf.supplier.id,
                            'reference': f"{sf.product.name}/{'Physic' if lot.lot_type == 'physic' else 'Open'}",
                            'product': sf.product.id,
                            'state': sf.type,
                            'quantity': qty,
                            'amount': amount,
                            'mtm': strat.get_mtm(line,qty),
                            'strategy': strat,
                            'unit': sf.unit.id if sf.unit else line.unit.id,
                            'currency': sf.currency.id,
                        })
                else:
                    fee_lines.append({
                        'lot': lot.id,
                        'sale': lot.sale_line.sale.id if lot.sale_line else None,
                        'purchase': line.purchase.id,
                        'line': line.id,
                        'type': (
                            'shipment fee' if sf.shipment_in
                            else 'sale fee' if sf.sale_line
                            else 'pur. fee'
                        ),
                        'date': Date.today(),
                        'price': price,
                        'counterparty': sf.supplier.id,
                        'reference': f"{sf.product.name}/{'Physic' if lot.lot_type == 'physic' else 'Open'}",
                        'product': sf.product.id,
                        'state': sf.type,
                        'quantity': qty,
                        'amount': amount,
                        'mtm': Decimal(0),
                        'strategy': None,
                        'unit': sf.unit.id if sf.unit else line.unit.id,
                        'currency': sf.currency.id,
                    })

        return fee_lines

    @classmethod
    def create_pnl_der_from_line(cls, line):
        Date = Pool().get('ir.date')
        der_lines = []

        for d in line.derivatives or []:
            price = Decimal(d.price_index.get_price_per_qt(
                d.price, line.unit, line.purchase.currency
            ))

            mtm = Decimal(d.price_index.get_price(
                Date.today(), line.unit, line.purchase.currency, True
            ))

            der_lines.append({
                'purchase': line.purchase.id,
                'line': line.id,
                'type': 'derivative',
                'date': Date.today(),
                'reference': d.price_index.price_index,
                'price': round(price, 4),
                'counterparty': d.party.id,
                'product': d.product.id,
                'state': 'fixed',
                'quantity': round(d.quantity, 5),
                'amount': round(price * d.quantity * Decimal(-1), 2),
                'mtm': round((price * d.quantity * Decimal(-1)) - (mtm * d.quantity * Decimal(-1)), 2),
                'unit': line.unit.id,
                'currency': line.purchase.currency.id,
            })

        return der_lines
    
    @classmethod
    def generate(cls, line):
        Date = Pool().get('ir.date')
        Valuation = Pool().get('valuation.valuation')
        ValuationLine = Pool().get('valuation.valuation.line')
        Valuation.delete(Valuation.search([
            ('line', '=', line.id),
            ('date', '=', Date.today()),
        ]))

        ValuationLine.delete(ValuationLine.search([
            ('line', '=', line.id),
        ]))

        values = []
        values.extend(cls.create_pnl_fee_from_line(line))
        values.extend(cls.create_pnl_price_from_line(line))
        values.extend(cls.create_pnl_der_from_line(line))

        Valuation.create(values)
        ValuationLine.create(values)

class Valuation(ValuationBase, ModelView):
    "Valuation"
    __name__ = 'valuation.valuation'

    @classmethod
    def get_totals(cls):
        cursor = Transaction().connection.cursor()
        table = cls.__table__()

        sql = f"""
            WITH totals AS (
                SELECT
                    date,
                    SUM(amount) AS total_amount
                FROM {table}
                WHERE line IS NOT NULL
                OR sale_line IS NOT NULL
                GROUP BY date
            ),
            ranked AS (
                SELECT
                    date,
                    total_amount,
                    LAG(total_amount) OVER (ORDER BY date) AS previous_total,
                    ROW_NUMBER() OVER (ORDER BY date DESC) AS rn
                FROM totals
            )
            SELECT
                total_amount AS last_total,
                total_amount - previous_total AS last_variation
            FROM ranked
            WHERE rn = 1;
        """

        cursor.execute(sql)
        row = cursor.fetchone()
        if not row:
            return Decimal(0), Decimal(0)

        last_total, last_variation = row

        return last_total or Decimal(0), last_variation or Decimal(0)

class ValuationLine(ValuationBase, ModelView):
    "Last Valuation"
    __name__ = 'valuation.valuation.line'

class ValuationDyn(ModelSQL,ModelView):
    "Valuation"
    __name__ = 'valuation.valuation.dyn'

    r_purchase = fields.Many2One('purchase.purchase',"Purchase")
    r_line = fields.Many2One('purchase.line',"Line")
    r_date = fields.Date("Date")
    r_type = fields.Selection(VALTYPE, "Type")
    r_reference = fields.Char("Reference")
    r_counterparty = fields.Many2One('party.party',"Counterparty")
    r_product = fields.Many2One('product.product',"Product")
    r_state = fields.Char("State")
    r_price = fields.Numeric("Price",digits='r_unit')
    r_currency = fields.Many2One('currency.currency',"Cur")
    r_quantity = fields.Numeric("Quantity",digits='r_unit')
    r_unit = fields.Many2One('product.uom',"Unit")
    r_amount = fields.Numeric("Amount",digits='r_unit')
    r_base_amount = fields.Numeric("Base Amount",digits='r_unit')
    r_rate = fields.Numeric("Rate",digits=(16,6))
    r_mtm = fields.Numeric("Mtm",digits='r_unit')
    r_strategy = fields.Many2One('mtm.strategy',"Strategy")
    r_lot = fields.Many2One('lot.lot',"Lot")

    @classmethod
    def table_query(cls):
        Valuation = Pool().get('valuation.valuation.line')
        val = Valuation.__table__()
        context = Transaction().context
        group_pnl = context.get('group_pnl')
        wh = (val.id > 0)
        
        query = val.select(
        Literal(0).as_('create_uid'),
        CurrentTimestamp().as_('create_date'),
        Literal(None).as_('write_uid'),
        Literal(None).as_('write_date'),
        Max(val.id).as_('id'),
        Max(val.purchase).as_('r_purchase'),
        Max(val.line).as_('r_line'),
        Max(val.date).as_('r_date'),
        val.type.as_('r_type'),
        Max(val.reference).as_('r_reference'),
        val.counterparty.as_('r_counterparty'),
        Max(val.product).as_('r_product'),
        val.state.as_('r_state'),
        Avg(val.price).as_('r_price'),
        Max(val.currency).as_('r_currency'),
        Sum(val.quantity).as_('r_quantity'),
        Max(val.unit).as_('r_unit'),
        Sum(val.amount).as_('r_amount'),
        Sum(val.base_amount).as_('r_base_amount'),
        Sum(val.rate).as_('r_rate'),
        Sum(val.mtm).as_('r_mtm'),
        Max(val.strategy).as_('r_strategy'),
        Max(val.lot).as_('r_lot'),
        where=wh,
        group_by=[val.type,val.counterparty,val.state])
            
        return query    

class ValuationReport(ValuationBase, ModelView):
    "Valuation Report"
    __name__ = 'valuation.report'

    @classmethod
    def table_query(cls):
        Valuation = Pool().get('valuation.valuation')
        Date = Pool().get('ir.date')
        val = Valuation.__table__()
        context = Transaction().context
        valuation_date = context.get('valuation_date')
        strategy = context.get('strategy')
        if not valuation_date:
            valuation_date = Date.today()
        wh = (val.date == valuation_date)
        if strategy:
            wh &= (val.strategy == strategy)

        query = val.select(
        Literal(0).as_('create_uid'),
        CurrentTimestamp().as_('create_date'),
        Literal(None).as_('write_uid'),
        Literal(None).as_('write_date'),
        val.id.as_('id'),
        val.purchase.as_('purchase'),
        val.sale.as_('sale'),
        val.sale_line.as_('sale_line'),
        val.line.as_('line'),
        val.date.as_('date'),
        val.type.as_('type'),
        val.reference.as_('reference'),
        val.counterparty.as_('counterparty'),
        val.product.as_('product'),
        val.state.as_('state'),
        val.price.as_('price'),
        val.currency.as_('currency'),
        val.quantity.as_('quantity'),
        val.unit.as_('unit'),
        val.amount.as_('amount'),
        val.base_amount.as_('base_amount'),
        val.rate.as_('rate'),
        val.mtm.as_('mtm'),
        val.strategy.as_('strategy'),
        val.lot.as_('lot'),
        where=wh)
            
        return query
    
class ValuationReportContext(ModelView):
    "Valuation Report Context"
    __name__ = 'valuation.report.context'

    valuation_date = fields.Date("Valuation date")
    supplier = fields.Many2One('party.party',"Supplier")
    client = fields.Many2One('party.party',"Client")
    product = fields.Many2One('product.product',"Product")
    purchase =  fields.Many2One('purchase.purchase', "Purchase")
    sale = fields.Many2One('sale.sale',"Sale")
    state = fields.Selection([
        ('all', 'All'),
        ('open', 'Open'),
        ('fixed', 'Fixed'),
        ('hedged', 'Hedged')
    ], 'State')
    strategy = fields.Many2One('mtm.strategy',"Strategy")
    
    @classmethod
    def default_valuation_date(cls):
        pool = Pool()
        Date = pool.get('ir.date')
        return Date.today()

    @classmethod
    def default_state(cls):
        return 'all'