<?php
/**
 * Persistence layer for target keywords.
 *
 * @package Postlinkly
 * @since   1.0.0
 */

declare(strict_types=1);

namespace Postlinkly\Keywords;

use Postlinkly\Database\TableNames;

/**
 * Persistence layer for target keywords.
 *
 * Reads and writes rows in `postlinkly_target_keywords`. Returns typed
 * {@see KeywordModel} instances so callers never see raw `wpdb` arrays.
 *
 * The repository is intentionally thin — it does not stem or normalise
 * phrases. That is the {@see TargetKeywordManager}'s job. The repository
 * only stores and retrieves what it is given.
 *
 * @since 1.0.0
 */
final class TargetKeywordRepository {

	/**
	 * Returns all target keywords for a post or term.
	 *
	 * @since 1.0.0
	 *
	 * @param int    $post_id   Post or term identifier.
	 * @param string $post_type Item type. Default `post`.
	 *
	 * @return array<int, KeywordModel>
	 */
	public function find_for( int $post_id, string $post_type = 'post' ): array {
		global $wpdb;

		if ( ! isset( $wpdb ) || $post_id <= 0 ) {
			return array();
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Caching is the manager's responsibility.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE post_id = %d AND post_type = %s ORDER BY id ASC',
				TableNames::target_keywords(),
				$post_id,
				$post_type
			)
		);

		if ( ! is_array( $rows ) ) {
			return array();
		}

		$out = array();
		foreach ( $rows as $row ) {
			$model = $this->hydrate( $row );
			if ( null !== $model ) {
				$out[] = $model;
			}
		}
		return $out;
	}

	/**
	 * Upserts a keyword row.
	 *
	 * Inserts a new row when the (post_id, post_type, source, phrase)
	 * combination does not exist; updates `stemmed`, `normalized`, and
	 * `word_count` when it does. The UNIQUE KEY on those four columns
	 * in the schema makes this safe.
	 *
	 * @since 1.0.0
	 *
	 * @param KeywordModel $keyword The keyword to persist.
	 *
	 * @return bool True on success.
	 */
	public function upsert( KeywordModel $keyword ): bool {
		global $wpdb;

		if ( ! isset( $wpdb ) ) {
			return false;
		}

		$table = TableNames::target_keywords();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Upsert; no cache layer needed here.
		$result = $wpdb->query(
			$wpdb->prepare(
				'INSERT INTO %i (post_id, post_type, source, phrase, stemmed, normalized, word_count)
				VALUES (%d, %s, %s, %s, %s, %s, %d)
				ON DUPLICATE KEY UPDATE
					stemmed    = VALUES(stemmed),
					normalized = VALUES(normalized),
					word_count = VALUES(word_count)',
				$table,
				$keyword->post_id,
				$keyword->post_type,
				$keyword->source,
				$keyword->phrase,
				$keyword->stemmed,
				$keyword->normalized,
				$keyword->word_count
			)
		);

		return false !== $result;
	}

	/**
	 * Deletes all keywords for a post/term from a specific source.
	 *
	 * Used when re-importing from an SEO plugin to clear stale rows
	 * before inserting the fresh set.
	 *
	 * @since 1.0.0
	 *
	 * @param int    $post_id   Post or term identifier.
	 * @param string $post_type Item type. Default `post`.
	 * @param string $source    Source identifier (e.g. `yoast`).
	 *
	 * @return int Number of rows deleted.
	 */
	public function delete_for_source( int $post_id, string $post_type, string $source ): int {
		global $wpdb;

		if ( ! isset( $wpdb ) || $post_id <= 0 || '' === $source ) {
			return 0;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Targeted delete; no cache layer needed.
		$deleted = $wpdb->delete(
			TableNames::target_keywords(),
			array(
				'post_id'   => $post_id,
				'post_type' => $post_type,
				'source'    => $source,
			),
			array( '%d', '%s', '%s' )
		);

		return is_int( $deleted ) ? $deleted : 0;
	}

	/**
	 * Deletes all keywords for a post or term (all sources).
	 *
	 * Called when a post is deleted so orphaned rows don't accumulate.
	 *
	 * @since 1.0.0
	 *
	 * @param int    $post_id   Post or term identifier.
	 * @param string $post_type Item type. Default `post`.
	 *
	 * @return int Number of rows deleted.
	 */
	public function delete_all_for( int $post_id, string $post_type = 'post' ): int {
		global $wpdb;

		if ( ! isset( $wpdb ) || $post_id <= 0 ) {
			return 0;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Targeted delete.
		$deleted = $wpdb->delete(
			TableNames::target_keywords(),
			array(
				'post_id'   => $post_id,
				'post_type' => $post_type,
			),
			array( '%d', '%s' )
		);

		return is_int( $deleted ) ? $deleted : 0;
	}

	/**
	 * Hydrates a database row into a KeywordModel.
	 *
	 * @since 1.0.0
	 *
	 * @param object $row Raw row from wpdb.
	 *
	 * @return KeywordModel|null Null when the row is malformed.
	 */
	private function hydrate( object $row ): ?KeywordModel {
		if ( ! isset( $row->post_id, $row->post_type, $row->source, $row->phrase ) ) {
			return null;
		}

		return new KeywordModel(
			(int) $row->post_id,
			(string) $row->post_type,
			(string) $row->source,
			(string) $row->phrase,
			(string) ( $row->stemmed ?? '' ),
			(string) ( $row->normalized ?? '' ),
			(int) ( $row->word_count ?? 0 ),
			(int) ( $row->id ?? 0 ),
			(bool) ( $row->checked ?? false ),
			(int) ( $row->impressions ?? 0 ),
			(int) ( $row->clicks ?? 0 )
		);
	}
}