<?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. Pastikan lokasi Indonesia ada
    $stmt = $db->prepare("SELECT id FROM locations WHERE name = 'Indonesia'");
    $stmt->execute();
    $loc = $stmt->fetch();
    if (!$loc) {
        $db->exec("INSERT INTO locations (name) VALUES ('Indonesia')");
        $locId = $db->lastInsertId();
    } else {
        $locId = $loc['id'];
    }

    // 2. Ambil ID dari variabel utama
    $variables = [
        'Suhu Udara Rata-rata' => 2,
        'Suhu Udara Maksimum' => 3,
        'Suhu Udara Minimum' => 4,
        'Precipitation' => 1
    ];
    
    $varIds = [];
    foreach ($variables as $name => $defaultId) {
        $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. Ambil ID dari periode utama
    $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. Contoh Google Drive Sharing Links (sebelum dinormalisasi)
    // Kita akan menyisipkan tautan Google Drive ini untuk didemonstrasikan.
    $sampleDriveLinks = [
        'SSP1-2.6' => 'https://drive.google.com/file/d/1RJaSwWtGr1vCfLGitbOph6EtzXHMvicn/view?usp=sharing',
        'SSP2-4.5' => 'https://drive.google.com/file/d/1BAerInDReE28vEnTjNPjiqivLY-pII4h/view?usp=sharing',
        'SSP3-7.0' => 'https://drive.google.com/file/d/17RAaoNK3D_ZF46SSQZ7aCeYKitVeUnBo/view?usp=sharing',
        'SSP5-8.5' => 'https://drive.google.com/file/d/1viEbo21f-ONUzqKTpVFwSQri7Pk4so_v/view?usp=sharing'
    ];

    // Fungsi normalisasi PHP (opsional untuk seeding langsung dengan url bersih)
    function phpNormalizeDriveUrl($url) {
        if (preg_match('/(?:drive|docs)\.google\.com\/file\/d\/([a-zA-Z0-9_-]+)/', $url, $matches)) {
            return "https://lh3.googleusercontent.com/d/" . $matches[1];
        }
        return $url;
    }

    $ssp126Normalized = phpNormalizeDriveUrl($sampleDriveLinks['SSP1-2.6']);
    $ssp245Normalized = phpNormalizeDriveUrl($sampleDriveLinks['SSP2-4.5']);
    $ssp370Normalized = phpNormalizeDriveUrl($sampleDriveLinks['SSP3-7.0']);
    $ssp585Normalized = phpNormalizeDriveUrl($sampleDriveLinks['SSP5-8.5']);

    // 5. Masukkan atau update data untuk variable suhu dan curah hujan
    $affectedRows = 0;
    foreach ($varIds as $varName => $vId) {
        foreach ($periodIds as $pRange => $pId) {
            // Kita lakukan UPSERT
            $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 = CASE WHEN EXCLUDED.image_stripe_ssp126_url != '' THEN EXCLUDED.image_stripe_ssp126_url ELSE future_climate.image_stripe_ssp126_url END,
                    image_stripe_ssp245_url = CASE WHEN EXCLUDED.image_stripe_ssp245_url != '' THEN EXCLUDED.image_stripe_ssp245_url ELSE future_climate.image_stripe_ssp245_url END,
                    image_stripe_ssp370_url = CASE WHEN EXCLUDED.image_stripe_ssp370_url != '' THEN EXCLUDED.image_stripe_ssp370_url ELSE future_climate.image_stripe_ssp370_url END,
                    image_stripe_ssp585_url = CASE WHEN EXCLUDED.image_stripe_ssp585_url != '' THEN EXCLUDED.image_stripe_ssp585_url ELSE future_climate.image_stripe_ssp585_url END,
                    updated_at = CURRENT_TIMESTAMP
            ");

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

    echo "Sukses! Berhasil mempopulerkan {$affectedRows} entri Climate Stripe Proyeksi dengan tautan Google Drive di database.\n";

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