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

declare( strict_types = 1 );

namespace PlugixaCrm\Modules\Notes\Repositories;

use PlugixaCrm\Core\Database\Repository;

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

/**
 * Notes on a record.
 *
 * `delete()` is NOT overridden to purge here, unlike EntityTagRepository. A note is something a
 * person wrote, and deleting the wrong one must be recoverable. The table has no UNIQUE key, so
 * the trashed row it leaves behind blocks nothing.
 */
final class NoteRepository extends Repository {

	protected function table(): string {
		return 'notes';
	}

	protected function entity_type(): string {
		return 'note';
	}

	/**
	 * @return string[]
	 */
	protected function fillable(): array {
		return array(
			'entity_type',
			'entity_id',
			'type',
			'body',
			'data',
			'related_type',
			'related_id',
			'super_type',
			'super_id',
			'is_pinned',
			'is_internal',
		);
	}

	/**
	 * Every entry in a record's stream, newest first — its own, plus everything filed under it as a
	 * super-parent.
	 *
	 * THE SUPER-PARENT HALF IS WHAT MAKES AN ACCOUNT'S STREAM WORTH READING. Without it an Account
	 * shows only what happened to the Account row itself, which is almost nothing: the work happens
	 * on its opportunities and its contacts. Espo solves this by filing an entry under both its
	 * parent and its super-parent, so the account's feed is one query rather than a fan-out over
	 * every related record — which is also the only version that can be paged.
	 *
	 * @param string $entity_type Entity slug.
	 * @param int    $entity_id   Record id.
	 * @param int    $limit       How many.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public function stream_for( string $entity_type, int $entity_id, int $limit = 50 ): array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, no object cache to read from.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM %i
				 WHERE deleted_at IS NULL
				 AND ( ( entity_type = %s AND entity_id = %d ) OR ( super_type = %s AND super_id = %d ) )
				 ORDER BY is_pinned DESC, created_at DESC, id DESC
				 LIMIT %d',
				$this->table_name(),
				$entity_type,
				$entity_id,
				$entity_type,
				$entity_id,
				max( 1, min( 200, $limit ) )
			),
			ARRAY_A
		);

		return array_map( array( $this, 'cast' ), (array) $rows );
	}

	/**
	 * Every note on a record, newest first.
	 *
	 * @param string $entity_type Entity slug.
	 * @param int    $entity_id   Record id.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public function for_record( string $entity_type, int $entity_id, bool $trashed = false ): array {
		global $wpdb;

		$table = $this->table_name();

		// Not interpolated from anything a caller sends: two literals, chosen by a bool. The trash
		// view is the exact complement of the live one, so it is one query with one flipped clause
		// rather than two queries that could drift apart.
		// Two shapes, two literal statements — see Repository::find(). This used to interpolate a
		// $scope fragment, which was safe (two hard-coded literals chosen by a bool) but not
		// STATICALLY safe: an analyser cannot tell that from a variable in a query.
		// POSTS ONLY, and this is the line that keeps "notes" meaning what it always meant.
		//
		// This table became the whole stream: it now also holds the entries the CRM writes itself —
		// created, assigned, status changed. Those belong in the FEED, not in the notes panel, and
		// without this clause every consumer of "the notes on this record" silently started
		// receiving system entries with an empty body. `Stream\StreamService::for_record()` is the
		// reader that wants everything; this one wants what a person wrote.
		if ( $trashed ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, no object cache to read from.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM %i
					 WHERE entity_type = %s AND entity_id = %d AND type = 'post' AND deleted_at IS NOT NULL
					 ORDER BY created_at DESC, id DESC",
					$table,
					$entity_type,
					$entity_id
				),
				ARRAY_A
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, no object cache to read from.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM %i
					 WHERE entity_type = %s AND entity_id = %d AND type = 'post' AND deleted_at IS NULL
					 ORDER BY created_at DESC, id DESC",
					$table,
					$entity_type,
					$entity_id
				),
				ARRAY_A
			);
		}

		return array_map( array( $this, 'cast' ), (array) $rows );
	}

	/**
	 * The newest stream entries across every record, before anything is authorised.
	 *
	 * **NOTHING MAY HAND THIS STRAIGHT TO A USER.** Every other read of this table names one record,
	 * and that is not a stylistic habit — it is the only reason the stream is safe. `StreamController`
	 * loads the parent record and runs a full read check against it, so a stream can never become a
	 * way to read a record you cannot open, one entry at a time. This method has no parent to check,
	 * so it returns raw rows and the caller MUST filter them per row against each entry's own
	 * `entity_type`/`entity_id`. `StreamService::recent()` is that caller and the only one.
	 *
	 * Nor can the query be scoped by the usual seam: this table is read through `$wpdb` rather than
	 * QueryBuilder, so `plugixa_crm_query_clauses` never fires for it, and `note` is not a registered
	 * entity anyway — there is no policy for AccessPolicy to apply.
	 *
	 * It over-fetches on purpose. Filtering removes rows, so asking for exactly what the widget shows
	 * would return fewer than that whenever anything is filtered out.
	 *
	 * @param int $limit How many raw rows to consider.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public function recent( int $limit = 60 ): array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, no object cache to read from.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM %i
				 WHERE deleted_at IS NULL
				 ORDER BY created_at DESC, id DESC
				 LIMIT %d',
				$this->table_name(),
				max( 1, min( 200, $limit ) )
			),
			ARRAY_A
		);

		return array_map( array( $this, 'cast' ), (array) $rows );
	}

	/**
	 * Destroy every note on a record. The cascade when the record is purged.
	 *
	 * A purge is already the irreversible one, so the notes go with it — there is nowhere left to
	 * restore them TO.
	 *
	 * @param string $entity_type Entity slug.
	 * @param int    $entity_id   Record id.
	 */
	public function purge_for_record( string $entity_type, int $entity_id ): void {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, no object cache to read from.
		$wpdb->delete(
			$this->table_name(),
			array(
				'entity_type' => $entity_type,
				'entity_id'   => $entity_id,
			),
			array( '%s', '%d' )
		);
	}

	/**
	 * $wpdb returns every column as a string, and "0" is truthy in JavaScript.
	 *
	 * @param array<string, mixed> $row Raw row.
	 *
	 * @return array<string, mixed>
	 */
	public function cast( array $row ): array {
		$row['id']         = (int) $row['id'];
		$row['entity_id']  = (int) $row['entity_id'];
		$row['created_by'] = null === $row['created_by'] ? null : (int) $row['created_by'];

		$row['related_id'] = (int) ( $row['related_id'] ?? 0 );
		$row['super_id']   = (int) ( $row['super_id'] ?? 0 );
		$row['is_pinned']   = (bool) ( $row['is_pinned'] ?? false );
		$row['is_internal'] = (bool) ( $row['is_internal'] ?? true );
		$row['type']       = (string) ( $row['type'] ?? 'post' );

		// `data` is stored as JSON and every consumer wants the array. Decoding it here rather than
		// in the client keeps the shape of a stream entry the same on both sides of the wire.
		$decoded      = is_string( $row['data'] ?? null ) && '' !== $row['data'] ? json_decode( (string) $row['data'], true ) : null;
		$row['data']  = is_array( $decoded ) ? $decoded : null;

		return $row;
	}
}