db = $db ?? $GLOBALS['wpdb']; } private function t( string $name ): string { return $this->db->prefix . 'sg_' . $name; } /** @return array{id:int,goal_id:int,target_minor:int,title:string,description:string,reached_at:?string,reached_donation_id:?int,created_at:string} */ private static function cast( array $r ): array { return array( 'id' => (int) ( $r['id'] ?? 0 ), 'goal_id' => (int) ( $r['goal_id'] ?? 0 ), 'target_minor' => (int) ( $r['target_minor'] ?? 0 ), 'title' => (string) ( $r['title'] ?? '' ), 'description' => (string) ( $r['description'] ?? '' ), 'reached_at' => isset( $r['reached_at'] ) && '' !== (string) $r['reached_at'] ? (string) $r['reached_at'] : null, 'reached_donation_id' => isset( $r['reached_donation_id'] ) && null !== $r['reached_donation_id'] ? (int) $r['reached_donation_id'] : null, 'created_at' => (string) ( $r['created_at'] ?? '' ), ); } /** Tiers of a goal ordered by target ascending. @return array[] */ public function forGoal( int $goalId ): array { $rows = $this->db->get_results( $this->db->prepare( 'SELECT * FROM ' . $this->t( 'goal_tiers' ) . ' WHERE goal_id = %d ORDER BY target_minor ASC, id ASC', $goalId ), ARRAY_A ); return array_map( array( self::class, 'cast' ), (array) $rows ); } public function find( int $id ): ?array { $row = $this->db->get_row( $this->db->prepare( 'SELECT * FROM ' . $this->t( 'goal_tiers' ) . ' WHERE id = %d', $id ), ARRAY_A ); return $row ? self::cast( $row ) : null; } /** Tier counts per goal id (one query) for the goals list. @return array */ public function countsByGoal(): array { $rows = $this->db->get_results( 'SELECT goal_id, COUNT(*) AS n FROM ' . $this->t( 'goal_tiers' ) . ' GROUP BY goal_id', ARRAY_A ); $out = array(); foreach ( (array) $rows as $r ) { $out[ (int) $r['goal_id'] ] = (int) $r['n']; } return $out; } /** @param array{target_minor:int,title:string,description:string} $d @return int new id (0 on failure, e.g. duplicate target) */ public function create( int $goalId, array $d ): int { $ok = $this->db->insert( $this->t( 'goal_tiers' ), array( 'goal_id' => $goalId, 'target_minor' => (int) $d['target_minor'], 'title' => (string) $d['title'], 'description' => (string) $d['description'], 'created_at' => gmdate( 'Y-m-d H:i:s' ), ), array( '%d', '%d', '%s', '%s', '%s' ) ); return $ok ? (int) $this->db->insert_id : 0; } public function update( int $id, int $goalId, array $d ): void { $this->db->update( $this->t( 'goal_tiers' ), array( 'target_minor' => (int) $d['target_minor'], 'title' => (string) $d['title'], 'description' => (string) $d['description'] ), array( 'id' => $id, 'goal_id' => $goalId ), array( '%d', '%s', '%s' ), array( '%d', '%d' ) ); } public function delete( int $id, int $goalId ): void { $this->db->delete( $this->t( 'goal_tiers' ), array( 'id' => $id, 'goal_id' => $goalId ), array( '%d', '%d' ) ); } /** * Mark every unreached tier of the goal with target <= $raised as reached by $donationId. One atomic * UPDATE per tier (WHERE reached_at IS NULL): replays and concurrent callers cannot re-mark a tier. * * @return int[] ids of the tiers THIS call marked. */ public function markReached( int $goalId, int $raised, int $donationId ): array { $marked = array(); if ( $goalId <= 0 || $donationId <= 0 ) { return $marked; } $now = gmdate( 'Y-m-d H:i:s' ); foreach ( TierPolicy::newlyReached( $this->forGoal( $goalId ), $raised ) as $tier ) { $n = $this->db->query( $this->db->prepare( 'UPDATE ' . $this->t( 'goal_tiers' ) . ' SET reached_at = %s, reached_donation_id = %d WHERE id = %d AND reached_at IS NULL', $now, $donationId, (int) $tier['id'] ) ); if ( 1 === (int) $n ) { $marked[] = (int) $tier['id']; } } return $marked; } /** * Tiers unlocked by each donation, one query for the whole page: donation id => [ {title}, ... ] in target order. * * @param int[] $donationIds * @return array> */ public function reachedByDonation( array $donationIds ): array { $ids = array_values( array_unique( array_filter( array_map( 'intval', $donationIds ), static fn( $i ) => $i > 0 ) ) ); if ( ! $ids ) { return array(); } $in = implode( ',', array_fill( 0, count( $ids ), '%d' ) ); $rows = $this->db->get_results( $this->db->prepare( 'SELECT reached_donation_id, title FROM ' . $this->t( 'goal_tiers' ) . " WHERE reached_donation_id IN ($in) ORDER BY target_minor ASC, id ASC", ...$ids ), ARRAY_A ); $out = array(); foreach ( (array) $rows as $r ) { $out[ (int) $r['reached_donation_id'] ][] = array( 'title' => (string) $r['title'] ); } return $out; } /** * Keep sg_goals.target_minor equal to the highest tier target. Leaves the goal untouched when it has no tiers. * * @return bool true when the goal row was set from tiers. */ public function syncGoalTarget( int $goalId ): bool { $max = $this->db->get_var( $this->db->prepare( 'SELECT MAX(target_minor) FROM ' . $this->t( 'goal_tiers' ) . ' WHERE goal_id = %d', $goalId ) ); if ( null === $max || (int) $max <= 0 ) { return false; } $this->db->query( $this->db->prepare( 'UPDATE ' . $this->t( 'goals' ) . ' SET target_minor = %d WHERE id = %d', (int) $max, $goalId ) ); return true; } }