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.dkscikruiwdmrsocggkr:76UaJhlvbM9Q0HXP@aws-1-eu-central-1.pooler.supabase.com:5432/postgres"
                  

# --- سكيمة قاعدة البيانات ---
DB_SCHEMA = """
CREATE TABLE public.section (
  id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  name_en text,
  name_ar text,
  image_url text,
  CONSTRAINT section_pkey PRIMARY KEY (id)
);

CREATE TABLE public.category (
  id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  section_id bigint NOT NULL,
  name_en text,
  name_ar text,
  image_url text,
  CONSTRAINT category_pkey PRIMARY KEY (id),
  CONSTRAINT category_section_id_fkey FOREIGN KEY (section_id) REFERENCES public.section(id)
);

CREATE TABLE public.offer (
  id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  image_url text,
  CONSTRAINT offer_pkey PRIMARY KEY (id)
);

CREATE TABLE public.product (
  id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  category_id bigint,
  name_en text,
  name_ar text,
  description text,
  quantity bigint,
  price double precision,
  CONSTRAINT product_pkey PRIMARY KEY (id),
  CONSTRAINT product_category_id_fkey FOREIGN KEY (category_id) REFERENCES public.category(id)
);

CREATE TABLE public.product_image (
  id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  product_id bigint,
  image_url text,
  CONSTRAINT product_image_pkey PRIMARY KEY (id),
  CONSTRAINT product_image_product_id_fkey FOREIGN KEY (product_id) REFERENCES public.product(id)
);

CREATE TABLE public.promotion (
  id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  name_en text,
  name_ar text,
  description text,
  discount_rate double precision,
  start_date timestamp without time zone,
  end_date timestamp without time zone,
  CONSTRAINT promotion_pkey PRIMARY KEY (id)
);

CREATE TABLE public.product_promotion (
  id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  product_id bigint,
  promotion_id bigint,
  CONSTRAINT product_promotion_pkey PRIMARY KEY (id),
  CONSTRAINT product_promotion_product_id_fkey FOREIGN KEY (product_id) REFERENCES public.product(id),
  CONSTRAINT product_promotion_promotion_id_fkey FOREIGN KEY (promotion_id) REFERENCES public.promotion(id)
);
"""

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

# --- توليد SQL باستخدام Gemini ---
def generate_sql_gemini(natural_language_query):
    prompt = f"""You are a PostgreSQL expert for an e-commerce database.
Your job is to convert a natural language query into a SQL SELECT statement, based on the following database schema.

Database Schema:
{DB_SCHEMA}

Database Structure Description:

1. **section** (الأقسام الرئيسية)
   - تخزن الأقسام الرئيسية للمتجر
   - تحتوي على اسم بالإنجليزية والعربية وصورة
   - كل قسم يحتوي على عدة فئات

2. **category** (الفئات)
   - تخزن الفئات التابعة للأقسام
   - مرتبطة بالقسم الرئيسي عبر section_id
   - تحتوي على اسم بالإنجليزية والعربية وصورة

3. **product** (المنتجات)
   - تخزن معلومات المنتجات الأساسية
   - مرتبطة بالفئة عبر category_id
   - تحتوي على: الاسم، الوصف، الكمية، السعر

4. **product_image** (صور المنتجات)
   - تخزن الصور المتعددة لكل منتج
   - مرتبطة بالمنتج عبر product_id
   - كل منتج يمكن أن يحتوي على عدة صور

5. **promotion** (العروض والتخفيضات)
   - تخزن معلومات العروض والتخفيضات
   - تحتوي على: نسبة الخصم، تاريخ البداية، تاريخ النهاية

6. **product_promotion** (ربط المنتجات بالعروض)
   - جدول وسيط يربط المنتجات بالعروض
   - علاقة many-to-many بين المنتجات والعروض

7. **offer** (العروض الخاصة)
   - تخسن العروض الإعلانية الخاصة
   - تحتوي principalmente على صورة العرض

Important Relationships:
- category.section_id = section.id
- product.category_id = category.id  
- product_image.product_id = product.id
- product_promotion.product_id = product.id
- product_promotion.promotion_id = promotion.id

Query Generation Rules:
- Use ONLY SELECT statements - no INSERT, UPDATE, DELETE
- Use proper JOIN syntax with table aliases for readability
- Always use the exact table and column names from the schema
- Include relevant WHERE clauses for filtering when appropriate
- Use ORDER BY for sorting when relevant
- Use LIMIT for limiting results when appropriate
- Calculate discounted prices when promotions are involved: (price * (1 - discount_rate/100))
- Handle date filters for active promotions using CURRENT_DATE
- The output must contain ONLY the SQL query, no explanations or comments
- Always end with a semicolon

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:
        return jsonify({"error": "Please send 'text' in the request body"}), 400

    natural_query = data['text']
    print(f"Natural query: {natural_query}")
    
    sql_query = generate_sql_gemini(natural_query)

    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>E-Commerce Natural Language to SQL API</h1>
    <p>Use <code>/api/query</code> with POST {"text": "your question"}.</p>
    <p>Examples:</p>
    <ul>
        <li>"عرض جميع المنتجات في قسم الإلكترونيات"</li>
        <li>"أعرض المنتجات ذات التخفيضات النشطة"</li>
        <li="أعرض أحدث 10 منتجات"</li>
        <li>"أعرض الفئات التابعة للقسم الغذائي"</li>
    </ul>
    """

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