<?php
declare(strict_types=1);

namespace WebCalendar\WP\Migration\Tec;

use DateTimeImmutable;

defined( 'ABSPATH' ) || exit;

/**
 * TEC reader over $wpdb.
 *
 * Reads wp_posts / wp_postmeta / term tables directly — the verified
 * authoritative store on TEC 6.x (tests/fixtures/tec/FORMAT-NOTES.md) —
 * so imports work even when the TEC plugin is deactivated, which is the
 * normal state mid-migration. Every query is a prepared statement; no TEC
 * code is loaded or executed.
 *
 * @psalm-api Wired into the import wizard and CLI in later stories.
 */
final class WpdbTecReader implements TecReaderInterface {

	private const POST_TYPE = 'tribe_events';

	private const META_KEYS = array(
		'_EventStartDate',
		'_EventEndDate',
		'_EventTimezone',
		'_EventAllDay',
		'_EventVenueID',
		'_EventOrganizerID',
		'_EventRecurrence',
		'_thumbnail_id',
		// The Events Calendar's Event Status feature. Absent on every event
		// that has never been cancelled or postponed, which is most of them.
		'_tec_event_status',
	);

	#[\Override]
	public function is_available(): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- read of another plugin's stored data; TEC may be deactivated.
		$count = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->prefix}posts WHERE post_type = %s",
				self::POST_TYPE
			)
		);
		return (int) $count > 0;
	}

	#[\Override]
	public function count_events_by_status(): array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- read of another plugin's stored data; TEC may be deactivated.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT post_status, COUNT(*) AS n FROM {$wpdb->prefix}posts WHERE post_type = %s GROUP BY post_status",
				self::POST_TYPE
			),
			ARRAY_A
		);

		$counts = array();
		foreach ( (array) $rows as $row ) {
			if ( is_array( $row ) && isset( $row['post_status'], $row['n'] ) ) {
				$counts[ is_scalar( $row['post_status'] ) ? (string) $row['post_status'] : '' ] = is_scalar( $row['n'] ) ? (int) $row['n'] : 0;
			}
		}
		return $counts;
	}

	#[\Override]
	public function events( ?DateTimeImmutable $modified_since = null ): array {
		global $wpdb;

		// Published and not password-protected: WordPress keeps a protected
		// event's content from visitors, and the calendar has no password
		// gate, so a mirror would publish it. published_ids() matches, so a
		// protected event's mirror is swept like an unpublished one.

		if ( null !== $modified_since ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- read of another plugin's stored data; TEC may be deactivated.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT ID, post_title, post_content, post_author FROM {$wpdb->prefix}posts WHERE post_type = %s AND post_status = 'publish' AND post_password = '' AND post_modified_gmt >= %s ORDER BY ID ASC",
					self::POST_TYPE,
					$modified_since->format( 'Y-m-d H:i:s' )
				),
				ARRAY_A
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- read of another plugin's stored data; TEC may be deactivated.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT ID, post_title, post_content, post_author FROM {$wpdb->prefix}posts WHERE post_type = %s AND post_status = 'publish' AND post_password = '' ORDER BY ID ASC",
					self::POST_TYPE
				),
				ARRAY_A
			);
		}

		$posts = array();
		foreach ( (array) $rows as $row ) {
			if ( is_array( $row ) && isset( $row['ID'], $row['post_title'], $row['post_content'], $row['post_author'] ) ) {
				$posts[] = $row;
			}
		}
		if ( array() === $posts ) {
			return array();
		}

		$ids        = array_map( static fn( array $r ): int => is_scalar( $r['ID'] ) ? (int) $r['ID'] : 0, $posts );
		$meta       = $this->meta_for( $ids );
		$categories = $this->categories_for( $ids );
		$tags       = $this->tags_for( $ids );
		$extras     = $this->additional_field_values( $ids );

		$events = array();
		foreach ( $posts as $row ) {
			$id = is_scalar( $row['ID'] ) ? (int) $row['ID'] : 0;
			$m  = $meta[ $id ] ?? array();

			$recurrence = null;
			if ( isset( $m['_EventRecurrence'] ) ) {
				$decoded = $this->unserialize_safely( $m['_EventRecurrence'] );
				if ( is_array( $decoded ) ) {
					/** @var array<string, mixed> $decoded */
					$recurrence = $decoded;
				}
			}

			$events[] = new TecEvent(
				id: $id,
				title: is_scalar( $row['post_title'] ) ? (string) $row['post_title'] : '',
				description: is_scalar( $row['post_content'] ) ? (string) $row['post_content'] : '',
				author_id: is_scalar( $row['post_author'] ) ? (int) $row['post_author'] : 0,
				start_local: $m['_EventStartDate'] ?? '',
				end_local: $m['_EventEndDate'] ?? '',
				timezone: $m['_EventTimezone'] ?? 'UTC',
				all_day: in_array( $m['_EventAllDay'] ?? '', array( '1', 'yes' ), true ),
				venue_id: isset( $m['_EventVenueID'] ) ? (int) $m['_EventVenueID'] : null,
				organizer_id: isset( $m['_EventOrganizerID'] ) ? (int) $m['_EventOrganizerID'] : null,
				thumbnail_id: isset( $m['_thumbnail_id'] ) ? (int) $m['_thumbnail_id'] : null,
				recurrence: $recurrence,
				category_ids: $categories[ $id ] ?? array(),
				tag_names: $tags[ $id ] ?? array(),
				extras: $extras[ $id ] ?? array(),
				status: $m['_tec_event_status'] ?? null,
			);
		}
		return $events;
	}

	#[\Override]
	public function published_ids(): array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- read of another plugin's stored data; TEC may be deactivated.
		$ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT ID FROM {$wpdb->prefix}posts WHERE post_type = %s AND post_status = 'publish' AND post_password = '' ORDER BY ID ASC",
				self::POST_TYPE
			)
		);

		$result = array();
		foreach ( (array) $ids as $id ) {
			if ( is_scalar( $id ) ) {
				$result[] = (int) $id;
			}
		}
		return $result;
	}

	#[\Override]
	public function venues( array $ids ): array {
		$venue_meta_keys = array(
			'_VenueAddress',
			'_VenueCity',
			'_VenueState',
			'_VenueZip',
			'_VenueCountry',
			'_VenuePhone',
			'_VenueURL',
			'_VenueLat',
			'_VenueLng',
		);

		$venues = array();
		foreach ( $this->posts_by_ids( $ids, 'tribe_venue' ) as $id => $title ) {
			$m = $this->meta_by_keys( array( $id ), $venue_meta_keys )[ $id ] ?? array();

			$venues[ $id ] = new TecVenue(
				id: $id,
				name: $title,
				address: $this->string_or_null( $m, '_VenueAddress' ),
				city: $this->string_or_null( $m, '_VenueCity' ),
				state: $this->string_or_null( $m, '_VenueState' ),
				zip: $this->string_or_null( $m, '_VenueZip' ),
				country: $this->string_or_null( $m, '_VenueCountry' ),
				phone: $this->string_or_null( $m, '_VenuePhone' ),
				website: $this->string_or_null( $m, '_VenueURL' ),
				lat: $this->float_or_null( $m, '_VenueLat' ),
				lng: $this->float_or_null( $m, '_VenueLng' ),
			);
		}
		return $venues;
	}

	#[\Override]
	public function organizers( array $ids ): array {
		$organizer_meta_keys = array(
			'_OrganizerPhone',
			'_OrganizerEmail',
			'_OrganizerWebsite',
		);

		$organizers = array();
		foreach ( $this->posts_by_ids( $ids, 'tribe_organizer' ) as $id => $title ) {
			$m = $this->meta_by_keys( array( $id ), $organizer_meta_keys )[ $id ] ?? array();

			$organizers[ $id ] = new TecOrganizer(
				id: $id,
				name: $title,
				phone: $this->string_or_null( $m, '_OrganizerPhone' ),
				email: $this->string_or_null( $m, '_OrganizerEmail' ),
				website: $this->string_or_null( $m, '_OrganizerWebsite' ),
			);
		}
		return $organizers;
	}

	#[\Override]
	public function occurrences( int $post_id ): array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- read of another plugin's stored data; TEC may be deactivated.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT start_date FROM {$wpdb->prefix}tec_occurrences WHERE post_id = %d ORDER BY start_date ASC",
				$post_id
			),
			ARRAY_A
		);

		$starts = array();
		foreach ( (array) $rows as $row ) {
			if ( is_array( $row ) && isset( $row['start_date'] ) && is_scalar( $row['start_date'] ) ) {
				$starts[] = (string) $row['start_date'];
			}
		}
		return $starts;
	}

	#[\Override]
	public function categories(): array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- read of another plugin's stored data; TEC may be deactivated.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT t.term_id, t.name, t.slug, tt.parent FROM {$wpdb->prefix}terms t INNER JOIN {$wpdb->prefix}term_taxonomy tt ON t.term_id = tt.term_id WHERE tt.taxonomy = %s",
				'tribe_events_cat'
			),
			ARRAY_A
		);

		$categories = array();
		foreach ( (array) $rows as $row ) {
			if ( ! is_array( $row ) || ! isset( $row['term_id'], $row['name'], $row['slug'], $row['parent'] )
				|| ! is_scalar( $row['term_id'] ) || ! is_scalar( $row['name'] ) || ! is_scalar( $row['slug'] ) || ! is_scalar( $row['parent'] ) ) {
				continue;
			}
			$term_id                = (int) $row['term_id'];
			$categories[ $term_id ] = new TecCategory(
				term_id: $term_id,
				name: (string) $row['name'],
				slug: (string) $row['slug'],
				parent_id: (int) $row['parent'],
			);
		}
		return $categories;
	}

	/**
	 * Titles of posts of one type, keyed by id. Unknown ids are omitted.
	 *
	 * @param list<int> $ids  Post ids.
	 * @param string    $type Post type.
	 * @return array<int, string> post_id => title.
	 */
	private function posts_by_ids( array $ids, string $type ): array {
		if ( array() === $ids ) {
			return array();
		}
		global $wpdb;

		$id_ph = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- read of another plugin's stored data; TEC may be deactivated.
		$rows = $wpdb->get_results(
			// phpstan-ignore argument.type (placeholder list is %d tokens; values are bound by prepare())
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $id_ph is a literal %d placeholder list; every value is bound by prepare().
				"SELECT ID, post_title FROM {$wpdb->prefix}posts WHERE ID IN ({$id_ph}) AND post_type = %s",
				...array_merge( $ids, array( $type ) )
			),
			ARRAY_A
		);

		$titles = array();
		foreach ( (array) $rows as $row ) {
			if ( is_array( $row ) && isset( $row['ID'], $row['post_title'] )
				&& is_scalar( $row['ID'] ) && is_scalar( $row['post_title'] ) ) {
				$titles[ (int) $row['ID'] ] = (string) $row['post_title'];
			}
		}
		return $titles;
	}

	/**
	 * Non-empty string meta value or null.
	 *
	 * @param array<string, string> $meta Meta map for one post.
	 * @param string                $key  Meta key.
	 */
	private function string_or_null( array $meta, string $key ): ?string {
		$value = $meta[ $key ] ?? '';
		return '' === $value ? null : $value;
	}

	/**
	 * Numeric meta value as float, or null when absent/non-numeric.
	 *
	 * @param array<string, string> $meta Meta map for one post.
	 * @param string                $key  Meta key.
	 */
	private function float_or_null( array $meta, string $key ): ?float {
		$value = $meta[ $key ] ?? '';
		return is_numeric( $value ) ? (float) $value : null;
	}

	/**
	 * Batch-load the relevant meta for a set of post ids.
	 *
	 * @param non-empty-list<int> $ids Post ids.
	 * @return array<int, array<string, string>> post_id => meta_key => value.
	 */
	private function meta_for( array $ids ): array {
		return $this->meta_by_keys( $ids, self::META_KEYS );
	}

	/**
	 * Batch-load selected meta keys for a set of post ids.
	 *
	 * @param non-empty-list<int>    $ids  Post ids.
	 * @param non-empty-list<string> $keys Meta keys.
	 * @return array<int, array<string, string>> post_id => meta_key => value.
	 */
	private function meta_by_keys( array $ids, array $keys ): array {
		global $wpdb;

		$id_ph  = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		$key_ph = implode( ',', array_fill( 0, count( $keys ), '%s' ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- read of another plugin's stored data; TEC may be deactivated.
		$rows = $wpdb->get_results(
			// phpstan-ignore argument.type (placeholder lists are %d/%s tokens; values are bound by prepare())
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $id_ph/$key_ph are literal %d/%s placeholder lists; every value is bound by prepare().
				"SELECT post_id, meta_key, meta_value FROM {$wpdb->prefix}postmeta WHERE post_id IN ({$id_ph}) AND meta_key IN ({$key_ph})",
				...array_merge( $ids, $keys )
			),
			ARRAY_A
		);

		$meta = array();
		foreach ( (array) $rows as $row ) {
			if ( is_array( $row ) && isset( $row['post_id'], $row['meta_key'], $row['meta_value'] )
				&& is_scalar( $row['post_id'] ) && is_scalar( $row['meta_key'] ) && is_scalar( $row['meta_value'] ) ) {
				$meta[ (int) $row['post_id'] ][ (string) $row['meta_key'] ] = (string) $row['meta_value'];
			}
		}
		return $meta;
	}

	/**
	 * Batch-load post_tag term names for a set of post ids.
	 *
	 * Names, not ids: WebCalendar tags are flat and global, matched by name,
	 * so the term id on the TEC side is of no further use.
	 *
	 * @param non-empty-list<int> $ids Post ids.
	 * @return array<int, list<string>> post_id => tag names.
	 */
	private function tags_for( array $ids ): array {
		global $wpdb;

		$id_ph = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- read of another plugin's stored data; TEC may be deactivated.
		$rows = $wpdb->get_results(
			// phpstan-ignore argument.type (placeholder list is %d tokens; values are bound by prepare())
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $id_ph is a literal %d placeholder list; every value is bound by prepare().
				"SELECT tr.object_id, t.name FROM {$wpdb->prefix}term_relationships tr INNER JOIN {$wpdb->prefix}term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id INNER JOIN {$wpdb->prefix}terms t ON t.term_id = tt.term_id WHERE tt.taxonomy = %s AND tr.object_id IN ({$id_ph})",
				...array_merge( array( 'post_tag' ), $ids )
			),
			ARRAY_A
		);

		$links = array();
		foreach ( (array) $rows as $row ) {
			if ( is_array( $row ) && isset( $row['object_id'], $row['name'] )
				&& is_scalar( $row['object_id'] ) && is_scalar( $row['name'] ) ) {
				$name = trim( (string) $row['name'] );
				if ( '' !== $name ) {
					$links[ (int) $row['object_id'] ][] = $name;
				}
			}
		}
		return $links;
	}

	/**
	 * Batch-load tribe_events_cat term ids for a set of post ids.
	 *
	 * @param non-empty-list<int> $ids Post ids.
	 * @return array<int, list<int>> post_id => term ids.
	 */
	private function categories_for( array $ids ): array {
		global $wpdb;

		$id_ph = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- read of another plugin's stored data; TEC may be deactivated.
		$rows = $wpdb->get_results(
			// phpstan-ignore argument.type (placeholder list is %d tokens; values are bound by prepare())
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $id_ph is a literal %d placeholder list; every value is bound by prepare().
				"SELECT tr.object_id, tt.term_id FROM {$wpdb->prefix}term_relationships tr INNER JOIN {$wpdb->prefix}term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy = %s AND tr.object_id IN ({$id_ph})",
				...array_merge( array( 'tribe_events_cat' ), $ids )
			),
			ARRAY_A
		);

		$links = array();
		foreach ( (array) $rows as $row ) {
			if ( is_array( $row ) && isset( $row['object_id'], $row['term_id'] )
				&& is_scalar( $row['object_id'] ) && is_scalar( $row['term_id'] ) ) {
				$links[ (int) $row['object_id'] ][] = (int) $row['term_id'];
			}
		}
		return $links;
	}

	/**
	 * Unserialize a raw meta value without instantiating objects.
	 *
	 * @param string $value Raw meta value.
	 * @return mixed
	 */
	private function unserialize_safely( string $value ) {
		// phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.serialize_unserialize -- allowed_classes false; TEC meta is plain arrays.
		$decoded = @unserialize( $value, array( 'allowed_classes' => false ) );
		return false === $decoded ? null : $decoded;
	}
	/**
	 * Additional Field definitions from The Events Calendar's options blob.
	 *
	 * Read from the settings rather than inferred from postmeta: the
	 * definitions carry the storage key, and a key we guessed that happens
	 * not to match reads back as "this event has no value" — indistinguishable
	 * from an empty field, and a quiet way to lose data.
	 *
	 * @return list<array{key: string, label: string, type: string, options: list<string>}>
	 */
	#[\Override]
	public function additional_fields(): array {
		$options = get_option( 'tribe_events_calendar_options' );
		if ( ! is_array( $options ) ) {
			return array();
		}

		$fields = $options['custom-fields'] ?? null;
		if ( ! is_array( $fields ) ) {
			return array();
		}

		$defined = array();
		foreach ( $fields as $field ) {
			if ( ! is_array( $field ) ) {
				continue;
			}

			$label = $field['label'] ?? '';
			if ( ! is_string( $label ) || '' === trim( $label ) ) {
				continue;
			}

			// Events Calendar Pro stores the postmeta key in the definition
			// itself. Taken from there rather than derived from the label or
			// the index: a key we invent that happens not to match reads back
			// as "this event has no value", which is indistinguishable from
			// an empty field and would lose data quietly.
			$key = $field['name'] ?? '';
			$key = is_string( $key ) ? trim( $key ) : '';

			$type = $field['type'] ?? '';
			$type = is_string( $type ) ? trim( $type ) : '';

			$defined[] = array(
				'key'     => $key,
				'label'   => trim( $label ),
				'type'    => $type,
				'options' => self::field_options( $field ),
			);
		}

		return $defined;
	}
	/**
	 * Additional Field values for a batch of events.
	 *
	 * Uses the same batched meta read as everything else here, so importing
	 * a thousand events costs one query for their custom fields rather than
	 * a thousand.
	 *
	 * @param list<int> $ids Event post ids.
	 * @return array<int, array<string, string>> post_id => key => value.
	 */
	private function additional_field_values( array $ids ): array {
		if ( array() === $ids ) {
			return array();
		}

		$keys = array();
		foreach ( $this->additional_fields() as $field ) {
			if ( '' !== $field['key'] ) {
				$keys[] = $field['key'];
			}
		}
		if ( array() === $keys ) {
			return array();
		}

		$values = array();
		foreach ( $this->meta_by_keys( $ids, $keys ) as $post_id => $meta ) {
			foreach ( $meta as $key => $value ) {
				if ( '' !== trim( $value ) ) {
					$values[ $post_id ][ $key ] = $value;
				}
			}
		}

		return $values;
	}
	/**
	 * The choices a dropdown, radio or checkbox field offers.
	 *
	 * The Events Calendar Pro's own UI asks for these "one option per line",
	 * so a stored value may be either a newline-delimited string or an array
	 * depending on how it was saved. Both are accepted, and an unrecognised
	 * shape yields nothing rather than a guess — a select whose choices we
	 * invented would refuse the values the events actually hold.
	 *
	 * @param array<mixed> $field One definition from TEC's options, unvalidated.
	 * @return list<string>
	 */
	private static function field_options( array $field ): array {
		// The key differs between TEC versions and is not documented; take
		// whichever is present rather than betting on one.
		$raw = null;
		foreach ( array( 'options', 'values', 'choices' ) as $candidate ) {
			if ( isset( $field[ $candidate ] ) ) {
				$raw = $field[ $candidate ];
				break;
			}
		}

		if ( is_string( $raw ) ) {
			$raw = preg_split( '/\r\n|\r|\n/', $raw ) ?: array();
		}
		if ( ! is_array( $raw ) ) {
			return array();
		}

		$options = array();
		foreach ( $raw as $option ) {
			if ( ! is_scalar( $option ) ) {
				continue;
			}
			$text = trim( (string) $option );
			if ( '' !== $text && ! in_array( $text, $options, true ) ) {
				$options[] = $text;
			}
		}

		return $options;
	}
}