# File: database.py (Versi Final untuk Supabase & Hugging Face)

import os
from dotenv import load_dotenv
import pandas as pd
from sqlalchemy import create_engine, MetaData, text

# -----------------------------------------------------------------------------
# LANGKAH 1: SETUP KONEKSI DAN METADATA
# -----------------------------------------------------------------------------

# Muat variabel dari file .env (penting untuk pengembangan lokal)
load_dotenv()
dotenv_path = os.path.join(os.path.dirname(__file__), '.env')
print(f"Mencari file .env di lokasi: {dotenv_path}")

if os.path.exists(dotenv_path):
    print("File .env DITEMUKAN!")
    load_dotenv(dotenv_path=dotenv_path)
    print("Isi DATABASE_URL setelah load:", os.getenv("DATABASE_URL"))
else:
    print("PERINGATAN: File .env TIDAK DITEMUKAN di lokasi tersebut.")

# Ambil URL koneksi dari environment variables (atau Hugging Face Secrets)
DATABASE_URL = os.getenv("DATABASE_URL")
if not DATABASE_URL:
    raise ValueError("DATABASE_URL tidak ditemukan. Pastikan sudah di-set di environment/secrets.")

# Buat engine koneksi ke database PostgreSQL
# echo=False bagus untuk produksi agar tidak menampilkan semua query di log
engine = create_engine(DATABASE_URL, echo=False)

# Buat objek metadata
metadata = MetaData()

# -----------------------------------------------------------------------------
# LANGKAH 2: REFLEKSI TABEL DARI DATABASE
# -----------------------------------------------------------------------------
# Daripada mendefinisikan tabel manual, kita 'mencerminkan' skema
# yang SUDAH ADA di database Supabase. Ini lebih aman dan fleksibel.
try:
    print("Mencoba melakukan refleksi tabel dari database...")
    metadata.reflect(bind=engine)
    
    # Ambil referensi ke tabel yang sudah ada
    users = metadata.tables['users']
    data_penyakit = metadata.tables['data_penyakit']
    detail_penyakit = metadata.tables['detail_penyakit']
    print("Refleksi tabel berhasil.")

except Exception as e:
    print(f"Error saat melakukan refleksi tabel: {e}")
    print("Pastikan nama tabel (users, data_penyakit, detail_penyakit) sudah benar dan ada di database Supabase.")
    # Set ke None jika gagal agar aplikasi tidak crash saat di-import
    users, data_penyakit, detail_penyakit = None, None, None

# -----------------------------------------------------------------------------
# LANGKAH 3: FUNGSI-FUNGSI HELPER (JIKA ADA)
# -----------------------------------------------------------------------------
# Contoh: Fungsi untuk mengambil data (versi yang lebih aman)
def ambil_data_penyakit(puskesmas, tahun):
    """
    Mengambil data penyakit dari database berdasarkan filter dengan cara yang aman
    untuk mencegah SQL Injection.
    """
    # Gunakan text() dan parameter binding (:nama_param) untuk keamanan
    query = text("""
        SELECT * FROM detail_penyakit
        WHERE kode_pusk = :pusk AND tahun = :thn
    """)
    
    # Koneksi dibuka dan ditutup secara otomatis dengan 'with'
    with engine.connect() as conn:
        df = pd.read_sql(query, conn, params={"pusk": puskesmas, "thn": tahun})

    # Logika kategori tetap sama
    def tentukan_kategori(jenis):
        if not isinstance(jenis, str): return 'Tidak Menular'
        menular_keywords = ['Infeksi', 'Demam', 'Diare', 'TBC', 'HIV']
        return 'Menular' if any(keyword.lower() in jenis.lower() for keyword in menular_keywords) else 'Tidak Menular'

    if not df.empty:
        df['kategori'] = df['jenis_penyakit'].apply(tentukan_kategori)
        
    return df

# ==============================================================================
# SCRIPT UNTUK SETUP AWAL (TIDAK PERLU DIJALANKAN LAGI SETELAH TABEL DIBUAT)
# ==============================================================================
# Kode di bawah ini HANYA untuk membuat tabel di awal jika kamu mau.
# Tapi karena kita sudah buat tabel di UI Supabase, blok ini sebenarnya
# tidak perlu dijalankan. Saya tetap sertakan sebagai referensi.
def create_tables_initial_setup():
    print("PERINGATAN: Fungsi ini akan mencoba membuat tabel. Seharusnya tidak diperlukan jika tabel sudah dibuat di Supabase.")
    # Definisi manual tabel (hanya untuk fungsi ini)
    # ... (salin-tempel definisi Table(...) dari file SQLite-mu ke sini) ...
    # ...
    # Lalu panggil metadata.create_all(engine)
    pass

# Jalankan 'python database.py' di terminal untuk memverifikasi koneksi
if __name__ == "__main__":
    print("Memverifikasi koneksi dan refleksi tabel...")
    try:
        # 1. Verifikasi koneksi
        with engine.connect() as connection:
            print("=> Koneksi ke database Supabase BERHASIL!")

        # 2. Verifikasi hasil refleksi tabel
        #    Cek apakah nama tabel ada di dalam kamus metadata.tables
        tabel_ditemukan = {
            'users': 'users' in metadata.tables,
            'data_penyakit': 'data_penyakit' in metadata.tables,
            'detail_penyakit': 'detail_penyakit' in metadata.tables
        }

        semua_ditemukan = all(tabel_ditemukan.values())

        if semua_ditemukan:
            print("=> Refleksi tabel BERHASIL: Semua tabel (users, data_penyakit, detail_penyakit) ditemukan.")
        else:
            print("=> PERINGATAN: Tidak semua tabel ditemukan!")
            for nama_tabel, status in tabel_ditemukan.items():
                print(f"   - Tabel '{nama_tabel}': {'Ditemukan' if status else 'TIDAK DITEMUKAN'}")
            print("   Pastikan nama tabel sudah benar sesuai di database Supabase.")

    except Exception as e:
        print(f"Verifikasi GAGAL: {e}")