pip install sqlite3 pytz langchain openai

import sqlite3
import pytz
from langchain.tools import tool

# Konfigurasi ⚙️
wita = pytz.timezone("Asia/Makassar")
CURRENT_USER = "Ogi"  # Pengguna default

def setup_database():
    conn = sqlite3.connect("database.db")
    cursor = conn.cursor()

    # Tabel Pengguna 👥
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS users (
            name TEXT PRIMARY KEY,
            email TEXT
        )""")

    # Tabel Pengeluaran dengan foreign key pengguna 💰
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS expenses (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user TEXT,
            description TEXT,
            amount REAL,
            date TEXT,
            FOREIGN KEY(user) REFERENCES users(name)
        )""")

    # Inisialisasi pengguna default 👤
    cursor.execute("INSERT OR IGNORE INTO users (name, email) VALUES (?, ?)", 
                 (CURRENT_USER, "contoh@email.com"))
    conn.commit()
    conn.close()

@tool
def execute_sql(query: str):
    """Eksekusi SQL dengan validasi dan keamanan ketat 🔒"""
    try:
        conn = sqlite3.connect("database.db")
        cursor = conn.cursor()

        # Pemeriksaan keamanan 🚫
        forbidden_patterns = [
            "DROP TABLE", "CREATE TABLE", 
            "INSERT INTO users", "UPDATE users",
            ";--", "/*", "*/"
        ]

        if any(patt in query.upper() for patt in forbidden_patterns):
            return "🚫 Operasi dilarang!"

        # Filter otomatis berdasarkan pengguna 👤
        if "WHERE" in query.upper() and "expenses" in query.upper():
            query += f" AND user='{CURRENT_USER}'"
        elif "WHERE" not in query.upper() and "expenses" in query.upper():
            query += f" WHERE user='{CURRENT_USER}'"

        cursor.execute(query)
        conn.commit()

        # Format hasil 📊
        if query.strip().upper().startswith("SELECT"):
            cols = [desc[0] for desc in cursor.description]
            rows = cursor.fetchall()
            return format_results(cols, rows) if rows else "📭 Data tidak ditemukan"

        elif query.strip().upper().startswith(("UPDATE", "DELETE")):
            return f"✅ {cursor.rowcount} data terpengaruh"

        return "✅ Operasi berhasil"

    except sqlite3.Error as e:
        return f"🚨 Error: {str(e)}"
    finally:
        conn.close()

from langchain.chat_models import ChatOpenAI
from langchain.agents import initialize_agent, AgentType

SYSTEM_TEMPLATE = """Anda adalah asisten SQL expert dengan kemampuan:
1. 📅 Format Tanggal: YYYY-MM-DD HH:MM:SS (Zona waktu WITA)
2. 👤 Pengguna saat ini: {user}
3. ✨ Contoh Query Valid:
   - UPDATE: UPDATE expenses SET description='Makan', amount=25000 WHERE id=3
   - DELETE: DELETE FROM expenses WHERE id=2 AND user='{user}'
   - DATE: SELECT * FROM expenses WHERE date BETWEEN '2024-05-30 00:00:00' AND '2024-05-30 23:59:59'
4. ⏰ Untuk operasi tanggal, SELALU gunakan BETWEEN dengan rentang waktu
5. 🎯 Langsung jalankan query SQL sesuai pertanyaan pengguna"""

llm = ChatOpenAI(
    model="gpt-4o-mini",
    temperature=0.7,
    openai_api_key="your-api-key"
)

agent = initialize_agent(
    tools=[execute_sql],
    llm=llm,
    agent=AgentType.OPENAI_FUNCTIONS,
    agent_kwargs={
        'system_message': SYSTEM_TEMPLATE.format(user=CURRENT_USER)
    }
)

def get_date_range(days_ago: int):
    """Buat rentang tanggal untuk filter ⏰"""
    today = datetime.datetime.now(wita)
    target_date = today - datetime.timedelta(days=days_ago)
    start = target_date.replace(hour=0, minute=0, second=0)
    end = target_date.replace(hour=23, minute=59, second=59)
    return (
        start.strftime("%Y-%m-%d %H:%M:%S"),
        end.strftime("%Y-%m-%d %H:%M:%S")
    )

print("💻 ** Sistem Manajemen Keuangan Ogi **")
while True:
    try:
        user_input = input("\n📝 Anda: ").strip()
        if user_input.lower() in ["exit", "keluar"]:
            break

        # Penanganan query spesifik tanggal 📅
        if "kemarin" in user_input.lower():
            start, end = get_date_range(1)
            user_input += f" (rentang: {start} sampai {end})"

        response = agent.run(user_input)
        print(f"\n🤖 Bot: {response}")

    except Exception as e:
        print(f"\n🤖 Bot: 💥 Error sistem: {str(e)}")

Anda: Tambah pengeluaran makan siang hari ini, jumlah 50000
Bot: ✅ Pengeluaran berhasil ditambahkan

Anda: Tampilkan pengeluaran saya kemarin
Bot: id | deskripsi  | jumlah | tanggal
    ----------------------------------
    1  | makan siang | 50000  | 2024-01-29 12:30:00

Anda: Update pengeluaran id 1 menjadi 45000
Bot: ✅ 1 data terpengaruh