<?php

namespace ShopManagerPro\Shared;

use ShopManagerPro\Premium\DatabaseManagerService as PremiumDatabaseManagerService;

class DatabaseManagerService {
	public const DB_VERSION_OPTION = 'shopmanagerpro_db_version';
	public static string $jobsTableName = '';
	public static string $jobsProductUpdatesTableName = '';
	public static string $filtersTableName = '';

	public static function runMigrations() {
		$migratedVersion = get_option(self::DB_VERSION_OPTION);
		if (SHOP_MANAGER_PRO_VERSION === $migratedVersion) {
			return;
		} Utils\Logger::info('Running migrations', ['from_version' => $migratedVersion, 'to_version' => SHOP_MANAGER_PRO_VERSION]);
		require_once ABSPATH.'wp-admin/includes/upgrade.php';
		self::createJobsTable();
		self::createJobsProductUpdatesTable();
		self::createFiltersTable();
		if (SHOP_MANAGER_PRO_COMMERCIAL) {
			PremiumDatabaseManagerService::runMigrations();
		} update_option(self::DB_VERSION_OPTION, SHOP_MANAGER_PRO_VERSION);
	}

	private static function createJobsTable() {
		global $wpdb;
		$charset_collate = $wpdb->get_charset_collate();
		$jobStatuses = implode("', '", array_column(\ShopManagerPro\Jobs\DTO\Generated\JobStatus::cases(), 'value'));
		$jobTypes = implode("', '", array_column(\ShopManagerPro\Jobs\DTO\Generated\JobType::cases(), 'value'));
		$sql = 'CREATE TABLE '.self::$jobsTableName." (
            id bigint unsigned NOT NULL AUTO_INCREMENT ,
			user_id bigint unsigned NULL ,
			type enum('$jobTypes') NOT NULL ,
			actions json NOT NULL ,
            status enum('$jobStatuses') NOT NULL DEFAULT 'pending' ,
			totalItems int unsigned NOT NULL DEFAULT 0 ,
			processedItems int unsigned NOT NULL DEFAULT 0 ,
            created_at datetime NOT NULL ,
			finished_at datetime NULL DEFAULT NULL ,
            PRIMARY KEY  ( id ) ,
			KEY created_at (created_at)
        ) $charset_collate ;";
		dbDelta($sql);
	}

	private static function createJobsProductUpdatesTable() {
		global $wpdb;
		$charset_collate = $wpdb->get_charset_collate();
		$sql = 'CREATE TABLE '.self::$jobsProductUpdatesTableName." (
			id bigint unsigned NOT NULL AUTO_INCREMENT ,
			job_id bigint unsigned NOT NULL ,
			post_id bigint unsigned NOT NULL ,
			status enum('pending', 'success', 'error') NOT NULL DEFAULT 'pending' ,
			changes json NOT NULL ,
			error json NULL ,
			created_at datetime NOT NULL ,
			finished_at datetime NULL DEFAULT NULL ,
			PRIMARY KEY  ( id )
		) $charset_collate ;";
		dbDelta($sql);
		$sql = 'ALTER TABLE '.self::$jobsTableName.'
			ADD CONSTRAINT FOREIGN KEY (user_id)
			REFERENCES '.$wpdb->users.' (id)
			ON UPDATE CASCADE
			ON DELETE SET NULL';
		@$wpdb->query($sql);
		$sql = 'ALTER TABLE '.self::$jobsProductUpdatesTableName.'
			ADD CONSTRAINT FOREIGN KEY (job_id)
			REFERENCES '.self::$jobsTableName.' (id)
			ON UPDATE CASCADE
			ON DELETE CASCADE';
		@$wpdb->query($sql);
		$sql = 'ALTER TABLE '.self::$jobsProductUpdatesTableName.'
			ADD CONSTRAINT FOREIGN KEY (post_id)
			REFERENCES '.$wpdb->posts.' (id)
			ON UPDATE CASCADE
			ON DELETE CASCADE';
		@$wpdb->query($sql);
	}

	private static function createFiltersTable() {
		global $wpdb;
		$charset_collate = $wpdb->get_charset_collate();
		$sql = 'CREATE TABLE '.self::$filtersTableName." (
			id bigint unsigned NOT NULL AUTO_INCREMENT ,
			version int unsigned NOT NULL DEFAULT 1 ,
			name varchar(60) NOT NULL ,
			filter_conditions json NOT NULL ,
			created_by bigint unsigned NULL ,
			created_at datetime NOT NULL ,
			updated_at datetime NOT NULL ,
			PRIMARY KEY  ( id ) ,
			KEY created_by (created_by) ,
			KEY created_at (created_at)
		) $charset_collate ;";
		dbDelta($sql);
		$sql = 'ALTER TABLE '.self::$filtersTableName.'
			ADD CONSTRAINT FOREIGN KEY (created_by)
			REFERENCES '.$wpdb->users.' (id)
			ON UPDATE CASCADE
			ON DELETE SET NULL';
		@$wpdb->query($sql);
	}
} global $wpdb;
DatabaseManagerService::$jobsTableName = $wpdb->prefix.'shopmanagerpro_jobs';
DatabaseManagerService::$jobsProductUpdatesTableName = $wpdb->prefix.'shopmanagerpro_jobs_products';
DatabaseManagerService::$filtersTableName = $wpdb->prefix.'shopmanagerpro_filters';
