<?php
if (!defined('ABSPATH')) exit;

function wpbr_table_batches() {
    global $wpdb;
    return $wpdb->prefix . 'wpbr_batches';
}

function wpbr_table_items() {
    global $wpdb;
    return $wpdb->prefix . 'wpbr_batch_items';
}

function wpbr_table_import_log() {
    global $wpdb;
    return $wpdb->prefix . 'wpbr_import_log';
}

/**
 * The two tables the matching-intelligence layer uses.
 *
 * Free names them and never creates, reads or writes them. They belong to Pro.
 * The names live here because three things still need to say them out
 * loud: the 0.3.0 migration, which renames a legacy 0.3.0 site's existing data into
 * them so nothing is lost; the uninstall inventory, which excludes them; and
 * any add-on that adopts the data.
 */
function wpbr_table_learned() {
    global $wpdb;
    return $wpdb->prefix . 'wpbr_learned_matches';
}

function wpbr_table_aliases() {
    global $wpdb;
    return $wpdb->prefix . 'wpbr_aliases';
}

function wpbr_table_log() {
    global $wpdb;
    return $wpdb->prefix . 'wpbr_log';
}

/**
 * A list of row ids, as received, cleaned for an IN ( ... ) clause.
 *
 * "These rows" is a list whose length is whatever the person selected. The
 * statements that act on it write one %d placeholder per id and bind the ids, so
 * what was selected only ever travels as values and never as SQL text:
 *
 *     "... WHERE batch_id = %d AND id IN (" . implode(',', array_fill(0, count($ids), '%d')) . ')'
 *
 * Plugin Check reads that form as prepared. It does not read a placeholder list
 * built into a variable first as prepared, and that is the whole difference
 * between the two.
 *
 * It is IN ( ... ) and not FIND_IN_SET( id, %s ) on purpose, and measured, on
 * MariaDB 10.11 and MySQL 8.0. Given the id as a plain column, the database goes
 * straight to the rows it names. Given FIND_IN_SET it reads every row of the
 * batch, the whole table once a batch is a large share of it, and searches the
 * list as a string for each. Five rows of a 5,000-row batch took 0.1-0.3 ms
 * against 1-14 ms; a thousand rows of a 50,000-row batch took 4-16 ms against
 * 0.8-1.5 s, and an UPDATE could lock the whole table while it ran.
 * docs/PLUGIN-CHECK.md has the figures; tests/SqlSafetyTest.php fails if
 * FIND_IN_SET comes back.
 *
 * Each entry gets the cast every request handler already applies (intval), and
 * only a positive whole number survives: a negative, a zero, an array or an
 * object is dropped rather than coerced into somebody else's id. An empty result
 * means "nothing to ask": IN () is a syntax error and the answer needs no query,
 * so every caller checks for it first.
 *
 * Used only against the batch items table, where `batch_id = %d` narrows the
 * rows to one batch.
 *
 * @param mixed $ids Row ids, as received.
 * @return int[] Positive whole numbers, each once, in the order given.
 */
function wpbr_clean_ids($ids) {
    $clean = array();
    foreach ((array) $ids as $id) {
        if (!is_scalar($id)) continue;
        $id = (int) $id;
        if ($id > 0) $clean[$id] = $id;
    }
    return array_values($clean);
}

/*
 * The four reads this plugin made, word for word, in a dozen places.
 *
 * Each is one fixed statement against a table the plugin owns, and each is
 * deliberately NOT cached. A batch row and its items are the state machine: the
 * status a request reads decides whether the next request replaces a file, skips
 * it or rolls it back, and the request that wrote that status may have been a
 * moment ago, in another process, behind a persistent object cache. A cached
 * copy that is one write behind is a file replaced twice or a rollback skipped,
 * so these always ask the database. docs/PLUGIN-CHECK.md has the longer account.
 *
 * Every id is cast here, so a caller that holds a string from a form or a row
 * gets the same statement an integer would.
 */

/** One batch, or null when there is none. */
function wpbr_get_batch($batch_id) {
    global $wpdb;
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned table with no WordPress API; the status read here steers the next write, so it is never cached.
    return $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}wpbr_batches WHERE id = %d", (int) $batch_id));
}

/** Every item in a batch, in table order. */
function wpbr_get_batch_items($batch_id) {
    global $wpdb;
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned table with no WordPress API; item status steers the next write, so it is never cached.
    return $wpdb->get_results($wpdb->prepare("SELECT * FROM {$wpdb->prefix}wpbr_batch_items WHERE batch_id = %d", (int) $batch_id));
}

/**
 * The items of one batch whose ids are in the list; ids from another batch never match.
 *
 * An id list with nothing usable in it asks nothing: IN () is a syntax error and
 * the answer is known. See wpbr_clean_ids() for why this is IN (...) and not
 * FIND_IN_SET(), and for the form Plugin Check reads as prepared.
 */
function wpbr_get_batch_items_in($batch_id, $ids) {
    global $wpdb;
    $ids = wpbr_clean_ids($ids);
    if (!$ids) return array();
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned table with no WordPress API; item status steers the next write, so it is never cached.
    return $wpdb->get_results($wpdb->prepare(
        "SELECT * FROM {$wpdb->prefix}wpbr_batch_items WHERE batch_id = %d AND id IN (" . implode(',', array_fill(0, count($ids), '%d')) . ')',
        array_merge(array((int) $batch_id), $ids)
    ));
}

/** One item, or null when there is none. */
function wpbr_get_item($item_id) {
    global $wpdb;
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned table with no WordPress API; item status steers the next write, so it is never cached.
    return $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}wpbr_batch_items WHERE id = %d", (int) $item_id));
}

/**
 * Make sure THIS site's tables are at the current schema before the engine writes to them.
 *
 * The schema is brought up to date on admin_init (wpbr_maybe_upgrade_db()), which a browser
 * request always reaches. A request that does not go through an admin screen - WP-Cron, a network
 * job working on a subsite nobody has opened since the update - would reach the engine with the old
 * table, and an UPDATE naming a column that is not there fails as a whole. The engine records a
 * backup, the hash of the original and the outcome of every row, so it asks first. One option read
 * when nothing is to do; per site, because the option and the tables both are.
 */
function wpbr_ensure_schema() {
    if (get_option('wpbr_db_version') !== WPBR_DB_VERSION) {
        wpbr_create_tables();
    }
}

/** The columns DB 3.0 adds to the items table. */
function wpbr_intent_columns() {
    return array('intent_state', 'intent_attempt', 'intent_base_sha', 'intent_new_sha', 'adapter_id', 'written_sha');
}

/** Are all the DB 3.0 columns present? Checked against the live schema, never assumed. */
function wpbr_intent_columns_present() {
    global $wpdb;
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- The plugin's own table; a schema read that decides whether the version may be bumped, so it is never cached.
    $have = (array) $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}wpbr_batch_items");
    return !array_diff(wpbr_intent_columns(), $have);
}

/** Do the batches and the activity log have the DB 3.1 `actor` column? Checked against the live schema, never assumed. */
function wpbr_actor_columns_present() {
    global $wpdb;
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- The plugin's own table; a schema read that decides whether the version may be bumped, so it is never cached.
    $batches = (array) $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}wpbr_batches");
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- The plugin's own table; the same schema read.
    $log = (array) $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}wpbr_log");
    return in_array('actor', $batches, true) && in_array('actor', $log, true);
}

function wpbr_create_tables() {
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();
    $batches_table = wpbr_table_batches();
    $items_table = wpbr_table_items();
    $import_log_table = wpbr_table_import_log();

    $sql1 = "CREATE TABLE ".$batches_table." (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        label varchar(191) NOT NULL,
        post_type varchar(64) NULL,
        meta_key_name varchar(191) NULL,
        status varchar(50) NOT NULL DEFAULT 'staged',
        source_path varchar(500) NOT NULL,
        created_at datetime NOT NULL,
        created_by bigint(20) unsigned NOT NULL,
        actor varchar(40) NULL,
        PRIMARY KEY (id)
    ) ".$charset_collate.";";

    $sql2 = "CREATE TABLE ".$items_table." (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        batch_id bigint(20) unsigned NOT NULL,
        staged_filename varchar(255) NOT NULL,
        staged_path varchar(500) NOT NULL,
        matched_post_id bigint(20) unsigned NULL,
        matched_post_title varchar(255) NULL,
        confidence_score decimal(5,4) NULL,
        match_kind varchar(20) NULL,
        rename_to varchar(255) NULL,
        status varchar(50) NOT NULL DEFAULT 'staged',
        notes text NULL,
        backup_path varchar(500) NULL,
        created_attachment_id bigint(20) unsigned NULL,
        previous_meta_value varchar(500) NULL,
        processed_at datetime NULL,
        original_sha256 char(64) NULL,
        outcome_code varchar(40) NULL,
        intent_state varchar(16) NULL,
        intent_attempt varchar(32) NULL,
        intent_base_sha char(64) NULL,
        intent_new_sha char(64) NULL,
        adapter_id varchar(40) NULL,
        written_sha char(64) NULL,
        PRIMARY KEY (id),
        KEY batch_id (batch_id)
    ) ".$charset_collate.";";

    $sql3 = "CREATE TABLE ".$import_log_table." (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        import_uuid varchar(36) NOT NULL,
        batch_id bigint(20) unsigned NOT NULL,
        item_id bigint(20) unsigned NOT NULL,
        source_filename varchar(255) NULL,
        prev_matched_post_id bigint(20) unsigned NULL,
        prev_matched_post_title varchar(255) NULL,
        prev_status varchar(50) NULL,
        prev_notes text NULL,
        created_at datetime NOT NULL,
        created_by bigint(20) unsigned NOT NULL,
        PRIMARY KEY (id),
        KEY import_uuid (import_uuid),
        KEY batch_id (batch_id)
    ) ".$charset_collate.";";

    $sql6 = "CREATE TABLE ".wpbr_table_log()." (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        batch_id bigint(20) unsigned NOT NULL,
        item_id bigint(20) unsigned NULL,
        filename varchar(255) NULL,
        action varchar(40) NOT NULL,
        result varchar(20) NOT NULL,
        message text NULL,
        created_at datetime NOT NULL,
        created_by bigint(20) unsigned NOT NULL,
        actor varchar(40) NULL,
        PRIMARY KEY (id),
        KEY batch_id (batch_id),
        KEY created_at (created_at)
    ) ".$charset_collate.";";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql1);
    dbDelta($sql2);
    dbDelta($sql3);
    dbDelta($sql6);

    // The version is bumped only once every column of this release is verified present. A request that dies in between
    // (or a database user that cannot ALTER) leaves the old version, the engine keeps running as it did, and the next
    // request completes the step: dbDelta only adds what is missing, so re-running it is a no-op.
    if (wpbr_intent_columns_present() && wpbr_actor_columns_present()) {
        update_option('wpbr_db_version', WPBR_DB_VERSION);
    }
}