<?php
/**
 * Helper database class.
 *
 * @package SaveToWishlist\Helpers
 * @since 1.0.0
 */
namespace SaveToWishlist\Helpers;

use SaveToWishlist\Abstracts\Singleton;

/**
 * Class Helper_Database
 *
 * This class serves as the wishlist database helper class.
 */
class Helper_Database extends Singleton {

    /**
     * Option key storing the installed schema version.
     *
     * @since 1.0.7
     */
    private const OPTION_DB_VERSION = 'stwlite_db_version';

    /**
     * Transient key flagging that required tables have been verified to exist.
     *
     * @since 1.0.7
     */
    private const TRANSIENT_SCHEMA_VERIFIED = 'stwlite_schema_verified';

    /**
     * Per-request memoisation flag — skips repeated checks within a single request.
     *
     * @since 1.0.7
     * @var bool
     */
    private static $schema_verified = false;

    /**
     * Get the collections table name.
     *
     * @since 1.0.0
     * @access public
     * @return string
     */
    public static function get_collections_table_name() {
        global $wpdb;

        $table_names = wp_cache_get( 'table_names', STWLITE_CACHE_GROUP );

        if ( ! is_array( $table_names ) ) {
            $table_names = array();
        }

        if ( isset( $table_names['collections'] ) ) {
            return $table_names['collections'];
        }

        $table_names['collections'] = $wpdb->prefix . 'stw_collections';

        wp_cache_set( 'table_names', $table_names, STWLITE_CACHE_GROUP );

        return $table_names['collections'];
    }

    /**
     * Get the collection items table name.
     *
     * @since 1.0.0
     * @access public
     * @return string
     */
    public static function get_collection_items_table_name() {
        global $wpdb;

        $table_names = wp_cache_get( 'table_names', STWLITE_CACHE_GROUP );

        if ( ! is_array( $table_names ) ) {
            $table_names = array();
        }

        if ( isset( $table_names['collection_items'] ) ) {
            return $table_names['collection_items'];
        }

        $table_names['collection_items'] = $wpdb->prefix . 'stw_collection_items';

        wp_cache_set( 'table_names', $table_names, STWLITE_CACHE_GROUP );

        return $table_names['collection_items'];
    }

    /**
     * Get the collection sharing table name.
     *
     * @since 1.0.0
     * @access public
     * @return string
     */
    public static function get_collection_sharing_table_name() {
        global $wpdb;

        $table_names = wp_cache_get( 'table_names', STWLITE_CACHE_GROUP );

        if ( ! is_array( $table_names ) ) {
            $table_names = array();
        }

        if ( isset( $table_names['collection_sharing'] ) ) {
            return $table_names['collection_sharing'];
        }

        $table_names['collection_sharing'] = $wpdb->prefix . 'stw_collection_sharing';

        wp_cache_set( 'table_names', $table_names, STWLITE_CACHE_GROUP );

        return $table_names['collection_sharing'];
    }

    /**
     * Get the collections table schema.
     *
     * @since 1.0.0
     * @access public
     * @return string
     */
    public static function get_collections_schema() {
        global $wpdb;

        $charset_collate = $wpdb->get_charset_collate();
        $table_name      = self::get_collections_table_name();
        $user_table_name = $wpdb->prefix . 'users';

        $schema = "CREATE TABLE IF NOT EXISTS `{$table_name}` (
                `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                `user_id` bigint(20) unsigned NOT NULL,
                `url_code` varchar(255) NOT NULL DEFAULT '',
                `name` varchar(255) NOT NULL DEFAULT 'My Wishlist',
                `description` text,
                `status` varchar(50) NOT NULL DEFAULT 'publish',
                `is_default` tinyint(1) NOT NULL DEFAULT 0,
                `is_public` tinyint(1) NOT NULL DEFAULT 0,
                `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
                `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                KEY `user_id` (`user_id`),
                KEY `is_default` (`is_default`),
                KEY `is_public` (`is_public`),
                CONSTRAINT `fk_wishlist_collections_user` FOREIGN KEY (`user_id`) REFERENCES `{$user_table_name}` (`ID`) ON DELETE CASCADE
                ) $charset_collate;";

        return $schema;
    }

    /**
     * Get the collection items table schema.
     *
     * @since 1.0.0
     * @access public
     * @return string
     */
    public static function get_collection_items_schema() {
        global $wpdb;

        $charset_collate        = $wpdb->get_charset_collate();
        $table_name             = self::get_collection_items_table_name();
        $collections_table_name = self::get_collections_table_name();
        $posts_table_name       = $wpdb->prefix . 'posts';

        $schema = "CREATE TABLE IF NOT EXISTS `{$table_name}` (
                `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                `collection_id` bigint(20) unsigned NOT NULL,
                `product_id` bigint(20) unsigned NOT NULL,
                `variation_id` bigint(20) DEFAULT 0,
                `sort_order` int(11) DEFAULT 0,
                `quantity` int(11) NOT NULL DEFAULT 1,
                `priority` enum('low','medium','high') DEFAULT 'medium',
                `notes` text,
                `added_at` datetime DEFAULT CURRENT_TIMESTAMP,
                `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                UNIQUE KEY `collection_product_variation` (`collection_id`,`product_id`,`variation_id`),
                KEY `collection_id` (`collection_id`),
                KEY `product_id` (`product_id`),
                KEY `variation_id` (`variation_id`),
                KEY `priority` (`priority`),
                KEY `added_at` (`added_at`),
                CONSTRAINT `fk_wishlist_items_collection` FOREIGN KEY (`collection_id`) REFERENCES `{$collections_table_name}` (`id`) ON DELETE CASCADE,
                CONSTRAINT `fk_wishlist_items_product` FOREIGN KEY (`product_id`) REFERENCES `{$posts_table_name}` (`ID`) ON DELETE CASCADE
                ) $charset_collate;";

        return $schema;
    }

    /**
     * Get the collection sharing table schema.
     *
     * @since 1.0.0
     * @access public
     * @return string
     */
    public static function get_collection_sharing_schema() {
        global $wpdb;

        $charset_collate        = $wpdb->get_charset_collate();
        $table_name             = self::get_collection_sharing_table_name();
        $collections_table_name = self::get_collections_table_name();

        $schema = "CREATE TABLE IF NOT EXISTS `{$table_name}` (
                `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                `collection_id` bigint(20) unsigned NOT NULL,
                `share_token` varchar(64) NOT NULL,
                `expires_at` datetime DEFAULT NULL,
                `view_count` int(11) DEFAULT 0,
                `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                UNIQUE KEY `share_token` (`share_token`),
                KEY `collection_id` (`collection_id`),
                KEY `expires_at` (`expires_at`),
                CONSTRAINT `fk_wishlist_shares_collection` FOREIGN KEY (`collection_id`) REFERENCES `{$collections_table_name}` (`id`) ON DELETE CASCADE
                ) $charset_collate;";

        return $schema;
    }

    /**
     * Update the collection items table schema.
     *
     * @since 1.0.4 Added variation ID.
     * @access private
     * @return void
     */
    private static function update_collection_items_schema() {
        global $wpdb;

        $table_name = self::get_collection_items_table_name();

        // phpcs:disable

        // Drop old unique index if it exists.
        $index_exists = $wpdb->get_results( "SHOW INDEX FROM `{$table_name}` WHERE Key_name = 'collection_product'" );
        if ( ! empty( $index_exists ) ) {
            $wpdb->query( "ALTER TABLE `{$table_name}` DROP INDEX `collection_product`" );
        }

        // Add variation_id index if it doesn't exist.
        $variation_index_exists = $wpdb->get_results( "SHOW INDEX FROM `{$table_name}` WHERE Key_name = 'variation_id'" );
        if ( empty( $variation_index_exists ) ) {
            $wpdb->query( "ALTER TABLE `{$table_name}` ADD KEY `variation_id` (`variation_id`)" );
        }

        // Add new unique index that includes variation_id if it doesn't exist.
        $unique_index_exists = $wpdb->get_results( "SHOW INDEX FROM `{$table_name}` WHERE Key_name = 'collection_product_variation'" );
        if ( empty( $unique_index_exists ) ) {
            $wpdb->query( "ALTER TABLE `{$table_name}` ADD UNIQUE KEY `collection_product_variation` (`collection_id`,`product_id`,`variation_id`)" );
        }

        // phpcs:enable
    }

    /**
     * Create the wishlist tables.
     *
     * @since 1.0.0
     * @since 1.0.4 Added variation ID.
     * @since 1.0.7 Persists the installed schema version for runtime self-healing.
     * @access public
     */
    public static function create_tables() {
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        dbDelta( self::get_collections_schema() );
        dbDelta( self::get_collection_items_schema() );
        dbDelta( self::get_collection_sharing_schema() );

        // Update collection items table schema.
        self::update_collection_items_schema();

        // Record the schema version so future boots can detect drift.
        // Autoloaded so the per-request read in maybe_install_schema() is served from the alloptions cache.
        update_option( self::OPTION_DB_VERSION, STWLITE_DB_VERSION, true );
    }

    /**
     * Verify the wishlist tables exist and (re)create them if not.
     *
     * Defends against scenarios where the activation hook never fires —
     * file-level updates via SFTP/Git/managed-host pipelines, manual DB
     * surgery that drops a table, partial restores, or new tables added
     * in a future release. Cheap on the hot path: a static flag short-
     * circuits within a single request, and a transient gates the
     * `SHOW TABLES` checks to roughly once every 12 hours.
     *
     * @since 1.0.7
     * @access public
     * @return void
     */
    public static function maybe_install_schema() {
        if ( self::$schema_verified ) {
            return;
        }

        $stored_version  = get_option( self::OPTION_DB_VERSION );
        $current_version = STWLITE_DB_VERSION;

        // Healthy and recently verified — nothing to do.
        if ( $stored_version === $current_version && get_transient( self::TRANSIENT_SCHEMA_VERIFIED ) ) {
            self::$schema_verified = true;
            return;
        }

        // Reach here when the schema version is stale or the verified transient expired —
        // run a (re)install when the version moved or any required table is actually missing.
        if ( $stored_version !== $current_version || ! self::tables_exist() ) {
            self::create_tables();
        }

        // Only flag verified when the tables are actually present now — otherwise a failed
        // create_tables() (insufficient privileges, disk full, FK/charset error) would suppress
        // retries for 12h while every query against the missing table keeps fataling.
        if ( ! self::tables_exist() ) {
            return;
        }

        set_transient( self::TRANSIENT_SCHEMA_VERIFIED, 1, 12 * HOUR_IN_SECONDS );
        self::$schema_verified = true;
    }

    /**
     * Check whether every required wishlist table exists in the database.
     *
     * @since 1.0.7
     * @access private
     * @return bool True when all required tables are present.
     */
    private static function tables_exist() {
        global $wpdb;

        $required_tables = array(
            self::get_collections_table_name(),
            self::get_collection_items_table_name(),
            self::get_collection_sharing_table_name(),
        );

        foreach ( $required_tables as $table ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) );
            if ( $found !== $table ) {
                return false;
            }
        }

        return true;
    }

    /**
     * Drop the wishlist tables.
     *
     * @since 1.0.0
     * @access public
     */
    public static function drop_tables() {
        global $wpdb;

        $wpdb->query( 'DROP TABLE IF EXISTS `' . esc_sql( self::get_collections_table_name() ) . '`' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->query( 'DROP TABLE IF EXISTS `' . esc_sql( self::get_collection_items_table_name() ) . '`' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->query( 'DROP TABLE IF EXISTS `' . esc_sql( self::get_collection_sharing_table_name() ) . '`' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery

        wp_cache_delete( 'table_names', STWLITE_CACHE_GROUP );
    }
}
