<?php
/**
 * Database layer for table reservations (bordsbokning).
 *
 * @package Maca_Menu_List
 */

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

/**
 * Physical restaurant tables.
 *
 * @return string
 */
function maca_menulist_db_booking_tables_table() {
    global $wpdb;

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

/**
 * Guest table reservations.
 *
 * @return string
 */
function maca_menulist_db_bookings_table() {
    global $wpdb;

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

/**
 * Date-specific schedule exceptions.
 *
 * @return string
 */
function maca_menulist_db_booking_exceptions_table() {
    global $wpdb;

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

/**
 * Recurring weekday schedule exceptions within a date range.
 *
 * @return string
 */
function maca_menulist_db_booking_recurring_exceptions_table() {
    global $wpdb;

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

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

/**
 * Create table reservation database tables.
 */
function maca_menulist_db_create_table_booking_tables() {
    global $wpdb;

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

    $charset_collate = $wpdb->get_charset_collate();
    $tables_table = maca_menulist_db_booking_tables_table();
    $bookings_table = maca_menulist_db_bookings_table();
    $exceptions_table = maca_menulist_db_booking_exceptions_table();

    $sql_tables = "CREATE TABLE $tables_table (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        table_number varchar(50) NOT NULL,
        seats smallint(5) unsigned NOT NULL DEFAULT 2,
        is_splittable tinyint(1) NOT NULL DEFAULT 0,
        split_party_size smallint(5) unsigned NOT NULL DEFAULT 2,
        sort_order int(11) NOT NULL DEFAULT 0,
        is_active tinyint(1) NOT NULL DEFAULT 1,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        KEY is_active (is_active),
        KEY sort_order (sort_order)
    ) $charset_collate;";

    $sql_bookings = "CREATE TABLE $bookings_table (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        table_id bigint(20) unsigned DEFAULT NULL,
        booking_date date NOT NULL,
        booking_time time NOT NULL,
        party_size smallint(5) unsigned NOT NULL DEFAULT 2,
        guest_name varchar(255) NOT NULL,
        guest_email varchar(255) NOT NULL,
        guest_phone varchar(50) DEFAULT NULL,
        guest_note text DEFAULT NULL,
        status varchar(20) NOT NULL DEFAULT 'pending',
        guest_token varchar(64) DEFAULT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        status_updated_at datetime DEFAULT CURRENT_TIMESTAMP,
        confirmed_at datetime DEFAULT NULL,
        PRIMARY KEY  (id),
        UNIQUE KEY guest_token (guest_token),
        KEY table_id (table_id),
        KEY booking_date (booking_date),
        KEY status (status),
        KEY date_time (booking_date, booking_time)
    ) $charset_collate;";

    $sql_exceptions = "CREATE TABLE $exceptions_table (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        exception_date date NOT NULL,
        exception_type varchar(20) NOT NULL DEFAULT 'closed',
        time_start time DEFAULT NULL,
        time_end time DEFAULT NULL,
        note varchar(255) DEFAULT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        UNIQUE KEY exception_date (exception_date)
    ) $charset_collate;";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql_tables);
    dbDelta($sql_bookings);
    dbDelta($sql_exceptions);

    if (function_exists('maca_menulist_db_create_booking_log_table')) {
        maca_menulist_db_create_booking_log_table();
    }
}

/**
 * Ensure the table reservation tables exist.
 */
function maca_menulist_db_ensure_table_booking_tables() {
    global $wpdb;

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

    $required = array(
        maca_menulist_db_booking_tables_table(),
        maca_menulist_db_bookings_table(),
        maca_menulist_db_booking_exceptions_table(),
    );

    foreach ($required as $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_table_booking_tables();
            break;
        }
    }

    maca_menulist_db_ensure_booking_table_splittable_columns();

    if (function_exists('maca_menulist_db_ensure_booking_day_split_schema')) {
        maca_menulist_db_ensure_booking_day_split_schema();
    }

    maca_menulist_db_ensure_booking_recurring_exceptions_table();

    if (function_exists('maca_menulist_db_ensure_booking_log_table')) {
        maca_menulist_db_ensure_booking_log_table();
    }

    $ensured = true;
}

/**
 * Add splittable-table columns for admin floor overview.
 */
function maca_menulist_db_ensure_booking_table_splittable_columns() {
    global $wpdb;

    static $columns_ensured = false;

    if ($columns_ensured) {
        return;
    }

    $tables_table = maca_menulist_db_booking_tables_table();

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

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

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
    if (!$wpdb->get_var("SHOW COLUMNS FROM $tables_table LIKE 'split_party_size'")) {
        $wpdb->query("ALTER TABLE $tables_table ADD COLUMN split_party_size smallint(5) unsigned NOT NULL DEFAULT 2 AFTER is_splittable");
    }

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

    $columns_ensured = true;
}

/**
 * List all physical tables.
 *
 * @param bool $active_only Only active tables.
 * @return array<int, object>
 */
function maca_menulist_db_get_booking_tables($active_only = false) {
    global $wpdb;

    maca_menulist_db_ensure_table_booking_tables();

    $tables_table = maca_menulist_db_booking_tables_table();
    $sql = "SELECT * FROM $tables_table";

    if ($active_only) {
        $sql .= ' WHERE is_active = 1';
    }

    $sql .= ' ORDER BY sort_order ASC, table_number ASC';

    return $wpdb->get_results($sql);
}

/**
 * Get one physical table by ID.
 *
 * @param int $table_id Table ID.
 * @return object|null
 */
function maca_menulist_db_get_booking_table($table_id) {
    global $wpdb;

    maca_menulist_db_ensure_table_booking_tables();

    $table_id = absint($table_id);
    if ($table_id <= 0) {
        return null;
    }

    $tables_table = maca_menulist_db_booking_tables_table();

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

/**
 * Insert or update a physical table.
 *
 * @param array<string, mixed> $data Table data.
 * @return int|false Table ID or false.
 */
function maca_menulist_db_save_booking_table($data) {
    global $wpdb;

    maca_menulist_db_ensure_table_booking_tables();

    $tables_table = maca_menulist_db_booking_tables_table();
    $table_id = isset($data['id']) ? absint($data['id']) : 0;
    $seats = isset($data['seats']) ? max(1, (int) $data['seats']) : 2;
    $is_splittable = !empty($data['is_splittable']) ? 1 : 0;
    $split_party_size = isset($data['split_party_size']) ? max(1, (int) $data['split_party_size']) : 2;
    $split_slot_sizes = null;

    if ($is_splittable) {
        $split_party_size = min($seats, $split_party_size);

        if (array_key_exists('split_slot_sizes', $data)) {
            if (trim((string) $data['split_slot_sizes']) !== '') {
                $parsed = maca_menulist_parse_split_slot_sizes_input((string) $data['split_slot_sizes'], $seats);
                if (is_wp_error($parsed)) {
                    return $parsed;
                }
                $split_slot_sizes = maca_menulist_encode_split_slot_sizes($parsed);
                $split_party_size = min($split_party_size, min($parsed));
            } else {
                $split_slot_sizes = null;
            }
        }
    } else {
        $split_party_size = 2;
        $split_slot_sizes = null;
    }

    $row = array(
        'table_number' => isset($data['table_number']) ? sanitize_text_field($data['table_number']) : '',
        'seats' => $seats,
        'is_splittable' => $is_splittable,
        'split_party_size' => $split_party_size,
        'split_slot_sizes' => $split_slot_sizes,
        'sort_order' => isset($data['sort_order']) ? (int) $data['sort_order'] : 0,
        'is_active' => !empty($data['is_active']) ? 1 : 0,
    );
    $formats = array('%s', '%d', '%d', '%d', '%s', '%d', '%d');

    if ($row['table_number'] === '') {
        return false;
    }

    if ($table_id > 0) {
        $updated = $wpdb->update(
            $tables_table,
            $row,
            array('id' => $table_id),
            $formats,
            array('%d')
        );

        return $updated !== false ? $table_id : false;
    }

    $inserted = $wpdb->insert($tables_table, $row, $formats);

    return $inserted ? (int) $wpdb->insert_id : false;
}

/**
 * Delete a physical table.
 *
 * @param int $table_id Table ID.
 * @return bool
 */
function maca_menulist_db_delete_booking_table($table_id) {
    global $wpdb;

    maca_menulist_db_ensure_table_booking_tables();

    $table_id = absint($table_id);
    if ($table_id <= 0) {
        return false;
    }

    $tables_table = maca_menulist_db_booking_tables_table();
    $deleted = $wpdb->delete($tables_table, array('id' => $table_id), array('%d'));

    return $deleted !== false && $deleted > 0;
}

/**
 * Insert a guest booking.
 *
 * @param array<string, mixed> $data Booking data.
 * @return array{id: int, guest_token: string}|null
 */
function maca_menulist_db_insert_booking($data) {
    global $wpdb;

    maca_menulist_db_ensure_table_booking_tables();

    $bookings_table = maca_menulist_db_bookings_table();
    $guest_token = bin2hex(random_bytes(16));

    $table_id = isset($data['table_id']) ? absint($data['table_id']) : 0;
    $booking_date = isset($data['booking_date']) ? sanitize_text_field($data['booking_date']) : '';
    $booking_time = isset($data['booking_time']) ? maca_menulist_normalize_time($data['booking_time']) : null;
    $party_size = isset($data['party_size']) ? max(1, (int) $data['party_size']) : 2;

    if ($booking_date === '' || $booking_time === null) {
        return null;
    }

    $status = isset($data['status']) ? sanitize_key($data['status']) : 'pending';
    if (!maca_menulist_is_valid_booking_status($status)) {
        $status = 'pending';
    }

    $now = maca_menulist_wp_now_mysql();
    $booking_row = array(
        'table_id' => $table_id > 0 ? $table_id : null,
        'booking_date' => $booking_date,
        'booking_time' => $booking_time,
        'party_size' => $party_size,
        'guest_name' => isset($data['guest_name']) ? sanitize_text_field($data['guest_name']) : '',
        'guest_email' => isset($data['guest_email']) ? sanitize_email($data['guest_email']) : '',
        'guest_phone' => isset($data['guest_phone']) ? sanitize_text_field($data['guest_phone']) : '',
        'guest_note' => isset($data['guest_note']) ? sanitize_textarea_field($data['guest_note']) : '',
        'status' => $status,
        'guest_token' => $guest_token,
        'created_at' => $now,
        'status_updated_at' => $now,
    );
    $booking_formats = array('%d', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');

    if ($status === 'confirmed') {
        $booking_row['confirmed_at'] = $now;
        $booking_formats[] = '%s';
    }

    $inserted = $wpdb->insert(
        $bookings_table,
        $booking_row,
        $booking_formats
    );

    if (!$inserted) {
        return null;
    }

    return array(
        'id' => (int) $wpdb->insert_id,
        'guest_token' => $guest_token,
    );
}

/**
 * Get one booking by ID.
 *
 * @param int $booking_id Booking ID.
 * @return object|null
 */
function maca_menulist_db_get_booking($booking_id) {
    global $wpdb;

    maca_menulist_db_ensure_table_booking_tables();

    $booking_id = absint($booking_id);
    if ($booking_id <= 0) {
        return null;
    }

    $bookings_table = maca_menulist_db_bookings_table();
    $tables_table = maca_menulist_db_booking_tables_table();

    return $wpdb->get_row(
        $wpdb->prepare(
            "SELECT b.*, t.table_number, t.seats AS table_seats
            FROM $bookings_table b
            LEFT JOIN $tables_table t ON t.id = b.table_id
            WHERE b.id = %d",
            $booking_id
        )
    );
}

/**
 * Get one booking by guest token.
 *
 * @param string $guest_token Guest token.
 * @return object|null
 */
function maca_menulist_db_get_booking_by_token($guest_token) {
    global $wpdb;

    maca_menulist_db_ensure_table_booking_tables();

    $guest_token = sanitize_text_field($guest_token);
    if ($guest_token === '') {
        return null;
    }

    $bookings_table = maca_menulist_db_bookings_table();
    $tables_table = maca_menulist_db_booking_tables_table();

    return $wpdb->get_row(
        $wpdb->prepare(
            "SELECT b.*, t.table_number, t.seats AS table_seats
            FROM $bookings_table b
            LEFT JOIN $tables_table t ON t.id = b.table_id
            WHERE b.guest_token = %s",
            $guest_token
        )
    );
}

/**
 * List bookings for admin.
 *
 * @param array<string, mixed> $args Query args.
 * @return array<int, object>
 */
function maca_menulist_db_list_bookings($args = array()) {
    global $wpdb;

    maca_menulist_db_ensure_table_booking_tables();

    $defaults = array(
        'status' => 'all',
        'date_from' => '',
        'date_to' => '',
        'paged' => 1,
        'per_page' => 20,
    );
    $args = wp_parse_args($args, $defaults);

    $bookings_table = maca_menulist_db_bookings_table();
    $tables_table = maca_menulist_db_booking_tables_table();
    $where = array('1=1');
    $params = array();

    $status = sanitize_key((string) $args['status']);
    if ($status !== 'all' && maca_menulist_is_valid_booking_status($status)) {
        $where[] = 'b.status = %s';
        $params[] = $status;
    }

    if (!empty($args['date_from'])) {
        $where[] = 'b.booking_date >= %s';
        $params[] = sanitize_text_field($args['date_from']);
    }

    if (!empty($args['date_to'])) {
        $where[] = 'b.booking_date <= %s';
        $params[] = sanitize_text_field($args['date_to']);
    }

    $where_sql = implode(' AND ', $where);
    $per_page = max(1, min(100, (int) $args['per_page']));
    $paged = max(1, (int) $args['paged']);
    $offset = ($paged - 1) * $per_page;

    $sql = "SELECT b.*, t.table_number, t.seats AS table_seats
            FROM $bookings_table b
            LEFT JOIN $tables_table t ON t.id = b.table_id
            WHERE $where_sql
            ORDER BY b.booking_date ASC, b.booking_time ASC, b.id ASC
            LIMIT %d OFFSET %d";

    $params[] = $per_page;
    $params[] = $offset;

    return $wpdb->get_results($wpdb->prepare($sql, ...$params));
}

/**
 * Count bookings matching filters.
 *
 * @param array<string, mixed> $args Query args.
 * @return int
 */
function maca_menulist_db_count_bookings($args = array()) {
    global $wpdb;

    maca_menulist_db_ensure_table_booking_tables();

    $bookings_table = maca_menulist_db_bookings_table();
    $where = array('1=1');
    $params = array();

    $status = isset($args['status']) ? sanitize_key((string) $args['status']) : 'all';
    if ($status !== 'all' && maca_menulist_is_valid_booking_status($status)) {
        $where[] = 'status = %s';
        $params[] = $status;
    }

    if (!empty($args['date_from'])) {
        $where[] = 'booking_date >= %s';
        $params[] = sanitize_text_field($args['date_from']);
    }

    if (!empty($args['date_to'])) {
        $where[] = 'booking_date <= %s';
        $params[] = sanitize_text_field($args['date_to']);
    }

    $where_sql = implode(' AND ', $where);
    $sql = "SELECT COUNT(*) FROM $bookings_table WHERE $where_sql";

    if (!empty($params)) {
        $sql = $wpdb->prepare($sql, ...$params);
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
    return (int) $wpdb->get_var($sql);
}

/**
 * Booking counts for a single date (admin overview).
 *
 * @param string $date Date Y-m-d.
 * @return array{
 *   confirmed_guests: int,
 *   pending_guests: int,
 *   waitlist_guests: int,
 *   pending_count: int,
 *   waitlist_count: int
 * }
 */
function maca_menulist_db_get_booking_day_stats($date) {
    global $wpdb;

    static $cache = array();

    maca_menulist_db_ensure_table_booking_tables();

    $date = sanitize_text_field($date);
    if ($date === '') {
        return array(
            'confirmed_guests' => 0,
            'pending_guests'   => 0,
            'waitlist_guests'  => 0,
            'pending_count'    => 0,
            'waitlist_count'   => 0,
        );
    }

    if (isset($cache[ $date ])) {
        return $cache[ $date ];
    }

    $bookings_table = maca_menulist_db_bookings_table();

    $confirmed_guests = (int) $wpdb->get_var(
        $wpdb->prepare(
            "SELECT COALESCE(SUM(party_size), 0) FROM $bookings_table WHERE booking_date = %s AND status = %s",
            $date,
            'confirmed'
        )
    );

    $pending_guests = (int) $wpdb->get_var(
        $wpdb->prepare(
            "SELECT COALESCE(SUM(party_size), 0) FROM $bookings_table WHERE booking_date = %s AND status = %s",
            $date,
            'pending'
        )
    );

    $waitlist_guests = (int) $wpdb->get_var(
        $wpdb->prepare(
            "SELECT COALESCE(SUM(party_size), 0) FROM $bookings_table WHERE booking_date = %s AND status = %s",
            $date,
            'waitlist'
        )
    );

    $pending_count = (int) $wpdb->get_var(
        $wpdb->prepare(
            "SELECT COUNT(*) FROM $bookings_table WHERE booking_date = %s AND status = %s",
            $date,
            'pending'
        )
    );

    $waitlist_count = (int) $wpdb->get_var(
        $wpdb->prepare(
            "SELECT COUNT(*) FROM $bookings_table WHERE booking_date = %s AND status = %s",
            $date,
            'waitlist'
        )
    );

    $cache[ $date ] = array(
        'confirmed_guests' => $confirmed_guests,
        'pending_guests'   => $pending_guests,
        'waitlist_guests'  => $waitlist_guests,
        'pending_count'    => $pending_count,
        'waitlist_count'   => $waitlist_count,
    );

    return $cache[ $date ];
}

/**
 * Get bookings that block a table on a given date.
 *
 * @param string $date       Booking date Y-m-d.
 * @param int    $table_id   Table ID.
 * @return array<int, object>
 */
function maca_menulist_db_get_table_bookings_for_date($date, $table_id) {
    global $wpdb;

    maca_menulist_db_ensure_table_booking_tables();

    $date = sanitize_text_field($date);
    $table_id = absint($table_id);
    if ($date === '' || $table_id <= 0) {
        return array();
    }

    $bookings_table = maca_menulist_db_bookings_table();
    $blocking_statuses = maca_menulist_get_blocking_booking_statuses();
    $placeholders = implode(',', array_fill(0, count($blocking_statuses), '%s'));
    $params = array_merge(array($date, $table_id), $blocking_statuses);

    return $wpdb->get_results(
        $wpdb->prepare(
            "SELECT * FROM $bookings_table
            WHERE booking_date = %s AND table_id = %d AND status IN ($placeholders)
            ORDER BY booking_time ASC",
            ...$params
        )
    );
}

/**
 * Clear table assignment on cancelled bookings (legacy rows or missed updates).
 *
 * @param string $date Optional booking date Y-m-d to limit the repair.
 * @return void
 */
function maca_menulist_db_repair_cancelled_booking_table_assignments($date = '') {
    global $wpdb;

    maca_menulist_db_ensure_table_booking_tables();

    if (function_exists('maca_menulist_db_ensure_booking_day_split_schema')) {
        maca_menulist_db_ensure_booking_day_split_schema();
    }

    $bookings_table = maca_menulist_db_bookings_table();
    $date = sanitize_text_field($date);
    $now = maca_menulist_wp_now_mysql();

    if ($date !== '') {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
        $wpdb->query(
            $wpdb->prepare(
                "UPDATE $bookings_table
                SET table_id = NULL, split_slot = NULL, status_updated_at = %s
                WHERE status = %s AND table_id IS NOT NULL AND booking_date = %s",
                $now,
                'cancelled',
                $date
            )
        );

        return;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
    $wpdb->query(
        $wpdb->prepare(
            "UPDATE $bookings_table
            SET table_id = NULL, split_slot = NULL, status_updated_at = %s
            WHERE status = %s AND table_id IS NOT NULL",
            $now,
            'cancelled'
        )
    );
}

/**
 * Update booking status and optional table assignment.
 *
 * @param int         $booking_id Booking ID.
 * @param string      $status     New status.
 * @param int|null    $table_id   Optional table ID.
 * @return bool
 */
function maca_menulist_db_update_booking_status($booking_id, $status, $table_id = null) {
    global $wpdb;

    maca_menulist_db_ensure_table_booking_tables();

    $booking_id = absint($booking_id);
    $status = sanitize_key($status);

    if ($booking_id <= 0 || !maca_menulist_is_valid_booking_status($status)) {
        return false;
    }

    $bookings_table = maca_menulist_db_bookings_table();

    // Cancelled bookings must leave the table so the floor overview shows it as free again.
    if ($status === 'cancelled') {
        if (function_exists('maca_menulist_db_ensure_booking_day_split_schema')) {
            maca_menulist_db_ensure_booking_day_split_schema();
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
        return false !== $wpdb->query(
            $wpdb->prepare(
                "UPDATE $bookings_table
                SET status = %s, status_updated_at = %s, table_id = NULL, split_slot = NULL
                WHERE id = %d",
                $status,
                maca_menulist_wp_now_mysql(),
                $booking_id
            )
        );
    }

    $data = array(
        'status' => $status,
        'status_updated_at' => maca_menulist_wp_now_mysql(),
    );
    $formats = array('%s', '%s');

    if ($table_id !== null) {
        $table_id = absint($table_id);
        if ($table_id > 0) {
            $data['table_id'] = $table_id;
            $formats[] = '%d';
        } else {
            if (function_exists('maca_menulist_db_ensure_booking_day_split_schema')) {
                maca_menulist_db_ensure_booking_day_split_schema();
            }

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
            return false !== $wpdb->query(
                $wpdb->prepare(
                    "UPDATE $bookings_table
                    SET status = %s, status_updated_at = %s, table_id = NULL, split_slot = NULL
                    WHERE id = %d",
                    $status,
                    maca_menulist_wp_now_mysql(),
                    $booking_id
                )
            );
        }
    }

    if ($status === 'confirmed') {
        $data['confirmed_at'] = maca_menulist_wp_now_mysql();
        $formats[] = '%s';
    }

    return (bool) $wpdb->update(
        $bookings_table,
        $data,
        array('id' => $booking_id),
        $formats,
        array('%d')
    );
}

/**
 * Update only the assigned table for a booking.
 *
 * @param int $booking_id Booking ID.
 * @param int $table_id     Table ID.
 * @return bool
 */
function maca_menulist_db_update_booking_table($booking_id, $table_id, $split_slot = null) {
    global $wpdb;

    maca_menulist_db_ensure_table_booking_tables();

    $booking_id = absint($booking_id);
    $table_id = absint($table_id);

    if ($booking_id <= 0 || $table_id <= 0) {
        return false;
    }

    $bookings_table = maca_menulist_db_bookings_table();
    $data = array(
        'table_id' => $table_id,
        'status_updated_at' => maca_menulist_wp_now_mysql(),
    );
    $formats = array('%d', '%s');

    if ($split_slot !== null) {
        if (function_exists('maca_menulist_normalize_split_slot')) {
            $split_slot = maca_menulist_normalize_split_slot((string) $split_slot);
        } else {
            $split_slot = '';
        }

        $data['split_slot'] = $split_slot !== '' ? $split_slot : null;
        $formats[] = '%s';
    }

    return (bool) $wpdb->update(
        $bookings_table,
        $data,
        array('id' => $booking_id),
        $formats,
        array('%d')
    );
}

/**
 * Update party size on a booking.
 *
 * @param int $booking_id Booking ID.
 * @param int $party_size New party size.
 * @return bool
 */
function maca_menulist_db_update_booking_party_size($booking_id, $party_size) {
    return maca_menulist_db_update_booking_fields(
        $booking_id,
        array(
            'party_size' => $party_size,
        )
    );
}

/**
 * Update editable reservation columns.
 *
 * @param int                  $booking_id Booking ID.
 * @param array<string, mixed> $fields     Allowed keys: booking_date, booking_time, party_size, guest_name, guest_email, guest_phone, guest_note.
 * @return bool
 */
function maca_menulist_db_update_booking_fields($booking_id, $fields) {
    global $wpdb;

    maca_menulist_db_ensure_table_booking_tables();

    $booking_id = absint($booking_id);
    if ($booking_id <= 0 || empty($fields)) {
        return false;
    }

    $allowed = array(
        'booking_date' => '%s',
        'booking_time' => '%s',
        'party_size' => '%d',
        'guest_name' => '%s',
        'guest_email' => '%s',
        'guest_phone' => '%s',
        'guest_note' => '%s',
    );

    $data = array();
    $formats = array();

    foreach ($fields as $key => $value) {
        if (!isset($allowed[$key])) {
            continue;
        }

        switch ($key) {
            case 'booking_date':
                $data[$key] = sanitize_text_field((string) $value);
                break;
            case 'booking_time':
                $normalized = maca_menulist_normalize_time((string) $value);
                if ($normalized === null) {
                    return false;
                }
                $data[$key] = $normalized;
                break;
            case 'party_size':
                $data[$key] = max(1, (int) $value);
                break;
            case 'guest_name':
                $data[$key] = sanitize_text_field((string) $value);
                break;
            case 'guest_email':
                $data[$key] = sanitize_email((string) $value);
                break;
            case 'guest_phone':
                $data[$key] = sanitize_text_field((string) $value);
                break;
            case 'guest_note':
                $data[$key] = sanitize_textarea_field((string) $value);
                break;
        }

        $formats[] = $allowed[$key];
    }

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

    $data['status_updated_at'] = maca_menulist_wp_now_mysql();
    $formats[] = '%s';

    $bookings_table = maca_menulist_db_bookings_table();

    return (bool) $wpdb->update(
        $bookings_table,
        $data,
        array('id' => $booking_id),
        $formats,
        array('%d')
    );
}

/**
 * Delete a booking.
 *
 * @param int $booking_id Booking ID.
 * @return bool
 */
function maca_menulist_db_delete_booking($booking_id) {
    global $wpdb;

    maca_menulist_db_ensure_table_booking_tables();

    $booking_id = absint($booking_id);
    if ($booking_id <= 0) {
        return false;
    }

    $bookings_table = maca_menulist_db_bookings_table();
    $deleted = $wpdb->delete($bookings_table, array('id' => $booking_id), array('%d'));

    return $deleted !== false && $deleted > 0;
}

/**
 * List schedule exceptions.
 *
 * @param string $from Optional start date.
 * @param string $to   Optional end date.
 * @return array<int, object>
 */
function maca_menulist_db_get_booking_exceptions($from = '', $to = '') {
    global $wpdb;

    maca_menulist_db_ensure_table_booking_tables();

    $exceptions_table = maca_menulist_db_booking_exceptions_table();
    $where = array('1=1');
    $params = array();

    if ($from !== '') {
        $where[] = 'exception_date >= %s';
        $params[] = sanitize_text_field($from);
    }

    if ($to !== '') {
        $where[] = 'exception_date <= %s';
        $params[] = sanitize_text_field($to);
    }

    $where_sql = implode(' AND ', $where);
    $sql = "SELECT * FROM $exceptions_table WHERE $where_sql ORDER BY exception_date ASC";

    if (!empty($params)) {
        $sql = $wpdb->prepare($sql, ...$params);
    }

    return $wpdb->get_results($sql);
}

/**
 * Get exception for a specific date.
 *
 * @param string $date Date Y-m-d.
 * @return object|null
 */
function maca_menulist_db_get_booking_exception_for_date($date) {
    global $wpdb;

    maca_menulist_db_ensure_table_booking_tables();

    $date = sanitize_text_field($date);
    if ($date === '') {
        return null;
    }

    $exceptions_table = maca_menulist_db_booking_exceptions_table();

    return $wpdb->get_row(
        $wpdb->prepare("SELECT * FROM $exceptions_table WHERE exception_date = %s", $date)
    );
}

/**
 * Save or update a schedule exception.
 *
 * @param array<string, mixed> $data Exception data.
 * @return int|false
 */
function maca_menulist_db_save_booking_exception($data) {
    global $wpdb;

    maca_menulist_db_ensure_table_booking_tables();

    $exceptions_table = maca_menulist_db_booking_exceptions_table();
    $exception_id = isset($data['id']) ? absint($data['id']) : 0;
    $exception_date = isset($data['exception_date']) ? sanitize_text_field($data['exception_date']) : '';
    $exception_type = isset($data['exception_type']) ? sanitize_key($data['exception_type']) : 'closed';

    if ($exception_date === '' || !maca_menulist_is_valid_booking_exception_type($exception_type)) {
        return false;
    }

    $row = array(
        'exception_date' => $exception_date,
        'exception_type' => $exception_type,
        'time_start' => !empty($data['time_start']) ? maca_menulist_normalize_time($data['time_start']) : null,
        'time_end' => !empty($data['time_end']) ? maca_menulist_normalize_time($data['time_end']) : null,
        'note' => isset($data['note']) ? sanitize_text_field($data['note']) : '',
    );
    $formats = array('%s', '%s', '%s', '%s', '%s');

    if ($exception_id > 0) {
        $updated = $wpdb->update(
            $exceptions_table,
            $row,
            array('id' => $exception_id),
            $formats,
            array('%d')
        );

        return $updated !== false ? $exception_id : false;
    }

    $inserted = $wpdb->insert($exceptions_table, $row, $formats);

    return $inserted ? (int) $wpdb->insert_id : false;
}

/**
 * Delete a schedule exception.
 *
 * @param int $exception_id Exception ID.
 * @return bool
 */
function maca_menulist_db_delete_booking_exception($exception_id) {
    global $wpdb;

    maca_menulist_db_ensure_table_booking_tables();

    $exception_id = absint($exception_id);
    if ($exception_id <= 0) {
        return false;
    }

    $exceptions_table = maca_menulist_db_booking_exceptions_table();
    $deleted = $wpdb->delete($exceptions_table, array('id' => $exception_id), array('%d'));

    return $deleted !== false && $deleted > 0;
}

/**
 * Ensure recurring weekday exceptions table exists.
 */
function maca_menulist_db_ensure_booking_recurring_exceptions_table() {
    global $wpdb;

    static $done = false;

    if ($done) {
        return;
    }

    $table = maca_menulist_db_booking_recurring_exceptions_table();
    $charset_collate = $wpdb->get_charset_collate();

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table)) !== $table) {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange
        $wpdb->query(
            "CREATE TABLE $table (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                date_from date NOT NULL,
                date_to date NOT NULL,
                days_of_week varchar(32) NOT NULL,
                exception_type varchar(20) NOT NULL DEFAULT 'closed',
                time_start time DEFAULT NULL,
                time_end time DEFAULT NULL,
                note varchar(255) DEFAULT NULL,
                created_at datetime DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                KEY date_range (date_from, date_to)
            ) $charset_collate"
        );
    }

    $done = true;
}

/**
 * List recurring schedule exceptions (active or future by default).
 *
 * @param string $from Optional start date filter (matches overlapping periods).
 * @param bool   $include_past Include rules whose end date is before today.
 * @return array<int, object>
 */
function maca_menulist_db_get_booking_recurring_exceptions($from = '', $include_past = false) {
    global $wpdb;

    maca_menulist_db_ensure_booking_recurring_exceptions_table();

    $table = maca_menulist_db_booking_recurring_exceptions_table();
    $where = array('1=1');
    $params = array();

    if (!$include_past) {
        $where[] = 'date_to >= %s';
        $params[] = wp_date('Y-m-d');
    }

    if ($from !== '') {
        $where[] = 'date_to >= %s';
        $params[] = sanitize_text_field($from);
    }

    $where_sql = implode(' AND ', $where);
    $sql = "SELECT * FROM $table WHERE $where_sql ORDER BY date_from ASC, id ASC";

    if (!empty($params)) {
        $sql = $wpdb->prepare($sql, ...$params);
    }

    return $wpdb->get_results($sql);
}

/**
 * Get one recurring exception by ID.
 *
 * @param int $exception_id Recurring exception ID.
 * @return object|null
 */
function maca_menulist_db_get_booking_recurring_exception($exception_id) {
    global $wpdb;

    maca_menulist_db_ensure_booking_recurring_exceptions_table();

    $exception_id = absint($exception_id);
    if ($exception_id <= 0) {
        return null;
    }

    $table = maca_menulist_db_booking_recurring_exceptions_table();

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

/**
 * Recurring exception matching a specific date (weekday + period).
 *
 * @param string $date Date Y-m-d.
 * @return object|null
 */
function maca_menulist_db_get_recurring_booking_exception_for_date($date) {
    global $wpdb;

    maca_menulist_db_ensure_booking_recurring_exceptions_table();

    $date = sanitize_text_field($date);
    if ($date === '' || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
        return null;
    }

    $table = maca_menulist_db_booking_recurring_exceptions_table();
    $day = (int) wp_date('w', (int) maca_menulist_wp_date_to_timestamp($date));

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $rows = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT * FROM $table WHERE date_from <= %s AND date_to >= %s ORDER BY id DESC",
            $date,
            $date
        )
    );

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

    foreach ($rows as $row) {
        $days = maca_menulist_parse_days_of_week($row->days_of_week ?? '');
        if (!empty($days) && in_array($day, $days, true)) {
            return $row;
        }
    }

    return null;
}

/**
 * Save or update a recurring schedule exception.
 *
 * @param array<string, mixed> $data Exception data.
 * @return int|false
 */
function maca_menulist_db_save_booking_recurring_exception($data) {
    global $wpdb;

    maca_menulist_db_ensure_booking_recurring_exceptions_table();

    $table = maca_menulist_db_booking_recurring_exceptions_table();
    $exception_id = isset($data['id']) ? absint($data['id']) : 0;
    $date_from = isset($data['date_from']) ? sanitize_text_field($data['date_from']) : '';
    $date_to = isset($data['date_to']) ? sanitize_text_field($data['date_to']) : '';
    $exception_type = isset($data['exception_type']) ? sanitize_key($data['exception_type']) : 'closed';
    $days_raw = isset($data['days_of_week']) ? $data['days_of_week'] : array();
    $days_of_week = maca_menulist_format_days_of_week(is_array($days_raw) ? $days_raw : array());

    if (
        $date_from === ''
        || $date_to === ''
        || $date_from > $date_to
        || $days_of_week === null
        || !maca_menulist_is_valid_booking_exception_type($exception_type)
    ) {
        return false;
    }

    $time_start = !empty($data['time_start']) ? maca_menulist_normalize_time($data['time_start']) : null;
    $time_end = !empty($data['time_end']) ? maca_menulist_normalize_time($data['time_end']) : null;

    if ($exception_type === 'custom_hours' && (!$time_start || !$time_end)) {
        return false;
    }

    $row = array(
        'date_from' => $date_from,
        'date_to' => $date_to,
        'days_of_week' => $days_of_week,
        'exception_type' => $exception_type,
        'time_start' => $time_start,
        'time_end' => $time_end,
        'note' => isset($data['note']) ? sanitize_text_field($data['note']) : '',
    );
    $formats = array('%s', '%s', '%s', '%s', '%s', '%s', '%s');

    if ($exception_id > 0) {
        $updated = $wpdb->update(
            $table,
            $row,
            array('id' => $exception_id),
            $formats,
            array('%d')
        );

        return $updated !== false ? $exception_id : false;
    }

    $inserted = $wpdb->insert($table, $row, $formats);

    return $inserted ? (int) $wpdb->insert_id : false;
}

/**
 * Delete a recurring schedule exception.
 *
 * @param int $exception_id Recurring exception ID.
 * @return bool
 */
function maca_menulist_db_delete_booking_recurring_exception($exception_id) {
    global $wpdb;

    maca_menulist_db_ensure_booking_recurring_exceptions_table();

    $exception_id = absint($exception_id);
    if ($exception_id <= 0) {
        return false;
    }

    $table = maca_menulist_db_booking_recurring_exceptions_table();
    $deleted = $wpdb->delete($table, array('id' => $exception_id), array('%d'));

    return $deleted !== false && $deleted > 0;
}

/**
 * Drop table reservation tables on uninstall.
 */
function maca_menulist_db_drop_table_booking_tables() {
    global $wpdb;

    foreach (array(
        maca_menulist_db_bookings_table(),
        maca_menulist_db_booking_recurring_exceptions_table(),
        maca_menulist_db_booking_exceptions_table(),
        maca_menulist_db_booking_tables_table(),
    ) as $table) {
        $wpdb->query("DROP TABLE IF EXISTS {$table}");
    }
}

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