<?php
/**
 * Database schema management.
 *
 * @package SubmissionInbox
 */

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Activation/uninstall routines intentionally manage this plugin's custom database tables.
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names come from plugin-owned $wpdb->prefix values returned by get_table_names().

/**
 * Creates and removes plugin tables.
 */
class SUBIN_Database {
	const OPTION_KEY = 'subin_db_version';
	const VERSION    = '1.4.0';

	/**
	 * Get plugin table names.
	 *
	 * @return array<string,string>
	 */
	public static function get_table_names() {
		global $wpdb;

		return array(
			'forms'        => $wpdb->prefix . 'subin_forms',
			'form_fields'  => $wpdb->prefix . 'subin_form_fields',
			'leads'        => $wpdb->prefix . 'subin_leads',
			'lead_fields'  => $wpdb->prefix . 'subin_lead_fields',
			'mail_events'  => $wpdb->prefix . 'subin_mail_events',
			'auto_reply_templates' => $wpdb->prefix . 'subin_auto_reply_templates',
			'auto_reply_rules' => $wpdb->prefix . 'subin_auto_reply_rules',
		);
	}

	/**
	 * Create database tables.
	 *
	 * @return void
	 */
	public static function create_tables() {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$tables          = self::get_table_names();
		$charset_collate = $wpdb->get_charset_collate();

		$sql = array();

		$sql[] = "CREATE TABLE {$tables['forms']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			title VARCHAR(255) NOT NULL DEFAULT '',
			slug VARCHAR(191) NOT NULL DEFAULT '',
			status VARCHAR(30) NOT NULL DEFAULT 'active',
			template_key VARCHAR(100) NOT NULL DEFAULT '',
			description TEXT NULL,
			layout_template VARCHAR(100) NOT NULL DEFAULT 'clean_card',
			style_preset VARCHAR(100) NOT NULL DEFAULT 'default',
			header_title VARCHAR(255) NULL,
			header_description TEXT NULL,
			footer_note TEXT NULL,
			privacy_text TEXT NULL,
			success_message TEXT NULL,
			submit_button_text VARCHAR(100) NOT NULL DEFAULT 'Send',
			notification_email VARCHAR(255) NULL,
			notification_subject TEXT NULL,
			notification_enabled TINYINT(1) NOT NULL DEFAULT 1,
			save_leads TINYINT(1) NOT NULL DEFAULT 1,
			settings_json LONGTEXT NULL,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY slug (slug),
			KEY status (status),
			KEY template_key (template_key),
			KEY created_at (created_at)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['form_fields']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			form_id BIGINT UNSIGNED NOT NULL,
			field_type VARCHAR(50) NOT NULL DEFAULT 'text',
			field_key VARCHAR(191) NOT NULL,
			field_label VARCHAR(255) NOT NULL DEFAULT '',
			placeholder VARCHAR(255) NULL,
			help_text TEXT NULL,
			is_required TINYINT(1) NOT NULL DEFAULT 0,
			field_options LONGTEXT NULL,
			sort_order INT UNSIGNED NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY form_id (form_id),
			KEY field_type (field_type),
			KEY field_key (field_key),
			KEY sort_order (sort_order)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['leads']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			provider VARCHAR(50) NOT NULL DEFAULT '',
			form_id VARCHAR(100) NOT NULL DEFAULT '',
			form_name VARCHAR(255) NOT NULL DEFAULT '',
			source_url TEXT NULL,
			referrer TEXT NULL,
			customer_name VARCHAR(255) NULL,
			customer_email VARCHAR(255) NULL,
			customer_phone VARCHAR(100) NULL,
			message_preview TEXT NULL,
			status VARCHAR(30) NOT NULL DEFAULT 'new',
			email_status VARCHAR(30) NOT NULL DEFAULT 'unknown',
			submitted_at DATETIME NULL,
			expires_at DATETIME NULL,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY status (status),
			KEY email_status (email_status),
			KEY provider (provider),
			KEY submitted_at (submitted_at),
			KEY customer_email (customer_email)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['lead_fields']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			lead_id BIGINT UNSIGNED NOT NULL,
			field_key VARCHAR(191) NOT NULL,
			field_label VARCHAR(255) NULL,
			field_value LONGTEXT NULL,
			is_sensitive TINYINT(1) NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY lead_id (lead_id),
			KEY field_key (field_key),
			KEY is_sensitive (is_sensitive)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['mail_events']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			lead_id BIGINT UNSIGNED NULL,
			source VARCHAR(100) NOT NULL DEFAULT 'unknown',
			to_email TEXT NULL,
			from_email VARCHAR(255) NULL,
			subject TEXT NULL,
			message_hash VARCHAR(64) NULL,
			status VARCHAR(30) NOT NULL DEFAULT 'pending',
			error_message TEXT NULL,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY lead_id (lead_id),
			KEY status (status),
			KEY source (source),
			KEY created_at (created_at)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['auto_reply_templates']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			template_key VARCHAR(100) NOT NULL DEFAULT '',
			name VARCHAR(255) NOT NULL DEFAULT '',
			status VARCHAR(30) NOT NULL DEFAULT 'draft',
			subject TEXT NOT NULL,
			body LONGTEXT NULL,
			preheader TEXT NULL,
			project_json LONGTEXT NULL,
			rendered_html LONGTEXT NULL,
			rendered_css LONGTEXT NULL,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY template_key (template_key),
			KEY status (status),
			KEY created_at (created_at),
			KEY updated_at (updated_at)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['auto_reply_rules']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			name VARCHAR(190) NOT NULL,
			provider VARCHAR(64) NOT NULL,
			form_id VARCHAR(190) NOT NULL,
			form_name VARCHAR(255) NULL,
			visitor_email_field VARCHAR(190) NOT NULL,
			visitor_email_field_label VARCHAR(255) NULL,
			template_id BIGINT UNSIGNED NOT NULL,
			enabled TINYINT(1) NOT NULL DEFAULT 1,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY provider (provider),
			KEY form_id (form_id),
			KEY template_id (template_id),
			KEY enabled (enabled)
		) {$charset_collate};";

		foreach ( $sql as $statement ) {
			dbDelta( $statement );
		}
	}

	/**
	 * Create or update tables and store schema version.
	 *
	 * @return void
	 */
	public static function install_or_upgrade() {
		self::create_tables();
		self::migrate_auto_reply_rules();
		update_option( self::OPTION_KEY, self::VERSION, false );
	}

	/**
	 * Migrate legacy built-in form auto-reply settings into rule rows.
	 *
	 * @return void
	 */
	private static function migrate_auto_reply_rules() {
		global $wpdb;

		$tables = self::get_table_names();
		if ( empty( $tables['forms'] ) || empty( $tables['form_fields'] ) || empty( $tables['auto_reply_rules'] ) ) {
			return;
		}

		$forms = $wpdb->get_results( "SELECT id, title, settings_json FROM {$tables['forms']} WHERE settings_json IS NOT NULL AND settings_json != ''", ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		if ( ! is_array( $forms ) ) {
			return;
		}

		foreach ( $forms as $form ) {
			$form_id  = absint( $form['id'] ?? 0 );
			$settings = json_decode( (string) ( $form['settings_json'] ?? '' ), true );
			if ( $form_id <= 0 || ! is_array( $settings ) || ! is_array( $settings['auto_reply'] ?? null ) ) {
				continue;
			}

			$config      = $settings['auto_reply'];
			$template_id = absint( $config['template_id'] ?? 0 );
			$field_key   = sanitize_key(
				(string) (
					$config['recipient_field_key']
					?? $config['email_field_key']
					?? $config['auto_reply_email_field_key']
					?? ''
				)
			);

			if ( empty( $config['enabled'] ) || $template_id <= 0 || '' === $field_key ) {
				continue;
			}

			$existing = absint(
				$wpdb->get_var(
					$wpdb->prepare(
						"SELECT id FROM {$tables['auto_reply_rules']} WHERE provider = %s AND form_id = %s LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
						'submission_inbox_form',
						(string) $form_id
					)
				)
			);
			if ( $existing > 0 ) {
				continue;
			}

			$field = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT field_label FROM {$tables['form_fields']} WHERE form_id = %d AND field_type = %s AND field_key = %s LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$form_id,
					'email',
					$field_key
				),
				ARRAY_A
			);
			if ( ! is_array( $field ) ) {
				continue;
			}

			$now  = current_time( 'mysql' );
			$name = sprintf(
				/* translators: %s: form title. */
				__( '%s auto-reply', 'submission-inbox' ),
				sanitize_text_field( (string) ( $form['title'] ?? __( 'Form', 'submission-inbox' ) ) )
			);

			$wpdb->insert(
				$tables['auto_reply_rules'],
				array(
					'name'                      => $name,
					'provider'                  => 'submission_inbox_form',
					'form_id'                   => (string) $form_id,
					'form_name'                 => sanitize_text_field( (string) ( $form['title'] ?? '' ) ),
					'visitor_email_field'       => $field_key,
					'visitor_email_field_label' => sanitize_text_field( (string) ( $field['field_label'] ?? $field_key ) ),
					'template_id'               => $template_id,
					'enabled'                   => 1,
					'created_at'                => $now,
					'updated_at'                => $now,
				),
				array( '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%s' )
			);
		}
	}

	/**
	 * Run a lightweight schema version check.
	 *
	 * @return void
	 */
	public static function maybe_upgrade() {
		$stored_version = (string) get_option( self::OPTION_KEY, '' );

		if ( '' === $stored_version || version_compare( $stored_version, self::VERSION, '<' ) ) {
			self::install_or_upgrade();
		}
	}

	/**
	 * Drop plugin tables.
	 *
	 * @return void
	 */
	public static function drop_tables() {
		global $wpdb;

		$tables = self::get_table_names();

		foreach ( $tables as $table ) {
			$wpdb->query( "DROP TABLE IF EXISTS {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}
	}
}
