<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-11.php
 *
 * Migration 4.11 — mbs_book_territorial_rights table
 *
 * Creates mbs_book_territorial_rights with one row per book, storing the
 * structured territorial rights that were previously encoded as JSON in the
 * mbs_territorial_rights_v2 postmeta key.
 *
 * Data model: the v2 JSON is {type:'world'|'include'|'exclude', countries:[...]}
 * — a single global rights type per book with an optional list of ISO 3166-1
 * country codes. One row per book is the correct normalised form for this
 * structure (not per-territory rows, since all territories share a single type).
 *
 * Backfills by decoding the mbs_territorial_rights_v2 JSON for each book that
 * has the key. INSERT IGNORE on PRIMARY KEY makes the backfill idempotent.
 *
 * @package MyBookShowroom
 * @since   1.7.0
 */

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

/**
 * Migration 4.11: mbs_book_territorial_rights 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_11 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.11';
	}

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

		$table           = $wpdb->prefix . 'mbs_book_territorial_rights';
		$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} (
					post_id     BIGINT UNSIGNED NOT NULL,
					rights_type VARCHAR(10)     NOT NULL DEFAULT 'world',
					countries   TEXT            NULL,
					PRIMARY KEY (post_id),
					KEY idx_rights_type (rights_type)
				) {$charset_collate}"
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange
		}

		// --- Backfill: all posts that have mbs_territorial_rights_v2 ----------
		// 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 = %s AND meta_value != ''",
				'mbs_territorial_rights_v2'
			)
		);

		$allowed_types = array( 'world', 'include', 'exclude' );

		foreach ( (array) $post_ids as $raw_post_id ) {
			$post_id   = (int) $raw_post_id;
			$json_raw  = (string) get_post_meta( $post_id, 'mbs_territorial_rights_v2', true );
			$decoded   = json_decode( $json_raw, true );

			if ( ! is_array( $decoded ) ) {
				continue; // Skip malformed JSON.
			}

			$rights_type = isset( $decoded['type'] ) && in_array( $decoded['type'], $allowed_types, true )
				? $decoded['type']
				: 'world';

			$countries_raw = ( isset( $decoded['countries'] ) && is_array( $decoded['countries'] ) )
				? $decoded['countries']
				: array();

			// Normalise country codes to uppercase; skip empty strings.
			$countries = array_values( array_filter(
				array_map( 'strtoupper', $countries_raw ),
				static fn( $c ) => '' !== $c
			) );

			$countries_json = empty( $countries ) ? null : wp_json_encode( $countries );

			if ( null === $countries_json ) {
				// No countries list (world type or empty).
				// 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, rights_type)
						 VALUES (%d, %s)",
						$post_id,
						$rights_type
					)
				);
			} else {
				// 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, rights_type, countries)
						 VALUES (%d, %s, %s)",
						$post_id,
						$rights_type,
						$countries_json
					)
				);
			}
		}
	}
}
