from flask import Flask, request, jsonify, render_template
import mysql.connector

app = Flask(__name__)

def employee_data():
    # Connect to MySQL database
    conn = mysql.connector.connect(
      host='db',  # Use the IP address or hostname of the MySQL container
      port='3306',  # MySQL port
      user='root',  # MySQL username
      password='root_password',  # MySQL password
      database='employees'  # Name of the database
    )
    
    # Create a cursor object to execute SQL commands
    cursor = conn.cursor()

    cursor.execute('SELECT Employee_Name, Title FROM employee_data')
    results = cursor.fetchall()
    cursor.close()
    conn.close()
    return results

# Endpoint to add a new entry to the database
@app.route('/add', methods=['POST'])
def add_entry():
    # Connect to MySQL database
    conn = mysql.connector.connect(
      host='db',  # Use the IP address or hostname of the MySQL container
      port='3306',  # MySQL port
      user='root',  # MySQL username
      password='root_password',  # MySQL password
      database='employees'  # Name of the database
    )
    
    # Create a cursor object to execute SQL commands
    cursor = conn.cursor()
    
    # Get data from request
    data = request.get_json()
    employee_name = data.get('employee_name')
    title = data.get('title')

    # SQL query to insert data into the employee_data table
    sql_query = "INSERT INTO employee_data (Employee_Name, Title) VALUES (%s, %s)"
    
    try:
        # Execute the SQL query
        cursor.execute(sql_query, (employee_name, title))
        # Commit the transaction
        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({'message': 'Entry added successfully'}), 200
    except Exception as e:
        # Rollback in case of any error
        conn.rollback()
        cursor.close()
        conn.close()
        return jsonify({'error': str(e)}), 500

# Endpoint to list all entries in the database
@app.route('/entries', methods=['GET'])
def get_entries():
    # Connect to MySQL database
    conn = mysql.connector.connect(
      host='db',  # Use the IP address or hostname of the MySQL container
      port='3306',  # MySQL port
      user='root',  # MySQL username
      password='root_password',  # MySQL password
      database='employees'  # Name of the database
    )
    
    # Create a cursor object to execute SQL commands
    cursor = conn.cursor()
    # SQL query to select all entries from the employee_data table
    sql_query = "SELECT * FROM employee_data"
    
    # Execute the SQL query
    cursor.execute(sql_query)
    # Fetch all entries
    entries = cursor.fetchall()
    
    # Convert entries to JSON format
    entries_json = [{'Employee_Name': entry[0], 'Title': entry[1]} for entry in entries]
    cursor.close()
    conn.close()
    return jsonify(entries_json), 200

@app.route('/')
def index():
    #return jsonify({'Employee Data': employee_data()})
    entries = employee_data()
    return render_template('index.html', entries=entries)
    #return "Hello world"

if __name__ == '__main__':
    #app.run(debug = True, host='0.0.0.0')
    app.run()