from flask import Flask, jsonify, request
from flask_cors import CORS
import mysql.connector
from mysql.connector import Error
from datetime import datetime

app = Flask(__name__)
CORS(app, resources={r"/api/*": {"origins": "*"}})  # Enable CORS for all routes

# Database connection function
def create_db_connection():
    try:
        print("Attempting to connect to database...")
        connection = mysql.connector.connect(
            host='157.173.210.15',
            user='root',
            password='techn0l0vMasterAdm1n',
            database='TestAgent1'
        )
        print("Database connection successful!")
        return connection
    except Error as e:
        print(f"Error connecting to MySQL Database: {e}")
        return None

# Route to get all requests 
@app.route('/api/requests', methods=['GET'])
def get_all_requests():
    try:
        connection = create_db_connection()
        if connection:
            cursor = connection.cursor(dictionary=True)
            cursor.execute( """
                SELECT BUID, RequestID, ToDept, FromRoomNo, RequestDescp, BotResponse,
                    Status, RequestCreatedAt, LastUpdatedAt, AttendedBy, GuestName, Notes 
                FROM t_Requests
                """)
        
            data = cursor.fetchall()
            cursor.close()
            connection.close()
        
            response = jsonify({"status": "success", "data": data})
            response.headers.add("Access-Control-Allow-Origin", "*")
            return response
        else:
            return jsonify({"status": "error", "message": "Database connection failed"}), 500
    except Error as e:
        return jsonify({"status": "error", "message": str(e)}), 500


# Route 2: Filter requests by department
@app.route('/api/requests/department/<string:department>', methods=['GET'])
def get_requests_by_department(department):
    try:
        connection = create_db_connection()
        if connection:
            cursor = connection.cursor(dictionary=True)
            
            cursor.execute("""
                SELECT BUID, RequestID, ToDept, FromRoomNo, RequestDescp, BotResponse,
                       Status, RequestCreatedAt, LastUpdatedAt, AttendedBy, GuestName, Notes
                FROM t_Requests WHERE ToDept = %s
            """, (department,))
            
            data = cursor.fetchall()
            cursor.close()
            connection.close()
            
            response =  jsonify({"status": "success", "data": data})
            response.headers.add("Access-Control-Allow-Origin", "*")
            return response
        else:
            return jsonify({"status": "error", "message": "Database connection failed"}), 500
    except Error as e:
        return jsonify({"status": "error", "message": str(e)}), 500

# Route 3: Update an existing request
@app.route('/api/requests/<string:request_id>', methods=['PUT'])
def update_request(request_id):
    try:
        data = request.json
        
        # Validate request data
        if not data:
            return jsonify({"status": "error", "message": "No data provided"}), 400
            
        connection = create_db_connection()
        if connection:
            cursor = connection.cursor()
            
            # Check if record exists
            cursor.execute("SELECT RequestID FROM t_Requests WHERE RequestID = %s", (request_id,))
            if not cursor.fetchone():
                cursor.close()
                connection.close()
                return jsonify({"status": "error", "message": "Request not found"}), 404
            
            # Update only the fields that are provided
            update_fields = []
            values = []
            
            fields = [
                'BUID', 'ToDept', 'FromRoomNo', 'RequestDescp', 'BotResponse',
                'Status', 'AttendedBy', 'GuestName' , 'Notes'
            ]
            
            for field in fields:
                if field in data:
                    update_fields.append(f"{field} = %s")
                    values.append(data[field])
            
            # Always update LastUpdatedAt
            update_fields.append("LastUpdatedAt = %s")
            values.append(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
            
            # Add RequestID at the end for WHERE clause
            values.append(request_id)
            
            if update_fields:
                query = f"UPDATE t_Requests SET {', '.join(update_fields)} WHERE RequestID = %s"
                cursor.execute(query, values)
                connection.commit()
            
            cursor.close()
            connection.close()
            
            return jsonify({"status": "success", "message": "Request updated successfully"})
        else:
            return jsonify({"status": "error", "message": "Database connection failed"}), 500
    except Error as e:
        return jsonify({"status": "error", "message": str(e)}), 500
        
@app.route('/api/users', methods=['GET'])
def get_all_users():
    try:
        connection = create_db_connection()
        if connection:
            cursor = connection.cursor(dictionary=True)
            cursor.execute("SELECT * FROM t_Users")
            data = cursor.fetchall()
            print(f"Data from t_Users: {data}")  # Add logging
            cursor.close()
            connection.close()
            response = jsonify({"status": "success", "data": data})
            response.headers.add("Access-Control-Allow-Origin", "*")
            return response
        else:
            return jsonify({"status": "error", "message": "Database connection failed"}), 500
    except Error as e:
        return jsonify({"status": "error", "message": str(e)}), 500        

if __name__ == '__main__':
    app.run(host='157.173.210.15', port=8080 ,debug=True) #When run locally