<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-13.php
 *
 * Migration 4.13 — Book Club Resources tables
 *
 * Creates three tables to normalize book club data previously stored
 * as postmeta JSON:
 *
 *   mbs_book_club            — book-level header (enabled flag + guide HTML),
 *                              one row per book (PRIMARY KEY post_id).
 *   mbs_book_club_questions  — ordered discussion questions, one row per question.
 *   mbs_book_club_files      — ordered downloadable files, one row per attachment.
 *
 * Previous postmeta storage shape:
 *   mbs_book_club_enabled   — '1' or '0' string
 *   mbs_book_club_guide     — HTML string (wp_kses_post-sanitized)
 *   mbs_book_club_questions — JSON array of strings
 *   mbs_book_club_files     — JSON array of {id:int,label:string} objects
 *                             (legacy: plain int attachment IDs also accepted)
 *
 * Backfill reads the four postmeta keys for every book that has them and
 * inserts rows. Questions and files use DELETE + INSERT per book (idempotent).
 * The header table uses INSERT IGNORE on the PRIMARY KEY.
 *
 * @package MyBookShowroom
 * @since   1.7.0
 */

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

/**
 * Migration 4.13: Book Club Resources tables.
 */
// 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_13 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.13';
	}

	/**
	 * Run the migration.
	 *
	 * Idempotent: dbDelta handles CREATE TABLE IF NOT EXISTS semantics.
	 * INSERT IGNORE on PRIMARY KEY (header table) and DELETE + INSERT
	 * (questions/files tables) ensure safe replay.
	 *
	 * @return void
	 */
	public function up(): void {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$cc = $wpdb->get_charset_collate();

		$t_header    = $wpdb->prefix . 'mbs_book_club';
		$t_questions = $wpdb->prefix . 'mbs_book_club_questions';
		$t_files     = $wpdb->prefix . 'mbs_book_club_files';

		// --- DDL: create tables via dbDelta -------------------------------------
		dbDelta( MBS_DB_Schema::book_club(           $t_header,    $cc ) );
		dbDelta( MBS_DB_Schema::book_club_questions( $t_questions, $cc ) );
		dbDelta( MBS_DB_Schema::book_club_files(     $t_files,     $cc ) );

		// --- Backfill: all books with any book-club postmeta -------------------
		// 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(
				"SELECT DISTINCT post_id FROM {$wpdb->postmeta}
				 WHERE meta_key IN (%s,%s,%s,%s)",
				'mbs_book_club_enabled',
				'mbs_book_club_guide',
				'mbs_book_club_questions',
				'mbs_book_club_files'
			)
		);

		foreach ( (array) $post_ids as $raw_id ) {
			$post_id = (int) $raw_id;
			if ( $post_id <= 0 ) {
				continue;
			}

			// --- Header row (mbs_book_club) ------------------------------------
			$enabled_raw = get_post_meta( $post_id, 'mbs_book_club_enabled', true );
			$guide_raw   = get_post_meta( $post_id, 'mbs_book_club_guide',   true );
			$enabled_int = ( '1' === (string) $enabled_raw ) ? 1 : 0;
			$guide       = is_string( $guide_raw ) ? $guide_raw : '';

			// 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 {$t_header} (post_id, enabled, guide)
					 VALUES (%d, %d, %s)",
					$post_id,
					$enabled_int,
					$guide
				)
			);

			// --- Questions rows (mbs_book_club_questions) ----------------------
			$q_raw = get_post_meta( $post_id, 'mbs_book_club_questions', true );
			if ( is_string( $q_raw ) && '' !== $q_raw ) {
				$questions = json_decode( $q_raw, true );
				if ( is_array( $questions ) && ! empty( $questions ) ) {
					// DELETE + INSERT for idempotent replay.
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$wpdb->query( $wpdb->prepare( "DELETE FROM {$t_questions} WHERE post_id = %d", $post_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
					foreach ( $questions as $position => $question ) {
						$q = is_string( $question ) ? sanitize_text_field( $question ) : '';
						if ( '' === $q ) {
							continue;
						}
						// 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 INTO {$t_questions} (post_id, position, question)
								 VALUES (%d, %d, %s)",
								$post_id,
								(int) $position,
								$q
							)
						);
					}
				}
			}

			// --- File rows (mbs_book_club_files) ------------------------------
			$f_raw = get_post_meta( $post_id, 'mbs_book_club_files', true );
			if ( is_string( $f_raw ) && '' !== $f_raw ) {
				$files = json_decode( $f_raw, true );
				if ( is_array( $files ) && ! empty( $files ) ) {
					// DELETE + INSERT for idempotent replay.
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$wpdb->query( $wpdb->prepare( "DELETE FROM {$t_files} WHERE post_id = %d", $post_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
					foreach ( $files as $position => $item ) {
						if ( is_array( $item ) ) {
							// Current format: {id, label} object.
							$att_id = absint( $item['id']    ?? 0 );
							$label  = sanitize_text_field( $item['label'] ?? '' );
						} elseif ( is_numeric( $item ) ) {
							// Legacy format: plain attachment ID integer.
							$att_id = absint( $item );
							$label  = '';
						} else {
							continue;
						}
						if ( $att_id <= 0 ) {
							continue;
						}
						// 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 INTO {$t_files} (post_id, position, attachment_id, label)
								 VALUES (%d, %d, %d, %s)",
								$post_id,
								(int) $position,
								$att_id,
								$label
							)
						);
					}
				}
			}
		}
	}
}
