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_grn")
async def create_grn(request: Request, grn_details: Dict = Body(...)):
    grn_details = jsonable_encoder(grn_details)


@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 IDENT_CURRENT('Vaaak.GrnList') as cnt;"):
        if int(itr[0]) == 1:
            row_count = int(itr[0])
        else:
            row_count = int(itr[0]) + 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 "{}-{} created!".format(rn_details['type'].upper(), row_count)

@router.get("/grn_details1/type/po_no")
async def grn_details1(request: Request, type, po_no):
    cursor = request.app.mssql.cursor()
    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.HSN_Code ,POI.Quantity, POI.UOM, POI.BasePrice, SP.Name AS UOM_Name, POI.CGST, POI.SGST, V.VendorName + ' ' + V.VendorAddress + ' ' + V.CountryCode + ' ' + V.MobileNo AS VendorDetails, V.VendorId 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 SpecificationType AS SP (NOLOCK) ON POI.UOM = SP.SpecificationTypeId  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', 'HSN_Code', 'Quantity', 'UOM',
               'BasePrice', 'UOM_Name', 'CGST', 'SGST', 'VendorDetails', 'VendorId']
    for row in rows:
        row = [*row]
        if row[1] in received_items:
            row[5] = row[5] - received_items[row[1]]
            if row[5] != 0:
                po_items.append(dict(zip(columns,row)))
        else:
            po_items.append(dict(zip(columns,row)))

    return po_items


@router.get("/grn_details/type/po_no")
async def grn_details(request: Request, type, po_no):
    cursor = request.app.mssql.cursor()
    # cursor.execute("exec Vaaak.spGetGrnDetails('rn', '10021')")
    rows = cursor.execute("{call Vaaak.spGetGrnDetails(?, ?)}", type, po_no)
    columns = ['PurchaseOrderListId', 'PurchaseOrderItemId', 'ProductID','ProductName','HSN_Code','Quantity','UOM','BasePrice','UOM_Name','CGST','SGST','VendorDetails','VendorId']

    grn_details = []
    if rows.rowcount != 0:
        for row in rows:
            grn_details.append(dict(zip(columns,row)))
        grn_details = json_serializer(grn_details)

        return grn_details
    else:
        if type == 'rn':
            return "RN not found"
        if type == 'grn':
            return "GRN not found"