app.py

from flask import Flask, render_template, jsonify
from flask_socketio import SocketIO
import sqlite3
import os
import time
from threading import Thread
from datetime import datetime
from watchdog.observers import Observer
from watchdog.events import FileSystemEventHandler
from db_utils import connect_to_db

app = Flask(__name__)
app.config['SECRET_KEY'] = 'secret!'
socketio = SocketIO(app, cors_allowed_origins="*")

# File paths
DB_DIR = r"C:\Users\HP PAVILLION\Desktop\NE\pms_embedded"
DB_PATH = os.path.join(DB_DIR, "plates.db")

# System stats
system_stats = {
    "total_vehicles": 0,
    "paid_vehicles": 0,
    "pending_payments": 0,
    "total_revenue": 0
}

def update_system_stats():
    """Update system statistics based on database."""
    try:
        conn = connect_to_db()
        if not conn:
            print("[ERROR] Failed to connect to database for stats", flush=True)
            return
        cursor = conn.cursor()
        
        # Count vehicles and payment status
        cursor.execute("SELECT DISTINCT plate_number FROM plates_logs")
        vehicles = set(row[0] for row in cursor.fetchall())
        
        cursor.execute("SELECT COUNT(*) FROM plates_logs WHERE payment_status = 1")
        paid = cursor.fetchone()[0]
        
        cursor.execute("SELECT COUNT(*) FROM plates_logs WHERE payment_status = 0")
        pending = cursor.fetchone()[0]
        
        # Calculate revenue from amount column
        cursor.execute("SELECT SUM(amount) FROM plates_logs WHERE amount IS NOT NULL")
        revenue = cursor.fetchone()[0] or 0
        
        conn.close()
        
        # Update stats
        system_stats["total_vehicles"] = len(vehicles)
        system_stats["paid_vehicles"] = paid
        system_stats["pending_payments"] = pending
        system_stats["total_revenue"] = revenue
        
        socketio.emit('stats_update', system_stats)
        print(f"[DEBUG] Updated stats: {system_stats}", flush=True)
    except Exception as e:
        print(f"[ERROR] Error updating stats: {e}", flush=True)

class DatabaseHandler(FileSystemEventHandler):
    def on_modified(self, event):
        if not event.is_directory and event.src_path.endswith('plates.db'):
            print("[DEBUG] Database modified", flush=True)
            update_system_stats()
            try:
                conn = connect_to_db()
                if conn:
                    cursor = conn.cursor()
                    # Emit latest activity log
                    cursor.execute("SELECT module, timestamp, message FROM activity_logs ORDER BY id DESC LIMIT 1")
                    row = cursor.fetchone()
                    if row:
                        socketio.emit('new_plate_detection', {
                            'module': row[0].lower(),
                            'timestamp': row[1],
                            'message': row[2]
                        })
                        print(f"[DEBUG] Emitted plate detection: {row[0]} | {row[1]} | {row[2]}", flush=True)
                    # Emit latest alert
                    cursor.execute("SELECT plate_number, timestamp FROM alerts ORDER BY id DESC LIMIT 1")
                    row_alert = cursor.fetchone()
                    if row_alert:
                        socketio.emit('new_alert', {
                            'plate_number': row_alert[0],
                            'timestamp': row_alert[1]
                        })
                        print(f"[DEBUG] Emitted alert: {row_alert[0]} | {row_alert[1]}", flush=True)
                    # Emit latest transaction
                    cursor.execute("SELECT timestamp, plate_number, status, old_balance, new_balance FROM transactions ORDER BY id DESC LIMIT 1")
                    row_transaction = cursor.fetchone()
                    if row_transaction:
                        log_entry = f"{row_transaction[0]} - {row_transaction[1]} - Status: {row_transaction[2]}, Old Balance: {row_transaction[3]}, New Balance: {row_transaction[4]}"
                        socketio.emit('new_transaction', {
                            'log': log_entry,
                            'type': 'payment',
                            'stats': system_stats
                        })
                        print(f"[DEBUG] Emitted transaction: {log_entry}", flush=True)
                    conn.close()
            except Exception as e:
                print(f"[ERROR] Error emitting events: {e}", flush=True)

def watch_logs():
    """Monitor database for changes."""
    observer = Observer()
    observer.schedule(DatabaseHandler(), path=os.path.dirname(DB_PATH), recursive=False)
    observer.start()
    print("[DEBUG] Started database watcher", flush=True)
    
    try:
        while True:
            time.sleep(1)
    except KeyboardInterrupt:
        observer.stop()
    observer.join()

@app.route('/')
def index():
    update_system_stats()
    return render_template('index.html', stats=system_stats)

@app.route('/logs')
def get_logs():
    logs = []
    try:
        conn = connect_to_db()
        if conn:
            cursor = conn.cursor()
            cursor.execute("SELECT plate_number, payment_status, timestamp, payment_timestamp, amount FROM plates_logs ORDER BY timestamp DESC")
            logs = [
                {
                    'Plate Number': row[0],
                    'Payment Status': 'Paid' if row[1] == 1 else 'Pending',
                    'Timestamp': row[2],
                    'Payment Timestamp': row[3] if row[3] else '',
                    'Amount': row[4] if row[4] is not None else ''
                }
                for row in cursor.fetchall()]
            conn.close()
            print(f"[DEBUG] Fetched {len(logs)} detection logs", flush=True)
    except Exception as e:
        print(f"[ERROR] Error fetching logs: {e}", flush=True)
    return jsonify(logs)

@app.route('/activity_logs')
def get_activity_logs():
    logs = []
    try:
        conn = connect_to_db()
        if conn:
            cursor = conn.cursor()
            cursor.execute("SELECT module, timestamp, message FROM activity_logs ORDER BY timestamp DESC LIMIT 50")
            logs = [
                {
                    'module': row[0].lower(),
                    'timestamp': row[1],
                    'message': row[2]
                }
                for row in cursor.fetchall()]
            conn.close()
            print(f"[DEBUG] Fetched {len(logs)} activity logs", flush=True)
    except Exception as e:
        print(f"[ERROR] Error fetching activity logs: {e}", flush=True)
    return jsonify(logs)

@app.route('/alerts')
def get_alerts():
    alerts = []
    try:
        conn = connect_to_db()
        if conn:
            cursor = conn.cursor()
            cursor.execute("SELECT plate_number, timestamp FROM alerts ORDER BY timestamp DESC")
            alerts = [
                {
                    'plate_number': row[0],
                    'timestamp': row[1]
                }
                for row in cursor.fetchall()]
            conn.close()
            print(f"[DEBUG] Fetched {len(alerts)} alerts", flush=True)
    except Exception as e:
        print(f"[ERROR] Error fetching alerts: {e}", flush=True)
    return jsonify(alerts)

@app.route('/transactions')
def get_transactions():
    transactions = []
    try:
        conn = connect_to_db()
        if conn:
            cursor = conn.cursor()
            cursor.execute("SELECT timestamp, plate_number, status, old_balance, new_balance FROM transactions ORDER BY timestamp DESC LIMIT 50")
            transactions = [
                f"{row[0]} - {row[1]} - Status: {row[2]}, Old Balance: {row[3]}, New Balance: {row[4]}"
                for row in cursor.fetchall()]
            conn.close()
            print(f"[DEBUG] Fetched {len(transactions)} transactions", flush=True)
    except Exception as e:
        print(f"[ERROR] Error fetching transactions: {e}", flush=True)
    return jsonify(transactions)

@app.route('/stats')
def get_stats():
    update_system_stats()
    return jsonify(system_stats)

@socketio.on('connect')
def on_connect():
    update_system_stats()
    socketio.emit('stats_update', system_stats)
    print("[DEBUG] Client connected", flush=True)

if __name__ == '__main__':
    Thread(target=watch_logs, daemon=True).start()
    socketio.run(app, debug=True, host='0.0.0.0', port=5000)