<?php

declare(strict_types=1);

namespace Signocore\Multilanguage\Application;

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

use Signocore\Multilanguage\Infrastructure\Traits\Singleton;

final class Database
{
	use Singleton;

	protected function init(): void
	{
		// Tables are created via ServiceRegistry::ensureTables() and Installer::activate().
		// No admin_init hook needed — the db_version option flag handles idempotency.
	}

	public function createTables(): void
	{
		$this->createLanguagesTable();
		$this->createTranslationsTable();
		$this->createStringsTable();
		$this->createStringTranslationsTable();
	}

	private function createLanguagesTable(): void
	{
		global $wpdb;

		$table = $wpdb->prefix . Constants::TABLE_LANGUAGES;
		if ($this->tableExists($table)) {
			return;
		}

		$charset = $wpdb->get_charset_collate();

		$wpdb->query("CREATE TABLE `{$table}` (
			`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
			`code` varchar(10) NOT NULL,
			`locale` varchar(35) NOT NULL,
			`name` varchar(255) NOT NULL,
			`native_name` varchar(255) NOT NULL,
			`is_default` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
			`is_active` tinyint(1) UNSIGNED NOT NULL DEFAULT 1,
			`is_rtl` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
			`flag` varchar(50) NOT NULL DEFAULT '',
			`sort_order` int(10) UNSIGNED NOT NULL DEFAULT 0,
			`domain` varchar(255) DEFAULT NULL,
			`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (`id`),
			UNIQUE INDEX `code_unique` (`code`),
			INDEX `is_active_index` (`is_active`),
			INDEX `is_default_index` (`is_default`)
		) ENGINE=InnoDB {$charset};");
	}

	private function createTranslationsTable(): void
	{
		global $wpdb;

		$table = $wpdb->prefix . Constants::TABLE_TRANSLATIONS;
		if ($this->tableExists($table)) {
			return;
		}

		$charset = $wpdb->get_charset_collate();

		$wpdb->query("CREATE TABLE `{$table}` (
			`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			`group_id` bigint(20) UNSIGNED NOT NULL,
			`element_id` bigint(20) UNSIGNED NOT NULL,
			`element_type` varchar(60) NOT NULL,
			`language_code` varchar(10) NOT NULL,
			`source_language_code` varchar(10) DEFAULT NULL,
			`status` varchar(20) NOT NULL DEFAULT 'draft',
			`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (`id`),
			UNIQUE INDEX `element_lang_unique` (`element_id`, `element_type`, `language_code`),
			INDEX `group_id_index` (`group_id`),
			INDEX `element_type_index` (`element_type`),
			INDEX `language_code_index` (`language_code`),
			INDEX `status_index` (`status`)
		) ENGINE=InnoDB {$charset};");
	}

	private function createStringsTable(): void
	{
		global $wpdb;

		$table = $wpdb->prefix . Constants::TABLE_STRINGS;
		if ($this->tableExists($table)) {
			return;
		}

		$charset = $wpdb->get_charset_collate();

		$wpdb->query("CREATE TABLE `{$table}` (
			`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			`context` varchar(160) NOT NULL,
			`name` varchar(160) NOT NULL,
			`value` text NOT NULL,
			`language_code` varchar(10) NOT NULL,
			`type` varchar(40) NOT NULL DEFAULT 'line',
			`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (`id`),
			UNIQUE INDEX `context_name_unique` (`context`, `name`),
			INDEX `context_index` (`context`)
		) ENGINE=InnoDB {$charset};");
	}

	private function createStringTranslationsTable(): void
	{
		global $wpdb;

		$table = $wpdb->prefix . Constants::TABLE_STRING_TRANSLATIONS;
		if ($this->tableExists($table)) {
			return;
		}

		$charset = $wpdb->get_charset_collate();

		$wpdb->query("CREATE TABLE `{$table}` (
			`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			`string_id` bigint(20) UNSIGNED NOT NULL,
			`language_code` varchar(10) NOT NULL,
			`value` text DEFAULT NULL,
			`status` varchar(20) NOT NULL DEFAULT 'needs_translation',
			`translator_id` bigint(20) UNSIGNED DEFAULT NULL,
			`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (`id`),
			UNIQUE INDEX `string_lang_unique` (`string_id`, `language_code`),
			INDEX `string_id_index` (`string_id`),
			INDEX `status_index` (`status`)
		) ENGINE=InnoDB {$charset};");
	}

	private function tableExists(string $table): bool
	{
		global $wpdb;

		$query = $wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($table));

		return $wpdb->get_var($query) === $table;
	}
}
