<?php

declare(strict_types=1);

namespace Signocore\Multilanguage\Infrastructure\Database;

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

use Signocore\Multilanguage\Application\Constants;

final class TranslationRepository
{
	/**
	 * Find a translation record by element ID and type.
	 */
	public static function findByElement(int $elementId, string $elementType): ?object
	{
		global $wpdb;

		$table = $wpdb->prefix . Constants::TABLE_TRANSLATIONS;

		return $wpdb->get_row($wpdb->prepare(
			"SELECT * FROM `{$table}` WHERE element_id = %d AND element_type = %s LIMIT 1",
			$elementId,
			$elementType
		));
	}

	/**
	 * Find a translation record by element, type, and language.
	 */
	public static function findByElementAndLanguage(int $elementId, string $elementType, string $langCode): ?object
	{
		global $wpdb;

		$table = $wpdb->prefix . Constants::TABLE_TRANSLATIONS;

		return $wpdb->get_row($wpdb->prepare(
			"SELECT * FROM `{$table}` WHERE element_id = %d AND element_type = %s AND language_code = %s LIMIT 1",
			$elementId,
			$elementType,
			$langCode
		));
	}

	/**
	 * Get all translation records in a group.
	 *
	 * @return array<int, object>
	 */
	public static function findByGroup(int $groupId): array
	{
		global $wpdb;

		$table = $wpdb->prefix . Constants::TABLE_TRANSLATIONS;

		/** @var array<int, object> */
		return $wpdb->get_results($wpdb->prepare(
			"SELECT * FROM `{$table}` WHERE group_id = %d",
			$groupId
		));
	}

	/**
	 * Get the group ID for an element.
	 */
	public static function getGroupId(int $elementId, string $elementType): ?int
	{
		global $wpdb;

		$table = $wpdb->prefix . Constants::TABLE_TRANSLATIONS;

		$result = $wpdb->get_var($wpdb->prepare(
			"SELECT group_id FROM `{$table}` WHERE element_id = %d AND element_type = %s LIMIT 1",
			$elementId,
			$elementType
		));

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

	/**
	 * Get the next available group ID (atomic, race-condition safe).
	 */
	public static function getNextGroupId(): int
	{
		global $wpdb;

		$optionName = Constants::OPTION_PREFIX . 'next_group_id';

		// Atomic increment at the database level
		$updated = $wpdb->query($wpdb->prepare(
			"UPDATE {$wpdb->options} SET option_value = option_value + 1 WHERE option_name = %s",
			$optionName
		));

		if ($updated > 0) {
			return (int) $wpdb->get_var($wpdb->prepare(
				"SELECT option_value FROM {$wpdb->options} WHERE option_name = %s",
				$optionName
			));
		}

		// First call: seed from current MAX(group_id)
		$table = $wpdb->prefix . Constants::TABLE_TRANSLATIONS;
		$max = (int) $wpdb->get_var("SELECT MAX(group_id) FROM `{$table}`");
		$nextId = $max + 1;

		// INSERT IGNORE handles concurrent seeders
		$wpdb->query($wpdb->prepare(
			"INSERT IGNORE INTO {$wpdb->options} (option_name, option_value, autoload) VALUES (%s, %d, %s)",
			$optionName,
			$nextId + 1,
			'no'
		));

		return $nextId;
	}

	/**
	 * Insert a translation record.
	 *
	 * @param array<string, mixed> $data
	 */
	public static function insert(array $data): int|false
	{
		global $wpdb;

		$table = $wpdb->prefix . Constants::TABLE_TRANSLATIONS;

		$result = $wpdb->insert($table, $data);

		return $result !== false ? $wpdb->insert_id : false;
	}

	/**
	 * Update a translation record by ID.
	 *
	 * @param array<string, mixed> $data
	 */
	public static function update(int $id, array $data): bool
	{
		global $wpdb;

		$table = $wpdb->prefix . Constants::TABLE_TRANSLATIONS;

		return $wpdb->update($table, $data, ['id' => $id]) !== false;
	}

	/**
	 * Delete a translation record by ID.
	 */
	public static function delete(int $id): bool
	{
		global $wpdb;

		$table = $wpdb->prefix . Constants::TABLE_TRANSLATIONS;

		return $wpdb->delete($table, ['id' => $id]) !== false;
	}

	/**
	 * Delete translation records by element ID and type.
	 */
	public static function deleteByElement(int $elementId, string $elementType): bool
	{
		global $wpdb;

		$table = $wpdb->prefix . Constants::TABLE_TRANSLATIONS;

		return $wpdb->delete($table, [
			'element_id' => $elementId,
			'element_type' => $elementType,
		]) !== false;
	}

	/**
	 * Delete all translation records for a specific language.
	 *
	 * @return int Number of deleted records.
	 */
	public static function deleteByLanguage(string $languageCode): int
	{
		global $wpdb;

		$table = $wpdb->prefix . Constants::TABLE_TRANSLATIONS;

		return (int) $wpdb->delete($table, ['language_code' => $languageCode]);
	}

	/**
	 * Get all translations for a post, keyed by language code.
	 *
	 * @param bool $publishedOnly Only include translations with a published WordPress post.
	 * @return array<string, int>
	 */
	public static function getTranslationsForPost(int $postId, bool $publishedOnly = false): array
	{
		global $wpdb;

		$table = $wpdb->prefix . Constants::TABLE_TRANSLATIONS;

		$groupId = self::getGroupId($postId, self::resolvePostElementType($postId));

		if ($groupId === null) {
			// Try any post_ element type
			$groupId = $wpdb->get_var($wpdb->prepare(
				"SELECT group_id FROM `{$table}` WHERE element_id = %d AND element_type LIKE 'post_%%' LIMIT 1",
				$postId
			));

			if ($groupId === null) {
				return [];
			}

			$groupId = (int) $groupId;
		}

		if ($publishedOnly) {
			$rows = $wpdb->get_results($wpdb->prepare(
				"SELECT t.language_code, t.element_id FROM `{$table}` t
				INNER JOIN {$wpdb->posts} p ON p.ID = t.element_id
				WHERE t.group_id = %d AND p.post_status = 'publish'",
				$groupId
			));
		} else {
			$rows = $wpdb->get_results($wpdb->prepare(
				"SELECT language_code, element_id FROM `{$table}` WHERE group_id = %d",
				$groupId
			));
		}

		$translations = [];
		foreach ($rows as $row) {
			$translations[$row->language_code] = (int) $row->element_id;
		}

		return $translations;
	}

	/**
	 * Get all translations for a term, keyed by language code.
	 *
	 * @return array<string, int>
	 */
	public static function getTranslationsForTerm(int $termId, string $taxonomy): array
	{
		global $wpdb;

		$table = $wpdb->prefix . Constants::TABLE_TRANSLATIONS;
		$elementType = 'tax_' . $taxonomy;

		$groupId = self::getGroupId($termId, $elementType);
		if ($groupId === null) {
			return [];
		}

		$rows = $wpdb->get_results($wpdb->prepare(
			"SELECT language_code, element_id FROM `{$table}` WHERE group_id = %d",
			$groupId
		));

		$translations = [];
		foreach ($rows as $row) {
			$translations[$row->language_code] = (int) $row->element_id;
		}

		return $translations;
	}

	/**
	 * Count translations in a specific language.
	 */
	public static function countByLanguage(string $langCode): int
	{
		global $wpdb;

		$table = $wpdb->prefix . Constants::TABLE_TRANSLATIONS;

		return (int) $wpdb->get_var($wpdb->prepare(
			"SELECT COUNT(*) FROM `{$table}` WHERE language_code = %s",
			$langCode
		));
	}

	/**
	 * Count translations with a specific status.
	 */
	public static function countByStatus(string $status): int
	{
		global $wpdb;

		$table = $wpdb->prefix . Constants::TABLE_TRANSLATIONS;

		return (int) $wpdb->get_var($wpdb->prepare(
			"SELECT COUNT(*) FROM `{$table}` WHERE status = %s",
			$status
		));
	}

	/**
	 * Resolve the element type for a post based on its post type.
	 */
	private static function resolvePostElementType(int $postId): string
	{
		$postType = get_post_type($postId);

		return $postType !== false ? 'post_' . $postType : 'post_post';
	}
}
