<?php

namespace AC_SYNC\Includes\Classes\Sql {
  if (!defined('ABSPATH'))
    exit; // Exit if accessed directly
  class Action_Wear_Sql_Manager
  {

    const DB_VERSION = '1.2.2';

    const TABLES = [
      "actionwear_log",
      "actionwear_categories_associations",
      "actionwear_categories_product",
      "actionwear_products",
      "actionwear_brands",
      "actionwear_categories",
      "actionwear_recharges_details",
      "actionwear_recharges",
      "actionwear_images",
      "actionwear_orders",
      "actionwear_addresses",
      "actionwear_jobs",
      "actionwear_sync_state"
    ];

    public static function truncateAll($exclude = [])
    {
      global $wpdb;
      $wpdb->query("SET FOREIGN_KEY_CHECKS = 0;");
      foreach (self::TABLES as $table) {
        if (!in_array($table, $exclude))
          $wpdb->query("TRUNCATE $wpdb->prefix$table");
      }
      $wpdb->query("SET FOREIGN_KEY_CHECKS = 1;");
    }

    public static function addPrefix($table)
    {
      global $wpdb;
      return $wpdb->prefix . $table;
    }

    public static function install()
    {
      global $wpdb;
      $current_db_version = get_option("_ACTIONWEAR_DB_VERSION");

      foreach (self::TABLES as $table) {
        $$table = self::addPrefix($table);
      }

      $wp_terms = $wpdb->prefix . "terms";
      $charset_collate = $wpdb->get_charset_collate();

      $sql = <<<SQL
        CREATE TABLE {$actionwear_products} (
          `id` INT NOT NULL AUTO_INCREMENT,
          `sku` VARCHAR(64) NOT NULL,
          `cover` VARCHAR(255) NULL DEFAULT NULL,
          `name` VARCHAR(255) NULL DEFAULT NULL,
          `brand_name` VARCHAR(255) NULL DEFAULT NULL,
          `wc_created` BOOLEAN NOT NULL DEFAULT FALSE,
          `wc_to_create` BOOLEAN NOT NULL DEFAULT FALSE,
          `is_configurable` BOOLEAN NOT NULL,
          `has_new` TINYINT(1) NOT NULL DEFAULT 0,
          `has_new_color` TINYINT(1) NOT NULL DEFAULT 0,
          `last_update` DATETIME NULL DEFAULT NULL,
          `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB {$charset_collate};
        CREATE TABLE {$actionwear_log} (
          `id` INT NOT NULL AUTO_INCREMENT,
          `message` TEXT NULL DEFAULT NULL,
          `gravity` TINYINT NOT NULL,
          `context` VARCHAR(255) NULL DEFAULT NULL,
          `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB {$charset_collate};
        CREATE TABLE {$actionwear_categories} (
          `id` INT NOT NULL AUTO_INCREMENT,
          `id_actionwear` VARCHAR(255) NOT NULL,
          `parent_id_actionwear` VARCHAR(255) NOT NULL,
          `name` VARCHAR(255) NOT NULL,
          `is_active` BOOLEAN NOT NULL,
          `product_count` INT NOT NULL,
          `position` INT NOT NULL,
          `level` INT NOT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB {$charset_collate};
        CREATE TABLE {$actionwear_brands} (
          `id` INT NOT NULL AUTO_INCREMENT,
          `id_actionwear` INT NOT NULL,
          `name` VARCHAR(255) NOT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB {$charset_collate};
        CREATE TABLE {$actionwear_categories_product} (
          `id` INT NOT NULL AUTO_INCREMENT,
          `id_product` INT NOT NULL,
          `id_category` VARCHAR(255) NOT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB {$charset_collate};
        CREATE TABLE {$actionwear_images} (
          `id` INT NOT NULL AUTO_INCREMENT,
          `sku` VARCHAR(255) NOT NULL,
          `images` TEXT NULL DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB {$charset_collate};
        CREATE TABLE {$actionwear_recharges} (
          `id` int NOT NULL AUTO_INCREMENT,
          `recharge_type` enum('global','brand','category','') NOT NULL DEFAULT 'global',
          `recharge_entity_id` int NOT NULL,
          `name` varchar(255) DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB {$charset_collate};
        CREATE TABLE {$actionwear_recharges_details} (
          `id` int NOT NULL AUTO_INCREMENT,
          `id_recharge` int NOT NULL,
          `price_from` float NOT NULL,
          `price_to` float NOT NULL,
          `quantity_from` int NOT NULL,
          `quantity_to` int NOT NULL,
          `percent` int NOT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB {$charset_collate};
        CREATE TABLE {$actionwear_categories_associations} (
          `id` INT NOT NULL AUTO_INCREMENT,
          `camac_category_id` VARCHAR(255) NOT NULL,
          `wc_category_id` INT NULL DEFAULT NULL,
          `ignore_me` BOOLEAN NOT NULL DEFAULT TRUE,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB {$charset_collate};
        CREATE TABLE {$actionwear_orders} (
          `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
          `wc_order_id` BIGINT UNSIGNED NOT NULL,
          `remote_order_id` VARCHAR(64) NULL DEFAULT NULL,
          `sync_status` ENUM('pending','synced','failed','cancelled') NOT NULL DEFAULT 'pending',
          `sync_payload` LONGTEXT NULL DEFAULT NULL,
          `sync_response` LONGTEXT NULL DEFAULT NULL,
          `sync_error` TEXT NULL DEFAULT NULL,
          `destination_address_id` BIGINT UNSIGNED NULL DEFAULT NULL,
          `synced_at` DATETIME NULL DEFAULT NULL,
          `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (`id`),
          INDEX `idx_wc_order` (`wc_order_id`),
          INDEX `idx_sync_status` (`sync_status`)
        ) ENGINE=InnoDB {$charset_collate};
        CREATE TABLE {$actionwear_addresses} (
          `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
          `label` VARCHAR(255) NOT NULL,
          `ragione_sociale` VARCHAR(255) NOT NULL,
          `indirizzo` VARCHAR(255) NOT NULL,
          `localita` VARCHAR(255) NOT NULL,
          `cap` VARCHAR(10) NOT NULL,
          `provincia` VARCHAR(10) NOT NULL,
          `telefono` VARCHAR(50) DEFAULT '',
          `nazione` VARCHAR(5) DEFAULT 'IT',
          `fermo_deposito` VARCHAR(255) DEFAULT '',
          `codice_destinazione` VARCHAR(64) NULL DEFAULT NULL,
          `type` ENUM('merchant','customer') DEFAULT 'merchant',
          `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
          `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB {$charset_collate};
        CREATE TABLE {$actionwear_jobs} (
          `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
          `job_type` VARCHAR(64) NOT NULL,
          `payload` LONGTEXT NULL,
          `priority` TINYINT NOT NULL DEFAULT 50,
          `conflict_group` VARCHAR(64) NULL,
          `status` ENUM('pending','claimed','completed','failed','cancelled') NOT NULL DEFAULT 'pending',
          `attempts` TINYINT UNSIGNED NOT NULL DEFAULT 0,
          `max_attempts` TINYINT UNSIGNED NOT NULL DEFAULT 3,
          `available_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          `claimed_at` DATETIME NULL,
          `claimed_by` VARCHAR(128) NULL,
          `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (`id`),
          INDEX `idx_runner` (`status`, `available_at`, `priority`, `id`),
          INDEX `idx_conflict` (`conflict_group`, `status`),
          INDEX `idx_type_status` (`job_type`, `status`),
          INDEX `idx_claimed_by` (`claimed_by`, `status`)
        ) ENGINE=InnoDB {$charset_collate};
        CREATE TABLE {$actionwear_sync_state} (
          `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
          `process_key` VARCHAR(64) NOT NULL,
          `state_key` VARCHAR(64) NOT NULL,
          `state_value` LONGTEXT NULL,
          `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (`id`),
          UNIQUE KEY `idx_process_state` (`process_key`, `state_key`)
        ) ENGINE=InnoDB {$charset_collate};
        ALTER TABLE {$actionwear_products} ADD UNIQUE(`sku`);
        ALTER TABLE {$actionwear_brands} ADD UNIQUE(`id_actionwear`);
        ALTER TABLE {$actionwear_categories} ADD INDEX(`id_actionwear`);
        ALTER TABLE {$actionwear_categories} ADD UNIQUE(`id_actionwear`);
SQL;

      require_once ABSPATH . 'wp-admin/includes/upgrade.php';
      dbDelta($sql);
      $current_version = get_option("_ACTIONWEAR_DB_VERSION");
      if (!$current_version)
        update_option("_ACTIONWEAR_DB_VERSION", self::DB_VERSION);

      $queries = [
        "ALTER TABLE {$wpdb->prefix}actionwear_products ADD UNIQUE(`sku`)",
        "ALTER TABLE {$wpdb->prefix}actionwear_products ADD INDEX `idx_wc_status` (`wc_to_create`, `wc_created`)",
        "ALTER TABLE {$wpdb->prefix}actionwear_products ADD INDEX `idx_products_brand` (`brand_name`)",
        "ALTER TABLE {$wpdb->prefix}actionwear_brands ADD UNIQUE(`id_actionwear`)",
        "ALTER TABLE {$wpdb->prefix}actionwear_categories ADD INDEX(`id_actionwear`)",
        "ALTER TABLE {$wpdb->prefix}actionwear_categories ADD UNIQUE(`id_actionwear`)",
        "ALTER TABLE {$wpdb->prefix}actionwear_categories_product ADD INDEX `idx_id_product` (`id_product`)",
        "ALTER TABLE {$wpdb->prefix}actionwear_categories_product ADD INDEX `idx_id_category` (`id_category`)",
        "ALTER TABLE {$wpdb->prefix}actionwear_images ADD INDEX `idx_sku` (`sku`)",
        "ALTER TABLE {$wpdb->prefix}actionwear_images ADD UNIQUE INDEX `idx_images_sku` (`sku`)",
      ];

      // force add of unique keys
      foreach ($queries as $query) {
        $wpdb->query($query);
      }

    }
  }
}
