<?php
/**
 * Custom tables for stored entries.
 *
 * @package TFlexForms
 */

declare( strict_types = 1 );

namespace TFlexForms;

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

/**
 * Creates and upgrades the schema with dbDelta(), and names the tables.
 */
final class Database {

	public const SCHEMA_VERSION = '4';
	public const VERSION_OPTION = 'tflexforms_db_version';

	/**
	 * Full table name.
	 *
	 * @param string $name One of entries, entry_values, entry_files, entry_notes, entry_tags, submission_keys, jobs.
	 */
	public static function table( string $name ): string {
		global $wpdb;
		return $wpdb->prefix . 'tflexforms_' . $name;
	}

	/**
	 * Creates or updates the tables when the schema version changes.
	 */
	public static function install(): void {
		if ( get_option( self::VERSION_OPTION ) === self::SCHEMA_VERSION ) {
			return;
		}
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset = $wpdb->get_charset_collate();
		$entries = self::table( 'entries' );
		$values  = self::table( 'entry_values' );
		$files   = self::table( 'entry_files' );
		$keys    = self::table( 'submission_keys' );
		$jobs    = self::table( 'jobs' );
		$notes   = self::table( 'entry_notes' );
		$tags    = self::table( 'entry_tags' );

		// dbDelta() needs this exact formatting: one column per line and two
		// spaces after PRIMARY KEY.
		dbDelta(
			"CREATE TABLE {$entries} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  form_id bigint(20) unsigned NOT NULL,
  status varchar(20) NOT NULL DEFAULT 'unread',
  mail_sent tinyint(1) NOT NULL DEFAULT 0,
  user_id bigint(20) unsigned NOT NULL DEFAULT 0,
  ip varchar(45) NOT NULL DEFAULT '',
  page_url varchar(2048) NOT NULL DEFAULT '',
  mail_message longtext NULL,
  mail_log longtext NULL,
  created_at datetime NOT NULL,
  PRIMARY KEY  (id),
  KEY form_created (form_id,created_at),
  KEY status (status)
) {$charset};
CREATE TABLE {$values} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  entry_id bigint(20) unsigned NOT NULL,
  field_name varchar(64) NOT NULL,
  field_value longtext NOT NULL,
  PRIMARY KEY  (id),
  KEY entry_id (entry_id),
  KEY field_name (field_name)
) {$charset};
CREATE TABLE {$files} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  entry_id bigint(20) unsigned NOT NULL,
  field_name varchar(64) NOT NULL,
  original_name varchar(255) NOT NULL,
  stored_name varchar(255) NOT NULL,
  mime varchar(100) NOT NULL DEFAULT '',
  size bigint(20) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY  (id),
  KEY entry_id (entry_id)
) {$charset};
CREATE TABLE {$keys} (
  key_hash char(64) NOT NULL,
  form_id bigint(20) unsigned NOT NULL,
  result text NULL,
  created_at datetime NOT NULL,
  PRIMARY KEY  (key_hash),
  KEY created_at (created_at)
) {$charset};
CREATE TABLE {$jobs} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  type varchar(64) NOT NULL,
  args longtext NOT NULL,
  status varchar(20) NOT NULL DEFAULT 'pending',
  attempts smallint(5) unsigned NOT NULL DEFAULT 0,
  max_attempts smallint(5) unsigned NOT NULL DEFAULT 1,
  run_at datetime NOT NULL,
  locked_at datetime NULL,
  last_error text NULL,
  created_at datetime NOT NULL,
  updated_at datetime NOT NULL,
  PRIMARY KEY  (id),
  KEY status_run (status,run_at),
  KEY type (type)
) {$charset};
CREATE TABLE {$notes} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  entry_id bigint(20) unsigned NOT NULL,
  user_id bigint(20) unsigned NOT NULL DEFAULT 0,
  type varchar(20) NOT NULL DEFAULT 'note',
  content longtext NOT NULL,
  created_at datetime NOT NULL,
  PRIMARY KEY  (id),
  KEY entry_id (entry_id)
) {$charset};
CREATE TABLE {$tags} (
  entry_id bigint(20) unsigned NOT NULL,
  tag varchar(50) NOT NULL,
  PRIMARY KEY  (entry_id,tag),
  KEY tag (tag)
) {$charset};"
		);

		update_option( self::VERSION_OPTION, self::SCHEMA_VERSION, false );
	}

	/**
	 * Drops all tables (uninstall only).
	 */
	public static function drop(): void {
		global $wpdb;
		foreach ( array( 'entry_tags', 'entry_notes', 'jobs', 'submission_keys', 'entry_files', 'entry_values', 'entries' ) as $name ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Uninstall cleanup of our own tables.
			$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', self::table( $name ) ) );
		}
		delete_option( self::VERSION_OPTION );
	}
}