#!/usr/bin/env python3
#====================================================================================
# This Python script can handle an insert, an update and a delete of a shopping cart
# if no cookie cust_id, redirect to login page
#====================================================================================
import config
import COOKIE
import WEB
import DB
from urllib import parse

input     = {}                              #global variables
cust_id   = ''
fname     = ''
products  = []
cart      = []
filter    = ''
msg       = ''

#config.RUN_MODE='test'                     #turn on test mode
print('Content-type: text/html')            #required HTTP response header
if config.RUN_MODE=='test': print('\n')     #if mode='test', end the headers so all output will print

#=============================================================================
# execute SQL against database
#=============================================================================
def exec(sql):
    global msg   
 
    msg  = ''
    conn = DB.connect('demo2','demo2','demo2','mysql')
    if isinstance(conn,str): msg=conn; return           #connection error 

    if 'select' in sql.lower():
        result = DB.select(sql)
    else:
        result = DB.update(sql)
    if isinstance(result,str): msg=result; return       #database error 

    return result
    conn.close()                                        #close the connection

#=============================================================================
# Read the customer from database
#=============================================================================
def read_customer():
    global fname, msg   
 
    sql = F""" SELECT * FROM shop_customer 
                WHERE cust_id = {cust_id} """ 
    result = exec(sql)             
    if (not result):       
        msg = F"Customer id {cust_id} does not exist in the database"
        return        
    row   = result[0]                                   #take first row
    fname = row['fname']                             
        
#=============================================================================
# Read all products from the database
#=============================================================================
def read_products():
    global prod_srch, products, msg  
    
    if not prod_srch: prod_srch='ice cream'

    sql = F""" SELECT * FROM shop_product 
                WHERE prod_desc LIKE '%{prod_srch}%' 
                   OR prod_type LIKE '%{prod_srch}%' """ 
    result = exec(sql)              
    if (not result):       
        msg = "There are no products matching your criteria"
        return         
    products = result
    
#=============================================================================
# Read cart data from the database
#=============================================================================
def read_cart():
    global cart, msg    
 
    sql = F""" SELECT * FROM shop_cart 
                WHERE cust_id = {cust_id} """
    result = exec(sql)       
    if (not result):       
        msg = F"Your shopping cart is empty"
        return        
    cart = result
    num  = len(cart)
    msg  = F"You have {num} items in your shopping cart"

#=============================================================================
# Validate all required input fields
#=============================================================================
def validate():
    global msg    
        
    valid = False
    for prod in input:
        qty = input[prod]                   #get the quantity entered 
        qty = int(qty)                      #convert to integer
        if qty >= 0: valid=True    
    if valid==False:
        msg = 'Please enter product(s) quantity'
    else:
        msg = ''
    
#==============================================================================
# Display the HTML page 
# The product list comes from the database 
#==============================================================================
def display():
    global cart_id 
    
    print("""
            <html>
            <head>
            <title>Add to Shopping Cart</title>
            <style>
                a      {text-decoration:none; color:brown;}
                table  {background-color:dddddd; border:black solid 1px; border-radius:5px}
                th     {background-color:gray}
                td     {text-align:center; padding-top:8; border-top:black solid 1px}
                #srch  {width:507px; height:40px; border:brown solid 1px; border-radius:10px}
                #desc  {text-align:left}
                #qty   {width:40px}                 
                .small {height:50px; width:50px}
                .gray  {color:white; background-color:333333; height:30px; border-radius:5px; cursor:pointer}
                .green {color:white; background-color:green;  height:30px; border-radius:5px; cursor:pointer}
            </style>
            </head>
            <body bgcolor=lightyellow>
            <h1 align=center>The Ice Cream Shop</h1>
    """)            
    prod_srch_encoded = parse.quote(prod_srch)
    prod_srch_upcase  = prod_srch.title()						#capitalize first letter
    print(F"""            
            <h2>{fname}, select from our fine products</h2>
            <form>
            <b><i>Search for...</i></b><br>
            <table>
            <tr><td><input type=text  name=srch id=srch >
                <td><input type=image src="img/search.jpg" width=40 height=40>
            </table> 
            </form>            
            <table width=560>
            <tr><td><a href=shopBrowse.py?srch=ice+cream><img src=img/ice-cream.jpg><br>| Ice Cream |</a>
                <td><a href=shopBrowse.py?srch=topping>  <img src=img/topping.jpg>  <br>| Topping   |</a>  
                <td><a href=shopBrowse.py?srch=specialty><img src=img/specialty.jpg><br>| Specialty |</a>  
            </table>         
            <br>
            <form method=post name=frm >
            <fieldset style='width:530;border-color:red'>
            <legend> {prod_srch_upcase} </legend>
            <table>
            <tr><th><th>Qty<th width=80>Product Id<th width=180>Description<th width=100>In Stock<th width=50>Price</tr>
    """)

    for product in products:
        prod_id    = product.get('prod_id')
        prod_id    = str(prod_id)
        prod_desc  = product.get('prod_desc')
        prod_price = product.get('prod_price')  
        prod_avail = product.get('prod_avail')
        prod_qty   = input.get(prod_id)                             #product quantity entered on screen 
        disabled   ='DISABLED'          if prod_avail=='N' else ''
        avail_msg  ='<i>(out of stock)' if prod_avail=='N' else ''
 
        if prod_avail=='Y': image_name=prod_desc.replace(' ','-') 
        else:               image_name='out-of-stock'

        print(F"<tr><td><img src=img/{image_name}.jpg class=small title={image_name}>")
        print(F"    <td><input id=qty type=number min=0 name={prod_id} value={prod_qty} {disabled} >")
        print(F"    <td>{prod_id} <td id=desc>{prod_desc} <td>{avail_msg} <td>${prod_price}</tr>")
    
    print(F"""
            </table>
            </fieldset>
            <br><font color=red>{msg}  </font><br>
            <input type=hidden name=srch value='{prod_srch}'>
    """)
    print("""
            <br>
            <input type=submit name=add      value='Add to Cart' class=gray >
            <input type=button name=view     value=' View Cart ' class=gray  onClick=location.href='shopCart.py'>
            <input type=button name=checkout value=' Checkout  ' class=green onClick=location.href='shopCheckout.py'>
            </form>
            <hr/>
            <center>
                                     shop               |
            <a href=shopCart.py>     shopping cart </a> |
            <a href=shopCheckout.py> checkout      </a> |
            <a href=shopQuery.py>    search orders </a> |
            <a href=shopProf.py>     profile       </a> |
            <a href=shop.py?out=y>   logout        </a>
            </center>
    """)
                 
    print("<body")
    print("</html>")

#=============================================================================
# Insert or update cart data in the database 
#=============================================================================
def add_upd_cart():
    global msg;
    
    num_rows_upd = 0   
 
    conn = DB.connect('demo2','demo2','demo2','mysql')
    if isinstance(conn,str): msg=conn; return               #connection error 

    for prod_id in input:                                   #get each product from the screen 
        sql = F""" SELECT * FROM shop_product 
                    WHERE prod_id = {prod_id} """ 
        result = exec(sql)                             

        product = result[0]								#take the first row
        desc    = product['prod_desc']
        type    = product['prod_type']
        price   = product['prod_price']
        qty     = input[prod_id]
        total   = int(qty) * float(price)  
        total   = round(total,2)  

        sql = F""" SELECT * FROM shop_cart 
                    WHERE cust_id = {cust_id}
                      AND prod_id = {prod_id} """        

        result = DB.select(sql)
        if isinstance(result,str): msg=result; return       #database error 

        if not result:                                      #item does not exist in cart -> insert
            sql = F""" INSERT INTO shop_cart 
                       VALUES(0,{cust_id},{prod_id},'{desc}','{type}',{price},{qty}) """

        if result and int(qty)>0:                           #item exists & quantity > 0 -> update
            sql = F""" UPDATE shop_cart
                          SET qty      = {qty} 
                        WHERE cust_id  = {cust_id}
                          AND prod_id  = {prod_id} """

        if result and int(qty)==0:                          #item exists & quantity = 0 -> delete
            sql = F""" DELETE from shop_cart
                        WHERE cust_id  = {cust_id}
                          AND prod_id  = {prod_id} """
            
        result = DB.update(sql)
        if isinstance(result,str): msg=result; return       #database error 

        num_rows_upd += 1                              

    msg = F'{num_rows_upd} item(s) added/updated in your cart!';
    conn.close()                                #close the connection
    
#==========================================================================
# main code
#==========================================================================
cookies = COOKIE.getAll()                           #get the cookies
cust_id = cookies.get('cust_id')  
if not cust_id:                                     #if there is no cust_id cookie --> not logged in
    print("Location: shop.py")                      #redirect to login page
    print('\n')                                     #end of response headers
    exit()

input = WEB.input()                                 #obtain the input fields from screen

prod_srch = input.get('srch') or ""
add_btn   = input.get('add')  or ""

if prod_srch: del input['srch']                     #delete the search criterea from the input param      
if add_btn  : del input['add']                      #delete the button from the input param      

read_customer()
read_cart()        
read_products()

if add_btn:
    validate()                                      #validate form fields   
    if not msg:
        add_upd_cart();       
    
print('\n')                                         #end of response headers
display()                                           #display the screen




#=== link to see the python code ================================================
import os
import sys
sys.path.insert(0,'/home/s/sultans/web/python/demo')
import zCode                          #import func to display the Python code
filename = os.path.abspath(__file__)  #get absolute file name 
zCode.display(filename)               #call it
#================================================================================