from datetime import datetime as dt
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()

def grn_rn(sql_object, type):
    if type == 'GRN':
        columns = ['grn_no', 'po_no', 'vender_name', 'grn_date', 'invoice_no', 'po_status']

        statement = "select COUNT(GRL.PurchaseOrderListId) as cnt, GRL.PurchaseOrderListId from Vaaak.GrnList AS GRL " \
                    "JOIN PurchaseOrderList as POL ON POL.PurchaseOrderListId = GRL.PurchaseOrderListId and POL.POStatus = 0" \
                    "Group By GRL.PurchaseOrderListId having COUNT(GRL.PurchaseOrderListId) = 1"

        rows = sql_object.execute(statement)

        if rows.rowcount != 0:
            po_list = []
            for row in rows:
                row = [*row]
                po_list.append(row[1])

            grn_list = []
            placeholders = ",".join("?" * len(po_list))

            statement = "select distinct GRL.GrnId,GRL.PurchaseOrderListId,VEN.VendorName,GRL.LastUpdate,GRI.InvoiceNumber,POL.POStatus from Vaaak.GrnList as GRL " \
                        "JOIN Vaaak.GrnItem as GRI on GRI.GrnId = GRL.GrnId " \
                        "JOIN PurchaseOrderList as POL ON POL.PurchaseOrderListId = GRL.PurchaseOrderListId " \
                        "JOIN Vaaak.Vendor as VEN on VEN.VendorId = POL.VendorId where GRL.PurchaseOrderListId in (%s)" % placeholders

            rows = sql_object.execute(statement, po_list)

            grn_list = {}

            for row in rows:
                if row[1] in grn_list:
                    grn_list[row[1]] = grn_list[row[1]] + [row[0]]
                else:
                    grn_list[row[1]] = [row[0]]

            return grn_list
        else:
            return []

    if type == 'RN':
        columns = ['rn_no', 'po_no', 'vender_name', 'rn_date', 'invoice_no', 'po_status']

        statement = "select COUNT(GRL.PurchaseOrderListId) as cnt, GRL.PurchaseOrderListId from Vaaak.GrnList AS GRL " \
                    "JOIN PurchaseOrderList as POL ON POL.PurchaseOrderListId = GRL.PurchaseOrderListId where POL.POStatus = 1 " \
                    "Group By GRL.PurchaseOrderListId having COUNT(GRL.PurchaseOrderListId) = 1 " \
                    "union " \
                    "select COUNT(GRL.PurchaseOrderListId) as cnt, GRL.PurchaseOrderListId from Vaaak.GrnList AS GRL " \
                    "JOIN PurchaseOrderList as POL ON POL.PurchaseOrderListId = GRL.PurchaseOrderListId " \
                    "Group By GRL.PurchaseOrderListId having COUNT(GRL.PurchaseOrderListId) > 1"

        rows = sql_object.execute(statement)

        if rows.rowcount != 0:
            po_list = []
            for row in rows:
                row = [*row]
                po_list.append(row[1])

            rn_list = []
            placeholders = ",".join("?" * len(po_list))

            statement = "select distinct GRL.GrnId,GRL.PurchaseOrderListId,VEN.VendorName,GRL.LastUpdate,GRI.InvoiceNumber,POL.POStatus from Vaaak.GrnList as GRL " \
                        "JOIN Vaaak.GrnItem as GRI on GRI.GrnId = GRL.GrnId " \
                        "JOIN PurchaseOrderList as POL ON POL.PurchaseOrderListId = GRL.PurchaseOrderListId " \
                        "JOIN Vaaak.Vendor as VEN on VEN.VendorId = POL.VendorId where GRL.PurchaseOrderListId in (%s)" % placeholders

            rows = sql_object.execute(statement, po_list)

            rn_list = {}

            for row in rows:
                if row[1] in rn_list:
                    rn_list[row[1]] = rn_list[row[1]] + [row[0]]
                else:
                    rn_list[row[1]] = [row[0]]

            return rn_list

        else:
            return []


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

    cursor = request.app.mssql.cursor()
    filter_items = []
    if filter['c1'] == 'PO':
        columns = ['po_number', 'vender_name', 'po_date', 'po_status']
        statement = "select POL.PurchaseOrderListId,VEN.VendorName,POL.CreatedDate, POL.POStatus from PurchaseOrderList as POL " \
                    "JOIN Vaaak.Vendor as VEN on VEN.VendorId = POL.VendorId where 1=1"

        if 'c2' in filter:
            if filter['c2'] in [1,0,-1]:
                statement = statement + ' and POStatus = {}'.format(filter['c2'])

        rows = cursor.execute(statement)
        for row in rows:
            row = [*row]
            row[2] = arrow.get(row[2]).format("DD/MM/YYYY")
            filter_items.append(dict(zip(columns, row)))

        rn_list = grn_rn(cursor,'RN')
        grn_list = grn_rn(cursor,'GRN')
        # rows = cursor.execute("select * from Vaaak.GrnList")
        #
        # for row in rows:
        #     if row[1] in rn_list:
        #         rn_list[row[1]] = rn_list[row[1]] + [row[0]]
        #     else:
        #         rn_list[row[1]] = [row[0]]

        for itr in filter_items:
            if itr['po_number'] in rn_list:
                itr['rn_nos'] = rn_list[itr['po_number']]
            else:
                itr['rn_nos'] = None

            if itr['po_number'] in grn_list:
                itr['grn_nos'] = grn_list[itr['po_number']]
            else:
                itr['grn_nos'] = None

        return filter_items

    if filter['c1'] == 'GRN':
        columns = ['grn_no', 'po_no', 'vender_name', 'grn_date', 'invoice_no', 'po_status']

        statement = "select COUNT(GRL.PurchaseOrderListId) as cnt, GRL.PurchaseOrderListId from Vaaak.GrnList AS GRL " \
                    "JOIN PurchaseOrderList as POL ON POL.PurchaseOrderListId = GRL.PurchaseOrderListId and POL.POStatus = 0" \
                    " Group By GRL.PurchaseOrderListId having COUNT(GRL.PurchaseOrderListId) = 1"

        rows = cursor.execute(statement)

        if rows.rowcount != 0:
            po_list = []
            for row in rows:
                row = [*row]
                po_list.append(row[1])

            grn_list = []
            placeholders = ",".join("?" * len(po_list))

            statement = "select distinct GRL.GrnId,GRL.PurchaseOrderListId,VEN.VendorName,GRL.LastUpdate,GRI.InvoiceNumber,POL.POStatus from Vaaak.GrnList as GRL " \
                        "JOIN Vaaak.GrnItem as GRI on GRI.GrnId = GRL.GrnId " \
                        "JOIN PurchaseOrderList as POL ON POL.PurchaseOrderListId = GRL.PurchaseOrderListId " \
                        "JOIN Vaaak.Vendor as VEN on VEN.VendorId = POL.VendorId where GRL.PurchaseOrderListId in (%s)" % placeholders

            rows = cursor.execute(statement, po_list)

            for row in rows:
                row = [*row]
                row[3] = arrow.get(row[3]).format("DD/MM/YYYY")
                grn_list.append(dict(zip(columns, row)))

            json_serializer(grn_list)

            return grn_list
        else:
            return []

    if filter['c1'] == 'RN':
        columns = ['rn_no', 'po_no', 'vender_name', 'rn_date', 'invoice_no', 'po_status']

        statement = "select COUNT(GRL.PurchaseOrderListId) as cnt, GRL.PurchaseOrderListId from Vaaak.GrnList AS GRL " \
                    "JOIN PurchaseOrderList as POL ON POL.PurchaseOrderListId = GRL.PurchaseOrderListId where POL.POStatus = 1 " \
                    "Group By GRL.PurchaseOrderListId having COUNT(GRL.PurchaseOrderListId) = 1 " \
                    "union " \
                    "select COUNT(GRL.PurchaseOrderListId) as cnt, GRL.PurchaseOrderListId from Vaaak.GrnList AS GRL " \
                    "JOIN PurchaseOrderList as POL ON POL.PurchaseOrderListId = GRL.PurchaseOrderListId " \
                    "Group By GRL.PurchaseOrderListId having COUNT(GRL.PurchaseOrderListId) > 1"

        rows = cursor.execute(statement)

        if rows.rowcount != 0:
            po_list = []
            for row in rows:
                row = [*row]
                po_list.append(row[1])

            rn_list = []
            placeholders = ",".join("?" * len(po_list))

            statement = "select distinct GRL.GrnId,GRL.PurchaseOrderListId,VEN.VendorName,GRL.LastUpdate,GRI.InvoiceNumber,POL.POStatus from Vaaak.GrnList as GRL " \
                        "JOIN Vaaak.GrnItem as GRI on GRI.GrnId = GRL.GrnId " \
                        "JOIN PurchaseOrderList as POL ON POL.PurchaseOrderListId = GRL.PurchaseOrderListId " \
                        "JOIN Vaaak.Vendor as VEN on VEN.VendorId = POL.VendorId where GRL.PurchaseOrderListId in (%s)" % placeholders

            rows = cursor.execute(statement, po_list)

            for row in rows:
                row = [*row]
                row[3] = arrow.get(row[3]).format("DD/MM/YYYY")
                rn_list.append(dict(zip(columns, row)))

            json_serializer(rn_list)

            return rn_list
        else:
            return []