<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Questions are stored in the plugin's own table, which has no WordPress API. The questions shown to visitors are cached in items().

/**
 * Questions, topics, validation, and JSON import/export.
 */
class Replyvane_Store {

	const MAX_QUESTION = 300;
	const MAX_ANSWER   = 10000;
	const MAX_ITEMS    = 2000;

	/* ------------------------------------------------------------------ */
	/* Reading                                                             */
	/* ------------------------------------------------------------------ */

	public static function items( $active_only = false ) {
		global $wpdb;
		$table = Replyvane_Plugin::items_table();
		// Shown questions are read on every chat request: keep them in the object
		// cache until the questions change (every change updates data_version).
		$key = 'items_' . Replyvane_Plugin::data_version();
		if ( $active_only ) {
			$cached = wp_cache_get( $key, 'replyvane' );
			if ( is_array( $cached ) ) {
				return $cached;
			}
		}
		$rows = $active_only
			? $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE active = 1 ORDER BY sort_order ASC, id ASC', $table ), ARRAY_A )
			: $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i ORDER BY sort_order ASC, id ASC', $table ), ARRAY_A );
		$rows = is_array( $rows ) ? $rows : array();
		if ( $active_only ) {
			wp_cache_set( $key, $rows, 'replyvane', HOUR_IN_SECONDS );
		}
		return $rows;
	}

	public static function get( $id ) {
		global $wpdb;
		$table = Replyvane_Plugin::items_table();
		return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM %i WHERE id = %d", $table, $id ), ARRAY_A );
	}

	public static function get_by_slug( $slug ) {
		global $wpdb;
		$table = Replyvane_Plugin::items_table();
		return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM %i WHERE slug = %s", $table, $slug ), ARRAY_A );
	}

	public static function delete( $id ) {
		global $wpdb;
		$wpdb->delete( Replyvane_Plugin::items_table(), array( 'id' => (int) $id ), array( '%d' ) );
		Replyvane_Plugin::bump_version();
	}

	public static function decode_list( $json ) {
		$list = json_decode( (string) $json, true );
		return is_array( $list ) ? $list : array();
	}

	/* ------------------------------------------------------------------ */
	/* Topics                                                              */
	/* ------------------------------------------------------------------ */

	public static function categories() {
		$cats = get_option( 'replyvane_categories', array() );
		$cats = is_array( $cats ) ? $cats : array();
		usort(
			$cats,
			function ( $a, $b ) {
				return (int) $a['order'] <=> (int) $b['order'];
			}
		);
		return $cats;
	}

	public static function category_ids() {
		return wp_list_pluck( self::categories(), 'id' );
	}

	public static function save_categories( array $raw ) {
		$clean = array();
		$seen  = array();
		foreach ( $raw as $i => $cat ) {
			if ( ! is_array( $cat ) ) {
				continue;
			}
			$id    = sanitize_title( isset( $cat['id'] ) ? $cat['id'] : '' );
			$title = trim( sanitize_text_field( isset( $cat['title'] ) ? $cat['title'] : '' ) );
			if ( '' === $id && '' !== $title ) {
				$id = sanitize_title( $title );
			}
			if ( '' === $id || isset( $seen[ $id ] ) ) {
				continue;
			}
			$seen[ $id ] = true;
			$clean[]     = array(
				'id'    => substr( $id, 0, 100 ),
				'title' => '' !== $title ? mb_substr( $title, 0, 80 ) : ucwords( str_replace( '-', ' ', $id ) ),
				'order' => isset( $cat['order'] ) ? (int) $cat['order'] : $i + 1,
			);
		}
		update_option( 'replyvane_categories', $clean, false );
		Replyvane_Plugin::bump_version();
		return $clean;
	}

	/** Adds a topic if it doesn't exist yet. Returns true when it had to be created. */
	public static function ensure_category( $id ) {
		$cats = self::categories();
		if ( in_array( $id, wp_list_pluck( $cats, 'id' ), true ) ) {
			return false;
		}
		$cats[] = array(
			'id'    => $id,
			'title' => ucwords( str_replace( '-', ' ', $id ) ),
			'order' => count( $cats ) + 1,
		);
		self::save_categories( $cats );
		return true;
	}

	public static function items_in_category( $id ) {
		global $wpdb;
		$table = Replyvane_Plugin::items_table();
		return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE category = %s", $table, $id ) );
	}

	/* ------------------------------------------------------------------ */
	/* Validation                                                          */
	/* ------------------------------------------------------------------ */

	private static function to_bool( $value, $default ) {
		if ( null === $value ) {
			return $default;
		}
		if ( is_bool( $value ) ) {
			return $value;
		}
		return in_array( strtolower( trim( (string) $value ) ), array( '1', 'true', 'yes', 'on' ), true );
	}

	public static function clean_answer( $text ) {
		$text = wp_check_invalid_utf8( (string) $text );
		$text = str_replace( array( "\r\n", "\r" ), "\n", $text );
		$text = wp_strip_all_tags( $text );
		return trim( mb_substr( $text, 0, self::MAX_ANSWER ) );
	}

	/** Accepts https/http links, site-relative paths, mailto: and tel:. */
	public static function clean_url( $url ) {
		$url = trim( (string) $url );
		if ( '' === $url ) {
			return '';
		}
		if ( preg_match( '#^(mailto|tel):#i', $url ) ) {
			return esc_url_raw( $url, array( 'mailto', 'tel' ) );
		}
		if ( 0 === strpos( $url, '/' ) && 0 !== strpos( $url, '//' ) ) {
			$url = home_url( $url );
		}
		return esc_url_raw( $url, array( 'http', 'https' ) );
	}

	/** Links as an array of {label, url}, or lines of "Label | URL". */
	public static function clean_links( $raw ) {
		if ( is_string( $raw ) ) {
			$lines = preg_split( '/\r\n|\r|\n/', $raw );
			$raw   = array();
			foreach ( $lines as $line ) {
				$parts = array_map( 'trim', explode( '|', $line, 2 ) );
				if ( 2 === count( $parts ) ) {
					$raw[] = array( 'label' => $parts[0], 'url' => $parts[1] );
				}
			}
		}
		$links = array();
		foreach ( (array) $raw as $link ) {
			if ( ! is_array( $link ) ) {
				continue;
			}
			$label = trim( sanitize_text_field( isset( $link['label'] ) ? $link['label'] : '' ) );
			$url   = self::clean_url( isset( $link['url'] ) ? $link['url'] : '' );
			if ( '' !== $label && '' !== $url ) {
				$links[] = array( 'label' => mb_substr( $label, 0, 60 ), 'url' => $url );
			}
		}
		return array_slice( $links, 0, 6 );
	}

	private static function clean_list( $raw, $sanitize, $max ) {
		if ( is_string( $raw ) ) {
			$raw = explode( ',', $raw );
		}
		$out = array();
		foreach ( (array) $raw as $value ) {
			$value = trim( call_user_func( $sanitize, (string) $value ) );
			if ( '' !== $value ) {
				$out[ $value ] = true;
			}
		}
		return array_slice( array_keys( $out ), 0, $max );
	}

	/**
	 * Turns one question (from the JSON file or the edit form) into a clean
	 * database row, or a WP_Error explaining what is wrong.
	 */
	public static function clean_item( array $raw, $fallback_order = 0 ) {
		$question = trim( sanitize_text_field( isset( $raw['question'] ) ? (string) $raw['question'] : '' ) );
		if ( '' === $question ) {
			return new WP_Error( 'question', 'The question is empty.' );
		}
		$answer = self::clean_answer( isset( $raw['answer'] ) ? $raw['answer'] : '' );
		if ( '' === $answer ) {
			return new WP_Error( 'answer', 'The answer is empty.' );
		}

		$slug = sanitize_title( isset( $raw['id'] ) ? (string) $raw['id'] : '' );
		if ( '' === $slug ) {
			$slug = sanitize_title( mb_substr( $question, 0, 80 ) );
		}
		$slug     = substr( $slug, 0, 100 );
		$category = sanitize_title( isset( $raw['category'] ) ? (string) $raw['category'] : '' );

		$related = self::clean_list( isset( $raw['related'] ) ? $raw['related'] : array(), 'sanitize_title', 8 );
		$related = array_values( array_diff( $related, array( $slug ) ) );

		return array(
			'slug'       => $slug,
			'category'   => '' !== $category ? substr( $category, 0, 100 ) : 'general',
			'question'   => mb_substr( $question, 0, self::MAX_QUESTION ),
			'answer'     => $answer,
			'keywords'   => implode( ', ', self::clean_list( isset( $raw['keywords'] ) ? $raw['keywords'] : array(), 'sanitize_text_field', 40 ) ),
			'links'      => wp_json_encode( self::clean_links( isset( $raw['links'] ) ? $raw['links'] : array() ) ),
			'related'    => wp_json_encode( $related ),
			'featured'   => self::to_bool( isset( $raw['featured'] ) ? $raw['featured'] : null, false ) ? 1 : 0,
			'sort_order' => isset( $raw['order'] ) && is_numeric( $raw['order'] ) ? (int) $raw['order'] : (int) $fallback_order,
			'active'     => self::to_bool( isset( $raw['active'] ) ? $raw['active'] : null, true ) ? 1 : 0,
			// "auto" = created from the website and refreshed on each scan; "manual" = yours, never overwritten.
			'source'     => isset( $raw['source'] ) && 'auto' === $raw['source'] ? 'auto' : 'manual',
		);
	}

	/* ------------------------------------------------------------------ */
	/* Writing                                                             */
	/* ------------------------------------------------------------------ */

	/** Saves a cleaned row. Returns the id or a WP_Error. */
	public static function save( array $row, $id = 0, $bump = true ) {
		global $wpdb;
		$table    = Replyvane_Plugin::items_table();
		$existing = self::get_by_slug( $row['slug'] );
		if ( $existing && (int) $existing['id'] !== (int) $id ) {
			return new WP_Error( 'slug', sprintf( 'Another question already uses the ID "%s".', $row['slug'] ) );
		}
		self::ensure_category( $row['category'] );
		$row['updated_at'] = current_time( 'mysql', true );

		if ( $id ) {
			$ok = $wpdb->update( $table, $row, array( 'id' => (int) $id ) );
		} else {
			$ok = $wpdb->insert( $table, $row );
			$id = (int) $wpdb->insert_id;
		}
		if ( false === $ok ) {
			return new WP_Error( 'db', 'The database refused the change: ' . $wpdb->last_error );
		}
		if ( $bump ) {
			Replyvane_Plugin::bump_version();
		}
		return (int) $id;
	}

	/**
	 * Imports questions from JSON.
	 *
	 * @param string $json JSON text.
	 * @param string $mode "merge" adds new questions and updates matching ids;
	 *                     "replace" deletes every existing question first.
	 */
	public static function import( $json, $mode = 'merge' ) {
		global $wpdb;
		$report = array(
			'added'    => 0,
			'updated'  => 0,
			'skipped'  => 0,
			'errors'   => array(),
			'warnings' => array(),
		);

		$json = preg_replace( '/^\xEF\xBB\xBF/', '', (string) $json ); // Strip a byte-order mark.
		$data = json_decode( $json, true );
		if ( ! is_array( $data ) ) {
			$report['errors'][] = 'This is not valid JSON (' . json_last_error_msg() . '). Check for a missing comma or quote near the place mentioned.';
			return $report;
		}
		if ( isset( $data[0] ) ) {
			$data = array( 'faqs' => $data ); // A bare list of questions is fine too.
		}
		if ( empty( $data['faqs'] ) || ! is_array( $data['faqs'] ) ) {
			$report['errors'][] = 'No "faqs" list was found in the file.';
			return $report;
		}
		if ( count( $data['faqs'] ) > self::MAX_ITEMS ) {
			$report['errors'][] = sprintf( 'The file has more than %d questions. Split it into smaller files.', self::MAX_ITEMS );
			return $report;
		}

		// Check every question before changing anything.
		$rows = array();
		$seen = array();
		foreach ( array_values( $data['faqs'] ) as $i => $raw ) {
			$n = $i + 1;
			if ( ! is_array( $raw ) ) {
				$report['errors'][] = "Question {$n} is not written as an object { … }.";
				$report['skipped']++;
				continue;
			}
			$row = self::clean_item( $raw, $n );
			if ( is_wp_error( $row ) ) {
				$label              = isset( $raw['question'] ) ? ' ("' . mb_substr( sanitize_text_field( (string) $raw['question'] ), 0, 60 ) . '")' : '';
				$report['errors'][] = "Question {$n}{$label}: " . $row->get_error_message();
				$report['skipped']++;
				continue;
			}
			if ( isset( $seen[ $row['slug'] ] ) ) {
				$report['errors'][] = "Question {$n}: the id \"{$row['slug']}\" is used more than once in the file.";
				$report['skipped']++;
				continue;
			}
			$seen[ $row['slug'] ] = true;
			$rows[]               = $row;
		}
		if ( empty( $rows ) ) {
			$report['errors'][] = 'Nothing was imported.';
			return $report;
		}

		// Topics.
		$file_cats = isset( $data['categories'] ) && is_array( $data['categories'] ) ? $data['categories'] : array();
		if ( 'replace' === $mode ) {
			$cats = $file_cats;
		} else {
			$cats  = self::categories();
			$index = array_flip( wp_list_pluck( $cats, 'id' ) );
			foreach ( $file_cats as $fc ) {
				$fid = sanitize_title( isset( $fc['id'] ) ? $fc['id'] : '' );
				if ( '' === $fid ) {
					continue;
				}
				if ( isset( $index[ $fid ] ) ) {
					$cats[ $index[ $fid ] ] = array_merge( $cats[ $index[ $fid ] ], $fc, array( 'id' => $fid ) );
				} else {
					$cats[] = $fc;
				}
			}
		}
		self::save_categories( $cats );
		$known_cats = self::category_ids();
		foreach ( array_unique( wp_list_pluck( $rows, 'category' ) ) as $cid ) {
			if ( ! in_array( $cid, $known_cats, true ) ) {
				self::ensure_category( $cid );
				$report['warnings'][] = "The topic \"{$cid}\" wasn't in the categories list, so it was created. You can rename it under Topics.";
			}
		}

		// Related questions must point at questions that exist.
		$known = array_keys( $seen );
		if ( 'replace' !== $mode ) {
			$known = array_merge( $known, wp_list_pluck( self::items(), 'slug' ) );
		}
		$known = array_flip( $known );
		foreach ( $rows as &$row ) {
			$related = self::decode_list( $row['related'] );
			$missing = array_diff( $related, array_keys( $known ) );
			if ( $missing ) {
				$report['warnings'][] = sprintf( '"%s" lists related questions that don\'t exist: %s. They were left out.', $row['slug'], implode( ', ', $missing ) );
				$row['related']       = wp_json_encode( array_values( array_intersect( $related, array_keys( $known ) ) ) );
			}
		}
		unset( $row );

		$table = Replyvane_Plugin::items_table();
		$wpdb->query( 'START TRANSACTION' );
		if ( 'replace' === $mode ) {
			$wpdb->query( $wpdb->prepare( "DELETE FROM %i", $table ));
		}
		foreach ( $rows as $row ) {
			$existing = self::get_by_slug( $row['slug'] );
			$result   = self::save( $row, $existing ? (int) $existing['id'] : 0, false );
			if ( is_wp_error( $result ) ) {
				$report['errors'][] = $row['slug'] . ': ' . $result->get_error_message();
				$report['skipped']++;
			} elseif ( $existing ) {
				$report['updated']++;
			} else {
				$report['added']++;
			}
		}
		$wpdb->query( 'COMMIT' );
		Replyvane_Plugin::bump_version();

		return $report;
	}

	/** Everything, in the same format the importer reads. */
	public static function export() {
		$faqs = array();
		foreach ( self::items() as $row ) {
			$faqs[] = array(
				'id'       => $row['slug'],
				'category' => $row['category'],
				'question' => $row['question'],
				'answer'   => $row['answer'],
				'keywords' => '' !== $row['keywords'] ? array_map( 'trim', explode( ',', $row['keywords'] ) ) : array(),
				'links'    => self::decode_list( $row['links'] ),
				'related'  => self::decode_list( $row['related'] ),
				'featured' => (bool) $row['featured'],
				'order'    => (int) $row['sort_order'],
				'active'   => (bool) $row['active'],
				'source'   => $row['source'],
			);
		}
		return array(
			'format'     => 'replyvane',
			'version'    => 1,
			'exported'   => gmdate( 'c' ),
			'categories' => self::categories(),
			'faqs'       => $faqs,
		);
	}

	/** Compact data sent to the chat window: active questions only. */
	public static function public_data() {
		$faqs = array();
		$used = array();
		foreach ( self::items( true ) as $row ) {
			$used[ $row['category'] ] = true;
			$faqs[]                   = array(
				'id' => $row['slug'],
				'c'  => $row['category'],
				'q'  => $row['question'],
				'a'  => $row['answer'],
				'k'  => $row['keywords'],
				'l'  => self::decode_list( $row['links'] ),
				'r'  => self::decode_list( $row['related'] ),
				'f'  => (bool) $row['featured'],
			);
		}
		$cats = array();
		foreach ( self::categories() as $cat ) {
			if ( isset( $used[ $cat['id'] ] ) ) {
				$cats[] = array(
					'id'    => $cat['id'],
					'title' => $cat['title'],
				);
			}
		}
		return array(
			'categories' => $cats,
			'faqs'       => $faqs,
		);
	}
}
// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching