import os
import json
from datetime import datetime
from pathlib import Path

import jwt
import requests
from fastapi import FastAPI, Body, Depends, HTTPException, status, Request
from fastapi.security import HTTPBasic, HTTPBasicCredentials
from fastapi.templating import Jinja2Templates
from phpserialize import dumps
import stripe

from env import *
from model import *
from utils import *
from db import database, sql_exe
from mail import send_mail

app = FastAPI(
    title="HH Custom Tailor",
    docs_url='/api/v1/docs',
    redoc_url='/api/v1/redoc',
    openapi_url='/api/v1/openapi.json'
)
stripe.api_key = API_KEY
public_key = PUBLIC_KEY


def substitute_parameters(query, values):
    for key, value in values.items():
        # Assuming values are either strings or numeric
        # More types and proper escaping would be needed for a complete solution
        if isinstance(value, str):
            value = f"'{value}'"
        query = query.replace(f":{key}", str(value))
    return query


security = HTTPBasic()
BASE_PATH = Path(__file__).resolve().parent
TEMPLATES = Jinja2Templates(directory=str(BASE_PATH / "templates"))

ADMIN = 'admin@hhcustomtailor.com'


@app.on_event("startup")
async def startup():
    print('connecting')
    await database.connect()


@app.on_event("shutdown")
async def shutdown():
    await database.disconnect()


async def auth(credentials: HTTPBasicCredentials = Depends(security)):
    sql = "SELECT entity_id, password_hash, is_active FROM customer_entity "\
        "where email=:email"
    res = await sql_exe(sql, {"email": credentials.username}, single=True)
    verified = False
    if res:
        if not res['is_active']:
            raise HTTPException(
                status_code=status.HTTP_401_UNAUTHORIZED,
                detail="Account is not active",
                headers={"WWW-Authenticate": "Basic"},
            )
        verified = verify_password(credentials.password, res['password_hash'])
    if (not res) or (not verified):
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Incorrect email or password",
            headers={"WWW-Authenticate": "Basic"},
        )
    return res['entity_id']


async def common_auth(credentials: HTTPBasicCredentials = Depends(security)):
    if not (credentials.username == COMMON_USERNAME and credentials.password == COMMON_PASSWORD):
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Incorrect email or password",
            headers={"WWW-Authenticate": "Basic"},
        )
    return True


@app.post('/api/v1/signup/', tags=["Auth"])
async def user_registration(
    item: User = Body(..., example={
        "email": "you@gmail.com",
        "password": "123456",
        "firstname": "John",
    })
):
    data = item.dict()
    sql = "SELECT entity_id, email, password_hash from customer_entity "\
        "where email = :email"
    res = await sql_exe(sql, {'email': item.email}, single=True)

    # generate OTP
    otp_code = generate_otp()
    receivers = [item.email]

    if res:
        code = 2
        message = 'User already exists. Please login.'
        return {'code': code, 'message': message, 'data': {'id': res.entity_id}}

    # sending otp email
    await send_mail(receivers, otp_code, 'Activation Code')

    # insert code into db
    data['password_hash'] = hashing(item.password)
    data.pop('password')
    data['otp_code'] = otp_code
    data['is_active'] = 0

    sql = "INSERT INTO customer_entity (email, password_hash, firstname, "\
        "lastname, gender, otp_code, is_active) VALUES (:email, "\
        ":password_hash,:firstname,:lastname,:gender,:otp_code,:is_active)"

    res = await sql_exe(sql, data)

    sql = "update referrals set referral_id = :entity_id, status = 1 where email = :register_email"
    await sql_exe(sql, {"entity_id": res.get('id'), "register_email": item.email})

    sql = "select referrer_id from referrals where email = :register_email"
    referrer_id = await sql_exe(sql, {"register_email": item.email}, single=True)

    if referrer_id:
        sql = "select firstname, lastname, email from customer_entity where entity_id = :referrer_id"
        referrer_info = await sql_exe(sql, {"referrer_id": referrer_id.referrer_id}, single=True)
        msg = f"Your friend {item.firstname} {item.lastname} "\
            "has registered with us using your referral link."
        await send_mail([referrer_info.email], msg=msg, sub="Referral Link Used")
    code = 1
    message = 'User created successfully. Please check email for OTP'
    return {'code': code, 'message': message, 'data': res}


@app.post('/api/v1/signin/', tags=["Auth"])
async def user_login(item: Login):
    sql = "SELECT entity_id, email, password_hash, is_active from "\
        "customer_entity where email = :email"
    res = await sql_exe(sql, {'email': item.email}, single=True)
    if res:
        if not res.is_active:
            code = 2
            message = "Your account is not active. Please verify the email first"
            data = {"id": res.entity_id}
        elif verify_password(item.password, res.password_hash):
            code = 1
            message = 'User logged in successfully'
            data = {"id": res.entity_id}
        else:
            code = 0
            message = 'Incorrect password'
            data = {}
    else:
        code = 0
        message = 'User not found'
        data = {}
    return {'code': code, 'message': message, 'data': data}


@app.post('/api/v1/signin_with_token/', tags=["Auth"])
async def user_login_with_token(token: str):
    sql = "SELECT email, password_hash, is_active from "\
        "customer_entity where token = :token"
    res = await sql_exe(sql, {'token': token}, single=True)
    if res:
        if not res.is_active:
            code = 2
            message = "Your account is not active. Please verify the email first"
            data = {}
        else:
            code = 1
            message = 'User logged in successfully'
            data = {"email": res.email, "password": res.password_hash}
    else:
        code = 0
        message = 'User not found'
        data = {}
    return {'code': code, 'message': message, 'data': data}


@app.post('/api/v1/verify_otp/', tags=["Auth"])
async def verify_otp(item: VerifyOTP = Body(
    ..., examples={
        "Registration": {
            "summary": "For registation",
            "value": {
                "customer_id": 1,
                "otp": 123456,
                "prompt": "registration",
            }},
        "ForgotPassword": {
            "summary": "For forgot password",
            "value": {
                "customer_id": "you@mail.com",
                "otp": 123456,
                "prompt": "forgot_password",
            }}
    })
):
    if item.prompt == 'registration':
        sql = "SELECT entity_id, otp_code from customer_entity "\
            "where entity_id = :entity_id"
        res = await sql_exe(sql, {'entity_id': item.customer_id}, single=True)

    elif item.prompt == 'forgot_password':
        sql = "SELECT entity_id, otp_code from customer_entity "\
            "where email = :email"
        res = await sql_exe(sql, {'email': item.customer_id}, single=True)

    else:
        code = 0
        message = 'Invalid prompt'
        return {'code': code, 'message': message, 'data': {}}

    if res and res[1] == item.otp:
        code = 1
        message = 'OTP verified successfully'
        if item.prompt == 'registration':
            sql = "UPDATE customer_entity SET is_active=1 WHERE entity_id=:entity_id"
            await sql_exe(sql, {'entity_id': item.customer_id})
        return {'code': code, 'message': message, 'data': {'id': res[0]}}
    else:
        code = 0
        if res:
            message = 'OTP does not match'
        else:
            message = 'User does not exist'
        return {'code': code, 'message': message, 'data': {}}


@app.post('/api/v1/resend_otp/', tags=["Auth"])
async def resend_otp(item: ForgotPassword = Body(
        ..., example={
            "email": "you@mail.com"
        })):
    sql = "SELECT otp_code from customer_entity where email=:email"
    res = await sql_exe(sql, {'email': item.email}, single=True)
    if res:
        receivers = [item.email]
        await send_mail(receivers, res[0], 'Your OTP')
        code = 1
        message = 'OTP sent successfully'
        return {'code': code, 'message': message, 'data': {}}
    else:
        code = 0
        message = 'User does not exist'
        return {'code': code, 'message': message, 'data': {}}


@app.post('/api/v1/forgot_password/', tags=["Auth"])
async def forgot_password(item: ForgotPassword):
    if not item.email.strip():
        return {'code': 0, 'message': "Email field shouldn't be empty", 'data': {}}
    sql = "SELECT entity_id, email from customer_entity where email=:email"
    res = await sql_exe(sql, {'email': item.email}, single=True)
    if res:
        code = 1
        otp_code = generate_otp()
        try:
            await send_mail([item.email], otp_code, 'Forgot Password')
        except Exception as e:
            print(e)
            code = 0
            message = 'Error in sending email'
        else:
            message = 'OTP sent successfully. Please check your email for OTP'
            sql = "UPDATE customer_entity SET otp_code=:otp_code "\
                "WHERE email = :email"
            await sql_exe(sql, {'email': item.email, 'otp_code': otp_code})
    else:
        code = 0
        message = 'User does not exist'
    return {'code': code, 'message': message, 'data': {}}


@app.post('/api/v1/change_password/', tags=["Auth"])
async def change_password(item: UpdatePassword,
                          entity_id: int = Depends(auth)):
    sql = "SELECT password_hash from customer_entity where entity_id=:entity_id"
    res = await sql_exe(sql, {'entity_id': entity_id}, single=True)
    if res and verify_password(item.old_password, res.password_hash):
        password_hash = hashing(item.new_password)
        sql = "UPDATE customer_entity SET password_hash = :password_hash "\
            "WHERE entity_id = :entity_id"
        data = {'password_hash': password_hash, 'entity_id': entity_id}
        res = await sql_exe(sql,  data)
        if res:
            code = 1
            message = 'Password updated successfully'
        else:
            code = 0
            message = 'Failed to update password'
    else:
        code = 0
        message = 'Old password is wrong'
    return {'code': code, 'message': message, 'data': {}}


@app.post('/api/v1/generate_new_password/', tags=["Auth"])
async def generate_new_password(item: GeneratePassword):
    password_hash = hashing(item.new_password)
    sql = "UPDATE customer_entity SET password_hash=:password_hash "\
        "WHERE entity_id=:entity_id"
    data = {'entity_id': item.customer_id, 'password_hash': password_hash}
    res = await sql_exe(sql, data)
    if res:
        code = 1
        message = 'Password updated successfully'
    else:
        code = 0
        message = 'Failed to update password'
    return {'code': code, 'message': message, 'data': {}}


@app.get('/api/v1/customers/', tags=["Admin"])
async def get_customers(common: bool = Depends(common_auth)):
    sql = "Select firstname, lastname, email from customer_entity"
    try:
        code = 1
        message = "Data fetched successfully"
        data = await sql_exe(sql)
    except Exception as e:
        print(e)
        code = 0
        message = "Failed to collect the data"
        data = {}
    return {"code": code, "message": message, "data": data}


@app.get('/api/v1/customer_info/', tags=["Profile"])
async def get_customer_info(entity_id: int = Depends(auth)):
    sql = "select c.firstname, c.lastname, c.email, e.value as gender, alternate_email, "\
        "q.items_count as cart_count from customer_entity c Left JOIN quote "\
        "q ON c.entity_id = q.customer_id AND q.is_active = '1' left join "\
        "eav_attribute_option_value e on e.option_id = c.gender WHERE "\
        "c.entity_id = :entity_id"
    try:
        code = 1
        message = "Data fetched successfully"
        data = await sql_exe(sql, {"entity_id": entity_id}, single=True)
        data = dict(data)
        if not data["cart_count"]:
            data['cart_count'] = 0
        if not data["gender"]:
            data["gender"] = ""
        if not data["alternate_email"]:
            data["alternate_email"] = ""
    except Exception as e:
        print(e)
        code = 0
        message = "Failed to collect the data"
        data = {}
    return {"code": code, "message": message, "data": data}


@app.post('/api/v1/update_account_info/', tags=["Profile"])
async def update_account_info(item: UpdateAccountInfo,
                              entity_id: int = Depends(auth)):
    data = {i: j for i, j in item.dict().items() if j}  # removing null values
    if item.gender:
        if item.gender.title() not in ['Male', 'Female']:
            code = 0
            message = 'Pass gender either Male or Female'
            return {'code': code, 'message': message}
        sql2 = 'SELECT option_id FROM eav_attribute_option_value where '\
            'value=:gender'
        res2 = await sql_exe(sql2, {"gender": item.gender.title()}, True)
        data['gender'] = res2.option_id

    if item.email:
        sql = 'SELECT email from customer_entity where email=:email and '\
            'entity_id !=:customer_id'
        res = await sql_exe(sql, {'email': item.email, 'customer_id': entity_id})
        if res:
            code = 0
            message = 'This email is already taken. Please provide different email'
            return {'code': code, 'message': message, 'data': data}
    updates = [f"{key}=:{key}" for key in data]
    if updates:
        sql = f"UPDATE customer_entity SET {','.join(updates)} WHERE "\
            "entity_id=:customer_id"
        data["customer_id"] = entity_id
        res = await sql_exe(sql, data)
        if res:
            code = 1
            message = 'Account information updated successfully'
            sql = 'SELECT c.firstname, c.lastname, c.email, e.value as gender, alternate_email '\
                'from customer_entity c left join eav_attribute_option_value e '\
                'on e.option_id = c.gender where entity_id=:customer_id'
            data = await sql_exe(sql, {'customer_id': entity_id})
            data = {i: j if j else "" for i, j in dict(data[0]).items()}
        else:
            code = 0
            message = 'Failed to update Account information'
    else:
        code = 0
        message = 'Nothing to update'
    return {'code': code, 'message': message, 'data': [data]}


@app.delete('/api/v1/delete_account/', tags=["Profile"])
async def delete_account(request: Request, entity_id: int = Depends(auth)):
    cus_info = "SELECT email, firstname, lastname FROM customer_entity "\
        "WHERE entity_id=:entity_id"
    cus_info = await sql_exe(cus_info, {"entity_id": entity_id}, True)
    deleted = "Begining"
    try:
        cus_entity_sql = "DELETE FROM customer_entity WHERE entity_id=:entity_id"
        await sql_exe(cus_entity_sql, {"entity_id": entity_id})
        deleted = "customer_entity"

        cus_address_sql = "DELETE FROM customer_address_entity WHERE parent_id=:entity_id"
        await sql_exe(cus_address_sql, {"entity_id": entity_id})
        deleted = "customer_address_entity"

        quote_sql = "SELECT entity_id FROM quote WHERE customer_id=:entity_id"
        quote_ids = await sql_exe(quote_sql, {"entity_id": entity_id})
        for quote_id in quote_ids:
            measurement_sql = "DELETE FROM measurement_quote WHERE quote_id=:quote_id"
            await sql_exe(measurement_sql, {"quote_id": quote_id.entity_id})
        deleted = "measurement_quote"

        address_sql = "Select address_id from quote_address where customer_id=:entity_id"
        address_ids = await sql_exe(address_sql, {"entity_id": entity_id})
        for address_id in address_ids:
            shipping_sql = "DELETE FROM quote_shipping_rate WHERE address_id=:address_id"
            await sql_exe(shipping_sql, {"address_id": address_id.address_id})
        deleted = "quote_shipping_rate"

        quote_sql = "DELETE FROM quote WHERE customer_id=:entity_id"
        await sql_exe(quote_sql, {"entity_id": entity_id})
        deleted = "quote"

        quote_address_sql = "DELETE FROM quote_address WHERE customer_id=:entity_id"
        await sql_exe(quote_address_sql, {"entity_id": entity_id})
        deleted = "quote_address"

        sales_order_sql = "SELECT * FROM sales_order WHERE customer_id=:entity_id"
        sales_orders = await sql_exe(sales_order_sql, {"entity_id": entity_id})
        for sales_order in sales_orders:
            # insert sales order to delete_sale_order table
            sales_sql = "INSERT INTO delete_sale_order(entity_id, order_id, base_grand_total, base_shipping_amount, base_total_paid, base_total_qty_ordered, increment_id, weight) values (:entity_id, :order_id, :base_grand_total, :base_shipping_amount, :base_total_paid, :base_total_qty_ordered, :increment_id, :weight)"
            await sql_exe(sales_sql, {"entity_id": sales_order.entity_id, "order_id": sales_order.order_id, "base_grand_total": sales_order.base_grand_total, "base_shipping_amount": sales_order.base_shipping_amount, "base_total_paid": sales_order.base_total_paid, "base_total_qty_ordered": sales_order.base_total_qty_ordered, "increment_id": sales_order.increment_id, "weight": sales_order.weight})

            sales_invoice_update_sql = "UPDATE sales_invoice SET billing_address_id = '', shipping_address_id = '' WHERE order_id=:order_id"
            await sql_exe(sales_invoice_update_sql, {"order_id": sales_order.order_id})

            sales_grid_update_sql = "UPDATE sales_invoice_grid SET customer_name = '', customer_email = '', billing_name = '', billing_address = '', shipping_address = '' WHERE order_id=:order_id"
            await sql_exe(sales_grid_update_sql, {"order_id": sales_order.order_id})
        deleted = "sales_order_update"

        sales_order_delete_sql = "DELETE FROM sales_order WHERE customer_id=:entity_id"
        await sql_exe(sales_order_delete_sql, {"entity_id": entity_id})
        deleted = "sales_order"

        address_delete_sql = "DELETE FROM sales_order_address WHERE customer_id=:entity_id"
        await sql_exe(address_delete_sql, {"entity_id": entity_id})
        deleted = "sales_order_address"

        order_grid_delete_sql = "DELETE FROM sales_order_grid WHERE customer_id=:entity_id"
        await sql_exe(order_grid_delete_sql, {"entity_id": entity_id})
        deleted = "sales_order_grid"

        measurement_delete_sql = "DELETE FROM measurements WHERE customer_id=:entity_id"
        await sql_exe(measurement_delete_sql, {"entity_id": entity_id})
        deleted = "measurements"
    except Exception as e:
        print(e)
        print(deleted)
        msg = f"Account not deleted. Error in {deleted}"
        subject = "Account not deleted"
        await send_mail([ADMIN], msg=msg, sub=subject)
        code = 0
        message = "Account not deleted"
    else:
        msg = TEMPLATES.TemplateResponse(
            "delete_account.html",
            {"request": request, "info": cus_info},
        )
        subject = "Heritage House Account Deletion Confirmation"
        await send_mail([cus_info.email], msg=msg.body.decode('utf-8'), sub=subject)
        code = 1
        message = 'Account deleted successfully'
    return {'code': code, 'message': message}


@app.get('/api/v1/country/', tags=["Address"])
async def get_country():
    sql = "SELECT country_id, name FROM directory_country WHERE is_active=1"
    try:
        code = 1
        message = "Data fetched successfully"
        data = await sql_exe(sql)
    except Exception as e:
        print(e)
        code = 0
        message = "Failed to collect the data"
        data = {}
    return {"code": code, "message": message, "data": data}


@app.get('/api/v1/region/{country_id}', tags=["Address"])
async def get_region(country_id: str):
    sql = "SELECT region_id,default_name as name FROM directory_country_region"\
        " WHERE country_id=:country_id"
    try:
        code = 1
        message = "Data fetched successfully"
        data = await sql_exe(sql, {"country_id": country_id.upper()})
    except Exception as e:
        print(e)
        code = 0
        message = "Failed to collect the data"
        data = {}
    return {"code": code, "message": message, "data": data}


@app.get('/api/v1/address/', tags=["Address"])
async def list_address(entity_id: int = Depends(auth)):
    sql = 'SELECT a.entity_id as id, a.firstname, a.lastname, a.street,'\
        'a.city, a.region, a.region_id, c.name as country, a.country_id,'\
        "a.telephone, IF(e.default_shipping!='',1,0) is_default_shipping,"\
        'a.postcode FROM customer_address_entity a INNER JOIN '\
        'directory_country c on c.country_id = a.country_id LEFT JOIN '\
        'customer_entity e on e.default_shipping = a.entity_id and a.'\
        'parent_id = e.entity_id WHERE c.is_active=1 and a.is_active=1 '\
        'and a.parent_id =:customer_id'
    try:
        res = await sql_exe(sql, {'customer_id': entity_id})
        if res:
            code = 1
            message = 'Data fetched successfully'
            data = res
        else:
            code = 2
            message = 'No address found'
            data = []
    except:
        code = 0
        message = 'Failed to collect the data'
        data = []
    return {'code': code, 'message': message, 'data': data}


# @app.get('/api/v1/address/{address_id}', tags=["Address"])
async def get_address(address_id: int, entity_id: int = Depends(auth)):
    sql = 'SELECT a.entity_id as id, a.firstname, a.lastname, a.street,'\
        'a.city, a.region_id, a.country_id ,a.postcode, a.telephone, '\
        "IF(e.default_shipping!='',1,0) is_default_shipping FROM "\
        'customer_address_entity a LEFT JOIN customer_entity e on '\
        'e.default_shipping = a.entity_id and a.parent_id = e.entity_id '\
        'WHERE a.is_active = 1 and a.entity_id = :address_id '\
        'and a.parent_id = :customer_id'
    try:
        data = {'customer_id': entity_id, 'address_id': address_id}
        res = await sql_exe(sql, data, True)
        if res:
            code = 1
            message = 'Data fetched successfully'
            data = res
        else:
            code = 2
            message = 'No address found with this id'
            data = []
    except:
        code = 0
        message = 'Failed to collect the data'
        data = []
    return {'code': code, 'message': message, 'data': data}


@app.post('/api/v1/address/', tags=["Address"])
async def add_address(item: SingleAddress,  entity_id: int = Depends(auth)):
    sql = "INSERT INTO customer_address_entity (parent_id, is_active, city, "\
        "country_id, firstname, lastname, postcode, region,region_id,street,"\
        "telephone) VALUES (:customer_id,  1, :city, :country_id,:firstname,"\
        ":lastname, :postcode, :region, :region_id, :street, :telephone)"
    sql_default_address = "UPDATE customer_entity SET default_shipping ="\
        ":address_id WHERE entity_id = :customer_id"

    data = {'customer_id': entity_id, 'city': item.city,
            'country_id': item.country_id, 'firstname': item.firstname,
            'lastname': item.lastname, 'postcode': item.postcode,
            'region': item.region, 'region_id': item.region_id,
            'street': item.street, 'telephone': item.telephone}
    try:
        code = 1
        message = 'Address added successfully'
        data = await sql_exe(sql, data, True)
        if item.is_default_shipping:
            await sql_exe(sql_default_address, {'address_id': data['id'],
                                                'customer_id': entity_id})
    except Exception as e:
        print(e)
        code = 0
        message = 'Failed to add address'
        data = {}
    return {'code': code, 'message': message, 'data': data}


@app.put('/api/v1/default_address/', tags=["Address"])
async def update_default_address(item: DefaultAddress,
                                 entity_id: int = Depends(auth)):
    if item.shipping_address_id and item.billing_address_id:
        sql = "UPDATE customer_entity SET default_shipping = "\
            ":shipping_address_id, default_billing = :billing_address_id "\
            "WHERE entity_id = :customer_id"
        data = {'shipping_address_id': item.shipping_address_id,
                'billing_address_id': item.billing_address_id,
                'customer_id': entity_id}
    elif item.shipping_address_id:
        sql = "UPDATE customer_entity SET default_shipping = "\
            ":shipping_address_id WHERE entity_id = :customer_id"
        data = {'shipping_address_id': item.shipping_address_id,
                'customer_id': entity_id}
    elif item.billing_address_id:
        sql = "UPDATE customer_entity SET default_billing = "\
            ":billing_address_id WHERE entity_id = :customer_id"
        data = {'billing_address_id': item.billing_address_id,
                'customer_id': entity_id}
    else:
        code = 1
        message = 'Nothing to Update'
        data = []
        return {'code': code, 'message': message, 'data': data}
    try:
        code = 1
        message = 'Address updated successfully'
        data = await sql_exe(sql, data)
    except Exception as e:
        print(e)
        code = 0
        message = 'Failed to update address'
        data = {}
    return {'code': code, 'message': message, 'data': data}


@app.put('/api/v1/address/{address_id}', tags=["Address"])
async def update_address(address_id: int, item: SingleAddress,
                         entity_id: int = Depends(auth)):
    sql = "UPDATE customer_address_entity SET city=:city, country_id = "\
        ":country_id, firstname =:firstname, lastname =:lastname, postcode="\
        ":postcode, region =:region, region_id =:region_id, street =:street,"\
        "telephone = :telephone WHERE entity_id = :address_id and "\
        "parent_id = :customer_id"
    sql_default_address = "UPDATE customer_entity SET default_shipping ="\
        ":address_id WHERE entity_id = :customer_id"
    data = {'customer_id': entity_id, 'city': item.city,
            'country_id': item.country_id, 'firstname': item.firstname,
            'lastname': item.lastname, 'postcode': item.postcode,
            'region': item.region, 'region_id': item.region_id,
            'street': item.street, 'telephone': item.telephone,
            'address_id': address_id}
    try:
        code = 1
        message = 'Address updated successfully'
        data = await sql_exe(sql, data)
        if item.is_default_shipping:
            await sql_exe(sql_default_address, {'address_id': address_id,
                                                'customer_id': entity_id})
    except Exception as e:
        print(e)
        code = 0
        message = 'Failed to update address'
        data = {}
    return {'code': code, 'message': message, 'data': data}


@app.delete('/api/v1/address/{address_id}', tags=["Address"])
async def delete_address(address_id: int, entity_id: int = Depends(auth)):
    sql = "delete from customer_address_entity where entity_id =:address_id "\
        "and parent_id =:customer_id"
    sql_shipping = "update customer_entity set default_shipping = '' where "\
        "default_shipping =:address_id and entity_id =:customer_id"
    data = {'customer_id': entity_id, 'address_id': address_id}
    try:
        code = 1
        message = 'Address deleted successfully'
        data = await sql_exe(sql, data)
        await sql_exe(sql_shipping, data)
    except Exception as e:
        print(e)
        code = 0
        message = 'Failed to delete address'
        data = {}
    return {'code': code, 'message': message, 'data': data}


@app.get("/api/v1/types/",  tags=["Types"])
async def types():
    data = await sql_exe('select id,name from categories where is_active=1')
    return {"code": 1, "message": "Data fetched successfully", "data": data}


@app.get('/api/v1/category/{type_id}/', tags=["Category"])
async def category(type_id: int = 2):
    sql = 'select c.entity_id, cv.value, c.item_id, cv.attribute_id from '\
        'catalog_category_entity c inner join catalog_category_entity_varchar '\
        'cv on c.entity_id = cv.entity_id and cv.attribute_id in (45,48) '\
        'and c.categories_id=:type_id'
    try:
        code = 1
        message = "Data fetched successfully"
        data = {}
        res = await sql_exe(sql, {"type_id": type_id})
        for e_id, value, item_id, attribute_id in res:
            if e_id not in data:
                data[e_id] = {"name": None, "image": None}
            if not item_id:
                item_id = ""
            data[e_id]["item_id"] = item_id
            if attribute_id == 45:
                data[e_id]["name"] = value
            elif attribute_id == 48:
                data[e_id]["image"] = image(value)
        data = [dict(category_id=e_id, **value)
                for e_id, value in data.items()]
    except Exception as e:
        print(e)
        code = 0
        message = "Failed to collect the data"
        data = {}
    return {"code": code, "message": message, "data": data}


@app.get('/api/v1/subcategory/{category_id}/', tags=["SubCategory"])
async def subcategory(category_id: int):
    sql = 'select c.entity_id, cv.value, c.item_id,  cv.attribute_id from '\
        'catalog_category_entity c inner join catalog_category_entity_varchar '\
        'cv on c.entity_id = cv.entity_id and cv.attribute_id in (45,48) '\
        'and c.parent_id=:category_id'
    try:
        code = 1
        message = "Data fetched successfully"
        data = {}
        res = await sql_exe(sql, {"category_id": category_id})
        for e_id, value, item_id, attribute_id in res:
            if e_id not in data:
                data[e_id] = {"name": None, "image": None}

            data[e_id]["item_id"] = item_id
            if attribute_id == 45:
                data[e_id]["name"] = value
            elif attribute_id == 48:
                data[e_id]["image"] = image(value)
        data = [dict(subcategory_id=e_id, **value)
                for e_id, value in data.items()]
    except Exception as e:
        print(e)
        code = 0
        message = "Failed to collect the data"
        data = {}
    return {"code": code, "message": message, "data": data}


@app.get('/api/v1/item_details/{item_id}/', tags=["Item"])
async def item_details(item_id: int):
    sql = 'select items.id, items.name_front as name, items.price, '\
        'items.description, c.entity_id as subcategory_id, items.image, '\
        'items.is_multiple, items_pieces.piece_id from items INNER JOIN '\
        'catalog_category_entity c on items.id = c.item_id LEFT join '\
        'items_pieces on items_pieces.item_id = c.item_id and items_pieces.'\
        'deleted = 0 WHERE items.id = :item_id and items.deleted = 0 '\
        'group by items.id'
    try:
        code = 1
        message = "Data fetched successfully"
        data = await sql_exe(sql, {"item_id": item_id}, single=True)
        data = dict(data)
        if data.get('image') == None:
            data['image'] = ""
        else:
            data['image'] = image(data.get('image'), "items")
        if data.get('is_multiple') == 1:
            data['piece_id'] = 0
    except Exception as e:
        print(e)
        code = 0
        message = "Failed to collect the data"
        data = {}
    return {"code": code, "message": message, "data": data}


@app.get('/api/v1/filters/{subcategory_id}/', tags=["Fabrics"])
async def get_filters(subcategory_id: int, entity_id: int = Depends(auth)):
    sql_color = 'select a.option_id as color_id, a.value as color_name from '\
        'eav_attribute_option_value as a inner join catalog_product_index_eav'\
        ' as p on a.option_id = p.value inner join catalog_category_product '\
        'as c on c.product_id = p.source_id INNER join fabrics as f on f.id '\
        '= p.source_id where p.attribute_id = 166 and p.store_id = 2 and '\
        '(f.group_fabrics!= "1,0") and f.front_end = 1 and f.deleted = 0 '\
        'and f.is_hidden=0 and c.category_id=:category_id group by a.value'

    sql_pattern = 'select a.option_id as color_id, a.value as color_name from '\
        'eav_attribute_option_value as a inner join catalog_product_index_eav'\
        ' as p on a.option_id = p.value inner join catalog_category_product '\
        'as c on c.product_id = p.source_id INNER join fabrics as f on f.id '\
        '= p.source_id where p.attribute_id = 167 and p.store_id = 2 and '\
        '(f.group_fabrics!= "1,0") and f.front_end = 1 and f.deleted = 0 '\
        'and f.is_hidden=0 and c.category_id=:category_id group by a.value'

    try:
        code = 1
        message = "Data fetched successfully"
        data = {"category_id": subcategory_id}
        color = await sql_exe(sql_color, data)
        pattern = await sql_exe(sql_pattern, data)
        res = [{"filtername": "color", "options": color},
               {"filtername": "pattern", "options": pattern}]
    except Exception as e:
        print(e)
        code = 0
        message = "Failed to collect the data"
        res = {}
    return {"code": code, "message": message, "data": res}


@app.post('/api/v1/filters/{subcategory_id}/', tags=["Fabrics"])
async def apply_filters(subcategory_id: int, item: Filters,
                        entity_id: int = Depends(auth)):
    sql = 'SELECT c.product_id as fabric_id,c.category_id as subcategory_id,'\
        'f.description, f.sample_file_name as image FROM '\
        'catalog_category_product c inner join fabrics f on c.product_id='\
        'f.id INNER JOIN catalog_product_index_price AS price_index ON '\
        'price_index.entity_id=c.product_id AND price_index.customer_group_id='\
        "0 AND price_index.website_id = '1' "
    data = {"category_id": subcategory_id}
    if item.filter_data.get('color'):
        data['color'] = tuple(item.filter_data["color"])
        sql += ' inner join catalog_product_index_eav colors on '\
            'colors.entity_id = c.product_id and colors.store_id = 2 and '\
            f'colors.value in :color '
    if item.filter_data.get('pattern'):
        data['pattern'] = tuple(item.filter_data["pattern"])
        sql += ' inner join catalog_product_index_eav patterns on '\
            'patterns.entity_id = c.product_id and patterns.store_id = 2 and'\
            f' patterns.value in :pattern '
    sql += ' where f.group_fabrics != "1,0" and f.front_end = 1 and '\
        'f.deleted = 0 and f.is_hidden = 0 and c.category_id = :category_id'\
        ' group by c.product_id'
    try:
        code = 1
        message = "Data fetched successfully"
        res = await sql_exe(sql, data)
        res = [{**i, 'image': image(i.image, 'fabric')} for i in res]
    except Exception as e:
        print(e)
        code = 0
        message = "Failed to collect the data"
        res = {}
    return {"code": code, "message": message, "data": res}


@app.get('/api/v1/piece/{subcategory_id}/', tags=["Piece"])
async def get_piece(subcategory_id: int, entity_id: int = Depends(auth)):
    sql = 'SELECT pieces.id, pieces.name_front as name, pieces.price,'\
        'pieces.template_id, items.entity_id as subcategory_id, pieces.images,'\
        'items_pieces.item_id, items_pieces.is_customizable FROM pieces inner '\
        'JOIN items_pieces on pieces.id=items_pieces.piece_id INNER JOIN items '\
        'i on i.id = items_pieces.item_id INNER JOIN catalog_category_entity '\
        'items on items.item_id=i.id WHERE i.deleted=0 and i.is_multiple=1 '\
        'and items_pieces.category_id = :category_id and items_pieces.deleted'\
        '=0 and pieces.deleted=0 order by pieces.id ASC'
    try:
        code = 1
        message = "Data fetched successfully"
        data = await sql_exe(sql, {"category_id": subcategory_id})
        if not data:
            code = 0
            message = "Piece for multiple items not found"
    except Exception as e:
        print(e)
        code = 0
        message = "Failed to collect the data"
        data = {}
    return {"code": code, "message": message, "data": data}


@app.get('/api/v1/piece_types/{piece_id}/', tags=["Piece"], response_model=gen(Piece))
async def get_piece_types(piece_id: int, entity_id: int = Depends(auth)):
    sql = 'SELECT pt.id, IF(f.id!="",1,0) as fabric,items.collection,pt.name,op.extra_cost '\
        'FROM ref_pieces_types pt INNER JOIN options_pieces op on op.'\
        'pieces_type_id = pt.id INNER JOIN options o on o.id = op.option_id '\
        'LEFT JOIN fabrics f on find_in_set(op.piece_id, f.template_id) and '\
        'f.deleted = 0 AND f.front_end = 1 AND f.is_hidden = 0 INNER JOIN '\
        'items_pieces on items_pieces.piece_id = op.piece_id inner JOIN items '\
        'on items.id = items_pieces.item_id WHERE op.piece_id = :piece_id '\
        'and op.deleted = 0 and pt.deleted = 0 AND o.deleted =0 and '\
        'o.is_hide_front = 0 group by pt.id'
    try:
        code = 1
        message = "Data fetched successfully"
        res = await sql_exe(sql, {"piece_id": piece_id})
        data = []
        for i in res:
            data.append({"id": i.id, "name": i.name,
                        "extra_cost": i.extra_cost})
        if res[0].fabric and int(res[0].collection):
            data.append({"id": 4, "name": "Fabric", "extra_cost": 0})
        if not res:
            code = 0
            message = "Piece for multiple items not found"
    except Exception as e:
        print(e)
        code = 0
        message = "Failed to collect the data"
        data = {}
    return {"code": code, "message": message, "data": data}


@app.get('/api/v1/lining_filters/{piece_id}/', tags=["Lining"])
async def get_lining_filters(piece_id: int, entity_id: int = Depends(auth)):
    sql_color = 'select a.option_id as color_id, a.value as color_name from '\
        'eav_attribute_option_value as a inner join catalog_product_index_eav '\
        'as p on a.option_id = p.value inner join catalog_category_product as '\
        'c on c.product_id = p.source_id INNER join fabrics as f on f.id = p.'\
        'source_id INNER JOIN catalog_category_entity cc on cc.entity_id = c.'\
        'category_id INNER JOIN items_pieces ip on ip.item_id = cc.item_id '\
        'where p.attribute_id = 166 and p.store_id = 2 and (f.group_fabrics!= '\
        '"0,0") and f.front_end = 1 and f.deleted = 0 and f.is_hidden = 0 and '\
        'ip.deleted = 0 and ip.piece_id = :piece_id group by a.value'

    sql_pattern = 'select a.option_id as color_id, a.value as color_name from '\
        'eav_attribute_option_value as a inner join catalog_product_index_eav '\
        'as p on a.option_id = p.value inner join catalog_category_product as '\
        'c on c.product_id = p.source_id INNER join fabrics as f on f.id = p.'\
        'source_id INNER JOIN catalog_category_entity cc on cc.entity_id = c.'\
        'category_id INNER JOIN items_pieces ip on ip.item_id = cc.item_id '\
        'where p.attribute_id = 167 and p.store_id = 2 and (f.group_fabrics!= '\
        '"0,0") and f.front_end = 1 and f.deleted = 0 and f.is_hidden = 0 and '\
        'ip.deleted = 0 and ip.piece_id = :piece_id group by a.value'

    try:
        code = 1
        message = "Data fetched successfully"
        data = {"piece_id": piece_id}
        color = await sql_exe(sql_color, data)
        pattern = await sql_exe(sql_pattern, data)
        res = [{"filtername": "color", "options": color},
               {"filtername": "pattern", "options": pattern}]
    except Exception as e:
        print(e)
        code = 0
        message = "Failed to collect the data"
        res = {}
    return {"code": code, "message": message, "data": res}


@app.post('/api/v1/lining_filters/{piece_id}/', tags=["Lining"])
async def apply_lining_filters(piece_id: int, item: Filters,
                               entity_id: int = Depends(auth)):
    sql = 'SELECT c.product_id as fabric_id,f.description, f.sample_file_name '\
        'as image FROM catalog_category_entity cc inner join '\
        'catalog_category_product c on cc.entity_id = c.category_id '\
        'inner join fabrics f on c.product_id = f.id INNER JOIN '\
        'catalog_product_index_price AS price_index ON price_index.entity_id '\
        '= c.product_id AND price_index.customer_group_id = 0 AND '\
        "price_index.website_id = '1' INNER JOIN items_pieces ip on "\
        'ip.item_id = cc.item_id '
    data = {"piece_id": piece_id}
    if item.filter_data.get('color'):
        data['color'] = tuple(item.filter_data["color"])
        sql += ' inner join catalog_product_index_eav colors on '\
            'colors.entity_id = c.product_id and colors.store_id = 2 and '\
            f'colors.value in :color '
    if item.filter_data.get('pattern'):
        data['pattern'] = tuple(item.filter_data["pattern"])
        sql += ' inner join catalog_product_index_eav patterns on '\
            'patterns.entity_id = c.product_id and patterns.store_id = 2 and'\
            f' patterns.value in :pattern '
    sql += ' where f.group_fabrics != "0,0" and f.front_end = 1 and f.deleted'\
        '= 0 and f.is_hidden = 0 and ip.deleted = 0 and ip.piece_id =:piece_id'\
        ' group by c.product_id'
    try:
        code = 1
        message = "Data fetched successfully"
        res = await sql_exe(sql, data)
        res = [{**i, 'image': image(i.image, 'fabric')} for i in res]
    except Exception as e:
        print(e)
        code = 0
        message = "Failed to collect the data"
        res = {}
    return {"code": code, "message": message, "data": res}


@app.get('/api/v1/style/{piece_id}/', tags=["Style"])
async def get_style(piece_id: int, entity_id: int = Depends(auth)):
    sql = 'SELECT o.id AS options_id, o.custom_name AS option_name FROM '\
        'options o INNER JOIN options_pieces op ON op.option_id = o.id AND '\
        'op.piece_id = :piece_id AND op.deleted = "0" AND o.deleted="0" and '\
        'o.is_hide_front="0" AND o.custom_name!="" AND op.is_visible="1" '\
        'and op.pieces_type_id = 2 ORDER BY op.sort_order ASC'

    sql2 = "SELECT c.id, c.comb, cp.filename, IF(d.choice_comb_id!='',1,0) "\
        'AS is_selected, t.name as template from choice_comb c Left JOIN '\
        'default_choices d on d.choice_comb_id = c.id and d.deleted = 0 and '\
        'd.piece_id = :piece_id JOIN choice_comb_photo cp on cp.'\
        'choice_comb_id = c.id inner JOIN templates t on t.id = c.template_id '\
        'AND t.is_active = 1 WHERE find_in_set(:piece_id,c.piece_id) and '\
        'c.deleted = 0 and c.reference_id =:option_id and cp.deleted = 0'

    try:
        code = 1
        message = "Data fetched successfully"
        res = []
        data = {"piece_id": piece_id}
        sql_res = await sql_exe(sql, data)
        for i in sql_res:
            if i.option_name == "Type of Jacket" and piece_id == 6:
                options_name = 'Type of Overcoat'
            else:
                options_name = i.option_name
            output = {"id": i.options_id, "name": options_name}
            data['option_id'] = i.options_id
            sql1_res = await sql_exe(sql2, data)
            output['choices'] = []
            for j in sql1_res:
                filename = f"{j.template}/{j.filename}.jpg"
                name = j.filename.replace('-', ' ').replace('_', ' ').title()
                output['choices'].append(
                    {'id': j.id, 'name': name,
                     'image': image(filename, 'choice'),
                     'is_selected': j.is_selected,
                     })
            res.append(output)
    except Exception as e:
        print(e)
        code = 0
        message = "Failed to collect the data"
        res = {}
    return {"code": code, "message": message, "data": res}


@app.get('/api/v1/accent/{piece_id}/', tags=["Accent"])
async def get_accent(piece_id: int, entity_id: int = Depends(auth)):
    sql_option = 'SELECT o.id AS options_id, o.custom_name AS options_name '\
        'FROM options o INNER JOIN options_pieces op ON op.option_id = o.id '\
        'AND op.piece_id = :piece_id AND op.deleted = "0" AND o.deleted="0" '\
        'and o.is_hide_front="0" AND o.custom_name!="" AND op.is_visible="1" '\
        'and op.pieces_type_id =3 ORDER BY op.sort_order ASC'

    sql_choices = 'SELECT c.name_front as name, c.id FROM choices c WHERE '\
        'c.option_id = :option_id and c.deleted =0 and c.is_hide_front = 0 and '\
        'c.is_visible = 1'

    sql_additional_infos = 'SELECT a.id as info_id, a.name as info_name, '\
        'a.display_text, a.display_image FROM additional_infos a WHERE '\
        'a.choice_id = :choice_id and a.deleted = 0 and a.hide_front_end = 0'
    output = []
    try:
        code = 1
        message = "Data fetched successfully"
        res = await sql_exe(sql_option, {"piece_id": piece_id})
        # res = dict(res)
        for i in res:
            res1 = await sql_exe(sql_choices, {"option_id": i.options_id})

            choices = []
            for j in res1:
                res2 = await sql_exe(sql_additional_infos, {"choice_id": j.id})
                additional_info = []
                for ai in res2:
                    images = []
                    if ai.display_image:
                        display_image = json.loads(ai.display_image)
                        display_text = json.loads(ai.display_text)
                        img_len = len(display_image)
                        text_len = len(display_text)
                        if img_len > text_len:
                            for _ in range(text_len, img_len):
                                display_text.append('')

                        for img, txt in zip(display_image, display_text):
                            if img:
                                images.append(
                                    {"display_text": txt,
                                     "display_image": image(img, 'accent')})
                    else:
                        display_image = ""
                    additional_info.append(
                        {"info_id": ai.info_id, "info_name": ai.info_name,
                         "images": images})
                choices.append({"id": j.id, "name": j.name,
                                "additional_infos": additional_info})
            output.append({"id": i.options_id, "name": i.options_name,
                           "choices": choices})

    except Exception as e:
        print(e)
        code = 0
        message = "Failed to collect the data"
        res = {}
    return {"code": code, "message": message, "data": output}


@app.post('/api/v1/feature_images/', tags=["Images"])
async def get_feature_images(item: Image, entity_id: int = Depends(auth)):
    if item.lining_fabric_id:
        lining_fabric_id = item.lining_fabric_id
    else:
        sql_std = "SELECT s.filename FROM fabrics_additional_infos f "\
            "inner JOIN standard_lining s on f.standard_lining_id = s.id "\
            "WHERE fabric_id= :fabric_id"
        res = await sql_exe(sql_std, {"fabric_id": item.fabric_id}, True)
        if res:
            lining_fabric_id = res.filename
        else:
            lining_fabric_id = ""
    sql_choice = 'SELECT c.id, c.reference_id, c.feature_cover_img_id, c.'\
        'base_id, c.default_feature_id,c.hide_feature_id, t.name as template,'\
        'cp.filename FROM choice_comb c INNER JOIN choice_comb_photo cp on '\
        'c.id = cp.choice_comb_id INNER JOIN templates t on t.id = c.'\
        'template_id WHERE c.deleted=0 AND cp.deleted=0 and t.is_active=1 '\
        'and c.no_image = 0 and c.id in :c_id'

    sql_feature = "SELECT f.id, f.filename, f.position,fc.name as type, f."\
        "is_satin, f.feature_extra_category_id FROM feature_cover_images f "\
        "INNER JOIN feature_category fc on fc.id = f.feature_category_id "\
        "WHERE f.is_active = 1 and fc.is_active = 1 and f.is_default = 0 "\
        "and f.id in ({})"

    sql_default = "SELECT f.id, f.filename, f.position,fc.name as type, f.is_satin "\
        "FROM feature_cover_images f INNER JOIN feature_category fc on "\
        "fc.id = f.feature_category_id WHERE f.is_active = 1 and fc."\
        "is_active = 1 and f.is_default = 1 and f.id in ({})"
    try:
        code = 1
        message = "Data fetched successfully"
        images = {}
        sql1 = await sql_exe(sql_choice, {"c_id": item.choices})
        choices = {}
        base = []
        hide = []
        for i in sql1:
            if i.base_id == 0:
                choices[i.reference_id] = i.filename
                sql2 = await sql_exe(sql_feature.format(i.feature_cover_img_id))
                for j in sql2:
                    if j.type not in images:
                        images[j.type] = []
                    file = f"{i.filename}-{j.filename}.png"
                    if j.feature_extra_category_id == 1:
                        file = f"{i.template}/lining/{lining_fabric_id}/{file}"
                    elif j.is_satin:
                        file = f"{i.template}/{file}"
                    else:
                        file = f"{i.template}/{item.fabric_id}/{file}"
                    images[j.type].append({"id": j.id, "position": j.position,
                                           "image": image(file, 'images')})
            if i.default_feature_id:
                sql2 = await sql_exe(sql_default.format(i.default_feature_id))
                for j in sql2:
                    if j.type not in images:
                        images[j.type] = []
                        file = f"{i.template}/{item.fabric_id}/"\
                            f"default-{j.filename}.png"
                    elif j.is_satin:
                        file = f"{i.template}/default-{j.filename}.png"
                    else:
                        file = f"{i.template}/{item.fabric_id}/"\
                            "default-{j.filename}.png"
                    images[j.type].append({"id": j.id, "position": j.position,
                                           "image": image(file, 'images')})
            if i.base_id != 0:
                sql3 = await sql_exe(sql_feature.format(i.feature_cover_img_id), single=True)
                base.append({"base_id": i.base_id, "file": i.filename,
                            "id": i.feature_cover_img_id, "position": sql3.position, "type": sql3.type, "is_satin": sql3.is_satin, "template": i.template, "feature_extra_category_id": sql3.feature_extra_category_id})
            if i.hide_feature_id:
                hide.extend(map(int, i.hide_feature_id.split(',')))
        for i in base:
            if i["base_id"] in choices:
                base_file = f'{choices[i["base_id"]]}-{i["file"]}.png'
                if i["feature_extra_category_id"] == 1:
                    base_file = f'{i["template"]}/lining/{lining_fabric_id}/"\
                        f"{base_file}'
                elif i["is_satin"]:
                    base_file = f'{i["template"]}/{base_file}'
                else:
                    base_file = f'{i["template"]}/{item.fabric_id}/{base_file}'
                images[i["type"]].append({"id": int(i["id"]), "position": i["position"],
                                          "image": image(base_file, 'images')})
        front = [i for i in images.get("front", []) if i["id"] not in hide]
        rear = [i for i in images.get("rear", []) if i["id"] not in hide]
        output = {"front": sorted(front, key=lambda k: k["position"]),
                  "rear": sorted(rear, key=lambda k: k["position"])}
    except Exception as e:
        print(e)
        code = 0
        message = "Failed to collect the data"
        output = {}
    return {"code": code, "message": message, "data": output}


# Angika - updated for 3D
@app.post('/api/v1/feature_objects/', tags=["Images"])
async def get_feature_objects(item: Image, entity_id: int = Depends(auth)):
    fabric = f'{i["template"]}/{item.fabric_id}'
    if item.lining_fabric_id:
        lining_fabric_id = item.lining_fabric_id
        lining_fabric = f'{i["template"]}/{lining_fabric_id}'
    else:
        sql_std = "SELECT s.filename FROM fabrics_additional_infos f "\
            "inner JOIN standard_lining s on f.standard_lining_id = s.id "\
            "WHERE fabric_id= :fabric_id"
        res = await sql_exe(sql_std, {"fabric_id": item.fabric_id}, True)
        if res:
            lining_fabric_id = res.filename
            lining_fabric = f'{i["template"]}/{lining_fabric_id}'
        else:
            lining_fabric_id = ""
            lining_fabric = ""
    sql_choice = 'SELECT c.id, c.reference_id, c.feature_cover_img_id, c.'\
        'base_id, c.default_feature_id,c.hide_feature_id, t.name as template,'\
        'cp.filename FROM choice_comb c INNER JOIN choice_comb_photo cp on '\
        'c.id = cp.choice_comb_id INNER JOIN templates t on t.id = c.'\
        'template_id WHERE c.deleted=0 AND cp.deleted=0 and t.is_active=1 '\
        'and c.no_image = 0 and c.id in :c_id'

    sql_feature = "SELECT f.id, f.filename, f.position,fc.name as type, f."\
        "is_satin, f.feature_extra_category_id FROM feature_cover_images f "\
        "INNER JOIN feature_category fc on fc.id = f.feature_category_id "\
        "WHERE f.is_active = 1 and fc.is_active = 1"\
        "and f.id in ({})"
# Angika - updated for 3D (need only the filenames)
    try:
        code = 1
        message = "Data fetched successfully"
        three_objects = {} #Angika
        sql1 = await sql_exe(sql_choice, {"c_id": item.choices})
        choices = {}
        base = []
        hide = []
        for i in sql1:
            if i.base_id == 0:
                choices[i.reference_id] = i.filename
                sql2 = await sql_exe(sql_feature.format(i.feature_cover_img_id))
                for j in sql2:
                    if j.type not in images:
                        three_objects[j.type] = [] #Angika
                    three_file = f"{i.filename}-{j.filename}"  #Angika single-breasted-1-btn-notch-lapel-body_front
                    three_objects[j.type].append({"obj": three_file}) #Angika
            if i.base_id != 0:
                sql3 = await sql_exe(sql_feature.format(i.feature_cover_img_id), single=True)
                base.append({"base_id": i.base_id, "file": i.filename,
                            "id": i.feature_cover_img_id, "position": sql3.position, "type": sql3.type, "is_satin": sql3.is_satin, "template": i.template, "feature_extra_category_id": sql3.feature_extra_category_id})
            if i.hide_feature_id:
                hide.extend(map(int, i.hide_feature_id.split(',')))
        for i in base:
            if i["base_id"] in choices:
                base_file = f'{choices[i["base_id"]]}-{i["file"]}'
                three_objects[i["type"]].append({"id": int(i["id"]), 
                                          "obj": base_file})
        front = [i for i in three_objects.get("front", []) if i["id"] not in hide]
        rear = [i for i in three_objects.get("rear", []) if i["id"] not in hide]
        output = {"front": front,"rear": rear,"fabric":fabric,"lining_fabric":lining_fabric}
    except Exception as e:
        print(e)
        code = 0
        message = "Failed to collect the data"
        output = {}
    return {"code": code, "message": message, "data": output}
# Angika -ends

@app.post('/api/v1/quantity/', tags=["Cart"])
async def adjust_quantity(item: Qty, entity_id: int = Depends(auth)):
    sql = "select quote_id, base_cost, weight from quote_item where item_id = :quote_item_id"
    res = await sql_exe(sql, {"quote_item_id": item.quote_item_id}, True)
    total_price = res.base_cost * item.qty
    total_weight = res.weight * item.qty

    sql = "update quote_item set  price = :total_price, base_price= :total_price, custom_price= :total_price, row_total = :total_price, base_row_total = :total_price, original_custom_price = :total_price, price_incl_tax = :total_price, base_price_incl_tax = :total_price, row_total_incl_tax = :total_price, base_row_total_incl_tax = :total_price, qty = :qty, row_weight = :total_weight  where item_id = :quote_item_id"
    await sql_exe(sql, {"total_price": total_price, "total_weight": total_weight, "qty": item.qty, "quote_item_id": item.quote_item_id})

    sql = "SELECT quote_id, SUM(custom_price) as quote_total, SUM(qty) as quote_qty FROM quote_item where quote_id = :quote_id"
    res = await sql_exe(sql, {"quote_id": res.quote_id}, True)

    quote_sql = "SELECT coupon_code, discount_amt from quote where entity_id = :quote_id"
    quote_sql_res = await sql_exe(quote_sql, {"quote_id": res.quote_id}, True)

    customer_promotion_sql = "update customer_promotions c set is_active = 1 WHERE c.customer_id = :customer_id and c.coupon_code = :coupon_code"
    await sql_exe(customer_promotion_sql, {'customer_id': entity_id, 'coupon_code': quote_sql_res.coupon_code})

    sql = "update quote set grand_total = :quote_total, base_grand_total = :quote_total, subtotal = :quote_total, base_subtotal = :quote_total,subtotal_with_discount = :quote_total, base_subtotal_with_discount= :quote_total, items_count = :quote_qty, items_qty = :quote_qty, discount_amt = 0, coupon_code = '' where entity_id = :quote_id"
    await sql_exe(sql, {"quote_total": res.quote_total, "quote_qty": res.quote_qty, "quote_id": res.quote_id})

    sql = "SELECT price, cost FROM flat_rate_shipping_method where code='flatrate_flatrate' "
    res1 = await sql_exe(sql, {}, True)
    if res.quote_total < res1.price:
        shipping_cost = res1.cost
    else:
        shipping_cost = 0
    grand_total = res.quote_total + shipping_cost

    sql3 = 'Select discount, status, id, customer_id, created_at FROM referral_orders where referrer_id = :customer_id AND status not in ("3")'
    now = datetime.now()
    discount = 0
    res3 = await sql_exe(sql3, {"customer_id": entity_id})
    for i in res3:
        if (now.date() - i.created_at.date()).days <= 730:
            sql4 = "Select balance FROM discount_calculation where referral_order_id = :id order by id desc limit 1"
            res4 = await sql_exe(sql4, {"id": i.id}, single=True)
            if res4:
                discount += res4.balance
            else:
                discount += i.discount

    output = {
        "quote_total": res.quote_total,
        "qty": res.quote_qty,
        "shipping_amount": shipping_cost,
        "grand_total": grand_total,
        "reward": discount
    }
    return {"code": 1, "message": "Success", "data": output}


@app.post('/api/v1/cart/', tags=["Cart"])
async def post_cart(item: Cart, request: Request, entity_id: int = Depends(auth)):
    total_price = 0

    sql = "select piece_group from quote_item_data order by id desc limit 1"
    quo_res = await sql_exe(sql, {}, True)
    if quo_res:
        piece_group = quo_res.piece_group
    else:
        piece_group = "G0"

    sql = "select c.firstname, c.lastname, c.gender, "\
        'c.email, q.grand_total, q.subtotal as sub_total, q.coupon_code, '\
        'q.discount_amt, IF(q.items_count!="",q.items_count,0) as cart_count,'\
        'IF(q.entity_id!="",q.entity_id,0) as entity_id from customer_entity '\
        "c Left JOIN quote q ON c.entity_id = q.customer_id AND "\
        "q.is_active = '1' WHERE c.entity_id = :entity_id"
    cus_res = await sql_exe(sql, {'entity_id': entity_id}, True)
    if not cus_res:
        return {"code": 0, "message": "Cart not found"}
    cart_count = cus_res.cart_count
    if cus_res.entity_id:
        quote_id = cus_res.entity_id
    else:
        sql = "INSERT INTO quote (customer_id, customer_email, "\
            "customer_firstname, customer_lastname, remote_ip,"\
            "customer_gender) VALUES (:entity_id, :email, :firstname,"\
            ":lastname, :ip, :gender)"
        data = {"entity_id": entity_id, "email": cus_res.email,
                "firstname": cus_res.firstname, "lastname": cus_res.lastname,
                "ip": request.client.host, "gender": cus_res.gender}
        res = await sql_exe(sql, data)
        quote_id = res['id']

    if item.type_id == 1:
        cart_count += 1
        piece_group = f"G{int(piece_group.replace('G', '')) + 1}"

        sql = 'select name_front as name, collection, weight, c.entity_id as '\
            'category_id, price from items inner join catalog_category_entity c on '\
            'c.item_id =items.id where items.id =:item_id and items.deleted =0'
        item_details = await sql_exe(sql, {'item_id': item.item_id}, True)

        # To get sku  - Angika updated
        $product_sql = "select sku from catalog_product_entity where entity_id = :fabric_id"
        $product_result =  await sql_exe(sql, {'fabric_id': item.fabric_id}, True)

        sql = "INSERT INTO quote_item (quote_id, product_id, sku, name, weight, qty,row_weight, piece_group, category_name, category_id, collection) VALUES ( :quote_id, :fabric_id, :sku, :fabric_name, :weight, 1.0000,:weight, :piece_group, :name, :category_id, :collection)"
        data = {"quote_id": quote_id, "fabric_id": item.fabric_id,
                "fabric_name": item.fabric_name, "name": item_details.name,
                "weight": item_details.weight, "piece_group": piece_group,
                "category_id": item_details.category_id,
                "collection": item_details.collection,
                "sku": $product_result.sku}
        res = await sql_exe(sql, data)
        # Angika updation end  

        # quote item option serialize
        sql_quote_option = "INSERT INTO quote_item_option (item_id, product_id, code, value) VALUES (:item_id, :product_id, :code, :value)"
        data = {"item_id": res['id'], "code": "info_buyRequest",
                "product_id": item.fabric_id,
                "value": json.dumps({"fabric_id": item.fabric_id, "fabric_name": item.fabric_name, "item_id": item_details.category_id})}
        await sql_exe(sql_quote_option, data)

        sql_quote_option = "INSERT INTO quote_item_option (item_id, product_id, code, value) VALUES (:item_id, :product_id, :code, :value)"
        data = {"item_id": res['id'], "code": "additional_options",
                "product_id": item.fabric_id,
                "value": dumps([{"label": "category", "value": item_details.name}])}
        await sql_exe(sql_quote_option, data)

        quote_item = res['id']
        addtl_price = 0

        for option in item.options_data:
            # print('#'*20, "Options\n", option, '\n')

            piece_id = option.piece_id
            sql = "select price, weight from pieces where id =:piece_id and deleted=0"
            res = await sql_exe(sql, {'piece_id': piece_id}, True)
            piece_price = res.price
            piece_weight = res.weight

            all_options = {}
            combination_id = []
            add_info = {}
            ex_fab = {}
            monogram_price = 0
            lining_price = 0
            for feature in option.features:
                # print('$'*20, "features\n", feature, '\n')

                if feature.id == 1:
                    sql = 'select o.id as ex_option_id, c.id as ex_choice_id,'\
                        'c.extra_cost from options o inner join options_pieces '\
                        'op on op.option_id = o.id and op.deleted=0 inner join '\
                        'choices c on c.option_id = o.id where op.piece_id = '\
                        ':piece_id and o.is_hide_front = 0 and c.extra_cost'\
                        '!="" and has_additional_fabrics = 1 and c.extra_cost!=0'
                    res = await sql_exe(sql, {'piece_id': piece_id}, True)
                    if res:
                        all_options[res.ex_option_id] = str(res.ex_choice_id)
                        ex_fab[res.ex_choice_id] = str(
                            feature.selected_value['id'])
                        lining_price = res.extra_cost

                elif feature.id == 2:
                    for selected in feature.selected_value:
                        # print(selected)
                        if not selected['choice_id']:
                            continue
                        combination_id.append(str(selected['choice_id']))
                        sql = 'select comb from choice_comb where id ='\
                            ':choice_id and deleted = 0'
                        res = await sql_exe(sql, {'choice_id': selected['choice_id']}, True)
                        if res:
                            all_options.update(json.loads(res.comb))
                            # print(2, selected['id'], res)
                    combination_id = ",".join(combination_id)

                elif feature.id == 3:
                    for selected in feature.selected_value:
                        all_options[selected['id']] = str(
                            selected['choice_id'])
                        add_data = {}
                        if selected.get('additional_infos'):
                            for additional in selected['additional_infos']:
                                # print('#'*20, "additional\n", additional, '\n')
                                add_data[additional['info_id']
                                         ] = additional['value']
                            add_info[selected['choice_id']] = add_data
                            sql = "select extra_cost from choices where id = :choice_id and deleted = 0"
                            res = await sql_exe(sql, {'choice_id': selected['choice_id']}, True)
                            monogram_price = res.extra_cost
                            if not monogram_price:
                                monogram_price = 0
                        else:
                            add_info[selected['choice_id']] = "null"

            json_array = {"options": all_options, "additional_fabrics": ex_fab,
                          "additional_info": add_info}
            json_array = json.dumps(json_array)
            # print("\n\n**************json_array", json_array, "\n\n")

            sql = "insert into quote_item_data (item_id, fabric_id, itemid, piece_id, options, choice_comb_id, piece_group, collection, piece_price, monogram_price, lining_price, sub_category_id, weight, comment) values (:quote_item, :fabric_id, :item_id, :piece_id, :json_array, :combination_id, :piece_group, :collection, :piece_price, :monogram_price, :lining_price, :category_id, :weight, :comment)"
            data = {"quote_item": quote_item, "fabric_id": item.fabric_id,
                    "item_id": item.item_id, "piece_id": piece_id,
                    "json_array": json_array,
                    "combination_id": combination_id,
                    "piece_group": piece_group,
                    "collection": item_details.collection,
                    "piece_price": piece_price, "monogram_price": monogram_price,
                    "lining_price": lining_price, "category_id": item_details.category_id,
                    "weight": piece_weight, "comment": option.comment}
            addtl_price += monogram_price + lining_price
            # print("monogram_price: ", monogram_price,
            #       "lining_price: ", lining_price)
            # print("addtl_price", addtl_price)
            res = await sql_exe(sql, data)
            # print(4, res)
        # print("addtl_price", addtl_price)
        # print("item_details.price", item_details.price)
        total_price = int(item_details.price) + addtl_price
        quote_loop = {quote_item: total_price}

    elif item.type_id == 2:
        quote_loop = {}
        for option in item.options_data:
            # print('#'*20, "Options\n", option, '\n')
            piece_id = option.piece_id

            sql = "select price, weight from pieces where id =:piece_id and deleted=0"
            res = await sql_exe(sql, {'piece_id': piece_id}, True)
            piece_price = res.price
            piece_weight = res.weight

            sql = "select items.id,name_front as name,collection,weight,c.entity_id as category_id from items inner join catalog_category_entity c on c.item_id = items.id inner join items_pieces on items_pieces.item_id = c.item_id where items_pieces.piece_id = :piece_id and items.deleted = 0 and items_pieces.deleted = 0"
            item_details = await sql_exe(sql, {'piece_id': piece_id}, True)

            all_options = {}
            combination_id = []
            add_info = {}
            ex_fab = {}
            monogram_price = 0
            lining_price = 0
            for feature in option.features:
                # print('$'*20, "features\n", feature, '\n')

                if feature.id == 4:
                    cart_count += 1
                    fabric_id = feature.selected_value['id']
                    fabric_name = feature.selected_value['fabric_name']
                    piece_group = f"G{int(piece_group.replace('G', '')) + 1}"
                   
                    # To get sku  - Angika updated
                    $product_sql = "select sku from catalog_product_entity where entity_id = :fabric_id"
                    $product_result =  await sql_exe(sql, {'fabric_id': fabric_id}, True)

                    sql = "INSERT INTO quote_item (quote_id, product_id, sku, name, weight, qty, row_weight, piece_group, category_name, category_id, collection) VALUES (:quote_id, :fabric_id, :sku, :fabric_name, :weight, 1.0000, :weight, :piece_group, :name, :category_id, :collection)"
                    data = {"quote_id": quote_id, "fabric_id": fabric_id, "fabric_name": fabric_name, "weight": item_details.weight,
                            "piece_group": piece_group, "name": item_details.name, "category_id": item_details.category_id, "collection": item_details.collection, "sku": $product_result.sku}
                    res = await sql_exe(sql, data)
                    # Angika updation ends

                    # quote item option serialize
                    sql_quote_option = "INSERT INTO quote_item_option (item_id, product_id, code, value) VALUES (:item_id, :product_id, :code, :value)"
                    data = {"item_id": res['id'], "code": "info_buyRequest",
                            "product_id": fabric_id,
                            "value": json.dumps({"fabric_id": fabric_id, "fabric_name": fabric_name, "item_id": item_details.category_id})}
                    await sql_exe(sql_quote_option, data)

                    sql_quote_option = "INSERT INTO quote_item_option (item_id, product_id, code, value) VALUES (:item_id, :product_id, :code, :value)"
                    data = {"item_id": res['id'], "code": "additional_options",
                            "product_id": fabric_id,
                            "value": dumps([{"label": "category", "value": item_details.name}])}
                    await sql_exe(sql_quote_option, data)

                    quote_item = res['id']
                    quote_loop[quote_item] = 0

                elif feature.id == 1:
                    sql = 'select o.id as ex_option_id, c.id as ex_choice_id,'\
                        'c.extra_cost from options o inner join options_pieces '\
                        'op on op.option_id = o.id and op.deleted=0 inner join '\
                        'choices c on c.option_id = o.id where op.piece_id = '\
                        ':piece_id and o.is_hide_front = 0 and c.extra_cost'\
                        '!="" and has_additional_fabrics = 1 and c.extra_cost!=0'
                    res = await sql_exe(sql, {'piece_id': piece_id}, True)
                    if res:
                        all_options[res.ex_option_id] = str(res.ex_choice_id)
                        ex_fab[res.ex_choice_id] = str(
                            feature.selected_value['id'])
                        lining_price = res.extra_cost
                        quote_loop[quote_item] += lining_price
                        # print(1, res)

                elif feature.id == 2:
                    for selected in feature.selected_value:
                        if not selected['choice_id']:
                            continue
                        combination_id.append(str(selected['choice_id']))
                        sql = 'select comb from choice_comb where id ='\
                            ':choice_id and deleted = 0'
                        res = await sql_exe(sql, {'choice_id': selected['choice_id']}, True)
                        if res:
                            all_options.update(json.loads(res.comb))
                            # print(2, selected['id'], res)
                    combination_id = ",".join(combination_id)

                elif feature.id == 3:
                    for selected in feature.selected_value:
                        all_options[selected['id']] = str(
                            selected['choice_id'])
                        add_data = {}
                        if selected.get('additional_infos'):
                            for additional in selected['additional_infos']:
                                # print('#'*20, "additional\n", additional, '\n')
                                add_data[additional['info_id']
                                         ] = additional['value']
                            add_info[selected['choice_id']] = add_data
                            sql = "select extra_cost from choices where id = :choice_id and deleted = 0"
                            res = await sql_exe(sql, {'choice_id': selected['choice_id']}, True)
                            monogram_price = res.extra_cost
                            if not monogram_price:
                                monogram_price = 0
                            quote_loop[quote_item] += monogram_price
                        else:
                            add_info[selected['choice_id']] = "null"
                        # print(3, selected['id'], add_info)

            quote_loop[quote_item] += piece_price

            json_array = {"option": all_options, "additional_fabrics": ex_fab,
                          "additional_info": add_info}
            json_array = json.dumps(json_array)
            # print("\n\n**************json_array", json_array, "\n\n")
            # print(combination_id)

            sql = "insert into quote_item_data (item_id, fabric_id, itemid, piece_id, options, choice_comb_id, piece_group, collection, piece_price, monogram_price, lining_price, sub_category_id, weight, comment) values (:quote_item, :fabric_id, :item_id, :piece_id, :json_array, :combination_id, :piece_group, :collection, :piece_price, :monogram_price, :lining_price, :category_id, :weight, :comment)"
            data = {"quote_item": quote_item, "fabric_id": fabric_id,
                    "item_id": item.item_id, "piece_id": piece_id,
                    "json_array": json_array,
                    "combination_id": combination_id,
                    "piece_group": piece_group,
                    "collection": item_details.collection,
                    "piece_price": piece_price, "monogram_price": monogram_price,
                    "lining_price": lining_price, "category_id": item_details.category_id,
                    "weight": piece_weight, "comment": option.comment}
            res = await sql_exe(sql, data)
            # print(4, res)

            total_price += piece_price + lining_price + monogram_price

    for quote_item, price in quote_loop.items():
        sql = "update quote_item set price = :total_price, base_price = :total_price, custom_price = :total_price, row_total = :total_price, base_row_total = :total_price,original_custom_price = :total_price, price_incl_tax = :total_price, base_price_incl_tax = :total_price, row_total_incl_tax = :total_price, base_row_total_incl_tax = :total_price, base_cost = :total_price where item_id = :quote_item"
        data = {"total_price": price, "quote_item": quote_item}
        res = await sql_exe(sql, data)
    sub_total = cus_res.sub_total
    if not sub_total:
        sub_total = 0
    sub_total = sub_total + total_price
    customer_promotion_sql = "update customer_promotions c set is_active = 1 WHERE c.customer_id = :customer_id and c.coupon_code = :coupon_code"
    await sql_exe(customer_promotion_sql, {'customer_id': entity_id, 'coupon_code': cus_res.coupon_code})

    sql = "update quote set grand_total = :sub_total, base_grand_total = :sub_total, subtotal = :sub_total, base_subtotal = :sub_total,subtotal_with_discount = :sub_total, base_subtotal_with_discount= :sub_total, items_count = :cart_count, items_qty = :cart_count, discount_amt = 0, coupon_code = '' where entity_id = :quote"
    data = {"sub_total": sub_total,
            "quote": quote_id, "cart_count": cart_count}
    res = await sql_exe(sql, data)

    return {"code": 1, "message": "Item added to cart"}


async def single_feature_image(piece_id, comb_id, fabric_id, lining_fabric_id):
    return_value = {}
    sql4 = "SELECT o.custom_name as option_name, o.id as option_id, ccp.filename, cc.id as choice_id FROM choice_comb cc INNER JOIN choice_comb_photo ccp on ccp.choice_comb_id = cc.id INNER JOIN options o on o.id = cc.reference_id where cc.id in :comb_id"

    res4 = await sql_exe(sql4, {"comb_id": comb_id})
    return_value["selected_value"] = [{"option_id": i.option_id, "choice_id": i.choice_id, "name": "Type of Overcoat" if i.option_name == "Type of Jacket" and piece_id == 6 else i.option_name, "value": i.filename.title(
    ).replace('-', ' ').replace('_', ' ')} for i in res4]

    sql_choice = 'SELECT c.id, c.reference_id, c.feature_cover_img_id, c.'\
        'base_id, c.default_feature_id,c.hide_feature_id, t.name as template,'\
        'cp.filename FROM choice_comb c INNER JOIN choice_comb_photo cp on '\
        'c.id = cp.choice_comb_id INNER JOIN templates t on t.id = c.'\
        'template_id WHERE c.deleted=0 AND cp.deleted=0 and t.is_active=1 '\
        'and c.no_image = 0 and c.id in :c_id'

    sql_feature = "SELECT f.id, f.filename, f.position,fc.name as type, f."\
        "is_satin, f.feature_extra_category_id FROM feature_cover_images f "\
        "INNER JOIN feature_category fc on fc.id = f.feature_category_id "\
        "WHERE f.is_active = 1 and fc.is_active = 1 and f.is_default = 0 "\
        "and f.id in ({})"

    sql_default = "SELECT f.id, f.filename, f.position,fc.name as type, f.is_satin "\
        "FROM feature_cover_images f INNER JOIN feature_category fc on "\
        "fc.id = f.feature_category_id WHERE f.is_active = 1 and fc."\
        "is_active = 1 and f.is_default = 1 and f.id in ({})"

    images = {}
    sql1 = await sql_exe(sql_choice, {"c_id": comb_id})
    choices = {}
    base = []
    hide = []
    for i in sql1:
        # print(i)
        if i.base_id == 0:
            choices[i.reference_id] = i.filename
            sql2 = await sql_exe(sql_feature.format(i.feature_cover_img_id))
            for j in sql2:
                if j.type not in images:
                    images[j.type] = []
                file = f"{i.filename}-{j.filename}.png"
                if j.feature_extra_category_id == 1:
                    file = f"{i.template}/lining/{lining_fabric_id}/{file}"
                elif j.is_satin:
                    file = f"{i.template}/{file}"
                else:
                    file = f"{i.template}/{fabric_id}/{file}"
                images[j.type].append({"id": j.id, "position": j.position,
                                       "image": image(file, 'images')})
        if i.default_feature_id:
            sql2 = await sql_exe(sql_default.format(i.default_feature_id))
            for j in sql2:
                if j.type not in images:
                    images[j.type] = []
                    file = f"{i.template}/{fabric_id}/default-{j.filename}.png"
                elif j.is_satin:
                    file = f"{i.template}/default-{j.filename}.png"
                else:
                    file = f"{i.template}/{fabric_id}/default-{j.filename}.png"

                # if j.type not in images:
                #     images[j.type] = []
                # file = f"{i.template}/{fabric_id}/default-{j.filename}.png"
                images[j.type].append({"id": j.id, "position": j.position,
                                       "image": image(file, 'images')})
        if i.base_id != 0:
            sql3 = await sql_exe(sql_feature.format(i.feature_cover_img_id), single=True)
            base.append({"base_id": i.base_id, "file": i.filename,
                        "id": i.feature_cover_img_id, "position": sql3.position, "type": sql3.type, "is_satin": sql3.is_satin, "template": i.template,
                         "feature_extra_category_id": sql3.feature_extra_category_id})
        if i.hide_feature_id:
            hide.extend(map(int, i.hide_feature_id.split(',')))
    for i in base:
        if i["base_id"] in choices:
            base_file = f'{choices[i["base_id"]]}-{i["file"]}.png'
            if i["feature_extra_category_id"] == 1:
                base_file = f'{i["template"]}/lining/"\
                    f"{lining_fabric_id}/{base_file}'
            elif i["is_satin"]:
                base_file = f'{i["template"]}/{base_file}'
            else:
                base_file = f'{i["template"]}/{fabric_id}/{base_file}'
            images[i["type"]].append({"id": int(i["id"]), "position": i["position"],
                                      "image": image(base_file, 'images')})
    # print(images)
    front = [i for i in images.get("front", []) if i["id"] not in hide]
    rear = [i for i in images.get("rear", []) if i["id"] not in hide]
    output_1 = {"front": sorted(front, key=lambda k: k["position"]),
                "rear": sorted(rear, key=lambda k: k["position"])}
    return_value["feature_images"] = output_1

    return return_value


async def sep_get_cart(res, entity_id, get_cart=0):
    if get_cart:
        sql2 = "SELECT price, cost FROM flat_rate_shipping_method where code = 'flatrate_flatrate'"
        res2 = await sql_exe(sql2, single=True)
        if res.quote_total < res2.price:
            shipping_cost = res2.cost
        else:
            shipping_cost = 0
        grand_total = res.quote_total + shipping_cost - res.discount_amt

        sql3 = 'Select discount, status, id, customer_id, created_at FROM referral_orders where referrer_id = :customer_id AND status not in ("3")'
        now = datetime.now()
        discount = 0
        res3 = await sql_exe(sql3, {"customer_id": entity_id})
        for i in res3:
            if (now.date() - i.created_at.date()).days <= 730:
                sql4 = "Select balance FROM discount_calculation where referral_order_id = :id order by id desc limit 1"
                res4 = await sql_exe(sql4, {"id": i.id}, single=True)
                if res4:
                    discount += res4.balance
                else:
                    discount += i.discount
        output = {"quote_id": res.id, "shipping_cost": shipping_cost, "reward": discount, 'discount_amt': res.discount_amt, 'coupon_code': res.coupon_code, "quote_total": res.quote_total,
                  "grand_total": grand_total, "weight": 0, "quote_item": []}
    else:
        output = {"quote_id": res.id, "shipping_cost": res.shipping_amount, "discount": res.discount_amount, "quote_total": res.quote_total,
                  "grand_total": res.grand_total, "total_paid": res.total_paid, "weight": 0, "quote_item": []}

    # check if quote has items

    sql2 = "SELECT qi.item_id as id,qi.piece_group, qi.product_id as fabric_id, qi.name as fabric_name, qi.row_weight, qi.qty, qi.custom_price as item_total, qi.category_name as item_name FROM quote_item qi where qi.quote_id = :quote_id order by qi.item_id desc"
    res2 = await sql_exe(sql2, {"quote_id": res.id})
    # print(res2)
    total_weight = 0
    for item_id in res2:
        total_weight += item_id.row_weight
        quote_data = {"quote_item_id": item_id.id, "fabric_id": item_id.fabric_id,  "fabric_name": item_id.fabric_name,
                      "item_name": item_id.item_name, "item_total": item_id.item_total, "quantity": item_id.qty,  "item_data": []}

        sql4 = "SELECT item_id FROM quote_item_data WHERE piece_group = :piece_group and item_id = :item_id"
        res4 = await sql_exe(sql4, {"piece_group": item_id.piece_group, "item_id": item_id.id})
        # need to add if not condition - Naveen
        sql5 = "update quote_item_data set item_id = :item_id where piece_group = :piece_group and reorder = 0"
        await sql_exe(sql5, {'piece_group': item_id.piece_group, 'item_id': item_id.id})
        sql3 = "SELECT qid.id, qid.piece_id, qid.piece_price, qid.lining_price, qid.monogram_price, qid.choice_comb_id, qid.options, p.name_front as piece_name, qid.comment FROM quote_item_data qid INNER JOIN pieces p on p.id = qid.piece_id WHERE qid.item_id = :quote_item_id"
        res3 = await sql_exe(sql3, {"quote_item_id": item_id.id})
        for r in res3:
            # print("r", r)
            item_data_out = {"quote_item_data_id": r.id,
                             "piece_price": int(r.piece_price) + int(r.lining_price) + int(r.monogram_price),
                             "piece_name": r.piece_name, "comment": r.comment}
            comb_id = tuple(map(int, r.choice_comb_id.split(',')))
            options = json.loads(r.options)
            if options.get('additional_fabrics', {}) and list(options['additional_fabrics'].values())[0]:
                lining_fabric_id = list(
                    options['additional_fabrics'].values())[0]
                item_data_out["additional_fabrics"] = lining_fabric_id
                # sql5 = "SELECT sample_file_name FROM fabrics WHERE id = :lining_fabric_id and deleted = 0 and is_hidden = 0 and front_end = 1"
                # res5 = await sql_exe(sql5, {"lining_fabric_id": lining_fabric_id}, True)
                # if res5:
                #     return_value["lining"] = image(
                #         res5.sample_file_name, 'fabric')
                # else:
                #     return_value["lining"] = ""
            else:
                sql5 = "SELECT s.filename FROM fabrics_additional_infos f inner JOIN standard_lining s on f.standard_lining_id = s.id WHERE fabric_id= :fabric_id"
                res5 = await sql_exe(sql5, {"fabric_id": item_id.fabric_id}, True)
                if res5:
                    lining_fabric_id = res5.filename
                else:
                    lining_fabric_id = ""
            accent_data = []

            if options.get('additional_info', []):
                for addinfo in options['additional_info'].values():
                    if type(addinfo) == dict:
                        accent_data.append(",".join(addinfo.values()))
            item_data_out["additional_info"] = {
                "heading": "Embroidery", "value": accent_data}
            imgs = await single_feature_image(r.piece_id, comb_id, item_id.fabric_id, lining_fabric_id)
            item_data_out.update(imgs)
            quote_data["item_data"].append(item_data_out)
        output["quote_item"].append(quote_data)
    output["weight"] = total_weight
    return output


async def list_user_cart(entity_id: int):
    sql = "SELECT q.entity_id as id, q.items_count, q.grand_total as grand_total, q.subtotal as quote_total, q.discount_amt as discount_amt, q.coupon_code FROM quote q WHERE q.customer_id = :entity_id and q.is_active = 1"
    res = await sql_exe(sql, {'entity_id': entity_id}, single=True)

    if res and res.items_count:
        output = await sep_get_cart(res, entity_id, 1)
        return {"code": 1, "message": "Cart data fetched successfully", "data": output}
    else:
        return {"code": 0, "message": "There is no item in your cart. Continue shopping"}


@app.get('/api/v1/cart/', tags=["Cart"])
async def list_cart(entity_id: int = Depends(auth)):
    return await list_user_cart(entity_id)


@app.get('/api/v1/cart/{entity_id}', tags=["Admin"])
async def admin_list_cart(entity_id: int, common=Depends(common_auth)):
    return await list_user_cart(entity_id)


@app.get('/api/v1/cart/{quote_item_data_id}', tags=["Cart"])
async def get_cart(quote_item_data_id: int, entity_id: int = Depends(auth)):
    sql3 = "SELECT qid.id, qid.piece_price,qid.fabric_id, qid.piece_id, qid.lining_price, qid.monogram_price, qid.choice_comb_id, qid.options, p.name_front as piece_name, qid.comment, qid.fabric_id FROM quote_item_data qid INNER JOIN pieces p on p.id = qid.piece_id WHERE qid.id = :quote_item_data_id"
    res3 = await sql_exe(sql3, {"quote_item_data_id": quote_item_data_id}, True)
    # print("r", r)
    item_data_out = {"quote_item_data_id": res3.id,
                     "piece_id": res3.piece_id,
                     "fabric_id": res3.fabric_id,
                     "piece_price": int(res3.piece_price) + int(res3.lining_price) + int(res3.monogram_price),
                     "piece_name": res3.piece_name, "comment": res3.comment}
    comb_id = tuple(map(int, res3.choice_comb_id.split(',')))
    # sql4 = "SELECT o.custom_name as option_name, ccp.filename FROM choice_comb cc INNER JOIN choice_comb_photo ccp on ccp.choice_comb_id = cc.id INNER JOIN options o on o.id = cc.reference_id where cc.id in :comb_id"
    # res4 = await sql_exe(sql4, {"comb_id": comb_id})
    # item_data_out["selected_value"] = [{"name": "Type of Overcoat" if i.option_name=="Type of Jacket" and res3.piece_id == 6 else i.option_name, "value": i.filename.title(
    # ).replace('-', ' ').replace('_', ' ')} for i in res4]

    options = json.loads(res3.options)
    # data_options = options.get('options', {})
    # mylist = []
    # for key, value in data_options.items():
    #     mylist.append({'option_id': key, 'choice_id': value})
    # item_data_out["styles"] = mylist

    if options.get('additional_fabrics', []):
        lining_fabric_id = list(
            options['additional_fabrics'].values())[0]
        # sql5 = "SELECT sample_file_name FROM fabrics WHERE id = :lining_fabric_id and deleted = 0 and is_hidden = 0 and front_end = 1"
        # res5 = await sql_exe(sql5, {"lining_fabric_id": lining_fabric_id}, True)
        # if res5:
        #     item_data_out["lining"] = lining_fabric_id
        # else:
        #     item_data_out["lining"] = ""
        item_data_out["lining_fabric_id"] = lining_fabric_id
    else:
        sql5 = "SELECT s.filename FROM fabrics_additional_infos f inner JOIN standard_lining s on f.standard_lining_id = s.id WHERE fabric_id= :fabric_id"
        res5 = await sql_exe(sql5, {"fabric_id": res3.fabric_id}, True)
        if res5:
            lining_fabric_id = res5.filename
        else:
            lining_fabric_id = ""
    accent_data = []
    add_infoslist = []
    if options.get('additional_info', []):
        for addinfo in options['additional_info'].values():
            if type(addinfo) == dict:
                accent_data.append(",".join(addinfo.values()))
                for key, value in addinfo.items():
                    add_infoslist.append({'key': key, 'value': value})
            item_data_out["accents"] = add_infoslist
    item_data_out["additional_info"] = {
        "heading": "Embroidery", "value": accent_data}
    imgs = await single_feature_image(res3.piece_id, comb_id, res3.fabric_id, lining_fabric_id)
    item_data_out.update(imgs)
    return {"code": 1, "message": "Cart data fetched successfully", "data": item_data_out}


@app.put('/api/v1/cart/{quote_item_data_id}', tags=["Cart"])
async def update_cart(quote_item_data_id: int, item: UpdateOptions,
                      entity_id: int = Depends(auth)):
    total_price = 0
    sql = "select * from quote_item_data where id = :quote_item_data_id"
    quote_item_data_res = await sql_exe(sql,
                                        {"quote_item_data_id": quote_item_data_id}, True)
    if not quote_item_data_res:
        return {"code": 0, "message": "Item not found in cart"}
    quote_item_data_price = int(quote_item_data_res.piece_price) + int(
        quote_item_data_res.lining_price) + int(quote_item_data_res.monogram_price)

    sql2 = "select custom_price from quote_item where item_id = :item_id"
    res2 = await sql_exe(sql2, {"item_id": quote_item_data_res.item_id}, True)
    items_price = int(res2.custom_price) - quote_item_data_price

    sql3 = "SELECT q.grand_total as quote_total FROM quote q WHERE q.customer_id = :entity_id and q.is_active = 1"
    cus_info = await sql_exe(sql3, {'entity_id': entity_id}, single=True)
    quote_total_price = int(cus_info.quote_total) - quote_item_data_price

    all_options = {}
    combination_id = []
    add_info = {}
    ex_fab = {}
    monogram_price = 0
    lining_price = 0
    for feature in item.features:
        if feature.id == 1:
            sql = 'select o.id as ex_option_id, c.id as ex_choice_id,'\
                'c.extra_cost from options o inner join options_pieces '\
                'op on op.option_id = o.id and op.deleted=0 inner join '\
                'choices c on c.option_id = o.id where op.piece_id = '\
                ':piece_id and o.is_hide_front = 0 and c.extra_cost'\
                '!="" and has_additional_fabrics = 1 and c.extra_cost!=0'
            res = await sql_exe(sql, {'piece_id': quote_item_data_res.piece_id}, True)
            if res:
                all_options[res.ex_option_id] = str(res.ex_choice_id)
                ex_fab[res.ex_choice_id] = str(
                    feature.selected_value['id'])
                lining_price = int(res.extra_cost)

        elif feature.id == 2:
            for selected in feature.selected_value:
                # print(selected)
                combination_id.append(str(selected['choice_id']))
                sql = 'select comb from choice_comb where id ='\
                    ':choice_id and deleted = 0'
                res = await sql_exe(sql, {'choice_id': selected['choice_id']}, True)
                if res:
                    all_options.update(json.loads(res.comb))
                    # print(2, selected['id'], res)
            combination_id = ",".join(combination_id)

        elif feature.id == 3:
            for selected in feature.selected_value:
                all_options[selected['id']] = str(
                    selected['choice_id'])
                add_data = {}
                if selected.get('additional_infos'):
                    for additional in selected['additional_infos']:
                        # print('#'*20, "additional\n", additional, '\n')
                        add_data[additional['info_id']
                                 ] = additional['value']
                    add_info[selected['choice_id']] = add_data
                    sql = "select extra_cost from choices where id = :choice_id and deleted = 0"
                    res = await sql_exe(sql, {'choice_id': selected['choice_id']}, True)
                    if not res.extra_cost:
                        monogram_price = 0
                    else:
                        monogram_price = int(res.extra_cost)
                else:
                    add_info[selected['choice_id']] = "null"

    json_array = {"options": all_options, "additional_fabrics": ex_fab,
                  "additional_info": add_info}
    json_array = json.dumps(json_array)

    sql = "update quote_item_data set options = :json_array, choice_comb_id ="\
        ":combination_id, piece_price = :piece_price, monogram_price = "\
        ":monogram_price, lining_price = :lining_price, comment = :comment where "\
        "id = :quote_item_data_id"
    await sql_exe(sql, {'json_array': json_array,
                        'combination_id': combination_id,
                        'piece_price': quote_item_data_res.piece_price,
                        'monogram_price': monogram_price,
                        'lining_price': lining_price,
                        'comment': item.comment,
                        'quote_item_data_id': quote_item_data_id})

    addtl_price = int(quote_item_data_res.piece_price) + \
        int(monogram_price) + int(lining_price)

    total_price = items_price + addtl_price

    sql = "update quote_item set price = :total_price,base_price= :total_price, custom_price= :total_price, row_total= :total_price, base_row_total= :total_price, original_custom_price= :total_price, price_incl_tax= :total_price, base_price_incl_tax= :total_price, base_cost = :total_price, row_total_incl_tax = :total_price, base_row_total_incl_tax = :total_price where item_id = :item_id"
    await sql_exe(sql, {'total_price': total_price, 'item_id': quote_item_data_res.item_id})

    grand_total = quote_total_price + addtl_price

    sql = "update quote set grand_total = :grand_total, base_grand_total = :grand_total, subtotal = :grand_total, base_subtotal = :grand_total, subtotal_with_discount = :grand_total, base_subtotal_with_discount = :grand_total where customer_id = :entity_id and is_active = 1"
    await sql_exe(sql, {'grand_total': grand_total, 'entity_id': entity_id})

    return {"code": 1, "message": "Item updated successfully"}


@app.delete('/api/v1/cart/', tags=["Cart"])
async def delete_cart(item: DeleteCart, entity_id: int = Depends(auth)):
    if item.type == 'all':
        sql = """select  IF(q.entity_id!="",q.entity_id,0) as quote_id, q.coupon_code from customer_entity c Left JOIN quote q ON c.entity_id = q.customer_id AND q.is_active = '1' WHERE c.entity_id = :customer_id"""
        cus_res = await sql_exe(sql, {'customer_id': entity_id}, True)
        if cus_res:
            quote_id = cus_res.quote_id
        else:
            code = 0
            message = "No cart found"
            return {"code": code, "message": message}
        sql = "Select item_id from quote_item where quote_id = :quote_id"
        res = await sql_exe(sql, {'quote_id': quote_id})
        items = tuple([i.item_id for i in res])
        sql = "Delete from quote_item where quote_id = :quote_id"
        await sql_exe(sql, {'quote_id': quote_id})
        if items:
            sql = "Delete from quote_item_data where item_id in :item_id"
            await sql_exe(sql, {'item_id': items})
            sql = "Delete from quote_item_option where item_id in :item_id"
            await sql_exe(sql, {'item_id': items})
        customer_promotion_sql = "update customer_promotions c set is_active = 1 WHERE c.customer_id = :customer_id and c.coupon_code = :coupon_code"
        await sql_exe(customer_promotion_sql, {'customer_id': entity_id, 'coupon_code': cus_res.coupon_code})

        sql = "Update quote set items_count = 0 , items_qty = 0, grand_total = 0,base_grand_total = 0, subtotal = 0,base_subtotal = 0,subtotal_with_discount = 0, base_subtotal_with_discount= 0, discount_amt = 0, coupon_code = '' where entity_id = :quote_id "
        await sql_exe(sql, {'quote_id': quote_id})
        code = 1
        message = 'Cart deleted successfully'
        return {"code": code, "message": message}

    elif item.type == 'single':
        get_quote_item = "select qty from quote_item where item_id = :item_id"
        res_quote_item = await sql_exe(get_quote_item, {"item_id": item.quote_item_id}, True)
        sql = "delete from quote_item where item_id = :item_id"
        await sql_exe(sql, {"item_id": item.quote_item_id})
        sql1 = "delete from quote_item_data where item_id = :item_id"
        await sql_exe(sql1, {"item_id": item.quote_item_id})
        sql2 = "select items_count, grand_total, subtotal as sub_total, entity_id as quote_id, coupon_code from quote where customer_id = :customer_id and is_active = 1"
        res = await sql_exe(sql2, {"customer_id": entity_id}, True)
        sub_total = res.sub_total - item.price

        customer_promotion_sql = "update customer_promotions c set is_active = 1 WHERE c.customer_id = :customer_id and c.coupon_code = :coupon_code"
        await sql_exe(customer_promotion_sql, {'customer_id': entity_id, 'coupon_code': res.coupon_code})

        sql3 = "Update quote set items_count = :items_count, items_qty = :items_count, grand_total = :sub_total, base_grand_total = :sub_total, subtotal = :sub_total, base_subtotal = :sub_total, subtotal_with_discount = :sub_total, base_subtotal_with_discount= :sub_total, discount_amt = 0, coupon_code = '' where entity_id = :quote_id"
        data = {'sub_total': sub_total, 'items_count': res.items_count - res_quote_item.qty,
                'quote_id': res.quote_id}
        try:
            code = 1
            message = 'Cart deleted successfully'
            data = await sql_exe(sql3, data)
        except Exception as e:
            print(e)
            code = 0
            message = 'Failed to delete cart'
            data = {}
        return {'code': code, 'message': message, 'data': data}
    else:
        code = 0
        message = 'Invalid type. Type should be either all or single'
        return {'code': code, 'message': message}


@app.get('/api/v1/measurements/', tags=["Measurement"])
async def get_measurement_history(entity_id: int = Depends(auth)):
    # Angika updates the query
    sql = "select o.order_id, p.name_front as piece_name m.id, m.name, m.online_status as is_online from measurements m left join line_items li on li.measurement_id = m.id left join orders o on o.id = li.order_id left join pieces p on p.measurement_template_id = m.measurement_template_id "\
     "where m.customer_id = :customer_id and m.is_new = 1 and m.created_at >= :created_at and m.deleted = 0 group by li.measurement_id order by id desc"
    data = await sql_exe(sql, {"customer_id": entity_id, "created_at":"2021-01-01 00:00:00"})
    if not data:
        code = 0
        message = 'No measurement history found. Please scan.'
        return {'code': code, 'message': message}
    output = [{'id': i.id, 'name': i.name +" " + i.order_id + " "+ i.piece_name, 'is_online':i.is_online}
              for i in data if i.field_values]
    # Angika - updation ends
    return {'code': 1, 'message': 'Measurement history', 'data': output}

# Angika - create view measurements api
 
 sql = "select o.order_id, m.id, m.name, m.general, m.field_values, p.name_front as piece_name from measurements m left join line_items li on li.measurement_id = m.id left join orders o on o.id = li.order_id left join pieces p on p.measurement_template_id = m.measurement_template_id"\
     "where m.id IN (measurement_id) group by li.measurement_id"
    data = await sql_exe(sql, {'measurement_id':measurement_id})
    output = [{'id': i.id, 'name': i.name +" " + i.order_id + " "+ i.piece_name, 'is_online':i.is_online
               'general': json.loads(i.general) if i.general else {},
               'measurement': json.loads(i.field_values)}
              for i in data if i.field_values]
    # Angika - updation ends
    return {'code': 1, 'message': 'Measurement history', 'data': output}


# Angika - ends

@app.post('/api/v1/measurement/', tags=["Measurement"])
async def post_measurement(item: MeasurementData,
                           entity_id: int = Depends(auth)):
    sql = "insert into measurements (name, general, field_values, comment, "\
        "customer_id, is_new, online_status) values(current_date,"\
        ":general, :measurement, :comment, :entity_id, 1, 1)"
    data = {
        'general': json.dumps(item.general.dict()) if item.general else '',
        'measurement': json.dumps([i.dict() for i in item.measurement]),
        'comment': item.comment, 'entity_id': entity_id}
    try:
        code = 1
        message = 'Measurement added successfully'
        data = await sql_exe(sql, data)
    except Exception as e:
        print(e)
        code = 0
        message = 'Failed to add measurement'
        data = {}
    return {'code': code, 'message': message, 'data': data}


def size_cal(size):
    if size:
        if size == '+2':
            return range(30, 60, 2)
        elif size == '+1':
            return range(25, 52)
    return []


@app.get('/api/v1/questionnaire/', tags=["Measurement"])
async def get_measurement_questionnaire(entity_id: int = Depends(auth)):
    question_sql = "select id,question,label,types from questionnaire where "\
        "is_active = 1 and general = 1 order by sort_order asc"
    data = await sql_exe(question_sql)
    if not data:
        code = 0
        message = 'No measurement questionnaire found.'
        return {'code': code, 'message': message}
    output = []
    choice_sql = "select id, name, description, size_cal,image from "\
        "questionnaire_choices where questionnaire_id = :q_id and deleted = 0"
    denotion_sql = "SELECT denotion.id, name FROM `denotion` inner join "\
        "question_denotion on denotion.id = question_denotion.denotion_id "\
        "where questionnaire_id = :q_id"
    for i in data:
        ques = {'id': i.id, 'option': i.question, 'label': i.label,
                'type': i.types, 'choices': [], 'denotation': ''}
        choices = await sql_exe(choice_sql, {'q_id': i.id})
        choice_list = []
        for j in choices:
            choice_list.append({
                'id': j.id, 'name': j.name,
                'description': j.description,
                'sub_option': list(size_cal(j.size_cal)),
                'image': j.image
            })
        ques['choices'] = choice_list
        ques['denotation'] = await sql_exe(denotion_sql, {'q_id': i.id})
        output.append(ques)

    return {'code': 1, 'message': 'Measurement questionnaire', 'data': output}


@app.get('/api/v1/recent_orders/', tags=["Order"])
async def get_recent_orders(entity_id: int = Depends(auth)):
    sql = "select s.entity_id as order_id, s.increment_id, sa.firstname as shipto, "\
        "s.total_paid,s.created_at,s.status from sales_order s "\
        "inner join sales_order_address sa on sa.parent_id = s.entity_id "\
        "and sa.address_type ='shipping' where s.customer_id = :customer_id order by s.entity_id desc"
    data = await sql_exe(sql, {"customer_id": entity_id})
    if data:
        return {'code': 1, 'message': 'Recent orders', 'data': data}
    else:
        return {'code': 0, 'message': 'No orders found', 'data': []}


@app.get('/api/v1/order/{order_id}', tags=["Order"])
async def get_order(order_id: int, entity_id: int = Depends(auth)):
    sql = "select s.entity_id, s.quote_id as id, s.subtotal as quote_total, s.discount_amount, s.shipping_amount, s.grand_total, s.total_paid, s.shipping_address_id, s.created_at from sales_order s where s.entity_id = :order_id"
    data = await sql_exe(sql, {"order_id": order_id}, True)
    output = await sep_get_cart(data, entity_id)

    sql2 = "SELECT a.street, a.city, a.region, c.name as country,a.postcode, a.telephone FROM sales_order_address a INNER JOIN directory_country c on c.country_id = a.country_id where a.parent_id = :order_id and a.entity_id = :shipping_address_id"
    data2 = await sql_exe(sql2, {"order_id": order_id, "shipping_address_id": data.shipping_address_id}, True)
    output['shipping_address'] = data2
    output['created_at'] = str(data['created_at'])
    # print(type(output['created_at']))

    return {"code": 1, "message": "Order collected successfully", "data": output}


async def referral_check(grand_total: int, entity_id: int):
    sql = "select referrer_id FROM referrals where referral_id = :entity_id AND status = :status"
    res = await sql_exe(sql, {"entity_id": entity_id, "status": "1"}, True)
    if res:
        referrer_id = res.referrer_id
        if grand_total <= 500:
            discount = grand_total * 0.3
        elif grand_total > 500 and grand_total <= 1500:
            discount = grand_total * 0.5
        else:
            discount = grand_total * 0.7
        sql = "insert into referral_orders (referrer_id, customer_id, "\
            "grand_total, discount, created_at) VALUES (:referrer_id, "\
            ":customer_id, :grand_total, :discount, NOW())"
        data = {
            "referrer_id": referrer_id,
            "customer_id": entity_id,
            "grand_total": grand_total,
            "discount": discount
        }
        await sql_exe(sql, data)

        sql = "update referrals set status = :status where referral_id = :entity_id AND status = :status1"
        await sql_exe(sql, {"entity_id": entity_id, "status": "2", "status1": "1"})

        # get email for referrer id
        sql = "select firstname, lastname, email from customer_entity where entity_id = :entity_id"
        res = await sql_exe(sql, {"entity_id": referrer_id}, True)
        username = f"{res.firstname} {res.lastname}"
        # send email to referrer email to inform referral has placed the order
        BODY = """Hi {},
        Your referral has placed an order. Please find the details below.
        Customer Name: {}

        Thank you,
        HH Custom Tailors"""
        SUB = "Referral Order Placed"
        await send_mail([res.email], msg=BODY.format(username, username), sub=SUB, cc=[ADMIN])
    else:
        sql = "UPDATE discounts SET status= :status, updated_at = NOW() WHERE customer_id = :customer_id AND status = :status0"
        await sql_exe(sql, {"customer_id": entity_id, "status": "1", "status0": "0"})

        sql = "UPDATE referral_orders SET status= :status WHERE referrer_id = :customer_id AND status = :status2"
        await sql_exe(sql, {"customer_id": entity_id, "status": "3", "status2": "2"})

        sql = "update discount_calculation set status = :status where customer_id = :customer_id AND status = :status1"
        await sql_exe(sql, {"customer_id": entity_id, "status": "3", "status1": "1"})
    return {"code": 1, "message": "Referral created successfully"}


@app.post('/api/v1/place_order/', tags=["Order"])
async def place_order(item: PlaceOrder, request: Request, entity_id: int = Depends(auth)):
    await referral_check(item.grand_total, entity_id)
    sql = "SELECT method, description, carrier, carrier_title,method_title, code FROM flat_rate_shipping_method where cost = :shipping_amount"
    shipping = await sql_exe(sql, {"shipping_amount": item.shipping_amount}, True)

    sql3 = "insert into sequence_order_2 values ()"
    res_seq = await sql_exe(sql3, {})

    sql2 = "select increment_id from sales_order order by entity_id desc limit 1"
    res2 = await sql_exe(sql2, {}, True)
    if res2:
        order_id = int(res2.increment_id) + 1
    else:
        order_id = int(f"220000{res_seq['id']}")

    sql3 = "insert into sequence_invoice_2 values ()"
    res_invoice = await sql_exe(sql3, {})

    sql4 = "select increment_id from sales_invoice order by entity_id desc limit 1"
    res4 = await sql_exe(sql4, {}, True)
    if res4 and res4.increment_id:
        invoice_id = int(res4.increment_id) + 1
    else:
        invoice_id = int(f"220000{res_invoice['id']}")

    sql4 = "select c.entity_id, c.firstname, c.lastname, c.email, c.gender, q.items_count as cart_count from customer_entity c Left JOIN quote q ON c.entity_id = q.customer_id AND q.is_active = '1' where c.entity_id = :entity_id"
    cus_info = await sql_exe(sql4, {"entity_id": entity_id}, True)

    remote_ip = request.client.host
    subtotal_with_discount = item.quote_total - item.discount_amount
    sql = "insert into sales_order (state, status, order_id, shipping_description, customer_id, base_discount_amount,base_grand_total, base_shipping_amount, base_subtotal,base_total_paid, base_total_qty_ordered, discount_amount,grand_total, shipping_amount, subtotal, total_paid,total_qty_ordered, quote_id, base_subtotal_incl_tax,subtotal_incl_tax, weight, increment_id, customer_email, customer_firstname, customer_lastname, remote_ip, shipping_method,x_forwarded_for, total_item_count, customer_gender, shipping_incl_tax, base_shipping_incl_tax) "\
        "values('processing', 'processing', :order_id, :description, :customer_id,:discount_amount, :grand_total, :shipping_amount, :quote_total,:total_paid, :cart_count, :discount_amount, :grand_total,:shipping_amount, :quote_total, :total_paid, :cart_count, :quote_id, :quote_total, :quote_total, :weight, :order_id, :customer_email, :customer_firstname, :customer_lastname, :remote_ip, :code, :remote_ip, :cart_count, :customer_gender, :shipping_amount, :shipping_amount)"
    data = {
        'description': shipping.description,
        'customer_id': entity_id,
        'discount_amount': item.discount_amount,
        'grand_total': item.grand_total,
        'shipping_amount': item.shipping_amount,
        'quote_total': item.quote_total,
        'total_paid': item.total_paid,
        'cart_count': cus_info.cart_count,
        'quote_id': item.quote_id,
        'weight': item.weight,
        'order_id': order_id,
        'customer_email': cus_info.email,
        'customer_firstname': cus_info.firstname,
        'customer_lastname': cus_info.lastname,
        'remote_ip': remote_ip,
        'code': shipping.code,
        'customer_gender': cus_info.gender
    }
    data = await sql_exe(sql, data)
    sales_order_id = data['id']

    quote_addr = "insert into quote_address (quote_id, customer_id,customer_address_id, address_type, email, firstname, lastname, street, city, region, region_id, postcode, country_id, telephone, same_as_billing, collect_shipping_rates, shipping_method, shipping_description, weight, subtotal, base_subtotal, subtotal_with_discount, base_subtotal_with_discount, shipping_amount, base_shipping_amount, discount_amount, base_discount_amount, grand_total, base_grand_total, subtotal_incl_tax, base_subtotal_total_incl_tax, shipping_incl_tax, base_shipping_incl_tax) values (:quote_id, :customer_id, :customer_address_id, :address_type, :customer_email, :customer_firstname, :customer_lastname, :street, :city, :region, :region_id, :postcode, :country_id, :telephone, :same_as_billing, 0, :code, :description, :weight, :quote_total, :quote_total, :subtotal_with_discount, :subtotal_with_discount, :shipping_amount, :shipping_amount, :discount_amount, :discount_amount, :grand_total, :grand_total, :quote_total, :quote_total, :shipping_amount, :shipping_amount)"
    data = {
        'quote_id': item.quote_id,
        'customer_id': entity_id,
        'customer_address_id': item.shipping_address.id,
        'address_type': "shipping",
        'customer_email': cus_info.email,
        'customer_firstname': cus_info.firstname,
        'customer_lastname': cus_info.lastname,
        'street': item.shipping_address.street,
        'city': item.shipping_address.city,
        'region': item.shipping_address.region,
        'region_id': item.shipping_address.region_id,
        'postcode': item.shipping_address.postcode,
        'country_id': item.shipping_address.country_id,
        'telephone': item.shipping_address.telephone,
        'same_as_billing': item.shipping_address.same_as_billing,
        'code': shipping.code,
        'description': shipping.description,
        'weight': item.weight,
        'quote_total': item.quote_total,
        'subtotal_with_discount': subtotal_with_discount,
        'shipping_amount': item.shipping_amount,
        'discount_amount': item.discount_amount,
        'grand_total': item.grand_total,
        'quote_total': item.quote_total,
    }
    quote_res = await sql_exe(quote_addr, data)
    billing_address_id = quote_res['id']

    data = {
        'quote_id': item.quote_id,
        'customer_id': entity_id,
        'customer_address_id': item.billing_address.id,
        'address_type': "billing",
        'customer_email': cus_info.email,
        'customer_firstname': cus_info.firstname,
        'customer_lastname': cus_info.lastname,
        'street': item.billing_address.street,
        'city': item.billing_address.city,
        'region': item.billing_address.region,
        'region_id': item.billing_address.region_id,
        'postcode': item.billing_address.postcode,
        'country_id': item.billing_address.country_id,
        'telephone': item.billing_address.telephone,
        'same_as_billing': item.billing_address.same_as_billing,
        'code': shipping.code,
        'description': shipping.description,
        'weight': item.weight,
        'quote_total': item.quote_total,
        'subtotal_with_discount': subtotal_with_discount,
        'shipping_amount': item.shipping_amount,
        'discount_amount': item.discount_amount,
        'grand_total': item.grand_total,
        'quote_total': item.quote_total,
    }
    quote_res = await sql_exe(quote_addr, data)
    shipping_address_id = quote_res['id']

    sales_order_addr = "insert into sales_order_address(parent_id, customer_address_id, quote_address_id, region_id, customer_id, region, postcode, lastname, street, city, email, telephone, country_id, firstname, address_type) values(:sales_order_id, :customer_address_id, :quote_address_id, :region_id, :customer_id, :region, :postcode, :lastname, :street, :city, :email, :telephone, :country_id, :firstname, :address_type)"
    data = {
        'sales_order_id': sales_order_id,
        'customer_address_id': item.shipping_address.id,
        'quote_address_id': billing_address_id,
        'region_id': item.shipping_address.region_id,
        'customer_id': entity_id,
        'region': item.shipping_address.region,
        'postcode': item.shipping_address.postcode,
        'lastname': cus_info.lastname,
        'street': item.shipping_address.street,
        'city': item.shipping_address.city,
        'email': cus_info.email,
        'telephone': item.shipping_address.telephone,
        'country_id': item.shipping_address.country_id,
        'firstname': cus_info.firstname,
        'address_type': "shipping"
    }
    sales_billing_address = await sql_exe(sales_order_addr, data)
    # Angika - please check here for billing and shipping address misplaced and country_id is missing
    billing_address = f"{item.shipping_address.street},"\
        f"{item.shipping_address.city},{item.shipping_address.region},"\
        f"{item.shipping_address.postcode}"
    # Angika - ends

    data = {
        'sales_order_id': sales_order_id,
        'customer_address_id': item.billing_address.id,
        'quote_address_id': shipping_address_id,
        'region_id': item.billing_address.region_id,
        'customer_id': entity_id,
        'region': item.billing_address.region,
        'postcode': item.billing_address.postcode,
        'lastname': cus_info.lastname,
        'street': item.billing_address.street,
        'city': item.billing_address.city,
        'email': cus_info.email,
        'telephone': item.billing_address.telephone,
        'country_id': item.billing_address.country_id,
        'firstname': cus_info.firstname,
        'address_type': "billing"
    }
    sales_shipping_address = await sql_exe(sales_order_addr, data)
    # Angika - please check here for billing and shipping address misplaced and country_id is missing
    shipping_address = f"{item.billing_address.street},"\
        f"{item.billing_address.city},{item.billing_address.region},"\
        f"{item.billing_address.postcode}"
    # Angika - ends
    sql = "update sales_order set billing_address_id = :sales_billing_address_id, shipping_address_id = :sales_shipping_address_id where entity_id = :sales_order_id"
    data = {
        'sales_billing_address_id': sales_billing_address['id'],
        'sales_shipping_address_id': sales_shipping_address['id'],
        'sales_order_id': sales_order_id
    }
    await sql_exe(sql, data)

    # sales invoice
    sales_invoice = "insert into sales_invoice (base_grand_total, base_discount_amount, grand_total, shipping_amount, base_shipping_amount, total_qty, subtotal, base_subtotal, discount_amount, billing_address_id, order_id, shipping_address_id, transaction_id, increment_id) values (:grand_total, :discount_amount, :grand_total, :shipping_amount, :shipping_amount, :cart_count, :quote_total, :quote_total, :discount_amount, :sales_billing_address_id, :sales_order_id, :sales_shipping_address_id, :invoice_id, :invoice_id)"
    data = {
        'discount_amount': item.discount_amount,
        'grand_total': item.grand_total,
        'shipping_amount': item.shipping_amount,
        'cart_count': cus_info.cart_count,
        'quote_total': item.quote_total,
        'sales_billing_address_id': sales_billing_address['id'],
        'sales_order_id': sales_order_id,
        'sales_shipping_address_id': sales_shipping_address['id'],
        'invoice_id': invoice_id
    }
    sales_invoice_res = await sql_exe(sales_invoice, data)
    sales_invoice_id = sales_invoice_res['id']

    # sales_order_grid
    name = f"{cus_info.firstname} {cus_info.lastname}"

    sql = "insert into sales_order_grid (status, customer_id, base_grand_total, base_total_paid, grand_total, total_paid, increment_id, shipping_name, billing_name, billing_address, shipping_address, shipping_information, customer_email, subtotal, shipping_and_handling, customer_name, payment_method) values('completed', :customer_id, :grand_total, :total_paid, :grand_total, :total_paid, :order_id, :shipping_name, :billing_name, :billing_address, :shipping_address, :description, :customer_email, :quote_total, :amount, :customer_name, :payment_method)"
    data = {
        'customer_id': entity_id,
        'grand_total': item.grand_total,
        'total_paid': item.total_paid,
        'order_id': order_id,
        'shipping_name': name,
        'billing_name': name,
        'billing_address': billing_address,
        'shipping_address': shipping_address,
        'description': shipping.description,
        'customer_email': cus_info.email,
        'quote_total': item.quote_total,
        'amount': item.shipping_amount,
        'customer_name': name,
        'payment_method': item.payment_method
    }
    await sql_exe(sql, data)

    sql = "insert into sales_invoice_grid (increment_id, state, order_id, order_increment_id, customer_name, customer_email, payment_method, billing_name, billing_address, shipping_address, subtotal, shipping_and_handling, grand_total, base_grand_total) values(:sales_invoice_id, 1, :sales_order_id, :sales_order_id, :customer_name, :customer_email, :payment_method, :billing_name, :billing_address, :shipping_address, :quote_total, :description, :grand_total, :grand_total)"
    data = {
        'sales_invoice_id': sales_invoice_id,
        'sales_order_id': sales_order_id,
        'customer_name': name,
        'customer_email': cus_info.email,
        'payment_method': item.payment_method,
        'billing_name': name,
        'billing_address': billing_address,
        'shipping_address': shipping_address,
        'quote_total': item.quote_total,
        'description': item.shipping_amount,
        'grand_total': item.grand_total
    }
    await sql_exe(sql, data)

    sql = "select * from quote_item where quote_id = :quote_id"
    res = await sql_exe(sql, {'quote_id': item.quote_id})

    for order_item in res:
        sql = "insert into sales_order_item (order_id, quote_item_id, product_id, product_type, weight, name, qty_ordered, price, base_price, original_price, base_original_price, row_total, base_row_total, row_weight, price_incl_tax, base_price_incl_tax,  row_total_incl_tax, base_row_total_incl_tax) values(:sales_order_id, :item_id, :product_id, :product_type, :weight, :name, :qty, :price, :base_price, :custom_price, :custom_price, :row_total, :base_row_total, :row_weight, :price_incl_tax, :base_price_incl_tax, :row_total_incl_tax, :base_row_total_incl_tax)"
        data = {
            'sales_order_id': sales_order_id,
            'item_id': order_item.item_id,
            'product_id': order_item.product_id,
            'product_type': order_item.product_type,
            'weight': order_item.weight,
            'name': order_item.name,
            'qty': order_item.qty,
            'price': order_item.price,
            'base_price': order_item.base_price,
            'custom_price': order_item.custom_price,
            'row_total': order_item.row_total,
            'base_row_total': order_item.base_row_total,
            'row_weight': order_item.row_weight,
            'price_incl_tax': order_item.price_incl_tax,
            'base_price_incl_tax': order_item.base_price_incl_tax,
            'row_total_incl_tax': order_item.row_total_incl_tax,
            'base_row_total_incl_tax': order_item.base_row_total_incl_tax
        }
        await sql_exe(sql, data)

        sql = "insert into sales_invoice_item(parent_id, order_item_id, product_id, name, qty, price, base_price, row_total, base_row_total, price_incl_tax, base_price_incl_tax, row_total_incl_tax, base_row_total_incl_tax, base_cost) values(:sales_invoice_id, :sales_order_item_id, :product_id, :name, :qty, :price, :base_price, :row_total, :base_row_total, :price_incl_tax, :base_price_incl_tax, :row_total_incl_tax, :base_row_total_incl_tax, :base_cost)"
        data = {
            'sales_invoice_id': sales_invoice_id,
            'sales_order_item_id': order_item.item_id,
            'product_id': order_item.product_id,
            'name': order_item.name,
            'qty': order_item.qty,
            'price': order_item.price,
            'base_price': order_item.base_price,
            'row_total': order_item.row_total,
            'base_row_total': order_item.base_row_total,
            'price_incl_tax': order_item.price_incl_tax,
            'base_price_incl_tax': order_item.base_price_incl_tax,
            'row_total_incl_tax': order_item.row_total_incl_tax,
            'base_row_total_incl_tax': order_item.base_row_total_incl_tax,
            'base_cost': order_item.base_cost
        }
        await sql_exe(sql, data)

    # quote_shipping_rate
    sql = "insert into quote_shipping_rate (address_id, carrier, carrier_title, code, method, price, method_title) values(:quote_shipping_address_id, :carrier, :carrier_title, :code, :method, :shipping_amount, :method_title)"
    data = {
        'quote_shipping_address_id': shipping_address_id,
        'carrier': shipping.carrier,
        'carrier_title': shipping.carrier_title,
        'code': shipping.code,
        'method': shipping.method,
        'shipping_amount': item.shipping_amount,
        'method_title': shipping.method_title
    }
    await sql_exe(sql, data)

    # sales_order_payment
    sql = "insert into sales_order_payment (parent_id, base_shipping_amount, shipping_amount, amount_paid, base_amount_ordered, amount_ordered, method) values (:sales_order_id, :shipping_amount, :shipping_amount, :total_paid, :total_paid, :total_paid, :payment_method)"
    data = {
        'sales_order_id': sales_order_id,
        'shipping_amount': item.shipping_amount,
        'total_paid': item.total_paid,
        'payment_method': item.payment_method
    }
    await sql_exe(sql, data)

    # sales_order_status_history
    sql = "insert into sales_order_status_history (parent_id, status, entity_name) values(:sales_order_id, 'completed', 'order')"
    data = {
        'sales_order_id': sales_order_id
    }
    await sql_exe(sql, data)

    # Update quote
    sql = "update quote set is_active = false where entity_id = :quote_id"
    data = {
        'quote_id': item.quote_id
    }
    await sql_exe(sql, data)

    # link quote id and measurement id
    # Angika - measurement_id should be an array instead of int
    sql = "select id from measurement_quote where quote_id = :quote_id"
    res = await sql_exe(sql, {'quote_id': item.quote_id}, True)
    if res:
        sql = "update measurement_quote set measurement_id = :measurement_id where quote_id = :quote_id"
        data = {
            'measurement_id': item.measurement_id,
            'quote_id': item.quote_id
        }
        await sql_exe(sql, data)
    else:
        sql = "insert into measurement_quote (quote_id, measurement_id) values(:quote_id, :measurement_id)"
        data = {
            'quote_id': item.quote_id,
            'measurement_id': item.measurement_id
        }
        await sql_exe(sql, data)
    # Angika - ends

    # sales_invoiced_aggregated
    sql = "insert into sales_invoiced_aggregated (order_status, orders_count, orders_invoiced, invoiced) values ('completed', :cart_count, :sales_invoice_id, :sales_invoice_id)"
    data = {
        'cart_count': cus_info.cart_count,
        'sales_invoice_id': sales_invoice_id
    }
    await sql_exe(sql, data)

    sql = "insert into sales_invoiced_aggregated_order (order_status, orders_count, orders_invoiced, invoiced) values ('completed', :cart_count, :sales_invoice_id, :sales_invoice_id)"
    data = {
        'cart_count': cus_info.cart_count,
        'sales_invoice_id': sales_invoice_id
    }
    await sql_exe(sql, data)
    # send email invoice to customer
    info = {'customer': {"firstname": cus_info.firstname, "lastname": cus_info.lastname},
            'order': {"order_id": order_id, "payment_method": item.payment_method},
            'billing_address': item.billing_address
            }
    a = await (get_order(sales_order_id, entity_id))
    info.update(a['data'])
    info['shipping_address'] = item.shipping_address
    for num, quote in enumerate(info['quote_item']):
        qry = "SELECT sample_file_name FROM fabrics WHERE id = :fabric_id and deleted = 0 and is_hidden = 0 and front_end = 1"
        img = await sql_exe(qry, {"fabric_id": quote['fabric_id']}, True)
        if img:
            info['quote_item'][num]['img'] = image(
                img.sample_file_name, 'fabric')
        else:
            info['quote_item'][num]['img'] = ""
        # Written by Angika
        for num1, quote_data in enumerate(quote['item_data']):
            if quote_data.get('additional_info'):
                info['quote_item'][num]['item_data'][num1]['additional_info_value'] = ", ".join(
                    quote_data['additional_info']['value'])
            else:
                info['quote_item'][num]['item_data'][num1]['additional_info_value'] = ""
            if quote_data.get('additional_fabrics'):
                # print('Additional Fabric Found')
                lining_fabric_id = quote_data['additional_fabrics']
                sql5 = "SELECT sample_file_name FROM fabrics WHERE id = :lining_fabric_id and deleted = 0 and is_hidden = 0 and front_end = 1"
                res5 = await sql_exe(sql5, {"lining_fabric_id": lining_fabric_id}, True)
                if res5:
                    info['quote_item'][num]['item_data'][num1]['additional_fabric'] = image(
                        res5.sample_file_name, 'fabric')
            else:
                info['quote_item'][num]['item_data'][num1]['additional_fabric'] = ""
        # End

    msg = TEMPLATES.TemplateResponse(
        "index.html",
        {"request": request, "info": info},
    )
    await send_mail([cus_info.email], msg=msg.body.decode(
        'utf-8'), sub="Order Placed Successfully")
    await send_mail(["info@hhcustomtailor.com"],
                    msg=msg.body.decode('utf-8'), sub="Order Placed Successfully")
    return {'code': 1,  'message': 'Order placed successfully'}


@app.post('/api/v1/reorder/', tags=["Order"])
async def reorder(item: Reorder, request: Request, entity_id: int = Depends(auth)):
    sql = "select c.firstname, c.lastname, c.gender, "\
        'c.email, q.grand_total, IF(q.items_count!="", q.items_count,0) as cart_count,'\
        'IF(q.entity_id!="", q.entity_id, 0) as entity_id from customer_entity '\
        "c Left JOIN quote q ON c.entity_id = q.customer_id AND "\
        "q.is_active = '1' WHERE c.entity_id = :entity_id"
    cus_res = await sql_exe(sql, {'entity_id': entity_id}, True)
    print(f"{cus_res=}")
    if not cus_res:
        return {"code": 0, "message": "Cart not found"}

    if cus_res.entity_id:
        quote = cus_res.entity_id
        cart_count = int(cus_res.cart_count)  # 2
        grand_total = cus_res.grand_total
    else:
        sql = "INSERT INTO quote (customer_id, customer_email, "\
            "customer_firstname, customer_lastname, remote_ip,"\
            "customer_gender) VALUES (:entity_id, :email, :firstname,"\
            ":lastname, :ip, :gender)"
        data = {"entity_id": entity_id, "email": cus_res.email,
                "firstname": cus_res.firstname, "lastname": cus_res.lastname,
                "ip": request.client.host, "gender": cus_res.gender}
        res = await sql_exe(sql, data)
        # print(substitute_parameters(sql, data))
        quote = res['id']
        cart_count = 0
        grand_total = 0

    sql = "select piece_group from quote_item_data order by id desc limit 1"
    res = await sql_exe(sql, {}, True)
    if res:
        piece_group = res.piece_group
    else:
        piece_group = "G1"

    sql = "select * from quote_item_data where id in :quote_item_data_id"
    res = await sql_exe(sql, {'quote_item_data_id': tuple(sorted(item.quote_item_data_id))})
    # print(substitute_parameters(sql, {'quote_item_data_id': tuple(sorted(item.quote_item_data_id))}))
    grand_total_price = 0
    first_item_id = ""
    for i in res:
        quote_piece_group = i.piece_group
        ex_quote_item_id = i.item_id
        price = int(i.piece_price) + int(i.monogram_price) + \
            int(i.lining_price)
        grand_total_price += price
        piece_weight = i.weight
        if ex_quote_item_id != first_item_id:
            cart_count = cart_count + 1
            piece_group = f"G{int(piece_group.replace('G', '')) + 1}"
            sql1 = "select * from quote_item where item_id = :ex_quote_item_id"
            res2 = await sql_exe(sql1, {'ex_quote_item_id': ex_quote_item_id}, True)

            # Angika updates starts
            sql = "INSERT INTO quote_item (quote_id, product_id, sku, name, qty, piece_group, category_name, category_id, collection, price, base_price, custom_price, row_total, base_row_total, original_custom_price, price_incl_tax, base_price_incl_tax, row_total_incl_tax, base_row_total_incl_tax, base_cost, weight, row_weight) VALUES (:quote, :product_id, :sku, :name,  :qty,  :piece_group, :category_name, :category_id, :collection, :total_price, :total_price, :total_price, :total_price, :total_price, :total_price, :total_price, :total_price, :total_price, :total_price, :total_price, :piece_weight, :piece_weight)"
            data = {
                'quote': quote,
                'product_id': res2.product_id,
                'sku': res2.sku,
                'name': res2.name,
                'qty': '1',
                'piece_group': piece_group,
                'category_name': res2.category_name,
                'category_id': res2.category_id,
                'collection': res2.collection,
                'total_price': price,
                'piece_weight': piece_weight
            }
            res3 = await sql_exe(sql, data)
            # Angika updates end
            # print(substitute_parameters(sql, data))
            quote_item_id = res3['id']

            # quote item option serialize
            sql_quote_option = "INSERT INTO quote_item_option (item_id, product_id, code, value) VALUES (:item_id, :product_id, :code, :value)"
            data = {"item_id": quote_item_id, "code": "info_buyRequest",
                    "product_id": res2.product_id,
                    "value": json.dumps({"fabric_id": res2.product_id, "fabric_name": res2.name, "item_id": res2.category_id})}
            # print(substitute_parameters(sql_quote_option, data))
            await sql_exe(sql_quote_option, data)

            sql_quote_option = "INSERT INTO quote_item_option (item_id, product_id, code, value) VALUES (:item_id, :product_id, :code, :value)"
            data = {"item_id": quote_item_id, "code": "additional_options",
                    "product_id": res2.product_id,
                    "value": dumps([{"label": "category", "value": res2.category_name}])}
            # print(substitute_parameters(sql_quote_option, data))
            await sql_exe(sql_quote_option, data)

        else:
            # update price in quote_item table
            sql = "select price, weight from quote_item where item_id  = :quote_item_id"
            res6 = await sql_exe(sql, {'quote_item_id': quote_item_id}, True)
            total_price = price + res6.price
            total_weight = piece_weight + res6.weight

            sql = "update quote_item set price = :total_price,base_price= :total_price,  custom_price= :total_price, row_total= :total_price, base_row_total= :total_price, original_custom_price= :total_price, price_incl_tax= :total_price, base_price_incl_tax= :total_price, base_cost = :total_price, row_total_incl_tax = :total_price, base_row_total_incl_tax = :total_price, weight = :total_weight, row_weight = :total_weight where item_id = :quote_item_id"
            data = {
                'total_price': total_price,
                'quote_item_id': quote_item_id,
                'total_weight': total_weight
            }
            # print(substitute_parameters(sql, data))
            await sql_exe(sql, data)

        sql = "insert into quote_item_data (item_id, fabric_id, itemid, piece_id, options, choice_comb_id, piece_group, collection, piece_price, monogram_price, lining_price, sub_category_id, weight, comment, reorder) values(:quote_item_id, :product_id, :itemid, :piece_id, :options, :choice_comb_id, :piece_group, :collection, :piece_price, :monogram_price, :lining_price, :sub_category_id, :piece_weight, :comment, '1')"
        data = {
            'quote_item_id': quote_item_id,
            'product_id': res2.product_id,
            'itemid': i.itemid,
            'piece_id': i.piece_id,
            'options': i.options,
            'choice_comb_id': i.choice_comb_id,
            'piece_group': piece_group,
            'collection': i.collection,
            'piece_price': i.piece_price,
            'monogram_price': i.monogram_price,
            'lining_price': i.lining_price,
            'sub_category_id': i.sub_category_id,
            'comment': i.comment,
            'piece_weight': piece_weight
        }
        # print(substitute_parameters(sql, data))
        await sql_exe(sql, data)
        first_item_id = ex_quote_item_id
    grand_total = grand_total + grand_total_price
    sql = "update quote set items_count = :cart_count, items_qty = :cart_count, grand_total = :grand_total, base_grand_total = :grand_total, subtotal = :grand_total, base_subtotal = :grand_total,subtotal_with_discount = :grand_total, base_subtotal_with_discount= :grand_total where entity_id = :quote"
    data = {
        'grand_total': grand_total,
        'quote': quote,
        'cart_count': cart_count
    }
    # print(substitute_parameters(sql, data))
    await sql_exe(sql, data)
    return {"code": 1, "message": "Cart reordered"}


@app.post('/api/v1/payment/', tags=["Payment"])
async def make_payment(item: Payment, entity_id: int = Depends(auth)):
    sql = "select increment_id, order_currency_code from sales_invoice order by entity_id desc limit 1"
    res = await sql_exe(sql, {}, True)
    url = "https://sandbox-pgw.2c2p.com/payment/4.1/PaymentToken"
    inp_token = {
        "merchantID": os.getenv("merchantID"),
        "invoiceNo": res.increment_id,
        "description": item.description,
        "amount": item.amount,
        "currencyCode": res.order_currency_code,
        "paymentChannel": ["ALL"]
    }
    payload = jwt.encode(inp_token, os.getenv("secret"), algorithm='HS256')
    headers = {
        'Content-Type': 'application/json',
        "Content-Type": "application/*+json"
    }
    response = requests.post(url, headers=headers,
                             data=json.dumps({"payload": payload}))
    output = jwt.decode(response.json()['payload'],
                        os.getenv("secret"), algorithms=['HS256'])
    return output


@app.get('/api/v1/get_coupon/', tags=["Coupon"])
async def get_coupen(entity_id: int = Depends(auth)):
    sql = "select coupon_code, amt_in_percent from customer_promotions "\
        "where customer_id = :customer_id and is_active = 1 order by id desc"
    data = await sql_exe(sql, {"customer_id": entity_id})
    if data:
        return {'code': 1, 'data': data,
                'message': 'Successfully retrived the list of Coupons'}
    else:
        return {'code': 0, 'data': data, 'message': 'No Coupon found'}


@app.post('/api/v1/apply_coupon/', tags=["Coupon"])
async def apply_coupon(entity_id: int = Depends(auth)):
    sql = "select coupon_code, amt_in_percent from customer_promotions "\
        "where customer_id = :customer_id and is_active = 1 order by id desc"
    data = await sql_exe(sql, {"customer_id": entity_id}, True)

    quote_sql = "select entity_id as quote_id, subtotal as "\
        "quote_total, grand_total as grand_total from quote where "\
        "customer_id = :customer_id and is_active = 1"
    quote = await sql_exe(quote_sql, {"customer_id": entity_id}, True)

    discount_amt = (float(quote.quote_total) *
                    float(data.amt_in_percent)) / 100
    sql = "update quote set coupon_code = :coupon_code, discount_amt = "\
        ":discount_amt where entity_id = :quote_id"
    await sql_exe(sql, {"quote_id": quote.quote_id, "coupon_code": data.coupon_code, "discount_amt": discount_amt})

    sql = "update customer_promotions set is_active = 0 where "\
        "customer_id = :customer_id and coupon_code = :coupon_code;"
    await sql_exe(sql, {"customer_id": entity_id, "coupon_code": data.coupon_code})

    data = {
        "grand_total": quote.grand_total,
        "discount": discount_amt
    }
    return {'code': 1, 'data': data, "message": "Coupon Applied Successfully"}


@app.put('/api/v1/cancel_coupon/', tags=["Coupon"])
async def cancel_coupon(entity_id: int = Depends(auth)):
    sql = "select entity_id as quote_id, coupon_code, discount_amt "\
        "from quote where customer_id = :customer_id and is_active = 1"
    data = await sql_exe(sql, {"customer_id": entity_id}, True)

    sql = "update quote set coupon_code = '', discount_amt = 0 where "\
        "entity_id = :quote_id"
    await sql_exe(sql, {"quote_id": data.quote_id})

    sql = "update customer_promotions set is_active = 1 where "\
        "customer_id = :customer_id and coupon_code = :coupon_code;"
    await sql_exe(sql, {"customer_id": entity_id, "coupon_code": data.coupon_code})
    return {'code': 1, 'data': {}, "message": "Coupon Cancelled Successfully"}


@app.post('/api/v1/stripe-pay/', tags=["Stripe"])
async def get_payment_indent(amount: int, entity_id: int = Depends(auth)):
    customer = stripe.Customer.create()
    ephemeralKey = stripe.EphemeralKey.create(
        customer=customer['id'],
        stripe_version='2023-08-16',
    )
    paymentIntent = stripe.PaymentIntent.create(
        amount=amount,
        currency='usd',
        customer=customer['id'],
        automatic_payment_methods={
            'enabled': True,
        },
    )
    return {
        "paymentIntent": paymentIntent.client_secret,
        "ephemeralKey": ephemeralKey.secret,
        "customer": customer['id'],
        "publishableKey": public_key}


@app.post('/api/v1/referral/', tags=["Referral"])
async def create_referral(items: List[Referral], entity_id: int = Depends(auth)):
    user_sql = "select firstname, lastname, email from customer_entity where entity_id = :entity_id"
    user = await sql_exe(user_sql, {"entity_id": entity_id}, True)

    username = f"{user.firstname} {user.lastname}"
    sql = "insert into referrals (referrer_id, name, email, message) "\
        "values(:entity_id, :name, :email, :message)"
    CC = [ADMIN]
    SUB = "Referral Request Received"
    BODY = """Hi {},
    We have received a referral request from your friend "{}". Please find the details below.
    Message from your friend: {}
    
    Thank you,
    HH Custom Tailors"""
    values = []
    for item in items:
        data = {
            "entity_id": entity_id,
            "name": item.name,
            "email": item.email,
            "message": item.message
        }
        values.append(data)
        await send_mail([item.email], msg=BODY.format(
            item.name, username, item.message), sub=SUB, cc=CC)
    res = await sql_exe(sql, values)
    # confirmation email to the sender
    BODY = """Hi {},
    Thank you for referring your friend to HH Custom Tailors. We have received your referral request.
    We will get back to you soon.
    
    Thank you,
    HH Custom Tailors"""
    SUB = "Referral Request Confirmation"
    await send_mail([user.email], msg=BODY.format(username), sub=SUB)

    return {"code": 1, "message": "Referral created successfully"}


@app.get('/api/v1/referral/', tags=["Referral"])
async def get_referral(entity_id: int = Depends(auth)):
    sql = "select discount, status, id, customer_id, created_at  from referral_orders where referrer_id = :entity_id AND status not in ('3')"
    res = await sql_exe(sql, {"entity_id": entity_id})

    discount = 0
    has_rewards = 0
    rewards = {}
    if res:
        for item in res:
            time = datetime.now() - item.created_at
            if time.years <= 2:
                sql = "select balance FROM discount_calculation where referral_order_id = :referral_order_id order by id desc limit 1"
                res1 = await sql_exe(sql, {"referral_order_id": item.id}, True)
                if res1:
                    total_val = res1.balance
                else:
                    total_val = item.discount
                discount += total_val
                if total_val:
                    rewards[item.id] = total_val
                has_rewards = 1
    sql = "select reward_amt from quote where entity_id = :quote_id"
    res = await sql_exe(sql, {"quote_id": entity_id}, True)
    data = {
        "has_rewards": has_rewards,
        "rewards": rewards,
        "total_amt": discount,
        "reward_amt": res.reward_amt
    }
    return {"code": 1, "data": data, "message": "Referral created successfully"}


@app.post('/api/v1/apply_rewards/', tags=["Referral"])
async def apply_rewards(item: Rewards, user_amt: float, entity_id: int = Depends(auth)):
    used = user_amt
    for order_id, value in item.rewards.items():
        balance = value - used
        if balance > 0:
            sql = "update discount_calculation set status='3' where referral_order_id = :referral_order_id"
            await sql_exe(sql, {"referral_order_id": order_id})

            sql = "insert into discount_calculation (customer_id,total,used,balance,carry_forward,referral_order_id,status,created_at) values(:customer_id,:total,:used,:balance,:carry_forward,:referral_order_id,:status,NOW())"
            data = {
                "customer_id": entity_id,
                "total": value,
                "used": used,
                "balance": balance,
                "carry_forward": 0,
                "referral_order_id": order_id,
                "status": "2"
            }
            await sql_exe(sql, data)

            sql = "update referral_orders set status='1' where id = :id"
            await sql_exe(sql, {"id": order_id})
            break
        if balance == 0:
            sql = "update discount_calculation set status='3' where referral_order_id = :referral_order_id"
            await sql_exe(sql, {"referral_order_id": order_id})

            sql = "insert into discount_calculation (customer_id,total,used,balance,carry_forward,referral_order_id,status,created_at) values(:customer_id,:total,:used,:balance,:carry_forward,:referral_order_id,:status,NOW())"
            data = {
                "customer_id": entity_id,
                "total": value,
                "used": used,
                "balance": balance,
                "carry_forward": 0,
                "referral_order_id": order_id,
                "status": "2"
            }
            await sql_exe(sql, data)

            sql = "update referral_orders set status='2' where id = :id"
            await sql_exe(sql, {"id": order_id})
            break
        if balance < 0:
            rem = used - value
            sql = "update discount_calculation set status='3' where referral_order_id = :referral_order_id"
            await sql_exe(sql, {"referral_order_id": order_id})

            sql = "insert into discount_calculation (customer_id,total,used,balance,carry_forward,referral_order_id,status,created_at) values(:customer_id,:total,:used,:balance,:carry_forward,:referral_order_id,:status,NOW())"
            data = {
                "customer_id": entity_id,
                "total": value,
                "used": value,
                "balance": 0,
                "carry_forward": rem,
                "referral_order_id": order_id,
                "status": "1"
            }
            await sql_exe(sql, data)

            sql = "update referral_orders set status='2' where id = :id"
            await sql_exe(sql, {"id": order_id})
            used = rem
    total_discount = sum(item.reward_amt.values())
    # /*Get Current Users already entered but not used discount id*/
    sql = "select id, current_amount from discounts where customer_id = :customer_id and status = :status"
    res = await sql_exe(sql, {"customer_id": entity_id, "status": "0"}, True)
    current_amt = user_amt
    if res:
        remaining_amt = total_discount - current_amt
        sql = "update discounts set total_discount =:total_discount, current_amount = :current_amount, remaining_amount = :remaining_amount, status = :status, updated_at = NOW() where id = :id"
        await sql_exe(sql, {"total_discount": total_discount, "current_amount": current_amt, "remaining_amount": remaining_amt, "status": "0", "id": res.id})
    else:
        remaining_amt = total_discount - current_amt
        sql = "insert into discounts (customer_id, total_discount, current_amount, remaining_amount, status, created_at) values(:customer_id, :total_discount, :current_amount, :remaining_amount, :status, NOW())"
        await sql_exe(sql, {"customer_id": entity_id, "total_discount": total_discount, "current_amount": current_amt, "remaining_amount": remaining_amt, "status": "0"})

    quote_sql = "select entity_id from quote where is_active = 1 and customer_id = :customer_id"
    res = await sql_exe(quote_sql, {"customer_id": entity_id}, True)

    sql = "update quote set reward_amt = :reward_amt where entity_id = :entity_id"
    await sql_exe(sql, {"reward_amt": current_amt, "entity_id": res.entity_id})

    return {"code": 1, "data": {}, "message": "Referral created successfully"}


@app.post('/api/v1/cancel_rewards/', tags=["Referral"])
async def cancel_rewards(entity_id: int = Depends(auth)):
    sql = "DELETE from discount_calculation where customer_id = :customer_id and status != :status"
    await sql_exe(sql, {"customer_id": entity_id, "status": "3"})

    sql = "update referral_orders SET status = :status where referrer_id = :customer_id AND status != :status3"
    await sql_exe(sql, {"customer_id": entity_id, "status": "1", "status3": "3"})

    sql = "delete from discounts where customer_id = :customer_id and status = :status"
    await sql_exe(sql, {"customer_id": entity_id, "status": "0"})

    quote_sql = "select entity_id from quote where is_active = 1 and customer_id = :customer_id"
    res = await sql_exe(quote_sql, {"customer_id": entity_id}, True)

    sql = "update quote set reward_amt = :reward_amt where entity_id = :entity_id"
    await sql_exe(sql, {"reward_amt": "", "entity_id": res.entity_id})

    return {"code": 1, "data": {}, "message": "Referral rewards cancelled successfully"}