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")))