<?php
/**
 * Database layer for waiter profiles and table assignments.
 *
 * @package Maca_Menu_List
 */

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

// 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

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

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

/**
 * Waiter-to-table assignment table name.
 *
 * @return string
 */
function maca_menulist_db_waiter_tables_table() {
    global $wpdb;

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

/**
 * Create waiter tables.
 */
function maca_menulist_db_create_waiter_tables() {
    global $wpdb;

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

    $charset_collate = $wpdb->get_charset_collate();
    $waiters_table = maca_menulist_db_waiters_table();
    $assignments_table = maca_menulist_db_waiter_tables_table();

    $sql_waiters = "CREATE TABLE $waiters_table (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        name varchar(255) NOT NULL,
        is_active tinyint(1) NOT NULL DEFAULT 1,
        sort_order int(11) NOT NULL DEFAULT 0,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        KEY is_active (is_active),
        KEY sort_order (sort_order)
    ) $charset_collate;";

    $sql_assignments = "CREATE TABLE $assignments_table (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        waiter_id bigint(20) unsigned NOT NULL,
        table_number varchar(50) NOT NULL,
        PRIMARY KEY  (id),
        UNIQUE KEY waiter_table (waiter_id, table_number),
        KEY table_number (table_number)
    ) $charset_collate;";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql_waiters);
    dbDelta($sql_assignments);
}

/**
 * Ensure waiter tables exist.
 */
function maca_menulist_db_ensure_waiter_tables() {
    static $ensured = false;

    if ($ensured) {
        return;
    }

    global $wpdb;

    $waiters_table = maca_menulist_db_waiters_table();
    if (!$wpdb->get_var("SHOW TABLES LIKE '$waiters_table'")) {
        maca_menulist_db_create_waiter_tables();
    }

    $ensured = true;
}

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

    maca_menulist_db_ensure_waiter_tables();

    $waiters_table = maca_menulist_db_waiters_table();
    $sql = "SELECT * FROM $waiters_table";

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

    $sql .= ' ORDER BY sort_order ASC, name ASC, id ASC';

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix helper.
    return $wpdb->get_results($sql);
}

/**
 * Get one waiter.
 *
 * @param int $waiter_id Waiter ID.
 * @return object|null
 */
function maca_menulist_db_get_waiter($waiter_id) {
    global $wpdb;

    maca_menulist_db_ensure_waiter_tables();

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

    $waiters_table = maca_menulist_db_waiters_table();

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

/**
 * Table numbers assigned to a waiter.
 *
 * @param int $waiter_id Waiter ID.
 * @return array<int, string>
 */
function maca_menulist_db_get_waiter_table_numbers($waiter_id) {
    global $wpdb;

    maca_menulist_db_ensure_waiter_tables();

    $waiter_id = absint($waiter_id);
    if ($waiter_id <= 0) {
        return array();
    }

    $assignments_table = maca_menulist_db_waiter_tables_table();
    $rows = $wpdb->get_col(
        $wpdb->prepare(
            "SELECT table_number FROM $assignments_table WHERE waiter_id = %d ORDER BY table_number ASC",
            $waiter_id
        )
    );

    if (!is_array($rows)) {
        return array();
    }

    return array_values(array_filter(array_map('strval', $rows)));
}

/**
 * Replace table assignments for a waiter.
 *
 * @param int                $waiter_id     Waiter ID.
 * @param array<int, string> $table_numbers Table numbers.
 * @return void
 */
function maca_menulist_db_set_waiter_table_numbers($waiter_id, $table_numbers) {
    global $wpdb;

    maca_menulist_db_ensure_waiter_tables();

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

    $assignments_table = maca_menulist_db_waiter_tables_table();
    $wpdb->delete($assignments_table, array('waiter_id' => $waiter_id), array('%d'));

    $seen = array();
    foreach ((array) $table_numbers as $table_number) {
        $table_number = sanitize_text_field((string) $table_number);
        if ($table_number === '') {
            continue;
        }

        $key = function_exists('maca_menulist_normalize_table_number')
            ? maca_menulist_normalize_table_number($table_number)
            : strtolower(trim($table_number));

        if ($key === '' || isset($seen[$key])) {
            continue;
        }

        $seen[$key] = true;
        $wpdb->insert(
            $assignments_table,
            array(
                'waiter_id' => $waiter_id,
                'table_number' => $table_number,
            ),
            array('%d', '%s')
        );
    }
}

/**
 * Insert or update a waiter.
 *
 * @param array<string, mixed> $data Waiter fields.
 * @param array<int, string>   $table_numbers Assigned tables.
 * @return int Waiter ID or 0 on failure.
 */
function maca_menulist_db_save_waiter($data, $table_numbers = array()) {
    global $wpdb;

    maca_menulist_db_ensure_waiter_tables();

    $waiters_table = maca_menulist_db_waiters_table();
    $waiter_id = isset($data['id']) ? absint($data['id']) : 0;
    $name = isset($data['name']) ? sanitize_text_field((string) $data['name']) : '';
    $is_active = !empty($data['is_active']) ? 1 : 0;
    $sort_order = isset($data['sort_order']) ? (int) $data['sort_order'] : 0;

    if ($name === '') {
        return 0;
    }

    $row = array(
        'name' => $name,
        'is_active' => $is_active,
        'sort_order' => $sort_order,
    );

    if ($waiter_id > 0) {
        $updated = $wpdb->update(
            $waiters_table,
            $row,
            array('id' => $waiter_id),
            array('%s', '%d', '%d'),
            array('%d')
        );

        if ($updated === false) {
            return 0;
        }
    } else {
        $row['created_at'] = maca_menulist_wp_now_mysql();
        $inserted = $wpdb->insert(
            $waiters_table,
            $row,
            array('%s', '%d', '%d', '%s')
        );

        if (!$inserted) {
            return 0;
        }

        $waiter_id = (int) $wpdb->insert_id;
    }

    maca_menulist_db_set_waiter_table_numbers($waiter_id, $table_numbers);

    return $waiter_id;
}

/**
 * Delete a waiter and assignments.
 *
 * @param int $waiter_id Waiter ID.
 * @return bool
 */
function maca_menulist_db_delete_waiter($waiter_id) {
    global $wpdb;

    maca_menulist_db_ensure_waiter_tables();

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

    $assignments_table = maca_menulist_db_waiter_tables_table();
    $waiters_table = maca_menulist_db_waiters_table();

    $wpdb->delete($assignments_table, array('waiter_id' => $waiter_id), array('%d'));

    return (bool) $wpdb->delete($waiters_table, array('id' => $waiter_id), array('%d'));
}

// 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

