<?php
/**
 * Database schema definition.
 *
 * @package FormPilot
 * @since   1.0.0
 */

declare( strict_types = 1 );

namespace FormPilot\Database;

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

/**
 * Central definition of every custom table used by the plugin.
 *
 * The SQL returned here is written for `dbDelta()`, which is strict about
 * formatting: one column per line, two spaces after `PRIMARY KEY`, `KEY`
 * instead of `INDEX`, no space after the comma inside a composite key, and
 * lowercase column types. A deviation makes dbDelta reissue the same ALTER on
 * every run instead of recognising the column as unchanged.
 *
 * Two limits shape the column widths:
 *
 * - A single column index is capped at 767 bytes on InnoDB's legacy COMPACT row
 *   format, which is 191 characters under utf8mb4. A *composite* index shares
 *   that budget, so `(bigint, varchar(191))` needs 772 bytes and fails there.
 *   Keys that participate in a composite index are therefore 100 characters,
 *   leaving the pair at 408 bytes and safe on every row format.
 * - Integer display widths are kept (`bigint(20)`) to match WordPress core and
 *   MariaDB, which still report them. MySQL 8.0.19+ strips them, so dbDelta
 *   sees a difference and reissues one harmless ALTER per upgrade run. That is
 *   the lesser evil: dropping the widths would cause the same churn on every
 *   MariaDB site instead, and MariaDB is the more common host.
 *
 * `dbDelta()` only ever adds and widens. It never drops a column or an index,
 * so removing either needs an explicit migration in {@see Installer}.
 *
 * @since 1.0.0
 */
final class Schema {

	/**
	 * Schema version.
	 *
	 * Bump this constant whenever a table definition changes so existing
	 * installations pick the change up through the upgrade routine.
	 */
	public const DB_VERSION = '1.2.0';

	/**
	 * Option storing the installed schema version.
	 */
	public const DB_VERSION_OPTION = 'formpilot_db_version';

	/**
	 * Forms table identifier.
	 */
	public const FORMS = 'formpilot_forms';

	/**
	 * Fields table identifier.
	 */
	public const FIELDS = 'formpilot_fields';

	/**
	 * Submissions table identifier.
	 */
	public const SUBMISSIONS = 'formpilot_submissions';

	/**
	 * Submission meta table identifier.
	 */
	public const SUBMISSION_META = 'formpilot_submission_meta';

	/**
	 * Webhook delivery log table identifier.
	 */
	public const WEBHOOK_LOG = 'formpilot_webhook_log';

	/**
	 * Returns every table identifier handled by the plugin.
	 *
	 * @return array<int, string> Unprefixed table identifiers.
	 */
	public static function tables(): array {
		return array(
			self::FORMS,
			self::FIELDS,
			self::SUBMISSIONS,
			self::SUBMISSION_META,
			self::WEBHOOK_LOG,
		);
	}

	/**
	 * Returns the prefixed table name for the current site.
	 *
	 * @param string $table Unprefixed table identifier, e.g. Schema::FORMS.
	 */
	public static function table( string $table ): string {
		global $wpdb;

		return $wpdb->prefix . $table;
	}

	/**
	 * Returns the CREATE TABLE statements for every plugin table.
	 *
	 * @return array<string, string> Statements keyed by unprefixed table identifier.
	 */
	public static function statements(): array {
		global $wpdb;

		$charset_collate = $wpdb->get_charset_collate();

		return array(
			self::FORMS           => self::forms_table( $charset_collate ),
			self::FIELDS          => self::fields_table( $charset_collate ),
			self::SUBMISSIONS     => self::submissions_table( $charset_collate ),
			self::SUBMISSION_META => self::submission_meta_table( $charset_collate ),
			self::WEBHOOK_LOG     => self::webhook_log_table( $charset_collate ),
		);
	}

	/**
	 * Builds the webhook delivery log statement.
	 *
	 * The row is both the log and the retry queue: a delivery that failed is a
	 * pending row with an attempt count and a time to try again. Keeping one table
	 * rather than a queue plus a log means a retry can never be scheduled for a
	 * delivery nobody can see, and the history of an endpoint is the same rows
	 * that are still being worked on.
	 *
	 * @param string $charset_collate Charset and collation clause.
	 */
	private static function webhook_log_table( string $charset_collate ): string {
		$table = self::table( self::WEBHOOK_LOG );

		return "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			form_id bigint(20) unsigned NOT NULL DEFAULT 0,
			submission_id bigint(20) unsigned NOT NULL DEFAULT 0,
			webhook_key varchar(100) NOT NULL DEFAULT '',
			url varchar(255) NOT NULL DEFAULT '',
			status varchar(20) NOT NULL DEFAULT 'pending',
			response_code smallint(5) unsigned NOT NULL DEFAULT 0,
			attempts smallint(5) unsigned NOT NULL DEFAULT 0,
			duration_ms int(11) unsigned NOT NULL DEFAULT 0,
			payload longtext NULL,
			response longtext NULL,
			message text NULL,
			next_attempt_at datetime DEFAULT NULL,
			created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY  (id),
			KEY status_next (status,next_attempt_at),
			KEY form_created (form_id,created_at),
			KEY submission_id (submission_id),
			KEY webhook_key (webhook_key)
		) {$charset_collate};";
	}

	/**
	 * Builds the forms table statement.
	 *
	 * @param string $charset_collate Charset and collation clause.
	 */
	private static function forms_table( string $charset_collate ): string {
		$table = self::table( self::FORMS );

		return "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			title varchar(255) NOT NULL DEFAULT '',
			slug varchar(191) NOT NULL DEFAULT '',
			status varchar(20) NOT NULL DEFAULT 'draft',
			settings longtext NULL,
			author_id bigint(20) unsigned NOT NULL DEFAULT 0,
			submission_count bigint(20) unsigned NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY  (id),
			UNIQUE KEY slug (slug),
			KEY status (status),
			KEY author_id (author_id),
			KEY created_at (created_at)
		) {$charset_collate};";
	}

	/**
	 * Builds the fields table statement.
	 *
	 * @param string $charset_collate Charset and collation clause.
	 */
	private static function fields_table( string $charset_collate ): string {
		$table = self::table( self::FIELDS );

		return "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			form_id bigint(20) unsigned NOT NULL DEFAULT 0,
			field_key varchar(100) NOT NULL DEFAULT '',
			type varchar(50) NOT NULL DEFAULT 'text',
			label text NULL,
			placeholder varchar(255) NOT NULL DEFAULT '',
			description text NULL,
			default_value longtext NULL,
			options longtext NULL,
			settings longtext NULL,
			is_required tinyint(1) unsigned NOT NULL DEFAULT 0,
			menu_order int(11) NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY  (id),
			UNIQUE KEY form_field_key (form_id,field_key),
			KEY form_order (form_id,menu_order),
			KEY type (type)
		) {$charset_collate};";
	}

	/**
	 * Builds the submissions table statement.
	 *
	 * `entry_key` is nullable on purpose: it holds an optional public token and
	 * MySQL treats NULL values as distinct inside a UNIQUE index, so rows
	 * without a token never collide.
	 *
	 * @param string $charset_collate Charset and collation clause.
	 */
	private static function submissions_table( string $charset_collate ): string {
		$table = self::table( self::SUBMISSIONS );

		return "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			form_id bigint(20) unsigned NOT NULL DEFAULT 0,
			user_id bigint(20) unsigned NOT NULL DEFAULT 0,
			status varchar(20) NOT NULL DEFAULT 'unread',
			entry_key varchar(64) DEFAULT NULL,
			ip_address varchar(100) NOT NULL DEFAULT '',
			user_agent varchar(255) NOT NULL DEFAULT '',
			referer varchar(255) NOT NULL DEFAULT '',
			post_id bigint(20) unsigned NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY  (id),
			UNIQUE KEY entry_key (entry_key),
			KEY form_created (form_id,created_at),
			KEY form_status (form_id,status),
			KEY status_created (status,created_at),
			KEY user_id (user_id),
			KEY created_at (created_at)
		) {$charset_collate};";
	}

	/**
	 * Builds the submission meta table statement.
	 *
	 * @param string $charset_collate Charset and collation clause.
	 */
	private static function submission_meta_table( string $charset_collate ): string {
		$table = self::table( self::SUBMISSION_META );

		return "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			submission_id bigint(20) unsigned NOT NULL DEFAULT 0,
			field_id bigint(20) unsigned NOT NULL DEFAULT 0,
			meta_key varchar(100) NOT NULL DEFAULT '',
			meta_value longtext NULL,
			PRIMARY KEY  (id),
			KEY submission_meta_key (submission_id,meta_key),
			KEY field_id (field_id)
		) {$charset_collate};";
	}
}