<?php
namespace ToDownload\Data;

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

final class Schema {
    public const SCHEMA_VERSION = 5;
	private const POST_TYPE_MIGRATION_OPTION = 'ToDownload_post_type_migration_v1_done';
    private const PUBLIC_ID_BACKFILL_OPTION = 'ToDownload_public_id_backfill_v1_done';

    public static function install(): void {
        global $wpdb;

        $charset = $wpdb->get_charset_collate();
		$orders = esc_sql( $wpdb->prefix . 'ToDownload_orders' );
        $order_items = esc_sql( $wpdb->prefix . 'ToDownload_order_items' );
        $customers = esc_sql( $wpdb->prefix . 'ToDownload_customers' );
		$license_keys = esc_sql( $wpdb->prefix . 'ToDownload_license_keys' );

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

        $sql1 = "CREATE TABLE $customers (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			public_id VARCHAR(36) NOT NULL,
            user_id BIGINT UNSIGNED NULL,
            email VARCHAR(190) NOT NULL,
            first_name VARCHAR(190) NULL,
            last_name VARCHAR(190) NULL,
            created_at DATETIME NOT NULL,
            updated_at DATETIME NOT NULL,
            PRIMARY KEY (id),
			UNIQUE KEY public_id_unique (public_id),
            UNIQUE KEY email_unique (email),
            KEY user_id_idx (user_id)
        ) $charset;";

        $sql2 = "CREATE TABLE $orders (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			public_id VARCHAR(36) NOT NULL,
            customer_id BIGINT UNSIGNED NOT NULL,
            seller_user_id BIGINT UNSIGNED NULL,
            status VARCHAR(40) NOT NULL,
            currency VARCHAR(10) NOT NULL DEFAULT 'USD',
            subtotal DECIMAL(10,2) NOT NULL DEFAULT 0,
            tax DECIMAL(10,2) NOT NULL DEFAULT 0,
            discount DECIMAL(10,2) NOT NULL DEFAULT 0,
            total DECIMAL(10,2) NOT NULL DEFAULT 0,
            gateway VARCHAR(50) NULL,
            transaction_id VARCHAR(190) NULL,
            notes LONGTEXT NULL,
            created_at DATETIME NOT NULL,
            updated_at DATETIME NOT NULL,
            PRIMARY KEY (id),
			UNIQUE KEY public_id_unique (public_id),
            KEY customer_id_idx (customer_id),
            KEY seller_user_id_idx (seller_user_id),
            KEY status_idx (status)
        ) $charset;";

        $sql3 = "CREATE TABLE $order_items (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            order_id BIGINT UNSIGNED NOT NULL,
            download_id BIGINT UNSIGNED NOT NULL,
            price DECIMAL(10,2) NOT NULL DEFAULT 0,
            created_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            KEY order_id_idx (order_id),
            KEY download_id_idx (download_id)
        ) $charset;";

        $sql4 = "CREATE TABLE $license_keys (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            order_id BIGINT UNSIGNED NOT NULL,
            download_id BIGINT UNSIGNED NOT NULL,
            license_key VARCHAR(190) NOT NULL,
            created_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY order_download_unique (order_id, download_id),
            KEY order_id_idx (order_id),
            KEY download_id_idx (download_id)
        ) $charset;";

        dbDelta($sql1);
        dbDelta($sql2);
        dbDelta($sql3);
		dbDelta($sql4);

        update_option('ToDownload_schema_version', self::SCHEMA_VERSION);
    }

    public static function maybe_upgrade(): void {
        $installed = (int) get_option('ToDownload_schema_version', 0);
        if ($installed < self::SCHEMA_VERSION) {
            self::install();
        }

        self::maybe_migrate_post_types();
        self::maybe_backfill_public_ids();
    }

    private static function maybe_backfill_public_ids(): void {
        // Keep this idempotent and cheap: run once per site.
        $done = (int) get_option(self::PUBLIC_ID_BACKFILL_OPTION, 0);
        if ($done === 1) {
            return;
        }

        global $wpdb;
        if (!$wpdb) {
            return;
        }

		$customers = esc_sql( $wpdb->prefix . 'ToDownload_customers' );
		$orders = esc_sql( $wpdb->prefix . 'ToDownload_orders' );

        // Backfill customers missing public_id.
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- table names are internal (wpdb prefix + plugin table).
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time backfill.
        $missing_customers = $wpdb->get_col(
			"SELECT id FROM " . $customers . " WHERE public_id IS NULL OR public_id = '' LIMIT 5000"
        );
        if (is_array($missing_customers) && !empty($missing_customers)) {
            foreach ($missing_customers as $id) {
                $id = (int) $id;
                if ($id <= 0) {
                    continue;
                }
                $uuid = function_exists('wp_generate_uuid4') ? (string) wp_generate_uuid4() : (string) uniqid('cust_', true);
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time backfill.
                $wpdb->update($customers, ['public_id' => $uuid], ['id' => $id]);
            }
        }

        // Backfill orders missing public_id.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time backfill.
        $missing_orders = $wpdb->get_col(
			"SELECT id FROM " . $orders . " WHERE public_id IS NULL OR public_id = '' LIMIT 5000"
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
        if (is_array($missing_orders) && !empty($missing_orders)) {
            foreach ($missing_orders as $id) {
                $id = (int) $id;
                if ($id <= 0) {
                    continue;
                }
                $uuid = function_exists('wp_generate_uuid4') ? (string) wp_generate_uuid4() : (string) uniqid('ord_', true);
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time backfill.
                $wpdb->update($orders, ['public_id' => $uuid], ['id' => $id]);
            }
        }

        update_option(self::PUBLIC_ID_BACKFILL_OPTION, 1);
    }

    private static function maybe_migrate_post_types(): void {
        // Keep this idempotent and cheap: run once per site.
        $done = (int) get_option(self::POST_TYPE_MIGRATION_OPTION, 0);
        if ($done === 1) {
            return;
        }

        global $wpdb;
        if (!$wpdb) {
            return;
        }

        // Migrate legacy uppercase CPT keys to their lowercase equivalents.
        // This prevents “missing products/discounts” after we fixed CPT keys.
        $map = [
            'ToDownload_download' => 'todownload_download',
            'ToDownload_discount' => 'todownload_discount',
        ];

        foreach ($map as $from => $to) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->update(
                $wpdb->posts,
                ['post_type' => $to],
                ['post_type' => $from],
                ['%s'],
                ['%s']
            );
        }

        update_option(self::POST_TYPE_MIGRATION_OPTION, 1);
    }
}