<?php
/**
 * File Path: includes/data/class-mbs-arc-applicant-dao.php
 *
 * ARC Applicant DAO -- My Book Showroom
 *
 * Data Access Object for the mbs_arc_applicants table.
 * All methods use $wpdb->prepare() -- no raw query string interpolation.
 *
 * Sprint F-027: ARC Signup Block.
 *
 * @package    My_Book_Showroom
 * @subpackage Includes/Data
 * @since      1.2.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_Applicant_DAO { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * Insert a new applicant row.
	 *
	 * @param array $data Allowed keys: post_id, name, email, message, ip_hash.
	 * @return int|false Inserted row ID or false on failure.
	 */
	/**
	 * Retrieve a single pending ARC applicant row by ID.
	 *
	 * Returns null if the applicant does not exist or is no longer pending.
	 *
	 * @since  1.2.0
	 * @param  int   $id Applicant row ID.
	 * @return array|null Associative row array, or null if not found / not pending.
	 */
	public static function get_pending_by_id( int $id ): ?array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_arc_applicants';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d AND status = 'pending'", $id ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
		return $row ?: null;
	}

	public static function insert( array $data ): int|false {
		global $wpdb;
		$allowed = array( 'post_id', 'name', 'email', 'message', 'ip_hash' );
		$row     = array_intersect_key( $data, array_flip( $allowed ) );
		if ( empty( $row['post_id'] ) || empty( $row['email'] ) ) {
			return false;
		}
		$result = $wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prefix . 'mbs_arc_applicants',
			$row
		);
		return false !== $result ? (int) $wpdb->insert_id : false;
	}

	/**
	 * Return all pending applicants for a book, oldest first.
	 *
	 * @param int $post_id Book post ID.
	 * @return array[]
	 */
	public static function get_pending_by_book( int $post_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_arc_applicants';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table 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 AND status = 'pending' ORDER BY submitted_at ASC",
				$post_id
			),
			ARRAY_A
		) ?: array();
	}

	/**
	 * Count pending applicants for a book.
	 *
	 * @param int $post_id Book post ID.
	 * @return int
	 */
	public static function count_pending_by_book( int $post_id ): int {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_arc_applicants';
		// 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 status = 'pending'",
				$post_id
			)
		);
	}

	/**
	 * Update the status of an applicant row and set reviewed_at to NOW().
	 *
	 * @param int    $id     Row ID.
	 * @param string $status New status: 'approved' or 'rejected'.
	 * @return bool
	 */
	public static function set_status( int $id, string $status ): bool {
		global $wpdb;
		$allowed_statuses = array( 'approved', 'rejected' );
		if ( ! in_array( $status, $allowed_statuses, true ) ) {
			return false;
		}
		$table  = $wpdb->prefix . 'mbs_arc_applicants';
		$result = $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.PreparedSQL.InterpolatedNotPrepared
				"UPDATE {$table} SET status = %s, reviewed_at = NOW() WHERE id = %d",
				$status,
				$id
			)
		);
		return false !== $result && $result > 0;
	}

	/**
	 * Check whether an email address already exists for a book in either
	 * the applicant table or the reviewer table (dedup on submission).
	 *
	 * @param string $email   Sanitized email address.
	 * @param int    $post_id Book post ID.
	 * @return bool True if a duplicate exists.
	 */
	public static function email_exists_for_book( string $email, int $post_id ): bool {
		global $wpdb;
		$app_table  = $wpdb->prefix . 'mbs_arc_applicants';
		$rec_table  = $wpdb->prefix . 'mbs_arc_recipients';

		// Check applicants table (any status -- already applied or approved/rejected).
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$in_applicants = (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 {$app_table} WHERE email = %s AND post_id = %d",
				$email,
				$post_id
			)
		);
		if ( $in_applicants > 0 ) {
			return true;
		}

		// Check recipients table (contact_type = 'email').
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$in_recipients = (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 {$rec_table} WHERE contact_method = %s AND contact_type = 'email' AND post_id = %d",
				$email,
				$post_id
			)
		);
		return $in_recipients > 0;
	}
	/**
	 * Delete all applicant rows for a book.
	 *
	 * 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;
		$wpdb->delete( $wpdb->prefix . 'mbs_arc_applicants', 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
	}

}
