<?php

namespace Mintall_Keep\DB;

defined( 'ABSPATH' ) || exit;

/**
 * Database helper – table names and schema creation.
 */
class Database {

	public const CONNECTIONS_TABLE = 'mintall_connections';
	public const API_KEYS_TABLE    = 'mintall_api_keys';

	public static function connections_table() {
		global $wpdb;
		return $wpdb->prefix . self::CONNECTIONS_TABLE;
	}

	public static function api_keys_table() {
		global $wpdb;
		return $wpdb->prefix . self::API_KEYS_TABLE;
	}

	/**
	 * Run schema migrations when the installed DB version is behind the code.
	 *
	 * Activation only fires on a fresh activate, not on an in-place update, so
	 * a plugin updated by file sync would otherwise keep a stale table shape
	 * (e.g. missing the callback-signing columns added in a later release).
	 * dbDelta is idempotent and ALTERs existing tables to match, so re-running
	 * it on a version bump safely adds any missing columns.
	 */
	public static function maybe_upgrade() {
		if ( get_option( 'mintall_keep_db_version' ) === MINTALL_KEEP_VERSION ) {
			return;
		}

		self::create_tables();
		self::migrate_certificate_meta_key();
		update_option( 'mintall_keep_db_version', MINTALL_KEEP_VERSION );
	}

	/**
	 * One-time migration: 1.2.0 renamed the storefront snapshot's storage key
	 * from `_mintall_certificate` to `_mintall_snapshot`. Without this, any
	 * store that certified products before upgrading loses its storefront
	 * JSON-LD entirely — the injector reads the new key, finds nothing, and
	 * the old (still valid) data just sits there unread.
	 *
	 * For every product with the legacy key, copy its value to the new key
	 * (unless something's already written a fresh snapshot there — don't
	 * clobber newer data with a stale one) and remove the legacy key either
	 * way, so it doesn't linger indefinitely.
	 */
	private static function migrate_certificate_meta_key() {
		global $wpdb;

		$post_ids = $wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			"SELECT DISTINCT post_id FROM {$wpdb->postmeta} WHERE meta_key = '_mintall_certificate'"
		);

		foreach ( $post_ids as $post_id ) {
			$post_id = (int) $post_id;
			$legacy  = get_post_meta( $post_id, '_mintall_certificate', true );

			if ( '' !== (string) $legacy && '' === (string) get_post_meta( $post_id, '_mintall_snapshot', true ) ) {
				// wp_slash() offsets update_post_meta()'s built-in unslash, so
				// JSON escapes (\", \n, \uXXXX) survive intact.
				update_post_meta( $post_id, '_mintall_snapshot', wp_slash( $legacy ) );
			}

			delete_post_meta( $post_id, '_mintall_certificate' );
		}
	}

	/**
	 * Create all custom tables via dbDelta.
	 */
	public static function create_tables() {
		global $wpdb;

		$charset_collate = $wpdb->get_charset_collate();

		$tables = "
			CREATE TABLE {$wpdb->prefix}mintall_connections (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				site_url varchar(255) NOT NULL,
				platform_id varchar(100) NOT NULL DEFAULT '',
				callback_public_key text NULL,
				callback_keypair_issued_at varchar(64) NOT NULL DEFAULT '',
				created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY  (id),
				UNIQUE KEY site_url (site_url)
			) {$charset_collate};

			CREATE TABLE {$wpdb->prefix}mintall_api_keys (
				id varchar(100) NOT NULL,
				connection_id bigint(20) unsigned NOT NULL,
				platform_account_id varchar(100) NOT NULL DEFAULT '',
				user_id varchar(100) NOT NULL DEFAULT '',
				user_email varchar(255) NOT NULL DEFAULT '',
				api_key text NOT NULL,
				created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY  (id),
				KEY connection_id (connection_id)
			) {$charset_collate};
		";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $tables );
	}
}