<?php

/**
 * Database schema installer.
 *
 * @package AfroIT_Messaging_WorkflowAutomations
 */

namespace AfroIT\Messaging\WorkflowAutomations\Infrastructure;


use AfroIT\Messaging\WorkflowAutomations\Async\QueueTable;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Class DatabaseSchema
 *
 * Creates tables for workflows, queues, and abandoned carts.
 */
final class DatabaseSchema {

	private const VERSION_OPTION = 'afromewo_db_version';
	private const CURRENT_VERSION = 1;

	/**
	 * Register activation hook.
	 *
	 * @return void
	 */
	public function register(): void {
		$this->maybeUpgrade();
	}

	/**
	 * Install or align schema when not yet at the current version.
	 *
	 * @return void
	 */
	public function maybeUpgrade(): void {
		if ( (int) get_option( self::VERSION_OPTION, 0 ) >= self::CURRENT_VERSION ) {
			return;
		}

		$this->install();
		update_option( self::VERSION_OPTION, self::CURRENT_VERSION, true );
	}

	/**
	 * Install all tables (dbDelta creates/updates to match definitions).
	 *
	 * @return void
	 */
	public function install(): void {
		global $wpdb;

		$charset_collate = $wpdb->get_charset_collate();
		$prefix          = $wpdb->prefix;

		$sql = $this->getAbandonedCartsTableSql( $prefix, $charset_collate )
			. $this->getWorkflowsTableSql( $prefix, $charset_collate )
			. $this->getEventQueueTableSql( $prefix, $charset_collate )
			. $this->getWorkflowQueueTableSql( $prefix, $charset_collate );

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );
	}

	/**
	 * Get workflow queue table SQL.
	 *
	 * @param string $prefix          Table prefix.
	 * @param string $charset_collate Charset.
	 * @return string
	 */
	private function getWorkflowQueueTableSql( string $prefix, string $charset_collate ): string {
		$table = $prefix . 'afromewo_workflow_queue';
		return "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			remote_workflow_uuid varchar(100) DEFAULT NULL,
			remote_execution_uuid varchar(100) DEFAULT NULL,
			context_json longtext NOT NULL,
			execution_json longtext DEFAULT NULL,
			run_at datetime NOT NULL,
			status varchar(20) NOT NULL DEFAULT 'pending',
			created_at datetime NOT NULL,
			PRIMARY KEY (id),
			UNIQUE KEY uq_remote_wf_exec (remote_workflow_uuid, remote_execution_uuid),
			KEY status_run_at (status, run_at)
		) {$charset_collate};";
	}

	/**
	 * Get abandoned carts table SQL.
	 *
	 * `cart_data` holds a JSON document (stored as longtext for dbDelta / host compatibility).
	 *
	 * @param string $prefix          Table prefix.
	 * @param string $charset_collate Charset.
	 * @return string
	 */
	private function getAbandonedCartsTableSql( string $prefix, string $charset_collate ): string {
		$table = $prefix . 'afromewo_abandoned_carts';
		return "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			session_id varchar(191) NOT NULL,
			token varchar(100) NOT NULL,
			user_id bigint(20) unsigned DEFAULT NULL,
			phone varchar(50) DEFAULT NULL,
			email varchar(255) NOT NULL DEFAULT '',
			cart_data longtext NOT NULL,
			created_at datetime NOT NULL,
			last_activity datetime DEFAULT NULL,
			status varchar(20) NOT NULL DEFAULT 'pending',
			recovered tinyint(1) NOT NULL DEFAULT 0,
			PRIMARY KEY (id),
			KEY uq_afromewo_abandoned_session (session_id, recovered),
			KEY recovered_created (recovered, created_at),
            KEY status_last_activity (status, last_activity),
            KEY token_index (token)
		) {$charset_collate};";
	}

	/**
	 * Get workflows table SQL.
	 *
	 * @param string $prefix          Table prefix.
	 * @param string $charset_collate Charset.
	 * @return string
	 */
	private function getWorkflowsTableSql( string $prefix, string $charset_collate ): string {
		$table = $prefix . 'afromewo_workflows';
		return "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			name varchar(255) NOT NULL,
			`trigger` varchar(50) NOT NULL,
			conditions longtext DEFAULT NULL,
			steps longtext DEFAULT NULL,
			is_active tinyint(1) NOT NULL DEFAULT 1,
			remote_workflow_uuid varchar(100) DEFAULT '',
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY (id),
			KEY trigger_active (`trigger`, is_active)
		) {$charset_collate};";
	}

	/**
	 * Get event queue table SQL.
	 *
	 * @param string $prefix          Table prefix.
	 * @param string $charset_collate Charset.
	 * @return string
	 */
	private function getEventQueueTableSql( string $prefix, string $charset_collate ): string {
		return QueueTable::getCreateTableSql( $prefix, $charset_collate );
	}

}