<?php
/**
 * Database layer for dish campaigns and dish–campaign links.
 *
 * @package Maca_Menu_List
 */

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

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

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

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

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

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

/**
 * Create campaign tables.
 */
function maca_menulist_db_create_campaign_tables() {
    global $wpdb;

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

    $charset_collate = $wpdb->get_charset_collate();
    $campaigns_table = maca_menulist_db_campaigns_table();
    $link_table = maca_menulist_db_dish_campaigns_table();

    $sql_campaigns = "CREATE TABLE $campaigns_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) 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_links = "CREATE TABLE $link_table (
        dish_id mediumint(9) NOT NULL,
        campaign_id mediumint(9) NOT NULL,
        PRIMARY KEY  (dish_id, campaign_id),
        KEY campaign_id (campaign_id)
    ) $charset_collate;";

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

/**
 * @return array<int, object>
 */
function maca_menulist_db_get_campaigns($active_only = false) {
    global $wpdb;

    $table = maca_menulist_db_campaigns_table();
    $where = $active_only ? 'WHERE is_active = 1' : '';

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

/**
 * @param int $campaign_id Campaign ID.
 * @return object|null
 */
function maca_menulist_db_get_campaign($campaign_id) {
    global $wpdb;

    $table = maca_menulist_db_campaigns_table();

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

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

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

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

    maca_menulist_db_stamp_row_timestamps($row, $formats, true);

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

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

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

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

    maca_menulist_db_stamp_row_timestamps($row, $formats, false);

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

/**
 * @param array<string, mixed> $data Row data.
 * @return array{0: array<string, mixed>, 1: array<int, string>}
 */
function maca_menulist_db_prepare_campaign_write($data) {
    $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',
    );

    $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 int $campaign_id Campaign ID.
 * @return int|false
 */
function maca_menulist_db_delete_campaign($campaign_id) {
    global $wpdb;

    $campaign_id = intval($campaign_id);

    $wpdb->delete(
        maca_menulist_db_dish_campaigns_table(),
        array('campaign_id' => $campaign_id),
        array('%d')
    );

    return $wpdb->delete(
        maca_menulist_db_campaigns_table(),
        array('id' => $campaign_id),
        array('%d')
    );
}

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

    $table = maca_menulist_db_dish_campaigns_table();

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

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

/**
 * @param int           $dish_id      Dish ID.
 * @param array<int, int> $campaign_ids Campaign IDs.
 */
function maca_menulist_db_set_dish_campaigns($dish_id, $campaign_ids) {
    global $wpdb;

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

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

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

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

/**
 * @param int  $campaign_id Campaign ID.
 * @param bool $active_only Only active dishes.
 * @return array<int, object>
 */
function maca_menulist_db_get_dishes_for_campaign($campaign_id, $active_only = true) {
    global $wpdb;

    $dishes_table = maca_menulist_db_dishes_table();
    $link_table = maca_menulist_db_dish_campaigns_table();
    $where_active = $active_only ? ' AND d.is_active = 1' : '';

    return $wpdb->get_results(
        $wpdb->prepare(
            "SELECT d.*
            FROM $dishes_table d
            INNER JOIN $link_table dc ON dc.dish_id = d.id
            WHERE dc.campaign_id = %d$where_active
            ORDER BY d.sort_order ASC, d.title ASC",
            intval($campaign_id)
        )
    );
}

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

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

    $campaigns_table = maca_menulist_db_campaigns_table();
    $link_table = maca_menulist_db_dish_campaigns_table();
    $placeholders = implode(',', array_fill(0, count($dish_ids), '%d'));

    $rows = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT dc.dish_id, c.*
            FROM $link_table dc
            INNER JOIN $campaigns_table c ON c.id = dc.campaign_id
            WHERE dc.dish_id IN ($placeholders)
            ORDER BY c.sort_order ASC, c.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;
}

/**
 * Drop campaign tables on uninstall.
 */
function maca_menulist_db_drop_campaign_tables() {
    global $wpdb;

    foreach (array(
        maca_menulist_db_dish_campaigns_table(),
        maca_menulist_db_campaigns_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.DirectDatabaseQuery.SchemaChange
