from flask import Flask, render_template, request, flash, redirect, url_for, session
from datetime import datetime
import sqlite3
import array
import time
import json
import mysql.connector
import smtplib
from email.message import EmailMessage
from utils import ContactForm, send_email, get_browser_language, get_price_strings
import requests
import json
import glob
import locale
import os
from jproperties import Properties
#from flask_autoindex import AutoIndex


app = Flask(__name__)
configs = Properties()
with open(app.root_path + '/app-config.properties', 'rb') as config_file:
    configs.load(config_file)

app.secret_key = configs.get("APP_KEY").data
is_in_maintainace = eval(configs.get("MAINTENANCE").data)

lang_list = []
languages = {}

@app.context_processor
def inject_now():
    return {
        'now': datetime.utcnow(),
        'analytics_enabled': eval(configs.get("ANALYTICS_ENABLED").data),
        'analytics_code': configs.get("ANALYTICS_CODE").data,
        'cookie': eval(configs.get("COOKIE_DISCLAIMER").data)
    }


def init_app():
    language = 'it_IT.utf8'
    locale.setlocale(locale.LC_ALL, language)
    language_list = glob.glob(app.static_folder + "/languages/*.json")
    #print(language_list)
    for lang in language_list:
        #filename = lang.split('/')
        #lang_code = filename[6].split('.')[0]
        path,filename = os.path.split(lang)
        lang_code = filename.split('.')[0]
        #print(lang_code)
        languages[lang_code] = {}
        lang_list.append(lang_code)
        with open(lang, 'r', encoding='utf8') as file:
            languages[lang_code] = json.loads(file.read())
init_app()


@app.errorhandler(404) 
def not_found(e):
    language = get_browser_language(request.accept_languages, lang_list)
    return render_template("404.html", **languages[language])

ppath = "./static/"
#idx = AutoIndex(app, browse_root=ppath, add_url_rules=False)

const_db_name = "static/exp_portale_awareness.db"
emailRegex = r'\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,7}\b'
creditCardRegex1 = r"((\d)(?!\2{3})){16}"
creditCardRegex2 = r"(\d{4})(-?)(\d{4})(\2\d{4}){2}"

#@app.route('/')
#def index():
#	if session and 'loggedin' in session:
#		flash("")
#		return render_template("dashboard.html")
#	else:
#		flash("")
#		return render_template("index.html")

def check_is_in_maintainace(f):
    def decorated_function(*args, **kwargs):
        if is_in_maintainace:
            language = get_browser_language(request.accept_languages, lang_list)    
            return render_template("maintenance.html",  **languages[language])
        return f(*args, **kwargs)
    decorated_function.__name__ = f.__name__
    return decorated_function

@app.route('/', methods=['GET'])
@check_is_in_maintainace
def index():
    language = get_browser_language(request.accept_languages, lang_list)    
    flash("")
    conn = connect_to_mysql()
    cursor=conn.cursor() 
    cursor.execute("SELECT * FROM courses WHERE deleted=0 ORDER BY creation_date DESC LIMIT 4")
    news = cursor.fetchall()

    form = ContactForm() 
    
    if form.validate_on_submit():
        name = form.name.data
        email = form.email.data
        phone = form.phone.data
        subject = form.subject.data
        message = form.message.data
        send_email(name, email, phone, subject, message)
        
        flash('Email sent successfully!', 'success')
        return redirect(url_for('thanks'))
    #return render_template("index.html", new=news, len=len(news), form=form, **languages[language])
    return redirect("learning")
        

@app.route('/servizi', methods=['GET'])
@check_is_in_maintainace
def servizi():
    language = get_browser_language(request.accept_languages, lang_list)

    flash("")
    return render_template("servizi.html", **languages[language])

@app.route('/contacts', methods=['GET', 'POST'])
@check_is_in_maintainace
def contacts():
    language = get_browser_language(request.accept_languages, lang_list)
    form = ContactForm()
    if form.validate_on_submit(): 
        name = form.name.data
        email = form.email.data
        phone = form.phone.data
        subject = form.subject.data
        message = form.message.data
        send_email(name, email, phone, subject, message)
        
        flash('Email sent successfully!', 'success')
        return redirect(url_for('thanks'))
    return render_template("contacts.html", form=form, **languages[language], current_page = 'contacts')


@app.route('/phishing', methods=['GET'])
@check_is_in_maintainace
def phishing():
    language = get_browser_language(request.accept_languages, lang_list)
    
    flash("")
    return render_template("phishing.html", **languages[language], current_page='phishing')

@app.route('/cyber-range', methods=['GET'])
@check_is_in_maintainace
def cyberrange():
    language = get_browser_language(request.accept_languages, lang_list)
    
    flash("")
    return render_template("cyber-range.html", **languages[language], current_page='cyber-range')

@app.route('/gamification', methods=['GET'])
@check_is_in_maintainace
def gamification():
    language = get_browser_language(request.accept_languages, lang_list)
     
    flash("")
    return render_template("gamification.html", **languages[language], current_page='gamification')

@app.route('/awareness-programs', methods=['GET'])
@check_is_in_maintainace
def programmiawareness():
    language = get_browser_language(request.accept_languages, lang_list)
     
    flash("")
    return render_template("awareness-programs.html", **languages[language], current_page='awareness-programs')


@app.route('/training', methods=['GET'])
@check_is_in_maintainace
def training():
    language = get_browser_language(request.accept_languages, lang_list)
    flash("")
    return render_template("training.html", **languages[language], current_page='training_courses')

@app.route('/thanks', methods=['GET'])
@check_is_in_maintainace
def thanks():
    language = get_browser_language(request.accept_languages, lang_list)
    flash("")
    return render_template("thanks.html", **languages[language])

@app.route('/learning/course/<string:courseId>/', methods=['GET'])
@app.route('/learning/course/<string:courseId>/<string:courseName>', methods=['GET'])
@check_is_in_maintainace
def course(courseId, courseName=None):
    language = get_browser_language(request.accept_languages, lang_list)
    flash("")
    conn = connect_to_mysql()
    cursor = conn.cursor()

    cursor.execute("SELECT udemyId FROM courses WHERE id = %s AND deleted = 0", (courseId,))
    udemy_id_row = cursor.fetchone()

    if not udemy_id_row:
        return render_template("404.html", **languages[language])

    udemy_id = udemy_id_row[0]
    price_string, full_price_string = get_price_strings(udemy_id)

    if courseName:
        courseName = courseName.replace("_", " ")

    cursor.execute("SELECT * FROM courses WHERE id = %s AND deleted = 0", (courseId,))
    course = cursor.fetchone()

    if not course:
        return render_template("404.html", **languages[language])

    if courseName and courseName != course[1]:
        return render_template("404.html", **languages[language])

    cursor.execute("SELECT * FROM badges WHERE %s LIKE CONCAT(linked_course, '%')", (course[1],))
    badges_with_course_name = cursor.fetchall()

    cursor.execute("SELECT * FROM badges WHERE %s LIKE name AND (linked_course IS NULL)", (course[1],))
    badges_without_course_name = cursor.fetchall()


    cursor.execute("select t.* from tags t inner JOIN courses_tags ct on t.id = ct.id_tag inner join courses c on ct.id_course = c.id where c.id = %s AND c.deleted = 0 AND t.deleted = 0", (courseId,))
    tags = cursor.fetchall()

    tagsIds = [str(tag[0]) for tag in tags]
    tagsIdsstring = ','.join(tagsIds)

    if not tags:
        # Se non ci sono tag, impostiamo tags su una lista vuota per evitare errori
        tags = []

    if tags:
        placeholder = ', '.join(['%s']*len(tagsIds))
        query = f"select distinct c.* from tags t inner JOIN courses_tags ct on t.id = ct.id_tag inner join courses c on ct.id_course = c.id where t.id in ({placeholder}) and c.id != %s AND c.deleted = 0 AND t.deleted = 0 ORDER by c.creation_date DESC LIMIT 4"
        cursor.execute(query, tagsIds + [courseId])
        related_courses = cursor.fetchall() 
    else:
        related_courses = []

    return render_template("course.html", badges=badges_with_course_name, badges_null=badges_without_course_name, course=course, related_courses=related_courses, tags=tags, price_string=price_string, full_price_string=full_price_string, **languages[language])
    
@app.route('/learning/tag/<string:tag_id>', methods=['GET'])
@check_is_in_maintainace
def tag(tag_id):
    language = get_browser_language(request.accept_languages, lang_list)
    flash("")
    conn = connect_to_mysql()
    cursor = conn.cursor()
    
    cursor.execute("SELECT c.*, t.description FROM courses c INNER JOIN courses_tags ct ON c.id = ct.id_course INNER JOIN tags t ON ct.id_tag = t.id WHERE t.id = %s AND c.deleted = 0 AND t.deleted = 0 ORDER BY c.creation_date DESC", (tag_id,))
    related_courses = cursor.fetchall()
    
    if not related_courses:
        return render_template("404.html", **languages[language])
    tag_name = related_courses[0][18]
    
    return render_template("tag.html", related_courses=related_courses, tag_name=tag_name, **languages[language])

@app.route('/learning', methods=['GET'])
@check_is_in_maintainace
def learning():
    language = get_browser_language(request.accept_languages, lang_list)

    flash("")
    conn = connect_to_mysql()
    cursor = conn.cursor()

    search_tag = request.args.get('tag')

    if search_tag:
        cursor.execute("SELECT * FROM courses WHERE deleted=0 AND FIND_IN_SET(%s, tags) > 0 ORDER BY id DESC", (search_tag,))
    else:
        cursor.execute("SELECT * FROM courses WHERE deleted=0 ORDER BY id DESC")


    rows = cursor.fetchall()
    return render_template("learning.html", items=rows, len=len(rows), **languages[language], current_page='training_learning') 

@app.route('/badges', methods=['GET'])
@check_is_in_maintainace
def badges():
    language = get_browser_language(request.accept_languages, lang_list)
    flash("")
    conn = connect_to_mysql()
    cursor = conn.cursor()

    # Query for badges with a non-empty course_name
    cursor.execute(" SELECT id, name, image, tags FROM badges WHERE image is not NULL ")
    badges = cursor.fetchall()

    return render_template("badges.html", **languages[language], badges=badges, current_page='badges')

@app.route('/badges/id/<string:badgeId>/', methods=['GET'])
@app.route('/badges/id/<string:badgeId>/<string:badgeName>', methods=['GET'])
@check_is_in_maintainace
def badgeid(badgeId, badgeName=None):
    language = get_browser_language(request.accept_languages, lang_list)
    flash("")
    conn = connect_to_mysql()
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM badges WHERE id = %s AND linked_course is not NULL", (badgeId,))
    badge = cursor.fetchone()
    if badge:
        cursor.execute("SELECT * FROM courses WHERE name LIKE CONCAT(%s, '%')", (badge[7],))
        linked_courses = cursor.fetchall() 
    else:
        return render_template("404.html", **languages[language])

    return render_template("badgeid.html", badge=badge, linked_courses=linked_courses, **languages[language])


#@app.route('/service/phishing', methods=['GET'])
#def phishing():
#	flash("")
#	return render_template("phishing.html")

@app.route('/service/cyberRange', methods=['GET'])
@check_is_in_maintainace
def cyberRange():
    language = get_browser_language(request.accept_languages, lang_list)
    flash("")
    return render_template("cyberRange.html", **languages[language])

# Rotta per il The CISO Challenge
#@app.route('/the-security-challenge', methods=['GET'])
#@app.route('/the-Security-Challenge', methods=['GET'])
def theSecurityChallenge():
    return render_template("the-Security-Challenge.html")

@app.route('/static')
@app.route('/static/<path:path>')
def autoindex(path='.'):
    return idx.render_autoindex(path)

def connect_to_mysql():
    try:
        connection = mysql.connector.connect(
            host= configs.get("DB_HOST").data,             
            user= configs.get("DB_USER").data,             
            password= configs.get("DB_PWD").data,            
            database= configs.get("DB_SCHEMA").data
        )       
        if connection.is_connected():
            print("Connessione al database MySQL riuscita")    
            return connection    
    except mysql.connector.Error as error:
        print("Errore durante la connessione a MySQL:", error)
        return None
    
def close_connection(connection):     
    if connection.is_connected():        
        connection.close()         
        print("Connessione al database MySQL chiusa")


#IMPORTANT: comment next line to deploy on Heroku
#app.run(host="0.0.0.0", port=5000)