#!/usr/bin/python3
#==================================================================================
# This python registration script can handle an insert, an update and a delete
# if cust_id cookie does not exit --> must be an insert (new registration)  
# if cust_id cookie exists        --> must be an update (update/delete profile)
#==================================================================================
import os
import cgi                              #cgi 
import cgitb                            #cgi with traceback error handling
import re                               #regular expression
from urllib import parse
import pymysql as mydb                  #Mysql 3x database driver
import config

cgitb.enable()

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

msg     = ''								#global variables
cust_id = ''
user  = ''
pswd  = ''
pswd2 = ''
first = ''
last  = ''
email = ''
phone = ''
addr  = ''
user_error  = ''
pswd_error  = ''
first_error = ''
last_error  = ''
email_error = ''
phone_error = ''
addr_error  = ''

#==========================================================================
# get_cookies(): retrieve all cookies
#                return a cookies dictionary
#==========================================================================
def get_cookies():
    cookies = {}

    cookiesStr = os.environ.get('HTTP_COOKIE')              #obtain the HTTP cookies
    if not cookiesStr: return cookies                       #if no cookies, return 

    cookiesArray = cookiesStr.split('; ')
    for cookie in cookiesArray:
        (name, value) = cookie.split('=',1)                 #split on first =
        value_decoded = parse.unquote(value)                #decode the value (if encoded)
        cookies[name] = value_decoded                       #cookie value (encoded)

    return cookies

#=============================================================================
# set_cookie(): set a cookie
#=============================================================================
def set_cookie(name,value):
    value2 = parse.quote(str(value))                        #url encode the cookie
    print(F"Set-Cookie: {name}={value2}; path=/")           #create a cookie

#=============================================================================
# del_cookie(): delete a cookie
#=============================================================================
def del_cookie(name):
    print(F"Set-Cookie: {name}=xyz; max-age=-999; path=/")  #delete a cookie

#=============================================================================
# Validate all required input fields
#=============================================================================
def validate():
    global user, pswd, pswd2, first, last, email, phone, addr, msg
    global user_error, pswd_error, first_error, last_error, email_error, addr_error
        
    user     = elements.getvalue('user')   or ""          #get fields from web page                  
    pswd     = elements.getvalue('pswd')   or ""
    pswd2    = elements.getvalue('pswd2')  or ""      
    first    = elements.getvalue('first')  or ""      
    last     = elements.getvalue('last')   or ""      
    email    = elements.getvalue('email')  or "" 
    phone    = elements.getvalue('phone')  or ""      
    addr     = elements.getvalue('addr')   or ""
    update   = elements.getvalue('update') or ""       
    delete   = elements.getvalue('delete') or ""  

    if (not user  or re.match('/^\s*$/',user)):  user_error = '*'         #if field is null or all spaces 
    if (not pswd  or re.match('/^\s*$/',pswd)):  pswd_error = '*'        
    if (not pswd2 or re.match('/^\s*$/',pswd2)): pswd_error = '*'        
    if (not first or re.match('/^\s*$/',first)): first_error= '*'
    if (not last  or re.match('/^\s*$/',last)):  last_error = '*'        
    if (not last  or re.match('/^\s*$/',last)):  email_error= '*'        
    if (not addr  or re.match('/^\s*$/',addr)):  addr_error = '*'

    if (first_error or last_error or user_error or pswd_error or email_error or addr_error):
        msg  = 'Please enter required field(s) above!'     

    if (pswd != pswd2):                          
        pswd_error = '*'
        msg  = 'Password confirmation does not match password!'     

#===============================================================================
# Read customer data from the database (2 purposes)
# if new -    purpose is ensure unique user  --> validate user uniqueness
# if exists - purpose is populate the screen --> read all, and populate screen   
#===============================================================================
def read_customer(purpose):
    global user, pswd, pswd2, first, last, phone, email, addr, msg;   
 
    try:
        conn   = mydb.connect(host='localhost',user='demo2',password='demo2',database='demo2')
        cursor = conn.cursor()
                
        if (purpose=='check_unique'):                   #check unique user                            
            sql = F"""SELECT cust_id, user  
                        FROM shop_customer 
                       WHERE user = '{user}'"""                      

        if (purpose=='populate'):                       #client requesting profile update
            sql = F"""SELECT *  
                        FROM shop_customer 
                       WHERE cust_id = '{cust_id}'"""

        cursor.execute(sql)                    #execute the sql

    except mydb.Error as e:
        errorNum = e.args[0]
        errorMsg = e.args[1]
        msg = 'Database Error - ' + str(errorNum) + errorMsg
        return      

    results = cursor.fetchall()                     #get all the rows (should be 0 or 1 row)

    if results and purpose=='check_unique':         #if row exists
        msg  = F"User {user} is already on file, please choose another"         

    if results and purpose=='populate':             #if row exists
        row   = results[0];                         #get first row
        user  = row[1];                             #populate the screen                                                                    
        pswd  = row[2];                             #with data from database                                
        pswd2 = row[2];
        first = row[3]; 
        last  = row[4];
        addr  = row[5];
        email = row[6];
        phone = row[7];

    cursor.close()
    conn.close()

#==============================================================================
# Display the HTML page  
# if there are errors, highlight those with an error message
#==============================================================================
def display():

    if (not cust_id):                                           #if no cust_id 
        button_name  = 'Register'                               #must be a new registration
        readonly     = ''
    else:                                                       #if cust_id cookie exists 
        button_name  = 'Update'                                 #must be update profile
        readonly     = 'readonly'    

    cust_id_cookie = cookies.get('cust_id') or cust_id          #either the cookie or DB value

    print(F"""
        <html>
        <head>
        <title>Register or Update Profile</title>
        <style>
            a      {{text-decoration:none; color:brown}}
           .field  {{width:400}}
           .field2 {{width:168}}
           .grn    {{color:white; background-color:green; height:30px; border-radius:5px; cursor:pointer}}
           .red    {{color:gray;  background-color:brown; height:30px; border-radius:5px}}
        </style>
        </head>
        <body bgcolor=lightyellow>
        <h1><center>Registration Profile</center></h1>
        <form method=POST>
        <fieldset style="width:580;border-color:red">
        <legend align="left">User Registration Profile [{cust_id}]</legend>
        <table>
        <tr><td>Enter a user id       
            <td><input type=text     name=user  size=54 value='{user}' class=field {readonly} > <font color=red>{user_error}</font> 
        <tr><td>Enter a password      
            <td><input type=password name=pswd  size=20 value='{pswd}' class=field2>
                Confirm                   
                <input type=password name=pswd2 size=20 value='{pswd2}'class=field2> <font color=red>{pswd_error}</font> 
        <tr><td>Enter first name      
            <td><input type=text     name=first size=54 value='{first}' class=field> <font color=red>{first_error}</font>
        <tr><td>Enter last name       
            <td><input type=text     name=last  size=54 value='{last}'  class=field> <font color=red>{last_error}</font>
        <tr><td>Enter your email  
            <td><input type=email    name=email size=54 value='{email}' class=field> <font color=red>{email_error}</font> 
        <tr><td>Enter your telephone  
            <td><input type=phone    name=phone size=54 value='{phone}' class=field> 
        <tr><td>Enter your address    
            <td><textarea name=addr rows=4 class=field>{addr}</textarea>  <font color=red>{addr_error}</font>
        <tr><td><td><input type=submit name=update value={button_name} class=grn> 
                    <input type=submit name=delete value="Delete"      class=red disabled > 
        </table>
        </fieldset>
        </form>
        <div id=msg style="color:red;"> {msg}   </div>
        <hr/>
        <center>
        <a href=shopBrowse.py> shop       </a> |
        <a href=shop.py?out=y> logout     </a>
        </center>
        </body>
        </html>
    """)

#=============================================================================
# Insert data in the database - for new registration
# retrieve the customer id PK generation by the auto_increment
# save cookie variables 
#=============================================================================
def insert_data():
    global cust_id, msg   
 
    user2  = re.sub("[;<>\/'&]","",user)                    #eliminate ;<>\/'& characters
    pswd2  = re.sub("[;<>\/'&]","",pswd) 
    first2 = re.sub("[;<>\/'&]","",first) 
    last2  = re.sub("[;<>\/'&]","",last) 
    addr2  = re.sub("[;<>\/'&]","",addr) 
    email2 = re.sub("[;<>\/'&]","",email) 
    phone2 = re.sub("[;<>\/'&]","",phone) 

    try:
        conn = mydb.connect(host='localhost',user='demo2',password='demo2',database='demo2')
 
        insert = F"""INSERT INTO shop_customer 
                     VALUES(0,'{user2}','{pswd2}','{first2}','{last2}','{addr2}','{email2}','{phone2}')"""

        cursor = conn.cursor()                              #create a cursor
        cursor.execute(insert);                             #execute the sql
        conn.commit();                                      #commit to database

#       select = "SELECT LAST_INSERT_ID() as id"            #retrieve value of cust_id PK (Mysql only) 
        select = F"""SELECT cust_id                         #retrieve value of cust_id PK  
                       FROM shop_customer 
                      WHERE user = '{user}'"""

        cursor.execute(select);                             #execute the sql

    except mydb.Error as e:
        errorNum = e.args[0]
        errorMsg = e.args[1]
        msg = 'Database Error - ' + str(errorNum) + errorMsg
        return      
            
    results = cursor.fetchall()                 #get all the rows (should only be 1 row)
    if (not results):
        msg = F"Could not execute the query {select}"
        return        
       
    row = results[0];                           #get the first row
    cust_id = row[0];                           #get the first column                                    

    cursor.close()                              #close the cursor/buffer
    conn.close()                                #close the connection

    set_cookie('cust_id',cust_id)               #save cookie

    msg = 'Registration successful!';

#=============================================================================
# Determine whether update or delete button is requested
#=============================================================================
def update_data():

    if (not cust_id): 
        return

    if (update):                                #update button is pressed
        update();
#   if (delete):                                #delete button is pressed
#       delete();

#=============================================================================
# Update data in the database - for profile update
#=============================================================================
def update():
    global msg   

    user2  = re.sub("[;<>\/'&]","",user)                    #eliminate ;<>\/'& characters
    pswd2  = re.sub("[;<>\/'&]","",pswd) 
    first2 = re.sub("[;<>\/'&]","",first) 
    last2  = re.sub("[;<>\/'&]","",last) 
    addr2  = re.sub("[;<>\/'&]","",addr) 
    email2 = re.sub("[;<>\/'&]","",email) 
    phone2 = re.sub("[;<>\/'&]","",phone) 

    sql = F"""UPDATE shop_customer
                 SET user    = '{user2}', 
                     pswd    = '{pswd2}', 
                     fname   = '{first2}', 
                     lname   = '{last2}', 
                     address = '{addr2}', 
                     email   = '{email2}', 
                     phone   = '{phone2}'
               WHERE cust_id = '{cust_id}' """
    try:
        conn = mydb.connect(host='localhost',user='demo2',password='demo2',database='demo2')

        cursor = conn.cursor()                              #create a cursor
        cursor.execute(sql);                                #execute the sql
        conn.commit();                                      #commit to database

    except mydb.Error as e:
        errorNum = e.args[0]
        errorMsg = e.args[1]
        msg = 'Database Error - ' + str(errorNum) + errorMsg
        return      

    cursor.close()                              #close the cursor/buffer
    conn.close()                                #close the connection

    msg = 'Your profile has been updated!'

#=============================================================================
# Delete data from the database - profile delete
#=============================================================================
def delete():
    global msg   

    sql = F"""DELETE FROM shop_customer
               WHERE cust_id = '{cust_id}' """            
    try:
        conn = mydb.connect(host='localhost',user='demo2',password='demo2',database='demo2')

        cursor = conn.cursor()                              #create a cursor
        cursor.execute(sql);                                #execute the sql
        conn.commit();                                      #commit to database

    except mydb.Error as e:
        errorNum = e.args[0]
        errorMsg = e.args[1]
        msg = 'Database Error - ' + str(errorNum) + errorMsg
        return      
       
    msg = 'Your profile has been deleted!'
        
    user =''
    pswd=''
    pswd2=''                                #clear out the screen
    first=''
    last=''
    email=''
    phone=''
    addr=''                                                                                                       

    del_cookie('cust_id')                   #clear out cust_id cookie           

#===============================================================================
# main code
#===============================================================================
cookies = get_cookies()                         #retrieve all cookies
cust_id = cookies.get('cust_id') or ""               

if not cust_id:                                 #if no cust_id cookie
    mode='new'                                  #  it must be a 'new' registration       
else:                                           #otherwise
    mode='upd'                                  #  it must be an 'update' to profile       

elements = cgi.FieldStorage()                   #obtain the http parameters
     
if not elements:								#if no data entered on the screen
    read_customer('populate')					#populate the data from the database

if elements:                                    #if data is entered on screen
    validate()                                  #validate form fields   
    if not msg and mode=='new':                 #if no error messages
        read_customer('check_unique');          #check to make sure the user is unique

    if not msg:                                 #if no error messages 
        if mode=='new':                         #  if new registration
            insert_data()                       #    register a new customer
        if mode=='upd':                         #  if update                  
            update_data()                       #    update customer profile
            
print('\n')                                     #end the 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
#================================================================================