<?php
/**
 * File Path: includes/class-mbs-db-schema.php
 *
 * Database Schema Definitions
 *
 * Rules for this file:
 *   · Methods accept $charset_collate as the sole parameter and return
 *     a complete CREATE TABLE string ready for dbDelta().
 *   · No WordPress function calls, no $wpdb references — callers resolve
 *     the table name prefix and charset before calling these methods.
 *   · Column order and types must match the live table and the DAO.
 *     Any schema change is made here first, then reflected in a migration
 *     block in MBS_DB_Installer::maybe_migrate().
 *
 * @package MyBookShowroom
 * @since   1.0.0
 */

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

class MBS_DB_Schema { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * CREATE TABLE DDL for mbs_format_rows.
	 *
	 * @param string $table           Fully-qualified table name (e.g. wp_mbs_format_rows).
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	public static function format_rows( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id                    BIGINT UNSIGNED       NOT NULL AUTO_INCREMENT,
			post_id               BIGINT UNSIGNED       NOT NULL,
			format                VARCHAR(50)           NOT NULL DEFAULT 'paperback',
			format_label          VARCHAR(100)          NULL DEFAULT NULL,
			subtitle              VARCHAR(500)          NULL DEFAULT NULL,
			pub_date              DATE                  NULL DEFAULT NULL,
			edition_notes         VARCHAR(500)          NULL DEFAULT NULL,
			edition               VARCHAR(100)          NULL DEFAULT NULL,
			edition_number        TINYINT UNSIGNED      NULL DEFAULT NULL,
			edition_type          VARCHAR(10)           NULL DEFAULT NULL,
			isbn13                VARCHAR(17)           NULL DEFAULT NULL,
			asin                  VARCHAR(10)           NULL DEFAULT NULL,
			price                 DECIMAL(10,2) UNSIGNED NULL DEFAULT NULL,
			page_length           INT UNSIGNED          NULL DEFAULT NULL,
			narrator              VARCHAR(255)          NULL DEFAULT NULL,
			duration_hh           TINYINT UNSIGNED      NULL DEFAULT NULL,
			duration_mm           TINYINT UNSIGNED      NULL DEFAULT NULL,
			file_format           VARCHAR(50)           NULL DEFAULT NULL,
			audio_sample_id       BIGINT UNSIGNED       NULL DEFAULT NULL,
			cover_image_id        BIGINT UNSIGNED       NULL DEFAULT NULL,
			audiobook_cover_style VARCHAR(50)           NULL DEFAULT NULL,
			spinning_video_id     BIGINT UNSIGNED       NULL DEFAULT NULL,
			video_hover_mode      TINYINT(1)            NOT NULL DEFAULT 0,
			autographed_available TINYINT(1)            NOT NULL DEFAULT 0,
			out_of_print          TINYINT(1)            NOT NULL DEFAULT 0,
			square_item_id        VARCHAR(100)           NULL DEFAULT NULL,
			price_type            VARCHAR(2)            NULL DEFAULT NULL,
			currency_code         CHAR(3)               NULL DEFAULT NULL,
			supplier_name         VARCHAR(255)          NULL DEFAULT NULL,
			discount_code         VARCHAR(50)           NULL DEFAULT NULL,
			on_sale_date          DATE                  NULL DEFAULT NULL,
			returnability         VARCHAR(3)            NULL DEFAULT NULL,
			series_number         DECIMAL(5,2) UNSIGNED NULL DEFAULT NULL,
			sort_order            INT UNSIGNED          NOT NULL DEFAULT 0,
			active                TINYINT(1)            NOT NULL DEFAULT 1,
			is_deleted            TINYINT(1)            NOT NULL DEFAULT 0,
			deleted_at            DATETIME              NULL DEFAULT NULL,
			created_at            DATETIME              NULL DEFAULT NULL,
			updated_at            DATETIME              NULL DEFAULT NULL,
			PRIMARY KEY (id),
			KEY idx_post_id (post_id),
			KEY idx_isbn13 (isbn13),
			KEY idx_asin (asin)
		) {$charset_collate} ROW_FORMAT=DYNAMIC;";
	}

	/**
	 * CREATE TABLE DDL for mbs_buy_links.
	 *
	 * S13-13: Added locale_us/uk/ca/au/de/jp columns.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	public static function buy_links( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id               BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
			post_id          BIGINT UNSIGNED  NOT NULL,
			format_row_id    BIGINT UNSIGNED  NULL DEFAULT NULL,
			retailer         VARCHAR(50)      NOT NULL DEFAULT '',
			retailer_key     VARCHAR(50)      NULL DEFAULT NULL,
			url              VARCHAR(500)     NOT NULL,
			label            VARCHAR(100)     NULL DEFAULT NULL,
			scope            VARCHAR(20)      NOT NULL DEFAULT 'shared',
			sort_order       INT UNSIGNED     NOT NULL DEFAULT 0,
			custom_store_id  VARCHAR(100)     NULL DEFAULT NULL,
			embed_snippet    LONGTEXT         NULL DEFAULT NULL,
			is_direct_author TINYINT(1)       NOT NULL DEFAULT 0,
			is_deleted       TINYINT(1)       NOT NULL DEFAULT 0,
			deleted_at       DATETIME         NULL DEFAULT NULL,
			created_at       DATETIME         NULL DEFAULT NULL,
			locale_us        VARCHAR(500)     NULL DEFAULT NULL,
			locale_uk        VARCHAR(500)     NULL DEFAULT NULL,
			locale_ca        VARCHAR(500)     NULL DEFAULT NULL,
			locale_au        VARCHAR(500)     NULL DEFAULT NULL,
			locale_de        VARCHAR(500)     NULL DEFAULT NULL,
			locale_jp           VARCHAR(500)     NULL DEFAULT NULL,
			link_type           VARCHAR(20)      NOT NULL DEFAULT 'buy',
			link_label          VARCHAR(100)     NULL DEFAULT NULL,
			icon_attachment_id  INT UNSIGNED     NULL DEFAULT NULL,
			PRIMARY KEY (id),
			KEY idx_post_id (post_id),
			KEY idx_format_row_id (format_row_id)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_endorsements.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	public static function endorsements( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id                BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
			post_id           BIGINT UNSIGNED  NOT NULL,
			quote_text        TEXT             NOT NULL,
			attributed_to     VARCHAR(255)     NOT NULL DEFAULT '',
			attribution_title VARCHAR(255)     NOT NULL DEFAULT '',
			sort_order        INT UNSIGNED     NOT NULL DEFAULT 0,
			is_visible        TINYINT(1)       NOT NULL DEFAULT 1,
			is_deleted        TINYINT(1)       NOT NULL DEFAULT 0,
			deleted_at        DATETIME         NULL DEFAULT NULL,
			created_at        DATETIME         NULL DEFAULT NULL,
			PRIMARY KEY (id),
			KEY idx_post_id (post_id)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_section_order.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	public static function section_order( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id          BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
			post_id     BIGINT UNSIGNED  NOT NULL DEFAULT 0,
			section_key VARCHAR(50)      NOT NULL,
			sort_order  INT UNSIGNED     NOT NULL DEFAULT 0,
			is_visible  TINYINT(1)       NOT NULL DEFAULT 1,
			PRIMARY KEY (id),
			UNIQUE KEY idx_post_section (post_id, section_key)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_pa_api_cache.
	 *
	 * Keyed by ASIN. One row per product. fetched_at is stored in UTC.
	 * TTL enforcement is handled in MBS_PA_API_Cache_DAO::is_stale().
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	public static function pa_api_cache( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			asin           VARCHAR(10)   NOT NULL,
			price_amount   DECIMAL(10,2) DEFAULT NULL,
			sales_rank     INT UNSIGNED  DEFAULT NULL,
			price_currency CHAR(3)       DEFAULT NULL,
			star_rating    DECIMAL(3,2)  DEFAULT NULL,
			review_count   INT UNSIGNED  DEFAULT NULL,
			availability   VARCHAR(50)   DEFAULT NULL,
			fetched_at     DATETIME      NOT NULL,
			PRIMARY KEY (asin)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_contributors.
	 *
	 * Single source of truth — consumed by both install() and the 1.7
	 * migration block in maybe_migrate().
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	public static function contributors( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id            BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
			post_id       BIGINT UNSIGNED  NOT NULL,
			format_row_id BIGINT UNSIGNED  NULL DEFAULT NULL,
			name          VARCHAR(255)     NOT NULL DEFAULT '',
			role          VARCHAR(100)     NOT NULL DEFAULT '',
			role_label    VARCHAR(255)     NULL DEFAULT NULL,
			sort_order    TINYINT UNSIGNED NOT NULL DEFAULT 0,
			public        TINYINT(1)       NOT NULL DEFAULT 1,
			deleted_at    DATETIME         NULL DEFAULT NULL,
			PRIMARY KEY (id),
			KEY idx_post_id (post_id),
			KEY idx_format_row_id (format_row_id)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_arc_recipients.
	 *
	 * Sprint ARC: per-book ARC reviewer tracking table.
	 * contact_method is free-text v1; contact_type added by F-026 via DB 2.2 migration.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	public static function arc_recipients( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id             BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
			post_id        BIGINT UNSIGNED  NOT NULL,
			reviewer_name  VARCHAR(255)     NOT NULL DEFAULT '',
			contact_method TEXT             NULL,
			contact_type   VARCHAR(20)      NULL DEFAULT NULL,
			date_sent      DATE             NULL DEFAULT NULL,
			review_posted  TINYINT(1)       NOT NULL DEFAULT 0,
			review_url     VARCHAR(512)     NULL DEFAULT NULL,
			review_urls    TEXT             NULL DEFAULT NULL,
			created_at     DATETIME         DEFAULT CURRENT_TIMESTAMP,
			status         VARCHAR(20)      NOT NULL DEFAULT 'sent',
			PRIMARY KEY (id),
			KEY idx_post_id (post_id)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_arc_review_urls.
	 *
	 * R2-004: Child table for per-recipient review URLs, replacing the
	 * review_urls TEXT JSON column in mbs_arc_recipients. One row per URL.
	 * Ordered by sort_order ASC.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 * @since 1.4.0
	 */
	public static function arc_review_urls( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id           BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
			recipient_id BIGINT UNSIGNED  NOT NULL,
			url          TEXT             NOT NULL,
			sort_order   TINYINT UNSIGNED NOT NULL DEFAULT 0,
			PRIMARY KEY (id),
			KEY idx_recipient_id (recipient_id)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_reading_order.
	 *
	 * R2-002: Junction table replacing JSON-serialized reading order stored in
	 * term_meta. One row per (term_id, post_id) pair, partitioned by type so
	 * series, collection, and universe all share one table. Ordered by
	 * sort_order ASC within each (term_id, type) group.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 * @since 1.5.0
	 */
	public static function reading_order( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id         BIGINT UNSIGNED                           NOT NULL AUTO_INCREMENT,
			term_id    BIGINT UNSIGNED                           NOT NULL,
			post_id    BIGINT UNSIGNED                           NOT NULL,
			sort_order INT UNSIGNED                              NOT NULL DEFAULT 0,
			type       ENUM('series','collection','universe')    NOT NULL,
			PRIMARY KEY (id),
			UNIQUE KEY uk_term_post_type (term_id, post_id, type),
			KEY idx_term_type (term_id, type)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_activity_log.
	 *
	 * Dashboard Phase 2: Publisher-tier team edit history log.
	 * Capped at 200 rows by MBS_Activity_Log_DAO::prune().
	 *
	 * The meta TEXT NULL column was added by migration 4.4 (Session 159)
	 * to store action-specific JSON context. This DDL reflects the live schema
	 * as of migration 4.4 (DB-007 schema doc sync).
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	public static function activity_log( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id         BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
			book_id    BIGINT UNSIGNED  NOT NULL,
			user_id    BIGINT UNSIGNED  NOT NULL,
			action     VARCHAR(50)      NOT NULL DEFAULT 'edit',
			created_at DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
			meta       TEXT             NULL DEFAULT NULL,
			PRIMARY KEY (id),
			KEY idx_book_id (book_id),
			KEY idx_created_at (created_at)
		) {$charset_collate};";
	}
	/**
	 * CREATE TABLE DDL for mbs_book_contributors.
	 *
	 * F-020: Replaces mbs_contributors. Pivot table linking books to
	 * mbs_contributor taxonomy terms with ONIX role codes and per-assignment
	 * PUB-tier statement override.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	public static function book_contributors( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id               BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
			post_id          BIGINT UNSIGNED  NOT NULL,
			term_id          BIGINT UNSIGNED  NOT NULL,
			onix_role_code   VARCHAR(3)       NOT NULL DEFAULT 'A01',
			role_label       VARCHAR(255)     NULL DEFAULT NULL,
			sort_order       TINYINT UNSIGNED NOT NULL DEFAULT 0,
			public           TINYINT(1)       NOT NULL DEFAULT 1,
			statement        TEXT             NULL DEFAULT NULL,
			deleted_at       DATETIME         NULL DEFAULT NULL,
			PRIMARY KEY (id),
			KEY idx_post_id (post_id),
			KEY idx_term_id (term_id)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_related_products.
	 *
	 * F-020: PRO/PUB-gated. Stores ONIX List 51 product relations
	 * (edition succession, format variants, bundles).
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	public static function related_products( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id             BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
			post_id        BIGINT UNSIGNED  NOT NULL,
			relation_code  VARCHAR(2)       NOT NULL,
			related_isbn   VARCHAR(17)      NOT NULL,
			related_format VARCHAR(50)      NULL DEFAULT NULL,
			sort_order     INT UNSIGNED     NOT NULL DEFAULT 0,
			PRIMARY KEY (id),
			KEY idx_post_id (post_id),
			KEY idx_isbn (related_isbn)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_pos_locations.
	 *
	 * Sprint POS-3: named physical locations for in-person sales events.
	 * PUB-tier UI gate; tables always created at install time.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	public static function pos_locations( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id         BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
			name       VARCHAR(255)     NOT NULL,
			address    VARCHAR(500)     NULL DEFAULT NULL,
			is_active  TINYINT(1)       NOT NULL DEFAULT 1,
			created_at DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY idx_is_active (is_active)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_pos_inventory.
	 *
	 * Sprint POS-3: per-location, per-format-row stock counts.
	 * UNIQUE KEY on (location_id, format_row_id) enforces one row per pair;
	 * enables INSERT ... ON DUPLICATE KEY UPDATE upsert in the DAO.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	public static function pos_inventory( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id             BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
			location_id    BIGINT UNSIGNED  NOT NULL,
			format_row_id  BIGINT UNSIGNED  NOT NULL,
			stock_qty      INT UNSIGNED     NOT NULL DEFAULT 0,
			updated_at     DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY idx_loc_format (location_id, format_row_id),
			KEY idx_location_id (location_id),
			KEY idx_format_row_id (format_row_id)
		) {$charset_collate};";
	}


	/**
	 * CREATE TABLE DDL for mbs_arc_applicants.
	 *
	 * F-027: ARC self-signup applicant staging table.
	 * Separate from mbs_arc_recipients (author-curated). Applicants are promoted
	 * to mbs_arc_recipients on approval. ip_hash stores SHA-256 of submitter IP.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	public static function arc_applicants( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id           BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
			post_id      BIGINT UNSIGNED  NOT NULL,
			name         VARCHAR(255)     NOT NULL DEFAULT '',
			email        VARCHAR(200)     NOT NULL DEFAULT '',
			message      TEXT             NULL,
			status       VARCHAR(20)      NOT NULL DEFAULT 'pending',
			ip_hash      VARCHAR(64)      NULL,
			submitted_at DATETIME         DEFAULT CURRENT_TIMESTAMP,
			reviewed_at  DATETIME         NULL,
			PRIMARY KEY (id),
			KEY idx_post_id (post_id),
			KEY idx_email   (email(100))
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_buy_link_clicks (INSIGHTS-1).
	 *
	 * No user_id or IP columns -- only referrer_host for privacy.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	public static function buy_link_clicks( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id            BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
			link_id       BIGINT UNSIGNED  NOT NULL,
			post_id       BIGINT UNSIGNED  NOT NULL,
			retailer_key  VARCHAR(50)      NULL DEFAULT NULL,
			referrer_host VARCHAR(255)     NULL DEFAULT NULL,
			clicked_at    DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY idx_link_id (link_id),
			KEY idx_post_id (post_id),
			KEY idx_clicked_at (clicked_at)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_brand_feed_cache (DASH-2A).
	 *
	 * Stores up to 2 most-recent RSS results per feed URL (FIFO enforced in code).
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	public static function brand_feed_cache( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id           BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
			feed_url     VARCHAR(500)     NOT NULL,
			result_title TEXT             NOT NULL,
			result_url   VARCHAR(500)     NOT NULL,
			fetched_at   DATETIME         NOT NULL,
			PRIMARY KEY (id),
			KEY idx_feed_url (feed_url(191)),
			KEY idx_fetched_at (fetched_at)
		) {$charset_collate};";
	}
	/**
	 * CREATE TABLE DDL for mbs_book_collection_positions.
	 *
	 * Phase 8: Per-book, per-collection position table.
	 * Replaced the mbs_collection_{term_id}_position postmeta pattern with a
	 * normalised row (migration 4.12). The postmeta keys were fully retired by
	 * migration 4.17. UNIQUE KEY on (post_id, term_id) enforces one position
	 * per book-collection pair and enables INSERT ... ON DUPLICATE KEY UPDATE
	 * upserts in MBS_Book_Collection_Position_DAO.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	public static function book_collection_positions( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			post_id  BIGINT UNSIGNED NOT NULL,
			term_id  BIGINT UNSIGNED NOT NULL,
			position INT UNSIGNED    NOT NULL DEFAULT 0,
			PRIMARY KEY (id),
			UNIQUE KEY idx_book_collection (post_id, term_id),
			KEY idx_term_id (term_id)
		) {$charset_collate};";
	}
	/**
	 * CREATE TABLE DDL for mbs_book_club.
	 *
	 * Phase 9: Book-level book club header table. Stores the visibility flag
	 * and discussion guide HTML for each book. One row per book (PRIMARY KEY
	 * post_id). Replaces the mbs_book_club_enabled and mbs_book_club_guide
	 * postmeta keys as the canonical write target.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	public static function book_club( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			post_id BIGINT UNSIGNED  NOT NULL,
			enabled TINYINT UNSIGNED NOT NULL DEFAULT 0,
			guide   LONGTEXT         NULL,
			PRIMARY KEY (post_id)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_book_club_questions.
	 *
	 * Phase 9: Ordered discussion questions, one row per question.
	 * Replaces the mbs_book_club_questions postmeta JSON array.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	public static function book_club_questions( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id       BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
			post_id  BIGINT UNSIGNED   NOT NULL,
			position SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			question TEXT              NOT NULL,
			PRIMARY KEY (id),
			KEY idx_post_id (post_id)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_book_club_files.
	 *
	 * Phase 9: Ordered downloadable file attachments, one row per file.
	 * Replaces the mbs_book_club_files postmeta JSON array of {id,label} objects.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 */
	public static function book_club_files( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id            BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
			post_id       BIGINT UNSIGNED   NOT NULL,
			position      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			attachment_id BIGINT UNSIGNED   NOT NULL,
			label         VARCHAR(255)      NULL DEFAULT NULL,
			PRIMARY KEY (id),
			KEY idx_post_id (post_id)
		) {$charset_collate};";
	}
	/**
	 * CREATE TABLE DDL for mbs_esp_queue.
	 *
	 * RF-011: Replaces the mbs_esp_broadcast_queue wp_options ring-buffer.
	 * status index supports queue polling by status.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 * @since 1.3.0
	 */
	public static function esp_queue( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			post_id    BIGINT UNSIGNED NOT NULL,
			list_id    VARCHAR(100)    NOT NULL DEFAULT '',
			book_title VARCHAR(255)    NOT NULL DEFAULT '',
			provider   VARCHAR(50)     NOT NULL DEFAULT '',
			payload    LONGTEXT        NOT NULL,
			status     VARCHAR(20)     NOT NULL DEFAULT 'pending',
			created_at DATETIME        NULL DEFAULT NULL,
			sent_at    DATETIME        NULL DEFAULT NULL,
			PRIMARY KEY (id),
			KEY idx_post_id (post_id),
			KEY idx_status (status)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_affiliate_stores.
	 *
	 * RF-012: Replaces mbs_setting_affiliate_custom_stores wp_options JSON blob.
	 * UNIQUE KEY on store_key prevents duplicate slugs.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 * @since 1.3.0
	 */
	public static function affiliate_stores( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id            BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
			store_key     VARCHAR(50)      NOT NULL,
			store_name    VARCHAR(255)     NOT NULL,
			affiliate_url VARCHAR(2083)    NOT NULL,
			sort_order    TINYINT UNSIGNED NOT NULL DEFAULT 0,
			created_at    DATETIME         NULL DEFAULT NULL,
			updated_at    DATETIME         NULL DEFAULT NULL,
			PRIMARY KEY (id),
			UNIQUE KEY store_key_idx (store_key)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_author_social_links.
	 *
	 * RF-012: Replaces mbs_author_social_links term meta JSON blob.
	 * UNIQUE KEY on (term_id, platform) ensures one row per platform per author.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 * @since 1.3.0
	 */
	public static function author_social_links( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id         BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
			term_id    BIGINT UNSIGNED  NOT NULL,
			platform   VARCHAR(50)      NOT NULL,
			url        VARCHAR(2083)    NOT NULL,
			sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0,
			created_at DATETIME         NULL DEFAULT NULL,
			updated_at DATETIME         NULL DEFAULT NULL,
			PRIMARY KEY (id),
			KEY idx_term_id (term_id),
			UNIQUE KEY term_platform (term_id, platform)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_brand_feed_urls.
	 *
	 * RF-012: Replaces mbs_setting_brand_feed_urls wp_options JSON blob.
	 * UNIQUE KEY uses a 191-char prefix index (feed_url is VARCHAR(2083),
	 * too long for a full-column unique index on utf8mb4).
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 * @since 1.3.0
	 */
	public static function brand_feed_urls( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id         BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
			feed_url   VARCHAR(2083)    NOT NULL,
			sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0,
			created_at DATETIME         NULL DEFAULT NULL,
			updated_at DATETIME         NULL DEFAULT NULL,
			PRIMARY KEY (id),
			UNIQUE KEY feed_url_idx (feed_url(191))
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_book_meta.
	 *
	 * AUD-008: canonical reconstruction of the final mbs_book_meta column set (migrations 4-9/4-15/4-19/4-34/4-37/4-38).
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 * @since 1.3.9
	 */
	public static function book_meta( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			post_id                         BIGINT UNSIGNED  NOT NULL,
			isbn13                          VARCHAR(17)      DEFAULT NULL,
			lifecycle_state                 VARCHAR(40)      DEFAULT NULL,
			featured                        TINYINT UNSIGNED DEFAULT NULL,
			coming_soon                     TINYINT UNSIGNED DEFAULT NULL,
			word_count                      INT UNSIGNED     DEFAULT NULL,
			series_position                 DECIMAL(10,2)    DEFAULT NULL,
			series_reading_order_position   DECIMAL(10,2)    DEFAULT NULL,
			series_reading_order_hidden     TINYINT(1)       NOT NULL DEFAULT 0,
			universe_reading_order_hidden   TINYINT(1)       NOT NULL DEFAULT 0,
			universe_reading_order_position DECIMAL(10,2)    DEFAULT NULL,
			title_sort_key                  VARCHAR(255)     NOT NULL DEFAULT '',
			PRIMARY KEY (post_id),
			KEY idx_lifecycle_state (lifecycle_state),
			KEY idx_featured (featured),
			KEY idx_coming_soon (coming_soon),
			KEY idx_isbn13 (isbn13),
			KEY idx_series_position (series_position),
			KEY idx_title_sort_key (title_sort_key(191)),
			KEY idx_series_ro_pos (series_reading_order_position),
			KEY idx_universe_ro_pos (universe_reading_order_position)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_book_stats.
	 *
	 * AUD-008: migration 4-10 review aggregate cache.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 * @since 1.3.9
	 */
	public static function book_stats( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			post_id      BIGINT UNSIGNED NOT NULL,
			review_avg   DECIMAL(3,2)    DEFAULT NULL,
			review_count INT UNSIGNED    NOT NULL DEFAULT 0,
			PRIMARY KEY (post_id),
			KEY idx_review_avg (review_avg),
			KEY idx_review_count (review_count)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_book_awards.
	 *
	 * AUD-008: migration 4-7 awards/accolades.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 * @since 1.3.9
	 */
	public static function book_awards( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id         BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
			post_id    BIGINT UNSIGNED  NOT NULL,
			title      VARCHAR(255)     NOT NULL DEFAULT '',
			award_year YEAR             DEFAULT NULL,
			status     VARCHAR(20)      NOT NULL DEFAULT 'finalist',
			sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0,
			PRIMARY KEY (id),
			KEY idx_post_id (post_id)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_arc_campaigns.
	 *
	 * AUD-008: migration 4-8 one campaign row per book.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 * @since 1.3.9
	 */
	public static function arc_campaigns( string $table, string $charset_collate ): string {
		return "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            DEFAULT NULL,
			target_review_date DATE            DEFAULT NULL,
			open_date          DATE            DEFAULT NULL,
			close_date         DATE            DEFAULT NULL,
			deadline           DATE            DEFAULT NULL,
			availability_date  DATE            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};";
	}

	/**
	 * CREATE TABLE DDL for mbs_book_territorial_rights.
	 *
	 * AUD-008: migration 4-11 ONIX territorial rights v2.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 * @since 1.3.9
	 */
	public static function book_territorial_rights( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			post_id     BIGINT UNSIGNED NOT NULL,
			rights_type VARCHAR(10)     NOT NULL DEFAULT 'world',
			countries   TEXT            DEFAULT NULL,
			PRIMARY KEY (post_id),
			KEY idx_rights_type (rights_type)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_book_classification_codes.
	 *
	 * AUD-008: migration 4-6/4-27 per-book BISAC/Thema/BIC/Bowker codes.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 * @since 1.3.9
	 */
	public static function book_classification_codes( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id         BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
			post_id    BIGINT UNSIGNED  NOT NULL,
			scheme     VARCHAR(20)      NOT NULL,
			code       VARCHAR(50)      NOT NULL,
			sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0,
			PRIMARY KEY (id),
			UNIQUE KEY idx_post_scheme_code (post_id, scheme, code),
			KEY idx_scheme_code (scheme, code)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_classification_codes.
	 *
	 * AUD-008: seeder reference table (BISAC/Thema/BIC code descriptions). Lifted verbatim from class-mbs-classification-seeder.php.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 * @since 1.3.9
	 */
	public static function classification_codes( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
			scheme      VARCHAR(10)  NOT NULL,
			code        VARCHAR(30)  NOT NULL,
			description VARCHAR(512) NOT NULL DEFAULT '',
			notes       TEXT         NOT NULL,
			parent_code VARCHAR(30)  NOT NULL DEFAULT '',
			PRIMARY KEY (id),
			UNIQUE KEY scheme_code (scheme, code),
			KEY scheme_idx (scheme),
			KEY parent_idx (parent_code)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_bisac_thema_map.
	 *
	 * AUD-008: seeder reference table BISAC -> Thema mapping.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 * @since 1.3.9
	 */
	public static function bisac_thema_map( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
			bisac_code   VARCHAR(30)  NOT NULL,
			thema_code   VARCHAR(30)  NOT NULL,
			is_qualifier TINYINT(1)   NOT NULL DEFAULT 0,
			PRIMARY KEY (id),
			KEY bisac_idx (bisac_code),
			KEY thema_idx (thema_code)
		) {$charset_collate};";
	}

	/**
	 * CREATE TABLE DDL for mbs_bic_thema_map.
	 *
	 * AUD-008: seeder reference table BIC -> Thema mapping.
	 *
	 * @param string $table           Fully-qualified table name.
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return string
	 * @since 1.3.9
	 */
	public static function bic_thema_map( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
			id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
			bic_code     VARCHAR(30)  NOT NULL,
			thema_code   VARCHAR(30)  NOT NULL,
			is_qualifier TINYINT(1)   NOT NULL DEFAULT 0,
			PRIMARY KEY (id),
			KEY bic_idx (bic_code),
			KEY thema_idx (thema_code)
		) {$charset_collate};";
	}

}
