manjapro_v6/whapi_webhook/ai/ResponAI_Helper.php

<?php
// include_once __DIR__ . "/../../config/connect.php";

class ResponAI_Helper {

    private $pdo;
    
    // Terima objek PDO di konstruktor
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }
    
    public function db_connect() {
        // Tidak perlu lagi global $pdo;
        return $this->pdo;
    }
    
    public function cek_role($sender) {
        // Cek di pelanggan
        $SQLPel = "SELECT 
            id AS id_pelanggan_db,
            nama AS nama,
            jenis_layanan AS jenis_layanan,
            email AS email,
            nik AS nik,
            `status` AS status_pelanggan,
            time_start AS mulai_berlangganan,
            alamat AS alamat,
            nomor_whatsapp AS nomor_whatsapp,
            desa AS desa,
            CONCAT(latitude, ', ', longitude) AS titik_koordinat,
            akun AS akun,
            akun AS id_pelanggan,
            user_hotspot AS user_hotspot,
            ip_address AS ip_address,
            status_koneksi AS status_koneksi,
            nama_server AS nama_server,
            nama_paket AS paket_berlanggan,
            nama_topologi AS nama_odp,
            port_odp AS port_odp,
            nama_marketing AS nama_sales
        FROM v_pelanggan
        WHERE nomor_whatsapp = ? ";
        
        $stmt1 = $this->pdo->prepare($SQLPel);
        $stmt1->execute([$sender]);
        $pelanggan = $stmt1->fetch(PDO::FETCH_ASSOC);
        if ($pelanggan) {
            return [
                "role" => "pelanggan",
                "data" => $pelanggan
            ];
        }
    
        // Cek di user akses
        $stmt2 = $this->pdo->prepare("SELECT * FROM user_akses WHERE nomor_whatsapp = ?");
        $stmt2->execute([$sender]);
        $karyawan = $stmt2->fetch(PDO::FETCH_ASSOC);
        if ($karyawan) {
            return [
                "role" => "karyawan",
                "data" => $karyawan
            ];
        }
    
        // Cek di data_server
        $SQLOwner = "SELECT 
            id AS id,
            nama_server AS nama_server, 
            nama_pemilik AS nama_pemilik, 
            alamat AS alamat, 
            expaired_date AS tanggal_expired_aplikasi,
            whatsapp AS nomor_whatsapp,
            username AS username,
            harga_sewa AS harga_sewa,
            email AS email,
            rekening_nomor AS nomor_rekening,
            rekening_bank AS bank_rekening,
            rekening_status AS status_rekening
            FROM data_server WHERE whatsapp = ?";
        
        $stmt3 = $this->pdo->prepare($SQLOwner);
        $stmt3->execute([$sender]);
        $owner = $stmt3->fetch(PDO::FETCH_ASSOC);
        if ($owner) {
            return [
                "role" => "owner",
                "data" => $owner
            ];
        }
    
        // Default
        return [
            "role" => "umum",
            "data" => null
        ];
    }

    public function get_id_server($apikey) {
        $stmt = $this->pdo->prepare("SELECT id FROM data_server WHERE apikey_whatsapp = ?");
        $stmt->execute([$apikey]);
        $rows = $stmt->fetchAll(PDO::FETCH_COLUMN);

        if (!$rows) {
            return ''; // tidak ada hasil, biar aman tetap string kosong
        }

        // Kalau ada id = 78, override pakai daftar fixed
        if (in_array(78, $rows)) {
            return "3,2,4,5,6,8,9,67,74,75,76";
        }

        // Balikin id dalam format '1,2,3'
        return implode(',', $rows);
    }
    
    // ambil data static sesuai roll akses
    public function get_data_statis($staticQuery) {
        $staticResult = [];
    
        foreach ($staticQuery as $q) {
            // Pastikan query hanya SELECT
            if (stripos(trim($q), 'SELECT') !== 0) {
                throw new Exception("Hanya query SELECT yang diperbolehkan.");
            }
    
            $stmt = $this->pdo->prepare($q);
            $stmt->execute();
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
            // Gabungkan hasil setiap query
            $staticResult[] = [
                "query" => $q,
                "rows" => $rows
            ];
        }
    
        return $staticResult;
    }

    public function process_db_action($jsonAI, array $allowedColumns, $idServers) {
        // Bisa single query (object) atau multi query (array)
        $queries = isset($jsonAI[0]) ? $jsonAI : [$jsonAI];
        $results = [];

        foreach ($queries as $q) {
            $action = strtoupper($q['action'] ?? '');
            $table  = $q['table'] ?? '';
            $columns_to_select = $q['columns'] ?? []; // Tambahkan ini untuk mengambil kolom yang diminta

            if (!$action || !$table) {
                $results[] = ["error" => "Instruksi tidak lengkap"];
                continue;
            }

            if (!array_key_exists($table, $allowedColumns)) {
                $results[] = ["error" => "Tabel tidak valid"];
                continue;
            }

            // Validasi kolom yang diminta
            $valid_columns = [];
            if (!empty($columns_to_select)) {
                foreach ($columns_to_select as $col) {
                    if (in_array($col, $allowedColumns[$table])) {
                        $valid_columns[] = $col;
                    }
                }
                if (empty($valid_columns)) {
                    $results[] = ["error" => "Tidak ada kolom yang valid diminta untuk tabel $table"];
                    continue;
                }
                $select_clause = implode(", ", $valid_columns);
            } else {
                // Jika tidak ada kolom spesifik diminta, default ke semua kolom yang diizinkan
                $select_clause = implode(", ", $allowedColumns[$table]);
            }


            // WHERE filtering
            $where   = $q['where'] ?? [];
            $clauses = [];
            $params  = [];

            foreach ($where as $col => $val) {
                if (!in_array($col, $allowedColumns[$table])) {
                    // Log atau abaikan kolom yang tidak diizinkan dalam klausa WHERE
                    // Untuk keamanan, kita abaikan saja
                    continue;
                }

                if (is_array($val) && isset($val['LIKE'])) {
                    // format: {"col":{"LIKE":"202508%"}}
                    $clauses[] = "$col LIKE ?";
                    $params[]  = $val['LIKE'];
                } else {
                    // default: {"col":"value"}
                    $clauses[] = "$col = ?";
                    $params[]  = $val;
                }
            }

            $whereSQL = $clauses ? "WHERE " . implode(" AND ", $clauses) : "";
            $wherestatis = $whereSQL ? " AND id_data_server IN ($idServers)" 
                                     : "WHERE id_data_server IN ($idServers)";

            if ($action === "COUNT") {
                $sql = "SELECT COUNT(id) as total FROM $table $whereSQL $wherestatis";
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute($params);
                $result = $stmt->fetch(PDO::FETCH_ASSOC);
                $this->log_query($sql, $params, $result);
                $results[] = $result;
                continue;
            }

            if ($action === "GET") {
                // Gunakan $select_clause yang sudah divalidasi
                if ($table == 'profile_paket') {
                    $sql = "SELECT $select_clause FROM $table $whereSQL $wherestatis GROUP BY nama, nama_profile";
                } else {
                    $sql = "SELECT $select_clause FROM $table $whereSQL $wherestatis LIMIT 10"; 
                }
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute($params);
                $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
                $this->log_query($sql, $params, $result);
                $results[] = $result;
                continue;
            }

            $results[] = ["error" => "Aksi tidak diizinkan"];
        }

        // Kalau hanya 1 query, return langsung
        return count($results) === 1 ? $results[0] : $results;
    }

    private function log_query($sql, $params, $result) {
        file_put_contents(
            __DIR__ . "/api_request.log",
            "[" . date('Y-m-d H:i:s') . "] SQL: $sql\nPARAMS: " . json_encode($params) .
            "\nRESULT: " . json_encode($result, JSON_PRETTY_PRINT) . "\n-----------------------------\n",
            FILE_APPEND
        );
    }
}