<?php
/**
 * Database layer for dish categories and dishes.
 *
 * @package Maca_Menu_List
 */

if (!defined('ABSPATH')) {
    exit;
}

/**
 * Dish categories table name.
 *
 * @return string
 */
function maca_menulist_db_dish_categories_table() {
    global $wpdb;

    return $wpdb->prefix . 'maca_dish_categories';
}

/**
 * Dishes table name.
 *
 * @return string
 */
function maca_menulist_db_dishes_table() {
    global $wpdb;

    return $wpdb->prefix . 'maca_dishes';
}

/**
 * Allergens table name.
 *
 * @return string
 */
function maca_menulist_db_allergens_table() {
    global $wpdb;

    return $wpdb->prefix . 'maca_allergens';
}

/**
 * Dish–allergen link table name.
 *
 * @return string
 */
function maca_menulist_db_dish_allergens_table() {
    global $wpdb;

    return $wpdb->prefix . 'maca_dish_allergens';
}

/**
 * Guest dish ratings table name.
 *
 * @return string
 */
function maca_menulist_db_dish_ratings_table() {
    global $wpdb;

    return $wpdb->prefix . 'maca_dish_ratings';
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange

/**
 * Create dish-related database tables.
 */
function maca_menulist_db_create_dish_tables() {
    global $wpdb;

    if (!isset($wpdb) || empty($wpdb)) {
        return;
    }

    $charset_collate = $wpdb->get_charset_collate();
    $categories_table = maca_menulist_db_dish_categories_table();
    $dishes_table = maca_menulist_db_dishes_table();
    $allergens_table = maca_menulist_db_allergens_table();
    $dish_allergens_table = maca_menulist_db_dish_allergens_table();
    $ratings_table = maca_menulist_db_dish_ratings_table();

    $sql_categories = "CREATE TABLE $categories_table (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        name varchar(255) NOT NULL,
        name_en varchar(255) DEFAULT NULL,
        sort_order int(11) DEFAULT 0,
        is_active tinyint(1) NOT NULL DEFAULT 1,
        start_date datetime DEFAULT NULL,
        end_date datetime DEFAULT NULL,
        time_start time DEFAULT NULL,
        time_end time DEFAULT NULL,
        days_of_week varchar(32) DEFAULT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY  (id)
    ) $charset_collate;";

    $sql_dishes = "CREATE TABLE $dishes_table (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        category_id mediumint(9) NOT NULL,
        title varchar(255) NOT NULL,
        description text DEFAULT NULL,
        price decimal(10,2) DEFAULT NULL,
        price_takeaway decimal(10,2) DEFAULT NULL,
        takeaway_active tinyint(1) NOT NULL DEFAULT 1,
        vat_code varchar(20) NOT NULL DEFAULT 'food',
        image_url varchar(500) DEFAULT NULL,
        sort_order int(11) DEFAULT 0,
        is_active tinyint(1) DEFAULT 1,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        KEY category_id (category_id)
    ) $charset_collate;";

    $sql_allergens = "CREATE TABLE $allergens_table (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        name varchar(255) NOT NULL,
        name_en varchar(255) DEFAULT NULL,
        sort_order int(11) DEFAULT 0,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY  (id)
    ) $charset_collate;";

    $sql_dish_allergens = "CREATE TABLE $dish_allergens_table (
        dish_id mediumint(9) NOT NULL,
        allergen_id mediumint(9) NOT NULL,
        PRIMARY KEY  (dish_id, allergen_id),
        KEY allergen_id (allergen_id)
    ) $charset_collate;";

    $sql_ratings = "CREATE TABLE $ratings_table (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        dish_id mediumint(9) NOT NULL,
        rating tinyint(1) NOT NULL,
        voter_key varchar(64) NOT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        UNIQUE KEY dish_voter (dish_id, voter_key),
        KEY dish_id (dish_id)
    ) $charset_collate;";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql_categories);
    dbDelta($sql_dishes);
    dbDelta($sql_allergens);
    dbDelta($sql_dish_allergens);
    dbDelta($sql_ratings);

    maca_menulist_db_ensure_allergen_columns();
    maca_menulist_db_ensure_dish_category_columns();
}

/**
 * Ensure dish category table has bilingual name and active flag columns.
 */
function maca_menulist_db_dish_categories_have_is_active_column() {
    return get_option('maca_menulist_db_schema_dish_category_is_active') === '1';
}

/**
 * Mark dish category is_active column as present (after migrate / dbDelta).
 *
 * @return void
 */
function maca_menulist_db_mark_dish_category_is_active_column() {
    update_option('maca_menulist_db_schema_dish_category_is_active', '1', false);
}

function maca_menulist_db_ensure_dish_category_columns() {
    global $wpdb;

    static $ensured = false;

    if ($ensured) {
        return;
    }

    $ensured = true;

    if (maca_menulist_db_dish_categories_have_is_active_column()) {
        return;
    }

    $table = maca_menulist_db_dish_categories_table();
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table)) !== $table) {
        return;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    if (!$wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'name_en'")) {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange
        $wpdb->query("ALTER TABLE $table ADD COLUMN name_en varchar(255) DEFAULT NULL AFTER name");
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    if (!$wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'is_active'")) {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange
        $wpdb->query("ALTER TABLE $table ADD COLUMN is_active tinyint(1) NOT NULL DEFAULT 1 AFTER sort_order");
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    if ($wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'is_active'")) {
        maca_menulist_db_mark_dish_category_is_active_column();
    }
}

/**
 * Whether a dish category is shown on the guest menu.
 *
 * @param object $category Dish category row.
 * @return bool
 */
function maca_menulist_dish_category_is_active($category) {
    return !isset($category->is_active) || (int) $category->is_active === 1;
}

/**
 * Add schedule columns to dish categories table on existing installs.
 */
function maca_menulist_db_ensure_dish_category_schedule_columns() {
    global $wpdb;

    $table = maca_menulist_db_dish_categories_table();
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    if ($wpdb->get_var("SHOW TABLES LIKE '$table'") !== $table) {
        return;
    }

    if (!$wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'start_date'")) {
        $wpdb->query("ALTER TABLE $table ADD COLUMN start_date datetime DEFAULT NULL AFTER is_active");
    }
    if (!$wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'end_date'")) {
        $wpdb->query("ALTER TABLE $table ADD COLUMN end_date datetime DEFAULT NULL AFTER start_date");
    }
    if (!$wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'time_start'")) {
        $wpdb->query("ALTER TABLE $table ADD COLUMN time_start time DEFAULT NULL AFTER end_date");
    }
    if (!$wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'time_end'")) {
        $wpdb->query("ALTER TABLE $table ADD COLUMN time_end time DEFAULT NULL AFTER time_start");
    }
    if (!$wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'days_of_week'")) {
        $wpdb->query("ALTER TABLE $table ADD COLUMN days_of_week varchar(32) DEFAULT NULL AFTER time_end");
    }
}

/**
 * Build a comparable schedule signature from a row object.
 *
 * @param object $row Dish or category row.
 * @return string Empty when no schedule fields are set.
 */
function maca_menulist_db_schedule_signature($row) {
    if (!$row) {
        return '';
    }

    $parts = array(
        (string) ($row->start_date ?? ''),
        (string) ($row->end_date ?? ''),
        (string) ($row->time_start ?? ''),
        (string) ($row->time_end ?? ''),
        (string) ($row->days_of_week ?? ''),
    );

    $joined = implode('|', $parts);

    return $joined === '||||' ? '' : $joined;
}

/**
 * Move per-dish QR schedules to dish categories (one-time migration).
 *
 * @return void
 */
function maca_menulist_db_migrate_dish_schedule_to_categories() {
    if (get_option('maca_menulist_db_migrated_dish_schedule_to_categories') === '1') {
        return;
    }

    global $wpdb;

    maca_menulist_db_ensure_dish_schedule_columns();
    maca_menulist_db_ensure_dish_category_schedule_columns();

    $dishes_table = maca_menulist_db_dishes_table();
    $categories_table = maca_menulist_db_dish_categories_table();

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    if ($wpdb->get_var("SHOW TABLES LIKE '$dishes_table'") !== $dishes_table) {
        update_option('maca_menulist_db_migrated_dish_schedule_to_categories', '1', false);
        return;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    if (!$wpdb->get_var("SHOW COLUMNS FROM $dishes_table LIKE 'start_date'")) {
        update_option('maca_menulist_db_migrated_dish_schedule_to_categories', '1', false);
        return;
    }

    $categories = maca_menulist_db_get_dish_categories(false);
    foreach ($categories as $category) {
        $category_id = (int) $category->id;
        if ($category_id <= 0) {
            continue;
        }

        if (maca_menulist_db_schedule_signature($category) !== '') {
            continue;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $dishes = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT start_date, end_date, time_start, time_end, days_of_week
                FROM $dishes_table
                WHERE category_id = %d",
                $category_id
            )
        );

        if (empty($dishes)) {
            continue;
        }

        $signatures = array();
        foreach ($dishes as $dish) {
            $signature = maca_menulist_db_schedule_signature($dish);
            if ($signature === '') {
                continue;
            }
            $signatures[$signature] = $dish;
        }

        if (count($signatures) !== 1) {
            continue;
        }

        $source = reset($signatures);
        maca_menulist_db_update_dish_category(
            $category_id,
            array(
                'start_date' => $source->start_date,
                'end_date' => $source->end_date,
                'time_start' => $source->time_start,
                'time_end' => $source->time_end,
                'days_of_week' => $source->days_of_week,
            )
        );
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    $wpdb->query(
        "UPDATE $dishes_table
        SET start_date = NULL, end_date = NULL, time_start = NULL, time_end = NULL, days_of_week = NULL
        WHERE start_date IS NOT NULL
            OR end_date IS NOT NULL
            OR time_start IS NOT NULL
            OR time_end IS NOT NULL
            OR days_of_week IS NOT NULL"
    );

    update_option('maca_menulist_db_migrated_dish_schedule_to_categories', '1', false);
}

/**
 * Ensure legacy per-dish schedule columns on upgrades that already have them.
 */
function maca_menulist_db_ensure_dish_schedule_columns() {
    global $wpdb;

    $table = maca_menulist_db_dishes_table();
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    if ($wpdb->get_var("SHOW TABLES LIKE '$table'") !== $table) {
        return;
    }

    if (!$wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'start_date'")) {
        return;
    }
    if (!$wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'end_date'")) {
        $wpdb->query("ALTER TABLE $table ADD COLUMN end_date datetime DEFAULT NULL AFTER start_date");
    }
    if (!$wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'time_start'")) {
        $wpdb->query("ALTER TABLE $table ADD COLUMN time_start time DEFAULT NULL AFTER end_date");
    }
    if (!$wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'time_end'")) {
        $wpdb->query("ALTER TABLE $table ADD COLUMN time_end time DEFAULT NULL AFTER time_start");
    }
    if (!$wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'days_of_week'")) {
        $wpdb->query("ALTER TABLE $table ADD COLUMN days_of_week varchar(32) DEFAULT NULL AFTER time_end");
    }
}

/**
 * Add VAT code column to dishes table on existing installs.
 */
/**
 * Add takeaway price and availability columns on existing installs.
 */
function maca_menulist_db_ensure_dish_takeaway_columns() {
    global $wpdb;

    $table = maca_menulist_db_dishes_table();
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    if ($wpdb->get_var("SHOW TABLES LIKE '$table'") !== $table) {
        return;
    }

    if (!$wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'price_takeaway'")) {
        $wpdb->query("ALTER TABLE $table ADD COLUMN price_takeaway decimal(10,2) DEFAULT NULL AFTER price");
    }

    if (!$wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'takeaway_active'")) {
        $wpdb->query("ALTER TABLE $table ADD COLUMN takeaway_active tinyint(1) NOT NULL DEFAULT 1 AFTER price_takeaway");
    }
}

function maca_menulist_db_ensure_dish_vat_code_column() {
    global $wpdb;

    $table = maca_menulist_db_dishes_table();
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    if ($wpdb->get_var("SHOW TABLES LIKE '$table'") !== $table) {
        return;
    }

    if (!$wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'vat_code'")) {
        $wpdb->query("ALTER TABLE $table ADD COLUMN vat_code varchar(20) NOT NULL DEFAULT 'food' AFTER price");
    }
}

/**
 * Ensure the guest ratings table exists (lazy migration for existing installs).
 */
function maca_menulist_db_ensure_dish_ratings_table() {
    global $wpdb;

    static $ensured = false;
    if ($ensured) {
        return;
    }

    $table = maca_menulist_db_dish_ratings_table();
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));

    if ($exists !== $table) {
        maca_menulist_db_create_dish_tables();
    }

    $ensured = true;
}

/**
 * @param bool $active_only Only categories marked active for the guest menu.
 * @return array<int, object>
 */
function maca_menulist_db_get_dish_categories($active_only = false) {
    global $wpdb;

    $table = maca_menulist_db_dish_categories_table();
    $where = ($active_only && maca_menulist_db_dish_categories_have_is_active_column())
        ? 'WHERE is_active = 1'
        : '';

    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from prefix; optional static WHERE.
    return $wpdb->get_results("SELECT * FROM $table $where ORDER BY sort_order ASC, name ASC");
}

/**
 * @param int $category_id Dish category ID.
 * @return object|null
 */
function maca_menulist_db_get_dish_category($category_id) {
    global $wpdb;

    $table = maca_menulist_db_dish_categories_table();

    return $wpdb->get_row(
        $wpdb->prepare(
            "SELECT * FROM $table WHERE id = %d",
            intval($category_id)
        )
    );
}

/**
 * @param array<string, mixed> $data Row data.
 * @return int|false
 */
function maca_menulist_db_insert_dish_category($data) {
    global $wpdb;

    $schema = array(
        'name' => '%s',
        'name_en' => '%s',
        'sort_order' => '%d',
        'is_active' => '%d',
        'start_date' => '%s',
        'end_date' => '%s',
        'time_start' => '%s',
        'time_end' => '%s',
        'days_of_week' => '%s',
    );

    list($row, $formats) = maca_menulist_db_prepare_row($data, $schema);

    if (empty($row)) {
        return false;
    }

    if (!isset($row['is_active'])) {
        $row['is_active'] = 1;
        $formats[] = '%d';
    }

    maca_menulist_db_stamp_row_timestamps($row, $formats, true);

    return $wpdb->insert(
        maca_menulist_db_dish_categories_table(),
        $row,
        $formats
    );
}

/**
 * @param int                  $category_id Dish category ID.
 * @param array<string, mixed> $data Row data.
 * @return int|false
 */
function maca_menulist_db_update_dish_category($category_id, $data) {
    global $wpdb;

    $schema = array(
        'name' => '%s',
        'name_en' => '%s',
        'sort_order' => '%d',
        'is_active' => '%d',
        'start_date' => '%s',
        'end_date' => '%s',
        'time_start' => '%s',
        'time_end' => '%s',
        'days_of_week' => '%s',
    );

    list($row, $formats) = maca_menulist_db_prepare_row($data, $schema);

    if (empty($row)) {
        return false;
    }

    maca_menulist_db_stamp_row_timestamps($row, $formats, false);

    return $wpdb->update(
        maca_menulist_db_dish_categories_table(),
        $row,
        array('id' => intval($category_id)),
        $formats,
        array('%d')
    );
}

/**
 * @param int $category_id Dish category ID.
 * @return int|false
 */
function maca_menulist_db_delete_dish_category($category_id) {
    global $wpdb;

    $dishes = maca_menulist_db_get_dishes_by_category($category_id, false);
    foreach ($dishes as $dish) {
        maca_menulist_db_delete_dish($dish->id);
    }

    return $wpdb->delete(
        maca_menulist_db_dish_categories_table(),
        array('id' => intval($category_id)),
        array('%d')
    );
}

/**
 * @param int $dish_id Dish ID.
 * @return object|null
 */
function maca_menulist_db_get_dish($dish_id) {
    global $wpdb;

    $table = maca_menulist_db_dishes_table();

    return $wpdb->get_row(
        $wpdb->prepare(
            "SELECT * FROM $table WHERE id = %d",
            intval($dish_id)
        )
    );
}

/**
 * @param bool $active_only Whether to return only active dishes.
 * @return array<int, object>
 */
function maca_menulist_db_get_dishes($active_only = false) {
    global $wpdb;

    $dishes_table = maca_menulist_db_dishes_table();
    $categories_table = maca_menulist_db_dish_categories_table();

    $where = $active_only ? 'WHERE d.is_active = 1' : '';

    return $wpdb->get_results(
        "SELECT d.*, c.name AS category_name, c.sort_order AS category_sort_order
        FROM $dishes_table d
        LEFT JOIN $categories_table c ON d.category_id = c.id
        $where
        ORDER BY c.sort_order ASC, c.name ASC, d.sort_order ASC, d.title ASC"
    );
}

/**
 * @param int  $category_id Dish category ID.
 * @param bool $active_only Whether to return only active dishes.
 * @return array<int, object>
 */
function maca_menulist_db_get_dishes_by_category($category_id, $active_only = true) {
    global $wpdb;

    $table = maca_menulist_db_dishes_table();
    $where_active = $active_only ? ' AND is_active = 1' : '';

    return $wpdb->get_results(
        $wpdb->prepare(
            "SELECT * FROM $table
            WHERE category_id = %d$where_active
            ORDER BY sort_order ASC, title ASC",
            intval($category_id)
        )
    );
}

/**
 * Build row + formats for dish insert/update.
 *
 * @param array<string, mixed> $data Row data.
 * @return array{0: array<string, mixed>, 1: array<int, string>}
 */
function maca_menulist_db_prepare_dish_write($data) {
    $schema = array(
        'category_id' => '%d',
        'title' => '%s',
        'title_en' => '%s',
        'description' => '%s',
        'description_en' => '%s',
        'price' => '%f',
        'price_takeaway' => '%f',
        'takeaway_active' => '%d',
        'vat_code' => '%s',
        'image_url' => '%s',
        'sort_order' => '%d',
        'is_active' => '%d',
    );

    $row = array();
    $formats = array();

    foreach ($schema as $key => $format) {
        if (array_key_exists($key, $data)) {
            $row[$key] = $data[$key];
            $formats[] = $format;
        }
    }

    return array($row, $formats);
}

/**
 * @param array<string, mixed> $data Row data.
 * @return int|false
 */
function maca_menulist_db_insert_dish($data) {
    global $wpdb;

    list($row, $formats) = maca_menulist_db_prepare_dish_write($data);

    if (empty($row)) {
        return false;
    }

    maca_menulist_db_stamp_row_timestamps($row, $formats, true);

    return $wpdb->insert(
        maca_menulist_db_dishes_table(),
        $row,
        $formats
    );
}

/**
 * @param int                  $dish_id Dish ID.
 * @param array<string, mixed> $data Row data.
 * @return int|false
 */
function maca_menulist_db_update_dish($dish_id, $data) {
    global $wpdb;

    list($row, $formats) = maca_menulist_db_prepare_dish_write($data);

    if (empty($row)) {
        return false;
    }

    maca_menulist_db_stamp_row_timestamps($row, $formats, false);

    return $wpdb->update(
        maca_menulist_db_dishes_table(),
        $row,
        array('id' => intval($dish_id)),
        $formats,
        array('%d')
    );
}

/**
 * @param int $dish_id Dish ID.
 * @return int|false
 */
function maca_menulist_db_delete_dish($dish_id) {
    global $wpdb;

    maca_menulist_db_set_dish_allergens(intval($dish_id), array());

    if (function_exists('maca_menulist_db_set_dish_campaigns')) {
        maca_menulist_db_set_dish_campaigns(intval($dish_id), array());
    }

    return $wpdb->delete(
        maca_menulist_db_dishes_table(),
        array('id' => intval($dish_id)),
        array('%d')
    );
}

/**
 * Update sort_order for dishes within a category (0-based, in list order).
 *
 * @param int   $category_id Dish category ID.
 * @param int[] $dish_ids    Dish IDs in desired order.
 * @return bool
 */
function maca_menulist_db_update_dishes_sort_order($category_id, $dish_ids) {
    global $wpdb;

    $category_id = intval($category_id);
    $dish_ids = array_values(array_filter(array_map('intval', (array) $dish_ids)));

    if ($category_id <= 0 || empty($dish_ids)) {
        return false;
    }

    $table = maca_menulist_db_dishes_table();
    $placeholders = implode(',', array_fill(0, count($dish_ids), '%d'));
    $query_args = array_merge(array($category_id), $dish_ids);

    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Placeholder count matches dish IDs.
    $found = $wpdb->get_col(
        $wpdb->prepare(
            "SELECT id FROM $table WHERE category_id = %d AND id IN ($placeholders)",
            $query_args
        )
    );

    if (count($found) !== count($dish_ids)) {
        return false;
    }

    foreach ($dish_ids as $index => $dish_id) {
        $wpdb->update(
            $table,
            array('sort_order' => $index),
            array('id' => $dish_id),
            array('%d'),
            array('%d')
        );
    }

    return true;
}

/**
 * Update sort_order for dish categories (0-based, in list order).
 *
 * @param int[] $category_ids Dish category IDs in desired order.
 * @return bool
 */
function maca_menulist_db_update_dish_categories_sort_order($category_ids) {
    global $wpdb;

    $category_ids = array_values(array_filter(array_map('intval', (array) $category_ids)));

    if (empty($category_ids)) {
        return false;
    }

    $table = maca_menulist_db_dish_categories_table();
    $placeholders = implode(',', array_fill(0, count($category_ids), '%d'));

    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Placeholder count matches category IDs.
    $found = $wpdb->get_col(
        $wpdb->prepare(
            "SELECT id FROM $table WHERE id IN ($placeholders)",
            $category_ids
        )
    );

    if (count($found) !== count($category_ids)) {
        return false;
    }

    foreach ($category_ids as $index => $category_id) {
        $wpdb->update(
            $table,
            array('sort_order' => $index),
            array('id' => $category_id),
            array('%d'),
            array('%d')
        );
    }

    return true;
}

/**
 * @return array<int, object>
 */
function maca_menulist_db_get_allergens() {
    global $wpdb;

    $table = maca_menulist_db_allergens_table();

    return $wpdb->get_results("SELECT * FROM $table ORDER BY sort_order ASC, name ASC");
}

/**
 * @param int $allergen_id Allergen ID.
 * @return object|null
 */
function maca_menulist_db_get_allergen($allergen_id) {
    global $wpdb;

    $table = maca_menulist_db_allergens_table();

    return $wpdb->get_row(
        $wpdb->prepare(
            "SELECT * FROM $table WHERE id = %d",
            intval($allergen_id)
        )
    );
}

/**
 * @param array<string, mixed> $data Row data.
 * @return int|false
 */
function maca_menulist_db_insert_allergen($data) {
    global $wpdb;

    $row = array(
        'name' => isset($data['name']) ? $data['name'] : '',
        'sort_order' => isset($data['sort_order']) ? (int) $data['sort_order'] : 0,
    );
    $formats = array('%s', '%d');

    if (array_key_exists('name_en', $data)) {
        $row['name_en'] = $data['name_en'];
        $formats[] = '%s';
    }

    maca_menulist_db_stamp_row_timestamps($row, $formats, true);

    return $wpdb->insert(
        maca_menulist_db_allergens_table(),
        $row,
        $formats
    );
}

/**
 * @param int                  $allergen_id Allergen ID.
 * @param array<string, mixed> $data Row data.
 * @return int|false
 */
function maca_menulist_db_update_allergen($allergen_id, $data) {
    global $wpdb;

    $row = array();
    $formats = array();

    if (array_key_exists('name', $data)) {
        $row['name'] = $data['name'];
        $formats[] = '%s';
    }

    if (array_key_exists('name_en', $data)) {
        $row['name_en'] = $data['name_en'];
        $formats[] = '%s';
    }

    if (array_key_exists('sort_order', $data)) {
        $row['sort_order'] = (int) $data['sort_order'];
        $formats[] = '%d';
    }

    if (empty($row)) {
        return false;
    }

    return $wpdb->update(
        maca_menulist_db_allergens_table(),
        $row,
        array('id' => intval($allergen_id)),
        $formats,
        array('%d')
    );
}

/**
 * @param int $allergen_id Allergen ID.
 * @return int|false
 */
function maca_menulist_db_delete_allergen($allergen_id) {
    global $wpdb;

    $wpdb->delete(
        maca_menulist_db_dish_allergens_table(),
        array('allergen_id' => intval($allergen_id)),
        array('%d')
    );

    return $wpdb->delete(
        maca_menulist_db_allergens_table(),
        array('id' => intval($allergen_id)),
        array('%d')
    );
}

/**
 * @param int $dish_id Dish ID.
 * @return array<int, object>
 */
function maca_menulist_db_get_allergens_for_dish($dish_id) {
    global $wpdb;

    $allergens_table = maca_menulist_db_allergens_table();
    $link_table = maca_menulist_db_dish_allergens_table();

    return $wpdb->get_results(
        $wpdb->prepare(
            "SELECT a.*
            FROM $allergens_table a
            INNER JOIN $link_table da ON da.allergen_id = a.id
            WHERE da.dish_id = %d
            ORDER BY a.sort_order ASC, a.name ASC",
            intval($dish_id)
        )
    );
}

/**
 * @param array<int, int> $dish_ids Dish IDs.
 * @return array<int, array<int, object>>
 */
function maca_menulist_db_get_allergens_for_dish_ids($dish_ids) {
    global $wpdb;

    $dish_ids = array_values(array_filter(array_map('intval', $dish_ids)));
    if (empty($dish_ids)) {
        return array();
    }

    $allergens_table = maca_menulist_db_allergens_table();
    $link_table = maca_menulist_db_dish_allergens_table();
    $placeholders = implode(',', array_fill(0, count($dish_ids), '%d'));

    $rows = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT da.dish_id, a.*
            FROM $link_table da
            INNER JOIN $allergens_table a ON a.id = da.allergen_id
            WHERE da.dish_id IN ($placeholders)
            ORDER BY a.sort_order ASC, a.name ASC",
            ...$dish_ids
        )
    );

    $grouped = array();
    foreach ($rows as $row) {
        $dish_id = (int) $row->dish_id;
        if (!isset($grouped[$dish_id])) {
            $grouped[$dish_id] = array();
        }
        $grouped[$dish_id][] = $row;
    }

    return $grouped;
}

/**
 * @param int $dish_id Dish ID.
 * @return array<int, int>
 */
function maca_menulist_db_get_dish_allergen_ids($dish_id) {
    global $wpdb;

    $table = maca_menulist_db_dish_allergens_table();

    $ids = $wpdb->get_col(
        $wpdb->prepare(
            "SELECT allergen_id FROM $table WHERE dish_id = %d",
            intval($dish_id)
        )
    );

    return array_map('intval', $ids);
}

/**
 * Replace allergen links for a dish.
 *
 * @param int           $dish_id Dish ID.
 * @param array<int, int> $allergen_ids Allergen IDs.
 */
function maca_menulist_db_set_dish_allergens($dish_id, $allergen_ids) {
    global $wpdb;

    $table = maca_menulist_db_dish_allergens_table();
    $dish_id = intval($dish_id);

    $wpdb->delete($table, array('dish_id' => $dish_id), array('%d'));

    $allergen_ids = array_values(array_unique(array_filter(array_map('intval', $allergen_ids))));
    foreach ($allergen_ids as $allergen_id) {
        if ($allergen_id <= 0) {
            continue;
        }

        $wpdb->insert(
            $table,
            array(
                'dish_id' => $dish_id,
                'allergen_id' => $allergen_id,
            ),
            array('%d', '%d')
        );
    }
}

/**
 * Drop dish tables on uninstall.
 */
function maca_menulist_db_drop_dish_tables() {
    global $wpdb;

    if (!function_exists('maca_menulist_db_drop_dish_views_table')) {
        require_once dirname(__FILE__) . '/db-dish-stats.php';
    }
    maca_menulist_db_drop_dish_views_table();

    if (!function_exists('maca_menulist_db_drop_campaign_tables')) {
        require_once dirname(__FILE__) . '/db-campaigns.php';
    }
    maca_menulist_db_drop_campaign_tables();

    foreach (array(
        maca_menulist_db_dish_ratings_table(),
        maca_menulist_db_dish_categories_table(),
        maca_menulist_db_dish_allergens_table(),
        maca_menulist_db_dishes_table(),
        maca_menulist_db_allergens_table(),
    ) as $table) {
        $wpdb->query("DROP TABLE IF EXISTS {$table}");
    }
}

/**
 * Get aggregated rating data for dishes, optionally including the current guest vote.
 *
 * @param array<int, int> $dish_ids  Dish IDs.
 * @param string          $voter_key Optional guest voter key.
 * @return array<int, array{average: float, count: int, user_rating: int}>
 */
function maca_menulist_db_get_dish_rating_data_for_dish_ids($dish_ids, $voter_key = '') {
    global $wpdb;

    maca_menulist_db_ensure_dish_ratings_table();

    $dish_ids = array_values(array_filter(array_map('intval', $dish_ids)));
    if (empty($dish_ids)) {
        return array();
    }

    $ratings_table = maca_menulist_db_dish_ratings_table();
    $placeholders = implode(',', array_fill(0, count($dish_ids), '%d'));
    $data = array();

    foreach ($dish_ids as $dish_id) {
        $data[$dish_id] = array(
            'average' => 0.0,
            'count' => 0,
            'user_rating' => 0,
        );
    }

    $summary_rows = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT dish_id, AVG(rating) AS average_rating, COUNT(*) AS rating_count
            FROM $ratings_table
            WHERE dish_id IN ($placeholders)
            GROUP BY dish_id",
            ...$dish_ids
        )
    );

    foreach ($summary_rows as $row) {
        $dish_id = (int) $row->dish_id;
        if (!isset($data[$dish_id])) {
            continue;
        }

        $data[$dish_id]['average'] = round((float) $row->average_rating, 1);
        $data[$dish_id]['count'] = (int) $row->rating_count;
    }

    if ($voter_key !== '') {
        $user_rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT dish_id, rating
                FROM $ratings_table
                WHERE voter_key = %s AND dish_id IN ($placeholders)",
                $voter_key,
                ...$dish_ids
            )
        );

        foreach ($user_rows as $row) {
            $dish_id = (int) $row->dish_id;
            if (isset($data[$dish_id])) {
                $data[$dish_id]['user_rating'] = (int) $row->rating;
            }
        }
    }

    return $data;
}

/**
 * Save or update a guest rating for a dish.
 *
 * @param int    $dish_id   Dish ID.
 * @param int    $rating    Rating value 1-5.
 * @param string $voter_key Guest voter key.
 * @return bool
 */
function maca_menulist_db_save_dish_rating($dish_id, $rating, $voter_key) {
    global $wpdb;

    maca_menulist_db_ensure_dish_ratings_table();

    $dish_id = intval($dish_id);
    $rating = intval($rating);
    $voter_key = sanitize_text_field($voter_key);

    if ($dish_id <= 0 || $rating < 1 || $rating > 5 || $voter_key === '') {
        return false;
    }

    $ratings_table = maca_menulist_db_dish_ratings_table();
    $existing_id = $wpdb->get_var(
        $wpdb->prepare(
            "SELECT id FROM $ratings_table WHERE dish_id = %d AND voter_key = %s",
            $dish_id,
            $voter_key
        )
    );

    if ($existing_id) {
        return (bool) $wpdb->update(
            $ratings_table,
            array('rating' => $rating),
            array('id' => (int) $existing_id),
            array('%d'),
            array('%d')
        );
    }

    return (bool) $wpdb->insert(
        $ratings_table,
        array(
            'dish_id' => $dish_id,
            'rating' => $rating,
            'voter_key' => $voter_key,
        ),
        array('%d', '%d', '%s')
    );
}

/**
 * Get rating summary for one dish.
 *
 * @param int $dish_id Dish ID.
 * @return array{average: float, count: int}
 */
function maca_menulist_db_get_dish_rating_summary($dish_id) {
    $dish_id = intval($dish_id);
    if ($dish_id <= 0) {
        return array(
            'average' => 0.0,
            'count' => 0,
        );
    }

    $data = maca_menulist_db_get_dish_rating_data_for_dish_ids(array($dish_id));
    if (!isset($data[$dish_id])) {
        return array(
            'average' => 0.0,
            'count' => 0,
        );
    }

    return array(
        'average' => (float) $data[$dish_id]['average'],
        'count' => (int) $data[$dish_id]['count'],
    );
}

/**
 * Ensure allergen table has bilingual name columns.
 */
function maca_menulist_db_ensure_allergen_columns() {
    global $wpdb;

    static $ensured = false;

    if ($ensured) {
        return;
    }

    $table = maca_menulist_db_allergens_table();
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));

    if ($exists !== $table) {
        return;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $has_name_en = $wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'name_en'");

    if (!$has_name_en) {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange
        $wpdb->query("ALTER TABLE $table ADD COLUMN name_en varchar(255) DEFAULT NULL AFTER name");
    }

    $ensured = true;
}

/**
 * EU-style default allergens seeded on install (Swedish + English names in DB).
 *
 * @return array<int, array{name_sv: string, name_en: string, sort_order: int}>
 */
function maca_menulist_get_default_allergens() {
    return array(
        array('name_sv' => 'Gluten', 'name_en' => 'Gluten', 'sort_order' => 10),
        array('name_sv' => 'Mjölk', 'name_en' => 'Milk', 'sort_order' => 20),
        array('name_sv' => 'Ägg', 'name_en' => 'Egg', 'sort_order' => 30),
        array('name_sv' => 'Fisk', 'name_en' => 'Fish', 'sort_order' => 40),
        array('name_sv' => 'Skaldjur', 'name_en' => 'Shellfish', 'sort_order' => 50),
        array('name_sv' => 'Jordnötter', 'name_en' => 'Peanuts', 'sort_order' => 60),
        array('name_sv' => 'Nötter', 'name_en' => 'Nuts', 'sort_order' => 70),
        array('name_sv' => 'Soja', 'name_en' => 'Soy', 'sort_order' => 80),
        array('name_sv' => 'Selleri', 'name_en' => 'Celery', 'sort_order' => 90),
        array('name_sv' => 'Senap', 'name_en' => 'Mustard', 'sort_order' => 100),
    );
}

/**
 * Insert default allergens when the table is empty (fresh install).
 */
function maca_menulist_db_seed_default_allergens() {
    if (get_option('maca_menulist_default_allergens_seeded') === '1') {
        return;
    }

    global $wpdb;

    maca_menulist_db_ensure_allergen_columns();

    $table = maca_menulist_db_allergens_table();
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));

    if ($exists !== $table) {
        return;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $count = (int) $wpdb->get_var("SELECT COUNT(*) FROM $table");

    if ($count > 0) {
        update_option('maca_menulist_default_allergens_seeded', '1');
        return;
    }

    maca_menulist_db_restore_default_allergens();
}

/**
 * Normalize allergen label for matching defaults.
 *
 * @param string $value Allergen name.
 * @return string
 */
function maca_menulist_normalize_allergen_key($value) {
    $value = trim((string) $value);

    if ($value === '') {
        return '';
    }

    if (function_exists('mb_strtolower')) {
        return mb_strtolower($value, 'UTF-8');
    }

    return strtolower($value);
}

/**
 * Find an existing allergen row that matches a default entry.
 *
 * @param array<int, object>                                      $allergens Existing allergens.
 * @param array{name_sv: string, name_en: string, sort_order: int} $default   Default allergen row.
 * @return object|null
 */
function maca_menulist_db_find_allergen_for_default($allergens, $default) {
    $match_keys = array();

    foreach (array($default['name_sv'], $default['name_en']) as $label) {
        $key = maca_menulist_normalize_allergen_key($label);
        if ($key !== '') {
            $match_keys[] = $key;
        }
    }

    if (empty($match_keys)) {
        return null;
    }

    foreach ($allergens as $allergen) {
        $candidate_keys = array(
            maca_menulist_normalize_allergen_key(isset($allergen->name) ? $allergen->name : ''),
            maca_menulist_normalize_allergen_key(isset($allergen->name_en) ? $allergen->name_en : ''),
        );

        foreach ($match_keys as $match_key) {
            if (in_array($match_key, $candidate_keys, true)) {
                return $allergen;
            }
        }
    }

    return null;
}

/**
 * Restore EU default allergens (upsert). Custom allergens are kept.
 *
 * @return array{updated: int, added: int}
 */
function maca_menulist_db_restore_default_allergens() {
    maca_menulist_db_ensure_allergen_columns();

    global $wpdb;

    $table = maca_menulist_db_allergens_table();
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));

    if ($exists !== $table) {
        return array(
            'updated' => 0,
            'added' => 0,
        );
    }

    $allergens = maca_menulist_db_get_allergens();
    $updated = 0;
    $added = 0;

    foreach (maca_menulist_get_default_allergens() as $default) {
        $existing = maca_menulist_db_find_allergen_for_default($allergens, $default);
        $row = array(
            'name' => $default['name_sv'],
            'name_en' => $default['name_en'],
            'sort_order' => $default['sort_order'],
        );

        if ($existing) {
            maca_menulist_db_update_allergen((int) $existing->id, $row);
            $updated++;
            continue;
        }

        maca_menulist_db_insert_allergen($row);
        $added++;
    }

    update_option('maca_menulist_default_allergens_seeded', '1');

    return array(
        'updated' => $updated,
        'added' => $added,
    );
}

// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange
