<?php
/**
 * Database installer and helpers.
 *
 * @package SKYNSMFA_Smart_Favorites
 */

declare( strict_types=1 );

namespace SKYNSMFA\Wishlist;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}


final class DB {
	/**
	 * dbDelta schema version (bump when CREATE TABLE statements change).
	 *
	 * Compared to option skynsmfa_db_version on each request (woocommerce_init).
	 * dbDelta creates missing tables and adds new columns/indexes to existing tables.
	 */
	public const SCHEMA_VERSION = '2.0.0';

	/**
	 * Singleton instance.
	 *
	 * @var DB|null
	 */
	private static $instance = null;

	/**
	 * Get singleton instance.
	 *
	 * @return DB
	 */
	public static function instance(): DB {
		if ( null === self::$instance ) {
			self::$instance = new self();
		}

		return self::$instance;
	}

	/**
	 * Constructor.
	 */
	private function __construct() {
	}

	/**
	 * Run dbDelta if stored schema version is older than SCHEMA_VERSION.
	 *
	 * Runs on woocommerce_init (plugin updates do not re-fire activation). Safe to
	 * call repeatedly: dbDelta only applies missing tables, columns, and keys.
	 *
	 * @return void
	 */
	public function maybe_upgrade(): void {
		$current = (string) get_option( 'skynsmfa_db_version', '0' );
		if ( version_compare( $current, self::SCHEMA_VERSION, '>=' ) ) {
			return;
		}

		$this->install();
		update_option( 'skynsmfa_db_version', self::SCHEMA_VERSION, false );
	}

	/**
	 * Schema changes applied via dbDelta since 1.0.x (for upgrade documentation).
	 *
	 * @return array<string, list<string>>
	 */
	public static function schema_changelog(): array {
		return array(
			'1.0.x' => array(
				'skynsmfa_wishlists: base columns',
				'skynsmfa_wishlist_items: base columns',
			),
			'1.1.0' => array(
				'skynsmfa_wishlists: privacy, share_token, share_token index',
				'skynsmfa_wishlist_items: sort_order, price_snapshot, currency_snapshot',
				'skynsmfa_events: new analytics table',
			),
			'2.0.0' => array(
				'Runs dbDelta again for sites stuck on an older skynsmfa_db_version (adds only; does not delete rows or drop columns).',
			),
		);
	}

	/**
	 * Install tables.
	 *
	 * @return void
	 */
	public function install(): void {
		global $wpdb;

		if ( ! function_exists( 'dbDelta' ) ) {
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		}

		$charset_collate = $wpdb->get_charset_collate();

		$wishlists_table = $wpdb->prefix . 'skynsmfa_wishlists';
		$items_table     = $wpdb->prefix . 'skynsmfa_wishlist_items';
		$events_table    = $wpdb->prefix . 'skynsmfa_events';

		$sql_wishlists = "CREATE TABLE {$wishlists_table} (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			session_key VARCHAR(64) NOT NULL DEFAULT '',
			name VARCHAR(190) NOT NULL DEFAULT '',
			is_default TINYINT(1) NOT NULL DEFAULT 0,
			privacy VARCHAR(20) NOT NULL DEFAULT 'private',
			share_token VARCHAR(64) NOT NULL DEFAULT '',
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY user_id (user_id),
			KEY session_key (session_key),
			KEY share_token (share_token)
		) {$charset_collate};";

		$sql_items = "CREATE TABLE {$items_table} (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			wishlist_id BIGINT(20) UNSIGNED NOT NULL,
			product_id BIGINT(20) UNSIGNED NOT NULL,
			variation_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			quantity INT(11) NOT NULL DEFAULT 1,
			sort_order INT(11) NOT NULL DEFAULT 0,
			price_snapshot DECIMAL(19,4) NULL,
			currency_snapshot VARCHAR(10) NOT NULL DEFAULT '',
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY wishlist_product (wishlist_id, product_id, variation_id),
			KEY product_id (product_id)
		) {$charset_collate};";

		$sql_events = "CREATE TABLE {$events_table} (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			event_type VARCHAR(20) NOT NULL DEFAULT '',
			product_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			owner_token VARCHAR(64) NOT NULL DEFAULT '',
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY product_created (product_id, created_at),
			KEY event_created (event_type, created_at),
			KEY user_product (user_id, product_id)
		) {$charset_collate};";

		dbDelta( $sql_wishlists );
		dbDelta( $sql_items );
		dbDelta( $sql_events );
	}
}
