<?php
/**
 * File Path: includes/data/class-mbs-section-order-dao.php
 *
 * Section Order DAO
 *
 * Handles all reads/writes to the mbs_section_order table (authoritative source).
 *
 * @package MyBookShowroom
 * @since   1.0.0
 */

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

// 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_Section_Order_DAO { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * Get current section order for a book (front-end use — visible sections only).
	 *
	 * Fetches section_key + is_visible in one query and filters in PHP.
	 * Falls back to the global default order (post_id = 0) when no per-book
	 * rows exist. If the global defaults are themselves missing, writes a
	 * diagnostic flag to wp_options so the condition is surfaceable in the
	 * admin UI without requiring server log access (D-007).
	 *
	 * An intentional empty result (author hid all sections) is returned as []
	 * and does NOT fall back to the global defaults.
	 *
	 * @param int $post_id Post ID, or 0 for the global default order.
	 * @return string[] Array of visible section keys in display order.
	 */
	public static function get_order( int $post_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_section_order';

		$results = $wpdb->get_results( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT section_key, is_visible FROM {$table}
			 WHERE post_id = %d ORDER BY sort_order ASC",
			$post_id
		), ARRAY_A );

		if ( empty( $results ) ) {
			if ( $post_id !== 0 ) {
				// No per-book order — fall back to global defaults.
				return self::get_order( 0 );
			}

			// D-007: Global default rows missing (table not seeded or accidentally
			// truncated). Write a diagnostic flag so the admin UI can surface this
			// condition without needing server log access.
			update_option( 'mbs_section_order_warning', 'global_defaults_missing' );
			return array();
		}

		// Global defaults are intact — clear any stale D-007 warning flag.
		if ( $post_id === 0 ) {
			delete_option( 'mbs_section_order_warning' );
		}

		// Returns [] intentionally when all sections are hidden — does NOT fall
		// back to global defaults (author explicitly hid all sections).
		return array_column(
			array_filter( $results, static fn( $r ) => (bool) $r['is_visible'] ),
			'section_key'
		);
	}

	/**
	 * Get ALL section rows for a book regardless of visibility (admin use only).
	 *
	 * Returns every row for the post so hidden sections still appear in the
	 * Book Editor drag list with their checkbox unchecked. Falls back to global
	 * defaults (post_id = 0) when no per-book rows exist.
	 *
	 * N-HIGH-002 fix: also writes/clears the D-007 flag on admin page loads so
	 * the anomaly is detectable without waiting for a front-end visit.
	 *
	 * V8-MED-001 fix: clears the D-007 flag when global defaults are recovered.
	 *
	 * @param int $post_id Post ID, or 0 for the global default rows.
	 * @return array[] Each item: ['key' => string, 'is_visible' => bool, 'sort_order' => int].
	 */
	public static function get_order_admin( int $post_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_section_order';

		$results = $wpdb->get_results( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT section_key, is_visible, sort_order
			 FROM {$table} WHERE post_id = %d ORDER BY sort_order ASC",
			$post_id
		), ARRAY_A );

		if ( empty( $results ) && $post_id !== 0 ) {
			// No per-book rows — fall back to global defaults.
			return self::get_order_admin( 0 );
		}

		if ( empty( $results ) ) {
			// post_id === 0: global defaults missing. Write D-007 warning so
			// admin page loads surface the anomaly (N-HIGH-002 fix).
			update_option( 'mbs_section_order_warning', 'global_defaults_missing' );
			return array();
		}

		// Non-empty: clear any stale D-007 warning when checking global defaults
		// so admin pages reflect DB recovery without waiting for a front-end visit.
		// (V8-MED-001 fix)
		if ( 0 === $post_id ) {
			delete_option( 'mbs_section_order_warning' );
		}

		return array_map( static function( $row ) {
			return array(
				'key'        => $row['section_key'],
				'is_visible' => (bool) $row['is_visible'],
				'sort_order' => (int) $row['sort_order'],
			);
		}, $results );
	}

	/**
	 * Save section order for a book.
	 *
	 * Uses INSERT … ON DUPLICATE KEY UPDATE for atomic saves (ARCH-04).
	 * The UNIQUE KEY idx_post_section (post_id, section_key) makes this safe
	 * and avoids a race condition between DELETE and re-INSERT.
	 *
	 * Only sort_order is updated on duplicate — is_visible is preserved for
	 * existing rows. New rows receive is_visible = 1 via column DEFAULT.
	 *
	 * @param int      $post_id  Post ID to save the order for.
	 * @param string[] $sections Ordered array of section keys.
	 * @return void
	 */
	public static function save_order( int $post_id, array $sections ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_section_order';

		foreach ( $sections as $i => $section_key ) {
			$wpdb->query( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				"INSERT INTO {$table} (post_id, section_key, sort_order)
				 VALUES (%d, %s, %d)
				 ON DUPLICATE KEY UPDATE sort_order = VALUES(sort_order)",
				$post_id,
				sanitize_key( $section_key ),
				$i + 1
			) );
		}

		// Remove any section keys for this post that are no longer in the new order.
		if ( ! empty( $sections ) ) {
			$placeholders = implode( ',', array_fill( 0, count( $sections ), '%s' ) );
			$wpdb->query( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				"DELETE FROM {$table} WHERE post_id = %d AND section_key NOT IN ({$placeholders})",
				array_merge( array( $post_id ), array_map( 'sanitize_key', $sections ) )
			) );
		} else {
			// Empty array — clear all rows for this post.
			$wpdb->delete( $table, array( 'post_id' => $post_id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
		}
	}

	/**
	 * Update is_visible for existing section rows.
	 *
	 * Called after save_order() so the row already exists before this UPDATE runs.
	 * Guarded against post_id = 0 to prevent overwriting global default visibility.
	 *
	 * @param int   $post_id        Post ID. Must be > 0; method is a no-op for 0.
	 * @param array $visibility_map Map of section_key => bool (true = visible).
	 * @return void
	 */
	public static function save_visibility( int $post_id, array $visibility_map ): void {
		if ( $post_id <= 0 ) {
			return; // Guard: never overwrite global default visibility.
		}
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_section_order';
		foreach ( $visibility_map as $section_key => $is_visible ) {
			$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				$table,
				array( 'is_visible' => $is_visible ? 1 : 0 ),
				array( 'post_id' => $post_id, 'section_key' => sanitize_key( $section_key ) ),
				array( '%d' ),
				array( '%d', '%s' )
			);
		}
	}

	/**
	 * Update is_visible for global default section rows (post_id = 0).
	 *
	 * Companion to save_visibility() with no post_id guard — targets
	 * global defaults only. Called from the Formatting tab save callback
	 * after save_order( 0, $sections ) so the rows already exist.
	 *
	 * @param array $visibility_map Map of section_key => bool (true = visible).
	 * @return void
	 * @since 1.0.0
	 */
	public static function save_global_visibility( array $visibility_map ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_section_order';
		foreach ( $visibility_map as $section_key => $is_visible ) {
			$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				$table,
				array( 'is_visible' => $is_visible ? 1 : 0 ),
				array( 'post_id' => 0, 'section_key' => sanitize_key( $section_key ) ),
				array( '%d' ),
				array( '%d', '%s' )
			);
		}
	}

	/**
	 * Delete all section-order rows for a book.
	 *
	 * Called from the before_delete_post hook. Guarded against post_id = 0
	 * to prevent accidental deletion of the global default order rows.
	 *
	 * @param int $post_id Book post ID. Must be > 0; method is a no-op for 0.
	 * @return void
	 */
	public static function delete_for_post( int $post_id ): void {
		if ( $post_id <= 0 ) {
			return;
		}
		global $wpdb;
		$wpdb->delete( $wpdb->prefix . 'mbs_section_order', array( 'post_id' => $post_id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
	}

}
