import frappe
from frappe import _

def get_supplier_quotes(item_code):
    q = "SELECT parent FROM  `tabSupplier Quotation Item` WHERE item_code='{0}' ORDER BY modified DESC LIMIT 3;".format(item_code)
    results = frappe.db.sql(q, as_dict=True)
    return results

def get_manufaturer_part_no(item_code):
    q = "SELECT manufacturer_part_no FROM `tabItem Manufacturer` WHERE parent='{0}' LIMIT 1;".format(item_code)
    results = frappe.db.sql(q, as_dict=True)
    if len(results) > 0:
        return results[0]['manufacturer_part_no']
    return ""

def get_supplier_quotes_by_mpn(manufacturer_part_no):
    q = "SELECT parent FROM  `tabSupplier Quotation Item` WHERE manufacturer_part_no='{0}' ORDER BY modified DESC LIMIT 3;".format(manufacturer_part_no)
    results = frappe.db.sql(q, as_dict=True)
    return results

def rfq_validations(doc, method):
    """
    Message print of values of supplier quotation id manufacturer
    part no associated with the item code
    """
    results = ""
    for current in doc.items:
        supplier_quotes = []
        for quote in get_supplier_quotes(current.item_code):
            supplier_quotes.append(quote['parent'])

        if len(supplier_quotes) > 0:
            results += "Item %s has Supplier Quotes: %s <br/><hr/>" % (current.item_code, ",".join(supplier_quotes))

        supplier_quotes = []
        manufaturer_part_no = get_manufaturer_part_no(current.item_code)

        # we didn't find manufaturer_part_no
        # hence no point processing it furthure
        if manufaturer_part_no.strip() == "":
            continue

        for quote in get_supplier_quotes_by_mpn(manufaturer_part_no):
            supplier_quotes.append(quote['parent'])

        if len(supplier_quotes) > 0:
            results += "Item %s{MPN:%s} has Supplier Quotes: %s <br/><hr/>" % (current.item_code, manufaturer_part_no, ",".join(supplier_quotes))


    if results.strip() != "":
        frappe.msgprint(_(results))

def get_marked_up_prices(opportunity_name):
    q = "SELECT name,supplier FROM `tabSupplier Quotation` WHERE opportunity='{0}' AND status='Submitted';".format(opportunity_name)
    quotes = frappe.db.sql(q, as_dict=True)
    results = {}
    suppliers = {}
    for current in quotes:
        rate_q = "SELECT item_code,rate FROM `tabSupplier Quotation Item` WHERE parent='{0}'".format(current['name'])
        rate_results = frappe.db.sql(rate_q, as_dict=True)
        for item in rate_results:
            results[item['item_code']] = item['rate'] * 1.15
            suppliers[item['item_code']] = current["supplier"]
    return results, suppliers

def quote_validations(doc, method):
    """
    Setting up rate, amount, and supplier in item table from previous supplier quotation
    based on opporunity.
    """
    marked_up_prices, suppliers = get_marked_up_prices(doc.opportunity)
    if marked_up_prices:
        for current in doc.items:
            if current.rate == 0.0:
                if current.item_code not in marked_up_prices:
                    current.rate = 0
                    current.amount = 0
                else:
                    current.rate = marked_up_prices[current.item_code]
                    current.amount = current.rate * current.qty
            if current.item_code in suppliers:
                current.supplier = suppliers[current.item_code]

def quote_update(doc, method):
    doc.quote_reviewed_by = frappe.session.user

def purchase_order_validation(doc, method):
    # fetch unit price from iro to vro
    for item in doc.items:
        if item.sales_order:
            so = frappe.get_doc("Sales Order", item.sales_order)
            if so.shipping_address:
                shipping_address = so.shipping_address.replace("<br>"," ")
            shipping_address = so.shipping_address

            doc.gtl_ship_address = shipping_address
            for so_item in so.items:
                if so_item.supplier == doc.supplier_name:
                    item.gtl_unit_price_usd = so_item.rate

    for item in doc.items:
        total_amt = item.qty*item.gtl_unit_price_usd
        item.gtl_total_price_usd = total_amt

    supplier_name = frappe.get_list("Supplier Quotation",{"rfq_no":doc.rfq_no})
    if supplier_name:
        for supp_doc_name in supplier_name:
            supp_doc = frappe.get_doc("Supplier Quotation",supp_doc_name["name"])
            for supp_item in supp_doc.items:
                for vro_item in doc.items:
                    if vro_item.item_name == supp_item.item_name:
                        if vro_item.rate not in [0,supp_item.rate] or vro_item.qty != supp_item.qty:
                            return True
                        vro_item.rate = supp_item.rate
                        vro_item.amount = supp_item.amount
    return True

def todo_validation(doc, method):
    """
    Fetch Values from different References.
    """
    if doc.reference_type == "Quotation":
        quote_doc = frappe.get_doc(doc.reference_type,doc.reference_name)
        doc.rfq_no = quote_doc.rfq_no
        doc.total = quote_doc.total
    if doc.reference_type == "Purchase Order":
        po_doc = frappe.get_doc(doc.reference_type,doc.reference_name)
        doc.po_no = po_doc.po_no
        doc.total = po_doc.total