{
 "nbformat": 4,
 "nbformat_minor": 5,
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python"
  },
  "colab": {
   "name": "QAPU_DB_ChatBot.ipynb"
  }
 },
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "# QAPU Veritabanı Sohbet Botu (Colab)\nDoğal dilde soru sorun → bot soruyu **SQL'e çevirir**, veritabanında **korumalı** şekilde çalıştırır\nve cevabı **Türkçe özetler**. Gradio sohbet arayüzü ile.\n\n**Güvenlik tasarımı**\n* Kimlik bilgileri koda yazılmaz — çalışma anında gizli olarak sorulur (`getpass`).\n* Bot **yalnızca SELECT** çalıştırabilir: SQL ayrıştırıcı (sqlglot) ile INSERT/UPDATE/DELETE/DDL,\n  çoklu ifade ve tehlikeli fonksiyonlar engellenir; bağlantı **salt-okunur** modda açılır,\n  otomatik `LIMIT 500` ve 20 sn zaman aşımı uygulanır.\n* Yine de en iyi uygulama, veritabanında salt-okunur bir kullanıcı açmaktır:\n```sql\nCREATE ROLE qapu_bot LOGIN PASSWORD '<güçlü şifre>';\nGRANT CONNECT ON DATABASE qapu TO qapu_bot;\nGRANT USAGE ON SCHEMA public TO qapu_bot;\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO qapu_bot;\nALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO qapu_bot;\n```\n\n**LLM arka ucu** (5. hücrede seçilir): `cerebras` (hızlı, ücretsiz kota) · `gemini` (Colab ile uyumlu)\n· `openai` uyumlu herhangi bir uç (şirket içi vLLM dahil). API anahtarı da gizli sorulur.\n\n> Not: Soru metni ve şema bilgisi seçtiğiniz LLM servisine gider; **sorgu sonuçları** yalnızca\n> cevap özetleme adımında LLM'e gösterilir. Veri gizliliği kritikse `openai` arka ucuyla\n> şirket içi bir model kullanın.\n"
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": "# 1) Kurulum\n!pip -q install gradio psycopg2-binary sqlglot cerebras_cloud_sdk google-generativeai openai\nprint(\"Hazır.\")"
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": "%%writefile qapu_chatbot.py\n# -*- coding: utf-8 -*-\n\"\"\"\nQAPU Veritabanı Sohbet Botu — çekirdek\n=====================================\nDoğal dildeki soruyu LLM ile SQL'e çevirir, korumalı şekilde (yalnızca SELECT,\notomatik LIMIT, zaman aşımı, salt-okunur işlem) PostgreSQL'de çalıştırır ve\nsonucu Türkçe özetler. LLM arka ucu takılabilir (Cerebras / Gemini / OpenAI\nuyumlu / yerel model); veritabanı kimlik bilgileri koda yazılmaz.\n\"\"\"\nimport re, json, decimal, datetime\nimport sqlglot\nfrom sqlglot import exp\n\nMAX_ROWS = 500          # otomatik LIMIT\nSTMT_TIMEOUT_MS = 20000 # sorgu zaman aşımı\nMAX_RETRY = 2           # hatalı SQL'de LLM'e yeniden deneme hakkı\n\n\n# ---------------------------------------------------------------- SQL GUARD\nclass GuardError(Exception):\n    pass\n\n\ndef guard_sql(sql: str) -> str:\n    \"\"\"Yalnızca tek bir SELECT/WITH ifadesine izin verir; LIMIT yoksa ekler.\"\"\"\n    sql = sql.strip().rstrip(\";\").strip()\n    if not sql:\n        raise GuardError(\"Boş SQL\")\n    try:\n        stmts = sqlglot.parse(sql, read=\"postgres\")\n    except Exception as e:\n        raise GuardError(f\"SQL ayrıştırılamadı: {e}\")\n    if len(stmts) != 1:\n        raise GuardError(\"Tek bir sorguya izin var\")\n    tree = stmts[0]\n    root = tree\n    while isinstance(root, exp.With):\n        root = root.this\n    if not isinstance(root, (exp.Select, exp.Union, exp.Intersect, exp.Except)):\n        raise GuardError(\"Yalnızca SELECT sorgularına izin var\")\n    # yazma / DDL / tehlikeli düğüm taraması\n    banned = (exp.Insert, exp.Update, exp.Delete, exp.Drop, exp.Create,\n              exp.Alter, exp.Merge, exp.Command, exp.Grant, exp.TruncateTable)\n    for node in tree.walk():\n        if isinstance(node, banned):\n            raise GuardError(f\"İzin verilmeyen ifade: {type(node).__name__}\")\n        if isinstance(node, exp.Anonymous) and node.name and \\\n           node.name.lower() in {\"pg_sleep\", \"dblink\", \"pg_read_file\", \"lo_import\", \"lo_export\"}:\n            raise GuardError(f\"İzin verilmeyen fonksiyon: {node.name}\")\n    # LIMIT enjeksiyonu (en dış seviyeye)\n    if not tree.args.get(\"limit\") and not (isinstance(root, exp.Select) and root.args.get(\"limit\")):\n        sql = f\"SELECT * FROM ({sql}) AS _guarded LIMIT {MAX_ROWS}\"\n    return sql\n\n\n# ------------------------------------------------------------------ VERİTABANI\nclass DB:\n    def __init__(self, host, port, dbname, user, password, sslmode=\"prefer\"):\n        import psycopg2\n        self.conn = psycopg2.connect(host=host, port=port, dbname=dbname,\n                                     user=user, password=password,\n                                     sslmode=sslmode,\n                                     options=f\"-c statement_timeout={STMT_TIMEOUT_MS} \"\n                                             f\"-c default_transaction_read_only=on\")\n        self.conn.autocommit = True\n\n    def run(self, sql):\n        \"\"\"Guard'dan geçmiş SQL'i çalıştırır; (kolonlar, satırlar) döner.\"\"\"\n        with self.conn.cursor() as cur:\n            cur.execute(sql)\n            cols = [d[0] for d in cur.description] if cur.description else []\n            rows = cur.fetchall() if cur.description else []\n        return cols, rows\n\n    # ---- şema tanıtımı ----\n    def schema_card(self, schemas=(\"public\",), max_cols=40):\n        \"\"\"information_schema'dan kompakt şema kartı üretir (partisyonlar hariç).\"\"\"\n        q = \"\"\"\n        SELECT c.table_name, c.column_name, c.data_type\n        FROM information_schema.columns c\n        JOIN information_schema.tables t\n          ON t.table_schema = c.table_schema AND t.table_name = c.table_name\n        WHERE c.table_schema = ANY(%s) AND t.table_type = 'BASE TABLE'\n        ORDER BY c.table_name, c.ordinal_position\"\"\"\n        with self.conn.cursor() as cur:\n            cur.execute(q, (list(schemas),))\n            rows = cur.fetchall()\n        tables = {}\n        for tname, col, typ in rows:\n            if re.search(r\"_(\\d{4}_\\d{2}|default)$\", tname):\n                continue  # partisyonları gizle — ana tablo yeterli\n            tables.setdefault(tname, []).append(f\"{col} {typ}\")\n        lines = []\n        for t, cols in sorted(tables.items()):\n            shown = cols[:max_cols]\n            extra = f\" …(+{len(cols)-max_cols} kolon)\" if len(cols) > max_cols else \"\"\n            lines.append(f\"- {t}({', '.join(shown)}){extra}\")\n        return \"\\n\".join(lines)\n\n    def data_dictionary(self):\n        \"\"\"variables tablosundan Türkçe veri sözlüğü (kolon kodu → anlam/birim).\"\"\"\n        try:\n            cols, rows = self.run(\n                \"SELECT v.id, v.description, COALESCE(v.unit,''), s.name \"\n                \"FROM variables v JOIN variable_segments s ON s.id = v.segment_id \"\n                \"ORDER BY s.name, v.id LIMIT 400\")\n        except Exception:\n            return \"\"\n        return \"\\n\".join(f\"- {r[0]}: {r[1]}\" + (f\" [{r[2]}]\" if r[2] else \"\")\n                         for r in rows)\n\n\n# ------------------------------------------------------------------- PROMPT\nDOMAIN_NOTES = \"\"\"\\\nAlan bilgisi (QAPU tarımsal sulama / enerji izleme veritabanı):\n- Ölçüm tabloları cihaz×zaman serisidir; ana tablolar: measurements_energy (ae_*: aktif enerji Wh,\n  re_l_*: endüktif reaktif, re_g_*: kapasitif reaktif — faz başına, örnekleme aralığı ~5 dk,\n  değerler aralık enerjisidir, kümülatif DEĞİLDİR), measurements_power (p/q/s_*: W/var/VA,\n  pf_*: güç faktörü, fq: frekans Hz), measurements_voltage (vrms_*, vfund_*, vharm_*),\n  measurements_current (irms_*, ifund_*, iharm_*, ipeak_*), measurements_environment (pcb_t, pcb_h),\n  measurements_device (batarya b_*, GSM rssi/mcc/mnc).\n- _r/_s/_t son ekleri üç fazı belirtir. Cihaz kimliği: device_id (varchar) → devices.id;\n  cihaz adı devices.name. Zaman kolonu: device_time (timestamptz).\n- Reaktif oran = SUM(re_l_r+re_l_s+re_l_t) / NULLIF(SUM(ae_r+ae_s+ae_t),0).\n  EPDK sınırları: endüktif %20, kapasitif %15. is_valid = true filtresi kullan.\n- Negatif enerji değerleri hatalıdır; GREATEST(x,0) ile ele.\n- Tarih filtrelerinde device_time kullan (partisyon budaması için).\"\"\"\n\nFEWSHOTS = [\n    (\"Kaç cihaz var?\",\n     \"SELECT COUNT(*) AS cihaz_sayisi FROM devices\"),\n    (\"Son 7 günde en çok enerji tüketen 5 tesis hangileri?\",\n     \"SELECT d.name, ROUND(SUM(GREATEST(e.ae_r,0)+GREATEST(e.ae_s,0)+GREATEST(e.ae_t,0))/1000.0, 1) AS kwh\\n\"\n     \"FROM measurements_energy e JOIN devices d ON d.id = e.device_id\\n\"\n     \"WHERE e.is_valid AND e.device_time >= now() - interval '7 days'\\n\"\n     \"GROUP BY d.name ORDER BY kwh DESC LIMIT 5\"),\n    (\"Hangi cihazlar son 30 günde endüktif reaktif sınırını aştı?\",\n     \"SELECT d.name,\\n\"\n     \"  ROUND(100.0*SUM(GREATEST(e.re_l_r,0)+GREATEST(e.re_l_s,0)+GREATEST(e.re_l_t,0))\\n\"\n     \"        /NULLIF(SUM(GREATEST(e.ae_r,0)+GREATEST(e.ae_s,0)+GREATEST(e.ae_t,0)),0), 1) AS enduktif_oran_yuzde\\n\"\n     \"FROM measurements_energy e JOIN devices d ON d.id = e.device_id\\n\"\n     \"WHERE e.is_valid AND e.device_time >= now() - interval '30 days'\\n\"\n     \"GROUP BY d.name\\n\"\n     \"HAVING SUM(GREATEST(e.re_l_r,0)+GREATEST(e.re_l_s,0)+GREATEST(e.re_l_t,0))\\n\"\n     \"     > 0.20*SUM(GREATEST(e.ae_r,0)+GREATEST(e.ae_s,0)+GREATEST(e.ae_t,0))\\n\"\n     \"ORDER BY enduktif_oran_yuzde DESC\"),\n]\n\n\ndef build_system_prompt(schema_card, data_dict):\n    fs = \"\\n\\n\".join(f\"Soru: {q}\\nSQL:\\n{s}\" for q, s in FEWSHOTS)\n    return f\"\"\"Sen QAPU enerji izleme veritabanı için bir SQL asistanısın.\nGörevin: kullanıcının Türkçe sorusunu TEK bir PostgreSQL SELECT sorgusuna çevirmek.\n\nKURALLAR:\n1. YALNIZCA SELECT yazabilirsin (INSERT/UPDATE/DELETE/DDL yasak, zaten engellenir).\n2. Cevabında yalnızca ```sql ... ``` bloğu olsun, açıklama yazma.\n3. Sonuç satır sayısını makul tut (gerekirse LIMIT; zaten {MAX_ROWS} satırda kesilir).\n4. Bilinmeyen kolon uydurma; yalnızca şemadaki tablo ve kolonları kullan.\n5. Soru belirsizse en makul yorumu seç; cevap aşamasında varsayımını belirtirsin.\n\nŞEMA:\n{schema_card}\n\n{DOMAIN_NOTES}\n\nVERİ SÖZLÜĞÜ (variables kataloğu — ölçüm kolonlarının anlamı):\n{data_dict}\n\nÖRNEKLER:\n{fs}\"\"\"\n\n\nANSWER_PROMPT = \"\"\"Kullanıcının sorusu: {question}\n\nÇalıştırılan SQL:\n{sql}\n\nSonuç ({nrows} satır{truncated}):\n{result}\n\nBu sonucu kullanıcıya Türkçe, kısa ve net biçimde açıkla. Sayıları okunur yaz\n(binlik ayraç, en fazla 1-2 ondalık). Sonuç boşsa bunun ne anlama gelebileceğini söyle.\nTeknik SQL detayı anlatma; yöneticiye rapor verir gibi yaz. Varsayım yaptıysan tek cümleyle belirt.\"\"\"\n\n\n# -------------------------------------------------------------------- AGENT\ndef _extract_sql(text):\n    m = re.search(r\"```sql\\s*(.*?)```\", text, re.S | re.I)\n    if m:\n        return m.group(1).strip()\n    m = re.search(r\"```\\s*(SELECT.*?)```\", text, re.S | re.I)\n    if m:\n        return m.group(1).strip()\n    m = re.search(r\"(WITH\\b.*|SELECT\\b.*)\", text, re.S | re.I)\n    if m:\n        return m.group(1).strip()\n    raise GuardError(\"Yanıtta SQL bulunamadı\")\n\n\ndef _fmt_cell(v):\n    if isinstance(v, decimal.Decimal):\n        v = float(v)\n    if isinstance(v, float):\n        return f\"{v:,.2f}\".rstrip(\"0\").rstrip(\".\")\n    if isinstance(v, (datetime.datetime, datetime.date)):\n        return str(v)\n    return str(v)\n\n\ndef rows_to_markdown(cols, rows, max_show=30):\n    if not cols:\n        return \"(sonuç yok)\"\n    head = \"| \" + \" | \".join(cols) + \" |\"\n    sep = \"|\" + \"---|\" * len(cols)\n    body = [\"| \" + \" | \".join(_fmt_cell(v) for v in r) + \" |\" for r in rows[:max_show]]\n    return \"\\n\".join([head, sep] + body)\n\n\nclass ChatAgent:\n    \"\"\"backend: callable(messages: list[{'role','content'}]) -> str\"\"\"\n\n    def __init__(self, db: DB, backend, system_prompt=None):\n        self.db = db\n        self.backend = backend\n        self.system = system_prompt or build_system_prompt(\n            db.schema_card(), db.data_dictionary())\n        self.history = []  # (soru, sql, cevap)\n\n    def ask(self, question, verbose=False):\n        msgs = [{\"role\": \"system\", \"content\": self.system}]\n        for q, s, _ in self.history[-3:]:  # kısa bağlam: son 3 soru\n            msgs.append({\"role\": \"user\", \"content\": q})\n            msgs.append({\"role\": \"assistant\", \"content\": f\"```sql\\n{s}\\n```\"})\n        msgs.append({\"role\": \"user\", \"content\": question})\n\n        sql, cols, rows, err = None, [], [], None\n        for attempt in range(MAX_RETRY + 1):\n            out = self.backend(msgs)\n            try:\n                sql = _extract_sql(out)\n                safe = guard_sql(sql)\n                cols, rows = self.db.run(safe)\n                err = None\n                break\n            except Exception as e:\n                err = str(e)\n                if verbose:\n                    print(f\"[deneme {attempt+1}] hata: {err}\")\n                msgs.append({\"role\": \"assistant\", \"content\": out})\n                msgs.append({\"role\": \"user\",\n                             \"content\": f\"Bu SQL hata verdi: {err}\\nLütfen düzeltilmiş tek bir SELECT yaz.\"})\n        if err:\n            return {\"answer\": f\"Üzgünüm, sorguyu çalıştıramadım: {err}\",\n                    \"sql\": sql, \"table_md\": \"\", \"rows\": []}\n\n        table_md = rows_to_markdown(cols, rows)\n        trunc = f\", ilk 30 gösteriliyor\" if len(rows) > 30 else \"\"\n        ans_msgs = [{\"role\": \"user\", \"content\": ANSWER_PROMPT.format(\n            question=question, sql=sql, nrows=len(rows), truncated=trunc,\n            result=table_md)}]\n        answer = self.backend(ans_msgs).strip()\n        self.history.append((question, sql, answer))\n        return {\"answer\": answer, \"sql\": sql, \"table_md\": table_md,\n                \"rows\": rows, \"cols\": cols}\n"
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": "# 3) Veritabanı bağlantısı — bilgiler gizli sorulur, koda yazılmaz\nfrom getpass import getpass\nimport qapu_chatbot as cb\n\nDB_HOST = input(\"Sunucu (host): \").strip()\nDB_PORT = input(\"Port [5432]: \").strip() or \"5432\"\nDB_NAME = input(\"Veritabanı adı [qapu]: \").strip() or \"qapu\"\nDB_USER = input(\"Kullanıcı: \").strip()\nDB_PASS = getpass(\"Şifre: \")\n\ndb = cb.DB(DB_HOST, int(DB_PORT), DB_NAME, DB_USER, DB_PASS)\ncols, rows = db.run(\"SELECT current_database(), version()\")\nprint(\"Bağlandı:\", rows[0][0], \"·\", rows[0][1].split(\",\")[0])\n\nSCHEMA = db.schema_card()\nSOZLUK = db.data_dictionary()\nprint(f\"Şema kartı: {len(SCHEMA.splitlines())} tablo · Veri sözlüğü: {len(SOZLUK.splitlines())} değişken\")"
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": "# 4) LLM arka ucu seçimi\nfrom getpass import getpass\n\nBACKEND = \"cerebras\"   # \"cerebras\" | \"gemini\" | \"openai\"\n\ndef make_backend(kind):\n    if kind == \"cerebras\":\n        from cerebras.cloud.sdk import Cerebras\n        key = getpass(\"Cerebras API anahtarı: \")\n        client = Cerebras(api_key=key)\n        model = \"llama-3.3-70b\"\n        def call(messages):\n            r = client.chat.completions.create(model=model, messages=messages,\n                                               temperature=0, max_tokens=1200)\n            return r.choices[0].message.content\n        return call\n\n    if kind == \"gemini\":\n        import google.generativeai as genai\n        key = getpass(\"Gemini API anahtarı: \")\n        genai.configure(api_key=key)\n        def call(messages):\n            system = \"\\n\".join(m[\"content\"] for m in messages if m[\"role\"] == \"system\") or None\n            model = genai.GenerativeModel(\"gemini-2.0-flash\", system_instruction=system)\n            hist = [{\"role\": \"user\" if m[\"role\"] == \"user\" else \"model\", \"parts\": [m[\"content\"]]}\n                    for m in messages if m[\"role\"] != \"system\"]\n            return model.generate_content(hist).text\n        return call\n\n    if kind == \"openai\":   # OpenAI uyumlu her uç: şirket içi vLLM, Groq, vb.\n        from openai import OpenAI\n        base = input(\"Base URL (ör. http://sunucu:8000/v1): \").strip()\n        key = getpass(\"API anahtarı (yoksa boş): \") or \"EMPTY\"\n        model = input(\"Model adı: \").strip()\n        client = OpenAI(api_key=key, base_url=base)\n        def call(messages):\n            r = client.chat.completions.create(model=model, messages=messages,\n                                               temperature=0, max_tokens=1200)\n            return r.choices[0].message.content\n        return call\n\n    raise ValueError(kind)\n\nbackend = make_backend(BACKEND)\nprint(\"LLM arka ucu hazır:\", BACKEND)"
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": "# 5) Ajanı oluştur ve hızlı test\nagent = cb.ChatAgent(db, backend)\n\nr = agent.ask(\"Kaç cihaz kayıtlı?\")\nprint(r[\"answer\"])\nprint(\"\\nSQL:\", r[\"sql\"])"
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": "# 6) Gradio sohbet arayüzü\nimport gradio as gr\n\nORNEKLER = [\n    \"Son 7 günde en çok enerji tüketen 5 tesis hangileri?\",\n    \"Hangi cihazlar son 30 günde endüktif reaktif sınırını (%20) aştı?\",\n    \"Meram 2 cihazının günlük ortalama güç faktörü son 2 haftada nasıl değişti?\",\n    \"Dün hiç veri göndermeyen cihazlar var mı?\",\n    \"Faz dengesizliği en yüksek 5 cihazı listele\",\n]\n\ndef chat_fn(message, history):\n    r = agent.ask(message)\n    out = r[\"answer\"]\n    if r.get(\"sql\"):\n        out += f\"\\n\\n<details><summary>🔍 Çalıştırılan SQL</summary>\\n\\n```sql\\n{r['sql']}\\n```\\n\\n</details>\"\n    if r.get(\"table_md\"):\n        out += \"\\n\\n\" + r[\"table_md\"]\n    return out\n\ndemo = gr.ChatInterface(\n    chat_fn,\n    title=\"QAPU Veritabanı Asistanı\",\n    description=\"Enerji izleme veritabanına Türkçe soru sorun. Bot yalnızca okuma (SELECT) yapabilir.\",\n    examples=ORNEKLER,\n)\ndemo.launch(debug=False, share=True)  # share=True: telefon/başka bilgisayardan erişilebilir geçici link"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "---\n### Notlar\n* `share=True` 72 saatlik geçici bir `*.gradio.live` linki üretir — kurum içi test için pratiktir;\n  kalıcı kullanım için botu bir sunucuda çalıştırmak gerekir (aynı kod, Colab'a bağımlı değildir).\n* Bot her soruda en fazla 2 kez kendi SQL hatasını düzeltmeyi dener; yine olmazsa hatayı açıkça söyler.\n* Şema kartı partisyon tablolarını gizler (ör. `measurements_energy_2026_10` yerine ana tablo),\n  `variables` kataloğu Türkçe veri sözlüğü olarak prompt'a gömülür — isabeti belirgin artırır.\n* Eşik/kural eklemek için `qapu_chatbot.py` içindeki `DOMAIN_NOTES` ve `FEWSHOTS` düzenlenebilir.\n"
  }
 ]
}