#!/usr/bin/python3
# coding=utf-8

import cgitb; cgitb.enable()
import cgi, re, os, sys, socket, datetime, pymysql, locale, random, subprocess
from datetime import datetime, timedelta
from http import cookies


# get some enviromental VALUES
locale.setlocale(locale.LC_ALL, 'el_GR')
form = cgi.FieldStorage()

htmlpage = form.getvalue('htmlpage')
page = form.getvalue('page')

# detect how 'index.html' is called and validate variables 'htmlpage' & 'page'
if page and os.path.isfile( '/home/nikos/www/cgi-bin/' + page ):
	page = page
elif form.getvalue('show') and os.path.isfile( htmlpage ):
	page = htmlpage.replace( '/home/nikos/public_html/', '' )
else:
	page = 'index.html'

FROM = form.getvalue('from')
MESSAGE = form.getvalue('message')
userform = form.getvalue('userform')

try:
	host = socket.gethostbyaddr( os.environ['REMOTE_ADDR'] )[0]
except KeyError:
	host = 'Not Available'
date = ( datetime.utcnow() + timedelta(hours=2) ).strftime( '%y-%m-%d %H:%M:%S' )
userinfo = os.environ['HTTP_USER_AGENT']


# identification of user browser
# ==============================
browser = userinfo.lower()
if   'chrome' in browser:
	browser = 'Chrome'
elif 'firefox' in browser:
	browser = 'Firefox'
elif 'opera' in browser:
	browser = 'Opera'
elif 'safari' in browser:
	browser = 'Safari'
elif 'msie' in browser:
	browser = 'Explorer'
else:
	browser = 'Unknown Browser'

# identification of user OS
# ==============================
useros = userinfo.lower()
if   'windows' in useros:
	useros = 'Windows'
elif 'mac' in useros:
	useros = 'MacOS'
elif 'linux' in useros:
	useros = 'Linux'
elif 'unix' in useros:
	useros = 'Unix'
elif 'bsd' in useros:
	useros = 'FreeBSD'
else:
	useros = 'Unknown OS'


# initialize cookie
cookie = cookies.SimpleCookie( os.environ.get('HTTP_COOKIE') )
cookie.load( cookie )
nikos = cookie.get('nikos')

# connect to database
con = pymysql.connect( db = 'metrites', host = 'localhost', user = 'nikos', passwd = 'somepassword', init_command='SET NAMES UTF8' )
cur = con.cursor()

print ( "Content-type: text/html; charset=utf-8\n" )


# =================================================================================================================
# if extra string is attached to the URL is 'stats' then show statistics for all HTML pages and exit
# =================================================================================================================
if form.getvalue('show') == 'stats':

	print(''' <center><h2><font color=lime> LOG </font> όλων των <font color=red> HTML </font> σελίδων</h2><br>
			  <table border=5 cellpadding=5 bgcolor=black>
			  <th><font color=orange size=5> Σελίδα </th>  <th><font color=orange size=5> Επισκέψεις </th>
	''')

	try:
		cur.execute( '''SELECT url, hits FROM counters ORDER BY hits DESC''' )

		data = cur.fetchall()
		for row in data:
			(url, hits) = row

			print( "<tr><td><center><a href='http://superhost.gr/?show=log&page=%s'><font color=tomato size=5> %s </a></td>" ) % (url, url)
			print( "<td><center><font color=cyan size=5> %s </a></td></tr>" ) % (hits)
	except pymysql.ProgrammingError as e:
		print( repr(e) )

	sys.exit(0)


# =================================================================================================================
# if extra string is attached to the URL is 'log' then show explicit page log and exit
# =================================================================================================================
if form.getvalue('show') == 'log':

	cur.execute('''SELECT hits FROM counters WHERE url = "?"''', (page,) )
	data = cur.fetchone()        #URL is unique, so should only be one

	print(''' <center><h2>  <font color=lime> LOG </font> της σελιδας <font color=red> %s </font> => <font color=magenta> %s </font> </h2><br>
			  <table border=5 cellpadding=5 bgcolor=blue>
			  <th><font color=yellow> Επισκέπτης </th>  <th><font color=yellow> Λειτουργικό </th>  <th><font color=yellow> Πλοηγός </th>  <th><font color=yellow> Επανάληψη </th>  <th><font color=yellow> Ημερομηνία </th>
	''') % (page, data[0])

	try:
		cur.execute( '''SELECT host, userOS, browser, hits, lastvisit FROM visitors
						WHERE counterID = (SELECT ID FROM counters WHERE url = "?") ORDER BY lastvisit DESC''', (page,) )

		data = cur.fetchall()
		for row in data:
			(host, userOS, hits, browser, lastvisit) = row
			lastvisit = lastvisit.strftime('%A %e %b, %H:%M').encode('utf8')

			print( "<tr>" )
			for item in (host, userOS, hits, browser, lastvisit):
				print( "<td><center><b><font color=white> %s </td>" ) % item
	except pymysql.ProgrammingError as e:
		print( repr(e) )

	sys.exit(0)


# =================================================================================================================
# if html form is submitted then send user mail
# =================================================================================================================
if( userform ):

	if (FROM is None) or (MESSAGE is None) or ('@' not in FROM) or ('Γράψε μου εδώ το σχόλιο σου!' in MESSAGE):
		print( "<h2><font color=red>Συμπλήρωσε σωστά το mail σου και δώσε το σχολιασμό σου!</font></h2>" )
	else:
		sp = subprocess.Popen(['mail', '-f', FROM, '-s', 'Πιθανός Πελάτης', 'support@superhost.gr'], stdin=subprocess.PIPE)
		sp.communicate(MESSAGE)
		res = sp.wait()
		if res:
			print( "<h2><font color=lime>Ευχαριστώ πολύ για το ενδιαφέρον! Θα επικοινωνήσω μαζί σου άμεσα :-)</font></h2>" )
		else:
			print( "<h2><font color=red>Δυστυχώς δεν μπόρεσε να αποσταλεί το e-mail :-(" )

	sys.exit(0)


# =================================================================================================================
# DATABASE INSERTS - do not increment the counter if a Cookie is set to the visitors browser already
# =================================================================================================================
if( ( not nikos ) and ( re.search( r'(msn|ovh|yandex|13448|kimsufi|corbina|spider|google|crawl|wowrack)', host ) is None ) ):

	try:
		#find the needed counter for the page URL
		cur.execute('''SELECT ID FROM counters WHERE url = "?"''', (page,) )
		data = cur.fetchone()        #URL is unique, so should only be one

		if not data:
			#first time for page; primary key is automatic, hit is defaulted
			cur.execute('''INSERT INTO counters (url) VALUES ("?")''', (page,) )
			cID = cur.lastrowid        #get the primary key value of the new record
		else:
			#found the page, save primary key and use it to issue hit UPDATE
			cID = data[0]
			cur.execute('''UPDATE counters SET hits = hits + 1 WHERE ID = "?"''', (cID,) )

		#find the visitor record for the (saved) cID and current host
		cur.execute('''SELECT * FROM visitors WHERE counterID = "?" and host = "?"''', (cID, host) )
		data = cur.fetchone()        #cID&host are unique

		if not data:
			#first time for this host on this page, create new record
			cur.execute('''INSERT INTO visitors (counterID, host, userOS, browser, lastvisit) VALUES ("?", "?", "?", "?", "?")''', (cID, host, useros, browser, date) )
		else:
			#found the page, save its primary key for later use
			vID = data[0]
			#UPDATE record using retrieved vID
			cur.execute('''UPDATE visitors SET userOS = "?", browser = "?", hits = hits + 1, lastvisit = "?"
									WHERE counterID = "?" and host = "?"''', (useros, browser, date, vID, host) )

		con.commit()		#if we made it here, the transaction is complete

	except pymysql.ProgrammingError as e:
		con.rollback()		#something failed, rollback the entire transaction
		print( repr(e) )


# =================================================================================================================
# return general counter, render html page and print or invoke a python script
# =================================================================================================================
cur.execute('''SELECT hits FROM counters WHERE url = "?"''', (page,) )
data = cur.fetchone()

# pick a random mp3 from the the music directory
quote = random.choice( list( open( "/home/nikos/www/data/private/quotes.txt", encoding="utf-8" ) ) )
# pick a random line from the quote text file
music = random.choice( os.listdir( "/home/nikos/www/data/private/music" ) )


if cur.rowcount:
	if page.endswith('.html'):
		f = open( "/home/nikos/www/" + page, encoding="utf-8" )
		htmldata = f.read()
		htmldata = htmldata % (quote, music)

		counter = ''' <center>
					  <a href="mailto:support@superhost.gr"> <img src="/data/images/mail.png"></a>
					  <table border=2 cellpadding=2 bgcolor=black>
							<td><font color=lime>Αριθμός Επισκεπτών</td>
							<td><a href="http://superhost.gr/?show=log&page=%s"><font color=yellow> %d </td>
					  </table><br>
			  ''' % (page, data[0])

		template = htmldata + counter
		print( template )
	elif page.endswith('.py'):
		print( '''<meta http-equiv="REFRESH" content="0;/cgi-bin/%s">''' ) % page
else:
	print( "Δεν μας θυμήθηκε κανείς αφεντικό !!! Της λησμονιάς το νερό ήπιαν φαίνεται !!!" )