<?php
/**
 * String memory: unique source segments, their translations and which page uses which segment.
 *
 * Tables
 *   softml_strings              id, hash (sha1 of source), source_text, kind
 *   softml_string_translations  string_id, language_code, text, status (draft|approved)
 *   softml_page_strings         page_id (source page), string_id, pos, cnt
 *
 * @package Soft_ML
 */

defined( 'ABSPATH' ) || exit;

class Soft_ML_Strings {

	const STATUS_DRAFT    = 'draft';
	const STATUS_APPROVED = 'approved';

	public static function t_strings() {
		global $wpdb;
		return $wpdb->prefix . 'softml_strings';
	}

	public static function t_trans() {
		global $wpdb;
		return $wpdb->prefix . 'softml_string_translations';
	}

	public static function t_pages() {
		global $wpdb;
		return $wpdb->prefix . 'softml_page_strings';
	}

	public static function install_schema() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$c = $wpdb->get_charset_collate();

		dbDelta( 'CREATE TABLE ' . self::t_strings() . " (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			hash char(40) NOT NULL,
			source_text mediumtext NOT NULL,
			kind varchar(10) NOT NULL DEFAULT 'text',
			created_at datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY hash (hash)
		) {$c};" );

		dbDelta( 'CREATE TABLE ' . self::t_trans() . " (
			string_id bigint(20) unsigned NOT NULL,
			language_code varchar(20) NOT NULL,
			text mediumtext NOT NULL,
			status varchar(10) NOT NULL DEFAULT 'draft',
			updated_at datetime NOT NULL,
			PRIMARY KEY  (string_id,language_code),
			KEY lang_status (language_code,status)
		) {$c};" );

		dbDelta( 'CREATE TABLE ' . self::t_pages() . " (
			page_id bigint(20) unsigned NOT NULL,
			string_id bigint(20) unsigned NOT NULL,
			pos int(10) unsigned NOT NULL DEFAULT 0,
			cnt int(10) unsigned NOT NULL DEFAULT 1,
			PRIMARY KEY  (page_id,string_id),
			KEY string_id (string_id)
		) {$c};" );
	}

	/**
	 * Record the segments found on a page (replaces its previous list).
	 *
	 * @param int   $page_id  Source page ID.
	 * @param array $segments Output of Soft_ML_Segmenter::extract() (insertion order = document order).
	 * @return array [ 'segments' => n, 'new' => n ]
	 */
	public static function register_page( $page_id, array $segments ) {
		global $wpdb;
		$page_id = absint( $page_id );
		$now     = current_time( 'mysql', true );

		$known = array();
		foreach ( array_chunk( array_keys( $segments ), 300 ) as $chunk ) {
			foreach ( self::ids_for_hashes( $chunk ) as $hash => $id ) {
				$known[ $hash ] = $id;
			}
		}

		$missing = array_diff_key( $segments, $known );
		foreach ( array_chunk( $missing, 100, true ) as $chunk ) {
			foreach ( $chunk as $hash => $s ) {
				self::ensure_string( $hash, $s['text'], $s['kind'], $now );
			}
			foreach ( self::ids_for_hashes( array_keys( $chunk ) ) as $hash => $id ) {
				$known[ $hash ] = $id;
			}
		}

		$wpdb->delete( self::t_pages(), array( 'page_id' => $page_id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$pos = 0;
		foreach ( $segments as $hash => $s ) {
			if ( ! isset( $known[ $hash ] ) ) {
				continue;
			}
			$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
				self::t_pages(),
				array(
					'page_id'   => $page_id,
					'string_id' => $known[ $hash ],
					'pos'       => ++$pos,
					'cnt'       => (int) $s['count'],
				),
				array( '%d', '%d', '%d', '%d' )
			);
		}
		self::bump_version();
		return array( 'segments' => count( $segments ), 'new' => count( $missing ) );
	}

	/**
	 * hash => string id for the given hashes that exist.
	 *
	 * @param string[] $hashes Hashes (at most a few hundred).
	 * @return array
	 */
	private static function ids_for_hashes( array $hashes ) {
		global $wpdb;
		$hashes = array_values( array_map( 'strval', $hashes ) );
		if ( ! $hashes ) {
			return array();
		}
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->prepare(
				'SELECT id, hash FROM %i WHERE hash IN (' . implode( ',', array_fill( 0, count( $hashes ), '%s' ) ) . ')',
				array_merge( array( self::t_strings() ), $hashes )
			),
			ARRAY_A
		);
		$out = array();
		foreach ( (array) $rows as $r ) {
			$out[ $r['hash'] ] = (int) $r['id'];
		}
		return $out;
	}

	/**
	 * hash => translated text for a page and language.
	 *
	 * @param int    $page_id       Source page ID.
	 * @param string $lang          Language.
	 * @param bool   $approved_only Public output uses approved only; previews include drafts.
	 * @return array
	 */
	public static function map_for_page( $page_id, $lang, $approved_only = true ) {
		global $wpdb;
		$key = 'map_' . absint( $page_id ) . '_' . $lang . '_' . ( $approved_only ? 'a' : 'd' ) . '_' . self::version();
		$hit = wp_cache_get( $key, 'softtrix-multilanguage' );
		if ( false === $hit ) {
			$hit = get_transient( 'softml_' . md5( $key ) );
		}
		if ( is_array( $hit ) ) {
			return $hit;
		}
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->prepare(
				'SELECT s.hash, t.text FROM %i p JOIN %i s ON s.id = p.string_id JOIN %i t ON t.string_id = s.id AND t.language_code = %s WHERE p.page_id = %d AND ( %d = 0 OR t.status = %s )',
				self::t_pages(),
				self::t_strings(),
				self::t_trans(),
				$lang,
				absint( $page_id ),
				$approved_only ? 1 : 0,
				self::STATUS_APPROVED
			),
			ARRAY_A
		);
		$map  = array();
		foreach ( (array) $rows as $r ) {
			$map[ $r['hash'] ] = $r['text'];
		}
		wp_cache_set( $key, $map, 'softtrix-multilanguage' );
		$ttl = (int) Soft_ML_Settings::get( 'performance', 'cache_ttl' );
		if ( $ttl > 0 ) {
			set_transient( 'softml_' . md5( $key ), $map, $ttl );
		}
		return $map;
	}

	/**
	 * Progress for a page + language.
	 *
	 * @return array [ total, approved, draft ]
	 */
	public static function progress( $page_id, $lang ) {
		global $wpdb;
		$row = $wpdb->get_row( // phpcs:ignore WordPress.DB
			$wpdb->prepare(
				'SELECT COUNT(*) AS total, SUM(t.status = %s) AS approved, SUM(t.status = %s) AS draft FROM %i p LEFT JOIN %i t ON t.string_id = p.string_id AND t.language_code = %s WHERE p.page_id = %d',
				self::STATUS_APPROVED,
				self::STATUS_DRAFT,
				self::t_pages(),
				self::t_trans(),
				$lang,
				absint( $page_id )
			),
			ARRAY_A
		);
		return array(
			'total'    => (int) $row['total'],
			'approved' => (int) $row['approved'],
			'draft'    => (int) $row['draft'],
		);
	}

	/**
	 * Rows for the editor.
	 *
	 * @param int    $page_id Source page.
	 * @param string $lang    Language.
	 * @param string $filter  all | todo | review.
	 * @param int    $offset  Offset.
	 * @param int    $limit   Limit.
	 * @return array [ rows, total ]
	 */
	public static function editor_rows( $page_id, $lang, $filter = 'all', $offset = 0, $limit = 40 ) {
		global $wpdb;
		$todo   = 'todo' === $filter ? 1 : 0;
		$review = 'review' === $filter ? 1 : 0;
		$total  = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->prepare(
				'SELECT COUNT(*) FROM %i p JOIN %i s ON s.id = p.string_id LEFT JOIN %i t ON t.string_id = s.id AND t.language_code = %s WHERE p.page_id = %d AND ( %d = 0 OR t.string_id IS NULL ) AND ( %d = 0 OR t.status = %s )',
				self::t_pages(),
				self::t_strings(),
				self::t_trans(),
				$lang,
				absint( $page_id ),
				$todo,
				$review,
				self::STATUS_DRAFT
			)
		);
		$rows   = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->prepare(
				'SELECT s.hash, s.source_text, s.kind, p.cnt, t.text, t.status FROM %i p JOIN %i s ON s.id = p.string_id LEFT JOIN %i t ON t.string_id = s.id AND t.language_code = %s WHERE p.page_id = %d AND ( %d = 0 OR t.string_id IS NULL ) AND ( %d = 0 OR t.status = %s ) ORDER BY p.pos ASC LIMIT %d OFFSET %d',
				self::t_pages(),
				self::t_strings(),
				self::t_trans(),
				$lang,
				absint( $page_id ),
				$todo,
				$review,
				self::STATUS_DRAFT,
				absint( $limit ),
				absint( $offset )
			),
			ARRAY_A
		);
		return array( (array) $rows, $total );
	}

	/**
	 * Save one translation after validating it against its source.
	 *
	 * @param string $hash   Source hash.
	 * @param string $lang   Language.
	 * @param string $text   Translation (placeholder format). Empty deletes the translation.
	 * @param string $status draft|approved.
	 * @return true|WP_Error
	 */
	public static function set_translation( $hash, $lang, $text, $status = self::STATUS_DRAFT ) {
		global $wpdb;
		if ( ! Soft_ML_Languages::exists( $lang ) || $lang === Soft_ML_Languages::default_code() ) {
			return new WP_Error( 'softml_lang', __( 'Invalid target language.', 'softtrix-multilanguage' ) );
		}
		$str = $wpdb->get_row( $wpdb->prepare( 'SELECT id, source_text, kind FROM %i WHERE hash = %s', self::t_strings(), $hash ), ARRAY_A ); // phpcs:ignore WordPress.DB
		if ( ! $str ) {
			return new WP_Error( 'softml_nostring', __( 'Unknown segment.', 'softtrix-multilanguage' ) );
		}
		$text = trim( preg_replace( '/[\x00-\x08\x0B\x0C\x0E-\x1F]/', '', wp_check_invalid_utf8( (string) $text ) ) );
		if ( '' === $text ) {
			$wpdb->delete( self::t_trans(), array( 'string_id' => (int) $str['id'], 'language_code' => $lang ), array( '%d', '%s' ) ); // phpcs:ignore WordPress.DB
			self::bump_version();
			return true;
		}
		$text = preg_replace( '/&(?!#?\w+;)/', '&amp;', $text );
		if ( 'attr' === $str['kind'] ) {
			$ok = false === strpbrk( $text, '<>' ) || ( substr_count( $text, '<' ) <= substr_count( $str['source_text'], '<' ) && substr_count( $text, '>' ) <= substr_count( $str['source_text'], '>' ) );
		} else {
			$ok = Soft_ML_Segmenter::placeholders_match( $str['source_text'], $text );
		}
		if ( ! $ok ) {
			return new WP_Error( 'softml_placeholders', __( 'The translation must keep the same <x1>…</x1> markers as the source and contain no other tags.', 'softtrix-multilanguage' ) );
		}
		$status = self::STATUS_APPROVED === $status ? self::STATUS_APPROVED : self::STATUS_DRAFT;
		$wpdb->query( // phpcs:ignore WordPress.DB
			$wpdb->prepare(
				'INSERT INTO %i (string_id,language_code,text,status,updated_at) VALUES (%d,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE text = VALUES(text), status = VALUES(status), updated_at = VALUES(updated_at)',
				self::t_trans(),
				(int) $str['id'],
				$lang,
				$text,
				$status,
				current_time( 'mysql', true )
			)
		);
		self::bump_version();
		return true;
	}

	/**
	 * Make sure a source string exists (used by imports that arrive before a page scan).
	 *
	 * @param string $hash Hash of $text.
	 * @param string $text Normalised source text.
	 * @param string $kind text|attr.
	 * @param string $now  Creation time (GMT), default now.
	 */
	public static function ensure_string( $hash, $text, $kind = 'text', $now = '' ) {
		global $wpdb;
		$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->prepare(
				'INSERT IGNORE INTO %i (hash,source_text,kind,created_at) VALUES (%s,%s,%s,%s)',
				self::t_strings(),
				$hash,
				$text,
				'attr' === $kind ? 'attr' : 'text',
				'' !== $now ? $now : current_time( 'mysql', true )
			)
		);
	}

	/**
	 * Site-wide coverage for one language over every scanned page's segments.
	 *
	 * @param string $lang Language.
	 * @return array total, translated, missing, missing_chars (unique segments)
	 */
	public static function coverage( $lang ) {
		global $wpdb;
		$total = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(DISTINCT string_id) FROM %i', self::t_pages() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$done  = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB
			$wpdb->prepare(
				'SELECT COUNT(DISTINCT t.string_id) FROM %i t JOIN %i p ON p.string_id = t.string_id WHERE t.language_code = %s',
				self::t_trans(),
				self::t_pages(),
				$lang
			)
		);
		$chars = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB
			$wpdb->prepare(
				'SELECT COALESCE(SUM(CHAR_LENGTH(s.source_text)),0) FROM %i s WHERE s.id IN (SELECT DISTINCT string_id FROM %i) AND NOT EXISTS (SELECT 1 FROM %i t WHERE t.string_id = s.id AND t.language_code = %s)',
				self::t_strings(),
				self::t_pages(),
				self::t_trans(),
				$lang
			)
		);
		return array(
			'total'         => $total,
			'translated'    => $done,
			'missing'       => max( 0, $total - $done ),
			'missing_chars' => $chars,
		);
	}

	/**
	 * Every scanned source segment that lacks a translation in at least one of the languages.
	 * Most-reused segments (header/footer first) come first. Output doubles as an import pack:
	 * fill in the language keys and import it.
	 *
	 * @param string[] $langs Language codes.
	 * @return array items
	 */
	public static function untranslated_items( array $langs ) {
		global $wpdb;
		$have = array();
		foreach ( $langs as $l ) {
			$have[ $l ] = array_flip(
				array_map(
					'intval',
					(array) $wpdb->get_col( // phpcs:ignore WordPress.DB
						$wpdb->prepare( 'SELECT string_id FROM %i WHERE language_code = %s', self::t_trans(), $l )
					)
				)
			);
		}
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB
			$wpdb->prepare( 'SELECT s.id, s.source_text, s.kind, COUNT(DISTINCT p.page_id) AS pages FROM %i s JOIN %i p ON p.string_id = s.id GROUP BY s.id, s.source_text, s.kind ORDER BY pages DESC, s.id ASC', self::t_strings(), self::t_pages() ),
			ARRAY_A
		);
		$items = array();
		foreach ( (array) $rows as $r ) {
			$missing = array();
			foreach ( $langs as $l ) {
				if ( ! isset( $have[ $l ][ (int) $r['id'] ] ) ) {
					$missing[] = $l;
				}
			}
			if ( ! $missing ) {
				continue;
			}
			$item = array( 's' => $r['source_text'] );
			if ( 'attr' === $r['kind'] ) {
				$item['k'] = 'attr';
			}
			$item['pages']   = (int) $r['pages'];
			$item['missing'] = $missing;
			$items[]         = $item;
		}
		return $items;
	}

	/**
	 * @param string $hash Source hash.
	 * @param string $lang Language.
	 * @return bool
	 */
	public static function has_translation( $hash, $lang ) {
		global $wpdb;
		return (bool) $wpdb->get_var( // phpcs:ignore WordPress.DB
			$wpdb->prepare(
				'SELECT 1 FROM %i t JOIN %i s ON s.id = t.string_id WHERE s.hash = %s AND t.language_code = %s LIMIT 1',
				self::t_trans(),
				self::t_strings(),
				$hash,
				$lang
			)
		);
	}

	/**
	 * Untranslated segments of a page for a language: count and total characters.
	 *
	 * @param int    $page_id Source page.
	 * @param string $lang    Language.
	 * @return array [ 'count' => n, 'chars' => n, 'total' => n ]
	 */
	public static function todo_stats( $page_id, $lang ) {
		global $wpdb;
		$row = $wpdb->get_row( // phpcs:ignore WordPress.DB
			$wpdb->prepare(
				'SELECT COUNT(*) AS total, SUM(t.string_id IS NULL) AS todo, COALESCE(SUM(CASE WHEN t.string_id IS NULL THEN CHAR_LENGTH(s.source_text) ELSE 0 END),0) AS chars FROM %i p JOIN %i s ON s.id = p.string_id LEFT JOIN %i t ON t.string_id = s.id AND t.language_code = %s WHERE p.page_id = %d',
				self::t_pages(),
				self::t_strings(),
				self::t_trans(),
				$lang,
				absint( $page_id )
			),
			ARRAY_A
		);
		return array(
			'count' => (int) $row['todo'],
			'chars' => (int) $row['chars'],
			'total' => (int) $row['total'],
		);
	}

	/**
	 * Approve every draft translation of a page's segments (explicit administrator action).
	 *
	 * @param int    $page_id Source page.
	 * @param string $lang    Language.
	 * @return int Rows approved.
	 */
	public static function approve_drafts( $page_id, $lang ) {
		global $wpdb;
		$n = $wpdb->query( // phpcs:ignore WordPress.DB
			$wpdb->prepare(
				'UPDATE %i t JOIN %i p ON p.string_id = t.string_id SET t.status = %s, t.updated_at = %s WHERE p.page_id = %d AND t.language_code = %s AND t.status = %s',
				self::t_trans(),
				self::t_pages(),
				self::STATUS_APPROVED,
				current_time( 'mysql', true ),
				absint( $page_id ),
				$lang,
				self::STATUS_DRAFT
			)
		);
		self::bump_version();
		return (int) $n;
	}

	/**
	 * Approve every draft translation used by any of the given pages or posts, in the given languages.
	 *
	 * @param int[]    $page_ids Source pages/posts.
	 * @param string[] $langs    Languages.
	 * @return int Rows approved.
	 */
	public static function approve_drafts_many( array $page_ids, array $langs ) {
		global $wpdb;
		$page_ids = array_values( array_filter( array_map( 'absint', $page_ids ) ) );
		$langs    = array_values( array_filter( $langs, array( 'Soft_ML_Languages', 'exists' ) ) );
		if ( ! $page_ids || ! $langs ) {
			return 0;
		}
		$n = 0;
		foreach ( array_chunk( $page_ids, 500 ) as $chunk ) {
			$n += (int) $wpdb->query( // phpcs:ignore WordPress.DB
				$wpdb->prepare(
					'UPDATE %i t JOIN (SELECT DISTINCT string_id FROM %i WHERE page_id IN (' . implode( ',', array_fill( 0, count( $chunk ), '%d' ) ) . ')) p ON p.string_id = t.string_id SET t.status = %s, t.updated_at = %s WHERE t.language_code IN (' . implode( ',', array_fill( 0, count( $langs ), '%s' ) ) . ') AND t.status = %s',
					array_merge( array( self::t_trans(), self::t_pages() ), $chunk, array( self::STATUS_APPROVED, current_time( 'mysql', true ) ), $langs, array( self::STATUS_DRAFT ) )
				)
			);
		}
		self::bump_version();
		return $n;
	}

	/**
	 * Draft translations used by pages/posts of one post type, per language.
	 *
	 * @param string $type Post type.
	 * @return array lang => count
	 */
	public static function draft_counts( $type ) {
		global $wpdb;
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB
			$wpdb->prepare(
				"SELECT t.language_code AS lang, COUNT(DISTINCT t.string_id) AS n FROM %i t JOIN %i p ON p.string_id = t.string_id JOIN {$wpdb->posts} s ON s.ID = p.page_id WHERE s.post_type = %s AND t.status = %s GROUP BY t.language_code",
				self::t_trans(),
				self::t_pages(),
				$type,
				self::STATUS_DRAFT
			),
			ARRAY_A
		);
		$out = array();
		foreach ( (array) $rows as $r ) {
			$out[ $r['lang'] ] = (int) $r['n'];
		}
		return $out;
	}

	/**
	 * IDs of the scanned originals of one post type (optionally only published ones).
	 *
	 * @param string $type           Post type.
	 * @param bool   $published_only Only published originals.
	 * @return int[]
	 */
	public static function scanned_ids( $type, $published_only = false ) {
		global $wpdb;
		if ( $published_only ) {
			$ids = $wpdb->get_col( // phpcs:ignore WordPress.DB
				$wpdb->prepare(
					"SELECT DISTINCT p.page_id FROM %i p JOIN {$wpdb->posts} s ON s.ID = p.page_id WHERE s.post_type = %s AND s.post_status = 'publish'",
					self::t_pages(),
					$type
				)
			);
		} else {
			$ids = $wpdb->get_col( // phpcs:ignore WordPress.DB
				$wpdb->prepare(
					"SELECT DISTINCT p.page_id FROM %i p JOIN {$wpdb->posts} s ON s.ID = p.page_id WHERE s.post_type = %s AND s.post_status NOT IN ('trash','auto-draft')",
					self::t_pages(),
					$type
				)
			);
		}
		return array_map( 'intval', (array) $ids );
	}

	/**
	 * Forget which text a page contains (it is no longer prepared for translation).
	 * Translations already made are kept: other pages may share the same text.
	 *
	 * @param int $page_id Original page ID.
	 */
	public static function forget_page( $page_id ) {
		global $wpdb;
		$wpdb->delete( self::t_pages(), array( 'page_id' => absint( $page_id ) ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
		self::bump_version();
	}

	/**
	 * Cache-busting counter; any translation change invalidates cached maps.
	 *
	 * @return int
	 */
	public static function version() {
		return (int) get_option( 'softml_strings_ver', 1 );
	}

	public static function bump_version() {
		update_option( 'softml_strings_ver', self::version() + 1, false );
	}
}