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 pydash

router = APIRouter()


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

    cursor = request.app.mssql.cursor()

    Last_update = arrow.now().replace(tzinfo='Asia/Calcutta').datetime

    #insert into grnlist
    for itr in cursor.execute("select count(*) as cnt,(select IDENT_CURRENT('Vaaak.GrnList')) as idnt  from Vaaak.GrnList"):
        itr = [*itr]
        if int(itr[0]) == 0:
            row_count = int(itr[1])
        else:
            row_count = int(itr[1]) + 1

    cursor.execute("insert into Vaaak.GrnList values (?, ?)",
                   rn_details['po_no'],
                   Last_update
                   )

    request.app.mssql.commit()

    #insert into grnitem
    for itr in rn_details['items']:
        cursor.execute("insert into Vaaak.GrnItem values (?, ?, ?, ?, ?)",
                       row_count,
                       itr['purchase_order_itemid'],
                       itr['received_qty'],
                       itr['invoice_no'],
                       Last_update
                       )
    request.app.mssql.commit()

    #update the stock in inventory table
    for itr in rn_details['items']:
        cursor.execute("update inventory set Quantity = Quantity + ?  where ProductId = (select ProductId from PurchaseOrderItem where PurchaseOrderItemId = ?)",itr['received_qty'], itr['purchase_order_itemid'])

    request.app.mssql.commit()

    #update item_status to 1
    rows = cursor.execute("select * from PurchaseOrderItem where PurchaseOrderListId = ?", rn_details['po_no'])

    po_items = {}

    for row in rows:
        po_items[row[0]] = row[4]

    rows = cursor.execute("select * from Vaaak.GrnItem where GrnId in (select GrnId from Vaaak.GrnList where PurchaseOrderListId = ?)", rn_details["po_no"])

    grn_items = []
    for row in rows:
        grn_items.append({
        "item_id": row[2],
        "qty": row[3],
        })

    grn_items = pydash.map_values(pydash.collections.group_by(grn_items, lambda x: x['item_id']),
                      lambda x: pydash.sum_by(x, lambda y: y['qty']))

    for k,v in grn_items.items():
        if po_items[k] <= v:
            cursor.execute("update PurchaseOrderItem set POItemStatus = 0 where PurchaseOrderItemId = ? and PurchaseOrderListId = ?", k, rn_details['po_no'])

    cursor.commit()

    #update po status to 0
    rows = cursor.execute("select * from PurchaseOrderItem where PurchaseOrderListId = ? and POItemStatus = 1;", rn_details['po_no'])

    if rows.rowcount == 0:
        cursor.execute("update PurchaseOrderList set POStatus = 0 where PurchaseOrderListId = ?", rn_details['po_no'])
        cursor.commit()

    return {
        "type": rn_details['type'].upper(),
        "id": row_count
    }


@router.get("/grn_details/type/po_no")
async def grn_details(request: Request, type, po_no):
    if type.upper() == 'GRN':
        cursor = request.app.mssql.cursor()

        rows = cursor.execute("select * from PurchaseOrderList where PurchaseOrderListId = ? and POStatus != -1",po_no)

        if rows.rowcount != 0:
            po_items = []
            received_items = {}

            rows = cursor.execute("select GRI.PurchaseOrderItemId, sum(GRI.ReceivedQty) as Received from Vaaak.GrnItem AS GRI "
                                  "where GrnId in (select GRL.GrnId from Vaaak.GrnList AS GRL where GRL.PurchaseOrderListId = ?) "
                                  "group by PurchaseOrderItemId", po_no)

            if rows.rowcount == 0:
                for row in rows:
                    received_items[row[0]] = row[1]

                rows = cursor.execute(" select  distinct POL.PurchaseOrderListId , 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(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_no)

                columns = ['PurchaseOrderListId', 'PurchaseOrderItemId', 'ProductID', 'ProductName', 'nick_name', 'HSN_Code', 'Quantity', 'UOM',
                           'BasePrice', 'UOM_Name', 'CGST', 'SGST', 'VendorId', 'VendorName', 'VendorAddress1', 'VendorAddress2', 'City', 'State','Country', 'Countrycode', 'mobile', 'landline', 'gst', 'remarks']

                for row in rows:
                    row = [*row]
                    if row[1] in received_items:
                        row[6] = row[6] - received_items[row[1]]
                        if row[6] != 0:
                            po_items.append(dict(zip(columns,row)))
                    else:
                        po_items.append(dict(zip(columns,row)))

                return po_items

            else:
                return "RN already exists"
        else:
            return "PO already Cancelled!"

    if type.upper() == "RN":
        cursor = request.app.mssql.cursor()
        rows = cursor.execute("select * from PurchaseOrderList where PurchaseOrderListId = ? and POStatus != -1", po_no)

        if rows.rowcount != 0:
            po_items = []
            received_items = {}

            rows = cursor.execute("select GRI.PurchaseOrderItemId, sum(GRI.ReceivedQty) as Received from Vaaak.GrnItem AS GRI "
                                  "where GrnId in (select GRL.GrnId from Vaaak.GrnList AS GRL where GRL.PurchaseOrderListId = ?) "
                                  "group by PurchaseOrderItemId", po_no)

            for row in rows:
                received_items[row[0]] = row[1]

            rows = cursor.execute(
                " select distinct POL.PurchaseOrderListId , 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(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_no)
            columns = ['PurchaseOrderListId', 'PurchaseOrderItemId', 'ProductID', 'ProductName','nick_name', 'HSN_Code', 'Quantity',
                       'UOM',
                       'BasePrice', 'UOM_Name', 'CGST', 'SGST', 'VendorId', 'VendorName', 'VendorAddress1',
                       'VendorAddress2', 'City', 'State', 'Country', 'Countrycode', 'mobile', 'landline', 'gst', 'remarks']

            for row in rows:
                row = [*row]
                if row[1] in received_items:
                    row[6] = row[6] - received_items[row[1]]
                    if row[6] != 0:
                        po_items.append(dict(zip(columns, row)))
                else:
                    po_items.append(dict(zip(columns, row)))

            return po_items
        else:
            return "PO already cancelled"