const sqlite3 = require('sqlite3').verbose();
const path = require('path');

// Menentukan lokasi penyimpanan file fisik database baru di dalam root proyek
const db_path = process.env.NODE_ENV === 'production'
    ? '/app/data/database_baru.sqlite'
    : path.resolve(__dirname, '../database_baru.sqlite');

// Menginisialisasi koneksi basis data dengan penanganan kesalahan otomatis
const db = new sqlite3.Database(db_path, sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE, (error_koneksi) => {
    /* Baris kompleks: Flag bitwise 'OPEN_READWRITE | OPEN_CREATE' memaksa sistem untuk 
       membuat file 'database_baru.sqlite' secara otomatis jika belum ada di direktori target, 
       sehingga mencegah aplikasi crash saat inisialisasi awal di server baru. */
    if (error_koneksi) {
        console.error('Kegagalan kritis: Sistem tidak dapat memuat file SQLite.', error_koneksi.message);
        // Praktik keamanan: Langsung menghentikan proses aplikasi jika koneksi database gagal
        process.exit(1);
    }
    console.log('Sistem berhasil terhubung ke file basis data SQLite yang baru.');
});

// Membangun tabel struktur data secara otomatis
const initialize_database = () => {
    db.serialize(() => {
        
        // 1. Membuat tabel MUSTAHIK sebagai entitas utama data warga
        db.run(`CREATE TABLE IF NOT EXISTS MUSTAHIK (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            nik TEXT NOT NULL UNIQUE,
            nama_kk TEXT NOT NULL,
            no_kk TEXT,
            jumlah_tanggungan INTEGER DEFAULT 0,
            lat REAL NOT NULL,
            lng REAL NOT NULL,
            status_validasi TEXT DEFAULT 'Pending'
        )`, (error_tabel) => {
            if (error_tabel) {
                console.error('Gagal mengeksekusi DDL tabel MUSTAHIK:', error_tabel.message);
            }
        });

        // 2. Membuat tabel INDIKATOR_BPS untuk menyimpan parameter kelayakan hasil survei lapangan
        db.run(`CREATE TABLE IF NOT EXISTS INDIKATOR_BPS (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            id_mustahik INTEGER NOT NULL,
            luas_lantai_per_kapita TEXT,
            material_lantai TEXT,
            material_dinding TEXT,
            fasilitas_sanitasi TEXT,
            sumber_air_minum TEXT,
            sumber_penerangan TEXT,
            pekerjaan_utama TEXT,
            pendapatan_bulanan REAL,
            frekuensi_makan TEXT,
            kepemilikan_aset TEXT,
            FOREIGN KEY (id_mustahik) REFERENCES MUSTAHIK(id) ON DELETE CASCADE
        )`, (error_tabel) => {
            /* Baris kompleks: Definisi 'FOREIGN KEY' dengan klausa 'ON DELETE CASCADE' memastikan integritas 
               referensial data terjaga secara otomatis. Jika data warga di tabel MUSTAHIK dihapus oleh admin, 
               maka seluruh baris rekaman kriteria surveinya di tabel INDIKATOR_BPS akan ikut terhapus otomatis 
               oleh engine database untuk mencegah tumpukan data sampah (orphan data). */
            if (error_tabel) {
                console.error('Gagal mengeksekusi DDL tabel INDIKATOR_BPS:', error_tabel.message);
            }
        });

        // 3. Membuat tabel PETUGAS (Kini sejajar di luar callback INDIKATOR_BPS)
        db.run(`CREATE TABLE IF NOT EXISTS PETUGAS (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            password TEXT NOT NULL
        )`, (error_tabel) => {
            if (error_tabel) console.error('Gagal mengeksekusi DDL tabel PETUGAS:', error_tabel.message);
        });

        // 4. Membuat tabel ADMIN (Kini sejajar di luar callback INDIKATOR_BPS)
        db.run(`CREATE TABLE IF NOT EXISTS ADMIN (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            password TEXT NOT NULL
        )`, (error_tabel) => {
            if (error_tabel) console.error('Gagal mengeksekusi DDL tabel ADMIN:', error_tabel.message);
        });

    });
};

// Mengeksekusi pembuatan struktur tabel baru
initialize_database();

module.exports = db;