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

declare( strict_types = 1 );

namespace PlugixaCrm\Modules\Integrations;

use PlugixaCrm\Core\Database\Schema as Db;

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

/**
 * The two tables every connector shares.
 *
 * `integration_links` answers "which record over there is this record over here?", and
 * `integration_jobs` is the one queue all connectors put deferred work on. Neither belongs to any
 * particular connector, which is why they are here in the shared framework rather than in the first
 * connector that needed them.
 *
 * BOTH NAMES ARE PREFIXED, AND THAT IS LOAD-BEARING. `Core\Database\LegacyCleanup` hard-drops bare
 * table names left behind by the withdrawn ERP packs — `products`, `invoices`, `subscriptions` and
 * some forty more — once per version bump. It refuses to drop a name an installed module claims, but
 * only finds the claim when the module's schema class is literally `Schema`, sits beside `Module.php`
 * and exposes a static `tables()`. All three hold here; the prefix means the question never has to
 * be asked. `docs/INTEGRATIONS-ROADMAP.md` §6 item 1 is the long version.
 *
 * 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( 'integration_links', 'integration_jobs' );
	}

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

		$changes = Db::delta( self::links( $charset ), self::jobs( $charset ) );

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

		return $changes;
	}

	/**
	 * "Their id 4711 is our contact 12."
	 *
	 * NO UNIQUE KEY, AND THIS IS THE DECISION THE ROADMAP SAYS TO MAKE BEFORE SHIPPING (§6 item 2).
	 * The obvious index — UNIQUE (integration, external_type, external_id) — is exactly the shape
	 * that breaks against a soft delete: `Repository::delete()` leaves the row in place with
	 * `deleted_at` set, the index goes on holding the key, and re-linking the same external record
	 * fails the INSERT against a row the application believes is gone. `$wpdb->insert` returns false,
	 * `insert()` returns 0, and NOTHING IS REPORTED. That link would then refuse to be created, for
	 * that one external record, for ever.
	 *
	 * Uniqueness is enforced among LIVE rows by {@see Repositories\LinkRepository::link()} instead —
	 * the same choice `products.sku` and `tags.slug` make. A trashed link releases its key, which is
	 * also what a human expects: unlinking and relinking should work.
	 *
	 * `external_id` is a VARCHAR because other people's identifiers are not integers. Stripe uses
	 * `cus_…`, Google uses opaque base64, and a numeric column would silently truncate the first two
	 * and cast the third to 0 — which collides every unmapped record onto one row.
	 *
	 * @param string $charset Charset/collate clause.
	 */
	private static function links( string $charset ): string {
		$table = Db::table( 'integration_links' );

		return "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL auto_increment,
			integration varchar(64) NOT NULL default '',
			external_type varchar(64) NOT NULL default '',
			external_id varchar(191) NOT NULL default '',
			entity_type varchar(64) NOT NULL default '',
			entity_id bigint(20) unsigned NOT NULL default 0,
			synced_at datetime 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 external (integration,external_type,external_id),
			KEY record (entity_type,entity_id),
			KEY integration (integration),
			KEY deleted_at (deleted_at)
		) {$charset};";
	}

	/**
	 * One piece of deferred work.
	 *
	 * THE QUEUE IS SHARED BECAUSE FOUR MODULES ALREADY PROVE IT SHOULD BE. Webhooks and Campaigns
	 * each hand-rolled their own table with their own attempt counter and their own backoff, and
	 * four separate modules independently register the same `plugixa_crm_five_minutes` schedule. A
	 * fifth and sixth copy, one per connector, is the version of that which nobody could maintain.
	 *
	 * `handler` is a string a connector registers a callback for, not a callable stored in the row.
	 * Serialising a callable into the database means `unserialize()` on data from a table, which is
	 * an object-injection surface, and it means a queued job breaks when the class it named is
	 * renamed. A short string that resolves through a filter at run time breaks visibly instead.
	 *
	 * `dedupe_key` is what stops a queue growing without bound when the thing producing it loops:
	 * an already-pending job with the same key is not queued twice. See
	 * {@see Repositories\JobRepository::enqueue()}.
	 *
	 * @param string $charset Charset/collate clause.
	 */
	private static function jobs( string $charset ): string {
		$table = Db::table( 'integration_jobs' );

		return "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL auto_increment,
			integration varchar(64) NOT NULL default '',
			handler varchar(128) NOT NULL default '',
			payload longtext,
			dedupe_key varchar(191) NOT NULL default '',
			status varchar(20) NOT NULL default 'pending',
			attempts tinyint(3) unsigned NOT NULL default 0,
			last_error varchar(255) NOT NULL default '',
			next_attempt_at datetime default NULL,
			finished_at datetime 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 integration (integration),
			KEY status (status),
			KEY due (status,next_attempt_at),
			KEY dedupe (status,dedupe_key),
			KEY deleted_at (deleted_at)
		) {$charset};";
	}
}