<?php
/**
 * Storage layer for participation entries (custom table) + reporting helpers.
 */

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

class ADVK_Entries {

	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Dedicated data layer for the custom {prefix}advk_entries table: the table name comes from $wpdb->prefix, every user value is bound through $wpdb->prepare(), and ORDER BY columns/direction are whitelisted. Object caching is not applicable to these per-request admin/AJAX reads.

	public static function table_name() {
		global $wpdb;
		return $wpdb->prefix . 'advk_entries';
	}

	public static function create_table() {
		global $wpdb;
		$table           = self::table_name();
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table} (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			created_at DATETIME NOT NULL,
			day TINYINT(3) UNSIGNED NOT NULL,
			first_name VARCHAR(190) NOT NULL DEFAULT '',
			last_name VARCHAR(190) NOT NULL DEFAULT '',
			email VARCHAR(190) NOT NULL DEFAULT '',
			consent TINYINT(1) NOT NULL DEFAULT 0,
			losnr VARCHAR(40) NOT NULL DEFAULT '',
			answers LONGTEXT NULL,
			social LONGTEXT NULL,
			correct_count TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
			total_questions TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
			is_winner TINYINT(1) NOT NULL DEFAULT 0,
			ref_code VARCHAR(20) NOT NULL DEFAULT '',
			referred_by VARCHAR(20) NOT NULL DEFAULT '',
			ip VARCHAR(100) NOT NULL DEFAULT '',
			consent_ref VARCHAR(255) NOT NULL DEFAULT '',
			PRIMARY KEY  (id),
			KEY day (day),
			KEY email (email),
			KEY is_winner (is_winner),
			KEY ref_code (ref_code),
			KEY referred_by (referred_by)
		) {$charset_collate};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );
	}

	/**
	 * Insert an entry. $data already sanitized by caller.
	 *
	 * @param array $data Entry fields.
	 * @return int|false Insert id or false.
	 */
	public static function insert( $data ) {
		global $wpdb;
		$ok = $wpdb->insert(
			self::table_name(),
			array(
				'created_at'      => current_time( 'mysql' ),
				'day'             => (int) $data['day'],
				'first_name'      => $data['first_name'],
				'last_name'       => $data['last_name'],
				'email'           => $data['email'],
				'consent'         => $data['consent'] ? 1 : 0,
				'losnr'           => $data['losnr'],
				'answers'         => wp_json_encode( $data['answers'] ),
				'social'          => wp_json_encode( $data['social'] ),
				'correct_count'   => (int) $data['correct_count'],
				'total_questions' => (int) $data['total_questions'],
				'ref_code'        => isset( $data['ref_code'] ) ? $data['ref_code'] : '',
				'referred_by'     => isset( $data['referred_by'] ) ? $data['referred_by'] : '',
				'ip'              => $data['ip'],
				'consent_ref'     => isset( $data['consent_ref'] ) ? $data['consent_ref'] : '',
			),
			array( '%s', '%d', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%d', '%d', '%s', '%s', '%s', '%s' )
		);
		return $ok ? (int) $wpdb->insert_id : false;
	}

	/**
	 * Generate a referral code that is not yet used.
	 *
	 * @return string
	 */
	public static function generate_ref_code() {
		global $wpdb;
		$table = self::table_name();
		do {
			$code   = strtoupper( wp_generate_password( 8, false, false ) );
			$exists = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table} WHERE ref_code = %s LIMIT 1", $code ) );
		} while ( $exists );
		return $code;
	}

	/**
	 * How many entries were referred by the given code.
	 *
	 * @param string $code Referral code.
	 * @return int
	 */
	public static function referral_count( $code ) {
		global $wpdb;
		if ( '' === (string) $code ) {
			return 0;
		}
		$table = self::table_name();
		return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE referred_by = %s", $code ) );
	}

	/**
	 * Name of the participant who owns a referral code (the referrer), or ''.
	 *
	 * @param string $code Referral code.
	 * @return string
	 */
	public static function name_for_ref_code( $code ) {
		global $wpdb;
		$code = (string) $code;
		if ( '' === $code ) {
			return '';
		}
		$table = self::table_name();
		$row   = $wpdb->get_row( $wpdb->prepare( "SELECT first_name, last_name FROM {$table} WHERE ref_code = %s LIMIT 1", $code ) );
		return $row ? trim( $row->first_name . ' ' . $row->last_name ) : '';
	}

	/**
	 * Whether a referral code exists (used to validate inbound ?ref= codes).
	 *
	 * @param string $code Referral code.
	 * @return bool
	 */
	public static function ref_code_exists( $code ) {
		global $wpdb;
		if ( '' === (string) $code ) {
			return false;
		}
		$table = self::table_name();
		return (bool) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table} WHERE ref_code = %s LIMIT 1", $code ) );
	}

	/**
	 * Leaderboard of top referrers (code, count, name).
	 *
	 * @param int $limit Max rows.
	 * @return array
	 */
	public static function top_referrers( $limit = 10 ) {
		global $wpdb;
		$table = self::table_name();
		$limit = max( 1, (int) $limit );
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT e.ref_code AS code, e.first_name, e.last_name, e.email,
					(SELECT COUNT(*) FROM {$table} r WHERE r.referred_by = e.ref_code) AS invites
				FROM {$table} e
				WHERE e.ref_code <> ''
				GROUP BY e.ref_code
				HAVING invites > 0
				ORDER BY invites DESC
				LIMIT %d",
				$limit
			)
		);
	}

	/**
	 * Whether an entry with this email already exists for the given day.
	 *
	 * @param string $email Email.
	 * @param int    $day   Day.
	 * @return bool
	 */
	public static function email_exists_for_day( $email, $day ) {
		global $wpdb;
		$table = self::table_name();
		$found = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM {$table} WHERE day = %d AND email = %s LIMIT 1",
				(int) $day,
				$email
			)
		);
		return ! empty( $found );
	}

	/**
	 * Fetch a single entry.
	 *
	 * @param int $id Entry id.
	 * @return object|null
	 */
	public static function get( $id ) {
		global $wpdb;
		$table = self::table_name();
		return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", (int) $id ) );
	}

	/**
	 * Permanently delete a single entry.
	 *
	 * @param int $id Entry id.
	 * @return bool Whether a row was removed.
	 */
	public static function delete( $id ) {
		global $wpdb;
		$id = (int) $id;
		if ( $id <= 0 ) {
			return false;
		}
		return (bool) $wpdb->delete( self::table_name(), array( 'id' => $id ), array( '%d' ) );
	}

	/**
	 * Fetch a page of entries with optional day filter.
	 *
	 * @param array $args per_page, offset, day, orderby, order, winners_only.
	 * @return array
	 */
	public static function query( $args = array() ) {
		global $wpdb;
		$table = self::table_name();

		$per_page     = isset( $args['per_page'] ) ? (int) $args['per_page'] : 25;
		$offset       = isset( $args['offset'] ) ? (int) $args['offset'] : 0;
		$day          = isset( $args['day'] ) ? (int) $args['day'] : 0;
		$winners_only = ! empty( $args['winners_only'] );

		$allowed_orderby = array( 'created_at', 'day', 'last_name', 'correct_count' );
		$orderby         = ( isset( $args['orderby'] ) && in_array( $args['orderby'], $allowed_orderby, true ) ) ? $args['orderby'] : 'created_at';
		$order           = ( isset( $args['order'] ) && strtoupper( $args['order'] ) === 'ASC' ) ? 'ASC' : 'DESC';

		$clauses = array( '1=1' );
		$prep    = array();
		if ( $day >= 1 && $day <= 24 ) {
			$clauses[] = 'day = %d';
			$prep[]    = $day;
		}
		if ( $winners_only ) {
			$clauses[] = 'is_winner = 1';
		}
		$where = implode( ' AND ', $clauses );

		$sql    = "SELECT * FROM {$table} WHERE {$where} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";
		$prep[] = $per_page;
		$prep[] = $offset;

		return $wpdb->get_results( $wpdb->prepare( $sql, $prep ) );
	}

	public static function count( $day = 0 ) {
		global $wpdb;
		$table = self::table_name();
		if ( $day >= 1 && $day <= 24 ) {
			return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE day = %d", $day ) );
		}
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
	}

	/* ---------------- reporting ---------------- */

	/**
	 * Aggregate stats for the dashboard.
	 *
	 * @return array
	 */
	public static function stats() {
		global $wpdb;
		$table = self::table_name();

		$total            = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
		$unique_emails    = (int) $wpdb->get_var( "SELECT COUNT(DISTINCT email) FROM {$table}" );
		$winners          = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE is_winner = 1" );
		$full_correct     = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE total_questions > 0 AND correct_count = total_questions" );
		$sum_correct      = (int) $wpdb->get_var( "SELECT COALESCE(SUM(correct_count),0) FROM {$table}" );
		$sum_questions    = (int) $wpdb->get_var( "SELECT COALESCE(SUM(total_questions),0) FROM {$table}" );

		// Per-day counts.
		$rows    = $wpdb->get_results( "SELECT day, COUNT(*) AS c FROM {$table} GROUP BY day", ARRAY_A );
		$per_day = array_fill( 1, 24, 0 );
		foreach ( $rows as $r ) {
			$d = (int) $r['day'];
			if ( $d >= 1 && $d <= 24 ) {
				$per_day[ $d ] = (int) $r['c'];
			}
		}

		$hit_rate = $sum_questions > 0 ? round( ( $sum_correct / $sum_questions ) * 100 ) : 0;

		return array(
			'total'         => $total,
			'unique_emails' => $unique_emails,
			'winners'       => $winners,
			'full_correct'  => $full_correct,
			'hit_rate'      => $hit_rate,
			'per_day'       => $per_day,
		);
	}

	/* ---------------- winners ---------------- */

	/**
	 * Draw a random winner for a day. Optionally only among fully-correct
	 * entries. Marks the chosen entry as winner.
	 *
	 * @param int  $day          Day (1..24).
	 * @param bool $correct_only Restrict pool to full-correct entries.
	 * @return object|null The winning entry, or null if pool empty.
	 */
	public static function draw_winner( $day, $correct_only = false ) {
		global $wpdb;
		$table = self::table_name();

		$where = 'day = %d';
		$prep  = array( (int) $day );
		if ( $correct_only ) {
			$where .= ' AND total_questions > 0 AND correct_count = total_questions';
		}

		$id = $wpdb->get_var(
			$wpdb->prepare( "SELECT id FROM {$table} WHERE {$where} ORDER BY RAND() LIMIT 1", $prep )
		);
		if ( ! $id ) {
			return null;
		}

		$wpdb->update( $table, array( 'is_winner' => 1 ), array( 'id' => (int) $id ), array( '%d' ), array( '%d' ) );
		return self::get( $id );
	}

	/**
	 * Draw several distinct winners for a day at once (excludes entries that are
	 * already winners, so repeated draws never pick the same person twice).
	 * Returns the newly-marked winners in draw order.
	 *
	 * @param int  $day          Door day.
	 * @param int  $count        How many winners to draw.
	 * @param bool $correct_only Only among fully-correct entries.
	 * @return array Entry objects (may be fewer than $count if not enough entries).
	 */
	public static function draw_winners( $day, $count, $correct_only = false ) {
		global $wpdb;
		$table = self::table_name();
		$count = max( 1, (int) $count );

		$where = 'day = %d AND is_winner = 0';
		$prep  = array( (int) $day );
		if ( $correct_only ) {
			$where .= ' AND total_questions > 0 AND correct_count = total_questions';
		}
		$prep[] = $count;

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $where is a fixed literal string built above; all values are bound via $prep.
		$ids = $wpdb->get_col( $wpdb->prepare( "SELECT id FROM {$table} WHERE {$where} ORDER BY RAND() LIMIT %d", $prep ) );

		$winners = array();
		foreach ( $ids as $id ) {
			$wpdb->update( $table, array( 'is_winner' => 1 ), array( 'id' => (int) $id ), array( '%d' ), array( '%d' ) );
			$winners[] = self::get( (int) $id );
		}
		return $winners;
	}

	/**
	 * All eligible entry ids for a day (not yet winners), for weighted drawing.
	 *
	 * @param int  $day          Door day.
	 * @param bool $correct_only Only fully-correct entries.
	 * @return int[]
	 */
	public static function eligible_ids( $day, $correct_only = false ) {
		global $wpdb;
		$table = self::table_name();
		$where = 'day = %d AND is_winner = 0';
		$prep  = array( (int) $day );
		if ( $correct_only ) {
			$where .= ' AND total_questions > 0 AND correct_count = total_questions';
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $where is a fixed literal; values bound via $prep.
		$ids = $wpdb->get_col( $wpdb->prepare( "SELECT id FROM {$table} WHERE {$where}", $prep ) );
		return array_map( 'intval', (array) $ids );
	}

	/** @return array Donation amounts: [ entry_id => amount (float) ]. */
	public static function get_donations() {
		$m = get_option( 'advk_donations', array() );
		return is_array( $m ) ? $m : array();
	}

	/**
	 * Donation amount recorded for an entry (0 when none).
	 *
	 * @param int $id Entry id.
	 * @return float
	 */
	public static function donation_for( $id ) {
		$m = self::get_donations();
		return isset( $m[ (int) $id ] ) ? (float) $m[ (int) $id ] : 0.0;
	}

	/**
	 * Record (or clear, when amount ≤ 0) a donation for an entry.
	 *
	 * @param int   $id     Entry id.
	 * @param float $amount Donation amount in euros.
	 */
	public static function set_donation( $id, $amount ) {
		$m      = self::get_donations();
		$amount = (float) $amount;
		if ( $amount > 0 ) {
			$m[ (int) $id ] = round( $amount, 2 );
		} else {
			unset( $m[ (int) $id ] );
		}
		update_option( 'advk_donations', $m, false );
	}

	/**
	 * Toggle/clear winner flag.
	 *
	 * @param int $id   Entry id.
	 * @param int $flag 0 or 1.
	 */
	public static function set_winner( $id, $flag ) {
		global $wpdb;
		$wpdb->update( self::table_name(), array( 'is_winner' => $flag ? 1 : 0 ), array( 'id' => (int) $id ), array( '%d' ), array( '%d' ) );
	}

	/**
	 * Clear all winners for a given day (or all days when $day = 0).
	 *
	 * @param int $day Day or 0 for all.
	 */
	public static function clear_winners( $day = 0 ) {
		global $wpdb;
		$table = self::table_name();
		if ( $day >= 1 && $day <= 24 ) {
			$wpdb->query( $wpdb->prepare( "UPDATE {$table} SET is_winner = 0 WHERE day = %d", $day ) );
		} else {
			$wpdb->query( "UPDATE {$table} SET is_winner = 0" );
		}
	}

	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter
}