<?php
/**
 * @package PlugixaCrm
 */

declare( strict_types = 1 );

namespace PlugixaCrm\Modules\KnowledgeBase;

use PlugixaCrm\Core\Categories\CategorySchema;
use PlugixaCrm\Core\Database\Schema as Db;

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

/**
 * The knowledge base: articles, the categories they sit in, and the cases they answer.
 *
 * THE CATEGORY TREE IS NOT DEFINED HERE. It comes from {@see CategorySchema}, which emits the node
 * table and its closure table together — the same shape `document_folders` uses. This module used to
 * hand-roll a `parent_id` column and carry a note explaining why a shared abstraction was not worth
 * it at two consumers; that note is now wrong in the way that matters, because the hand-rolled
 * version could only answer "who is my parent" and the question a category tree exists to serve is
 * "give me everything under Billing".
 *
 * `body` is the article; `body_plain` is the same text with the markup stripped, kept so that search
 * matches what a reader sees rather than the tags around it — searching for "strong" should not find
 * every article with a bold word in it.
 */
final class Schema {

	/**
	 * @return string[]
	 */
	public static function tables(): array {
		return array_merge(
			array( 'kb_articles', 'kb_article_categories', 'case_articles' ),
			CategorySchema::tables( 'kb_categories' )
		);
	}

	/**
	 * @return array<string, string> What dbDelta changed. Empty on a settled schema.
	 */
	public static function install(): array {
		$charset = Db::charset();

		$statements = array_merge(
			array( self::articles( $charset ) ),
			CategorySchema::statements( 'kb_categories' ),
			array( self::article_categories( $charset ), self::case_articles( $charset ) )
		);

		$changes = Db::delta( ...$statements );

		Db::ensure_innodb( ...self::tables() );

		return $changes;
	}

	/**
	 * One article.
	 *
	 * `portal_visible` IS A SECOND DECISION, NOT A SYNONYM FOR `status`. Publishing internally means
	 * the article is finished and staff can rely on it; publishing to customers means somebody outside
	 * the company may read it. Conflating them would make every internal runbook a customer document
	 * the day a portal was switched on, so the column defaults to 0 — the safe direction — and an
	 * article reaches the portal only when somebody says so. It arrived with the portal in 3.13.0,
	 * which is why an existing knowledge base is entirely internal until reviewed.
	 *
	 * `public_visible` IS A THIRD DECISION, AND REUSING `portal_visible` FOR IT WOULD HAVE BEEN THE
	 * WORST BUG IN THIS PLUGIN. It arrived in 3.23.0 with the public help centre, and the temptation
	 * was obvious: an article already flagged for customers is already "external", so why add a
	 * column? Because "our customers, who signed a contract and had to log in" and "anybody on the
	 * internet, including competitors and search engines" are not the same audience, and a site that
	 * had spent two years ticking the first box would have published all of it to the second one THE
	 * MOMENT IT UPDATED — a disclosure delivered by an upgrade, with nobody having done anything and
	 * nothing to see in a diff. So: a new column, defaulting to 0, and the three flags stack —
	 * `status` says it is finished, `portal_visible` says a customer may read it, `public_visible`
	 * says the world may.
	 *
	 * @param string $charset Charset/collate clause.
	 */
	private static function articles( string $charset ): string {
		$table = Db::table( 'kb_articles' );

		return "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL auto_increment,
			name varchar(255) NOT NULL default '',
			status varchar(20) NOT NULL default 'draft',
			language varchar(10) NOT NULL default '',
			body longtext,
			body_plain longtext,
			publish_date date default NULL,
			expiration_date date default NULL,
			position int(11) NOT NULL default 0,
			portal_visible tinyint(1) NOT NULL default 0,
			public_visible tinyint(1) NOT NULL default 0,
			assigned_user_id bigint(20) unsigned default NULL,
			created_by bigint(20) unsigned default NULL,
			modified_by bigint(20) unsigned default NULL,
			created_at datetime default NULL,
			modified_at datetime default NULL,
			deleted_at datetime default NULL,
			PRIMARY KEY  (id),
			KEY name (name),
			KEY status (status),
			KEY position (position),
			KEY deleted_at (deleted_at)
		) {$charset};";
	}

	/**
	 * An article can be filed in several categories. Espo models it the same way, because a
	 * "Refunds" article belongs under both Billing and Returns and picking one loses the other.
	 *
	 * @param string $charset Charset/collate clause.
	 */
	private static function article_categories( string $charset ): string {
		$table = Db::table( 'kb_article_categories' );

		return "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL auto_increment,
			article_id bigint(20) unsigned NOT NULL default 0,
			category_id bigint(20) unsigned NOT NULL default 0,
			created_by bigint(20) unsigned default NULL,
			modified_by bigint(20) unsigned default NULL,
			created_at datetime default NULL,
			modified_at datetime default NULL,
			deleted_at datetime default NULL,
			PRIMARY KEY  (id),
			KEY article_id (article_id),
			KEY category_id (category_id),
			KEY article_category (article_id,category_id),
			KEY deleted_at (deleted_at)
		) {$charset};";
	}

	/**
	 * Which articles answer which case.
	 *
	 * OWNED BY THE KNOWLEDGE BASE, NOT BY CASES, so that Cases never has to know a knowledge base
	 * exists. The dependency points one way on purpose: a support desk without articles is a working
	 * support desk, and a module that can be removed without editing another is the property the
	 * whole registry exists to preserve.
	 *
	 * @param string $charset Charset/collate clause.
	 */
	private static function case_articles( string $charset ): string {
		$table = Db::table( 'case_articles' );

		return "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL auto_increment,
			case_id bigint(20) unsigned NOT NULL default 0,
			article_id bigint(20) unsigned NOT NULL default 0,
			created_by bigint(20) unsigned default NULL,
			modified_by bigint(20) unsigned default NULL,
			created_at datetime default NULL,
			modified_at datetime default NULL,
			deleted_at datetime default NULL,
			PRIMARY KEY  (id),
			KEY case_id (case_id),
			KEY article_id (article_id),
			KEY case_article (case_id,article_id),
			KEY deleted_at (deleted_at)
		) {$charset};";
	}
}