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

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 IDENT_CURRENT('PurchaseOrderList') as cnt;"):
        if int(itr[0]) == 1:
            row_count = int(itr[0])
        else:
            row_count = int(itr[0]) + 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
    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']
                   )

    request.app.mssql.commit()

    #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'])
    request.app.mssql.commit()

    cursor.close()



    return JSONResponse({
        "po_no": row_count
    })


@router.get("/get_po/{details}/{po_id}")
async def get_po(request: Request, details, po_id):
    cursor = request.app.mssql.cursor()
    po_details = {}
    rows = cursor.execute("select * from PurchaseOrderList where PurchaseOrderListId = ?",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 * from PurchaseOrderItem where 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"

    else:
        return "Requested PO not found"


@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