import os
import re
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",
        "lastname": "",
        "city": "",
        "country": "",
        "region": "",
        "region_id": 0,
    })
):
    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')

    data = {
        "email": item.email,
        "password_hash": hashing(item.password),
        "firstname": item.firstname,
        "lastname": item.lastname,
        # "gender": item.gender,
        "otp_code": otp_code,
        "is_active": 0
    }

    sql = "INSERT INTO customer_entity (email, password_hash, firstname, "\
        "lastname, gender, otp_code, is_active) VALUES (:email, "\
        ":password_hash,:firstname,:lastname, 0, :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 = "INSERT INTO customer_address_entity (parent_id, is_active, city, "\
        "country_id, firstname, lastname, region, region_id, type"\
        ") VALUES (:customer_id,  1, :city, :country, :firstname,"\
        ":lastname, :region, :region_id, :type)"

    sql_default_address = "UPDATE customer_entity SET default_shipping ="\
        ":shipping_address_id, default_billing=:billing_address_id "\
        "WHERE entity_id = :customer_id"

    data = {'customer_id': res.get('id'), 'city': item.city,
            'country': item.country, 'firstname': item.firstname,
            'lastname': item.lastname, 'region': item.region,
            'region_id': item.region_id, 'type': 'ShippingAddress'
            }
    shiping_add_res = await sql_exe(sql, data)
    data['type'] = 'BillingAddress'
    billing_add_res = await sql_exe(sql, data)

    data = {
        "shipping_address_id": shiping_add_res.get('id'),
        "billing_address_id": billing_add_res.get('id'),
        "customer_id": res.get('id'),
    }
    await sql_exe(sql_default_address, data)
    # coupon_code = "HH".res.get('id').firstletter of firstname.first letter of lastname."10"
    coupon_code = "HH"
    if data.get('firstname'):
        coupon_code += "." + data.get('firstname')[0]
    if data.get('lastname'):
        coupon_code += "." + data.get('lastname')[0]
    coupon_code += ".10"
    sql = "update customer_promotions set coupon_code = :coupon_code,"\
        "customer_id = :entity_id, is_active = 1 where email = :email"
    await sql_exe(sql, {"entity_id": res.get('id'), "email": item.email,
                        "coupon_code": coupon_code})
    # //Commanded by Angika - dont delete it we will use this for future.
    # 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 = 3 and '\
        '(f.group_fabrics!= "1,0") and f.front_end = 1 and f.deleted = 0 '\
        'and f.is_hidden = 0 and f.is_approved = 1 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 = 3 and '\
        '(f.group_fabrics!= "1,0") and f.front_end = 1 and f.deleted = 0 '\
        'and f.is_hidden = 0 and f.is_approved = 1 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 = 3 and '\
            '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 = 3 and'\
            ' patterns.value in :pattern '
    sql += ' where f.group_fabrics != "1,0" and f.front_end = 1 and f.is_approved = 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 = 3 and (f.group_fabrics!= '\
        '"0,0") and f.front_end = 1 and f.deleted = 0 and f.is_approved = 1 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 = 3 and (f.group_fabrics!= '\
        '"0,0") and f.front_end = 1 and f.deleted = 0 and f.is_approved = 1 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 = 3 and '\
            '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 = 3 and'\
            ' 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 f.is_approved = 1 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}


@app.post('/api/v1/feature_objects/', tags=["Images"])
async def get_feature_objects(item: Image, entity_id: int = Depends(auth)):
    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.is_button, 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_button as is_button, 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 ({})"

    sql_fabric = "SELECT f.id, f.large_file_name, f.scaling FROM fabrics f where f.id = :fabric_id"
    fabric_res = await sql_exe(sql_fabric, {"fabric_id": item.fabric_id}, True)

    sql_btn = "SELECT b.filename FROM fabrics_additional_infos f "\
        "inner JOIN button_infos b on f.button_infos_id = b.id "\
        "WHERE fabric_id= :fabric_id"
    btn_res = await sql_exe(sql_btn, {"fabric_id": item.fabric_id}, True)
    try:
        code = 1
        message = "Data fetched successfully"
        three_objects = {}
        sql1 = await sql_exe(sql_choice, {"c_id": item.choices})
        choices = {}
        base = []
        hide = []
        satin_img = ""
        btn = ""
        obj_type = ""
        lining_scaling = 0
        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 three_objects:
                        three_objects[j.type] = []
                    if j.is_satin:
                        obj_type = "satin"
                        satin_txt = f'satin/black.jpg'
                        satin_img = image(satin_txt, 'fabric')
                    else:
                        obj_type = ""
                    three_file = f'{i["template"]}/{i.filename}-{j.filename}'
                    three_objects[j.type].append(
                        {"id": int(j.id), "position": j.position, "obj": three_file, "obj_type": ("button" if j.is_button == 1 else obj_type)})
            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 three_objects:
                        three_objects[j.type] = []
                        obj_type = ""
                        three_file = f'{i["template"]}/{j.filename}'
                    elif j.is_satin:
                        three_file = f'{i["template"]}/{j.filename}'
                        obj_type = "satin"
                        satin_txt = f'satin/black.jpg'
                        satin_img = image(satin_txt, 'fabric')
                    else:
                        obj_type = ""
                        three_file = f'{i["template"]}/{j.filename}'
                    three_objects[j.type].append(
                        {"id": int(j.id), "position": j.position, "obj": three_file, "obj_type": ("button" if j.is_button == 1 else obj_type)})
            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, "is_button": sql3.is_button, "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(',')))
        if item.lining_fabric_id:
            lining_fabric_res = await sql_exe(sql_fabric, {"fabric_id": item.lining_fabric_id}, True)
            custom_lining = f'largefile/{lining_fabric_res.large_file_name}'
            lining_fabric = image(custom_lining, 'fabric')
            lining_scaling = lining_fabric_res.scaling
        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
                standard_fabric = f'standard_lining/{lining_fabric_id}'
                lining_fabric = image(standard_fabric, 'fabric')+'.jpg'
            else:
                lining_fabric_id = ""
                lining_fabric = ""
        for i in base:
            if i["base_id"] in choices:
                b_file = f'{i["template"]}/{choices[i["base_id"]]}-{i["file"]}'
                if i["is_satin"]:
                    obj_type = "satin"
                    satin_txt = f'satin/black.jpg'
                    satin_img = image(satin_txt, 'fabric')
                else:
                    obj_type = ""
                three_objects[i["type"]].append({"id": int(i["id"]), "position": i["position"],
                                                 "obj": b_file, "obj_type": ("button" if i["is_button"] == 1 else obj_type)})
        if btn_res:
            standard_btn = f'buttons/{btn_res.filename}'
            btn = image(standard_btn, 'fabric')

        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]
        fabric_file_name = f'largefile/{fabric_res.large_file_name}'
        output = {"front": front, "rear": rear, "scaling": fabric_res.scaling,
                  "fabric": image(fabric_file_name, 'fabric'), "lining_fabric": lining_fabric, "button_texture": btn, "button_scaling": 1, "lining_scaling": lining_scaling, "satin_texture": satin_img}
    except Exception as e:
        print(e)
        code = 0
        message = "Failed to collect the data"
        output = {}
    return {"code": code, "message": message, "data": output}


# @app.get("/api/v1/single_feature_object", tags=["Images"])
async def single_feature_object(item: FeatureObject):
    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": item.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 item.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.is_button, 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, "\
        "f.is_button 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 ({})"

    sql_fabric = "SELECT f.id, f.sample_file_name, f.large_file_name, f.scaling FROM fabrics f where f.id = :fabric_id"
    fabric_res = await sql_exe(sql_fabric, {"fabric_id": item.fabric_id}, True)

    sql_btn = "SELECT b.filename FROM fabrics_additional_infos f "\
        "inner JOIN button_infos b on f.button_infos_id = b.id "\
        "WHERE fabric_id= :fabric_id"
    btn_res = await sql_exe(sql_btn, {"fabric_id": item.fabric_id}, True)
    btn = ""
    obj_type = ""
    three_objects = {}
    sql1 = await sql_exe(sql_choice, {"c_id": item.comb_id})
    choices = {}
    base = []
    hide = []
    satin_img = ""
    lining_scaling = 0
    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 three_objects:
                    three_objects[j.type] = []
                if j.is_satin:
                    obj_type = "satin"
                    satin_txt = f'satin/black.jpg'
                    satin_img = image(satin_txt, 'fabric')
                else:
                    obj_type = ""
                three_file = f'{i["template"]}/{i.filename}-{j.filename}'
                three_objects[j.type].append(
                    {"id": int(j.id), "position": j.position, "obj": three_file, "obj_type": ("button" if j.is_button == 1 else obj_type)})
        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 three_objects:
                    three_objects[j.type] = []
                    three_file = f'{i["template"]}/{j.filename}'
                    obj_type = ""
                elif j.is_satin:
                    three_file = f'{i["template"]}/{j.filename}'
                    obj_type = "satin"
                    satin_txt = f'satin/black.jpg'
                    satin_img = image(satin_txt, 'fabric')
                else:
                    three_file = f'{i["template"]}/{j.filename}'
                    obj_type = ""
                three_objects[j.type].append(
                    {"id": int(j.id), "position": j.position, "obj": three_file, "obj_type": ("button" if j.is_button == 1 else obj_type)})
        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, "is_button": sql3.is_button, "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(',')))
    if item.lining_fabric_id:
        lining_fabric_res = await sql_exe(sql_fabric, {"fabric_id": item.lining_fabric_id}, True)
        custom_lining = f'largefile/{lining_fabric_res.large_file_name}'
        lining_fabric = image(custom_lining, 'fabric')
        lining_scaling = lining_fabric_res.scaling
    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
            standard_fabric = f'standard_lining/{lining_fabric_id}'
            lining_fabric = image(standard_fabric, 'fabric')+'.jpg'
        else:
            lining_fabric_id = ""
            lining_fabric = ""
    for i in base:
        if i["base_id"] in choices:
            b_file = f'{i["template"]}/{choices[i["base_id"]]}-{i["file"]}'
            if i["is_satin"]:
                obj_type = "satin"
                satin_txt = f'satin/black.jpg'
                satin_img = image(satin_txt, 'fabric')
            else:
                obj_type = ""
            three_objects[i["type"]].append({"id": int(i["id"]), "position": i["position"],
                                             "obj": b_file, "obj_type": ("button" if i["is_button"] == 1 else obj_type)})
    if btn_res:
        standard_btn = f'buttons/{btn_res.filename}'
        btn = image(standard_btn, 'fabric')

    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]
    fabric_file_name = f'largefile/{fabric_res.large_file_name}'
    thumb_file_name = fabric_res.sample_file_name
    output = {"front": front, "rear": rear, "scaling": fabric_res.scaling, "thumbnail": image(thumb_file_name, 'fabric'),
              "fabric": image(fabric_file_name, 'fabric'), "lining_fabric": lining_fabric, "button_texture": btn, "button_scaling": 1, "lining_scaling": lining_scaling, "satin_texture": satin_img}
    return_value["feature_images"] = output

    return return_value


@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 = '', reward_amt = 0 where entity_id = :quote_id"
    await sql_exe(sql, {"quote_total": res.quote_total, "quote_qty": res.quote_qty, "quote_id": res.quote_id})
    await cancel_rewards(entity_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)

        product_sql = "select sku from catalog_product_entity where entity_id = :fabric_id"
        product_result = await sql_exe(product_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)

        # 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}"

                    product_sql = "select sku from catalog_product_entity where entity_id = :fabric_id"
                    product_result = await sql_exe(product_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,
                            "sku": product_result.sku, "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}
                    res = await sql_exe(sql, data)

                    # 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})

    await cancel_rewards(entity_id)

    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 = '', reward_amt = 0 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):
    # Measurement Quote
    sql_mes = "select id from measurement_quote where quote_id = :quote_id"
    res_mes = await sql_exe(sql_mes, {"quote_id": res.id}, single=True)
    if res_mes:
        has_measurement = 1
    else:
        has_measurement = 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 = (float(res.quote_total) + shipping_cost) - \
            float(res.discount_amt) - res.reward_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, "quote_reward_amt": res.reward_amt, "reward": discount, 'discount_amt': res.discount_amt, 'coupon_code': res.coupon_code, "quote_total": res.quote_total,
                  "grand_total": grand_total, "weight": 0, "has_measurement": has_measurement, "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, "has_measurement": has_measurement, "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, qid.is_physical_fabric, 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" //added by Angika 16 feb 2025 only is_physical_fabric column
        res3 = await sql_exe(sql3, {"quote_item_id": item_id.id})
        for r in res3:
//added by Angika 16 feb 2025 new if condition for physical fabrics
            if(r.is_physical_fabric == 1){
                options = json.loads(r.options) -> returns array(category_id=> array(fabric_ids))
                fabric_swatch_url = image("swatch.png", 'fabric')
                item_data_out = {"quote_item_data_id": r.id,
                                     "piece_price": int(r.piece_price,
                                        "fabric_swatch_url":fabric_swatch_url, "is_physical_fabric":r.is_physical_fabric}
                for(options as category_id => fabric_array){
                        sql = "select cc.value as name from category_entity_varchar cc where cc.entity_id = :category_id and cc.attribute_id = 45"
                        item_details = await sql_exe(sql, {'category_id': category_id}, True)
                        category = item_details.name
                        for(fabric_array as $fabric_id){
                            sql = "select sample_file_name from fabrics where id = :fabric_id"
                            fabric_res = await sql_exe(sql, {'fabric_id': fabric_id}, True)
                            thumb_file_name = fabric_res.sample_file_name
                            thumb_url[] = image(thumb_file_name, 'fabric')

                        }

                        data[] = array("category"=>category, "fabrics"=>thumb_url)
               }
               
               item_data_out.append{"data" : data}
               expected output => {quote_item_id: 12,
                        piece_price: 40,
                        fabric_swatch_url: url,
                        is_physical_fabric : 1,
                        data: {
                        [category: "suit",
                        fabrics:{[url1, url2]},
                        category: "shirt",
                        fabrics:{[url1, url2]
                        ]}
               }


        } else { //added  by Angika 16 feb 2025  only this else { word
           
            # 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, "is_physical_fabric":r.is_physical_fabric}  //added by Angika feb 16 2025 - only is_physical_fabric key

           
                             
            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) - (dont remove the this code just command it)
            imgs = await single_feature_object(FeatureObject(piece_id=r.piece_id, comb_id=comb_id, fabric_id=item_id.fabric_id, lining_fabric_id=lining_fabric_id))
            item_data_out.update(imgs)
            quote_data["item_data"].append(item_data_out)

           } //added  by Angika 16 feb 2025  only this bracket
        output["quote_item"].append(quote_data)
    output["weight"] = total_weight
    return output


//created this function by Angika 16 feb 2025 

post_physical_fabric_to_cart: 
input: 
data = array(category_id =>array(fabric_ids)) -> array("73"=>array("2345",6536,38473))
price = price
==========================================================================



$first_category_id = array_key($data[0]) -> first key value
$first_fabric_id = $data[0][0] -> first fabric value

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"
piece_group = f"G{int(piece_group.replace('G', '')) + 1}"

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']
cart_count += 1

sql = "select cc.value as name c.entity_id as category_id from catalog_category_entity c inner join "\
"catalog_category_entity_varchar cc on c.entity_id = cc.entity_id and cc.attribute_id = 45"\
 "where c.entity_id = :category_id"
item_details = await sql_exe(sql, {'category_id': first_category_id}, True)

product_sql = "select sku from catalog_product_entity where entity_id = :fabric_id"
product_result = await sql_exe(product_sql, {'fabric_id': first_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": first_fabric_id,
        "fabric_name": product_result.sku, "name": item_details.name,
        "weight": 1, "piece_group": piece_group,
        "category_id": item_details.category_id,
        "collection": 0,
        "sku": product_result.sku}
res = await sql_exe(sql, data)
quote_item = res['id']

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)

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": first_fabric_id,
        "value": json.dumps({"fabric_id": first_fabric_id, "fabric_name": product_result.sku, "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": first_fabric_id,
        "value": dumps([{"label": "category", "value": item_details.name}])}
await sql_exe(sql_quote_option, data)

quote_item = res['id']
json_array = json.dumps($data)

sql = "insert into quote_item_data (item_id, options, choice_comb_id, piece_price, monogram_price, lining_price, sub_category_id, comment, is_physical_fabric) "\
      "values (:quote_item, :json_array, :combination_id, :piece_price, :monogram_price, :lining_price, :category_id, :comment,1)"
data = {"quote_item": quote_item, "json_array": json_array,
            "combination_id": combination_id,
            "piece_group": piece_group,
            "category_id": item_details.category_id,
            "weight": piece_weight, "comment": option.comment}
sub_total = cus_res.sub_total
if not sub_total:
        sub_total = 0
sub_total = sub_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})
await cancel_rewards(entity_id)
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 = '', reward_amt = 0 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"}

//end


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, q.reward_amt 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/{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) - dont remove it, just command it.
    imgs = await single_feature_object(FeatureObject(piece_id=res3.piece_id, comb_id=comb_id, fabric_id=res3.fabric_id, lining_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.subtotal as quote_total, coupon_code 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

    customer_promotion = """
    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, {'customer_id': entity_id,
                                       'coupon_code': cus_info.coupon_code})
    await cancel_rewards(entity_id)

    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, discount = 0, reward_amt=0, coupon_code ='' 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 = '', reward_amt = 0 where entity_id = :quote_id "
        await sql_exe(sql, {'quote_id': quote_id})
        await cancel_rewards(entity_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 = '', reward_amt = 0 where entity_id = :quote_id"
        data = {'sub_total': sub_total, 'items_count': res.items_count - res_quote_item.qty,
                'quote_id': res.quote_id}
        await cancel_rewards(entity_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/content_management/', tags=["Content Management"])
async def content_management(id: int):
    sql = "SELECT c.id, c.title, c.content FROM content_management c WHERE c.is_active = 1 and c.id = :id"
    res = await sql_exe(sql, {"id": id})
    for j in res:
        if j:
            re_pattern = re.compile(r'"(.*?)"\s*=>\s*"(.*?)(?<!\\)"')
            content = []
            for i in j.content.strip()[2:-2].split('],['):
                matches = re_pattern.findall(i)
                d = {k: re.sub(r'\\(.)', r'\1', v) for k, v in matches}
                if d['type'] == 'table':
                    rows = []
                    value = d['value'][1:-2].split('},')
                    for v in value:
                        data = {i.split(':')[0]: i.split(':')[1]
                                for i in v[1:].split(',')}
                        rows.append(list(data.values()))
                    json_data = {'header': list(data.keys()), 'rows': rows}
                    d['value'] = json.dumps(json_data)
                content.append(d)
            data = {"id": j.id, "title": j.title, "content": content}
    return data


@app.post('/api/v1/events/', tags=["Events"])
async def events(item: Event):
    sql = """
    insert into event 
        (name, email, city, country, created_at)
    values
        (name, email, city, country, current_date)
    """
    data = {
        'name': item.name,
        'email': item.email,
        'city': item.city,
        'country': item.country
    }
    try:
        code = 1
        message = 'Thank you for registering our Live events. We will get back to you shortly'
        data = await sql_exe(sql, data)
    except Exception as e:
        print(e)
        code = 0
        message = 'Something went wrong. Please contact us at info@hhcustomtailor.com'
    return {'code': code, 'message': message}


@app.post('/api/v1/newsletter/', tags=["NewsLetter"])
async def newsletter(item: NewsLetter):
    sql = """
    insert into newsletter_subscriber
        (name, subscriber_email, city, country, created_at)
    values
        (:name, :email, :city, :country, current_date)
    """
    data = {
        'name': item.name,
        'email': item.email,
        'city': item.city,
        'country': item.country
    }
    try:
        data = await sql_exe(sql, data)
        code = 1
        message = 'Thank you for subscribing to our Newsletter'
    except Exception as e:
        print(e)
        code = 0
        message = 'Something went wrong. Please contact us at info@hhcustomtailor.com'
    return {'code': code, 'message': message}


@app.get('/api/v1/measurements/', tags=["Measurement"])
async def get_measurement_history(
        page_number: int = 1,
        size: int = 10,
        entity_id: int = Depends(auth)):
    sql = """
    select o.order_id, p.name_front as piece_name, m.id, m.name,
        m.online_status as is_online, m.field_values 
    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 >= '2021-01-01 00:00:00' and m.deleted = 0 
    group by m.id 
    order by m.id desc
    limit :limit offset :offset    
    """
    data = await sql_exe(sql,
                         {"customer_id": entity_id,
                          "limit": size, "offset": (page_number - 1) * size})
    if not data:
        code = 0
        message = 'No measurement history found. Please scan.'
        return {'code': code, 'message': message}
    output = [{'id': i.id, 'name': f"{i.name} {i.order_id} {i.piece_name}",
               'is_online': i.is_online}
              for i in data if i.field_values]
    return {'code': 1, 'message': 'Measurement history', 'data': output}


@app.get('/api/v1/measurements/{measurement_id}', tags=["Measurement"])
async def get_measurement_by_id(measurement_id: str, entity_id: int = Depends(auth)):
    sql = "select o.order_id, m.id, m.name, m.general, m.field_values, "\
        "p.name_front as piece_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.id IN :measurement_id "
    measurement_ids = tuple(map(int, measurement_id.split(',')))
    data = await sql_exe(sql, {'measurement_id': measurement_ids})
    output = [{'id': i.id, 'name': f"{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]
    return {'code': 1, 'message': 'Measurement history', 'data': output}


@app.post('/api/v1/measurement/', tags=["Measurement"])
async def post_measurement(item: MeasurementData,
                           entity_id: int = Depends(auth)):
    templ_sql = "select id, field_list from measurement_templates where "\
                "is_active=1"
    templ = await sql_exe(templ_sql)
    for i in templ:
        measurements = []
        if i.field_list:
            for part in json.loads(i.field_list):
                # print(part)
                ms_part = "select mirrorsize_parts from all_measurement_parts"\
                    " where parts = :part"
                ms_part = await sql_exe(ms_part, {'part': part}, True)
                if not ms_part:
                    continue
                tmplate_part = "select mtp.id as mtp_id from "\
                    "measurement_template_part mtp inner join "\
                    "measurement_parts mp on mp.id = mtp.measurement_part_id "\
                    "where mp.name = :part and mtp.measurement_template_id "\
                    "= :mtp_id and mtp.is_active = 1 and mp.is_active = 1"
                tmplate_part = await sql_exe(
                    tmplate_part, {'part': part, 'mtp_id': i.id}, True)
                for msrmnt in item.measurement:
                    # print(msrmnt.part,ms_part, end=';')
                    if msrmnt.part == ms_part.mirrorsize_parts:
                        measurements.append({
                            "part": part,
                            "value": msrmnt.value,
                            "mtp_id": tmplate_part.mtp_id
                        })
            sql = "insert into measurements (name, general, field_values, comment, "\
                "customer_id, is_new, online_status, measurement_template_id) values(current_date,"\
                ":general, :measurement, :comment, :entity_id, 1, 1, :template_id)"
            data = {
                'general': json.dumps(item.general.dict()) if item.general else '',
                'measurement': json.dumps(measurements),
                'comment': item.comment, 'entity_id': entity_id, 'template_id': i.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}


@app.put('/api/v1/measurement/', tags=["Measurement"])
async def update_measurement(item: UpdateMeasurement,
                             entity_id: int = Depends(auth)):
    check_sql = "select * from measurement_quote where quote_id = :entity_id"
    check = await sql_exe(check_sql, {'entity_id': entity_id}, True)
    if check:
        sql = """
        update measurement_quote 
            set 
                measurement_id = :measurement_id,
                comments = :comments 
        where quote_id = :entity_id
        """
    else:
        sql = """
        insert into measurement_quote
            (quote_id, measurement_id, comments)
        values
            (:entity_id, :measurement_id, :comments)
        """
    data = {
        'entity_id': entity_id,
        'measurement_id': item.measurement_id,
        'comments': item.comments
    }
    try:
        code = 1
        message = 'Measurement updated successfully'
        data = await sql_exe(sql, data)
    except Exception as e:
        print(e)
        code = 0
        message = 'Failed to update 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,video_file,is_mandatory 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:
        video_file = ""
        if (i.video_file != ""):
            video_file = image(i.video_file, 'measurement')
        ques = {'id': i.id, 'option': i.question, 'label': i.label, 'video': video_file, 'is_mandatory': i.is_mandatory,
                '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': image(j.image, 'measurement')
            })
        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
        discount = grand_total * 0.1
        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.quote_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,
        '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,
        '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)
    billing_address = f"{item.billing_address.street},"\
        f"{item.billing_address.city},{item.billing_address.region},"\
        f"{item.billing_address.postcode}"

    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)
    shipping_address = f"{item.shipping_address.street},"\
        f"{item.shipping_address.city},{item.shipping_address.region},"\
        f"{item.shipping_address.postcode}"

    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
    # 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)

    # 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'] = ""
        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")
    await send_mail(["angika.devi@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)

            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)
            # 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: 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}"
    ref_sql = "insert into referrals (referrer_id, name, email, message) "\
        "values(:entity_id, :name, :email, :message)"
    CC = [ADMIN]
    SUB = "Referral Request Received"
    BODY = """Dear {}, <br/><br/>
    It really is great being referred to you by "{}", and we look forward to being of service. <br/>
    Please do activate your account by clicking the Activate Account button below.  <br/>
    <a href='https://hhcustomtailor.com/menstore/customer/account/login/'><button type='button'>Activate Account</button></a><br/>
    We are also delighted to be able to offer you a 10% discount on your first order with us, and invite you to visit our website to commence your journey into the world of bespoke tailoring. <br/>
    Please also do feel free to email us at <a href="mailto:info@hhcustomtailor.com">info@hhcustomtailor.com</a> should you have any questions at any time.<br/><br/><br/>
    
    Warmest Regards, <br/>
    Team Heritage House Custom Tailors<br/>
    All Custom. All Yours."""
    values = []
    for name, email in zip(items.name, items.email):
        data = {
            "entity_id": entity_id,
            "name": name,
            "email": email,
            "message": items.message
        }
        values.append(data)
        await send_mail([email], msg=BODY.format(name, username), sub=SUB, cc=CC)
        # check coupon_code exists by email
        sql = "select id from customer_promotions where customer_email =:email"
        res = await sql_exe(sql, {"email": email}, True)
        if res:
            split_email = email.split("@")
            coupon_code = f"HH.{split_email[0]}.10"
            coupon_sql = """
            insert into customer_promotions 
                (coupon_code, amt_in_percent, customer_email,
                is_active) 
                values (:coupon_code, 10, :email, 0)
            """
            await sql_exe(coupon_sql, {"coupon_code": coupon_code, "email": email})
    await sql_exe(ref_sql, values)
    # confirmation email to the sender
    BODY = """Dear {}, <br/>

    We truly want to thank you for forwarding your recommendations to (referred person’s name). <br/>
    We look forward to being of service and forwarding your discount to your dashboard as soon as their first purchase has been made. <br/>
    Wishing you the best of days always. <br/><br/><br/>
    
    Warmest Regards, <br/>
    Team Heritage House Custom Tailors<br/>
    All Custom. All Yours."""
    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):
    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:
            years = datetime.now().year - item.created_at.year
            if 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)
    # reward_amt = 0
    # if res:
    #     reward_amt = res.reward_amt
    return {
        "has_rewards": has_rewards,
        "rewards": rewards,
        # "total_amt": discount,
        # "reward_amt": reward_amt
    }


@app.post('/api/v1/apply_rewards/', tags=["Referral"])
async def apply_rewards(item: Rewards, entity_id: int = Depends(auth)):
    ref = await get_referral(entity_id)
    if item.quote_total >= item.reward_amt:
        used = item.reward_amt
    else:
        used = item.quote_total
    user_amt = used
    for order_id, value in ref['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(ref['rewards'].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_discount = :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_discount, 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": "Reward applied 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": 0, "entity_id": res.entity_id})

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

@app.get('/api/v1/fabric/{fabric_id}/', tags=["Fabrics"])
async def get_fabric(fabric_id: int):
    sql = "select id, sample_file_name as image, description from fabrics where id = :fabric_id"
    res = await sql_exe(sql, {"fabric_id": fabric_id}, True)
    if not res:
        return {"code": 0, "message": "Fabric not found"}
    thumb_file_name = res.image
    data = {
        "fabric_id": res.id,
        "description": res.description.strip(),
        "image": image(thumb_file_name, 'fabric')
    }
    return {"code": 1, "data": data, "message": "Fabric details fetched successfully"}