app.py

import os
import re
import psycopg2
from flask import Flask, request, jsonify
import google.generativeai as genai
from flask import Response
import json

# --- إعدادات Flask ---
app = Flask(__name__)

# --- إعدادات Gemini ---
GEMINI_API_KEY = "AIzaSyCWukRy76nPgkrMflCTWh_s4gEU--wSVr8"  # يفضل استخدام متغيرات البيئة
genai.configure(api_key=GEMINI_API_KEY)
model = genai.GenerativeModel('gemini-2.0-flash')

# --- إعدادات Supabase ---
SUPABASE_DB_URL = "postgresql://postgres.mougnkvoyyhcuxeeqvmh:Xf5E0DhUvKEHEAqq@aws-0-eu-central-1.pooler.supabase.com:6543/postgres"

# --- سكيمة قاعدة البيانات ---
DB_SCHEMA = """

CREATE TABLE public.profiles (

  id uuid NOT NULL,

  updated_at timestamp with time zone,

  username text UNIQUE CHECK (char_length(username) >= 3),

  full_name text,

  avatar_url text,

  website text,

  cam_mac text UNIQUE,

  fcm_token text,

  notification_enabled boolean DEFAULT true,

  CONSTRAINT profiles_pkey PRIMARY KEY (id),

  CONSTRAINT profiles_id_fkey FOREIGN KEY (id) REFERENCES auth.users(id)

);



CREATE TABLE public.place (

  id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,

  created_at timestamp with time zone DEFAULT (now() AT TIME ZONE 'utc'::text),

  name text,

  CONSTRAINT place_pkey PRIMARY KEY (id)

);



CREATE TABLE public.user_place (

  id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,

  created_at timestamp with time zone NOT NULL DEFAULT now(),

  place_id bigint,

  user_cam_mac text,

  CONSTRAINT user_place_pkey PRIMARY KEY (id),

  CONSTRAINT user_place_place_id_fkey FOREIGN KEY (place_id) REFERENCES public.place(id),

  CONSTRAINT user_place_user_cam_mac_fkey FOREIGN KEY (user_cam_mac) REFERENCES public.profiles(cam_mac)

);



CREATE TABLE public.data (

  id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,

  created_at timestamp without time zone,

  caption text,

  image_url text,

  latitude double precision DEFAULT '36.1833854'::double precision,

  longitude double precision DEFAULT '37.1309255'::double precision,

  user_place_id bigint,

  cam_mac text,

  CONSTRAINT data_pkey PRIMARY KEY (id),

  CONSTRAINT data_user_place_id_fkey FOREIGN KEY (user_place_id) REFERENCES public.user_place(id)

);



CREATE TABLE public.biodata (

  id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,

  created_at timestamp with time zone NOT NULL DEFAULT now(),

  mac_address text,

  acceleration_x double precision,

  acceleration_y double precision,

  acceleration_z double precision,

  gyro_x double precision,

  gyro_y double precision,

  gyro_z double precision,

  temperature double precision,

  CONSTRAINT biodata_pkey PRIMARY KEY (id),

  CONSTRAINT biodata_mac_address_fkey FOREIGN KEY (mac_address) REFERENCES public.profiles(cam_mac)

);



CREATE TABLE public.notification (

  id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,

  created_at timestamp without time zone NOT NULL DEFAULT now(),

  user_cam_mac text,

  title text,

  message text,

  is_read boolean,

  acceleration_x double precision,

  acceleration_y double precision,

  acceleration_z double precision,

  gyro_x double precision,

  gyro_y double precision,

  gyro_z double precision,

  CONSTRAINT notification_pkey PRIMARY KEY (id),

  CONSTRAINT notification_user_cam_mac_fkey FOREIGN KEY (user_cam_mac) REFERENCES public.profiles(cam_mac)

);



CREATE TABLE public.flag (

  id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,

  flag smallint,

  user_mac_address text,

  CONSTRAINT flag_pkey PRIMARY KEY (id),

  CONSTRAINT flag_user_mac_address_fkey FOREIGN KEY (user_mac_address) REFERENCES public.profiles(cam_mac)

);



"""

# --- الاتصال بقاعدة البيانات ---
def get_db_connection():
    try:
        return psycopg2.connect(SUPABASE_DB_URL)
    except Exception as err:
        print(f"Database connection error: {err}")
        return None

# --- التحقق من صحة cam_mac ---
def validate_cam_mac(cam_mac):
    conn = get_db_connection()
    if not conn:
        return False
        
    try:
        cursor = conn.cursor()
        cursor.execute("SELECT 1 FROM profiles WHERE cam_mac = %s;", (cam_mac,))
        return cursor.fetchone() is not None
    except Exception as e:
        print(f"Validation error: {e}")
        return False
    finally:
        if conn:
            conn.close()

# --- توليد SQL باستخدام Gemini مع تخصيص حسب cam_mac ---
def generate_sql_gemini(natural_language_query, cam_mac):
    prompt = f"""YYou are a PostgreSQL expert.

Your job is to convert a natural language query into a SQL SELECT statement, based on the following database schema.



The query **must always be filtered by the camera MAC address: '{cam_mac}'**, using the appropriate field.



Schema:

{DB_SCHEMA}



Schema Description:



1. **profiles**

   - Represents users/devices.

   - cam_mac (TEXT, UNIQUE) is the MAC address of the camera device.

   - Linked to most tables using cam_mac.



2. **data**

   - Stores captured image info (image_url, caption, created_at, etc.).

   - Linked via cam_mac and user_place_id.

   - To find places, JOIN with `user_place` → `place`.



3. **biodata**

   - Contains sensor readings (acceleration, gyro, temp).

   - Linked via mac_address to profiles.cam_mac.



4. **notification**

   - Stores alerts/messages for the user.

   - Linked via user_cam_mac to profiles.cam_mac.



5. **flag**

   - Represents boolean flags (e.g. status).

   - Linked via user_mac_address to profiles.cam_mac.



6. **user_place**

   - Connects a user_cam_mac to a place_id.

   - JOIN with `place` to get the name.



7. **place**

   - List of place names.



Rules:

- If the question is about number of visits, frequency, or attendance to a specific place, use the `data` table.

- Use **only SELECT** statements.

- Use only the provided schema.

- Use **camel_mac** filter in WHERE clause.

- Use proper JOINs (no subqueries unless necessary).

- Always match table relationships correctly:

    data.user_place_id = user_place.id

    user_place.place_id = place.id

    user_place.user_cam_mac = profiles.cam_mac

- Use table aliases (like d, p, up, pl) when helpful.

- The output must contain only the SQL query, no comments or explanations.

- Add a semicolon at the end.





Question: "{natural_language_query}"





SQL:"""

    try:
        response = model.generate_content(prompt)
        sql = response.text.strip()

        # تنظيف الناتج
        sql = re.sub(r"^```sql\s*", "", sql, flags=re.IGNORECASE)
        sql = re.sub(r"\s*```$", "", sql)
        sql = re.sub(r"^SQL:\s*", "", sql, flags=re.IGNORECASE)

        if not sql.upper().startswith("SELECT"):
            sql = "SELECT " + sql.split("SELECT")[-1] if "SELECT" in sql else f"SELECT * FROM ({sql}) AS subquery"

        if not sql.endswith(";"):
            sql += ";"

        return sql
    except Exception as e:
        print(f"Gemini error: {e}")
        return None

# --- نقطة النهاية الرئيسية ---
@app.route('/api/query', methods=['POST'])
def handle_query():
    data = request.get_json()
    if not data or 'text' not in data or 'cam_mac' not in data:
        return jsonify({"error": "Please send 'text' and 'cam_mac' in the request body"}), 400

    natural_query = data['text']
    cam_mac = data['cam_mac']
    print(f"Natural query from {cam_mac}: {natural_query}")
    
    # التحقق من صحة cam_mac
    if not validate_cam_mac(cam_mac):
        return jsonify({"error": "Invalid cam_mac address"}), 403
    
    sql_query = generate_sql_gemini(natural_query, cam_mac)

    if not sql_query:
        return jsonify({"error": "Failed to generate SQL query"}), 500

    print(f"Generated SQL: {sql_query}")

    if not sql_query.upper().strip().startswith("SELECT"):
        return jsonify({"error": "Only SELECT queries are allowed"}), 403

    conn = get_db_connection()
    if not conn:
        return jsonify({"error": "Database connection failed"}), 500

    cursor = None
    try:
        cursor = conn.cursor()
        cursor.execute(sql_query)
        columns = [desc[0] for desc in cursor.description]
        rows = cursor.fetchall()
        data = [dict(zip(columns, row)) for row in rows]

        response_data = {
            "data": data,
        }
        
        response_json = json.dumps(response_data, ensure_ascii=False)
        
        return Response(
            response_json,
            status=200,
            mimetype='application/json; charset=utf-8'
        )
        
    except Exception as e:
        print(f"SQL execution error: {e}")
        return jsonify({"error": str(e), "generated_sql": sql_query}), 500
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

@app.route('/')
def home():
    return """

    <h1>Natural Language to SQL API (Gemini)</h1>

    <p>Use <code>/api/query</code> with POST {"text": "your question", "cam_mac": "device_mac_address"}.</p>

    """

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=7860)