modules/database.py

import sqlite3
import pandas as pd
import hashlib

DB_NAME = "menuvision.db"

def init_db():
    conn = sqlite3.connect(DB_NAME)
    c = conn.cursor()
    # Table for Restaurants
    c.execute('''CREATE TABLE IF NOT EXISTS restaurants 
                 (id INTEGER PRIMARY KEY, name TEXT, location TEXT, owner TEXT)''')
    
    # Table for Users (Login/Signup)
    c.execute('''CREATE TABLE IF NOT EXISTS users 
                 (email TEXT PRIMARY KEY, password TEXT, role TEXT)''')
    
    conn.commit()
    conn.close()

# --- USER FUNCTIONS ---
def create_user(email, password, role):
    try:
        conn = sqlite3.connect(DB_NAME)
        c = conn.cursor()
        # Hash password for basic security
        hashed_pw = hashlib.sha256(password.encode()).hexdigest()
        c.execute("INSERT INTO users (email, password, role) VALUES (?, ?, ?)", (email, hashed_pw, role))
        conn.commit()
        conn.close()
        return f"✅ Account created for {email} as {role}!"
    except sqlite3.IntegrityError:
        return "❌ Error: User with this email already exists."
    except Exception as e:
        return f"Error: {e}"

def verify_login(email, password):
    conn = sqlite3.connect(DB_NAME)
    c = conn.cursor()
    hashed_pw = hashlib.sha256(password.encode()).hexdigest()
    
    c.execute("SELECT role FROM users WHERE email=? AND password=?", (email, hashed_pw))
    result = c.fetchone()
    conn.close()
    
    if result:
        return f"✅ Login Successful! Welcome back, {result[0]}."
    else:
        return "❌ Invalid email or password."

# --- RESTAURANT FUNCTIONS ---
def add_restaurant(name, location, owner):
    try:
        conn = sqlite3.connect(DB_NAME)
        c = conn.cursor()
        c.execute("INSERT INTO restaurants (name, location, owner) VALUES (?, ?, ?)", (name, location, owner))
        conn.commit()
        conn.close()
        return f"Success: Restaurant '{name}' added!"
    except Exception as e:
        return f"Error: {e}"

def search_restaurants(query):
    conn = sqlite3.connect(DB_NAME)
    sql = f"SELECT name, location, owner FROM restaurants WHERE name LIKE '%{query}%' OR location LIKE '%{query}%'"
    df = pd.read_sql_query(sql, conn)
    conn.close()
    return df