<?php

namespace CommerceBird\Admin\Mappings;

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

final class ZohoCategoryMap {

	/**
	 * @var \wpdb
	 */
	private $db;

	private string $table;

	public function __construct() {
		global $wpdb;
		$this->db    = $wpdb;
		$this->table = $wpdb->prefix . 'cmbird_zi_category_map';
	}

	public function table_name(): string {
		return $this->table;
	}

	public function zoho_id_for_term( int $term_id ): ?string {
		$row = $this->db->get_var(
			$this->db->prepare(
				"SELECT zoho_category_id FROM {$this->table} WHERE wc_term_id = %d AND status = 'active' LIMIT 1",
				$term_id
			)
		);

		if ( null !== $row ) {
			return (string) $row;
		}

		if ( defined( 'CMBIRD_ZI_CATEGORY_MAP_LEGACY_FALLBACK' ) && CMBIRD_ZI_CATEGORY_MAP_LEGACY_FALLBACK ) {
			$legacy = get_option( 'cmbird_zoho_id_for_term_id_' . $term_id );
			if ( ! empty( $legacy ) ) {
				// Back-fill on hit so subsequent reads avoid the fallback.
				$this->claim_auto( $term_id, (string) $legacy );
				return (string) $legacy;
			}
		}

		return null;
	}

	public function term_id_for_zoho_id( string $zoho_id ): ?int {
		$row = $this->db->get_var(
			$this->db->prepare(
				"SELECT wc_term_id FROM {$this->table} WHERE zoho_category_id = %s AND status = 'active' LIMIT 1",
				$zoho_id
			)
		);

		if ( null !== $row ) {
			return (int) $row;
		}

		if ( defined( 'CMBIRD_ZI_CATEGORY_MAP_LEGACY_FALLBACK' ) && CMBIRD_ZI_CATEGORY_MAP_LEGACY_FALLBACK ) {
			$option_name = $this->db->get_var(
				$this->db->prepare(
					"SELECT option_name FROM {$this->db->options}
					 WHERE option_name LIKE 'cmbird_zoho_id_for_term_id_%%'
					   AND option_value = %s
					 LIMIT 1",
					$zoho_id
				)
			);
			if ( ! empty( $option_name ) ) {
				$term_id = (int) substr( $option_name, strlen( 'cmbird_zoho_id_for_term_id_' ) );
				if ( $term_id > 0 ) {
					$this->claim_auto( $term_id, $zoho_id );
					return $term_id;
				}
			}
		}

		return null;
	}

	/**
	 * Resolve a Zoho category id to a WC term id, preferring active rows but
	 * gracefully falling back to the newest row in any status.
	 */
	public function term_id_for_zoho_id_any_status( string $zoho_id ): ?int {
		$active = $this->term_id_for_zoho_id( $zoho_id );
		if ( null !== $active ) {
			return $active;
		}

		$row = $this->db->get_var(
			$this->db->prepare(
				"SELECT wc_term_id
				 FROM {$this->table}
				 WHERE zoho_category_id = %s
				 ORDER BY updated_at DESC, id DESC
				 LIMIT 1",
				$zoho_id
			)
		);

		return null !== $row ? (int) $row : null;
	}

	/**
	 * Resolve a WC term id to a Zoho category id, preferring active rows but
	 * gracefully falling back to the newest row in any status.
	 */
	public function zoho_id_for_term_any_status( int $term_id ): ?string {
		$active = $this->zoho_id_for_term( $term_id );
		if ( null !== $active ) {
			return $active;
		}

		$row = $this->db->get_var(
			$this->db->prepare(
				"SELECT zoho_category_id
				 FROM {$this->table}
				 WHERE wc_term_id = %d
				 ORDER BY updated_at DESC, id DESC
				 LIMIT 1",
				$term_id
			)
		);

		return null !== $row ? (string) $row : null;
	}

	public function claim_auto( int $term_id, string $zoho_id ): ClaimResult {
		$now = current_time( 'mysql' );

		// Suppress wpdb's automatic error printing — we expect unique-key violations.
		$previous_show_errors = $this->db->show_errors( false );

		$inserted = $this->db->insert(
			$this->table,
			array(
				'wc_term_id'       => $term_id,
				'zoho_category_id' => $zoho_id,
				'source'           => 'auto',
				'status'           => 'active',
				'created_at'       => $now,
				'updated_at'       => $now,
				'last_verified_at' => $now,
			),
			array( '%d', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		$this->db->show_errors( $previous_show_errors );

		if ( false !== $inserted ) {
			return new ClaimResult( ClaimResult::STATUS_CREATED, (int) $this->db->insert_id );
		}

		// Insert failed — must be a unique-violation. Resolve it.
		return $this->resolve_auto_conflict( $term_id, $zoho_id, $now );
	}

	private function resolve_auto_conflict( int $term_id, string $zoho_id, string $now ): ClaimResult {
		// Look up the conflicting row by either constraint.
		$existing = $this->db->get_row(
			$this->db->prepare(
				"SELECT * FROM {$this->table}
				 WHERE status = 'active' AND ( wc_term_id = %d OR zoho_category_id = %s )
				 LIMIT 1",
				$term_id,
				$zoho_id
			)
		);

		if ( null === $existing ) {
			// Race: row vanished between failed INSERT and SELECT. Retry once.
			return $this->claim_auto( $term_id, $zoho_id );
		}

		// Same auto pair → noop, bump last_verified_at.
		if (
			'auto' === $existing->source
			&& (int) $existing->wc_term_id === $term_id
			&& (string) $existing->zoho_category_id === $zoho_id
		) {
			// NOOP is a re-confirmation, not a data change — only bump last_verified_at.
			// updated_at stays put so it accurately reflects the last time row data changed.
			$this->db->update(
				$this->table,
				array( 'last_verified_at' => $now ),
				array( 'id' => $existing->id ),
				array( '%s' ),
				array( '%d' )
			);
			return new ClaimResult( ClaimResult::STATUS_NOOP, (int) $existing->id );
		}

		// Otherwise it's a genuine conflict — implemented in Task 7.
		return $this->queue_for_review( $term_id, $zoho_id, $existing, $now );
	}

	private function queue_for_review( int $term_id, string $zoho_id, object $existing, string $now ): ClaimResult {
		if ( 'manual' === $existing->source ) {
			$reason = 'manual_override_protected';
		} elseif ( (int) $existing->wc_term_id === $term_id ) {
			$reason = 'term_remap_pending';
		} else {
			$reason = 'duplicate_zoho_id';
		}

		$this->db->insert(
			$this->table,
			array(
				'wc_term_id'       => $term_id,
				'zoho_category_id' => $zoho_id,
				'source'           => 'auto',
				'status'           => 'review',
				'review_reason'    => $reason,
				'created_at'       => $now,
				'updated_at'       => $now,
			),
			array( '%d', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		return new ClaimResult(
			ClaimResult::STATUS_QUEUED_FOR_REVIEW,
			(int) $this->db->insert_id,
			$reason
		);
	}

	public function set_manual( int $term_id, string $zoho_id ): ClaimResult {
		$now = current_time( 'mysql' );

		// Delete any active rows that would conflict on either unique key.
		// This wipes auto rows AND any prior manual row for the same term/zoho id —
		// operator override is destructive by design.
		$this->db->query(
			$this->db->prepare(
				"DELETE FROM {$this->table}
				 WHERE status = 'active'
				   AND ( wc_term_id = %d OR zoho_category_id = %s )",
				$term_id,
				$zoho_id
			)
		);

		$rows_deleted = (int) $this->db->rows_affected;

		$this->db->insert(
			$this->table,
			array(
				'wc_term_id'       => $term_id,
				'zoho_category_id' => $zoho_id,
				'source'           => 'manual',
				'status'           => 'active',
				'created_at'       => $now,
				'updated_at'       => $now,
				'last_verified_at' => $now,
			),
			array( '%d', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		return new ClaimResult(
			$rows_deleted > 0 ? ClaimResult::STATUS_UPDATED : ClaimResult::STATUS_CREATED,
			(int) $this->db->insert_id
		);
	}

	public function forget_term( int $term_id ): void {
		$this->db->delete(
			$this->table,
			array( 'wc_term_id' => $term_id ),
			array( '%d' )
		);
	}

	public function delete_by_id( int $row_id ): bool {
		$deleted = $this->db->delete(
			$this->table,
			array( 'id' => $row_id ),
			array( '%d' )
		);
		return $deleted !== false && $deleted > 0;
	}

	public function reconcile_zoho_presence( array $current_zoho_ids ): ReconcileResult {
		if ( empty( $current_zoho_ids ) ) {
			// No current ids — every active row is orphaned by definition.
			$hard_deleted = (int) $this->db->query(
				"DELETE FROM {$this->table} WHERE source = 'auto' AND status = 'active'"
			);
			$now          = current_time( 'mysql' );
			$orphaned     = (int) $this->db->query(
				$this->db->prepare(
					"UPDATE {$this->table}
					 SET status = 'orphan', review_reason = 'zoho_missing', updated_at = %s
					 WHERE source = 'manual' AND status = 'active'",
					$now
				)
			);
			return new ReconcileResult( $hard_deleted, $orphaned );
		}

		$placeholders = implode( ',', array_fill( 0, count( $current_zoho_ids ), '%s' ) );

		$hard_deleted = (int) $this->db->query(
			$this->db->prepare(
				"DELETE FROM {$this->table}
				 WHERE source = 'auto'
				   AND status = 'active'
				   AND zoho_category_id NOT IN ({$placeholders})",
				...array_map( 'strval', $current_zoho_ids )
			)
		);

		$now      = current_time( 'mysql' );
		$orphaned = (int) $this->db->query(
			$this->db->prepare(
				"UPDATE {$this->table}
				 SET status = 'orphan', review_reason = 'zoho_missing', updated_at = %s
				 WHERE source = 'manual'
				   AND status = 'active'
				   AND zoho_category_id NOT IN ({$placeholders})",
				$now,
				...array_map( 'strval', $current_zoho_ids )
			)
		);

		return new ReconcileResult( $hard_deleted, $orphaned );
	}

	public function list( int $offset = 0, int $limit = 50, string $status = 'active' ): array {
		if ( 'all' === $status ) {
			$query = $this->db->prepare(
				"SELECT id, wc_term_id, zoho_category_id, source, status, review_reason, created_at, updated_at, last_verified_at
				 FROM {$this->table}
				 ORDER BY updated_at DESC
				 LIMIT %d OFFSET %d",
				$limit,
				$offset
			);
		} else {
			$query = $this->db->prepare(
				"SELECT id, wc_term_id, zoho_category_id, source, status, review_reason, created_at, updated_at, last_verified_at
				 FROM {$this->table}
				 WHERE status = %s
				 ORDER BY updated_at DESC
				 LIMIT %d OFFSET %d",
				$status,
				$limit,
				$offset
			);
		}

		$rows = $this->db->get_results(
			$query,
			ARRAY_A
		);

		return $rows ?: array();
	}

	public function resolve_review( int $row_id, ReviewResolution $resolution ): void {
		// The review queue surfaces both 'review' rows (genuine pending conflicts) and
		// 'orphan' rows (manual mappings whose Zoho category no longer exists,
		// review_reason = 'zoho_missing'). Both must be resolvable, otherwise an orphan
		// row can never be cleared and the warning reappears on every page reload.
		$review_row = $this->db->get_row(
			$this->db->prepare(
				"SELECT * FROM {$this->table} WHERE id = %d AND status IN ( 'review', 'orphan' )",
				$row_id
			)
		);

		if ( null === $review_row ) {
			return; // already resolved by another tab/operator
		}

		switch ( $resolution ) {
			case ReviewResolution::KeepExisting:
				$this->db->delete( $this->table, array( 'id' => $row_id ), array( '%d' ) );
				return;

			case ReviewResolution::DiscardBoth:
				$this->db->query(
					$this->db->prepare(
						"DELETE FROM {$this->table}
						 WHERE id = %d
						    OR ( status = 'active'
						         AND ( wc_term_id = %d OR zoho_category_id = %s ) )",
						$row_id,
						(int) $review_row->wc_term_id,
						(string) $review_row->zoho_category_id
					)
				);
				return;

			case ReviewResolution::ReplaceWithPending:
				// Delete the review row and any conflicting active row, then promote pending as manual.
				$this->db->query(
					$this->db->prepare(
						"DELETE FROM {$this->table}
						 WHERE id = %d
						    OR ( status = 'active'
						         AND ( wc_term_id = %d OR zoho_category_id = %s ) )",
						$row_id,
						(int) $review_row->wc_term_id,
						(string) $review_row->zoho_category_id
					)
				);
				$this->set_manual( (int) $review_row->wc_term_id, (string) $review_row->zoho_category_id );
				return;
		}
	}

	public static function migrate_legacy_options( bool $dry_run = false ): array {
		global $wpdb;

		$rows = $wpdb->get_results(
			"SELECT option_name, option_value
			 FROM {$wpdb->options}
			 WHERE option_name LIKE 'cmbird_zoho_id_for_term_id_%'"
		);

		$migrated = 0;
		$skipped  = array();

		if ( empty( $rows ) ) {
			return array(
				'migrated' => 0,
				'skipped'  => array(),
			);
		}

		$self  = new self();
		$table = $self->table_name();
		$now   = current_time( 'mysql' );

		foreach ( $rows as $row ) {
			$term_id = (int) substr( $row->option_name, strlen( 'cmbird_zoho_id_for_term_id_' ) );
			$zoho_id = (string) $row->option_value;

			if ( $term_id <= 0 || '' === $zoho_id ) {
				$skipped[] = array(
					'option_name' => $row->option_name,
					'reason'      => 'invalid_value',
				);
				continue;
			}

			if ( null === term_exists( $term_id, 'product_cat' ) ) {
				$skipped[] = array(
					'option_name' => $row->option_name,
					'reason'      => 'orphan_term',
				);
				continue;
			}

			if ( $dry_run ) {
				++$migrated;
				continue;
			}

			// INSERT IGNORE: unique-violations from already-migrated rows are silent skips.
			$inserted = $wpdb->query(
				$wpdb->prepare(
					"INSERT IGNORE INTO {$table}
					 (wc_term_id, zoho_category_id, source, status, created_at, updated_at)
					 VALUES (%d, %s, 'auto', 'active', %s, %s)",
					$term_id,
					$zoho_id,
					$now,
					$now
				)
			);

			if ( $inserted ) {
				++$migrated;
			} else {
				$skipped[] = array(
					'option_name' => $row->option_name,
					'reason'      => 'duplicate_constraint',
				);
			}
		}

		return array(
			'migrated' => $migrated,
			'skipped'  => $skipped,
		);
	}
}
