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

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

class ADVK_Entries {

	/**
	 * Schema revision for the entries table, bumped independently of the plugin
	 * version so an in-place update migrates the table (dbDelta adds any missing
	 * columns/keys) even when the plugin version string stays the same.
	 *   2 = added the `calendar` column (multi-calendar support).
	 */
	const SCHEMA = 2;

	// 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';
	}

	/**
	 * Clamp a day filter to the valid range (1..31), or 0 for "no day filter".
	 *
	 * @param mixed $day Day value.
	 * @return int
	 */
	private static function clamp_day( $day ) {
		$day = (int) $day;
		return ( $day >= 1 && $day <= 31 ) ? $day : 0;
	}

	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,
			calendar VARCHAR(64) NOT NULL DEFAULT '',
			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 '',
			extra_fields LONGTEXT NULL,
			PRIMARY KEY  (id),
			KEY day (day),
			KEY calendar (calendar),
			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'] : '',
				'extra_fields'    => isset( $data['extra_fields'] ) ? $data['extra_fields'] : '',
				'calendar'        => isset( $data['calendar'] ) ? (string) $data['calendar'] : '',
			),
			array( '%s', '%d', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%d', '%d', '%s', '%s', '%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, $calendar = '' ) {
		global $wpdb;
		$table = self::table_name();
		$found = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM {$table} WHERE day = %d AND email = %s AND calendar = %s LIMIT 1",
				(int) $day,
				$email,
				(string) $calendar
			)
		);
		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          = self::clamp_day( isset( $args['day'] ) ? $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 ) {
			$clauses[] = 'day = %d';
			$prep[]    = $day;
		}
		if ( $winners_only ) {
			$clauses[] = 'is_winner = 1';
		}
		// Optional calendar scope: an explicit 'calendar' key (even '') filters to
		// that calendar; omitting it (or null) returns entries across all calendars.
		if ( array_key_exists( 'calendar', $args ) && null !== $args['calendar'] ) {
			$clauses[] = 'calendar = %s';
			$prep[]    = (string) $args['calendar'];
		}
		// Optional free-text search over name / email / lot number.
		if ( ! empty( $args['search'] ) ) {
			$like      = '%' . $wpdb->esc_like( (string) $args['search'] ) . '%';
			$clauses[] = '( first_name LIKE %s OR last_name LIKE %s OR email LIKE %s OR losnr LIKE %s )';
			$prep[]    = $like;
			$prep[]    = $like;
			$prep[]    = $like;
			$prep[]    = $like;
		}
		$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, $calendar = null, $search = '' ) {
		global $wpdb;
		$table = self::table_name();
		$day   = self::clamp_day( $day );

		$clauses = array();
		$prep    = array();
		if ( $day >= 1 ) {
			$clauses[] = 'day = %d';
			$prep[]    = $day;
		}
		if ( null !== $calendar ) {
			$clauses[] = 'calendar = %s';
			$prep[]    = (string) $calendar;
		}
		if ( '' !== (string) $search ) {
			$like      = '%' . $wpdb->esc_like( (string) $search ) . '%';
			$clauses[] = '( first_name LIKE %s OR last_name LIKE %s OR email LIKE %s OR losnr LIKE %s )';
			$prep[]    = $like;
			$prep[]    = $like;
			$prep[]    = $like;
			$prep[]    = $like;
		}
		if ( empty( $clauses ) ) {
			return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
		}
		$where = implode( ' AND ', $clauses );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $where is fixed literals; values bound via $prep.
		return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE {$where}", $prep ) );
	}

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

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

		// Shared calendar scope: null = all calendars; a string (incl. '') = that
		// calendar only. Using a "1 = 1" fallback keeps every query uniform.
		$cal_where = ( null !== $calendar ) ? 'calendar = %s' : '1 = 1';

		$agg = function ( $select, $extra = '' ) use ( $wpdb, $table, $cal_where, $calendar ) {
			$sql = "SELECT {$select} FROM {$table} WHERE {$cal_where}" . ( '' !== $extra ? " AND {$extra}" : '' );
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $select/$extra/$cal_where are fixed literals; the calendar value is bound.
			return null !== $calendar ? $wpdb->get_var( $wpdb->prepare( $sql, (string) $calendar ) ) : $wpdb->get_var( $sql );
		};

		$total         = (int) $agg( 'COUNT(*)' );
		$unique_emails = (int) $agg( 'COUNT(DISTINCT email)' );
		$winners       = (int) $agg( 'COUNT(*)', 'is_winner = 1' );
		$full_correct  = (int) $agg( 'COUNT(*)', 'total_questions > 0 AND correct_count = total_questions' );
		$sum_correct   = (int) $agg( 'COALESCE(SUM(correct_count),0)' );
		$sum_questions = (int) $agg( 'COALESCE(SUM(total_questions),0)' );

		// Per-day counts.
		$sql_pd  = "SELECT day, COUNT(*) AS c FROM {$table} WHERE {$cal_where} GROUP BY day";
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $cal_where is a fixed literal; the calendar value is bound.
		$rows    = null !== $calendar ? $wpdb->get_results( $wpdb->prepare( $sql_pd, (string) $calendar ), ARRAY_A ) : $wpdb->get_results( $sql_pd, 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, $calendar = null ) {
		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';
		}
		if ( null !== $calendar ) {
			$where .= ' AND calendar = %s';
			$prep[] = (string) $calendar;
		}

		$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, $calendar = null ) {
		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';
		}
		if ( null !== $calendar ) {
			$where .= ' AND calendar = %s';
			$prep[] = (string) $calendar;
		}
		$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, $calendar = null ) {
		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';
		}
		if ( null !== $calendar ) {
			$where .= ' AND calendar = %s';
			$prep[] = (string) $calendar;
		}
		// 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, $calendar = null ) {
		global $wpdb;
		$table = self::table_name();
		$day   = self::clamp_day( $day );

		$clauses = array();
		$prep    = array();
		if ( $day >= 1 ) {
			$clauses[] = 'day = %d';
			$prep[]    = $day;
		}
		if ( null !== $calendar ) {
			$clauses[] = 'calendar = %s';
			$prep[]    = (string) $calendar;
		}
		if ( empty( $clauses ) ) {
			$wpdb->query( "UPDATE {$table} SET is_winner = 0" );
			return;
		}
		$where = implode( ' AND ', $clauses );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $where is a fixed literal; values bound via $prep.
		$wpdb->query( $wpdb->prepare( "UPDATE {$table} SET is_winner = 0 WHERE {$where}", $prep ) );
	}

	// 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
}