<?php

/**
 * Class For Database Migration
 *
 * @category Database
 * @author   BitCode Developer <developer@bitcode.pro>
 */

namespace BitCode\BitForm\Core\Database;

use BitCode\BitForm\Core\Util\Log;

/**
 * Database Migration
 */
final class DB
{
  /**
   * Undocumented function
   *
   * @return void
   */

  /**
   * Idempotently add the email-template status/config columns.
   *
   * @return void
   */
  public static function ensureEmailTemplateStatusColumn()
  {
    global $wpdb;
    $emailTable = $wpdb->prefix . 'bitforms_email_template';
    if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$emailTable}` LIKE 'status'")) {
      // Schema migration: table name interpolation only (no user input). $wpdb->prepare() cannot parameterize DDL.
      $wpdb->query(
        "ALTER TABLE `{$emailTable}`
            ADD COLUMN `status` TINYINT(1) DEFAULT 1 AFTER `body`,
            ADD COLUMN `config` LONGTEXT NULL AFTER `status`"
      );
    }
  }

  /**
   * Idempotently add the workflows `workflow_info` column.
   *
   * @return void
   */
  public static function ensureWorkflowInfoColumn()
  {
    global $wpdb;
    $table = $wpdb->prefix . 'bitforms_workflows';
    if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$table}` LIKE 'workflow_info'")) {
      // Schema migration: table name interpolation only (no user input). $wpdb->prepare() cannot parameterize DDL.
      $wpdb->query(
        "ALTER TABLE `{$table}`
            ADD COLUMN `workflow_info` LONGTEXT NULL AFTER `workflow_condition`"
      );
    }
  }

  /**
   * Idempotently add the workflows `workflow_category` column (conditional-logic classic/basic split).
   *
   * @return void
   */
  public static function ensureWorkflowCategoryColumn()
  {
    self::ensureWorkflowInfoColumn();
    global $wpdb;
    $table = $wpdb->prefix . 'bitforms_workflows';
    if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$table}` LIKE 'workflow_category'")) {
      // Schema migration: table name interpolation only (no user input). $wpdb->prepare() cannot parameterize DDL.
      $wpdb->query(
        "ALTER TABLE `{$table}`
            ADD COLUMN `workflow_category` VARCHAR(20) DEFAULT 'classic' AFTER `workflow_info`,
            ADD KEY `workflow_category` (`workflow_category`)"
      );
    }
  }

  public static function migrate()
  {
    global $wpdb;
    global $bitforms_db_version;
    $collate = '';

    if ($wpdb->has_cap('collation')) {
      if (!empty($wpdb->charset)) {
        $collate .= "DEFAULT CHARACTER SET $wpdb->charset";
      }

      if (!empty($wpdb->collate)) {
        $collate .= " COLLATE $wpdb->collate";
      }
    }

    $max_index_length = 191;

    $table_schema = [
      "CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_reports` (
                `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                `category` varchar(50)  NOT NULL,
                `type` varchar(20)  NOT NULL,
                `context` varchar(20)  NOT NULL,
                `details` longtext DEFAULT NULL,
                `isDefault` tinyint(1) DEFAULT 0,/* 0 not default, 1 default */
                `user_id` bigint(20) unsigned DEFAULT NULL,
                `user_ip` int(11) unsigned DEFAULT NULL,
                `user_device` varchar(50) DEFAULT NULL,
                `created_at` datetime DEFAULT NULL,
                `updated_at` datetime DEFAULT NULL,
                PRIMARY KEY (`id`)
            ) $collate;",

      "CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_form` (
                `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                `form_name` varchar(255) DEFAULT NULL,
                `form_content` longtext DEFAULT NULL,
                `builder_helper_state` longtext DEFAULT NULL,
                `atomic_class_map` longtext DEFAULT NULL,
                `generated_script_page_ids` longtext DEFAULT NULL,
                `user_id` bigint(20) unsigned DEFAULT NULL,
                `user_ip` int(11) unsigned DEFAULT NULL,
                `views` int(11) unsigned DEFAULT 0,
                `entries` int(11) unsigned DEFAULT 0,
                `user_device` varchar(50) DEFAULT NULL,
                `status` tinyint(1) DEFAULT 1,/* 0 not published, 1 published,  2 trashed */
                `created_at` datetime DEFAULT NULL,
                `updated_at` datetime DEFAULT NULL,
                PRIMARY KEY (`id`)
            ) $collate;",

      "CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_workflows` (
                `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                `workflow_name` varchar(255) NOT NULL,
                `workflow_type` varchar(50) NOT NULL,
                `workflow_run` varchar(50) NOT NULL,
                `workflow_behaviour` varchar(25) NOT NULL,
                `workflow_condition` longtext DEFAULT NULL,
                `workflow_info` longtext DEFAULT NULL,
                `workflow_category` varchar(20) DEFAULT 'classic',/* classic = legacy, basic = field show/hide, advanced = multi-condition routing */
                `workflow_order` int(11) unsigned DEFAULT NULL,
                `workflow_status` tinyint(1) DEFAULT 1,/* 0 disable, 1 enable,  2 enable in field settings */
                `form_id` bigint(20) unsigned DEFAULT NULL,
                `user_id` bigint(20) unsigned DEFAULT NULL,
                `user_ip` int(11) unsigned DEFAULT NULL,
                `user_location` text DEFAULT NULL,
                `user_device` varchar(50) DEFAULT NULL,
                `created_at` datetime DEFAULT NULL,
                `updated_at` datetime DEFAULT NULL,
                PRIMARY KEY (`id`),
                KEY `form_id` (`form_id`),
                KEY `workflow_category` (`workflow_category`)
            ) $collate;",

      "CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_success_messages` (
                `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                `message_title` varchar(255) DEFAULT NULL,
                `message_content` longtext DEFAULT NULL,
                `message_config` longtext DEFAULT NULL,
                `form_id` bigint(20) unsigned DEFAULT NULL,
                `user_id` bigint(20) unsigned DEFAULT NULL,
                `user_ip` int(11) unsigned DEFAULT NULL,
                `created_at` datetime DEFAULT NULL,
                `updated_at` datetime DEFAULT NULL,
                PRIMARY KEY (`id`)
            ) $collate;",

      "CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_email_template` (
                `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                `title` text DEFAULT NULL,
                `sub` text DEFAULT NULL,
                `body` longtext DEFAULT NULL,
                `status` tinyint(1) DEFAULT 1,/* 0 disabled, 1 enabled */
                `config` longtext DEFAULT NULL,/* SendTo / From / From Name / CC / BCC / Reply-To / attachments JSON */
                `form_id` bigint(20) unsigned DEFAULT NULL,
                `user_id` bigint(20) unsigned DEFAULT NULL,
                `user_ip` int(11) unsigned DEFAULT NULL,
                `created_at` datetime DEFAULT NULL,
                `updated_at` datetime DEFAULT NULL,
                PRIMARY KEY (`id`)
            ) $collate;",

      "CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_integration` (
                `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                `category` varchar(50)  NOT NULL,
                `integration_name` varchar(255) DEFAULT NULL,
                `integration_type` varchar(50)  NOT NULL,
                `integration_details` longtext DEFAULT NULL,
                `form_id` bigint(20) unsigned DEFAULT NULL, /* form_id = 0 means all/app */
                `user_id` bigint(20) unsigned DEFAULT NULL,
                `user_ip` int(11) unsigned DEFAULT NULL,
                `status` tinyint(1) DEFAULT 1,/* 0 disabled, 1 published,  2 trashed */
                `created_at` datetime DEFAULT NULL,
                `updated_at` datetime DEFAULT NULL,
                PRIMARY KEY (`id`)
            ) $collate;",

      "CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_form_entries` (
                `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                `form_id` bigint(20) unsigned DEFAULT NULL,
                `user_id` bigint(20) unsigned DEFAULT NULL,
                `user_ip` varchar(255) DEFAULT NULL,
                `user_location` text DEFAULT NULL,
                `user_device` varchar(50) DEFAULT NULL,
                `referer` TEXT DEFAULT NULL,
                `status` tinyint(1) DEFAULT 0,/* 0 not viewed, 1 viewed,  2 trashed */
                `created_at` datetime DEFAULT NULL,
                `updated_at` datetime DEFAULT NULL,
                PRIMARY KEY (`id`),
                KEY `form_id` (`form_id`)
            ) $collate;",

      "CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_form_entrymeta` (
                `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                `bitforms_form_entry_id` bigint(20) unsigned DEFAULT NULL,
                `meta_key` varchar(255) DEFAULT NULL,
                `meta_value` longtext,
                PRIMARY KEY (`meta_id`),
                KEY `meta_key` (`meta_key`({$max_index_length})),
                KEY `entry_id` (`bitforms_form_entry_id`)
            ) $collate;",

      "CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_form_entry_log` (
                `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
                `user_id` int(11) NOT NULL,
                `log_type` varchar(50) DEFAULT NULL,
                `ip` int(11) DEFAULT NULL,
                `action_type` varchar(50) DEFAULT NULL,
                `form_entry_id` bigint(20) DEFAULT NULL,
                `form_id` bigint(20) DEFAULT NULL,
                `content` longtext,
                `created_at` DATETIME NOT NULL,
                PRIMARY KEY (`id`),
                KEY `form_id` (`form_id`),
                KEY `form_entry_id` (`form_entry_id`)
            ) $collate;",

      "CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_form_log_details` (
                `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
                `log_id` bigint(20) NOT NULL,
                `integration_id` bigint(20) DEFAULT NULL,
                `api_type` varchar(255) DEFAULT NULL,
                `response_type` varchar(50) DEFAULT NULL,
                `response_obj` LONGTEXT DEFAULT NULL,
                `created_at` DATETIME NOT NULL,
                PRIMARY KEY (`id`),
                KEY `log_id` (`log_id`),
                KEY `integration_id` (`integration_id`)
            ) $collate;",
      "CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_form_entry_relatedinfo` (
                `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
                `info_type` VARCHAR(50) NOT NULL,
                `info_details` LONGTEXT NULL,
                `form_id` BIGINT(20) UNSIGNED NOT NULL,
                `entry_id` BIGINT(20) UNSIGNED NOT NULL,
                `user_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
                `user_ip` INT(11) UNSIGNED NULL DEFAULT NULL,
                `status` INT(1) UNSIGNED NOT NULL DEFAULT '1',
                `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
                `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                KEY `form_id` (`form_id`),
                KEY `entry_id` (`entry_id`)
            ) $collate;"
    ];

    try {
      include_once ABSPATH . 'wp-admin/includes/upgrade.php';
      foreach ($table_schema as $table) {
        dbDelta($table);
      }
      $installed_db_version = get_option('bitforms_db_version');
      if ($installed_db_version && version_compare('1.1', $installed_db_version, '>=')) {
        // if new db version is equal or higher than 1.1
        // Schema migration: table name interpolation only (no user input). $wpdb->prepare() cannot parameterize DDL statements.
        try {
          $wpdb->query("ALTER TABLE `{$wpdb->prefix}bitforms_form_entry_log`
                MODIFY `log_type` VARCHAR(50) NULL DEFAULT NULL,
                MODIFY `created_at` DATETIME NOT NULL,
                CHANGE COLUMN `meta_key` `content` LONGTEXT NULL,
                ADD COLUMN `action_type` VARCHAR(50) NULL DEFAULT NULL,
                ADD COLUMN `content` LONGTEXT NULL AFTER `action_type`");
        } catch (\Exception $e) {
          Log::debug_log("Update failed: {$e->getMessage()} in query: ALTER TABLE `{$wpdb->prefix}bitforms_form_entry_log`...");
        }
        try {
          $wpdb->query("ALTER TABLE `{$wpdb->prefix}bitforms_form_log_details`
                MODIFY `api_type` VARCHAR(255) NULL DEFAULT NULL,
                MODIFY `response_obj` LONGTEXT NULL,
                MODIFY `created_at` DATETIME NOT NULL");
        } catch (\Exception $e) {
          Log::debug_log("Update failed: {$e->getMessage()} in query: ALTER TABLE `{$wpdb->prefix}bitforms_form_log_details`...");
        }
      }
      if ($installed_db_version && version_compare('1.3', $installed_db_version, '>=')) {
        // if new db version is equal or higher than 1.3
        $wpdb->query("ALTER TABLE `{$wpdb->prefix}bitforms_form_entries` MODIFY `user_ip` VARCHAR(255) NULL DEFAULT NULL");
      }

      if ($installed_db_version && version_compare('2.0', $installed_db_version, '>=')) {
        if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$wpdb->prefix}bitforms_success_messages` LIKE 'message_config'")) {
          $wpdb->query(
            "ALTER TABLE `{$wpdb->prefix}bitforms_success_messages`
                ADD COLUMN `message_config` LONGTEXT NULL AFTER `message_content`"
          );
        }
        if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$wpdb->prefix}bitforms_form` LIKE 'builder_helper_state'")) {
          $wpdb->query(
            "ALTER TABLE `{$wpdb->prefix}bitforms_form`
                ADD COLUMN `builder_helper_state` LONGTEXT NULL AFTER `form_content`,
                ADD COLUMN `atomic_class_map` LONGTEXT NULL AFTER `builder_helper_state`,
                ADD COLUMN `generated_script_page_ids` LONGTEXT NULL AFTER `atomic_class_map`"
          );
        }
        if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$wpdb->prefix}bitforms_workflows` LIKE 'workflow_order'")) {
          $wpdb->query(
            "ALTER TABLE `{$wpdb->prefix}bitforms_workflows`
                ADD COLUMN `workflow_order` INT(11) DEFAULT NULL AFTER `workflow_condition`"
          );
        }
      }

      if ($installed_db_version && version_compare('2.2', $installed_db_version, '>=')) {
        if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$wpdb->prefix}bitforms_form_entries` LIKE 'referer'")) {
          $wpdb->query(
            "ALTER TABLE `{$wpdb->prefix}bitforms_form_entries`
                ADD COLUMN `referer` TEXT NULL DEFAULT NULL"
          );
        }
      }

      if ($installed_db_version && version_compare('2.4', $installed_db_version, '>=')) {
        if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$wpdb->prefix}bitforms_workflows` LIKE 'workflow_status'")) {
          $wpdb->query(
            "ALTER TABLE `{$wpdb->prefix}bitforms_workflows`
                ADD COLUMN `workflow_status` TINYINT(1) DEFAULT 1 NOT NULL AFTER `workflow_order`"
          );
        }
      }

      // add workflow_info column if not exists
      if ($installed_db_version && version_compare('3.0', $installed_db_version, '>=')) {
        self::ensureWorkflowInfoColumn();
      }

      // add workflow_category column if not exists (conditional logic separation)
      // existing rows default to 'classic' so Free keeps executing them unchanged; data normalization in Fallback WorkFlow@normalizeCategory
      if ($installed_db_version && version_compare('3.2', $installed_db_version, '>=')) {
        self::ensureWorkflowCategoryColumn();
        // email template enable/disable + per-template config (SendTo/From/CC/BCC/Reply-To/attachments)
        self::ensureEmailTemplateStatusColumn();
      }

      update_option('bitforms_db_version', $bitforms_db_version, true);
    } catch (\Exception $e) {
      // Log the error to a file or the database
      Log::debug_log('Error during DB migration: ' . $e->getMessage());
    }
  }
}