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

declare( strict_types = 1 );

namespace PlugixaCrm\Modules\Notes;

use PlugixaCrm\Core\Database\Schema as Db;

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

/**
 * Notes: every entry in a record's stream — what people wrote AND what the CRM did.
 *
 * THIS TABLE GREW INTO EspoCRM's `Note`, WHICH IS WHAT IT ALREADY WAS. Espo's stream is one table
 * of entries with a `type`: a person's message is `Post`, and "created this", "changed Status from
 * Open to Won", "assigned to Sam" are entries of other types in the same feed. That is exactly this
 * table with a type column — same polymorphic (entity_type, entity_id), same body, same audit
 * columns — so the alternative was a second table holding things written against a record, next to
 * the one that already did.
 *
 * **Every existing note is a `post` and needs no migration.** The column defaults to `post`, which
 * is what every row in it already is.
 *
 * The columns the stream added:
 *   type              post | create | update | assign | status | relate | delete | restore
 *   data              JSON: for `update`, the field and its before/after; for `relate`, what to
 *   related_type/id   the OTHER record an entry is about — the task that was created, say
 *   super_type/id     a second record this entry also belongs to. Espo's `superParent`, and the
 *                     reason an Account's stream shows its opportunities' activity without anyone
 *                     querying opportunities: the entry is simply filed under both.
 *   is_pinned         a post held at the top of the feed
 */
final class Schema {

	/**
	 * @return string[]
	 */
	public static function tables(): array {
		return array( 'notes' );
	}

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

		$changes = Db::delta( self::notes( $charset ) );

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

		return $changes;
	}

	/**
	 * One note.
	 *
	 * Polymorphic on (entity_type, entity_id) — the same shape as `entity_tags`, and for the same
	 * reason: a note that could only hang off a contact would have to be re-invented the day
	 * somebody wanted one on a deal. `entity_type` is a registry slug, so it is whatever entities
	 * exist, including ones no module has written yet.
	 *
	 * NO UNIQUE KEY, AND THAT IS THE POINT. `entity_tags` and `custom_field_values` both override
	 * delete() to purge(), because a soft-deleted row still occupies its UNIQUE key and the next
	 * insert of the same pair is silently rejected by a row the application believes is gone.
	 * There is no such key here — a record may carry any number of notes, including two identical
	 * ones — so nothing stops a trashed note from sitting harmlessly in the table.
	 *
	 * Which is what we want, because unlike a tag link, A NOTE IS USER DATA. Somebody wrote it,
	 * and deleting the wrong one should be recoverable. So notes keep the ordinary soft delete.
	 *
	 * `is_internal` DEFAULTS TO 1, AND THE DIRECTION IS THE WHOLE POINT. It arrived with the customer
	 * portal in 3.13.0, on a table that by then held years of staff discussion written when nobody
	 * outside the company could ever read it. A default of 0 would have made every one of those rows
	 * customer-visible the moment somebody switched a portal on — a data leak delivered by an upgrade,
	 * with no action by anybody and nothing on screen to suggest it had happened.
	 *
	 * So the safe direction is the default, and visibility is decided by a note's ORIGIN: a reply
	 * posted through the portal is written with 0, because the customer wrote it; a note typed by
	 * staff stays internal until somebody ticks "visible to the customer". EspoCRM's `Note.isInternal`
	 * works the same way.
	 *
	 * `KEY recent (created_at)` EXISTS FOR ONE QUERY AND WOULD OTHERWISE BE REDUNDANT-LOOKING. Both
	 * other keys already end in `created_at`, but both LEAD with a type column, so neither can serve
	 * an ordering that names no record — and the dashboard's cross-record feed is exactly that. Left
	 * out, `ORDER BY created_at DESC LIMIT n` is a filesort over the whole table, on the one table in
	 * this plugin that grows with every save anybody makes.
	 *
	 * @param string $charset Engine/charset clause.
	 */
	private static function notes( string $charset ): string {
		$table = Db::table( 'notes' );

		return "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL auto_increment,
			entity_type varchar(50) NOT NULL default '',
			entity_id bigint(20) unsigned NOT NULL default 0,
			type varchar(20) NOT NULL default 'post',
			body longtext,
			data longtext,
			related_type varchar(50) NOT NULL default '',
			related_id bigint(20) unsigned NOT NULL default 0,
			super_type varchar(50) NOT NULL default '',
			super_id bigint(20) unsigned NOT NULL default 0,
			is_pinned tinyint(1) NOT NULL default 0,
			is_internal tinyint(1) NOT NULL default 1,
			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 entity (entity_type,entity_id,created_at),
			KEY super (super_type,super_id,created_at),
			KEY deleted_at (deleted_at),
			KEY recent (created_at)
		) {$charset};";
	}
}