<?php
/**
 * Annual membership fee renewals (per calendar year).
 *
 * @package TonItaliaRegistration
 */

defined( 'ABSPATH' ) || exit;

/**
 * Class TON_Reg_Membership_Renewals
 */
class TON_Reg_Membership_Renewals {

	const STATUS_PENDING = 'pending';
	const STATUS_PAID    = 'paid';

	/**
	 * @return string
	 */
	public static function table_name() {
		global $wpdb;
		return $wpdb->prefix . 'ton_reg_membership_renewals';
	}

	/**
	 * Create renewals table.
	 */
	public static function create_table() {
		global $wpdb;

		$table   = self::table_name();
		$charset = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			registration_id bigint(20) unsigned NOT NULL,
			year smallint(5) unsigned NOT NULL,
			status varchar(20) NOT NULL DEFAULT 'pending',
			paid_at datetime DEFAULT NULL,
			paid_by bigint(20) unsigned DEFAULT NULL,
			reminder_first_jan_sent_at datetime DEFAULT NULL,
			reminder_last_jan_sent_at datetime DEFAULT NULL,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY registration_year (registration_id, year),
			KEY year (year),
			KEY status (status)
		) {$charset};";

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

	/**
	 * Current calendar year (site timezone).
	 *
	 * @return int
	 */
	public static function current_year() {
		return (int) wp_date( 'Y' );
	}

	/**
	 * @param object $registration Registration row.
	 * @return int
	 */
	public static function admission_year( $registration ) {
		if ( ! empty( $registration->libro_associati_date ) ) {
			$timestamp = strtotime( (string) $registration->libro_associati_date );
			if ( $timestamp ) {
				return (int) wp_date( 'Y', $timestamp );
			}
		}

		if ( ! empty( $registration->created_at ) ) {
			$timestamp = strtotime( (string) $registration->created_at );
			if ( $timestamp ) {
				return (int) wp_date( 'Y', $timestamp );
			}
		}

		return self::current_year();
	}

	/**
	 * Years shown in admin checkboxes: from admission through current calendar year only.
	 *
	 * @param object $registration Registration row.
	 * @return array{start:int,end:int}
	 */
	public static function get_checkbox_year_range( $registration ) {
		$start = self::admission_year( $registration );
		$end   = self::current_year();

		if ( $start > $end ) {
			$start = $end;
		}

		return array(
			'start' => $start,
			'end'   => $end,
		);
	}

	/**
	 * @param int $registration_id Registration ID.
	 * @param int $year            Calendar year.
	 * @return string paid|pending
	 */
	public static function get_status( $registration_id, $year ) {
		$row = self::get_by_registration_year( $registration_id, $year );
		if ( ! $row || self::STATUS_PAID !== (string) $row->status ) {
			return self::STATUS_PENDING;
		}

		return self::STATUS_PAID;
	}

	/**
	 * @param int $registration_id Registration ID.
	 * @param int $year            Calendar year.
	 * @return object|null
	 */
	public static function get_by_registration_year( $registration_id, $year ) {
		global $wpdb;

		return $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM ' . self::table_name() . ' WHERE registration_id = %d AND year = %d',
				$registration_id,
				$year
			)
		);
	}

	/**
	 * @param int $registration_id Registration ID.
	 * @return array<int,object>
	 */
	public static function get_by_registration( $registration_id ) {
		global $wpdb;

		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM ' . self::table_name() . ' WHERE registration_id = %d ORDER BY year ASC',
				$registration_id
			)
		);
	}

	/**
	 * @param array<int> $registration_ids Registration IDs.
	 * @param int        $year             Calendar year.
	 * @return array<int,string> Map registration_id => status.
	 */
	public static function get_statuses_for_registrations( array $registration_ids, $year ) {
		global $wpdb;

		$registration_ids = array_values( array_filter( array_map( 'intval', $registration_ids ) ) );
		if ( empty( $registration_ids ) ) {
			return array();
		}

		$placeholders = implode( ',', array_fill( 0, count( $registration_ids ), '%d' ) );
		$params       = array_merge( array( (int) $year ), $registration_ids );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
		$sql = $wpdb->prepare(
			'SELECT registration_id, status FROM ' . self::table_name() . " WHERE year = %d AND registration_id IN ({$placeholders})",
			$params
		);

		$rows = $wpdb->get_results( $sql );
		$map  = array();

		foreach ( $registration_ids as $id ) {
			$map[ $id ] = self::STATUS_PENDING;
		}

		foreach ( $rows as $row ) {
			$map[ (int) $row->registration_id ] = (string) $row->status;
		}

		return $map;
	}

	/**
	 * @param int  $registration_id Registration ID.
	 * @param int  $year            Calendar year.
	 * @param bool $paid            Whether fee is paid.
	 * @param int  $user_id         Admin user marking payment.
	 * @return bool
	 */
	public static function set_paid( $registration_id, $year, $paid, $user_id = 0 ) {
		global $wpdb;

		$registration_id = (int) $registration_id;
		$year            = (int) $year;
		$user_id         = (int) $user_id;
		$now             = current_time( 'mysql', true );
		$existing        = self::get_by_registration_year( $registration_id, $year );

		$data = array(
			'status'     => $paid ? self::STATUS_PAID : self::STATUS_PENDING,
			'paid_at'    => $paid ? $now : null,
			'paid_by'    => $paid ? ( $user_id ?: null ) : null,
			'updated_at' => $now,
		);

		if ( $existing ) {
			return false !== $wpdb->update(
				self::table_name(),
				$data,
				array( 'id' => (int) $existing->id ),
				null,
				array( '%d' )
			);
		}

		$data['registration_id'] = $registration_id;
		$data['year']            = $year;
		$data['created_at']      = $now;

		return false !== $wpdb->insert( self::table_name(), $data );
	}

	/**
	 * Ensure a pending row exists for reminder tracking.
	 *
	 * @param int $registration_id Registration ID.
	 * @param int $year            Calendar year.
	 * @return object|null
	 */
	public static function ensure_pending_row( $registration_id, $year ) {
		$existing = self::get_by_registration_year( $registration_id, $year );
		if ( $existing ) {
			return $existing;
		}

		$now = current_time( 'mysql', true );
		global $wpdb;

		$wpdb->insert(
			self::table_name(),
			array(
				'registration_id' => (int) $registration_id,
				'year'            => (int) $year,
				'status'          => self::STATUS_PENDING,
				'created_at'      => $now,
				'updated_at'      => $now,
			)
		);

		return self::get_by_registration_year( $registration_id, $year );
	}

	/**
	 * @param int    $registration_id Registration ID.
	 * @param int    $year            Calendar year.
	 * @param string $reminder_type   first|last.
	 * @return bool
	 */
	public static function mark_reminder_sent( $registration_id, $year, $reminder_type ) {
		global $wpdb;

		$row = self::ensure_pending_row( $registration_id, $year );
		if ( ! $row ) {
			return false;
		}

		$column = 'first' === $reminder_type ? 'reminder_first_jan_sent_at' : 'reminder_last_jan_sent_at';
		$now    = current_time( 'mysql', true );

		return false !== $wpdb->update(
			self::table_name(),
			array(
				$column      => $now,
				'updated_at' => $now,
			),
			array( 'id' => (int) $row->id ),
			null,
			array( '%d' )
		);
	}

	/**
	 * Admitted members without paid renewal for a given year.
	 *
	 * @param int    $year          Calendar year.
	 * @param string $reminder_type first|last — skip if that reminder was already sent.
	 * @return array<int,object> Registration rows.
	 */
	public static function get_unpaid_admitted( $year, $reminder_type = '' ) {
		global $wpdb;

		$reg_table = TON_Reg_Database::table_name();
		$ren_table = self::table_name();
		$year      = (int) $year;

		$sql = "SELECT r.* FROM {$reg_table} r
			LEFT JOIN {$ren_table} ren ON ren.registration_id = r.id AND ren.year = %d
			WHERE r.status = 'admitted'
			AND r.anonymized_at IS NULL
			AND r.email <> ''
			AND ( ren.id IS NULL OR ren.status <> %s )";

		$params = array( $year, self::STATUS_PAID );

		if ( 'first' === $reminder_type ) {
			$sql     .= ' AND ( ren.reminder_first_jan_sent_at IS NULL )';
		} elseif ( 'last' === $reminder_type ) {
			$sql     .= ' AND ( ren.reminder_last_jan_sent_at IS NULL )';
		}

		$sql .= ' ORDER BY r.cognome ASC, r.nome ASC, r.id ASC';

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

	/**
	 * Registration IDs with pending renewal for a year.
	 *
	 * @param int $year Calendar year.
	 * @return array<int>
	 */
	public static function get_unpaid_registration_ids( $year ) {
		$rows = self::get_unpaid_admitted( $year );
		return array_map(
			static function ( $row ) {
				return (int) $row->id;
			},
			$rows
		);
	}

	/**
	 * Display label and CSS class for list table badge.
	 *
	 * @param string $status Renewal status (paid|pending).
	 * @param int    $year   Calendar year shown.
	 * @return array{label:string,class:string}
	 */
	public static function display_badge( $status, $year ) {
		$year = (int) $year;

		if ( self::STATUS_PAID === $status ) {
			return array(
				'label' => sprintf(
					/* translators: %d: calendar year */
					__( 'Pagato %d', 'ton-italia-registration' ),
					$year
				),
				'class' => 'ton-reg-renewal--paid',
			);
		}

		$month = (int) wp_date( 'n' );
		$day   = (int) wp_date( 'j' );

		if ( 1 === $month && $day >= 25 ) {
			return array(
				'label' => sprintf(
					/* translators: %d: calendar year */
					__( 'Sollecito %d', 'ton-italia-registration' ),
					$year
				),
				'class' => 'ton-reg-renewal--reminder',
			);
		}

		if ( $month >= 2 ) {
			return array(
				'label' => sprintf(
					/* translators: %d: calendar year */
					__( 'Non pagato %d', 'ton-italia-registration' ),
					$year
				),
				'class' => 'ton-reg-renewal--overdue',
			);
		}

		return array(
			'label' => sprintf(
				/* translators: %d: calendar year */
				__( 'Da pagare %d', 'ton-italia-registration' ),
				$year
			),
			'class' => 'ton-reg-renewal--pending',
		);
	}
}