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

declare( strict_types = 1 );

namespace PlugixaCrm\Modules\DocumentLibrary;

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

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

/**
 * The Documents LIBRARY — EspoCRM's Document entity.
 *
 * NOT to be confused with `Modules\Documents`, which is the per-record file-ATTACHMENT panel (table
 * `documents`). This module is a first-class, navigable file library: each row is a Document record that
 * wraps ONE uploaded file plus metadata (name, type, status, dates, folder, assignee) and can be linked to
 * accounts and opportunities. It reuses `Documents\Storage` and `Documents\FileIntake` for the bytes, so the
 * two features share one storage layer and one allow-list — they differ only in what they hang a file off.
 *
 * The names are deliberately `library_*` because `documents` / `/documents` / `modules/documents/` are
 * already owned by attachments. The entity's api_base MUST equal its table name (`RecordLoader` uses it as
 * the table, and `TagPermissionsTest` enforces it), which is why the table is `library_documents`.
 *
 * dbDelta rules apply: lowercase types, TWO spaces after "PRIMARY KEY", every KEY named.
 */
final class Schema {

	/**
	 * @return string[]
	 */
	public static function tables(): array {
		return array_merge(
			array( 'library_documents', 'document_links' ),
			CategorySchema::tables( 'document_folders' )
		);
	}

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

		$changes = array_merge(
			Db::delta( self::documents( $charset ) ),
			Db::delta( ...self::folders() ),
			Db::delta( self::links( $charset ) )
		);

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

		return $changes;
	}

	/**
	 * A library document: one file, plus what a business needs to file it.
	 *
	 * The file lives on the row exactly as it does on the attachment table — `disk_name` is 32 hex
	 * characters and `.bin`, unguessable and never derived from anything a caller knows, and `mime` is
	 * what finfo read from the real bytes at upload. The metadata around it (type, status, publish and
	 * expiration dates) is the part attachments never had and a document library needs.
	 *
	 * The `UNIQUE KEY disk_name` is safe with soft delete for the same reason it is on `documents`: a
	 * library document always has a file, `disk_name` is 128 bits of randomness, and it is never
	 * re-created — so no insert, live or trashed, ever collides. The key turns a 1-in-2^128 collision
	 * into a loud failure instead of one file quietly overwriting another.
	 *
	 * `portal_visible` DEFAULTS TO 0, AND THE DEFAULT IS THE MIGRATION. dbDelta's ADD COLUMN applies
	 * a default to every row that already exists, so this one column decides what happens to a
	 * library full of signed contracts, supplier price lists and internal procedures the first time
	 * a site updates into the customer portal. Defaulting to 1 would have published all of it, to
	 * every portal customer, with nobody having done anything — a leak delivered by an upgrade. It
	 * is a THIRD decision, distinct from `status = active` (the document is current) and from being
	 * linked to a record (it concerns them): neither of those means a customer may read it.
	 *
	 * @param string $charset Charset/collate clause.
	 */
	private static function documents( string $charset ): string {
		$table = Db::table( 'library_documents' );

		return "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL auto_increment,
			name varchar(255) NOT NULL default '',
			folder_id bigint(20) unsigned default NULL,
			type varchar(50) NOT NULL default '',
			status varchar(20) NOT NULL default 'active',
			publish_date date default NULL,
			expiration_date date default NULL,
			description longtext,
			original_name varchar(255) NOT NULL default '',
			disk_name varchar(64) NOT NULL default '',
			mime varchar(100) NOT NULL default '',
			size bigint(20) unsigned NOT NULL default 0,
			portal_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),
			UNIQUE KEY disk_name (disk_name),
			KEY folder_id (folder_id),
			KEY status (status),
			KEY name (name),
			KEY assigned_user_id (assigned_user_id),
			KEY created_at (created_at),
			KEY deleted_at (deleted_at)
		) {$charset};";
	}

	/**
	 * A folder in the document tree, plus the closure table that answers descent.
	 *
	 * SHARED WITH THE KNOWLEDGE BASE. Both are category trees, so both come from
	 * {@see CategorySchema} — the node columns, the `_paths` table, and the indexes that make
	 * "everything under this folder" a single join. All this module adds is `assigned_user_id`.
	 *
	 * There is still NO physical "Top Level" row: a folder with a null parent IS top level, and a
	 * document with a null `folder_id` is unfiled. Building a real root row would mean every query had
	 * to special-case it and every delete had to protect it; a null is cheaper and cannot be renamed or
	 * removed by accident.
	 *
	 * @return string[]
	 */
	private static function folders(): array {
		return CategorySchema::statements(
			'document_folders',
			array( 'assigned_user_id bigint(20) unsigned default NULL' ),
			array( 'KEY assigned_user_id (assigned_user_id)' )
		);
	}

	/**
	 * A link between a document and a record it belongs to.
	 *
	 * A PLAIN junction, deliberately NOT behind a Repository: it has no soft delete and no audit history
	 * of its own (unlinking is just gone), so it carries none of the five audit columns a Repository
	 * table must — and SchemaTest would force them if it extended one. It is polymorphic like the
	 * attachment table, so one link table serves accounts, opportunities, contacts and leads.
	 *
	 * `UNIQUE KEY link` makes the same document-to-record pairing idempotent; the `reverse` key is for the
	 * record→documents direction the reciprocal panel reads.
	 *
	 * @param string $charset Charset/collate clause.
	 */
	private static function links( string $charset ): string {
		$table = Db::table( 'document_links' );

		return "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL auto_increment,
			document_id bigint(20) unsigned NOT NULL default 0,
			entity_type varchar(50) NOT NULL default '',
			entity_id bigint(20) unsigned NOT NULL default 0,
			created_at datetime default NULL,
			created_by bigint(20) unsigned default NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY link (document_id,entity_type,entity_id),
			KEY reverse (entity_type,entity_id)
		) {$charset};";
	}
}