from flask import Flask, render_template, request, redirect, url_for, session, jsonify, flash
import sqlite3
from flask_socketio import SocketIO, send, emit
from datetime import datetime

app = Flask(__name__)
app.secret_key = 'your_secret_key'  
socketio = SocketIO(app)

# Function to create a connection to the SQLite database
def get_db_connection():
    conn = sqlite3.connect('database.db')
    conn.row_factory = sqlite3.Row
    return conn

# Function to initialize the database
def init_db():
    conn = get_db_connection()
    with app.open_resource('schema.sql', mode='r') as f:
        conn.cursor().executescript(f.read())
    conn.commit()
    conn.close()

# Initialize the database
init_db()


# to get the challenges for users
def get_all_challenges():
    conn = get_db_connection()
    all_challanges = conn.execute(f'SELECT * FROM challenges WHERE player = "{session["username"]}" OR username = "{session["username"]}" AND accepted = 1 ORDER BY id DESC').fetchall()
    conn.close()
    return all_challanges

def get_all_unread_notifications():
    all_c = get_all_challenges()
    n = 0
    for c in all_c:
        if not c['seen']:
            n+=1
    return n

def newChallenge(word, hint, player):
    sent_at = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    conn = get_db_connection()
    conn.execute("INSERT INTO challenges (username, word, hint, player, sent_at, accepted, seen, won, complete_at) VALUES (?, ?, ?, ?, ?, 0, 0, 0, NULL)",
                 (session['username'], word, hint, player, sent_at))
    conn.commit()
    conn.close()

# # Websocket for notifications - Just for connection purpose
# @socketio.on('my event')
# def handle_message(data):
#     print('received message: ' + data['data'])

# # Websocket for sending notification
# @socketio.on('message')
# def handle_message(message):
#     print(f'Received message: {message}')
#     send(message, broadcast=True)


# Route for the signup page
@app.route('/signup', methods=['GET', 'POST'])
def signup():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        confirm_password = request.form['confirm_password']

        if password != confirm_password:
            return render_template('signup.html', error_msg="Passwords do not match. Please try again.")

        conn = get_db_connection()
        
        existing_user = conn.execute('SELECT * FROM users WHERE username = ?', (username,)).fetchone()
        
        if existing_user:
            conn.close()
            return render_template('signup.html', error_msg="User already exists. Please choose a different username.")
        
        conn.execute('INSERT INTO users (username, password, score) VALUES (?, ?, ?)',
                     (username, password, 0))
        conn.commit()
        conn.close()

        # Pass a success message to the template
        return render_template('signup.html', success_msg="Account has been created successfully!")

    return render_template('signup.html')




# Route for the login page
@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']

        conn = get_db_connection()
        user = conn.execute('SELECT * FROM users WHERE username = ? AND password = ?', (username, password)).fetchone()
        conn.close()

        if user:
            # Store the username in the session
            session['username'] = username
            return redirect(url_for('home'))
        else:
            # Pass the error message to the template
            return render_template('login.html', msg="Invalid username or password. Please try again.")

    return render_template('login.html')


# Route for the home page
@app.route('/')
def home():
    # Check if username is stored in session, if not redirect to login
    if 'username' in session:
        username = session['username']

        return render_template('index.html', name=username, all_c=get_all_challenges(), n_not=get_all_unread_notifications())
    else:
        return redirect(url_for('login'))

@app.route('/notification-seen')
def notificationSeen():
    conn = get_db_connection()
    conn.execute(f"UPDATE challenges SET seen = 1 WHERE player='{session['username']}'")
    conn.commit()
    conn.close()
    return ''

@app.route('/challenge-won/<id>')
def challenge_won(id):
    completed_at = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    conn = get_db_connection()
    conn.execute(f"UPDATE challenges SET won = 1, complete_at = ? WHERE id = ?", (completed_at, id))
    conn.commit()
    conn.close()
    return ''


@app.route('/accepted-challenge/<id>')
def a_challenge(id):
    completed_at = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    conn = get_db_connection()
    conn.execute(f'UPDATE challenges SET accepted = 1, complete_at = ? where id = ?', (completed_at, id))
    conn.commit()
    conn.close()
    return ''



@app.route('/two-player-challenge', methods=["POST", "GET"])
def twoPlayerChallenge():
    if request.method == 'POST':
        word = request.form.get('word')
        hint = request.form.get('hint')
        player = request.form.get('player')
        
        # Remove sent_at from the arguments passed to newChallenge
        newChallenge(word, hint, player)
        flash("Challenge sent Successfully!")
        return redirect(url_for('home'))
    
    conn = get_db_connection()
    users = conn.execute('SELECT username FROM users')
    return render_template('new-challenge.html', name=session['username'], users=users, all_c=get_all_challenges(), n_not=get_all_unread_notifications())



@app.route('/get-challenge/<id>')
def getChallenge(id):
    conn = get_db_connection()
    word = conn.execute(f'SELECT * FROM challenges where id = {id}').fetchone()
    return word

def get_leaderboard_data():
    connection = sqlite3.connect('database.db')
    cursor = connection.cursor()
    cursor.execute("SELECT username, score FROM users ORDER BY score DESC")
    leaderboard_data = cursor.fetchall()
    connection.close()
    return leaderboard_data

@app.route('/leader_board')
def leader_board():
    # Fetch leaderboard data
    leaderboard_data = get_leaderboard_data()
    
    # Check if username is stored in session, if not redirect to login
    if 'username' in session:
        username = session['username']
        return render_template('leader_board.html', users=leaderboard_data, name=username, all_c=get_all_challenges(), n_not=get_all_unread_notifications())
    else:
        return redirect(url_for('login'))
    

# Route for logging out
@app.route('/logout')
def logout():
    session.pop('username', None)
    return redirect(url_for('login'))

@app.route('/users')
def show_users():
    conn = get_db_connection()
    users = conn.execute('SELECT * FROM users').fetchall()
    conn.close()
    return render_template('users.html', users=users)


@app.route('/increment_score', methods=['POST'])
def increment_score():
    conn = get_db_connection()
    score = conn.execute('SELECT score FROM users WHERE username = ?', (session['username'],)).fetchone()[0]
    data = request.json
    if data['difficulty'] == 'competitive':
        score += 1
        conn.execute('UPDATE users SET score = ? WHERE username = ?', (score, session['username']))
        conn.commit()
        score = conn.execute('SELECT score FROM users WHERE username = ?', (session['username'],)).fetchone()[0]
        print(score)
        print("UPDATED")

    return 'Score updated'


@app.route('/decrement_score', methods=['POST'])
def decrement_score():
    conn = get_db_connection()
    score = conn.execute('SELECT score FROM users WHERE username = ?', (session['username'],)).fetchone()[0]
    data = request.json
    if data['difficulty'] == 'competitive':
        score = max(0,score-1)
        conn.execute('UPDATE users SET score = ? WHERE username = ?', (score, session['username']))
        conn.commit()
        score = conn.execute('SELECT score FROM users WHERE username = ?', (session['username'],)).fetchone()[0]
        print(score)
        print("UPDATED")

    return 'Score updated'


if __name__ == '__main__':
    socketio.run(app, debug=True, port=5005)
    # app.run(debug=True, port=5005)