<?php
/**
 * =========================================================================
 * SheetsAPI.php - UNIVERSAL GOOGLE SHEETS CRUD HELPER
 * =========================================================================
 * Helper ini menggantikan koneksi MySQL / DataStore JSON untuk project
 * yang menggunakan mode Google Sheets (Excel) sebagai database.
 *
 * Data dikirim via HTTP ke Google Apps Script (Web App) yang mengelola
 * Google Spreadsheet sebagai storage.
 *
 * CARA PAKAI:
 *   include 'helper/SheetsAPI.php';
 *   $users = sheetsLoad('users');
 *   sheetsAdd('users', ['nama' => 'Budi', 'email' => 'budi@mail.com']);
 *
 * PRASYARAT:
 *   - File config/sheets_config.php harus ada dan berisi URL Apps Script
 *   - Google Apps Script sudah di-deploy sebagai Web App (Anyone can access)
 *
 * Developer: Sikawarsito, S.Kom — Xieqa Autokoding Engine
 * =========================================================================
 */

// Load konfigurasi URL Apps Script
if (!defined('SHEETS_API_URL')) {
    $sheetsConfigPath = __DIR__ . '/../config/sheets_config.php';
    if (file_exists($sheetsConfigPath)) {
        require_once $sheetsConfigPath;
    }
}

/**
 * Kirim HTTP request ke Google Apps Script
 * @param array $payload Data yang dikirim (akan di-encode ke JSON)
 * @return array|null Response dari Apps Script
 */
function sheetsRequest($payload) {
    if (!defined('SHEETS_API_URL') || SHEETS_API_URL === 'MASUKKAN_URL_APPS_SCRIPT_DI_SINI') {
        return ['status' => 'error', 'message' => 'URL Apps Script belum dikonfigurasi di config/sheets_config.php'];
    }

    $url = SHEETS_API_URL;
    $jsonPayload = json_encode($payload, JSON_UNESCAPED_UNICODE);

    if (function_exists('curl_init')) {
        // Gunakan cURL jika tersedia (lebih andal)
        $ch = curl_init($url);
        curl_setopt_array($ch, [
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_POST           => true,
            CURLOPT_POSTFIELDS     => $jsonPayload,
            CURLOPT_HTTPHEADER     => ['Content-Type: application/json'],
            CURLOPT_FOLLOWLOCATION => true,
            CURLOPT_TIMEOUT        => 15,
            CURLOPT_SSL_VERIFYPEER => false,
        ]);
        $result = curl_exec($ch);
        curl_close($ch);
    } else {
        // Fallback: file_get_contents
        $context = stream_context_create([
            'http' => [
                'method'  => 'POST',
                'header'  => "Content-Type: application/json\r\n",
                'content' => $jsonPayload,
                'timeout' => 15,
            ],
            'ssl' => ['verify_peer' => false]
        ]);
        $result = @file_get_contents($url, false, $context);
    }

    if ($result === false) {
        return ['status' => 'error', 'message' => 'Gagal menghubungi Apps Script. Periksa koneksi internet dan URL.'];
    }

    $decoded = json_decode($result, true);
    if (json_last_error() !== JSON_ERROR_NONE) {
        return ['status' => 'error', 'message' => 'Respons Apps Script tidak valid (bukan JSON).'];
    }

    return $decoded;
}

/**
 * LOAD DATA: Ambil semua record dari sheet
 * @param string $sheet Nama sheet (tab) di Google Spreadsheet
 * @return array Array of records
 */
function sheetsLoad($sheet) {
    $res = sheetsRequest(['action' => 'get', 'sheet' => $sheet]);
    if (!$res || $res['status'] !== 'success') return [];
    return $res['data'] ?? [];
}

/**
 * ADD RECORD: Tambah record baru (auto-increment id)
 * @param string $sheet Nama sheet
 * @param array $data Data record baru (tanpa 'id')
 * @return array|null Record yang ditambahkan (dengan id) atau null jika gagal
 */
function sheetsAdd($sheet, $data) {
    $res = sheetsRequest(['action' => 'add', 'sheet' => $sheet, 'data' => $data]);
    if (!$res || $res['status'] !== 'success') return null;
    return $res['record'] ?? null;
}

/**
 * UPDATE RECORD: Update record berdasarkan id
 * @param string $sheet Nama sheet
 * @param int $id ID record
 * @param array $updates Data yang diupdate
 * @return bool True jika berhasil
 */
function sheetsUpdate($sheet, $id, $updates) {
    $res = sheetsRequest(['action' => 'update', 'sheet' => $sheet, 'id' => $id, 'data' => $updates]);
    return ($res && $res['status'] === 'success');
}

/**
 * DELETE RECORD: Hapus record berdasarkan id
 * @param string $sheet Nama sheet
 * @param int $id ID record
 * @return bool True jika berhasil
 */
function sheetsDelete($sheet, $id) {
    $res = sheetsRequest(['action' => 'delete', 'sheet' => $sheet, 'id' => $id]);
    return ($res && $res['status'] === 'success');
}

/**
 * FIND BY ID: Cari satu record berdasarkan id
 * @param string $sheet Nama sheet
 * @param int $id ID record
 * @return array|null Record atau null
 */
function sheetsFind($sheet, $id) {
    $res = sheetsRequest(['action' => 'find', 'sheet' => $sheet, 'id' => $id]);
    if (!$res || $res['status'] !== 'success') return null;
    return $res['record'] ?? null;
}

/**
 * SEARCH: Cari records berdasarkan field tertentu
 * @param string $sheet Nama sheet
 * @param string $field Nama kolom
 * @param mixed $value Nilai yang dicari
 * @return array Array of matching records
 */
function sheetsSearch($sheet, $field, $value) {
    $all = sheetsLoad($sheet);
    return array_values(array_filter($all, fn($r) => isset($r[$field]) && $r[$field] == $value));
}

/**
 * SEARCH LIKE: Cari records yang mengandung kata kunci (case-insensitive)
 * @param string $sheet Nama sheet
 * @param string $field Nama kolom
 * @param string $keyword Kata kunci
 * @return array Array of matching records
 */
function sheetsSearchLike($sheet, $field, $keyword) {
    $all = sheetsLoad($sheet);
    return array_values(array_filter($all, fn($r) => isset($r[$field]) && stripos($r[$field], $keyword) !== false));
}

/**
 * COUNT: Hitung jumlah record di sheet
 * @param string $sheet Nama sheet
 * @return int Jumlah records
 */
function sheetsCount($sheet) {
    return count(sheetsLoad($sheet));
}

/**
 * PAGINATE: Ambil data dengan pagination (client-side)
 * @param string $sheet Nama sheet
 * @param int $page Halaman ke-
 * @param int $perPage Jumlah per halaman
 * @return array ['records' => [...], 'total' => N, 'page' => N, 'total_pages' => N]
 */
function sheetsPaginate($sheet, $page = 1, $perPage = 10) {
    $all   = sheetsLoad($sheet);
    $total = count($all);
    $totalPages = (int) ceil($total / $perPage);
    $offset = ($page - 1) * $perPage;
    return [
        'records'     => array_slice($all, $offset, $perPage),
        'total'       => $total,
        'page'        => $page,
        'per_page'    => $perPage,
        'total_pages' => $totalPages,
    ];
}

/**
 * LOGIN CHECK: Verifikasi login dari sheet 'users'
 * @param string $username
 * @param string $password
 * @return array|null User data jika valid, null jika gagal
 */
function sheetsLogin($username, $password) {
    $res = sheetsRequest(['action' => 'login', 'username' => $username, 'password' => $password]);
    if (!$res || $res['status'] !== 'success') return null;
    return $res['user'] ?? null;
}
?>
