<?php
declare(strict_types=1);

/** Kategorien eines Bereichs laden. */
function triptale_categories(string $type): array
{
    try {
        $q = db()->prepare('SELECT c.id, ct.name FROM ' . table('categories') . ' c INNER JOIN ' . table('category_translations') . ' ct ON ct.category_id = c.id AND ct.language_code = :language AND ct.name <> \'\' WHERE c.type = :type AND c.status = \'active\' ORDER BY c.sort_order, ct.name');
        $q->execute([':type' => $type, ':language' => language()]);
        return $q->fetchAll();
    } catch (Throwable $exception) {
        return [];
    }
}

/** Übersetzungen einer Kategorie laden; fehlende Werte bleiben leer. */
function triptale_category_translations(int $categoryId): array
{
    $values = array_fill_keys(array_keys(triptale_available_languages()), '');
    try {
        $q = db()->prepare('SELECT language_code, name FROM ' . table('category_translations') . ' WHERE category_id = :id');
        $q->execute([':id' => $categoryId]);
        foreach ($q->fetchAll() as $row) { if (isset($values[$row['language_code']])) { $values[$row['language_code']] = (string)$row['name']; } }
    } catch (Throwable $exception) {}
    return $values;
}

/** Alle eingetragenen Kategorienamen speichern. */
function triptale_save_category_translations(int $categoryId, array $submitted): void
{
    foreach (array_keys(triptale_available_languages()) as $language) {
        if (!array_key_exists('translation_' . $language, $submitted)) {
            continue;
        }
        $name = trim((string)($submitted['translation_' . $language] ?? ''));
        try {
            if ($name === '') { db()->prepare('DELETE FROM ' . table('category_translations') . ' WHERE category_id = :id AND language_code = :language')->execute([':id' => $categoryId, ':language' => $language]); }
            else { db()->prepare('INSERT INTO ' . table('category_translations') . ' (category_id, language_code, name) VALUES (:id, :language, :name) ON DUPLICATE KEY UPDATE name = VALUES(name)')->execute([':id' => $categoryId, ':language' => $language, ':name' => $name]); }
        } catch (Throwable $exception) {}
    }
}

/** Aus dem Formular nur gültige Kategorie-IDs übernehmen. */
function triptale_category_ids(array $submitted, string $type): array
{
    $ids = array_values(array_unique(array_filter(array_map('intval', $submitted), static fn(int $id): bool => $id > 0)));
    if (!$ids) { return []; }
    $marks = implode(',', array_fill(0, count($ids), '?'));
    $q = db()->prepare('SELECT id FROM ' . table('categories') . " WHERE type = ? AND status = 'active' AND id IN ($marks)");
    $q->execute(array_merge([$type], $ids));
    return array_map('intval', $q->fetchAll(PDO::FETCH_COLUMN));
}

/** Kategorie-Zuordnungen eines Eintrags speichern. */
function triptale_sync_categories(string $type, int $itemId, array $categoryIds): void
{
    $maps = [
        'attraction' => ['attraction_categories', 'attraction_id'],
        'accommodation' => ['accommodation_categories', 'accommodation_id'],
        'restaurant' => ['restaurant_categories', 'restaurant_id'],
        'custom' => ['trip_day_item_categories', 'trip_day_item_id'],
        'custom_point' => ['custom_point_categories', 'custom_point_id'],
    ];
    if (!isset($maps[$type])) { return; }
    [$mapTable, $itemColumn] = $maps[$type];
    $delete = db()->prepare('DELETE FROM ' . table($mapTable) . " WHERE $itemColumn = :id");
    $delete->execute([':id' => $itemId]);
    if (!$categoryIds) { return; }
    $insert = db()->prepare('INSERT INTO ' . table($mapTable) . " ($itemColumn, category_id) VALUES (:item_id, :category_id)");
    foreach ($categoryIds as $categoryId) { $insert->execute([':item_id' => $itemId, ':category_id' => $categoryId]); }
}

/** Bereits ausgewählte Kategorien eines Eintrags laden. */
function triptale_selected_category_ids(string $type, int $itemId): array
{
    $maps = [
        'attraction' => ['attraction_categories', 'attraction_id'],
        'accommodation' => ['accommodation_categories', 'accommodation_id'],
        'restaurant' => ['restaurant_categories', 'restaurant_id'],
        'custom' => ['trip_day_item_categories', 'trip_day_item_id'],
        'custom_point' => ['custom_point_categories', 'custom_point_id'],
    ];
    if (!isset($maps[$type])) { return []; }
    [$mapTable, $itemColumn] = $maps[$type];
    $q = db()->prepare('SELECT category_id FROM ' . table($mapTable) . " WHERE $itemColumn = :id");
    $q->execute([':id' => $itemId]);
    return array_map('intval', $q->fetchAll(PDO::FETCH_COLUMN));
}
