<?php
/**
 * Database layer for guest table orders.
 *
 * @package Maca_Menu_List
 */

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

/**
 * Table orders table name.
 *
 * @return string
 */
function maca_menulist_db_table_orders_table() {
    global $wpdb;

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

/**
 * Table order lines table name.
 *
 * @return string
 */
function maca_menulist_db_table_order_lines_table() {
    global $wpdb;

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

// 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 order database tables.
 */
function maca_menulist_db_create_table_order_tables() {
    global $wpdb;

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

    $charset_collate = $wpdb->get_charset_collate();
    $orders_table = maca_menulist_db_table_orders_table();
    $lines_table = maca_menulist_db_table_order_lines_table();

    $sql_orders = "CREATE TABLE $orders_table (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        table_number varchar(50) DEFAULT NULL,
        service_mode varchar(20) NOT NULL DEFAULT '',
        pickup_name varchar(100) DEFAULT NULL,
        pickup_phone varchar(50) DEFAULT NULL,
        order_note text DEFAULT NULL,
        status varchar(20) NOT NULL DEFAULT 'new',
        guest_token varchar(64) DEFAULT NULL,
        eta_minutes smallint(5) unsigned DEFAULT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        status_updated_at datetime DEFAULT CURRENT_TIMESTAMP,
        handled_at datetime DEFAULT NULL,
        payment_provider varchar(20) DEFAULT NULL,
        payment_method varchar(20) DEFAULT NULL,
        payment_status varchar(20) NOT NULL DEFAULT 'none',
        payment_external_id varchar(255) DEFAULT NULL,
        payment_amount decimal(10,2) DEFAULT NULL,
        payment_currency varchar(3) DEFAULT 'SEK',
        paid_at datetime DEFAULT NULL,
        tip_amount decimal(10,2) NOT NULL DEFAULT 0,
        tip_confirmed tinyint(1) NOT NULL DEFAULT 0,
        bill_split_mode varchar(10) NOT NULL DEFAULT '',
        bill_split_count tinyint(3) unsigned NOT NULL DEFAULT 0,
        bill_split_fixed_amount decimal(10,2) NOT NULL DEFAULT 0,
        bill_split_paid_shares tinyint(3) unsigned NOT NULL DEFAULT 0,
        PRIMARY KEY  (id),
        UNIQUE KEY guest_token (guest_token),
        KEY status (status),
        KEY created_at (created_at)
    ) $charset_collate;";

    $sql_lines = "CREATE TABLE $lines_table (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        order_id bigint(20) unsigned NOT NULL,
        dish_id mediumint(9) DEFAULT NULL,
        title varchar(255) NOT NULL,
        price decimal(10,2) DEFAULT NULL,
        qty int(11) NOT NULL DEFAULT 1,
        line_note text DEFAULT NULL,
        sort_order int(11) NOT NULL DEFAULT 0,
        PRIMARY KEY  (id),
        KEY order_id (order_id)
    ) $charset_collate;";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql_orders);
    dbDelta($sql_lines);

    maca_menulist_db_migrate_table_order_schema();
}

/**
 * Add missing columns on existing installs.
 */
function maca_menulist_db_migrate_table_order_schema() {
    global $wpdb;

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

    if ($table_exists !== $orders_table) {
        return;
    }

    if (!$wpdb->get_var("SHOW COLUMNS FROM $orders_table LIKE 'guest_token'")) {
        $wpdb->query("ALTER TABLE $orders_table ADD COLUMN guest_token varchar(64) DEFAULT NULL AFTER status");
        $wpdb->query("ALTER TABLE $orders_table ADD UNIQUE KEY guest_token (guest_token)");
    }

    if (!$wpdb->get_var("SHOW COLUMNS FROM $orders_table LIKE 'eta_minutes'")) {
        $wpdb->query("ALTER TABLE $orders_table ADD COLUMN eta_minutes smallint(5) unsigned DEFAULT NULL AFTER guest_token");
    }

    if (!$wpdb->get_var("SHOW COLUMNS FROM $orders_table LIKE 'status_updated_at'")) {
        $wpdb->query("ALTER TABLE $orders_table ADD COLUMN status_updated_at datetime DEFAULT CURRENT_TIMESTAMP AFTER created_at");
    }

    if (!$wpdb->get_var("SHOW COLUMNS FROM $orders_table LIKE 'payment_provider'")) {
        $wpdb->query("ALTER TABLE $orders_table ADD COLUMN payment_provider varchar(20) DEFAULT NULL AFTER handled_at");
    }

    if (!$wpdb->get_var("SHOW COLUMNS FROM $orders_table LIKE 'payment_method'")) {
        $wpdb->query("ALTER TABLE $orders_table ADD COLUMN payment_method varchar(20) DEFAULT NULL AFTER payment_provider");
    }

    if (!$wpdb->get_var("SHOW COLUMNS FROM $orders_table LIKE 'payment_status'")) {
        $wpdb->query("ALTER TABLE $orders_table ADD COLUMN payment_status varchar(20) NOT NULL DEFAULT 'none' AFTER payment_method");
        $wpdb->query("ALTER TABLE $orders_table ADD COLUMN payment_external_id varchar(255) DEFAULT NULL AFTER payment_status");
        $wpdb->query("ALTER TABLE $orders_table ADD COLUMN payment_amount decimal(10,2) DEFAULT NULL AFTER payment_external_id");
        $wpdb->query("ALTER TABLE $orders_table ADD COLUMN payment_currency varchar(3) DEFAULT 'SEK' AFTER payment_amount");
        $wpdb->query("ALTER TABLE $orders_table ADD COLUMN paid_at datetime DEFAULT NULL AFTER payment_currency");
    } elseif (!$wpdb->get_var("SHOW COLUMNS FROM $orders_table LIKE 'payment_method'")) {
        $wpdb->query("ALTER TABLE $orders_table ADD COLUMN payment_method varchar(20) DEFAULT NULL AFTER payment_provider");
    }

    if (!$wpdb->get_var("SHOW COLUMNS FROM $orders_table LIKE 'tip_amount'")) {
        $wpdb->query("ALTER TABLE $orders_table ADD COLUMN tip_amount decimal(10,2) NOT NULL DEFAULT 0 AFTER paid_at");
    }

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

    if (!$wpdb->get_var("SHOW COLUMNS FROM $orders_table LIKE 'bill_split_count'")) {
        $wpdb->query("ALTER TABLE $orders_table ADD COLUMN bill_split_count tinyint(3) unsigned NOT NULL DEFAULT 0 AFTER tip_confirmed");
        $wpdb->query("ALTER TABLE $orders_table ADD COLUMN bill_split_paid_shares tinyint(3) unsigned NOT NULL DEFAULT 0 AFTER bill_split_count");
    }

    if (!$wpdb->get_var("SHOW COLUMNS FROM $orders_table LIKE 'bill_split_mode'")) {
        $wpdb->query("ALTER TABLE $orders_table ADD COLUMN bill_split_mode varchar(10) NOT NULL DEFAULT '' AFTER tip_confirmed");
        $wpdb->query("ALTER TABLE $orders_table ADD COLUMN bill_split_fixed_amount decimal(10,2) NOT NULL DEFAULT 0 AFTER bill_split_count");
        $wpdb->query(
            "UPDATE $orders_table SET bill_split_mode = 'equal' WHERE bill_split_count >= 2 AND (bill_split_mode IS NULL OR bill_split_mode = '')"
        );
    }

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

    if (!$wpdb->get_var("SHOW COLUMNS FROM $orders_table LIKE 'pickup_name'")) {
        $wpdb->query("ALTER TABLE $orders_table ADD COLUMN pickup_name varchar(100) DEFAULT NULL AFTER service_mode");
    }

    if (!$wpdb->get_var("SHOW COLUMNS FROM $orders_table LIKE 'pickup_phone'")) {
        $wpdb->query("ALTER TABLE $orders_table ADD COLUMN pickup_phone varchar(50) DEFAULT NULL AFTER pickup_name");
    }

    maca_menulist_db_migrate_partial_payment_statuses();

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

/**
 * Normalize payment_status for orders with partial amounts (legacy data).
 */
function maca_menulist_db_migrate_partial_payment_statuses() {
    if (get_option('maca_menulist_payment_partial_migrated', '') === '1') {
        return;
    }

    global $wpdb;

    $orders_table = maca_menulist_db_table_orders_table();
    $lines_table = maca_menulist_db_table_order_lines_table();

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $wpdb->query(
        "UPDATE $orders_table o
        LEFT JOIN (
            SELECT order_id, COALESCE(SUM(qty * COALESCE(price, 0)), 0) AS lines_total
            FROM $lines_table
            GROUP BY order_id
        ) lt ON lt.order_id = o.id
        SET o.payment_status = 'partial'
        WHERE o.payment_status IN ('paid', 'none')
        AND COALESCE(o.payment_amount, 0) > 0
        AND (COALESCE(lt.lines_total, 0) + COALESCE(o.tip_amount, 0)) > COALESCE(o.payment_amount, 0) + 0.01"
    );

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

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

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

    $table = maca_menulist_db_table_orders_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_order_tables();
    } else {
        maca_menulist_db_migrate_table_order_schema();
    }

    $ensured = true;
}

/**
 * Insert a guest table order with line items.
 *
 * @param array<string, mixed>              $order_data Order fields.
 * @param array<int, array<string, mixed>>  $lines      Line items.
 * @return array{id: int, guest_token: string}|null
 */
function maca_menulist_db_insert_table_order($order_data, $lines) {
    global $wpdb;

    maca_menulist_db_ensure_table_order_tables();

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

    $orders_table = maca_menulist_db_table_orders_table();
    $lines_table = maca_menulist_db_table_order_lines_table();

    $table_number = isset($order_data['table_number']) ? sanitize_text_field($order_data['table_number']) : '';
    $service_mode = isset($order_data['service_mode']) ? sanitize_key($order_data['service_mode']) : '';
    if ($service_mode === '' && function_exists('maca_menulist_get_default_order_service_mode')) {
        $service_mode = maca_menulist_get_default_order_service_mode();
    }
    if (!in_array($service_mode, array('dine_in', 'takeaway'), true)) {
        $service_mode = 'dine_in';
    }
    $pickup_name = isset($order_data['pickup_name']) ? sanitize_text_field($order_data['pickup_name']) : '';
    $pickup_phone = isset($order_data['pickup_phone']) ? sanitize_text_field($order_data['pickup_phone']) : '';
    $order_note = isset($order_data['order_note']) ? sanitize_textarea_field($order_data['order_note']) : '';
    $guest_token = bin2hex(random_bytes(16));
    $now = maca_menulist_wp_now_mysql();

    $inserted = $wpdb->insert(
        $orders_table,
        array(
            'table_number' => $table_number,
            'service_mode' => $service_mode,
            'pickup_name' => $pickup_name !== '' ? $pickup_name : null,
            'pickup_phone' => $pickup_phone !== '' ? $pickup_phone : null,
            'order_note' => $order_note,
            'status' => 'new',
            'guest_token' => $guest_token,
            'created_at' => $now,
            'status_updated_at' => $now,
        ),
        array('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')
    );

    if (!$inserted) {
        return null;
    }

    $order_id = (int) $wpdb->insert_id;
    $sort_order = 0;

    foreach ($lines as $line) {
        $qty = isset($line['qty']) ? max(1, (int) $line['qty']) : 1;
        $title = isset($line['title']) ? sanitize_text_field($line['title']) : '';
        $line_note = isset($line['note']) ? sanitize_text_field($line['note']) : '';
        $dish_id = isset($line['id']) ? intval($line['id']) : 0;
        $price = null;

        if (isset($line['price']) && $line['price'] !== '' && $line['price'] !== null) {
            $price = (float) $line['price'];
        }

        if ($title === '') {
            continue;
        }

        $wpdb->insert(
            $lines_table,
            array(
                'order_id' => $order_id,
                'dish_id' => $dish_id > 0 ? $dish_id : 0,
                'title' => $title,
                'price' => $price,
                'qty' => $qty,
                'line_note' => $line_note,
                'sort_order' => $sort_order,
            ),
            array('%d', '%d', '%s', '%f', '%d', '%s', '%d')
        );

        $sort_order++;
    }

    return array(
        'id' => $order_id,
        'guest_token' => $guest_token,
    );
}

/**
 * Append line items to an existing table order.
 *
 * @param int                               $order_id   Order ID.
 * @param array<int, array<string, mixed>>  $lines      Line items.
 * @param string                            $order_note Optional note to append.
 * @return bool
 */
function maca_menulist_db_append_table_order_lines($order_id, $lines, $order_note = '') {
    global $wpdb;

    maca_menulist_db_ensure_table_order_tables();

    $order_id = intval($order_id);
    if ($order_id <= 0 || empty($lines)) {
        return false;
    }

    $orders_table = maca_menulist_db_table_orders_table();
    $lines_table = maca_menulist_db_table_order_lines_table();
    $order = maca_menulist_db_get_table_order($order_id);

    if (!$order || !maca_menulist_table_order_allows_append($order->status, $order)) {
        return false;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $sort_order = (int) $wpdb->get_var(
        $wpdb->prepare("SELECT MAX(sort_order) FROM $lines_table WHERE order_id = %d", $order_id)
    );
    $sort_order++;

    foreach ($lines as $line) {
        $qty = isset($line['qty']) ? max(1, (int) $line['qty']) : 1;
        $title = isset($line['title']) ? sanitize_text_field($line['title']) : '';
        $line_note = isset($line['note']) ? sanitize_text_field($line['note']) : '';
        $dish_id = isset($line['id']) ? intval($line['id']) : 0;
        $price = null;

        if (isset($line['price']) && $line['price'] !== '' && $line['price'] !== null) {
            $price = (float) $line['price'];
        }

        if ($title === '') {
            continue;
        }

        $wpdb->insert(
            $lines_table,
            array(
                'order_id' => $order_id,
                'dish_id' => $dish_id > 0 ? $dish_id : 0,
                'title' => $title,
                'price' => $price,
                'qty' => $qty,
                'line_note' => $line_note,
                'sort_order' => $sort_order,
            ),
            array('%d', '%d', '%s', '%f', '%d', '%s', '%d')
        );

        $sort_order++;
    }

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

    $order_note = sanitize_textarea_field($order_note);
    if ($order_note !== '') {
        $existing_note = isset($order->order_note) ? trim((string) $order->order_note) : '';
        $data['order_note'] = $existing_note !== '' ? $existing_note . "\n" . $order_note : $order_note;
        $formats[] = '%s';
    }

    $wpdb->update(
        $orders_table,
        $data,
        array('id' => $order_id),
        $formats,
        array('%d')
    );

    if (function_exists('maca_menulist_db_sync_order_payment_status_from_totals')) {
        maca_menulist_db_sync_order_payment_status_from_totals($order_id);
    }

    return true;
}

/**
 * Get one order by ID.
 *
 * @param int $order_id Order ID.
 * @return object|null
 */
function maca_menulist_db_get_table_order($order_id) {
    global $wpdb;

    maca_menulist_db_ensure_table_order_tables();

    $order_id = intval($order_id);
    if ($order_id <= 0) {
        return null;
    }

    $orders_table = maca_menulist_db_table_orders_table();

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

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

    maca_menulist_db_ensure_table_order_tables();

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

    $orders_table = maca_menulist_db_table_orders_table();

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

/**
 * Attach line items to order objects.
 *
 * @param array<int, object> $orders Order rows.
 * @return array<int, object>
 */
function maca_menulist_db_attach_table_order_lines($orders, $attach_payments = true) {
    global $wpdb;

    if (empty($orders)) {
        return array();
    }

    $lines_table = maca_menulist_db_table_order_lines_table();
    $order_ids = array_map(
        static function ($order) {
            return (int) $order->id;
        },
        $orders
    );

    $placeholders = implode(',', array_fill(0, count($order_ids), '%d'));
    $line_rows = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT * FROM $lines_table WHERE order_id IN ($placeholders) ORDER BY sort_order ASC, id ASC",
            ...$order_ids
        )
    );

    $lines_by_order = array();
    foreach ($line_rows as $line) {
        $order_id = (int) $line->order_id;
        if (!isset($lines_by_order[$order_id])) {
            $lines_by_order[$order_id] = array();
        }
        $lines_by_order[$order_id][] = $line;
    }

    foreach ($orders as $index => $order) {
        $order_id = (int) $order->id;
        $orders[$index]->lines = isset($lines_by_order[$order_id]) ? $lines_by_order[$order_id] : array();
    }

    if ($attach_payments && function_exists('maca_menulist_db_attach_order_payments')) {
        $orders = maca_menulist_db_attach_order_payments($orders);
    }

    return $orders;
}

/**
 * Get active table orders for staff views.
 *
 * @param string $view Staff view: waiter or kitchen.
 * @return array<int, object>
 */
function maca_menulist_db_get_active_table_orders($view = 'waiter', $attach_payments = true, $table_filter = null) {
    global $wpdb;

    maca_menulist_db_ensure_table_order_tables();

    $orders_table = maca_menulist_db_table_orders_table();
    $active_statuses = maca_menulist_get_active_table_order_statuses($view);
    $placeholders = implode(',', array_fill(0, count($active_statuses), '%s'));
    $select = is_array($table_filter) ? 'id, table_number, service_mode, pickup_name, pickup_phone, order_note, status, eta_minutes, created_at, status_updated_at, payment_method, payment_status, payment_amount, payment_currency, tip_amount, bill_split_mode, bill_split_count, bill_split_paid_shares' : '*';
    $sql = "SELECT $select FROM $orders_table WHERE status IN ($placeholders)";
    $args = $active_statuses;

    if (is_array($table_filter)) {
        $service_mode = isset($table_filter['service_mode']) ? sanitize_key((string) $table_filter['service_mode']) : '';
        $table_number = isset($table_filter['table_number']) ? trim((string) $table_filter['table_number']) : '';
        $walk_in = !empty($table_filter['walk_in']);

        if ($walk_in) {
            $sql .= " AND (service_mode = %s OR service_mode = '' OR service_mode IS NULL)";
            $args[] = 'dine_in';
            $sql .= " AND (table_number = '' OR table_number IS NULL)";
        } elseif ($service_mode === 'takeaway') {
            $sql .= ' AND service_mode = %s';
            $args[] = 'takeaway';
            if ($table_number !== '') {
                $sql .= ' AND pickup_name = %s';
                $args[] = $table_number;
            }
        } elseif ($table_number !== '') {
            $normalized_table = function_exists('maca_menulist_normalize_table_number')
                ? maca_menulist_normalize_table_number($table_number)
                : strtolower(trim($table_number));
            $sql .= " AND LOWER(TRIM(COALESCE(table_number, ''))) = %s";
            $args[] = $normalized_table;
            $sql .= " AND (service_mode = %s OR service_mode = '' OR service_mode IS NULL)";
            $args[] = 'dine_in';
        }
    }

    $sql .= ' ORDER BY created_at ASC, id ASC';

    $orders = $wpdb->get_results($wpdb->prepare($sql, ...$args));

    if (empty($orders)) {
        return array();
    }

    $orders = maca_menulist_db_attach_table_order_lines($orders, $attach_payments);

    if (
        $view === 'kitchen'
        && function_exists('maca_menulist_order_visible_on_kitchen_board')
    ) {
        $orders = array_values(
            array_filter(
                $orders,
                static function ($order) {
                    return maca_menulist_order_visible_on_kitchen_board($order);
                }
            )
        );
    }

    return $orders;
}

/**
 * Get open table orders for staff views.
 *
 * @param string $view Staff view: waiter or kitchen.
 * @return array<int, object>
 */
function maca_menulist_db_get_table_orders($view = 'waiter', $attach_payments = true, $table_filter = null) {
    return maca_menulist_db_get_active_table_orders($view, $attach_payments, $table_filter);
}

/**
 * Lightweight active-order rows for staff table overview (no lines or payments).
 *
 * @param string $view Staff view: waiter or kitchen.
 * @return array<int, object>
 */
function maca_menulist_db_get_staff_order_summary_rows($view = 'waiter') {
    global $wpdb;

    maca_menulist_db_ensure_table_order_tables();

    $orders_table = maca_menulist_db_table_orders_table();
    $active_statuses = maca_menulist_get_active_table_order_statuses($view);
    $placeholders = implode(',', array_fill(0, count($active_statuses), '%s'));

    $orders = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT id, table_number, service_mode, pickup_name, status, created_at
            FROM $orders_table
            WHERE status IN ($placeholders)
            ORDER BY created_at ASC, id ASC",
            ...$active_statuses
        )
    );

    if (empty($orders)) {
        return array();
    }

    if (
        $view === 'kitchen'
        && function_exists('maca_menulist_order_visible_on_kitchen_board')
    ) {
        $orders = array_values(
            array_filter(
                $orders,
                static function ($order) {
                    return maca_menulist_order_visible_on_kitchen_board($order);
                }
            )
        );
    }

    return $orders;
}

/**
 * Count table orders created today (site timezone).
 *
 * @return int
 */
function maca_menulist_db_count_table_orders_today() {
    global $wpdb;

    maca_menulist_db_ensure_table_order_tables();

    $orders_table = maca_menulist_db_table_orders_table();
    $today = wp_date('Y-m-d');

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    return (int) $wpdb->get_var(
        $wpdb->prepare(
            "SELECT COUNT(*) FROM $orders_table WHERE created_at >= %s AND created_at <= %s",
            $today . ' 00:00:00',
            $today . ' 23:59:59'
        )
    );
}

/**
 * Count table orders for admin history.
 *
 * @param array<string, mixed> $args Query args.
 * @return int
 */
function maca_menulist_db_count_table_orders($args = array()) {
    global $wpdb;

    maca_menulist_db_ensure_table_order_tables();

    $query = maca_menulist_db_build_table_orders_query($args, true);

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

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

    maca_menulist_db_ensure_table_order_tables();

    $query = maca_menulist_db_build_table_orders_query($args, false);
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
    $orders = $wpdb->get_results($query['sql']);

    if (empty($orders)) {
        return array();
    }

    $attach_lines = !array_key_exists('attach_lines', $args) || !empty($args['attach_lines']);
    if (!$attach_lines) {
        return $orders;
    }

    return maca_menulist_db_attach_table_order_lines($orders);
}

/**
 * Build SQL for listing or counting table orders.
 *
 * @param array<string, mixed> $args   Query args.
 * @param bool                 $count  Whether to return COUNT(*) query.
 * @return array{sql: string}
 */
function maca_menulist_db_build_table_orders_query($args, $count = false) {
    global $wpdb;

    $defaults = array(
        'status_filter'          => 'all',
        'older_than_days'        => 0,
        'last_days'              => 0,
        'date_from'              => '',
        'date_to'                => '',
        'payment_status_filter'  => 'all',
        'payment_method_filter'  => 'all',
        'table_number'           => '',
        'orderby'                => 'created_at',
        'paged'                  => 1,
        'per_page'               => 20,
    );
    $args = wp_parse_args($args, $defaults);

    $orders_table = maca_menulist_db_table_orders_table();
    $lines_table = maca_menulist_db_table_order_lines_table();
    $where = array('1=1');
    $params = array();

    $status_filter = sanitize_key((string) $args['status_filter']);
    if ($status_filter === 'active') {
        $statuses = array_values(
            array_unique(
                array_merge(
                    maca_menulist_get_active_table_order_statuses('waiter'),
                    maca_menulist_get_active_table_order_statuses('kitchen')
                )
            )
        );
        $placeholders = implode(',', array_fill(0, count($statuses), '%s'));
        $where[] = "status IN ($placeholders)";
        $params = array_merge($params, $statuses);
    } elseif ($status_filter === 'completed') {
        $statuses = maca_menulist_get_completed_table_order_statuses();
        $placeholders = implode(',', array_fill(0, count($statuses), '%s'));
        $where[] = "status IN ($placeholders)";
        $params = array_merge($params, $statuses);
    } elseif ($status_filter !== 'all' && maca_menulist_is_valid_table_order_status($status_filter)) {
        $where[] = 'status = %s';
        $params[] = maca_menulist_normalize_table_order_status($status_filter);
    }

    $older_than_days = max(0, (int) $args['older_than_days']);
    if ($older_than_days > 0) {
        $where[] = 'created_at < %s';
        $params[] = maca_menulist_wp_days_ago_mysql($older_than_days);
    }

    $last_days = max(0, (int) $args['last_days']);
    if ($last_days > 0) {
        $where[] = 'created_at >= %s';
        $params[] = maca_menulist_wp_days_ago_mysql($last_days);
    }

    $date_from = sanitize_text_field((string) $args['date_from']);
    if ($date_from !== '' && preg_match('/^\d{4}-\d{2}-\d{2}$/', $date_from)) {
        $where[] = 'created_at >= %s';
        $params[] = $date_from . ' 00:00:00';
    }

    $date_to = sanitize_text_field((string) $args['date_to']);
    if ($date_to !== '' && preg_match('/^\d{4}-\d{2}-\d{2}$/', $date_to)) {
        $where[] = 'created_at <= %s';
        $params[] = $date_to . ' 23:59:59';
    }

    $table_number = sanitize_text_field((string) $args['table_number']);
    if ($table_number !== '') {
        $where[] = 'table_number = %s';
        $params[] = $table_number;
    }

    $payment_status_filter = sanitize_key((string) $args['payment_status_filter']);
    if ($payment_status_filter === 'unpaid') {
        $where[] = "payment_status IN ('none', 'pending', 'failed')";
    } elseif ($payment_status_filter === 'incomplete') {
        $where[] = "payment_status IN ('none', 'pending', 'failed', 'partial')";
    } elseif ($payment_status_filter !== 'all' && in_array($payment_status_filter, maca_menulist_payment_statuses(), true)) {
        $where[] = 'payment_status = %s';
        $params[] = $payment_status_filter;
    }

    $payment_method_filter = sanitize_key((string) $args['payment_method_filter']);
    if ($payment_method_filter === 'unset') {
        $where[] = "(payment_method IS NULL OR payment_method = '')";
    } elseif (in_array($payment_method_filter, maca_menulist_payment_methods(), true)) {
        $where[] = 'payment_method = %s';
        $params[] = $payment_method_filter;
    }

    $where_sql = implode(' AND ', $where);

    if ($count) {
        $sql = "SELECT COUNT(*) FROM $orders_table WHERE $where_sql";
        if (!empty($params)) {
            $sql = $wpdb->prepare($sql, ...$params);
        }
        return array('sql' => $sql);
    }

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

    $orderby = sanitize_key((string) $args['orderby']);
    if ($orderby === 'paid_at') {
        $order_sql = 'ORDER BY COALESCE(o.paid_at, o.created_at) DESC, o.id DESC';
    } else {
        $order_sql = 'ORDER BY o.created_at DESC, o.id DESC';
    }

    $sql = "SELECT o.*,
            (SELECT COUNT(*) FROM $lines_table l WHERE l.order_id = o.id) AS line_count,
            (SELECT COALESCE(SUM(l.qty * COALESCE(l.price, 0)), 0) FROM $lines_table l WHERE l.order_id = o.id) AS order_total
            FROM $orders_table o
            WHERE $where_sql
            $order_sql
            LIMIT %d OFFSET %d";

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

    return array('sql' => $wpdb->prepare($sql, ...$params));
}

/**
 * Delete one table order and its lines.
 *
 * @param int $order_id Order ID.
 * @return bool
 */
function maca_menulist_db_delete_table_order($order_id) {
    global $wpdb;

    maca_menulist_db_ensure_table_order_tables();

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

    $orders_table = maca_menulist_db_table_orders_table();
    $lines_table = maca_menulist_db_table_order_lines_table();

    $wpdb->delete($lines_table, array('order_id' => $order_id), array('%d'));
    $deleted = $wpdb->delete($orders_table, array('id' => $order_id), array('%d'));

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

/**
 * Delete multiple table orders.
 *
 * @param array<int, int> $order_ids Order IDs.
 * @return int Number deleted.
 */
function maca_menulist_db_delete_table_orders($order_ids) {
    $order_ids = array_values(array_filter(array_map('absint', (array) $order_ids)));
    if (empty($order_ids)) {
        return 0;
    }

    if (count($order_ids) === 1) {
        return maca_menulist_db_delete_table_order((int) $order_ids[0]) ? 1 : 0;
    }

    return maca_menulist_db_delete_table_orders_bulk($order_ids);
}

/**
 * Delete multiple orders with two SQL statements (lines, then orders).
 *
 * @param array<int, int> $order_ids Order IDs.
 * @return int Number deleted.
 */
function maca_menulist_db_delete_table_orders_bulk($order_ids) {
    global $wpdb;

    $order_ids = array_values(array_unique(array_filter(array_map('absint', (array) $order_ids))));
    if (empty($order_ids)) {
        return 0;
    }

    maca_menulist_db_ensure_table_order_tables();

    $orders_table = maca_menulist_db_table_orders_table();
    $lines_table = maca_menulist_db_table_order_lines_table();
    $deleted = 0;

    foreach (array_chunk($order_ids, 200) as $chunk) {
        $placeholders = implode(',', array_fill(0, count($chunk), '%d'));

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM $lines_table WHERE order_id IN ($placeholders)",
                ...$chunk
            )
        );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $batch_deleted = $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM $orders_table WHERE id IN ($placeholders)",
                ...$chunk
            )
        );

        if ($batch_deleted !== false) {
            $deleted += (int) $batch_deleted;
        }
    }

    return $deleted;
}

/**
 * Delete completed orders older than a number of days.
 *
 * @param int $days Minimum age in days.
 * @return int Number deleted.
 */
function maca_menulist_db_delete_old_completed_table_orders($days) {
    global $wpdb;

    maca_menulist_db_ensure_table_order_tables();

    $days = max(1, (int) $days);
    $orders_table = maca_menulist_db_table_orders_table();
    $lines_table = maca_menulist_db_table_order_lines_table();
    $statuses = maca_menulist_get_completed_table_order_statuses();
    $placeholders = implode(',', array_fill(0, count($statuses), '%s'));
    $params = array_merge($statuses, array(maca_menulist_wp_days_ago_mysql($days)));

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    $wpdb->query(
        $wpdb->prepare(
            "DELETE l FROM $lines_table l
            INNER JOIN $orders_table o ON o.id = l.order_id
            WHERE o.status IN ($placeholders) AND o.created_at < %s",
            ...$params
        )
    );

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    $deleted = $wpdb->query(
        $wpdb->prepare(
            "DELETE FROM $orders_table WHERE status IN ($placeholders) AND created_at < %s",
            ...$params
        )
    );

    return $deleted === false ? 0 : (int) $deleted;
}

/**
 * Build WHERE clause for waiter staff-board orders.
 *
 * @param int $older_than_days Only orders older than this many days (0 = all).
 * @return array{where_sql: string, params: array<int, mixed>}
 */
function maca_menulist_db_staff_board_orders_where($older_than_days = 0) {
    $statuses = maca_menulist_get_active_table_order_statuses('waiter');
    $placeholders = implode(',', array_fill(0, count($statuses), '%s'));
    $where_sql = "status IN ($placeholders)";
    $params = $statuses;

    $older_than_days = max(0, (int) $older_than_days);
    if ($older_than_days > 0) {
        $where_sql .= ' AND created_at < %s';
        $params[] = maca_menulist_wp_days_ago_mysql($older_than_days);
    }

    return array(
        'where_sql' => $where_sql,
        'params'    => $params,
    );
}

/**
 * IDs of orders still shown on the waiter staff board.
 *
 * @param int $older_than_days Only orders older than this many days (0 = all).
 * @param int $limit           Max rows (0 = no limit).
 * @return int[]
 */
function maca_menulist_db_get_staff_board_order_ids($older_than_days = 0, $limit = 0) {
    global $wpdb;

    maca_menulist_db_ensure_table_order_tables();

    $orders_table = maca_menulist_db_table_orders_table();
    $where = maca_menulist_db_staff_board_orders_where($older_than_days);
    $sql = "SELECT id FROM $orders_table WHERE {$where['where_sql']} ORDER BY id ASC";
    $params = $where['params'];

    $limit = max(0, (int) $limit);
    if ($limit > 0) {
        $sql .= ' LIMIT %d';
        $params[] = $limit;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    $ids = $wpdb->get_col($wpdb->prepare($sql, ...$params));

    if (empty($ids)) {
        return array();
    }

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

/**
 * Mark open staff-board orders as closed (removes them from waiter/kitchen lists).
 *
 * @param int $older_than_days Only orders older than this many days (0 = all on board).
 * @return int Number of orders updated.
 */
function maca_menulist_db_close_staff_board_orders($older_than_days = 0) {
    global $wpdb;

    maca_menulist_db_ensure_table_order_tables();

    $orders_table = maca_menulist_db_table_orders_table();
    $where = maca_menulist_db_staff_board_orders_where($older_than_days);
    $now = maca_menulist_wp_now_mysql();
    $params = array_merge(array('closed', $now, $now), $where['params']);

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    $updated = $wpdb->query(
        $wpdb->prepare(
            "UPDATE $orders_table SET status = %s, status_updated_at = %s, handled_at = %s WHERE {$where['where_sql']}",
            ...$params
        )
    );

    return $updated === false ? 0 : (int) $updated;
}

/**
 * Permanently delete orders that are still open on the staff board.
 *
 * @param int $older_than_days Only orders older than this many days (0 = all on board).
 * @return int Number deleted.
 */
function maca_menulist_db_delete_staff_board_orders($older_than_days = 0) {
    $deleted = 0;

    do {
        $ids = maca_menulist_db_get_staff_board_order_ids($older_than_days, 200);
        if (empty($ids)) {
            break;
        }

        $batch_deleted = maca_menulist_db_delete_table_orders_bulk($ids);
        $deleted += $batch_deleted;

        if ($batch_deleted < 1) {
            break;
        }
    } while (true);

    return $deleted;
}

/**
 * Update order status and optional ETA.
 *
 * @param int         $order_id    Order ID.
 * @param string      $status      New status.
 * @param int|null    $eta_minutes Optional ETA minutes while preparing or ready.
 * @return bool
 */
function maca_menulist_db_update_table_order_status($order_id, $status, $eta_minutes = null) {
    global $wpdb;

    maca_menulist_db_ensure_table_order_tables();

    $order_id = intval($order_id);
    $status = maca_menulist_normalize_table_order_status(sanitize_key($status));

    if ($order_id <= 0 || !maca_menulist_is_valid_table_order_status($status)) {
        return false;
    }

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

    if ($eta_minutes !== null && in_array($status, array('preparing', 'ready'), true)) {
        $data['eta_minutes'] = max(0, (int) $eta_minutes);
        $formats[] = '%d';
    }

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

    $updated = $wpdb->update(
        $orders_table,
        $data,
        array('id' => $order_id),
        $formats,
        array('%d')
    );

    return $updated !== false;
}

/**
 * Update table number on an order.
 *
 * @param int    $order_id     Order ID.
 * @param string $table_number New table number.
 * @return bool
 */
function maca_menulist_db_update_table_order_table_number($order_id, $table_number) {
    global $wpdb;

    maca_menulist_db_ensure_table_order_tables();

    $order_id = intval($order_id);
    $table_number = sanitize_text_field((string) $table_number);

    if ($order_id <= 0 || $table_number === '') {
        return false;
    }

    $orders_table = maca_menulist_db_table_orders_table();
    $updated = $wpdb->update(
        $orders_table,
        array('table_number' => $table_number),
        array('id' => $order_id),
        array('%s'),
        array('%d')
    );

    return $updated !== false;
}

/**
 * Update ETA minutes without changing order status (kitchen "Ready in…" while preparing).
 *
 * @param int $order_id    Order ID.
 * @param int $eta_minutes Minutes until ready.
 * @return bool
 */
function maca_menulist_db_set_table_order_eta($order_id, $eta_minutes) {
    global $wpdb;

    maca_menulist_db_ensure_table_order_tables();

    $order_id = intval($order_id);
    if ($order_id <= 0) {
        return false;
    }

    $orders_table = maca_menulist_db_table_orders_table();
    $updated = $wpdb->update(
        $orders_table,
        array(
            'eta_minutes' => max(0, (int) $eta_minutes),
            'status_updated_at' => maca_menulist_wp_now_mysql(),
        ),
        array('id' => $order_id),
        array('%d', '%s'),
        array('%d')
    );

    return $updated !== false;
}

/**
 * Mark a table order as handled (legacy waiter action).
 *
 * @param int $order_id Order ID.
 * @return bool
 */
function maca_menulist_db_handle_table_order($order_id) {
    return maca_menulist_db_update_table_order_status($order_id, 'served');
}

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

    foreach (array(
        maca_menulist_db_table_order_lines_table(),
        maca_menulist_db_table_orders_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
