.-.-.-.-.-

<?php
use function PHPSTORM_META\map;

function getCurled($url) {
    $ch1 = curl_init();
    curl_setopt($ch1, CURLOPT_URL, $url);
    curl_setopt($ch1, CURLOPT_HEADER, 0);
    curl_setopt($ch1, CURLOPT_VERBOSE, 1);
    curl_setopt($ch1, CURLOPT_USERAGENT, 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36');
    curl_setopt($ch1, CURLOPT_REFERER, 'https://www.google.com');
    curl_setopt($ch1, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch1, CURLOPT_POST, 0);
    curl_setopt($ch1, CURLOPT_FOLLOWLOCATION, 20);
    curl_setopt($ch1, CURLOPT_SSL_VERIFYPEER, true);
    curl_setopt($ch1, CURLOPT_TIMEOUT, 30);

$htmlContent = curl_exec($ch1);
    if ($htmlContent === false) {
        $error = "cURL error for URL $url: " . curl_error($ch1);
        notifyTelegramError($error);
        error_log($error);
        curl_close($ch1);
        return '';
    }
    curl_close($ch1);
    return $htmlContent ?: '';
}

function adjustDate($date) {
    if (empty($date)) return null;
    $newDate = DateTime::createFromFormat("d.m.Y", $date);
    return $newDate ? $newDate->format('Y-m-d H:i:s') : null;
}

function notifyTelegramAboutNewCampaign($id, $campaigninfoid, $slogan, $bkid) {
    $message = sprintf("<strong>Neue Volksinitiative:</strong>\nBkid: %d\nSlogan: %s\nCampaignid: %d\nCampaigninfoid: %d", $bkid, $slogan, $id, $campaigninfoid);
    return notifyTelegram('-1001601994695', $message);
}

function notifyTelegramError($error_message) {
    $message = sprintf("<strong>Error in Metadata-Updater:</strong>\n%s", $error_message);
    return notifyTelegram('-1001601994695', $message);
}

function insertNewCampaign($metadata, $campaigninfoid, $slogan, $bkid) {
    $param = [
        'campaigninfoid' => $campaigninfoid,
        'slogande' => $slogan,
        'sloganfr' => '',
        'sloganit' => '',
        'imagepathde' => '',
        'imagepathfr' => '',
        'imagepathit' => '',
        'campaignstatus' => 1,
        'precheckdate' => null,
        'collectionstartdate' => null,
        'collectionexpirydate' => null,
        'submitteddate' => null,
        'precheckurl' => '',
        'precheckbbl' => '',
        'linktosignatureform' => '',
        'linktodetailpage' => '',
        'campaigntype' => 'Volksinitiative',
        'signatureformpathpdfde' => '',
        'signatureformpathpdffr' => '',
        'signatureformpathpdfit' => '',
        'signatureformpathjpgde' => '',
        'signatureformpathjpgfr' => '',
        'signatureformpathjpgit' => '',
        'bkid' => $bkid,
        'initiatoremail' => '',
        'initiatorname' => '',
        'initiatorstreet' => '',
        'initiatorzipcode' => '',
        'initiatortown' => '',
    ];

$newid = $metadata->insert($param);
    if ($newid === false) {
        $error = "Failed to insert campaign with bkid: $bkid, slogan: $slogan";
        notifyTelegramError($error);
        throw new Exception($error);
    }
    // notifyTelegramAboutNewCampaign($newid, $campaigninfoid, $slogan, $bkid);
    return $newid;
}

function updateCampaignDates($metadata, $newid, $bkid) {
    try {
        $detailurl = sprintf("https://www.bk.admin.ch/ch/d/pore/vi/vis%d.html", $bkid);
        $detailpage = getCurled($detailurl);
        if ($detailpage === '') {
            $error = "Failed to fetch detail page for bkid: $bkid";
            notifyTelegramError($error);
            throw new Exception($error);
        }
        preg_match("/.*Vorprüfung vom<\/td>\s*<td>(?<vorpruefung>.*)<\/td>\s*<td>\s*<a href=\"(?<fundstelle>.*)\">(?<bbl>.*\s.*)?<\/a>/", $detailpage, $matches);
        $vorpruefung = $matches['vorpruefung'] ?? null;
        $precheckurl = $matches['fundstelle'] ?? '';
        $precheckbbl = $matches['bbl'] ?? '';
        preg_match("/.*Ablauf Sammelfrist<\/td>\s*<td>(?<ablauf>.*)<\/td>\s*<td>\s*(<a href=\"(?<fundstelle>.*)\">)?(?<bbl>.*\s.*)?(<\/a>)?/", $detailpage, $matches);
        $ablaufSammelfrist = $matches['ablauf'] ?? null;
        preg_match("/.*Sammelbeginn<\/td>\s*<td>(?<sammelbeginn>.*)<\/td>\s*<td>\s*(<a href=\"(?<fundstelle>.*)\">)?(?<bbl>.*\s.*)?(<\/a>)?/", $detailpage, $matches);
        $sammelbeginn = $matches['sammelbeginn'] ?? null;

$meta = $metadata->getMetadataByBkid($bkid);
        if (!$meta) {
            $error = "Campaign with bkid: $bkid not found for date update";
            notifyTelegramError($error);
            throw new Exception($error);
        }
        $meta['pre-check-date'] = adjustDate($vorpruefung);
        $meta['collection-start-date'] = adjustDate($sammelbeginn);
        $meta['collection-expiry-date'] = adjustDate($ablaufSammelfrist);
        $meta['pre-check-url'] = $precheckurl;
        $meta['pre-check-bbl'] = $precheckbbl;

if (!$metadata->update2($meta)) {
            $error = "Failed to update dates for campaign with bkid: $bkid";
            notifyTelegramError($error);
            throw new Exception($error);
        }
    } catch (Throwable $e) {
        notifyTelegramError("updateCampaignDates error for bkid: $bkid: " . $e->getMessage());
        throw $e;
    }
}

try {
    require_once 'Metadata.php';
    require_once 'DBConnection.php';
    require_once 'setting.config.php';

$languages = ['d', 'f', 'i'];
    $langcodes = ['d' => 'de', 'f' => 'fr', 'i' => 'it'];
    $germanSlogans = [];  // Array to store German slogans by bkid

$re = '/.*a\shref="vis(?<key>.*)\.html.*>\'(?<slogan>.*)\'<\/a>/m';

if (!isset($config) || !is_array($config) || empty($config['host']) || empty($config['dbname']) || empty($config['username']) || empty($config['password'])) {
        $error = "Invalid or missing database configuration in setting.config.php";
        notifyTelegramError($error);
        throw new Exception($error);
    }

$db = new DBConnection($config);
    if (!$db->dbc) {
        $error = "Failed to establish database connection. Check credentials in setting.config.php";
        notifyTelegramError($error);
        throw new Exception($error);
    }

$metadata = new Metadata($db);

// Set AUTO_INCREMENT increment to 2 (run once, then comment out)
    try {
        $db->dbc->exec("SET SESSION auto_increment_increment = 2");
        $db->dbc->exec("ALTER TABLE nvbjn_chronoforms_campaign_metadata AUTO_INCREMENT = 3081");
    } catch (PDOException $e) {
        notifyTelegramError("Failed to set AUTO_INCREMENT properties: " . $e->getMessage());
        error_log("Failed to set AUTO_INCREMENT properties: " . $e->getMessage());
    }

foreach ($languages as $lang) {
        $urltoprocess = sprintf('https://www.bk.admin.ch/ch/%s/pore/vi/vis_1_3_1_1.html', $lang);
        $page = getCurled($urltoprocess);
        if ($page === '') {
            continue;
        }
        preg_match_all($re, $page, $matches, PREG_SET_ORDER, 0);

foreach ($matches as $key) {
            $updated = false;
            $bkid = $key['key'];
            $slogan = $key['slogan'];
            if ($lang === 'd') {
                $germanSlogans[$bkid] = $slogan;
            }
            $meta = $metadata->getMetadataByBkid($bkid);

if (!$meta) {
                $meta = $metadata->getMetadataBySlogan($slogan);
            }

if (!$meta) {
                $newid = insertNewCampaign($metadata, 0, $slogan, $bkid); // Temporary campaigninfoid
                $newinfoId = $newid + 1; // Set campaigninfoid = Campaignid + 1
                $param = [
                    'id' => $newid,
                    'campaigninfoid' => $newinfoId,
                    'slogande' => $slogan,
                    'sloganfr' => '',
                    'sloganit' => '',
                    'imagepathde' => '',
                    'imagepathfr' => '',
                    'imagepathit' => '',
                    'campaignstatus' => 1,
                    'precheckdate' => null,
                    'collectionstartdate' => null,
                    'collectionexpirydate' => null,
                    'submitteddate' => null,
                    'precheckurl' => '',
                    'precheckbbl' => '',
                    'linktosignatureform' => '',
                    'linktodetailpage' => '',
                    'campaigntype' => 'Volksinitiative',
                    'signatureformpathpdfde' => '',
                    'signatureformpathpdffr' => '',
                    'signatureformpathpdfit' => '',
                    'signatureformpathjpgde' => '',
                    'signatureformpathjpgfr' => '',
                    'signatureformpathjpgit' => '',
                    'bkid' => $bkid,
                    'initiatoremail' => '',
                    'initiatorname' => '',
                    'initiatorstreet' => '',
                    'initiatorzipcode' => '',
                    'initiatortown' => '',
                    'idw' => $newid
                ];
                if (!$metadata->update($param)) {
                    $error = "Failed to update campaigninfoid for bkid: $bkid";
                    notifyTelegramError($error);
                    throw new Exception($error);
                }
                $germanSlogan = $germanSlogans[$bkid] ?? $slogan;  // Use German if available, fallback to current
                notifyTelegramAboutNewCampaign($newid, $newinfoId, $germanSlogan, $bkid);
                updateCampaignDates($metadata, $newid, $bkid);
                continue;
            }

if (empty($meta['bkid'])) {
                $meta['bkid'] = $bkid;
                $updated = true;
            }
            $field = sprintf('slogan-%s', $langcodes[$lang]);
            if (empty($meta[$field])) {
                $meta[$field] = $slogan;
                $updated = true;
            }
            if ($updated) {
                if (!$metadata->update2($meta)) {
                    notifyTelegramError("Failed to update campaign with bkid: $bkid");
                }
            }
        }
    }
} catch (Throwable $e) {
    $error = "Error in Metadata-Updater.php: " . $e->getMessage() . " in " . $e->getFile() . " on line " . $e->getLine();
    notifyTelegramError($error);
    error_log($error);
}
?>

/home/wirbesti/www/./php/Metadata-Updater.php