<?php

namespace classes;

use RuntimeException;
use Throwable;

/**
 * Additive schema and the one-time legacy-to-exact-product migration for
 * customer-rule product restrictions.
 */
class customer_rule_product_restriction_schema_bootstrap
{
    public const LEGACY_SEED_KEY = 'legacy_exact_product_sets_v1';

    private static bool $initialized = false;

    /** @var array<string, string> */
    private const RULES = [
        'restrictAdditionalServices' => 'Additional services',
        'restrictTankCleaning' => 'Tank cleaning',
        'restrictSpotFree' => 'SpotFree',
        'restrictInteriorCleaning' => 'Interior cleaning',
        'onlyTankCleaning' => 'Non-tank products',
    ];

    public static function ensureSchema(): void
    {
        if (self::$initialized) {
            return;
        }

        global $db;
        if (!isset($db) || !is_object($db) || !method_exists($db, 'query')) {
            return;
        }

        self::createTables($db);
        self::deduplicateCustomerAttributes($db);
        self::seedLegacyProductSets($db);
        self::$initialized = true;
    }

    private static function createTables(object $db): void
    {
        $statements = [
            "CREATE TABLE IF NOT EXISTS customer_rule_product_restrictions (
                attribute VARCHAR(191) NOT NULL,
                version INT UNSIGNED NOT NULL DEFAULT 1,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (attribute)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
            "CREATE TABLE IF NOT EXISTS customer_rule_product_collections (
                id INT UNSIGNED NOT NULL AUTO_INCREMENT,
                attribute VARCHAR(191) NOT NULL,
                name VARCHAR(191) NOT NULL,
                sort_order INT NOT NULL DEFAULT 0,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY uniq_customer_rule_collection_name (attribute, name),
                KEY idx_customer_rule_collection_attribute_order (attribute, sort_order, id),
                CONSTRAINT fk_customer_rule_collection_attribute
                    FOREIGN KEY (attribute) REFERENCES customer_rule_product_restrictions(attribute)
                    ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
            "CREATE TABLE IF NOT EXISTS customer_rule_product_collection_products (
                collection_id INT UNSIGNED NOT NULL,
                product_id INT UNSIGNED NOT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (collection_id, product_id),
                KEY idx_customer_rule_collection_product (product_id, collection_id),
                CONSTRAINT fk_customer_rule_collection_product_collection
                    FOREIGN KEY (collection_id) REFERENCES customer_rule_product_collections(id)
                    ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
            "CREATE TABLE IF NOT EXISTS customer_rule_product_migrations (
                migration_key VARCHAR(191) NOT NULL,
                details_json LONGTEXT NULL,
                applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (migration_key)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
            "CREATE TABLE IF NOT EXISTS customer_rule_product_audit_logs (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                actor_user_id INT UNSIGNED NULL,
                attribute VARCHAR(191) NOT NULL,
                old_version INT UNSIGNED NOT NULL,
                new_version INT UNSIGNED NOT NULL,
                changes_json LONGTEXT NOT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY idx_customer_rule_product_audit_attribute (attribute, created_at),
                KEY idx_customer_rule_product_audit_actor (actor_user_id, created_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        ];

        foreach ($statements as $statement) {
            if ($db->query($statement) === false) {
                throw new RuntimeException('Unable to initialize customer-rule product restriction schema');
            }
        }
    }

    private static function deduplicateCustomerAttributes(object $db): void
    {
        if (!self::tableExists($db, 'customer_attributes')) {
            return;
        }

        if (self::indexExists($db, 'customer_attributes', 'uniq_customer_attributes_user_attribute')) {
            return;
        }

        if ($db->query(
            'DELETE duplicate_row FROM customer_attributes duplicate_row
             INNER JOIN customer_attributes keep_row
                ON keep_row.user_id = duplicate_row.user_id
               AND keep_row.attribute = duplicate_row.attribute
               AND keep_row.id < duplicate_row.id'
        ) === false) {
            throw new RuntimeException('Unable to deduplicate customer attributes');
        }

        if ($db->query(
            'ALTER TABLE customer_attributes
             ADD UNIQUE KEY uniq_customer_attributes_user_attribute (user_id, attribute)'
        ) === false) {
            throw new RuntimeException('Unable to enforce unique customer attributes');
        }
    }

    private static function seedLegacyProductSets(object $db): void
    {
        if (!self::tableExists($db, 'products') || !self::tableExists($db, 'categories')) {
            return;
        }

        $migrationKey = self::escape($db, self::LEGACY_SEED_KEY);
        $existing = $db->query(
            "SELECT migration_key FROM customer_rule_product_migrations WHERE migration_key = '{$migrationKey}' LIMIT 1"
        );
        if ($existing && (int)$existing->num_rows > 0) {
            return;
        }

        if ($db->query('START TRANSACTION') === false) {
            throw new RuntimeException('Unable to start customer-rule product migration');
        }
        try {
            if ($db->query(
                "INSERT IGNORE INTO customer_rule_product_migrations (migration_key, details_json)
                 VALUES ('{$migrationKey}', '{\"status\":\"in_progress\"}')"
            ) === false) {
                throw new RuntimeException('Unable to claim customer-rule product migration');
            }
            if (self::affectedRows($db) === 0) {
                $db->query('ROLLBACK');
                return;
            }

            foreach (array_keys(self::RULES) as $attribute) {
                $safeAttribute = self::escape($db, $attribute);
                if ($db->query(
                    "INSERT IGNORE INTO customer_rule_product_restrictions (attribute, version)
                     VALUES ('{$safeAttribute}', 1)"
                ) === false) {
                    throw new RuntimeException("Unable to initialize restriction {$attribute}");
                }
            }

            $counts = [];
            $seededProductIds = [];
            foreach (self::RULES as $attribute => $collectionName) {
                $safeAttribute = self::escape($db, $attribute);
                $safeName = self::escape($db, 'Legacy migration: ' . $collectionName);
                if ($db->query(
                    "INSERT INTO customer_rule_product_collections (attribute, name, sort_order)
                     VALUES ('{$safeAttribute}', '{$safeName}', 0)"
                ) === false) {
                    throw new RuntimeException("Unable to create seed collection for {$attribute}");
                }
                $collectionId = (int)$db->insert_id();
                if ($collectionId < 1) {
                    throw new RuntimeException("Unable to create seed collection for {$attribute}");
                }

                $predicate = self::legacyPredicate($db, $attribute);
                $activePredicate = self::columnExists($db, 'products', 'deleted_at')
                    ? 'p.deleted_at IS NULL'
                    : '1 = 1';
                $insert = $db->query(
                    "INSERT IGNORE INTO customer_rule_product_collection_products (collection_id, product_id)
                     SELECT {$collectionId}, p.id
                     FROM products p
                     LEFT JOIN categories c ON c.id = p.category
                     WHERE ({$activePredicate}) AND ({$predicate})"
                );
                if ($insert === false) {
                    throw new RuntimeException("Unable to seed products for {$attribute}");
                }
                $counts[$attribute] = self::affectedRows($db);
                $seeded = $db->query(
                    "SELECT product_id FROM customer_rule_product_collection_products
                     WHERE collection_id = {$collectionId} ORDER BY product_id"
                );
                $seededProductIds[$attribute] = [];
                if ($seeded) {
                    while ($row = $seeded->fetch_assoc()) {
                        $seededProductIds[$attribute][] = (int)$row['product_id'];
                    }
                }
            }

            $details = self::escape($db, (string)json_encode([
                'counts' => $counts,
                'product_ids' => $seededProductIds,
                'seeded_at' => gmdate(DATE_ATOM),
            ], JSON_UNESCAPED_SLASHES));
            if ($db->query(
                "UPDATE customer_rule_product_migrations
                 SET details_json = '{$details}', applied_at = NOW()
                 WHERE migration_key = '{$migrationKey}'"
            ) === false) {
                throw new RuntimeException('Unable to record customer-rule product migration');
            }
            if ($db->query('COMMIT') === false) {
                throw new RuntimeException('Unable to commit customer-rule product migration');
            }
        } catch (Throwable $throwable) {
            $db->query('ROLLBACK');
            throw $throwable;
        }
    }

    private static function legacyPredicate(object $db, string $attribute): string
    {
        $text = "LOWER(CONCAT(COALESCE(p.name, ''), ' ', COALESCE(c.name, '')))";

        return match ($attribute) {
            'restrictAdditionalServices' => "p.category = 8 OR LOWER(COALESCE(c.name, '')) IN ('tillægsydelser', 'tillaegsydelser')" .
                (self::tableExists($db, 'products_options') && self::columnExists($db, 'products_options', 'option_id')
                    ? ' OR EXISTS (SELECT 1 FROM products_options po WHERE po.option_id = p.id)'
                    : ''),
            'restrictTankCleaning' => "p.category = 5 OR {$text} LIKE '%tank cleaning%' OR {$text} LIKE '%tankcleaning%' OR {$text} LIKE '%tankrens%' OR {$text} LIKE '%tank rens%'",
            'restrictSpotFree' => "p.id IN (23, 24) OR {$text} LIKE '%spot free%' OR {$text} LIKE '%spotfree%' OR {$text} LIKE '%skylning med ro%'",
            'restrictInteriorCleaning' => "{$text} LIKE '%interior%' OR {$text} LIKE '%indvendig%'",
            'onlyTankCleaning' => "NOT (p.category = 5 OR {$text} LIKE '%tank cleaning%' OR {$text} LIKE '%tankcleaning%' OR {$text} LIKE '%tankrens%' OR {$text} LIKE '%tank rens%')",
            default => '0 = 1',
        };
    }

    private static function tableExists(object $db, string $table): bool
    {
        $safeTable = self::escape($db, $table);
        $result = $db->query("SHOW TABLES LIKE '{$safeTable}'");
        return $result && (int)$result->num_rows > 0;
    }

    private static function indexExists(object $db, string $table, string $index): bool
    {
        $safeTable = str_replace('`', '', $table);
        $safeIndex = self::escape($db, $index);
        $result = $db->query("SHOW INDEX FROM `{$safeTable}` WHERE Key_name = '{$safeIndex}'");
        return $result && (int)$result->num_rows > 0;
    }

    private static function columnExists(object $db, string $table, string $column): bool
    {
        $safeTable = str_replace('`', '', $table);
        $safeColumn = self::escape($db, $column);
        $result = $db->query("SHOW COLUMNS FROM `{$safeTable}` LIKE '{$safeColumn}'");
        return $result && (int)$result->num_rows > 0;
    }

    private static function escape(object $db, string $value): string
    {
        return method_exists($db, 'escape_string')
            ? $db->escape_string($value)
            : addslashes($value);
    }

    private static function affectedRows(object $db): int
    {
        if (method_exists($db, 'conn')) {
            $connection = $db->conn();
            return (int)($connection->affected_rows ?? 0);
        }
        return (int)($db->affected_rows ?? 0);
    }
}
