<?php
try {
    $db = new PDO('sqlite:climate.db');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

    echo "Menghubungkan ke database climate.db...\n";

    // 1. Ambil ID lokasi Indonesia
    $stmt = $db->prepare("SELECT id FROM locations WHERE name = 'Indonesia'");
    $stmt->execute();
    $loc = $stmt->fetch();
    $locId = $loc ? $loc['id'] : 1;

    // 2. Daftar variabel
    $variables = [
        'Precipitation',
        'Suhu Udara Rata-rata',
        'Suhu Udara Maksimum',
        'Suhu Udara Minimum',
        'Kelembaban Relatif',
        'Kecepatan Angin',
        'Radiasi Matahari',
        'Suhu Permukaan Laut',
        'Anomali Tinggi Muka Laut',
        'Salinitas Permukaan Laut',
        'pH Permukaan Laut'
    ];

    $varIds = [];
    foreach ($variables as $name) {
        $stmt = $db->prepare("SELECT id FROM variables WHERE name = :name");
        $stmt->execute([':name' => $name]);
        $var = $stmt->fetch();
        if ($var) {
            $varIds[$name] = $var['id'];
        } else {
            $db->prepare("INSERT INTO variables (name) VALUES (:name)")->execute([':name' => $name]);
            $varIds[$name] = $db->lastInsertId();
        }
    }

    // 3. Daftar periode
    $periods = ['2021-2050', '2031-2060', '2041-2070', '2051-2080', '2061-2090', '2071-2100'];
    $periodIds = [];
    foreach ($periods as $range) {
        $stmt = $db->prepare("SELECT id FROM periods WHERE range = :range");
        $stmt->execute([':range' => $range]);
        $per = $stmt->fetch();
        if ($per) {
            $periodIds[$range] = $per['id'];
        } else {
            $db->prepare("INSERT INTO periods (range) VALUES (:range)")->execute([':range' => $range]);
            $periodIds[$range] = $db->lastInsertId();
        }
    }

    // 4. Daftar 60 Google Drive IDs unik yang ditemukan di log
    $driveIds = [
        '1RJaSwWtGr1vCfLGitbOph6EtzXHMvicn', '1BAerInDReE28vEnTjNPjiqivLY-pII4h',
        '17RAaoNK3D_ZF46SSQZ7aCeYKitVeUnBo', '1viEbo21f-ONUzqKTpVFwSQri7Pk4so_v',
        '1AbUFmM8Fx3Zx_56mTb_oM656SXA8twcO', '1Ju2PDNGGSAMbMxWi2bFFYX_IzDchVo6F',
        '1ppI1lOWn7STFt3mA27uxwy3AK4SHmAGJ', '1v-VA64tw19bZkkfvIKOKkCqaEihXKyPe',
        '1Crc8YGKDMbD9xilyoDJmRSX4Bf5cIAuZ', '1kTHFVXF-26wVhzXQHIpIajZFJFvcv8RF',
        '1N2xVdc4ruJM2PchD9HOMVnh1N6qb54mW', '1IeLIfZmR0X3YyvJDltCt8v9Kc4narlVA',
        '19PGf_99lJJLvVKgMif8gyBXNd2OGUAL_', '1GSdtzLlZ-Mwr9SulrOd_wUZR5ZZXYqLF',
        '12BvWz3Ua-5VUwOSjvSEsc1_1YuU_VBT3', '1ZLCfnhALgJPBIe0kPk7fO0ZRO2nmSqoo',
        '1o2hThPvxhrNYqH5cKkVRo0MqAzWl86Ip', '1nn6GPerh0zDSE4JgRkInFhFjdLvATKpO',
        '1xKWOGtblOhP9G7jraQWd2mZCuHFiO5Ok', '16AwnrBQAqNJ78wiz6ksKuX2XjJRsxNxG',
        '1alVxAayYKalrw2paWsJMPcv7XgJ9QU7o', '1EOpTBlMCdK-r8X_bvzFV_fus-7AeX8R7',
        '11Nu9_WrbC27B7f2hS85TzE97YZ6jpTx8', '1Ejw3daYPXG6HPlbrZjwYE-UFC_Jj4mCv',
        '129I-uZ_mN-nX64Q4V64R26UZ5R9iZf8I', '1M8TuNjVOAzP309EaUvTKE_iA70uSa4eA',
        '1fag01DIqo6HiN_MkfYpn2GXb9m9icFRn', '1CYzZUlvatiC4punu8QTv2uQh_tT59jBE',
        '1oR69r8x3oUzyeqG-DudHNn9mjW4rBwIk', '14JxKBx5j-xoRPV5pAIUTG5wiZWYl4BZQ',
        '1lXjMej57rlIqM3KICDxVjDelijCkjTQw', '1JTdrAc_g9f4f3Lt3kNPC_Z-XOY5L6da7',
        '1ShKyfrRa4YJNOxM6wbpY1CPNKdSjzsAX', '1mfbq0GsGSpPkRnLuFle4zbi6ZfbL63tn',
        '1OSH3tjo21QS6cRYxZZuTsKa-A2pYJdtx', '1YP_EX3qTK4DniKkLXgHiTm0XLeHEdPzp',
        '1TdZtRCZx2gcwHvNS5pmfFL76Gl1vJ4Wc', '1fD4r8ws9ob5GDb3GC1a0P-yV9I7-NOB2',
        '1f8e-vt2E8RS9vZjk6wW0NzX2Gm6hxTVo', '1jJFPXe3-k_7Q93w25BnoWNcZoCja9cd9',
        '1pin3CmKhAAw4bdEmyHGcZ6CnRTz5oTCU', '1TKeOJVmldt0Wub4wX1LVrENC0mJdJbhG',
        '1yMzXu8bT6XSfUFT1VrrVgmFm8YRnuBIJ', '1hU5dXLlH6Ypr6ct3meiZyfcaPrkxebem',
        '1z6ApGjieTCwN5B3t_GH2h36zjmWFXUhm', '1fBZfOBJ6s_ttJmJHkp_RieBv6oQ2vTu6',
        '12SMHMolaw-qqCR8u4vbqGat4vYo0kaLD', '1S_vYAkhDY1SGuf88Aj-fJ6fvL92koKVU',
        '1HnTE9k52eDB_pfO9a7yt5J3Hx3Sxu8De', '1_WjSxhpTW4KGYtro5QHgdqdpVU74dOHI',
        '1-yz39obBBmnCe1ryhNADFCi-Mh5VymFT', '1NH1Zhc9wm5R2cMTMKDB5zyvh58KUvOrY',
        '1DfGEnOiaOcC-XJeNJR5RRvjan-otzRDu', '1epDuwq4n_QDgu0c_Ah-73OS35AyjuexO',
        '1QWJFA1VjyBN_iislEC7P3Zqj7dqGYJ93', '1AMK9YZyTf7y29nyTm-abFvKrW8oANXyo',
        '1NnFIxTd3gx0OYy1eUpN0SYFB24BB4G-h', '1aIF3JQXnDfBtnHNakI0jCDhQx3b3BAtz',
        '1EriKgTOs8QkvdJlO_Qee4RBGxoPCbUqB', '1_RB9MVVe4BR70AJOUID7GvjoDOdPUTq0'
    ];

    $totalIds = count($driveIds);
    $idIndex = 0;

    $stmt = $db->prepare("
        INSERT INTO future_climate (
            location_id, variable_id, period_id, description,
            image_stripe_ssp126_url, image_stripe_ssp245_url, image_stripe_ssp370_url, image_stripe_ssp585_url,
            updated_at
        ) 
        VALUES (
            :locId, :varId, :perId, :desc,
            :ssp126, :ssp245, :ssp370, :ssp585,
            CURRENT_TIMESTAMP
        )
        ON CONFLICT(location_id, variable_id, period_id) 
        DO UPDATE SET 
            image_stripe_ssp126_url = EXCLUDED.image_stripe_ssp126_url,
            image_stripe_ssp245_url = EXCLUDED.image_stripe_ssp245_url,
            image_stripe_ssp370_url = EXCLUDED.image_stripe_ssp370_url,
            image_stripe_ssp585_url = EXCLUDED.image_stripe_ssp585_url,
            updated_at = CURRENT_TIMESTAMP
    ");

    $affectedRows = 0;
    foreach ($varIds as $varName => $vId) {
        $pIndex = 0;
        foreach ($periodIds as $pRange => $pId) {
            // Kita alokasikan 4 ID unik untuk skenario SSP1, SSP2, SSP3, SSP5
            // Agar berbeda di setiap variabel dan periode, kita gunakan indeks yang digeser secara unik
            $id1 = $driveIds[$idIndex % $totalIds];
            $idIndex++;
            $id2 = $driveIds[$idIndex % $totalIds];
            $idIndex++;
            $id3 = $driveIds[$idIndex % $totalIds];
            $idIndex++;
            $id4 = $driveIds[$idIndex % $totalIds];
            $idIndex++;

            $ssp126 = "https://lh3.googleusercontent.com/d/" . $id1;
            $ssp245 = "https://lh3.googleusercontent.com/d/" . $id2;
            $ssp370 = "https://lh3.googleusercontent.com/d/" . $id3;
            $ssp585 = "https://lh3.googleusercontent.com/d/" . $id4;

            $desc = "Proyeksi Climate Stripe untuk variabel {$varName} pada periode {$pRange} di wilayah Indonesia.";
            
            $stmt->execute([
                ':locId' => $locId,
                ':varId' => $vId,
                ':perId' => $pId,
                ':desc' => $desc,
                ':ssp126' => $ssp126,
                ':ssp245' => $ssp245,
                ':ssp370' => $ssp370,
                ':ssp585' => $ssp585
            ]);
            $affectedRows++;
            $pIndex++;
        }
    }

    echo "Sukses! Berhasil mempopulerkan {$affectedRows} entri proyeksi dengan data link gambar unik untuk tiap variabel, skenario, dan periode!\n";

} catch (Exception $e) {
    echo "Terjadi kesalahan: " . $e->getMessage() . "\n";
}
