<?php
/**
 * File Path: includes/data/class-mbs-arc-recipient-dao.php
 *
 * ARC Recipient DAO
 *
 * Data Access Object for the mbs_arc_recipients table.
 * All database writes for ARC reviewer rows go through this class.
 * No direct $wpdb calls in business logic.
 *
 * @package MyBookShowroom
 * @since   1.1.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_Arc_Recipient_DAO { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * Return all reviewer rows for a given book, ordered by id ASC.
	 *
	 * @param int $post_id Book post ID.
	 * @return array Array of stdClass objects (table rows).
	 */
	public static function get_by_book( int $post_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_arc_recipients';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix
		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE post_id = %d ORDER BY id ASC",
				$post_id
			)
		) ?? array();
	}

	/**
	 * Insert a new reviewer row.
	 *
	 * @param array $data Associative array of column => value pairs.
	 *                    Valid keys: post_id, reviewer_name, contact_method,
	 *                    date_sent, review_posted, review_url.
	 *                    (contact_type added by F-026 migration.)
	 * @return int|false  Inserted row ID on success; false on failure.
	 */
	public static function insert( array $data ): int|false {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_arc_recipients';

		$allowed = array( 'post_id', 'reviewer_name', 'contact_method', 'contact_type',
		                  'date_sent', 'review_posted', 'review_url', 'review_urls' );
		$row     = array_intersect_key( $data, array_flip( $allowed ) );

		if ( empty( $row['post_id'] ) ) {
			return false;
		}

		// Uniqueness guard: prevent duplicate contact_method for the same post_id.
		if ( ! empty( $row['contact_method'] ) ) {
			$post_id       = (int) $row['post_id'];
			$contact_method = (string) $row['contact_method'];
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix
			$existing = $wpdb->get_var( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				"SELECT id FROM {$table} WHERE post_id = %d AND contact_method = %s LIMIT 1",
				$post_id, $contact_method
			) );
			if ( $existing ) {
				return (int) $existing; // already exists — return existing ID, don't duplicate
			}
		}

		$result = $wpdb->insert( $table, $row ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
		return ( false === $result ) ? false : (int) $wpdb->insert_id;
	}

	/**
	 * Update an existing reviewer row.
	 *
	 * @param int   $id   Row ID (primary key).
	 * @param array $data Associative array of column => value pairs.
	 *                    Valid keys: reviewer_name, contact_method, contact_type,
	 *                    date_sent, review_posted, review_url.
	 * @return bool True on success, false on failure.
	 * @since 1.1.0
	 */
	public static function update( int $id, array $data ): bool {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_arc_recipients';

		$allowed = array( 'reviewer_name', 'contact_method', 'contact_type',
		                  'date_sent', 'review_posted', 'review_url', 'review_urls' );
		$row     = array_intersect_key( $data, array_flip( $allowed ) );

		if ( empty( $row ) ) {
			return false;
		}

		return (bool) $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$table,
			$row,
			array( 'id' => $id ),
			null,
			array( '%d' )
		);
	}

	/**
	 * Delete a reviewer row by primary key.
	 *
	 * Also deletes child rows from mbs_arc_review_urls (R2-004).
	 *
	 * @param int $id Row ID.
	 * @return bool True on success.
	 */
	public static function delete( int $id ): bool {
		global $wpdb;
		// R2-004: delete child URL rows first.
		$wpdb->delete( $wpdb->prefix . 'mbs_arc_review_urls', array( 'recipient_id' => $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
		$table = $wpdb->prefix . 'mbs_arc_recipients';
		return (bool) $wpdb->delete( $table, array( 'id' => $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
	}

	/**
	 * Count reviewer rows for a given book.
	 *
	 * @param int $post_id Book post ID.
	 * @return int Number of reviewer rows.
	 */
	public static function count_by_book( int $post_id ): int {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_arc_recipients';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$table} WHERE post_id = %d",
				$post_id
			)
		);
	}

	/**
	 * Upsert a campaign row for a book.
	 *
	 * Performs INSERT ... ON DUPLICATE KEY UPDATE against mbs_arc_campaigns,
	 * so it is safe to call on every save regardless of whether the row exists.
	 * Pass an empty string for any date column to store NULL in the database.
	 *
	 * @param int   $post_id Book post ID.
	 * @param array $data    Accepted keys: status (string), copy_count (int),
	 *                       distribution_date, target_review_date, close_date,
	 *                       open_date, deadline, availability_date (all YYYY-MM-DD|'').
	 * @return void
	 */
	public static function save_campaign( int $post_id, array $data ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_arc_campaigns';

		$status      = sanitize_text_field( $data['status']      ?? 'not_started' );
		$copy_count  = (int) ( $data['copy_count'] ?? 0 );
		$date_keys   = array( 'distribution_date', 'target_review_date', 'close_date',
		                      'open_date', 'deadline', 'availability_date' );

		// Convert empty strings to NULL; non-empty dates keep their value.
		$dates = array();
		foreach ( $date_keys as $k ) {
			$v         = isset( $data[ $k ] ) ? (string) $data[ $k ] : null;
			$dates[ $k ] = ( '' === $v ) ? null : $v;
		}

		// Build placeholders: %s for non-null dates, literal NULL for null dates.
		$d_phs  = array();  // per-column SQL fragment for VALUES(...)
		$d_args = array();  // corresponding prepare() args (only for non-null dates)
		foreach ( $dates as $k => $v ) {
			if ( null === $v ) {
				$d_phs[ $k ] = 'NULL';
			} else {
				$d_phs[ $k ] = '%s';
				$d_args[]    = $v;
			}
		}

		$col_list = 'post_id, status, copy_count, '
			. implode( ', ', $date_keys );

		$val_list = '%d, %s, %d, '
			. implode( ', ', array_values( $d_phs ) );

		// ON DUPLICATE KEY UPDATE: mirror each column (use VALUES() for non-null,
		// literal NULL for columns where NULL was explicitly passed).
		$upd_parts = array( 'status = VALUES(status)', 'copy_count = VALUES(copy_count)' );
		foreach ( $date_keys as $k ) {
			$upd_parts[] = ( 'NULL' === $d_phs[ $k ] )
				? "{$k} = NULL"
				: "{$k} = VALUES({$k})";
		}
		$upd_sql = implode( ', ', $upd_parts );

		$args = array_merge( array( $post_id, $status, $copy_count ), $d_args );

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,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( // 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
				"INSERT INTO {$table} ({$col_list}) VALUES ({$val_list})
				 ON DUPLICATE KEY UPDATE {$upd_sql}", // 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
				...$args
			)
		);
	}

	/**
	 * Retrieve the campaign row for a book.
	 *
	 * @param int $post_id Book post ID.
	 * @return object|null stdClass row or null if no campaign row exists yet.
	 */
	public static function get_campaign( int $post_id ): ?object {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_arc_campaigns';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$row = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE post_id = %d",
				$post_id
			)
		);
		return $row ?: null;
	}

		/**
	 * Count active ARC books (books with mbs_arc_status = 'active'),
	 * optionally excluding a specific post_id.
	 *
	 * Used by the FREE 1-book-Active enforcement gate.
	 *
	 * @param int $exclude_post_id Post ID to exclude from count (current book).
	 * @return int Number of OTHER books currently Active in ARC.
	 */
	public static function count_active_books( int $exclude_post_id = 0 ): int {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_arc_campaigns';

		if ( $exclude_post_id > 0 ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			return (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				$wpdb->prepare(
					"SELECT COUNT(*) FROM {$table} WHERE status = 'active' AND post_id != %d",
					$exclude_post_id
				)
			);
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE status = 'active'" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
	}

	/**
	 * Return all books currently Active in ARC (mbs_arc_status = 'active').
	 *
	 * Used by the dashboard pipeline lane (ARC-5).
	 *
	 * @return array Array of WP_Post objects.
	 */
	public static function get_active_arc_books(): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_arc_campaigns';

		// Single indexed JOIN — no WP_Query meta_query overhead.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$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 c.post_id
				   FROM {$table} c
				  INNER JOIN {$wpdb->posts} p ON p.ID = c.post_id
				  WHERE c.status = 'active'
				    AND p.post_type   = %s
				    AND p.post_status IN ('publish','draft','future')",
				MBS_POST_TYPE
			)
		);

		if ( empty( $ids ) ) {
			return array();
		}

		return get_posts(
			array(
				'post_type'      => MBS_POST_TYPE,
				'post__in'       => array_map( 'intval', $ids ),
				'posts_per_page' => -1,
				'orderby'        => 'post__in',
				'no_found_rows'  => true,
			)
		);
	}

	/**
	 * Count reviews received (review_posted = 1) for a given book.
	 *
	 * @param int $post_id Book post ID.
	 * @return int Number of rows with review_posted = 1.
	 */
	public static function count_reviews_received( int $post_id ): int {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_arc_recipients';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$table} WHERE post_id = %d AND review_posted = 1",
				$post_id
			)
		);
	}

	/**
	 * Return all books that have ever had an ARC campaign (any arc_status),
	 * enriched with stats for the DASH-2B Campaign Overview table.
	 *
	 * Each returned object has:
	 *   - post_id           (int)    Book post ID
	 *   - title             (string) Book title
	 *   - arc_status        (string) 'not_started' | 'active' | 'closed'
	 *   - copy_count        (int)    Copies allocated (mbs_arc_copy_count)
	 *   - copies_sent       (int)    Recipients in mbs_arc_recipients
	 *   - reviews_received  (int)    Recipients with review_posted = 1
	 *   - target_date       (string) mbs_arc_target_review_date or ''
	 *   - conversion_pct    (float)  reviews_received / copies_sent * 100 (0 if none sent)
	 *   - is_overdue        (bool)   target_date passed & reviews < sent & status active
	 *
	 * @return object[]
	 * @since 4.4.0
	 */
	public static function get_all_arc_books_with_stats(): array {
		global $wpdb;
		$campaigns_table  = $wpdb->prefix . 'mbs_arc_campaigns';
		$recipients_table = $wpdb->prefix . 'mbs_arc_recipients';

		// Single query replaces WP_Query + N+1 get_post_meta calls.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT
				    c.post_id,
				    p.post_title                                              AS title,
				    c.status                                                  AS arc_status,
				    c.copy_count,
				    COALESCE( c.target_review_date, '' )                      AS target_date,
				    COUNT( r.id )                                             AS copies_sent,
				    SUM( CASE WHEN r.review_posted = 1 THEN 1 ELSE 0 END )   AS reviews_received
				 FROM {$campaigns_table} c
				 INNER JOIN {$wpdb->posts} p
				         ON p.ID = c.post_id
				        AND p.post_type   = %s
				        AND p.post_status IN ('publish','draft','future','private')
				 LEFT JOIN {$recipients_table} r ON r.post_id = c.post_id
				 GROUP BY c.post_id, p.post_title, c.status, c.copy_count, c.target_review_date
				 ORDER BY p.post_modified DESC",
				MBS_POST_TYPE
			)
		);

		$results = array();
		foreach ( $rows as $row ) {
			$copies_sent      = (int) $row->copies_sent;
			$reviews_received = (int) $row->reviews_received;
			$target_date      = (string) $row->target_date;
			$arc_status       = (string) $row->arc_status;
			$conversion_pct   = $copies_sent > 0
				? round( $reviews_received / $copies_sent * 100, 1 )
				: 0.0;
			$is_overdue = (
				! empty( $target_date )
				&& strtotime( $target_date ) < time()
				&& $reviews_received < $copies_sent
				&& 'active' === $arc_status
			);
			$results[] = (object) array(
				'post_id'          => (int) $row->post_id,
				'title'            => (string) $row->title,
				'arc_status'       => $arc_status,
				'copy_count'       => (int) $row->copy_count,
				'copies_sent'      => $copies_sent,
				'reviews_received' => $reviews_received,
				'target_date'      => $target_date,
				'conversion_pct'   => $conversion_pct,
				'is_overdue'       => $is_overdue,
			);
		}
		return $results;
	}

	/**
	 * Delete all ARC data for a book across recipients and campaigns tables.
	 *
	 * Hard-deletes all rows in mbs_arc_recipients and mbs_arc_campaigns for the
	 * given post_id. Called from the before_delete_post hook so no orphan rows
	 * remain after book deletion.
	 *
	 * @param int $post_id Book post ID.
	 * @return void
	 */
	public static function delete_all_for_book( int $post_id ): void {
		global $wpdb;
		// R2-004: delete child URL rows for all recipients of this book.
		$recipient_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.PreparedSQL.InterpolatedNotPrepared
				"SELECT id FROM {$wpdb->prefix}mbs_arc_recipients WHERE post_id = %d",
				$post_id
			)
		);
		if ( ! empty( $recipient_ids ) ) {
			$ids_in = implode( ',', array_map( 'intval', $recipient_ids ) );
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$wpdb->query( "DELETE FROM {$wpdb->prefix}mbs_arc_review_urls WHERE recipient_id IN ({$ids_in})" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
		}
		$wpdb->delete( $wpdb->prefix . 'mbs_arc_recipients', 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
		$wpdb->delete( $wpdb->prefix . 'mbs_arc_campaigns',  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
	}


	/**
	 * Delete-and-reinsert review URLs for a given recipient (R2-004).
	 *
	 * Idempotent: deletes all existing URL rows for the recipient, then inserts
	 * the supplied array in sort_order sequence. Pass an empty array to clear all
	 * URLs without inserting any replacements.
	 *
	 * @param int   $recipient_id mbs_arc_recipients.id.
	 * @param array $urls         Flat list of sanitized URL strings.
	 * @return void
	 * @since 1.4.0
	 */
	public static function save_urls( int $recipient_id, array $urls ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_arc_review_urls';
		// Delete all existing URLs for this recipient.
		$wpdb->delete( $table, array( 'recipient_id' => $recipient_id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
		// Re-insert the supplied URLs in order.
		$sort = 0;
		foreach ( $urls as $url ) {
			$url = trim( (string) $url );
			if ( '' === $url ) {
				continue;
			}
			$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
				$table,
				array(
					'recipient_id' => $recipient_id,
					'url'          => $url,
					'sort_order'   => $sort,
				)
			);
			$sort++;
		}
	}

	/**
	 * Return all review URLs for every recipient belonging to a book (R2-004).
	 *
	 * Returns an associative array keyed by recipient_id (int), where each value
	 * is a flat list of URL strings ordered by sort_order ASC.
	 *
	 * @param int $post_id Book post ID.
	 * @return array<int, string[]> Map of recipient_id => [url, ...].
	 * @since 1.4.0
	 */
	public static function get_urls_for_book( int $post_id ): array {
		global $wpdb;
		$recipients_table = $wpdb->prefix . 'mbs_arc_recipients';
		$urls_table       = $wpdb->prefix . 'mbs_arc_review_urls';

		// Single JOIN fetches all URLs for all recipients in one query.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT u.recipient_id, u.url
				   FROM {$urls_table} u
				  INNER JOIN {$recipients_table} r ON r.id = u.recipient_id
				  WHERE r.post_id = %d
				  ORDER BY u.recipient_id ASC, u.sort_order ASC",
				$post_id
			)
		) ?? array();

		$map = array();
		foreach ( $rows as $row ) {
			$rid = (int) $row->recipient_id;
			if ( ! isset( $map[ $rid ] ) ) {
				$map[ $rid ] = array();
			}
			$map[ $rid ][] = (string) $row->url;
		}
		return $map;
	}

}
