<?php

namespace Yuvayana\AcadlixImporter\Helper;

use Illuminate\Database\Capsule\Manager;

if (!class_exists('Helper')) {
  class Helper
  {
    protected static $_instance = null;

    public function acadlix_importer_options()
    {
      $options = [
        'acadlix_importer_category_show' => 'no',
        'acadlix_importer_category_completed' => 'no',
        'acadlix_importer_category_completed_size' => 0,
        'acadlix_importer_master_show' => 'no',
        'acadlix_importer_master_completed' => 'no',
        'acadlix_importer_master_completed_size' => 0,
        'acadlix_importer_question_show' => 'no',
        'acadlix_importer_question_completed' => 'no',
        'acadlix_importer_question_completed_size' => 0,
        'acadlix_importer_prerequisite_show' => 'no',
        'acadlix_importer_prerequisite_completed' => 'no',
        'acadlix_importer_prerequisite_completed_size' => 0,
        'acadlix_importer_statistic_show' => 'no',
        'acadlix_importer_statistic_completed' => 'no',
        'acadlix_importer_statistic_completed_size' => 0,
        'acadlix_importer_statistic_ref_show' => 'no',
        'acadlix_importer_statistic_ref_completed' => 'no',
        'acadlix_importer_statistic_ref_completed_size' => 0,
        'acadlix_importer_toplist_show' => 'no',
        'acadlix_importer_toplist_completed' => 'no',
        'acadlix_importer_toplist_completed_size' => 0,
        'acadlix_importer_post_show' => "no",
        'acadlix_importer_post_completed' => "no",
        'acadlix_importer_post_completed_size' => 0
      ];
      return $options;
    }

    public function acadlix_importer_get_all_options()
    {
      $options = $this->acadlix_importer_options();
      $new_options = [];
      if (count($options) > 0) {
        foreach ($options as $key => $option) {
          $new_options[$key] = get_option($key, $option);
        }
      }
      return $new_options;
    }

    public function acadlix_importer_update_option($key = '', $value = '')
    {
      if (!empty($key)) {
        update_option($key, $value);
      }
    }

    public function acadlix_importer_update_fk($table, $oldConstraint, $column, $referencedTable, $newConstraint)
    {
      global $wpdb;
      // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
      $exists = $wpdb->get_var(
        $wpdb->prepare(
          "SELECT CONSTRAINT_NAME 
                     FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
                     WHERE TABLE_SCHEMA = %s 
                     AND TABLE_NAME = %s 
                     AND CONSTRAINT_NAME = %s",
          DB_NAME,
          $table,
          $oldConstraint
        )
      );
      if ($exists) {
        try {
          Manager::schema()->table($table, function ($table) use ($oldConstraint) {
            $table->dropForeign($oldConstraint);
          });
        } catch (\Exception $e) {
          // Fail silently or log if needed
        }
      }

      // Step 2: Check if new constraint already exists
      // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
      $newExists = $wpdb->get_var(
        $wpdb->prepare(
          "SELECT CONSTRAINT_NAME 
                    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
                    WHERE TABLE_SCHEMA = %s 
                    AND TABLE_NAME = %s 
                    AND CONSTRAINT_NAME = %s",
          DB_NAME,
          $table,
          $newConstraint
        )
      );
      if (!$newExists) {
        Manager::schema()->table($table, function ($table) use ($column, $referencedTable, $newConstraint) {
          $table->foreign($column, $newConstraint)
            ->references('id')
            ->on($referencedTable)
            ->onDelete('cascade');
        });
      }
    }

    public function acadlix_importer_update_index($table, $oldIndex, $columns, $newIndex)
    {
      global $wpdb;

      // Step 1: Drop old index if it exists
      // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
      $oldExists = $wpdb->get_var(
        $wpdb->prepare(
          "SELECT INDEX_NAME
                 FROM INFORMATION_SCHEMA.STATISTICS
                 WHERE TABLE_SCHEMA = %s
                 AND TABLE_NAME = %s
                 AND INDEX_NAME = %s",
          DB_NAME,
          $table,
          $oldIndex
        )
      );

      if ($oldExists) {
        try {
          Manager::schema()->table($table, function ($table) use ($oldIndex) {
            $table->dropIndex($oldIndex); // Drop old index
          });
        } catch (\Throwable $e) {
          // Optional: log or silently ignore
        }
      }

      // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
      $newExists = $wpdb->get_var(
        $wpdb->prepare(
          "SELECT INDEX_NAME
                     FROM INFORMATION_SCHEMA.STATISTICS
                     WHERE TABLE_SCHEMA = %s
                     AND TABLE_NAME = %s
                     AND INDEX_NAME = %s",
          DB_NAME,
          $table,
          $newIndex
        )
      );

      if (!$newExists) {
        Manager::schema()->table($table, function ($table) use ($columns, $newIndex) {
          $table->index($columns, $newIndex);
        });
      }
    }

    public function is_wp_pro_quiz_active()
    {
      return defined('WPPROQUIZ_VERSION');
    }

    public function is_wp_adv_quiz_active()
    {
      return defined('WPADVQUIZ_VERSION');
    }

    public static function instance()
    {
      if (is_null(self::$_instance)) {
        self::$_instance = new self();
      }

      return self::$_instance;
    }
  }
}