<?php

declare(strict_types=1);

namespace Signocore\Multilanguage\Infrastructure\Database;

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

use Signocore\Multilanguage\Application\Constants;

final class StringRepository
{
	/**
	 * Find a registered string by context and name.
	 */
	public static function findString(string $context, string $name): ?object
	{
		global $wpdb;

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

		return $wpdb->get_row($wpdb->prepare(
			"SELECT * FROM `{$table}` WHERE `context` = %s AND `name` = %s LIMIT 1",
			$context,
			$name
		));
	}

	/**
	 * Find a registered string by ID.
	 */
	public static function findStringById(int $id): ?object
	{
		global $wpdb;

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

		return $wpdb->get_row($wpdb->prepare(
			"SELECT * FROM `{$table}` WHERE `id` = %d LIMIT 1",
			$id
		));
	}

	/**
	 * Get a translation record for a string and language.
	 */
	public static function getTranslation(int $stringId, string $langCode): ?object
	{
		global $wpdb;

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

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

	/**
	 * Get just the translated value for a string and language.
	 */
	public static function getTranslationValue(int $stringId, string $langCode): ?string
	{
		global $wpdb;

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

		$value = $wpdb->get_var($wpdb->prepare(
			"SELECT `value` FROM `{$table}` WHERE `string_id` = %d AND `language_code` = %s AND `status` != 'needs_translation' LIMIT 1",
			$stringId,
			$langCode
		));

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

	/**
	 * Insert or update a string translation.
	 */
	public static function saveTranslation(int $stringId, string $langCode, string $value, ?int $translatorId = null): bool
	{
		global $wpdb;

		$table = $wpdb->prefix . Constants::TABLE_STRING_TRANSLATIONS;
		$status = $value === '' ? 'needs_translation' : 'translated';

		$existing = self::getTranslation($stringId, $langCode);

		if ($existing !== null) {
			$data = [
				'value' => $value,
				'status' => $status,
			];

			if ($translatorId !== null) {
				$data['translator_id'] = $translatorId;
			}

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

		$data = [
			'string_id' => $stringId,
			'language_code' => $langCode,
			'value' => $value,
			'status' => $status,
			'translator_id' => $translatorId,
		];

		return $wpdb->insert($table, $data) !== false;
	}

	/**
	 * Register or update a string, returns the string ID.
	 */
	public static function registerString(string $context, string $name, string $value, string $langCode, string $type = 'line'): int
	{
		global $wpdb;

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

		$existing = self::findString($context, $name);

		if ($existing !== null) {
			if ($existing->value !== $value) {
				$wpdb->update($table, [
					'value' => $value,
					'language_code' => $langCode,
					'type' => $type,
				], ['id' => (int) $existing->id]);
			}

			return (int) $existing->id;
		}

		$wpdb->insert($table, [
			'context' => $context,
			'name' => $name,
			'value' => $value,
			'language_code' => $langCode,
			'type' => $type,
		]);

		return (int) $wpdb->insert_id;
	}

	/**
	 * Search strings by query and optional context.
	 *
	 * @return array<int, object>
	 */
	public static function searchStrings(string $query, ?string $context = null, int $limit = 50, int $offset = 0): array
	{
		global $wpdb;

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

		$where = [];
		$params = [];

		$likeQuery = '%' . $wpdb->esc_like($query) . '%';
		$where[] = '(`name` LIKE %s OR `value` LIKE %s)';
		$params[] = $likeQuery;
		$params[] = $likeQuery;

		if ($context !== null && $context !== '') {
			$where[] = '`context` = %s';
			$params[] = $context;
		}

		$whereClause = implode(' AND ', $where);
		$params[] = $limit;
		$params[] = $offset;

		/** @var array<int, object> */
		return $wpdb->get_results($wpdb->prepare(
			"SELECT * FROM `{$table}` WHERE {$whereClause} ORDER BY `context` ASC, `name` ASC LIMIT %d OFFSET %d",
			...$params
		));
	}

	/**
	 * Count total strings, optionally filtered by context.
	 */
	public static function countStrings(?string $context = null): int
	{
		global $wpdb;

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

		if ($context !== null && $context !== '') {
			return (int) $wpdb->get_var($wpdb->prepare(
				"SELECT COUNT(*) FROM `{$table}` WHERE `context` = %s",
				$context
			));
		}

		return (int) $wpdb->get_var("SELECT COUNT(*) FROM `{$table}`");
	}

	/**
	 * Get all unique contexts.
	 *
	 * @return array<int, string>
	 */
	public static function getContexts(): array
	{
		global $wpdb;

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

		/** @var array<int, string> */
		return $wpdb->get_col("SELECT DISTINCT `context` FROM `{$table}` ORDER BY `context` ASC");
	}

	/**
	 * Get strings with translation status for a given language.
	 *
	 * @return array<int, object>
	 */
	public static function getAllForLanguage(string $langCode, ?string $context = null, int $limit = 50, int $offset = 0): array
	{
		global $wpdb;

		$stringsTable = $wpdb->prefix . Constants::TABLE_STRINGS;
		$translationsTable = $wpdb->prefix . Constants::TABLE_STRING_TRANSLATIONS;

		$where = '';
		$params = [$langCode];

		if ($context !== null && $context !== '') {
			$where = 'WHERE s.`context` = %s';
			$params[] = $context;
		}

		$params[] = $limit;
		$params[] = $offset;

		/** @var array<int, object> */
		return $wpdb->get_results($wpdb->prepare(
			"SELECT s.*, t.`value` AS `translation_value`, t.`status` AS `translation_status`, t.`translator_id`
			FROM `{$stringsTable}` s
			LEFT JOIN `{$translationsTable}` t ON s.`id` = t.`string_id` AND t.`language_code` = %s
			{$where}
			ORDER BY s.`context` ASC, s.`name` ASC
			LIMIT %d OFFSET %d",
			...$params
		));
	}

	/**
	 * Delete a registered string and all its translations.
	 */
	public static function deleteString(int $id): bool
	{
		global $wpdb;

		$stringsTable = $wpdb->prefix . Constants::TABLE_STRINGS;
		$translationsTable = $wpdb->prefix . Constants::TABLE_STRING_TRANSLATIONS;

		$wpdb->delete($translationsTable, ['string_id' => $id]);

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

	/**
	 * Mark all translations of a string as 'needs_update'.
	 */
	public static function markNeedsUpdate(int $stringId): bool
	{
		global $wpdb;

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

		return $wpdb->update(
			$table,
			['status' => 'needs_update'],
			['string_id' => $stringId]
		) !== false;
	}

	/**
	 * Get strings with translation for a language, with SQL-based status filter.
	 *
	 * @return array<int, object>
	 */
	public static function getAllForLanguageWithStatus(
		string $langCode,
		?string $context,
		?string $status,
		int $limit = 30,
		int $offset = 0
	): array {
		global $wpdb;

		$stringsTable = $wpdb->prefix . Constants::TABLE_STRINGS;
		$translationsTable = $wpdb->prefix . Constants::TABLE_STRING_TRANSLATIONS;

		$where = [];
		$params = [$langCode];

		if ($context !== null && $context !== '') {
			$where[] = 's.`context` = %s';
			$params[] = $context;
		}

		self::addStatusCondition($where, $params, $status);

		$whereClause = !empty($where) ? 'AND ' . implode(' AND ', $where) : '';
		$params[] = $limit;
		$params[] = $offset;

		/** @var array<int, object> */
		return $wpdb->get_results($wpdb->prepare(
			"SELECT s.*, t.`value` AS `translation_value`, t.`status` AS `translation_status`, t.`translator_id`
			FROM `{$stringsTable}` s
			LEFT JOIN `{$translationsTable}` t ON s.`id` = t.`string_id` AND t.`language_code` = %s
			WHERE 1=1 {$whereClause}
			ORDER BY s.`context` ASC, s.`name` ASC
			LIMIT %d OFFSET %d",
			...$params
		));
	}

	/**
	 * Count strings with optional context and status filter.
	 */
	public static function countStringsWithStatus(?string $context, ?string $status, string $langCode): int
	{
		global $wpdb;

		$stringsTable = $wpdb->prefix . Constants::TABLE_STRINGS;
		$translationsTable = $wpdb->prefix . Constants::TABLE_STRING_TRANSLATIONS;

		$where = [];
		$params = [$langCode];

		if ($context !== null && $context !== '') {
			$where[] = 's.`context` = %s';
			$params[] = $context;
		}

		self::addStatusCondition($where, $params, $status);

		$whereClause = !empty($where) ? 'AND ' . implode(' AND ', $where) : '';

		return (int) $wpdb->get_var($wpdb->prepare(
			"SELECT COUNT(*) FROM `{$stringsTable}` s
			LEFT JOIN `{$translationsTable}` t ON s.`id` = t.`string_id` AND t.`language_code` = %s
			WHERE 1=1 {$whereClause}",
			...$params
		));
	}

	/**
	 * Search strings with translation and optional status filter.
	 *
	 * @return array<int, object>
	 */
	public static function searchStringsWithTranslation(
		string $query,
		string $langCode,
		?string $context,
		?string $status,
		int $limit = 30,
		int $offset = 0
	): array {
		global $wpdb;

		$stringsTable = $wpdb->prefix . Constants::TABLE_STRINGS;
		$translationsTable = $wpdb->prefix . Constants::TABLE_STRING_TRANSLATIONS;

		$where = [];
		$params = [$langCode];

		$likeQuery = '%' . $wpdb->esc_like($query) . '%';
		$where[] = '(s.`name` LIKE %s OR s.`value` LIKE %s)';
		$params[] = $likeQuery;
		$params[] = $likeQuery;

		if ($context !== null && $context !== '') {
			$where[] = 's.`context` = %s';
			$params[] = $context;
		}

		self::addStatusCondition($where, $params, $status);

		$whereClause = !empty($where) ? 'AND ' . implode(' AND ', $where) : '';
		$params[] = $limit;
		$params[] = $offset;

		/** @var array<int, object> */
		return $wpdb->get_results($wpdb->prepare(
			"SELECT s.*, t.`value` AS `translation_value`, t.`status` AS `translation_status`, t.`translator_id`
			FROM `{$stringsTable}` s
			LEFT JOIN `{$translationsTable}` t ON s.`id` = t.`string_id` AND t.`language_code` = %s
			WHERE 1=1 {$whereClause}
			ORDER BY s.`context` ASC, s.`name` ASC
			LIMIT %d OFFSET %d",
			...$params
		));
	}

	/**
	 * Count search results with optional status filter.
	 */
	public static function countSearchStrings(string $query, ?string $context, ?string $status, string $langCode): int
	{
		global $wpdb;

		$stringsTable = $wpdb->prefix . Constants::TABLE_STRINGS;
		$translationsTable = $wpdb->prefix . Constants::TABLE_STRING_TRANSLATIONS;

		$where = [];
		$params = [$langCode];

		$likeQuery = '%' . $wpdb->esc_like($query) . '%';
		$where[] = '(s.`name` LIKE %s OR s.`value` LIKE %s)';
		$params[] = $likeQuery;
		$params[] = $likeQuery;

		if ($context !== null && $context !== '') {
			$where[] = 's.`context` = %s';
			$params[] = $context;
		}

		self::addStatusCondition($where, $params, $status);

		$whereClause = !empty($where) ? 'AND ' . implode(' AND ', $where) : '';

		return (int) $wpdb->get_var($wpdb->prepare(
			"SELECT COUNT(*) FROM `{$stringsTable}` s
			LEFT JOIN `{$translationsTable}` t ON s.`id` = t.`string_id` AND t.`language_code` = %s
			WHERE 1=1 {$whereClause}",
			...$params
		));
	}

	/**
	 * Add status filter conditions to WHERE clause.
	 *
	 * @param string[] $where
	 * @param array<int, mixed> $params
	 */
	private static function addStatusCondition(array &$where, array &$params, ?string $status): void
	{
		if ($status === null) {
			return;
		}

		if ($status === 'translated') {
			$where[] = 't.`status` = %s';
			$params[] = 'translated';
		} elseif ($status === 'needs_update') {
			$where[] = 't.`status` = %s';
			$params[] = 'needs_update';
		} elseif ($status === 'needs_translation') {
			$where[] = '(t.`id` IS NULL OR t.`status` = %s)';
			$params[] = 'needs_translation';
		}
	}
}
