db.py

import psycopg2
import os

DB_URL = os.getenv("DB_URL")  # Set this in Hugging Face Secrets

conn = psycopg2.connect(DB_URL)
cur = conn.cursor()

def get_table_status():
    cur.execute("SELECT table_id, status FROM tables")
    return {row[0]: row[1] for row in cur.fetchall()}

def log_customer_visit(face_id, timestamp, table_id):
    cur.execute("INSERT INTO visits (face_id, timestamp, table_id) VALUES (%s, %s, %s)",
                (face_id, timestamp, table_id))
    conn.commit()

def get_alerts():
    cur.execute("SELECT type, trigger_time FROM alerts WHERE status='active'")
    return [{"type": r[0], "time": r[1].isoformat()} for r in cur.fetchall()]