import arrow
from fastapi import APIRouter, Request, Body, status
from typing import Dict, List
from fastapi.encoders import jsonable_encoder
from fastapi.responses import JSONResponse
from lib.common import *
import json
import pydash

router = APIRouter()


@router.post("/create_po")
async def create_po(request: Request, po_details: Dict = Body(...)):
    po_details = jsonable_encoder(po_details)

    cursor = request.app.mssql.cursor()

    # get identity
    for itr in cursor.execute("select count(*) as cnt,(select IDENT_CURRENT('PurchaseOrderList')) as idnt  from PurchaseOrderList"):
        itr = [*itr]
        if int(itr[0]) == 0:
            row_count = int(itr[1])
        else:
            row_count = int(itr[1]) + 1

    #po_ref
    po_details['po_ref_no'] = 'AVPL/PSD/{}/{}/{}'.format(arrow.get().format('YYYY'), arrow.get().format('MM'), row_count)
    po_details['po_date'] = arrow.get(po_details['po_date'], "DD/MM/YYYY").datetime
    po_details['delivery_date'] = arrow.get(po_details['delivery_date'], "DD/MM/YYYY").datetime
    po_details['Last_update'] = arrow.now().replace(tzinfo='Asia/Calcutta').datetime

    #insert values into purchase_orders
    try:
        cursor.execute("insert into PurchaseOrderList values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
                       po_details['po_ref_no'],
                       po_details['vendor_id'],
                       po_details['po_date'],
                       po_details['delivery_date'],
                       po_details['purchase_event'],
                       po_details['prepared_by'],
                       1,
                       po_details['Last_update'],
                       None
                       )
        # insert values into purchase_order_details
        for prd in po_details['product_details']:
            cursor.execute("insert into PurchaseOrderItem values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                           row_count,
                           prd['product_id'],
                           prd['hsn_code'],
                           prd['qty'],
                           prd['uom'],
                           prd['base_price'],
                           prd['cgst'],
                           prd['sgst'],
                           1,
                           po_details['Last_update'],
                           prd['nick_name']
                           )

        request.app.mssql.commit()

        return JSONResponse({
            "po_no": row_count
        })

    except Exception as e:
        request.app.mssql.rollback()
        return "Error.PO not created."







@router.get("/get_po/{type}/{details}/{po_id}")
async def get_po(request: Request, type, details, po_id):
    cursor = request.app.mssql.cursor()

    if type.upper() == 'PO':
        po_details = {}
        rows = cursor.execute(
            "SELECT POL.PurchaseOrderListId, POL.PORefferenceNo, POL.VendorId, POL.CreatedDate, "
            "POL.DeliveryDate, POL.PurchaseEvent, C.FirstName+''+C.LastName as POPreparedBy, POL.POStatus, POL.remarks, SUM(GRL.FreightCharge) as FreightCharge "
            "FROM PurchaseOrderList as POL "
            "LEFT JOIN Vaaak.GrnList as GRL "
            "ON GRL.PurchaseOrderListId = POL.PurchaseOrderListId "
            "join Contact as C ON C.CustomerId = POL.POPreparedBy "
            "WHERE POL.PurchaseOrderListId = ? and C.CustomerDefault=1 "
            "GROUP BY POL.PurchaseOrderListId, POL.PORefferenceNo, POL.VendorId, POL.CreatedDate, POL.DeliveryDate, "
            "POL.PurchaseEvent, C.FirstName+''+C.LastName, POL.POStatus, POL.remarks ",
            (po_id,)
        )
        if rows.rowcount != 0:
            for row in rows:
                if row:
                    # get purchase_order
                    for itr, itr_itm in enumerate(row):
                        po_details[row.cursor_description[itr][0]] = itr_itm
                    # columns = [itr[0] for itr in row.cursor_description]

                    # po_details['CreatedDate'] = arrow.get(po_details['CreatedDate']).format("DD/MM/YY HH:mm")
                    # po_details['LastUpdate'] = arrow.get(po_details['LastUpdate']).format("DD/MM/YY HH:mm")
                    json_serializer(po_details)
                    po_details['product_details'] = []
                    if details == '1':
                        # get purchase_order_details
                        rows = cursor.execute(
                            "select  distinct POL.PurchaseOrderListId , POI.PurchaseOrderItemId , POI.ProductID , POI.PdtNickName, "
                            " PAT.Name AS ProductName, POI.HSN_Code ,POI.Quantity, POI.UOM, POI.BasePrice, "
                            " SP.Name AS UOM_Name, POI.CGST, POI.SGST, "
                            " V.VendorId, V.VendorName, V.VendorAddress1,V.VendorAddress2,V.City,V.State,V.Country, V.CountryCode, V.MobileNo , V.landlineNo, V.GSTIN, V.Remarks"
                            " from PurchaseOrderItem as POI "
                            "JOIN  PurchaseOrderList AS POL(NOLOCK) ON POI.PurchaseOrderListId = POL.PurchaseOrderListId "
                            "JOIN Product AS PP (NOLOCK) ON PP.ProductId = POI.ProductId  "
                            "LEFT JOIN Vaaak.Vendor AS V (NOLOCK) ON POL.VendorId = V.VendorId  "
                            "LEFT JOIN Specification AS SP (NOLOCK) ON POI.UOM = SP.SpecificationId  "
                            "JOIN ProductTranslation AS PT  (NOLOCK)  ON PT.ProductId = POI.ProductId AND PT.LocaleId = 1 "
                            "JOIN Vaaak.ProductAdditionalTranslation AS PAT  (NOLOCK)  ON PT.ProductAdditionalTransId = PAT.ProductAdditionalTransId  "
                            "LEFT JOIN vaaak.GrnItem AS GI (NOLOCK)  ON POI.PurchaseOrderItemId=GI.PurchaseOrderItemId  where POI.PurchaseOrderListId = ?",
                            po_id)
                        if rows.rowcount != 0:
                            for row in rows:
                                if row:
                                    product = {}
                                    for itr, itr_itm in enumerate(row):
                                        product[row.cursor_description[itr][0]] = itr_itm
                                    po_details['product_details'].append(product)
                            json_serializer(po_details)
                            return JSONResponse(po_details)
                        else:
                            return "Requested PO not found"
                    else:
                        return JSONResponse(po_details)
        else:
            return "Requested PO not found"

    if type.upper() == 'GRN':

        rows = cursor.execute("select GRI.GrnId,GRI.PurchaseOrderItemId,GRL.PurchaseOrderListId,GRI.ReceivedQty,GRI.InvoiceNumber,GRL.FreightCharge,C.FirstName+' '+C.LastName as PreparedBy,GRL.LastUpdate from Vaaak.GrnItem as GRI "
                              "JOIN Vaaak.GrnList as GRL on GRL.GrnId = GRI.GrnId  "
                              "JOIN Contact as C on C.CustomerId = GRL.PreparedBy "
                              "where GRI.GrnId = ? and C.CustomerDefault=1 "
                            , po_id)
        grn_items = []
        if rows.rowcount != 0:
            for row in rows:
                temp_dict = {}
                for itr, itr_itm in enumerate(row):
                    temp_dict[row.cursor_description[itr][0]] = itr_itm
                grn_items.append(temp_dict)

            json_serializer(grn_items)

            product_details = []
            if details == '1':
                columns = ['item_id', 'product_id', 'product_name','nick_name', 'hsn_code', 'order_quantity', 'uom', 'base_price',
                           'uom_name', 'cgst', 'sgst','VendorId', 'VendorName', 'VendorAddress1', 'VendorAddress2', 'City', 'State','Country', 'Countrycode', 'mobile', 'landline', 'gst', 'remarks' , 'received_qty']
                rows = cursor.execute(
                    "select  distinct POI.PurchaseOrderItemId , POI.ProductID , PAT.Name AS ProductName, POI.PdtNickName, POI.HSN_Code ,POI.Quantity, POI.UOM, POI.BasePrice, SP.Name AS UOM_Name, POI.CGST, POI.SGST, V.VendorId, V.VendorName, V.VendorAddress1,V.VendorAddress2,V.City,V.State,V.Country, V.CountryCode, V.MobileNo , V.landlineNo, V.GSTIN, V.Remarks "
                    "from PurchaseOrderItem as POI "
                    "JOIN PurchaseOrderList as POL ON POL.PurchaseOrderListId = POI.PurchaseOrderListId "
                    "JOIN Product AS PP  ON PP.ProductId = POI.ProductId  "
                    "JOIN Vaaak.Vendor AS V  ON POL.VendorId = V.VendorId "
                    "JOIN Specification AS SP ON SP.SpecificationId = POI.UOM "
                    "JOIN ProductTranslation AS PT  ON PT.ProductId = POI.ProductId AND PT.LocaleId = 1 "
                    "JOIN Vaaak.ProductAdditionalTranslation AS PAT  ON PT.ProductAdditionalTransId = PAT.ProductAdditionalTransId  "
                    "where POI.PurchaseOrderListId = ?", grn_items[0]['PurchaseOrderListId'])

                if rows.rowcount != 0:
                    for itr, row in enumerate(rows):
                        row = [*row]
                        row.append(
                            pydash.collections.filter_(grn_items, lambda x: x['PurchaseOrderItemId'] == row[0])[0][
                                'ReceivedQty'])
                        product_details.append(dict(zip(columns, row)))

                json_serializer(product_details)
                grn_details = {
                    'grn_no': grn_items[0]['GrnId'],
                    'po_no': grn_items[0]['PurchaseOrderListId'],
                    'invoice_no': grn_items[0]['InvoiceNumber'],
                    'grn_date': grn_items[0]['LastUpdate'],
                    'Freight_charge': grn_items[0]['FreightCharge'],
                    'Prepared_By' : grn_items[0]['PreparedBy'],
                    'product_details': product_details
                }
                return grn_details
        else:
            return "GRN not found"


    if type.upper() == 'RN':

        rows = cursor.execute("select GRI.GrnId,GRI.PurchaseOrderItemId,GRL.PurchaseOrderListId,GRI.ReceivedQty,GRI.InvoiceNumber,GRL.FreightCharge,C.FirstName+' '+C.LastName as PreparedBy,GRL.LastUpdate from Vaaak.GrnItem as GRI "
                              "JOIN Vaaak.GrnList as GRL on GRL.GrnId = GRI.GrnId  "
                              "JOIN Contact as C on C.CustomerId = GRL.PreparedBy "
                              "where GRI.GrnId = ? and C.CustomerDefault=1 ", po_id)
        rn_items = []
        if rows.rowcount != 0:
            for row in rows:
                temp_dict = {}
                for itr, itr_itm in enumerate(row):
                    temp_dict[row.cursor_description[itr][0]] = itr_itm
                rn_items.append(temp_dict)

            json_serializer(rn_items)

            product_details = []
            if details == '1':
                columns = ['item_id', 'product_id', 'product_name', 'nick_name', 'hsn_code', 'order_quantity', 'uom', 'base_price',
                           'uom_name', 'cgst', 'sgst', 'VendorId', 'VendorName', 'VendorAddress1', 'VendorAddress2', 'City', 'State','Country', 'Countrycode', 'mobile', 'landline', 'gst', 'remarks', 'received_qty']
                rows = cursor.execute(
                    "select  distinct POI.PurchaseOrderItemId , POI.ProductID , PAT.Name AS ProductName,POI.PdtNickName, POI.HSN_Code ,POI.Quantity, POI.UOM, POI.BasePrice, SP.Name AS UOM_Name, POI.CGST, POI.SGST,"
                    " V.VendorId, V.VendorName, V.VendorAddress1,V.VendorAddress2,V.City,V.State,V.Country, V.CountryCode, V.MobileNo , V.landlineNo, V.GSTIN, V.Remarks "
                    "from PurchaseOrderItem as POI "
                    "JOIN PurchaseOrderList as POL ON POL.PurchaseOrderListId = POI.PurchaseOrderListId "
                    "JOIN Product AS PP  ON PP.ProductId = POI.ProductId  "
                    "JOIN Vaaak.Vendor AS V  ON POL.VendorId = V.VendorId "
                    "JOIN Specification AS SP ON SP.SpecificationId = POI.UOM "
                    "JOIN ProductTranslation AS PT  ON PT.ProductId = POI.ProductId AND PT.LocaleId = 1 "
                    "JOIN Vaaak.ProductAdditionalTranslation AS PAT  ON PT.ProductAdditionalTransId = PAT.ProductAdditionalTransId  "
                    "where POI.PurchaseOrderListId = ?", rn_items[0]['PurchaseOrderListId'])

                if rows.rowcount != 0:
                    for itr, row in enumerate(rows):
                        row = [*row]
                        if len(pydash.collections.filter_(rn_items, lambda x: x['PurchaseOrderItemId'] == row[0])) > 0:
                            row.append(
                                pydash.collections.filter_(rn_items, lambda x: x['PurchaseOrderItemId'] == row[0])[0][
                                    'ReceivedQty'])
                            product_details.append(dict(zip(columns, row)))



                json_serializer(product_details)
                rn_details = {
                    'rn_no': rn_items[0]['GrnId'],
                    'po_no': rn_items[0]['PurchaseOrderListId'],
                    'invoice_no': rn_items[0]['InvoiceNumber'],
                    'rn_date': rn_items[0]['LastUpdate'],
                    'Freight_charge': rn_items[0]['FreightCharge'],
                    'Prepared_By' : rn_items[0]['PreparedBy'],
                    'product_details': product_details
                }
                return rn_details
        else:
            return "RN not found"

@router.get("/get_alias_name/{productid}")
async def get_valid_po(request: Request, productid):
    cursor = request.app.mssql.cursor()
    rows = cursor.execute("select top 1 PdtNickName from  PurchaseOrderItem where ProductId = ? and PdtNickName IS NOT null",productid)
    if rows.rowcount != 0:
        result = rows.fetchone()
        return result[0] if result else "" 
    else:
        return ""

@router.post("/set_alias_name")
async def get_valid_po(request: Request, details: Dict = Body(...)):
    aliasdetails = jsonable_encoder(details)
    cursor = request.app.mssql.cursor()
    rows = cursor.execute("select top 1 PdtNickName from  PurchaseOrderItem where ProductId = ? and PdtNickName IS NOT null",aliasdetails['productid'])
    if rows.rowcount != 0:
        cursor.execute("update PurchaseOrderItem set PdtNickName=? where ProductId=?",aliasdetails['aliasname'], aliasdetails['productid'])
        request.app.mssql.commit()
        return "Alias name Updated"
    else:
        return "something went wrong"


@router.get("/get_valid_po")
async def get_valid_po(request: Request):
    cursor = request.app.mssql.cursor()

    po_no = []
    for itr in cursor.execute("select PurchaseOrderListId from PurchaseOrderList where POStatus = 1"):
        po_no.append(itr[0])

    return po_no

@router.post("/cancel_po")
async def cancel_po(request: Request, po_details: Dict = Body(...)):
    po_details = jsonable_encoder(po_details)

    cursor = request.app.mssql.cursor()

    rows = cursor.execute("select * from Vaaak.GrnList where PurchaseOrderListId = ?",po_details['po_no'])

    if rows.rowcount == 0:
        cursor.execute("update PurchaseOrderList set POStatus = -1, remarks = ? where PurchaseOrderListId = ?",po_details['remarks'], po_details['po_no'])
        request.app.mssql.commit()
        return "PO - {} Cancelled".format(po_details['po_no'])
    else:
        return "GRN/RN already created for this PO."