<?php
/**
 * Activation + schema upgrades.
 *
 * Custom tables (not CPT) are used because a single full-store stocktake can
 * generate thousands of count lines, and we need fast lookups by SKU.
 *
 * @package Stocktake_For_WooCommerce
 */

defined( 'ABSPATH' ) || exit;

// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- every query in this file targets the plugin's own fixed stwc_* tables (constant, prefixed names — no user input); all variable values go through $wpdb->prepare().

class STWC_Activator {

	/**
	 * Run on plugin activation.
	 */
	public static function activate() {
		self::create_tables();
		// When this shop started using the plugin. The review engine measures
		// days of use against it, and an install that predates the stamp backfills
		// it from its oldest stocktake rather than starting the clock again.
		if ( ! get_option( 'stwc_activated_at' ) ) {
			update_option( 'stwc_activated_at', time(), false );
		}
		// Also run the dedupe + unique-key migration here: the common upgrade
		// path is deactivate → install new zip → activate, which stamps the
		// version so maybe_upgrade() would never run it.
		self::ensure_unique_item_index();
		update_option( 'stwc_db_version', STWC_VERSION );

	}

	/**
	 * Run on every load; applies schema changes when the version moved.
	 * dbDelta adds any missing columns to existing tables.
	 */
	public static function maybe_upgrade() {
		if ( get_option( 'stwc_db_version' ) === STWC_VERSION ) {
			return;
		}
		self::create_tables();
		self::ensure_unique_item_index();
		update_option( 'stwc_db_version', STWC_VERSION );
	}

	/**
	 * Guarantee the (session_id, product_id, variation_id) uniqueness that stops
	 * a re-run snapshot from duplicating rows. dbDelta does not reliably add a
	 * unique key to an existing table, so do it explicitly (after de-duping).
	 */
	protected static function ensure_unique_item_index() {
		global $wpdb;
		$items = $wpdb->prefix . 'stwc_items';

		// phpcs:disable WordPress.DB.DirectDatabaseQuery -- schema maintenance on our own table.
		$wpdb->query( $wpdb->prepare( 'DELETE i1 FROM %i i1 INNER JOIN %i i2 ON i1.session_id = i2.session_id AND i1.product_id = i2.product_id AND i1.variation_id = i2.variation_id AND i1.id > i2.id', $items, $items ) );
		$has = $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM %i WHERE Key_name = 'session_target'", $items ) );
		if ( ! $has ) {
			$wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ADD UNIQUE KEY session_target (session_id, product_id, variation_id)', $items ) );
		}
		// phpcs:enable WordPress.DB.DirectDatabaseQuery
	}

	/**
	 * Create / migrate the sessions and items tables via dbDelta.
	 */
	public static function create_tables() {
		global $wpdb;

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

		$charset_collate = $wpdb->get_charset_collate();
		$sessions        = $wpdb->prefix . 'stwc_sessions';
		$items           = $wpdb->prefix . 'stwc_items';

		$sql_sessions = "CREATE TABLE {$sessions} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			name VARCHAR(191) NOT NULL DEFAULT '',
			status VARCHAR(20) NOT NULL DEFAULT 'open',
			scope_type VARCHAR(20) NOT NULL DEFAULT 'all',
			scope_value LONGTEXT NULL,
			count_mode VARCHAR(20) NOT NULL DEFAULT 'partial',
			blind TINYINT(1) NOT NULL DEFAULT 0,
			building TINYINT(1) NOT NULL DEFAULT 0,
			approval TINYINT(1) NOT NULL DEFAULT 0,
			note TEXT NULL,
			created_by BIGINT UNSIGNED NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',
			completed_at DATETIME NULL,
			PRIMARY KEY  (id),
			KEY status (status)
		) {$charset_collate};";

		$sql_items = "CREATE TABLE {$items} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			session_id BIGINT UNSIGNED NOT NULL,
			product_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			variation_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			sku VARCHAR(191) NOT NULL DEFAULT '',
			name VARCHAR(255) NOT NULL DEFAULT '',
			expected_qty DOUBLE NOT NULL DEFAULT 0,
			counted_qty DOUBLE NULL,
			unit_cost DOUBLE NOT NULL DEFAULT 0,
			applied TINYINT(1) NOT NULL DEFAULT 0,
			applied_from DOUBLE NULL,
			applied_qty DOUBLE NOT NULL DEFAULT 0,
			counted_by BIGINT UNSIGNED NULL,
			counted_at DATETIME NULL,
			extra TINYINT(1) NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			KEY session_id (session_id),
			KEY session_sku (session_id, sku),
			UNIQUE KEY session_target (session_id, product_id, variation_id)
		) {$charset_collate};";

		$backups       = $wpdb->prefix . 'stwc_backups';
		$backup_items  = $wpdb->prefix . 'stwc_backup_items';

		$sql_backups = "CREATE TABLE {$backups} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			session_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			label VARCHAR(191) NOT NULL DEFAULT '',
			item_count INT NOT NULL DEFAULT 0,
			created_by BIGINT UNSIGNED NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',
			PRIMARY KEY  (id),
			KEY session_id (session_id)
		) {$charset_collate};";

		$sql_backup_items = "CREATE TABLE {$backup_items} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			backup_id BIGINT UNSIGNED NOT NULL,
			product_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			variation_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			name VARCHAR(255) NOT NULL DEFAULT '',
			qty DOUBLE NOT NULL DEFAULT 0,
			applied_qty DOUBLE NOT NULL DEFAULT 0,
			managed TINYINT(1) NOT NULL DEFAULT 1,
			PRIMARY KEY  (id),
			KEY backup_id (backup_id)
		) {$charset_collate};";

		$log = $wpdb->prefix . 'stwc_log';

		$sql_log = "CREATE TABLE {$log} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			session_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			item_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			product_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			variation_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			name VARCHAR(255) NOT NULL DEFAULT '',
			user_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			event VARCHAR(20) NOT NULL DEFAULT '',
			qty_before DOUBLE NULL,
			qty_after DOUBLE NULL,
			note TEXT NULL,
			created_at DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',
			PRIMARY KEY  (id),
			KEY session_id (session_id),
			KEY event (event)
		) {$charset_collate};";

		dbDelta( $sql_sessions );
		dbDelta( $sql_items );
		dbDelta( $sql_backups );
		dbDelta( $sql_backup_items );
		dbDelta( $sql_log );

		// Counting capability: managers can always count; the Pro "counters"
		// setting grants it to additional roles.
		foreach ( array( 'administrator', 'shop_manager' ) as $role_name ) {
			$role = get_role( $role_name );
			if ( $role && ! $role->has_cap( 'stwc_count' ) ) {
				$role->add_cap( 'stwc_count' );
			}
		}
	}
}
