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)