<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-8.php
 *
 * Migration 4.8 — mbs_arc_campaigns table
 *
 * Creates mbs_arc_campaigns with one row per book, consolidating the eight
 * ARC postmeta keys that were previously written individually. Backfills
 * all written keys from postmeta. UNIQUE KEY on post_id enforces one
 * campaign row per book.
 *
 * @package MyBookShowroom
 * @since   1.6.0
 */

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

/**
 * Migration 4.8: mbs_arc_campaigns table.
 */
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- DAO/migration: table names via $wpdb->prefix are trusted server-controlled values, not user input
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- same reason: $wpdb->prefix table names are safe; $where_sql is built from sanitized server-controlled inputs
class MBS_Migration_4_8 implements MBS_Migration { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * @return string Migration version string.
	 */
	public function version(): string {
		return '4.8';
	}

	/**
	 * Run the migration.
	 *
	 * Idempotent: SHOW TABLES guard prevents double-create.
	 * INSERT IGNORE on backfill is safe to replay.
	 *
	 * @return void
	 */
	public function up(): void {
		global $wpdb;

		$table           = $wpdb->prefix . 'mbs_arc_campaigns';
		$charset_collate = $wpdb->get_charset_collate();

		// --- DDL: create table if not present --------------------------------
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$exists = $wpdb->get_var( "SHOW TABLES LIKE '{$table}'" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
		if ( $exists !== $table ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
			$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
				"CREATE TABLE {$table} (
					id                 BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
					post_id            BIGINT UNSIGNED  NOT NULL,
					status             VARCHAR(20)      NOT NULL DEFAULT 'not_started',
					copy_count         INT UNSIGNED     NOT NULL DEFAULT 0,
					distribution_date  DATE             NULL DEFAULT NULL,
					target_review_date DATE             NULL DEFAULT NULL,
					open_date          DATE             NULL DEFAULT NULL,
					close_date         DATE             NULL DEFAULT NULL,
					deadline           DATE             NULL DEFAULT NULL,
					availability_date  DATE             NULL DEFAULT NULL,
					created_at         DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
					updated_at         DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP
					                   ON UPDATE CURRENT_TIMESTAMP,
					PRIMARY KEY (id),
					UNIQUE KEY idx_post_id (post_id),
					KEY idx_status (status)
				) {$charset_collate}"
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange
		}

		// --- Backfill: collect distinct post_ids that have any ARC key -------
		$arc_keys = array(
			'mbs_arc_status',
			'mbs_arc_copy_count',
			'mbs_arc_distribution_date',
			'mbs_arc_target_review_date',
			'mbs_arc_close_date',
			'mbs_arc_open_date',
			'mbs_arc_deadline',
			'mbs_arc_availability_date',
		);

		$placeholders = implode( ',', array_fill( 0, count( $arc_keys ), '%s' ) );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$post_ids = $wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- safe: variadic ...$params / ...$row_ids correctly matches prepared placeholders; PHPCS cannot statically verify spread arg count
				"SELECT DISTINCT post_id FROM {$wpdb->postmeta}
				 WHERE meta_key IN ({$placeholders})", // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- safe: variadic ...$params spread correctly matches prepared placeholders; PHPCS cannot statically verify spread arg count
				...$arc_keys
			)
		);

		foreach ( (array) $post_ids as $raw_post_id ) {
			$post_id = (int) $raw_post_id;

			// Fetch each key for this post (single query per book, no array_map).
			$meta = array();
			foreach ( $arc_keys as $key ) {
				$meta[ $key ] = (string) get_post_meta( $post_id, $key, true );
			}

			$status     = in_array( $meta['mbs_arc_status'], array( 'not_started', 'active', 'closed' ), true )
			              ? $meta['mbs_arc_status'] : 'not_started';
			$copy_count = absint( $meta['mbs_arc_copy_count'] );

			$date_map = array(
				'distribution_date'  => $meta['mbs_arc_distribution_date'],
				'target_review_date' => $meta['mbs_arc_target_review_date'],
				'close_date'         => $meta['mbs_arc_close_date'],
				'open_date'          => $meta['mbs_arc_open_date'],
				'deadline'           => $meta['mbs_arc_deadline'],
				'availability_date'  => $meta['mbs_arc_availability_date'],
			);

			// Build date value string: NULL literal for empty, quoted for valid YYYY-MM-DD.
			$date_pattern = '/^\d{4}-\d{2}-\d{2}$/';
			$dist  = preg_match( $date_pattern, $date_map['distribution_date']  ) ? $date_map['distribution_date']  : null;
			$tgt   = preg_match( $date_pattern, $date_map['target_review_date'] ) ? $date_map['target_review_date'] : null;
			$close = preg_match( $date_pattern, $date_map['close_date']         ) ? $date_map['close_date']         : null;
			$open  = preg_match( $date_pattern, $date_map['open_date']          ) ? $date_map['open_date']          : null;
			$dead  = preg_match( $date_pattern, $date_map['deadline']           ) ? $date_map['deadline']           : null;
			$avail = preg_match( $date_pattern, $date_map['availability_date']  ) ? $date_map['availability_date']  : null;

			// INSERT IGNORE: safe on UNIQUE(post_id) — skips if already backfilled.
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				$wpdb->prepare(
					"INSERT IGNORE INTO {$table}
					 (post_id, status, copy_count,
					  distribution_date, target_review_date, close_date,
					  open_date, deadline, availability_date)
					 VALUES (%d, %s, %d,
					  %s, %s, %s,
					  %s, %s, %s)",
					$post_id, $status, $copy_count,
					$dist, $tgt, $close,
					$open, $dead, $avail
				)
			);
		}
	}
}
