function/prompt/prompt_custom.py

# Thêm thư mục gốc vào sys.path
import os,sys
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), "../..")))
from function.prompt import prompt_selling as  prompt_selling
from langchain_core.prompts.prompt import PromptTemplate


PROMPT_SUFFIX = """Only use the following tables:  
{table_info}  

Question: {input}  
"""


_DEFAULT_TEMPLATE = """
Instructions for Generating SQL Queries in Python (PyMySQL-Compatible)

Purpose:
Given a user input question, generate a syntactically correct SQL query with strict role enforcement, deletion restrictions, and data integrity checks.

====================================================
Role-Based Security & Enforcement (Mandatory)
====================================================
1. **Role Validation**:
   - Before generating any SQL query, validate the user's role and permissions.
   - If the user’s role is unauthorized for the requested operation:
       → Return an error: "ERROR: Unauthorized operation for role."
       → DO NOT generate or execute any SQL query.

2. **Table-Specific Rule Enforcement**:
   - If the requested operation violates specific table rules (e.g., attempt to delete restricted data):
       → Return an error: "ERROR: Operation not permitted on this table."
       → DO NOT generate or execute any SQL query.

====================================================
Deletion Restriction (No DELETE Allowed)
====================================================
- Deletion operations are strictly prohibited.
- Instead of DELETE:
   → Use UPDATE to set:
       `isdeleted = TRUE`,
       `deletion_date = CURRENT_TIMESTAMP`
- Restoration:
   → UPDATE to set:
       `isdeleted = FALSE`,
       `deletion_date = NULL`

====================================================
Data Integrity Requirements
====================================================
3. **Required Fields for INSERT/UPDATE**:
   - Always include:
       `creation_date`, `update_date`, `isdeleted`

4. **Schema Adherence**:
   - Use only explicitly provided column names from schema.
   - Never assume or guess column names.
   - Wrap all table and column names with backticks (`).

5. **SELECT Queries**:
   - Do NOT use `SELECT *`.
   - Specify only the necessary columns.

6. **Formatting & Readability**:
   - Ensure clean, readable formatting for SQL queries.

7. **Invalid Operation Handling**:
   - If an operation is invalid or not allowed:
       → Return: "ERROR: Invalid or unauthorized operation."

8. **ID Columns**:
   - Never show or include the following IDs in the output:
     → product, category, post, orders, shipment, payment IDs.

9. **Current Year**:
   - Always use current year as 2025.

====================================================
Safe UPDATE Query Execution
====================================================
10. **Avoid Subquery Errors**:
    - Never use subqueries in the WHERE clause that return multiple rows.
    - Store result in a variable first, then use it in the UPDATE WHERE clause.

    ✅ Example:
    ```sql
    -- WRONG:
    UPDATE `product_translation`
    SET `pro_name` = 'Pink guava leaf tea'
    WHERE `pro_id` = (SELECT `pro_id` FROM `product` WHERE `pro_name` = 'Trà ổi lá hồng');

    -- CORRECT:
    SET @pro_id_var = (SELECT `pro_id` FROM `product` WHERE `pro_name` = 'Trà ổi lá hồng' LIMIT 1);
    UPDATE `product_translation`
    SET `pro_name` = 'Pink guava leaf tea'
    WHERE `pro_id` = @pro_id_var;
    ```

====================================================
Category Duplication Prevention (CRITICAL)
====================================================
⚠️ Before INSERT or UPDATE on `category` table:
- Prevent duplicate `cate_name` (translated to Vietnamese if needed).

✅ For INSERT:
   1. If language is Vietnamese, check directly in `category`.
   2. If language is English:
       → Translate `cate_name` to Vietnamese.
       → Check for duplicate in `category`.
   3. If duplicate exists:
       → Return error: "DUPLICATE_CATEGORY_NAME"
       → DO NOT execute INSERT.

✅ For UPDATE:
   1. Translate `cate_name` to Vietnamese if needed.
   2. Check if any record (excluding current) has the same `cate_name`.
   3. If duplicate exists:
       → Return error: "DUPLICATE_CATEGORY_NAME"
       → DO NOT execute UPDATE.

🚨 Validation must occur within SQL execution (no pre-checks or separate queries allowed).

====================================================
Output Format for Each Query
====================================================
Question: <Original Input Question>

SQLQuery:
<Generated SQL Query>

SQLResult:
<Result or Error Message>

Answer:
<Plain Language Explanation of Result>
"""

from function.gemini_response import get_table
from function.prompt import personalization
import importlib
import os
import pkgutil
from function.prompt.table import users,review,user_voucher,user_coin,orders_orderitem,cart_cartItem,category_categoryTranslation,product,post_postTranslation,shipment,favourite,absence,payments_shipment_group,group_order_members,group_orders,cart_cartitem_group,shipper_attendance,shipper_commission_detail,shipper_salary_summary
from function.prompt import prompt_detail_table
    
    
async def return_table(query):
    data = await get_table.response_general(query)
    print(data)
    text = ""
    text_schema = ""

    grouped_data = {}

    table_mapping = {
        "user": ["user"],
        "absence": ["absence"],
        "cart_group":["cart_group","cart_item_group"],
        "group_order":["group_orders","payments_group"],
        "group_order_member":["group_order_members"],
        "user_voucher": ["user_voucher"],
        "category": ["category", "category_translation"],
        "cart": ["cart", "cart_item"],
        "orders": ["orders", "order_item", "payment"],
        "favourite": ["favourite", "favourite_item"],
        "post": ["post", "post_translation"],
        "product": ["product", "product_translation"],
        "shipment": ["shipment"],
        "shipment_group": ["shipment_group"],
        "shipper_attendance":["shipper_attendance"],
        "shipper_commission_detail" : ["shipper_commission_detail"],
        "shipper_salary_summary": ["shipper_salary_summary"],
        "review": ["review"],
        "user_coin": ["user_coin"],
    }

    content_mapping = {
        "user": users.prompt_user,
        "user_voucher": user_voucher.prompt_user_voucher,
        "category": category_categoryTranslation.prompt_category_management,
        "cart": cart_cartItem.prompt_cart_management,
        "cart_group": cart_cartitem_group.prompt_cart_group_management ,
        "orders": orders_orderitem.prompt,
        "favourite": favourite.prompt_favourite_management,
        "post": post_postTranslation.prompt_post_management,
        "product": product.prompt_product_management,
        "shipment": shipment.prompt_shipment,
        "review": review.prompt_review,
        "user_coin": user_coin.prompt_user_coint,
        "shipper_salary_summary": shipper_salary_summary.prompt_commission_management,
        "shipper_commission_detail": shipper_commission_detail.prompt_commission_management,
        "shipper_attendance": shipper_attendance.prompt_shipper_attendance_management,
        "shipment_group": payments_shipment_group.prompt_shipment,
        "group_order_member": group_order_members.prompt_shipper_attendance_management,
        "group_order": group_orders.prompt,
        "absence": absence.prompt_absence_management,
             
            
    }

    # Mapping bảng -> hàm lấy schema riêng biệt
    
    schema_mapping = {
        "user": prompt_detail_table.prompt_users,
        "user_voucher": prompt_detail_table.prompt_user_voucher,
        "category": prompt_detail_table.prompt_categort,
        "category_translation": prompt_detail_table.prompt_category_translation,
        "cart": prompt_detail_table.prompt_cart,
        "cart_item": prompt_detail_table.prompt_cart_item,
        "orders":prompt_detail_table.prompt_orders,
        "order_item": prompt_detail_table.prompt_order_item,
        "payment": prompt_detail_table.prompt_payments,
        "favourite": prompt_detail_table.prompt_favourite,
        "favourite_item": prompt_detail_table.prompt_fav_item,
        "post": prompt_detail_table.prompt_post,
        "post_translation": prompt_detail_table.prompt_post_translation,
        "product": prompt_detail_table.prompt_product,
        "product_translation": prompt_detail_table.prompt_product_translation,
        "shipment": prompt_detail_table.prompt_shipment,
        "product_variants": prompt_detail_table.prompt_product_variants,
        "review": prompt_detail_table.prompt_review,
        "user_coin": prompt_detail_table.prompt_user_coin,
        "absence": prompt_detail_table.prompt_absence,
        "cart_group": prompt_detail_table.prompt_cart_group,
        "cart_item_group": prompt_detail_table.prompt_cartitem_group,
        "group_orders": prompt_detail_table.prompt_group_orders,
        "payments_group": prompt_detail_table.prompt_payments_group,
        "group_order_members":prompt_detail_table.prompt_group_orders_member,
        "shipment_group":prompt_detail_table.prompt_shipment_group,
        "shipper_attendance":prompt_detail_table.prompt_shipper_attendance,
        "shipper_commission_detail":prompt_detail_table.prompt_shipper_commission_detail,
        "shipper_salary_summary":prompt_detail_table.prompt_shipper_salary_summary
    }

    # Gom nội dung prompt theo nhóm
    for value in data:
        for key, tables in table_mapping.items():
            if value in tables:
                grouped_data[key] = content_mapping[key]
                break

    # Ghép nội dung prompt
    for idx, (table_name, content) in enumerate(grouped_data.items(), start=1):
        text += f"{idx}. {table_name}:\n{content}\n\n"

    # Lấy schema riêng cho từng bảng
    for idx, table_name in enumerate(data, start=1):
        schema_text = schema_mapping.get(table_name)
        if schema_text:
            text_schema += f"{idx}. Bảng: {table_name}\n{schema_text}\n\n"
        else:
            text_schema += f"{idx}. Bảng: {table_name}\nKhông tìm thấy schema.\n\n"

    return text, text_schema


        
from function.prompt import general_rule 
from function.prompt import prompt_table,personalization
async def get_prompt_custom(query):
    text, text_schema = await return_table(query)
    _ROLE_TEMPLATE = f"""
 **Roles and their associated permissions are central to ensuring secure, organized, and efficient database operations. Pay special attention to role-based rules and table-specific restrictions when constructing SQL queries:      
    {text}. \n
 **General Rule:** {general_rule.prompt_rule}.
 ** Vui lòng xem thêm mô tả các bảng dưới đây để xác định chính xác câu lệnh SQL cần dùng và phải hợp lý: {text_schema}. 
 -  Không dùng % hoặc format() để "nhét" trực tiếp biến vào câu SQL.
 -  **Tuyệt đối Cấm dùng "%%s" để lấy giá trị từ biến**
 - ** Hãy luôn luôn tránh lỗi cú pháp SQL khi trả về cho mình.**
 - "- Tránh các lỗi như :\n"
    "  (1054, \"Unknown column 'oi.pro_id' in 'field list'\")\n . Luôn đảm bảo bạn không bao giờ bị lỗi này"
    "  (1054, \"Unknown column 'oi.note' in 'field list'\") . Luôn đảm bảo bạn không bao giờ bị lỗi này\n"
    "  (1054, \"Unknown column 'oi.size' in 'field list'\") . Luôn đảm bảo bạn không bao giờ bị lỗi này \n"
    "  (1054, \"Unknown column 'c.is_deleted' in 'on clause'\").  Luôn đảm bảo bạn không bao giờ bị lỗi này\n"
Tham khảo thêm prompt cá nhân hóa: 
 - {personalization.prompt}
Tuyệt đối không trả về câu SQL nằm trong [],list điều này bị cấm tuyệt đối
"""

    PROMPT = PromptTemplate(
    input_variables=["input", "table_info", "dialect", "top_k", "role","language"],
    template=_ROLE_TEMPLATE + _DEFAULT_TEMPLATE + PROMPT_SUFFIX
)
    return PROMPT
import asyncio
# print(asyncio.run(return_table("Liệt kê các voucher mà người dùng có user_id là 5 đang sở hữu")))