from flask import Flask, jsonify, render_template
import pymysql

app = Flask(__name__)

# Database config
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': '',
    'database': 'db_traffic2'
}

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/get_video')
def get_video_data():
    try:
        conn = pymysql.connect(**db_config)
        cursor = conn.cursor(pymysql.cursors.DictCursor)

        # Total vehicle log entries
        cursor.execute("SELECT COUNT(*) AS total FROM vehicle_log")
        total = cursor.fetchone()['total']

        # Last 10 vehicle log entries
        cursor.execute("SELECT * FROM vehicle_log ORDER BY id DESC LIMIT 10")
        last_records = cursor.fetchall()

        # Get latest count from total_vehicle table
        cursor.execute("SELECT count FROM total_vehicle ORDER BY id DESC LIMIT 1")
        latest_vehicle_count = cursor.fetchone()
        current_count = latest_vehicle_count['count'] if latest_vehicle_count else 0

        return jsonify({
            'total_entries': total,
            'last_10_entries': last_records,
            'vehicles_in_zone': current_count  # ⬅️ NEW FIELD
        })

    except Exception as e:
        return jsonify({'error': str(e)}), 500

    finally:
        cursor.close()
        conn.close()


if __name__ == '__main__':
    app.run(debug=True, port=5000)