<?php
namespace Plugin\ItemShop;

class ItemShop {
    const ITEMS_TABLE = 'NEXORA_ITEMSHOP_ITEMS';
    const ORDERS_TABLE = 'NEXORA_ITEMSHOP_ORDERS';
    const PRESETS_TABLE = 'NEXORA_ITEMSHOP_PRESETS';
    const BULK_HISTORY_TABLE = 'NEXORA_ITEMSHOP_BULK_HISTORY';
    const COLLECTIONS_TABLE = 'NEXORA_ITEMSHOP_COLLECTIONS';
    const COLLECTION_ITEMS_TABLE = 'NEXORA_ITEMSHOP_COLLECTION_ITEMS';
    const CAMPAIGNS_TABLE = 'NEXORA_ITEMSHOP_CAMPAIGNS';
    const REWARD_DELIVERIES_TABLE = 'NEXORA_ITEMSHOP_REWARD_DELIVERIES';
    const ITEM_BYTES = 16;
    const WAREHOUSE_COLUMNS = 8;
    const WAREHOUSE_ROWS = 15;
    const WAREHOUSE_SLOTS = 120;
    const WAREHOUSE_STORAGE_BYTES = 3840;

    private $realm;
    private $db;
    private $settings;

    public function __construct($realm=null) {
        $this->realm = class_exists('NexoraRealm')
            ? \NexoraRealm::validate($realm === null ? \NexoraRealm::active() : $realm)
            : 'legacy';
        $this->db = \Connection::Database('Me_MuOnline', $this->realm);
        $this->settings = $this->loadSettings();
    }

    public static function configPath($realm=null) {
        if(class_exists('NexoraRealm')) {
            $realm = \NexoraRealm::validate($realm === null ? \NexoraRealm::active() : $realm);
            $realmFile = __PATH_ITEMSHOP_ROOT__.'config.'.$realm.'.xml';
            if(is_file($realmFile)) return $realmFile;
        }
        return __PATH_ITEMSHOP_ROOT__.'config.xml';
    }

    public function getRealm() { return $this->realm; }
    public function getRealmLabel() { return class_exists('NexoraRealm') ? \NexoraRealm::label($this->realm) : ucfirst($this->realm); }
    public function isEnabled() { return !empty($this->settings['enabled']); }
    public function settings() { return $this->settings; }

    public static function itemSectionLabels() {
        return array(
            0=>'Espadas', 1=>'Hachas', 2=>'Mazas y cetros', 3=>'Lanzas', 4=>'Arcos y ballestas', 5=>'Bastones',
            6=>'Escudos', 7=>'Cascos', 8=>'Armaduras', 9=>'Pantalones', 10=>'Guantes', 11=>'Botas',
            12=>'Alas y orbes', 13=>'Accesorios', 14=>'Consumibles', 15=>'Especiales'
        );
    }

    public static function suggestedCategory($section) {
        $section = (int)$section;
        if($section >= 0 && $section <= 5) return 'Armas';
        if($section >= 6 && $section <= 11) return 'Equipamiento';
        if($section === 12) return 'Alas y orbes';
        if($section === 13) return 'Accesorios';
        if($section === 14) return 'Consumibles';
        return 'Especiales';
    }

    public static function authoritativeCatalogPath() {
        return __PATH_ITEMSHOP_ROOT__.'data/item-catalog.json';
    }

    public static function authoritativeCatalog() {
        static $catalog = null;
        if($catalog !== null) return $catalog;
        $catalog = array('version'=>1,'source'=>'','count'=>0,'dimension_conflicts'=>0,'items'=>array(),'by_code'=>array());
        $path = self::authoritativeCatalogPath();
        if(!is_file($path) || !is_readable($path)) return $catalog;
        $json = @file_get_contents($path);
        $data = is_string($json) ? json_decode($json, true) : null;
        if(!is_array($data) || empty($data['items']) || !is_array($data['items'])) return $catalog;
        $catalog['version'] = isset($data['version']) ? (int)$data['version'] : 1;
        $catalog['source'] = isset($data['source']) ? (string)$data['source'] : '';
        $catalog['source_sha256'] = isset($data['source_sha256']) ? (string)$data['source_sha256'] : '';
        $catalog['source_hashes'] = isset($data['source_hashes']) && is_array($data['source_hashes']) ? $data['source_hashes'] : array();
        $catalog['dimension_conflicts'] = isset($data['dimension_conflicts']) ? (int)$data['dimension_conflicts'] : 0;
        foreach($data['items'] as $row) {
            if(!is_array($row)) continue;
            $section = isset($row['s']) ? (int)$row['s'] : -1;
            $index = isset($row['i']) ? (int)$row['i'] : -1;
            $width = isset($row['w']) ? (int)$row['w'] : 0;
            $height = isset($row['h']) ? (int)$row['h'] : 0;
            $name = trim((string)($row['n'] ?? ''));
            if($section < 0 || $section > 15 || $index < 0 || $name === '' || $width < 1 || $width > self::WAREHOUSE_COLUMNS || $height < 1 || $height > self::WAREHOUSE_ROWS) continue;
            $normalized = array(
                'section'=>$section,
                'index'=>$index,
                'name'=>$name,
                'width'=>$width,
                'height'=>$height,
                'slot'=>(string)($row['slot'] ?? ''),
                'skill'=>(int)($row['skill'] ?? 0),
                'have_serial'=>(int)($row['serial'] ?? 0),
                'have_option'=>(int)($row['option'] ?? 0),
                'drop_item'=>(int)($row['drop'] ?? 0),
                'excellent_type'=>(int)($row['excellent'] ?? 0),
                'level380'=>!empty($row['level380']) ? 1 : 0,
                'socket_capable'=>!empty($row['socket']) ? 1 : 0,
                'dimension_conflict'=>!empty($row['dimension_conflict']) ? 1 : 0,
                'txt_width'=>isset($row['txt_w']) ? (int)$row['txt_w'] : null,
                'txt_height'=>isset($row['txt_h']) ? (int)$row['txt_h'] : null,
                'encoder_compatible'=>$index <= 511,
            );
            $catalog['items'][] = $normalized;
            $catalog['by_code'][$section.':'.$index] = $normalized;
        }
        $catalog['count'] = count($catalog['items']);
        return $catalog;
    }

    public static function authoritativeItem($section, $index) {
        $catalog = self::authoritativeCatalog();
        $key = (int)$section.':'.(int)$index;
        return isset($catalog['by_code'][$key]) ? $catalog['by_code'][$key] : null;
    }

    public static function authoritativeAdminItems() {
        $catalog = self::authoritativeCatalog();
        return isset($catalog['items']) && is_array($catalog['items']) ? $catalog['items'] : array();
    }

    public static function smartOptionsCatalogPath() {
        return __PATH_ITEMSHOP_ROOT__.'data/item-options.json';
    }

    public static function smartOptionsCatalog() {
        static $catalog = null;
        if($catalog !== null) return $catalog;
        $catalog = array(
            'version'=>1,
            'source'=>'',
            'source_hashes'=>array(),
            'excellent_profiles'=>array(),
            'ancient'=>array('types'=>array(),'by_code'=>array()),
            'harmony'=>array('attack'=>array(),'defensive'=>array(),'staffs'=>array()),
            'socket'=>array('options'=>array(),'bonuses'=>array()),
        );
        $path = self::smartOptionsCatalogPath();
        if(!is_file($path) || !is_readable($path)) return $catalog;
        $json = @file_get_contents($path);
        $data = is_string($json) ? json_decode($json, true) : null;
        if(!is_array($data)) return $catalog;
        foreach(array('version','source','source_hashes','excellent_profiles','ancient','harmony','socket') as $key) {
            if(array_key_exists($key, $data)) $catalog[$key] = $data[$key];
        }
        if(!is_array($catalog['excellent_profiles'])) $catalog['excellent_profiles'] = array();
        if(!is_array($catalog['ancient'])) $catalog['ancient'] = array('types'=>array(),'by_code'=>array());
        if(!is_array($catalog['harmony'])) $catalog['harmony'] = array('attack'=>array(),'defensive'=>array(),'staffs'=>array());
        if(!is_array($catalog['socket'])) $catalog['socket'] = array('options'=>array(),'bonuses'=>array());
        return $catalog;
    }

    public static function smartOptionsCatalogReady() {
        $catalog = self::smartOptionsCatalog();
        return !empty($catalog['excellent_profiles'])
            && !empty($catalog['source_hashes'])
            && isset($catalog['ancient']['by_code']) && is_array($catalog['ancient']['by_code'])
            && !empty($catalog['harmony']['attack'])
            && !empty($catalog['harmony']['defensive'])
            && !empty($catalog['harmony']['staffs'])
            && isset($catalog['socket']['options']) && is_array($catalog['socket']['options'])
            && isset($catalog['socket']['bonuses']) && is_array($catalog['socket']['bonuses']);
    }

    public static function harmonyFamilyForSection($section) {
        $section = (int)$section;
        if($section >= 0 && $section <= 4) return 'attack';
        if($section === 5) return 'staffs';
        if($section >= 6 && $section <= 11) return 'defensive';
        return '';
    }

    public static function smartOptionsForItem($section, $index) {
        $section = (int)$section;
        $index = (int)$index;
        $catalog = self::smartOptionsCatalog();
        $authoritative = self::authoritativeItem($section, $index);
        $excellentType = is_array($authoritative) ? (int)$authoritative['excellent_type'] : 0;
        $profile = isset($catalog['excellent_profiles'][(string)$excellentType]) && is_array($catalog['excellent_profiles'][(string)$excellentType])
            ? $catalog['excellent_profiles'][(string)$excellentType]
            : array('name'=>'Sin perfil','options'=>array());
        $ancientMap = isset($catalog['ancient']['by_code']) && is_array($catalog['ancient']['by_code']) ? $catalog['ancient']['by_code'] : array();
        $ancient = isset($ancientMap[$section.':'.$index]) && is_array($ancientMap[$section.':'.$index]) ? $ancientMap[$section.':'.$index] : array('a'=>null,'b'=>null);
        $family = self::harmonyFamilyForSection($section);
        $harmony = ($family !== '' && isset($catalog['harmony'][$family]) && is_array($catalog['harmony'][$family])) ? $catalog['harmony'][$family] : array();
        $socketCapable = is_array($authoritative) && !empty($authoritative['socket_capable']);
        $socketOptions = array();
        if($socketCapable && isset($catalog['socket']['options']) && is_array($catalog['socket']['options'])) {
            foreach($catalog['socket']['options'] as $row) {
                if(!is_array($row)) continue;
                $type = isset($row['type']) ? (int)$row['type'] : -1;
                if(($section >= 0 && $section <= 5 && in_array($type,array(0,2),true)) || ($section >= 6 && $section <= 11 && in_array($type,array(1,2),true))) {
                    $socketOptions[] = $row;
                }
            }
        }
        $socketBonuses = array();
        if($socketCapable && isset($catalog['socket']['bonuses']) && is_array($catalog['socket']['bonuses'])) {
            foreach($catalog['socket']['bonuses'] as $row) {
                if(!is_array($row) || !isset($row['index'])) continue;
                // Socket_Options.xml usa Index 50 como "No Bonus" de interfaz. El HEX real sin bonus confirmado usa byte 10 = 0x00.
                if((int)$row['index'] === 50) continue;
                $socketBonuses[] = $row;
            }
        }
        return array(
            'excellent_type'=>$excellentType,
            'excellent_profile'=>$profile,
            'ancient'=>$ancient,
            'harmony_family'=>$family,
            'harmony'=>$socketCapable ? array() : $harmony,
            'socket_capable'=>$socketCapable ? 1 : 0,
            'socket_options'=>$socketOptions,
            'socket_bonuses'=>$socketBonuses,
            'level380'=>is_array($authoritative) && !empty($authoritative['level380']) ? 1 : 0,
            'skill'=>is_array($authoritative) ? (int)$authoritative['skill'] : 0,
            'have_option'=>is_array($authoritative) ? (int)$authoritative['have_option'] : 0,
        );
    }

    private static function ancientAllowedRaw($section, $index) {
        $smart = self::smartOptionsForItem($section, $index);
        $allowed = array(0);
        if(isset($smart['ancient']['a']) && is_array($smart['ancient']['a'])) { $allowed[] = 5; $allowed[] = 9; }
        if(isset($smart['ancient']['b']) && is_array($smart['ancient']['b'])) { $allowed[] = 6; $allowed[] = 10; }
        return array_values(array_unique($allowed));
    }

    private static function harmonyAllowedRaw($section) {
        $family = self::harmonyFamilyForSection($section);
        $catalog = self::smartOptionsCatalog();
        $allowed = array(0);
        if($family === '' || empty($catalog['harmony'][$family]) || !is_array($catalog['harmony'][$family])) return $allowed;
        foreach($catalog['harmony'][$family] as $group) {
            if(empty($group['levels']) || !is_array($group['levels'])) continue;
            foreach($group['levels'] as $level) if(isset($level['raw'])) $allowed[] = (int)$level['raw'];
        }
        return array_values(array_unique($allowed));
    }

    private static function socketAllowedValues($section) {
        $catalog = self::smartOptionsCatalog();
        $allowed = array(254,255);
        if(isset($catalog['socket']['options']) && is_array($catalog['socket']['options'])) {
            foreach($catalog['socket']['options'] as $row) {
                if(!is_array($row) || !isset($row['index'],$row['type'])) continue;
                $type=(int)$row['type'];
                if(((int)$section <= 5 && in_array($type,array(0,2),true)) || ((int)$section >= 6 && (int)$section <= 11 && in_array($type,array(1,2),true))) $allowed[]=(int)$row['index'];
            }
        }
        return array_values(array_unique($allowed));
    }

    private static function socketAllowedBonuses() {
        $catalog = self::smartOptionsCatalog();
        $allowed = array(0);
        if(isset($catalog['socket']['bonuses']) && is_array($catalog['socket']['bonuses'])) {
            foreach($catalog['socket']['bonuses'] as $row) {
                if(!is_array($row) || !isset($row['index'])) continue;
                if((int)$row['index'] === 50) continue;
                $allowed[] = (int)$row['index'];
            }
        }
        return array_values(array_unique($allowed));
    }

    private function loadSettings() {
        $path = self::configPath($this->realm);
        if(!is_file($path) || !is_readable($path)) throw new \RuntimeException('No se pudo cargar la configuración de la tienda.');
        $xml = simplexml_load_file($path);
        if(!$xml) throw new \RuntimeException('La configuración de la tienda no es válida.');
        $currency = strtoupper(trim((string)$xml->currency));
        if(!in_array($currency, array('WCOINC','WCOINP','GOBLINPOINT'), true)) $currency = 'WCOINC';
        return array(
            'enabled' => (int)$xml->enabled === 1,
            'title' => trim((string)$xml->title) !== '' ? trim((string)$xml->title) : 'Arsenal Nexora',
            'intro' => trim((string)$xml->intro),
            'currency' => $currency,
            'serial_server_number' => max(0, (int)$xml->serial_server_number),
        );
    }

    public function saveSettings($input) {
        $enabled = isset($input['enabled']) ? (int)$input['enabled'] : 0;
        $title = trim((string)($input['title'] ?? ''));
        $intro = trim((string)($input['intro'] ?? ''));
        $currency = strtoupper(trim((string)($input['currency'] ?? 'WCOINC')));
        $serverNumber = isset($input['serial_server_number']) ? (int)$input['serial_server_number'] : -1;
        if(!in_array($enabled, array(0,1), true)) throw new \InvalidArgumentException('Estado de tienda inválido.');
        if($title === '' || mb_strlen($title) > 100) throw new \InvalidArgumentException('El título debe tener entre 1 y 100 caracteres.');
        if(mb_strlen($intro) > 500) throw new \InvalidArgumentException('La introducción no puede superar 500 caracteres.');
        if(!in_array($currency, array('WCOINC','WCOINP','GOBLINPOINT'), true)) throw new \InvalidArgumentException('Moneda no permitida.');
        if($serverNumber < 0 || $serverNumber > 9999) throw new \InvalidArgumentException('Número de GameServer inválido.');

        $path = self::configPath($this->realm);
        if(!is_writable($path)) throw new \RuntimeException('La configuración de la tienda no tiene permiso de escritura.');
        $xml = simplexml_load_file($path);
        if(!$xml) throw new \RuntimeException('No se pudo abrir la configuración de la tienda.');
        $xml->enabled = $enabled;
        $xml->title = $title;
        $xml->intro = $intro;
        $xml->currency = $currency;
        $xml->serial_server_number = $serverNumber;
        if(!$xml->asXML($path)) throw new \RuntimeException('No se pudo guardar la configuración.');
        $this->settings = $this->loadSettings();
        if(class_exists('NexoraAudit')) {
            \NexoraAudit::log('itemshop.settings_update', 'itemshop', $this->realm, null, $this->settings, $this->realm);
        }
    }

    public function installSchema() {
        $queries = array(
            "IF OBJECT_ID(N'[dbo].[".self::ITEMS_TABLE."]', N'U') IS NULL BEGIN
                CREATE TABLE [dbo].[".self::ITEMS_TABLE."](
                    [id] int IDENTITY(1,1) NOT NULL PRIMARY KEY,
                    [public_key] char(32) NOT NULL UNIQUE,
                    [name] nvarchar(100) NOT NULL,
                    [category] nvarchar(50) NOT NULL,
                    [description] nvarchar(500) NULL,
                    [item_section] tinyint NOT NULL,
                    [item_index] smallint NOT NULL,
                    [item_level] tinyint NOT NULL,
                    [has_skill] bit NOT NULL CONSTRAINT [DF_NIS_skill] DEFAULT(0),
                    [has_luck] bit NOT NULL CONSTRAINT [DF_NIS_luck] DEFAULT(0),
                    [option_level] tinyint NOT NULL CONSTRAINT [DF_NIS_option] DEFAULT(0),
                    [excellent_mask] tinyint NOT NULL CONSTRAINT [DF_NIS_exc] DEFAULT(0),
                    [ancient_option] tinyint NOT NULL CONSTRAINT [DF_NIS_ancient] DEFAULT(0),
                    [harmony_option] tinyint NOT NULL CONSTRAINT [DF_NIS_harmony] DEFAULT(0),
                    [socket_count] tinyint NOT NULL CONSTRAINT [DF_NIS_socket] DEFAULT(0),
                    [option_380] bit NOT NULL CONSTRAINT [DF_NIS_380] DEFAULT(0),
                    [socket_bonus] tinyint NOT NULL CONSTRAINT [DF_NIS_socket_bonus] DEFAULT(0),
                    [socket_option_1] tinyint NOT NULL CONSTRAINT [DF_NIS_socket_1] DEFAULT(255),
                    [socket_option_2] tinyint NOT NULL CONSTRAINT [DF_NIS_socket_2] DEFAULT(255),
                    [socket_option_3] tinyint NOT NULL CONSTRAINT [DF_NIS_socket_3] DEFAULT(255),
                    [socket_option_4] tinyint NOT NULL CONSTRAINT [DF_NIS_socket_4] DEFAULT(255),
                    [socket_option_5] tinyint NOT NULL CONSTRAINT [DF_NIS_socket_5] DEFAULT(255),
                    [durability] tinyint NOT NULL CONSTRAINT [DF_NIS_durability] DEFAULT(255),
                    [width] tinyint NOT NULL,
                    [height] tinyint NOT NULL,
                    [price_wc] int NOT NULL,
                    [sale_price_wc] int NULL,
                    [sale_start] datetime2 NULL,
                    [sale_end] datetime2 NULL,
                    [stock] int NOT NULL CONSTRAINT [DF_NIS_stock] DEFAULT(-1),
                    [max_per_account] int NOT NULL CONSTRAINT [DF_NIS_limit] DEFAULT(0),
                    [image_file] nvarchar(255) NULL,
                    [active] bit NOT NULL CONSTRAINT [DF_NIS_active] DEFAULT(0),
                    [featured] bit NOT NULL CONSTRAINT [DF_NIS_featured] DEFAULT(0),
                    [sort_order] int NOT NULL CONSTRAINT [DF_NIS_order] DEFAULT(100),
                    [created_at] datetime2 NOT NULL CONSTRAINT [DF_NIS_created] DEFAULT(SYSUTCDATETIME()),
                    [updated_at] datetime2 NOT NULL CONSTRAINT [DF_NIS_updated] DEFAULT(SYSUTCDATETIME()),
                    CONSTRAINT [CK_NIS_section] CHECK ([item_section] BETWEEN 0 AND 15),
                    CONSTRAINT [CK_NIS_index] CHECK ([item_index] BETWEEN 0 AND 511),
                    CONSTRAINT [CK_NIS_level] CHECK ([item_level] BETWEEN 0 AND 15),
                    CONSTRAINT [CK_NIS_option] CHECK ([option_level] BETWEEN 0 AND 7),
                    CONSTRAINT [CK_NIS_exc] CHECK ([excellent_mask] BETWEEN 0 AND 63),
                    CONSTRAINT [CK_NIS_ancient] CHECK ([ancient_option] BETWEEN 0 AND 15),
                    CONSTRAINT [CK_NIS_socket] CHECK ([socket_count] BETWEEN 0 AND 5),
                    CONSTRAINT [CK_NIS_size] CHECK ([width] BETWEEN 1 AND 8 AND [height] BETWEEN 1 AND 15),
                    CONSTRAINT [CK_NIS_price] CHECK ([price_wc] > 0 AND ([sale_price_wc] IS NULL OR [sale_price_wc] > 0)),
                    CONSTRAINT [CK_NIS_stock] CHECK ([stock] >= -1),
                    CONSTRAINT [CK_NIS_limit] CHECK ([max_per_account] >= 0)
                );
                CREATE INDEX [IX_NIS_catalog] ON [dbo].[".self::ITEMS_TABLE."]([active],[featured],[sort_order],[id]);
                CREATE INDEX [IX_NIS_code] ON [dbo].[".self::ITEMS_TABLE."]([item_section],[item_index]);
            END",
            "IF OBJECT_ID(N'[dbo].[".self::ITEMS_TABLE."]', N'U') IS NOT NULL BEGIN
                IF EXISTS (SELECT 1 FROM sys.check_constraints WHERE [name]=N'CK_NIS_index' AND [parent_object_id]=OBJECT_ID(N'[dbo].[".self::ITEMS_TABLE."]') AND [definition] NOT LIKE '%511%')
                    ALTER TABLE [dbo].[".self::ITEMS_TABLE."] DROP CONSTRAINT [CK_NIS_index];
                IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE [name]=N'CK_NIS_index' AND [parent_object_id]=OBJECT_ID(N'[dbo].[".self::ITEMS_TABLE."]')) BEGIN
                    ALTER TABLE [dbo].[".self::ITEMS_TABLE."] WITH CHECK ADD CONSTRAINT [CK_NIS_index] CHECK ([item_index] BETWEEN 0 AND 511);
                    ALTER TABLE [dbo].[".self::ITEMS_TABLE."] CHECK CONSTRAINT [CK_NIS_index];
                END
            END",
            "IF OBJECT_ID(N'[dbo].[".self::ITEMS_TABLE."]', N'U') IS NOT NULL BEGIN
                IF COL_LENGTH(N'dbo.".self::ITEMS_TABLE."', N'option_380') IS NULL ALTER TABLE [dbo].[".self::ITEMS_TABLE."] ADD [option_380] bit NOT NULL CONSTRAINT [DF_NIS_380] DEFAULT(0);
                IF COL_LENGTH(N'dbo.".self::ITEMS_TABLE."', N'socket_bonus') IS NULL ALTER TABLE [dbo].[".self::ITEMS_TABLE."] ADD [socket_bonus] tinyint NOT NULL CONSTRAINT [DF_NIS_socket_bonus] DEFAULT(0);
                IF COL_LENGTH(N'dbo.".self::ITEMS_TABLE."', N'socket_option_1') IS NULL ALTER TABLE [dbo].[".self::ITEMS_TABLE."] ADD [socket_option_1] tinyint NOT NULL CONSTRAINT [DF_NIS_socket_1] DEFAULT(255);
                IF COL_LENGTH(N'dbo.".self::ITEMS_TABLE."', N'socket_option_2') IS NULL ALTER TABLE [dbo].[".self::ITEMS_TABLE."] ADD [socket_option_2] tinyint NOT NULL CONSTRAINT [DF_NIS_socket_2] DEFAULT(255);
                IF COL_LENGTH(N'dbo.".self::ITEMS_TABLE."', N'socket_option_3') IS NULL ALTER TABLE [dbo].[".self::ITEMS_TABLE."] ADD [socket_option_3] tinyint NOT NULL CONSTRAINT [DF_NIS_socket_3] DEFAULT(255);
                IF COL_LENGTH(N'dbo.".self::ITEMS_TABLE."', N'socket_option_4') IS NULL ALTER TABLE [dbo].[".self::ITEMS_TABLE."] ADD [socket_option_4] tinyint NOT NULL CONSTRAINT [DF_NIS_socket_4] DEFAULT(255);
                IF COL_LENGTH(N'dbo.".self::ITEMS_TABLE."', N'socket_option_5') IS NULL ALTER TABLE [dbo].[".self::ITEMS_TABLE."] ADD [socket_option_5] tinyint NOT NULL CONSTRAINT [DF_NIS_socket_5] DEFAULT(255);
            END",
            "IF OBJECT_ID(N'[dbo].[".self::ITEMS_TABLE."]', N'U') IS NOT NULL BEGIN
                UPDATE [dbo].[".self::ITEMS_TABLE."] SET
                    [socket_option_1]=CASE WHEN [socket_count]>=1 THEN 254 ELSE 255 END,
                    [socket_option_2]=CASE WHEN [socket_count]>=2 THEN 254 ELSE 255 END,
                    [socket_option_3]=CASE WHEN [socket_count]>=3 THEN 254 ELSE 255 END,
                    [socket_option_4]=CASE WHEN [socket_count]>=4 THEN 254 ELSE 255 END,
                    [socket_option_5]=CASE WHEN [socket_count]>=5 THEN 254 ELSE 255 END
                WHERE [socket_count]>0 AND [socket_option_1]=255 AND [socket_option_2]=255 AND [socket_option_3]=255 AND [socket_option_4]=255 AND [socket_option_5]=255;
            END",
            "IF OBJECT_ID(N'[dbo].[".self::PRESETS_TABLE."]', N'U') IS NULL BEGIN
                CREATE TABLE [dbo].[".self::PRESETS_TABLE."](
                    [id] int IDENTITY(1,1) NOT NULL PRIMARY KEY,
                    [name] nvarchar(80) NOT NULL,
                    [preset_key] varchar(50) NOT NULL,
                    [price_wc] int NOT NULL,
                    [stock] int NOT NULL CONSTRAINT [DF_NISP_stock] DEFAULT(-1),
                    [max_per_account] int NOT NULL CONSTRAINT [DF_NISP_limit] DEFAULT(0),
                    [sort_order] int NOT NULL CONSTRAINT [DF_NISP_order] DEFAULT(100),
                    [category_override] nvarchar(50) NULL,
                    [created_at] datetime2 NOT NULL CONSTRAINT [DF_NISP_created] DEFAULT(SYSUTCDATETIME()),
                    [updated_at] datetime2 NOT NULL CONSTRAINT [DF_NISP_updated] DEFAULT(SYSUTCDATETIME()),
                    CONSTRAINT [CK_NISP_price] CHECK ([price_wc] > 0),
                    CONSTRAINT [CK_NISP_stock] CHECK ([stock] >= -1),
                    CONSTRAINT [CK_NISP_limit] CHECK ([max_per_account] >= 0),
                    CONSTRAINT [CK_NISP_order] CHECK ([sort_order] BETWEEN -100000 AND 100000)
                );
                CREATE UNIQUE INDEX [UX_NISP_name] ON [dbo].[".self::PRESETS_TABLE."]([name]);
            END",
            "IF OBJECT_ID(N'[dbo].[".self::BULK_HISTORY_TABLE."]', N'U') IS NULL BEGIN
                CREATE TABLE [dbo].[".self::BULK_HISTORY_TABLE."](
                    [id] bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
                    [operation_key] char(32) NOT NULL UNIQUE,
                    [action] varchar(40) NOT NULL,
                    [description] nvarchar(200) NOT NULL,
                    [affected] int NOT NULL,
                    [snapshot] nvarchar(max) NOT NULL,
                    [rolled_back] bit NOT NULL CONSTRAINT [DF_NISBH_rolled] DEFAULT(0),
                    [rollback_count] int NOT NULL CONSTRAINT [DF_NISBH_restore] DEFAULT(0),
                    [conflict_count] int NOT NULL CONSTRAINT [DF_NISBH_conflict] DEFAULT(0),
                    [created_at] datetime2 NOT NULL CONSTRAINT [DF_NISBH_created] DEFAULT(SYSUTCDATETIME()),
                    [rolled_back_at] datetime2 NULL
                );
                CREATE INDEX [IX_NISBH_created] ON [dbo].[".self::BULK_HISTORY_TABLE."]([created_at] DESC);
            END",
            "IF OBJECT_ID(N'[dbo].[".self::COLLECTIONS_TABLE."]', N'U') IS NULL BEGIN
                CREATE TABLE [dbo].[".self::COLLECTIONS_TABLE."](
                    [id] int IDENTITY(1,1) NOT NULL PRIMARY KEY,
                    [name] nvarchar(100) NOT NULL,
                    [description] nvarchar(300) NULL,
                    [created_at] datetime2 NOT NULL CONSTRAINT [DF_NISC_created] DEFAULT(SYSUTCDATETIME()),
                    [updated_at] datetime2 NOT NULL CONSTRAINT [DF_NISC_updated] DEFAULT(SYSUTCDATETIME())
                );
                CREATE UNIQUE INDEX [UX_NISC_name] ON [dbo].[".self::COLLECTIONS_TABLE."]([name]);
            END",
            "IF OBJECT_ID(N'[dbo].[".self::COLLECTION_ITEMS_TABLE."]', N'U') IS NULL BEGIN
                CREATE TABLE [dbo].[".self::COLLECTION_ITEMS_TABLE."](
                    [collection_id] int NOT NULL,
                    [item_id] int NOT NULL,
                    [sort_order] int NOT NULL CONSTRAINT [DF_NISCI_order] DEFAULT(100),
                    [created_at] datetime2 NOT NULL CONSTRAINT [DF_NISCI_created] DEFAULT(SYSUTCDATETIME()),
                    CONSTRAINT [PK_NISCI] PRIMARY KEY ([collection_id],[item_id]),
                    CONSTRAINT [FK_NISCI_collection] FOREIGN KEY ([collection_id]) REFERENCES [dbo].[".self::COLLECTIONS_TABLE."]([id]) ON DELETE CASCADE,
                    CONSTRAINT [FK_NISCI_item] FOREIGN KEY ([item_id]) REFERENCES [dbo].[".self::ITEMS_TABLE."]([id]) ON DELETE CASCADE
                );
                CREATE INDEX [IX_NISCI_item] ON [dbo].[".self::COLLECTION_ITEMS_TABLE."]([item_id],[collection_id]);
            END",
            "IF OBJECT_ID(N'[dbo].[".self::COLLECTION_ITEMS_TABLE."]', N'U') IS NOT NULL BEGIN
                IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE [name]=N'FK_NISCI_collection' AND [parent_object_id]=OBJECT_ID(N'[dbo].[".self::COLLECTION_ITEMS_TABLE."]'))
                    ALTER TABLE [dbo].[".self::COLLECTION_ITEMS_TABLE."] WITH CHECK ADD CONSTRAINT [FK_NISCI_collection] FOREIGN KEY ([collection_id]) REFERENCES [dbo].[".self::COLLECTIONS_TABLE."]([id]) ON DELETE CASCADE;
                IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE [name]=N'FK_NISCI_item' AND [parent_object_id]=OBJECT_ID(N'[dbo].[".self::COLLECTION_ITEMS_TABLE."]'))
                    ALTER TABLE [dbo].[".self::COLLECTION_ITEMS_TABLE."] WITH CHECK ADD CONSTRAINT [FK_NISCI_item] FOREIGN KEY ([item_id]) REFERENCES [dbo].[".self::ITEMS_TABLE."]([id]) ON DELETE CASCADE;
            END",
            "IF OBJECT_ID(N'[dbo].[".self::CAMPAIGNS_TABLE."]', N'U') IS NULL BEGIN
                CREATE TABLE [dbo].[".self::CAMPAIGNS_TABLE."](
                    [id] bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
                    [name] nvarchar(120) NOT NULL,
                    [collection_id] int NOT NULL,
                    [collection_name] nvarchar(100) NOT NULL,
                    [status] varchar(20) NOT NULL CONSTRAINT [DF_NISCAM_status] DEFAULT('scheduled'),
                    [start_at] datetime2 NOT NULL,
                    [end_at] datetime2 NOT NULL,
                    [publish_on_start] bit NOT NULL CONSTRAINT [DF_NISCAM_publish] DEFAULT(0),
                    [feature_on_start] bit NOT NULL CONSTRAINT [DF_NISCAM_feature] DEFAULT(0),
                    [discount_percent] tinyint NULL,
                    [restore_on_end] bit NOT NULL CONSTRAINT [DF_NISCAM_restore] DEFAULT(1),
                    [snapshot] nvarchar(max) NULL,
                    [affected] int NOT NULL CONSTRAINT [DF_NISCAM_affected] DEFAULT(0),
                    [restored_count] int NOT NULL CONSTRAINT [DF_NISCAM_restored] DEFAULT(0),
                    [conflict_count] int NOT NULL CONSTRAINT [DF_NISCAM_conflicts] DEFAULT(0),
                    [last_error] nvarchar(500) NULL,
                    [started_at] datetime2 NULL,
                    [ended_at] datetime2 NULL,
                    [created_at] datetime2 NOT NULL CONSTRAINT [DF_NISCAM_created] DEFAULT(SYSUTCDATETIME()),
                    [updated_at] datetime2 NOT NULL CONSTRAINT [DF_NISCAM_updated] DEFAULT(SYSUTCDATETIME()),
                    CONSTRAINT [CK_NISCAM_discount] CHECK ([discount_percent] IS NULL OR [discount_percent] BETWEEN 1 AND 95),
                    CONSTRAINT [CK_NISCAM_dates] CHECK ([end_at] > [start_at])
                );
                CREATE INDEX [IX_NISCAM_due] ON [dbo].[".self::CAMPAIGNS_TABLE."]([status],[start_at],[end_at]);
                CREATE INDEX [IX_NISCAM_collection] ON [dbo].[".self::CAMPAIGNS_TABLE."]([collection_id],[status]);
            END",
            "IF OBJECT_ID(N'[dbo].[".self::ORDERS_TABLE."]', N'U') IS NULL BEGIN
                CREATE TABLE [dbo].[".self::ORDERS_TABLE."](
                    [id] bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
                    [user_id] int NOT NULL,
                    [account_username] varchar(50) NOT NULL,
                    [item_id] int NOT NULL,
                    [item_name] nvarchar(100) NOT NULL,
                    [item_snapshot] nvarchar(max) NOT NULL,
                    [price_paid] int NOT NULL,
                    [currency] varchar(20) NOT NULL,
                    [warehouse_slot] smallint NOT NULL,
                    [item_serial] bigint NOT NULL,
                    [status] varchar(20) NOT NULL,
                    [ip_address] varchar(45) NULL,
                    [created_at] datetime2 NOT NULL CONSTRAINT [DF_NISO_created] DEFAULT(SYSUTCDATETIME())
                );
                CREATE INDEX [IX_NISO_account] ON [dbo].[".self::ORDERS_TABLE."]([account_username],[item_id],[status]);
                CREATE INDEX [IX_NISO_created] ON [dbo].[".self::ORDERS_TABLE."]([created_at] DESC);
            END"
        );
        foreach($queries as $query) {
            if(!$this->db->query($query)) throw new \RuntimeException('No se pudo instalar el esquema de la tienda. Revisa permisos CREATE TABLE del usuario web.');
        }
        if($this->schemaReady()) $this->ensureRewardDeliverySchema();
        if($this->campaignSchemaReady()) $this->ensureCampaignCron();
        return $this->schemaReady();
    }

    public function schemaReady() {
        foreach(array(self::ITEMS_TABLE,self::ORDERS_TABLE) as $table) {
            $row = $this->db->query_fetch_single("SELECT name FROM sysobjects WHERE xtype='U' AND name=?", array($table));
            if(!is_array($row)) return false;
        }
        return true;
    }

    public function catalogOpsSchemaReady() {
        foreach(array(self::PRESETS_TABLE,self::BULK_HISTORY_TABLE) as $table) {
            $row = $this->db->query_fetch_single("SELECT name FROM sysobjects WHERE xtype='U' AND name=?", array($table));
            if(!is_array($row)) return false;
        }
        return true;
    }

    public function campaignSchemaReady() {
        foreach(array(self::COLLECTIONS_TABLE,self::COLLECTION_ITEMS_TABLE,self::CAMPAIGNS_TABLE) as $table) {
            $row = $this->db->query_fetch_single("SELECT name FROM sysobjects WHERE xtype='U' AND name=?", array($table));
            if(!is_array($row)) return false;
        }
        return true;
    }

    public function campaignCronReady() {
        if(!defined('WEBENGINE_CRON') || !defined('__PATH_CRON__') || !is_file(__PATH_CRON__.'itemshop_campaigns.php')) return false;
        try {
            $platform = \Connection::Database('Platform', $this->realm);
            $row = $platform->query_fetch_single('SELECT [cron_status],[cron_run_time],[cron_file_md5] FROM '.WEBENGINE_CRON.' WHERE [cron_file_run]=?', array('itemshop_campaigns.php'));
            if(!is_array($row) || (int)($row['cron_status'] ?? 0) !== 1 || (int)($row['cron_run_time'] ?? 0) !== 60) return false;
            $expected=md5_file(__PATH_CRON__.'itemshop_campaigns.php');
            return is_string($expected) && hash_equals((string)$expected,(string)($row['cron_file_md5'] ?? ''));
        } catch(\Throwable $e) { return false; }
    }

    public function ensureCampaignCron() {
        if(!defined('WEBENGINE_CRON') || !defined('__PATH_CRON__')) throw new \RuntimeException('Cron Manager no está disponible en esta instalación.');
        $file = 'itemshop_campaigns.php';
        $path = __PATH_CRON__.$file;
        if(!is_file($path)) throw new \RuntimeException('Falta el runner de campañas de Arsenal.');
        $platform = \Connection::Database('Platform', $this->realm);
        $row = $platform->query_fetch_single('SELECT [cron_id] FROM '.WEBENGINE_CRON.' WHERE [cron_file_run]=?', array($file));
        $md5 = md5_file($path);
        if(is_array($row) && !empty($row['cron_id'])) {
            $ok = $platform->query('UPDATE '.WEBENGINE_CRON.' SET [cron_name]=?,[cron_run_time]=?,[cron_status]=1,[cron_file_md5]=? WHERE [cron_id]=?', array('Arsenal Campaign Manager',60,$md5,(int)$row['cron_id']));
            if(!$ok) throw new \RuntimeException('No se pudo actualizar la tarea programada de campañas.');
        } else {
            $ok = $platform->query('INSERT INTO '.WEBENGINE_CRON.' ([cron_name],[cron_file_run],[cron_run_time],[cron_status],[cron_protected],[cron_file_md5]) VALUES (?,?,?,?,?,?)', array('Arsenal Campaign Manager',$file,60,1,0,$md5));
            if(!$ok) throw new \RuntimeException('No se pudo registrar la tarea programada de campañas.');
        }
        if(function_exists('nexoraCronResetState')) nexoraCronResetState($file, $this->realm);
        return true;
    }

    private function extendedIndexSchemaReady() {
        if(!$this->schemaReady()) return false;
        $row = $this->db->query_fetch_single(
            "SELECT [definition] FROM sys.check_constraints WHERE [name]=N'CK_NIS_index' AND [parent_object_id]=OBJECT_ID(N'[dbo].[".self::ITEMS_TABLE."]')"
        );
        if(!is_array($row) || !isset($row['definition'])) return false;
        return strpos((string)$row['definition'], '511') !== false;
    }

    public function smartOptionsSchemaReady() {
        if(!$this->schemaReady()) return false;
        foreach(array('option_380','socket_bonus','socket_option_1','socket_option_2','socket_option_3','socket_option_4','socket_option_5') as $column) {
            if(!$this->columnExists(self::ITEMS_TABLE, $column)) return false;
        }
        return true;
    }

    public function diagnostics() {
        $checks = array();
        $checks['schema'] = $this->schemaReady();
        $checks['index511'] = $this->extendedIndexSchemaReady();
        $checks['smart_options'] = $this->smartOptionsSchemaReady() && self::smartOptionsCatalogReady();
        $checks['catalog_ops'] = $this->catalogOpsSchemaReady();
        $checks['campaigns'] = $this->campaignSchemaReady();
        $checks['campaign_cron'] = $this->campaignCronReady();
        $checks['purchase_center'] = $this->purchaseCenterReady();
        $checks['currency'] = $this->columnExists($this->currencyTable(), 'AccountID')
            && $this->columnExists($this->currencyTable(), $this->currencyColumn());
        $checks['warehouse'] = $this->columnExists('warehouse', 'Items') && $this->columnExists('warehouse', 'DbVersion');
        $serial = $this->db->query_fetch_single('SELECT [ItemCount] FROM [GameServerInfo] WHERE [Number]=?', array($this->settings['serial_server_number']));
        $checks['serial'] = is_array($serial);
        $checks['images'] = is_dir($this->imageDirectory()) && is_writable($this->imageDirectory());
        return $checks;
    }

    public function getCatalog() {
        if(!$this->schemaReady()) return array();
        $rows = $this->db->query_fetch('SELECT * FROM ['.self::ITEMS_TABLE.'] WHERE [active]=1 AND [stock]<>0 ORDER BY [featured] DESC,[sort_order] ASC,[id] DESC');
        return is_array($rows) ? $rows : array();
    }

    public function getAdminItems() {
        if(!$this->schemaReady()) return array();
        $rows = $this->db->query_fetch('SELECT * FROM ['.self::ITEMS_TABLE.'] ORDER BY [active] DESC,[featured] DESC,[sort_order] ASC,[id] DESC');
        return is_array($rows) ? $rows : array();
    }

    public function getAdminCategories() {
        if(!$this->schemaReady()) return array();
        $rows = $this->db->query_fetch('SELECT DISTINCT [category] FROM ['.self::ITEMS_TABLE.'] WHERE [category] IS NOT NULL AND LTRIM(RTRIM([category]))<>\'\' ORDER BY [category] ASC');
        $categories = array();
        if(is_array($rows)) foreach($rows as $row) {
            $value = trim((string)($row['category'] ?? ''));
            if($value !== '') $categories[] = $value;
        }
        return $categories;
    }

    public function getAdminIdentityHints() {
        if(!$this->schemaReady()) return array();
        $rows = $this->db->query_fetch('SELECT [item_section],[item_index],[name],[category],[width],[height] FROM ['.self::ITEMS_TABLE.'] ORDER BY [updated_at] DESC,[id] DESC');
        $hints = array();
        if(is_array($rows)) foreach($rows as $row) {
            $section = (int)($row['item_section'] ?? -1);
            $index = (int)($row['item_index'] ?? -1);
            if($section < 0 || $section > 15 || $index < 0 || $index > 511) continue;
            $key = $section.':'.$index;
            if(isset($hints[$key])) continue;
            $hints[$key] = array(
                'name'=>(string)($row['name'] ?? ''),
                'category'=>(string)($row['category'] ?? ''),
                'width'=>(int)($row['width'] ?? 0),
                'height'=>(int)($row['height'] ?? 0),
            );
        }
        return $hints;
    }

    public function getAdminCodeMap() {
        $map = array();
        foreach($this->getAdminItems() as $row) {
            $section = (int)($row['item_section'] ?? -1);
            $index = (int)($row['item_index'] ?? -1);
            if($section < 0 || $section > 15 || $index < 0 || $index > 511) continue;
            $code = $section.':'.$index;
            if(isset($map[$code])) continue;
            $map[$code] = array(
                'id'=>(int)($row['id'] ?? 0),
                'name'=>(string)($row['name'] ?? ''),
                'active'=>!empty($row['active']) ? 1 : 0,
            );
        }
        return $map;
    }

    public function getBulkPresets() {
        if(!$this->catalogOpsSchemaReady()) return array();
        $rows = $this->db->query_fetch('SELECT * FROM ['.self::PRESETS_TABLE.'] ORDER BY [name] ASC,[id] ASC');
        return is_array($rows) ? $rows : array();
    }

    public function saveBulkPreset($input) {
        if(!$this->catalogOpsSchemaReady()) throw new \RuntimeException('Instala / valida las tablas de Arsenal 1.6.8 antes de guardar presets.');
        $name = trim((string)($input['preset_name'] ?? ''));
        if($name === '' || mb_strlen($name) < 2 || mb_strlen($name) > 80) throw new \InvalidArgumentException('El nombre del preset debe tener entre 2 y 80 caracteres.');
        $options = $this->normalizeBulkOptions($input);
        $id = isset($input['preset_id']) && filter_var($input['preset_id'], FILTER_VALIDATE_INT) !== false ? (int)$input['preset_id'] : 0;
        $duplicate = $this->db->query_fetch_single('SELECT TOP 1 [id] FROM ['.self::PRESETS_TABLE.'] WHERE LOWER([name])=LOWER(?) AND [id]<>?', array($name,$id));
        if(is_array($duplicate)) throw new \InvalidArgumentException('Ya existe un preset con ese nombre en este reino.');
        if($id > 0) {
            $affected = $this->db->query_affected(
                'UPDATE ['.self::PRESETS_TABLE.'] SET [name]=?,[preset_key]=?,[price_wc]=?,[stock]=?,[max_per_account]=?,[sort_order]=?,[category_override]=?,[updated_at]=SYSUTCDATETIME() WHERE [id]=?',
                array($name,$options['preset'],$options['price_wc'],$options['stock'],$options['max_per_account'],$options['sort_order'],$options['category_override'] === '' ? null : $options['category_override'],$id)
            );
            if($affected !== 1) throw new \RuntimeException('No se pudo actualizar el preset.');
        } else {
            $affected = $this->db->query_affected(
                'INSERT INTO ['.self::PRESETS_TABLE.'] ([name],[preset_key],[price_wc],[stock],[max_per_account],[sort_order],[category_override]) VALUES (?,?,?,?,?,?,?)',
                array($name,$options['preset'],$options['price_wc'],$options['stock'],$options['max_per_account'],$options['sort_order'],$options['category_override'] === '' ? null : $options['category_override'])
            );
            if($affected !== 1) throw new \RuntimeException('No se pudo guardar el preset.');
            $created = $this->db->query_fetch_single('SELECT TOP 1 [id] FROM ['.self::PRESETS_TABLE.'] WHERE [name]=? ORDER BY [id] DESC', array($name));
            $id = is_array($created) ? (int)($created['id'] ?? 0) : 0;
        }
        if(class_exists('NexoraAudit')) {
            \NexoraAudit::log('itemshop.preset_save', 'itemshop_preset', $id, null, array('name'=>$name,'options'=>$options), $this->realm);
        }
        return $id;
    }

    public function deleteBulkPreset($id) {
        if(!$this->catalogOpsSchemaReady()) throw new \RuntimeException('Instala / valida las tablas de Arsenal 1.6.8 antes de administrar presets.');
        if(filter_var($id, FILTER_VALIDATE_INT) === false || (int)$id < 1) throw new \InvalidArgumentException('Preset inválido.');
        $id = (int)$id;
        $row = $this->db->query_fetch_single('SELECT [name] FROM ['.self::PRESETS_TABLE.'] WHERE [id]=?', array($id));
        if(!is_array($row)) throw new \RuntimeException('El preset ya no existe.');
        $affected = $this->db->query_affected('DELETE FROM ['.self::PRESETS_TABLE.'] WHERE [id]=?', array($id));
        if($affected !== 1) throw new \RuntimeException('No se pudo eliminar el preset.');
        if(class_exists('NexoraAudit')) {
            \NexoraAudit::log('itemshop.preset_delete', 'itemshop_preset', $id, array('name'=>(string)($row['name'] ?? '')), null, $this->realm);
        }
        return true;
    }

    private function normalizeBulkCodes($codes) {
        if(is_string($codes)) $codes = preg_split('/[\\s,;]+/', trim($codes), -1, PREG_SPLIT_NO_EMPTY);
        if(!is_array($codes)) return array();
        $normalized = array();
        foreach($codes as $code) {
            if(is_array($code) && isset($code['section'],$code['index'])) $code = (int)$code['section'].':'.(int)$code['index'];
            $code = trim((string)$code);
            if(!preg_match('/^(\\d{1,2}):(\\d{1,3})$/', $code, $match)) continue;
            $section = (int)$match[1];
            $index = (int)$match[2];
            if($section < 0 || $section > 15 || $index < 0 || $index > 511) continue;
            $normalized[$section.':'.$index] = array('section'=>$section,'index'=>$index);
        }
        return $normalized;
    }

    private function normalizeBulkOptions($options) {
        if(!is_array($options)) $options = array();
        $preset = isset($options['preset']) ? (string)$options['preset'] : 'full_excellent_0';
        if(!in_array($preset, array('full_excellent_0','full_excellent_socket5_0','skill_luck_0','skill_luck_socket5_0'), true)) throw new \InvalidArgumentException('Preset de carga masiva inválido.');

        $price = $options['price_wc'] ?? null;
        $stock = $options['stock'] ?? -1;
        $limit = $options['max_per_account'] ?? 0;
        $sortOrder = $options['sort_order'] ?? 100;
        $categoryOverride = trim((string)($options['category_override'] ?? ''));
        if(mb_strlen($categoryOverride) > 50) throw new \InvalidArgumentException('La categoría del preset no puede superar 50 caracteres.');
        foreach(array('price_wc'=>$price,'stock'=>$stock,'max_per_account'=>$limit,'sort_order'=>$sortOrder) as $field=>$value) {
            if(filter_var($value, FILTER_VALIDATE_INT) === false) throw new \InvalidArgumentException('Valor inválido para '.$field.'.');
        }
        $price = (int)$price;
        $stock = (int)$stock;
        $limit = (int)$limit;
        $sortOrder = (int)$sortOrder;
        if($price < 1 || $price > 2147483647) throw new \InvalidArgumentException('El precio del lote debe ser mayor que cero.');
        if($stock < -1 || $stock > 2147483647) throw new \InvalidArgumentException('Stock del lote inválido.');
        if($limit < 0 || $limit > 2147483647) throw new \InvalidArgumentException('Límite por cuenta inválido.');
        if($sortOrder < -100000 || $sortOrder > 100000) throw new \InvalidArgumentException('Orden del lote inválido.');
        return array(
            'preset'=>$preset,
            'price_wc'=>$price,
            'stock'=>$stock,
            'max_per_account'=>$limit,
            'sort_order'=>$sortOrder,
            'category_override'=>$categoryOverride,
        );
    }

    private function buildBulkItemInput($section, $index, $options) {
        $authoritative = self::authoritativeItem($section, $index);
        if(!is_array($authoritative)) throw new \InvalidArgumentException('El código '.$section.':'.$index.' no existe en el catálogo técnico de Arsenal.');
        if(empty($authoritative['encoder_compatible'])) throw new \InvalidArgumentException('El código '.$section.':'.$index.' está fuera del rango DbVersion 3 confirmado.');

        $smart = self::smartOptionsForItem($section, $index);
        $excellentMask = 0;
        $excellentCount = 0;
        if(isset($smart['excellent_profile']['options']) && is_array($smart['excellent_profile']['options'])) {
            foreach($smart['excellent_profile']['options'] as $excellentRow) {
                if(!is_array($excellentRow) || !isset($excellentRow['bit'])) continue;
                $bit = ((int)$excellentRow['bit'] & 0x3F);
                if($bit < 1) continue;
                $excellentMask |= $bit;
                $excellentCount++;
            }
        }

        $usesExcellent = in_array((string)$options['preset'], array('full_excellent_0','full_excellent_socket5_0'), true);
        $opensFiveSockets = in_array((string)$options['preset'], array('full_excellent_socket5_0','skill_luck_socket5_0'), true);
        if($usesExcellent && (int)$authoritative['excellent_type'] > 0 && $excellentCount < 1) {
            throw new \InvalidArgumentException('Perfil Excellent especial identificado, pero el mapa de bits todavía no está validado. Usa un preset sin Excellent o configura este ítem manualmente.');
        }
        if(!$usesExcellent) $excellentMask = 0;
        $openSockets = $opensFiveSockets && !empty($authoritative['socket_capable']);
        $socketValue = $openSockets ? 254 : 255;
        $categoryOverride = trim((string)($options['category_override'] ?? ''));

        return array(
            'id'=>0,
            'name'=>(string)$authoritative['name'],
            'category'=>$categoryOverride !== '' ? $categoryOverride : self::suggestedCategory($section),
            'description'=>'',
            'item_section'=>(int)$section,
            'item_index'=>(int)$index,
            'item_level'=>0,
            'has_skill'=>(int)$authoritative['skill'] > 0 ? 1 : 0,
            'has_luck'=>(int)$authoritative['have_option'] > 0 ? 1 : 0,
            'option_level'=>0,
            'excellent_mask'=>$excellentMask,
            'ancient_option'=>0,
            'harmony_option'=>0,
            'socket_count'=>$openSockets ? 5 : 0,
            'option_380'=>0,
            'socket_bonus'=>0,
            'socket_option_1'=>$socketValue,
            'socket_option_2'=>$socketValue,
            'socket_option_3'=>$socketValue,
            'socket_option_4'=>$socketValue,
            'socket_option_5'=>$socketValue,
            'durability'=>255,
            'width'=>(int)$authoritative['width'],
            'height'=>(int)$authoritative['height'],
            'price_wc'=>(int)$options['price_wc'],
            'sale_price_wc'=>'',
            'sale_start'=>'',
            'sale_end'=>'',
            'stock'=>(int)$options['stock'],
            'max_per_account'=>(int)$options['max_per_account'],
            'active'=>0,
            'featured'=>0,
            'sort_order'=>(int)$options['sort_order'],
        );
    }

    private function prepareBulkRows($codes, $options, $includeInput=false) {
        if(!$this->schemaReady()) throw new \RuntimeException('Primero instala el esquema de la tienda desde Configuración.');
        if(!$this->smartOptionsSchemaReady() || !self::smartOptionsCatalogReady()) throw new \RuntimeException('Arsenal necesita instalar/validar el motor de opciones inteligentes antes de una carga masiva.');
        $options = $this->normalizeBulkOptions($options);
        $codes = $this->normalizeBulkCodes($codes);
        if(count($codes) < 1) throw new \InvalidArgumentException('Selecciona al menos un ítem del catálogo técnico.');
        if(count($codes) > 500) throw new \InvalidArgumentException('Por seguridad, cada lote puede contener hasta 500 ítems. Divide una carga mayor en varios lotes.');

        $existing = $this->getAdminCodeMap();
        $rows = array();
        foreach($codes as $code=>$identity) {
            $section = (int)$identity['section'];
            $index = (int)$identity['index'];
            $authoritative = self::authoritativeItem($section, $index);
            $base = array(
                'code'=>$code,
                'section'=>$section,
                'index'=>$index,
                'name'=>is_array($authoritative) ? (string)$authoritative['name'] : $code,
                'category'=>is_array($authoritative) ? self::suggestedCategory($section) : '',
                'width'=>is_array($authoritative) ? (int)$authoritative['width'] : 0,
                'height'=>is_array($authoritative) ? (int)$authoritative['height'] : 0,
                'skill'=>0,
                'luck'=>0,
                'excellent_mask'=>0,
                'excellent_count'=>0,
                'socket_count'=>0,
                'status'=>'blocked',
                'message'=>'No se pudo validar este código.',
            );

            if(isset($existing[$code])) {
                $base['status'] = 'existing';
                $base['message'] = 'Ya existe en Arsenal como #'.(int)$existing[$code]['id'].' · '.$existing[$code]['name'].'.';
                $base['existing_id'] = (int)$existing[$code]['id'];
                $rows[] = $base;
                continue;
            }

            try {
                $input = $this->buildBulkItemInput($section, $index, $options);
                $validated = $this->validateItemInput($input);
                $base['name'] = (string)$validated['name'];
                $base['category'] = (string)$validated['category'];
                $base['width'] = (int)$validated['width'];
                $base['height'] = (int)$validated['height'];
                $base['skill'] = !empty($validated['has_skill']) ? 1 : 0;
                $base['luck'] = !empty($validated['has_luck']) ? 1 : 0;
                $base['excellent_mask'] = (int)$validated['excellent_mask'];
                $base['excellent_count'] = substr_count(decbin((int)$validated['excellent_mask']), '1');
                $base['socket_count'] = (int)$validated['socket_count'];
                $base['status'] = 'ready';
                $base['message'] = 'Listo · se creará oculto y usando el render técnico como fallback.';
                if(in_array((string)$options['preset'], array('full_excellent_socket5_0','skill_luck_socket5_0'), true) && empty($authoritative['socket_capable'])) {
                    $base['message'] = 'Listo · este ítem no admite Socket según Items.xml; el preset conserva 0 slots.';
                }
                if($includeInput) $base['_input'] = $input;
            } catch(\Throwable $e) {
                $base['status'] = 'blocked';
                $base['message'] = $e->getMessage();
            }
            $rows[] = $base;
        }
        return $rows;
    }

    public function previewBulkItems($codes, $options=array()) {
        return $this->prepareBulkRows($codes, $options, false);
    }

    public function bulkCreateItems($codes, $options=array()) {
        $rows = $this->prepareBulkRows($codes, $options, true);
        $result = array('created'=>array(),'existing'=>array(),'blocked'=>array(),'failed'=>array());
        $readyRows = array();
        foreach($rows as $row) {
            $status = (string)($row['status'] ?? 'blocked');
            if($status === 'existing') { unset($row['_input']); $result['existing'][] = $row; continue; }
            if($status !== 'ready' || empty($row['_input']) || !is_array($row['_input'])) { unset($row['_input']); $result['blocked'][] = $row; continue; }
            $readyRows[] = $row;
        }
        if(count($readyRows) < 1) return $result;

        if(!$this->db->beginTransaction()) throw new \RuntimeException('No se pudo iniciar la carga masiva de Arsenal.');
        try {
            foreach($readyRows as $row) {
                // El rango queda bloqueado durante el lote para evitar duplicados administrativos concurrentes.
                $existingNow = $this->db->query_fetch_single(
                    'SELECT TOP 1 [id],[name] FROM ['.self::ITEMS_TABLE.'] WITH (UPDLOCK,HOLDLOCK) WHERE [item_section]=? AND [item_index]=? ORDER BY [id] ASC',
                    array((int)$row['section'],(int)$row['index'])
                );
                if(is_array($existingNow)) {
                    unset($row['_input']);
                    $row['status'] = 'existing';
                    $row['existing_id'] = (int)($existingNow['id'] ?? 0);
                    $row['message'] = 'El código fue creado antes de completar este lote y se omitió.';
                    $result['existing'][] = $row;
                    continue;
                }

                // prepareBulkRows() ya ejecutó validateItemInput() en este mismo request.
                $data = $this->validateItemInput($row['_input']);
                $publicKey = bin2hex(random_bytes(16));
                $inserted = $this->db->query_affected(
                    'INSERT INTO ['.self::ITEMS_TABLE.'] ([public_key],[name],[category],[description],[item_section],[item_index],[item_level],[has_skill],[has_luck],[option_level],[excellent_mask],[ancient_option],[harmony_option],[socket_count],[option_380],[socket_bonus],[socket_option_1],[socket_option_2],[socket_option_3],[socket_option_4],[socket_option_5],[durability],[width],[height],[price_wc],[sale_price_wc],[sale_start],[sale_end],[stock],[max_per_account],[active],[featured],[sort_order]) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
                    array($publicKey,$data['name'],$data['category'],$data['description'],$data['item_section'],$data['item_index'],$data['item_level'],$data['has_skill'],$data['has_luck'],$data['option_level'],$data['excellent_mask'],$data['ancient_option'],$data['harmony_option'],$data['socket_count'],$data['option_380'],$data['socket_bonus'],$data['socket_option_1'],$data['socket_option_2'],$data['socket_option_3'],$data['socket_option_4'],$data['socket_option_5'],$data['durability'],$data['width'],$data['height'],$data['price_wc'],$data['sale_price_wc'],$data['sale_start'],$data['sale_end'],$data['stock'],$data['max_per_account'],0,0,$data['sort_order'])
                );
                if($inserted !== 1) throw new \RuntimeException('No se pudo crear '.$row['code'].' · '.$row['name'].'.');
                $created = $this->db->query_fetch_single('SELECT [id] FROM ['.self::ITEMS_TABLE.'] WHERE [public_key]=?', array($publicKey));
                if(!is_array($created)) throw new \RuntimeException('Se insertó '.$row['code'].' pero no se pudo recuperar su identificador.');

                unset($row['_input']);
                $row['id'] = (int)$created['id'];
                $row['status'] = 'created';
                $row['message'] = 'Creado oculto.';
                $result['created'][] = $row;
            }
            if(!$this->db->commit()) throw new \RuntimeException('No se pudo confirmar la carga masiva de Arsenal.');
        } catch(\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }

        if(class_exists('NexoraAudit')) {
            $createdCodes = array();
            foreach($result['created'] as $createdRow) $createdCodes[] = (string)($createdRow['code'] ?? '');
            \NexoraAudit::log('itemshop.bulk_import', 'itemshop', $this->realm, null, array(
                'created'=>count($result['created']),
                'existing'=>count($result['existing']),
                'blocked'=>count($result['blocked']),
                'failed'=>0,
                'preset'=>(string)($options['preset'] ?? 'full_excellent_0'),
                'codes'=>$createdCodes,
                'items_hidden'=>1,
            ), $this->realm);
        }
        return $result;
    }

    public function itemAssetInfo($section, $index) {
        $section = (int)$section;
        $index = (int)$index;
        if($section < 0 || $section > 15 || $index < 0 || $index > 511) return null;
        $base = __PATH_TEMPLATE_ROOT__.'img/item_images/'.$section.'/'.$index;
        foreach(array('gif','webp','png','jpg','jpeg') as $extension) {
            $file = $base.'.'.$extension;
            if(!is_file($file)) continue;
            $info = @getimagesize($file);
            $result = array(
                'url'=>__PATH_TEMPLATE_IMG__.'item_images/'.$section.'/'.$index.'.'.$extension,
                'extension'=>$extension,
                'pixel_width'=>is_array($info) && !empty($info[0]) ? (int)$info[0] : 0,
                'pixel_height'=>is_array($info) && !empty($info[1]) ? (int)$info[1] : 0,
                'grid_width'=>null,
                'grid_height'=>null,
                'grid_reliable'=>false,
            );
            if($result['pixel_width'] > 0 && $result['pixel_height'] > 0 && $result['pixel_width'] % 32 === 0 && $result['pixel_height'] % 32 === 0) {
                $gridWidth = (int)($result['pixel_width']/32);
                $gridHeight = (int)($result['pixel_height']/32);
                if($gridWidth >= 1 && $gridWidth <= self::WAREHOUSE_COLUMNS && $gridHeight >= 1 && $gridHeight <= self::WAREHOUSE_ROWS) {
                    $result['grid_width'] = $gridWidth;
                    $result['grid_height'] = $gridHeight;
                    $result['grid_reliable'] = true;
                }
            }
            return $result;
        }
        return null;
    }

    public function getItem($id, $lock=false) {
        if(!\Validator::UnsignedNumber($id) || (int)$id < 1) return null;
        $hint = $lock ? ' WITH (UPDLOCK,HOLDLOCK)' : '';
        $row = $this->db->query_fetch_single('SELECT * FROM ['.self::ITEMS_TABLE.']'.$hint.' WHERE [id]=?', array((int)$id));
        return is_array($row) ? $row : null;
    }

    public function saveItem($input, $file=null) {
        if(!$this->schemaReady()) throw new \RuntimeException('Primero instala el esquema de la tienda desde Configuración.');
        if(!$this->smartOptionsSchemaReady() || !self::smartOptionsCatalogReady()) throw new \RuntimeException('Arsenal necesita instalar/validar la Fase 5 antes de guardar opciones de ítems.');
        $data = $this->validateItemInput($input);
        $id = !empty($input['id']) ? (int)$input['id'] : 0;
        $isEdit = $id > 0;
        $publicKey = bin2hex(random_bytes(16));
        if($id > 0) {
            $existing = $this->getItem($id);
            if(!is_array($existing)) throw new \RuntimeException('El ítem solicitado no existe.');
            $publicKey = (string)$existing['public_key'];
            $updated = $this->db->query_affected(
                'UPDATE ['.self::ITEMS_TABLE.'] SET [name]=?,[category]=?,[description]=?,[item_section]=?,[item_index]=?,[item_level]=?,[has_skill]=?,[has_luck]=?,[option_level]=?,[excellent_mask]=?,[ancient_option]=?,[harmony_option]=?,[socket_count]=?,[option_380]=?,[socket_bonus]=?,[socket_option_1]=?,[socket_option_2]=?,[socket_option_3]=?,[socket_option_4]=?,[socket_option_5]=?,[durability]=?,[width]=?,[height]=?,[price_wc]=?,[sale_price_wc]=?,[sale_start]=?,[sale_end]=?,[stock]=?,[max_per_account]=?,[active]=?,[featured]=?,[sort_order]=?,[updated_at]=SYSUTCDATETIME() WHERE [id]=?',
                array($data['name'],$data['category'],$data['description'],$data['item_section'],$data['item_index'],$data['item_level'],$data['has_skill'],$data['has_luck'],$data['option_level'],$data['excellent_mask'],$data['ancient_option'],$data['harmony_option'],$data['socket_count'],$data['option_380'],$data['socket_bonus'],$data['socket_option_1'],$data['socket_option_2'],$data['socket_option_3'],$data['socket_option_4'],$data['socket_option_5'],$data['durability'],$data['width'],$data['height'],$data['price_wc'],$data['sale_price_wc'],$data['sale_start'],$data['sale_end'],$data['stock'],$data['max_per_account'],$data['active'],$data['featured'],$data['sort_order'],$id)
            );
            if($updated !== 1) throw new \RuntimeException('No se pudo actualizar el ítem.');
        } else {
            $inserted = $this->db->query_affected(
                'INSERT INTO ['.self::ITEMS_TABLE.'] ([public_key],[name],[category],[description],[item_section],[item_index],[item_level],[has_skill],[has_luck],[option_level],[excellent_mask],[ancient_option],[harmony_option],[socket_count],[option_380],[socket_bonus],[socket_option_1],[socket_option_2],[socket_option_3],[socket_option_4],[socket_option_5],[durability],[width],[height],[price_wc],[sale_price_wc],[sale_start],[sale_end],[stock],[max_per_account],[active],[featured],[sort_order]) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
                array($publicKey,$data['name'],$data['category'],$data['description'],$data['item_section'],$data['item_index'],$data['item_level'],$data['has_skill'],$data['has_luck'],$data['option_level'],$data['excellent_mask'],$data['ancient_option'],$data['harmony_option'],$data['socket_count'],$data['option_380'],$data['socket_bonus'],$data['socket_option_1'],$data['socket_option_2'],$data['socket_option_3'],$data['socket_option_4'],$data['socket_option_5'],$data['durability'],$data['width'],$data['height'],$data['price_wc'],$data['sale_price_wc'],$data['sale_start'],$data['sale_end'],$data['stock'],$data['max_per_account'],$data['active'],$data['featured'],$data['sort_order'])
            );
            if($inserted !== 1) throw new \RuntimeException('No se pudo crear el ítem.');
            $created = $this->db->query_fetch_single('SELECT [id] FROM ['.self::ITEMS_TABLE.'] WHERE [public_key]=?', array($publicKey));
            if(!is_array($created)) throw new \RuntimeException('El ítem se creó, pero no se pudo recuperar su identificador.');
            $id = (int)$created['id'];
        }

        if(is_array($file) && isset($file['error']) && (int)$file['error'] !== UPLOAD_ERR_NO_FILE) {
            $imageFile = $this->storeImage($file, $publicKey);
            $imageUpdate = $this->db->query_affected('UPDATE ['.self::ITEMS_TABLE.'] SET [image_file]=?,[updated_at]=SYSUTCDATETIME() WHERE [id]=?', array($imageFile,$id));
            if($imageUpdate !== 1) throw new \RuntimeException('El ítem se guardó, pero no se pudo asociar la imagen.');
        }
        if(class_exists('NexoraAudit')) {
            \NexoraAudit::log($isEdit ? 'itemshop.item_save' : 'itemshop.item_create', 'itemshop_item', $id, null, $data, $this->realm);
        }
        return $id;
    }

    public function setItemActive($id, $active) {
        if(!\Validator::UnsignedNumber($id) || !in_array((int)$active,array(0,1),true)) throw new \InvalidArgumentException('Acción de catálogo inválida.');
        $affected = $this->db->query_affected('UPDATE ['.self::ITEMS_TABLE.'] SET [active]=?,[updated_at]=SYSUTCDATETIME() WHERE [id]=?', array((int)$active,(int)$id));
        if($affected !== 1) throw new \RuntimeException('No se pudo cambiar el estado del ítem.');
        if(class_exists('NexoraAudit')) {
            \NexoraAudit::log('itemshop.item_visibility', 'itemshop_item', (int)$id, null, array('active'=>(int)$active), $this->realm);
        }
    }

    private function normalizeAdminItemIds($ids, $limit=2000) {
        if(is_string($ids)) $ids = preg_split('/[\s,;]+/', trim($ids), -1, PREG_SPLIT_NO_EMPTY);
        if(!is_array($ids)) return array();
        $normalized = array();
        foreach($ids as $id) {
            if(filter_var($id, FILTER_VALIDATE_INT) === false) continue;
            $id = (int)$id;
            if($id < 1) continue;
            $normalized[$id] = $id;
            if(count($normalized) > (int)$limit) throw new \InvalidArgumentException('Demasiados ítems seleccionados para una sola acción masiva.');
        }
        return array_values($normalized);
    }

    private function catalogOperationFields() {
        return array('active','price_wc','sale_price_wc','sale_start','sale_end','stock','max_per_account','category','featured','sort_order');
    }

    private function normalizeOperationValue($field, $value) {
        if($value === null || $value === '') return $value === '' && in_array($field,array('category'),true) ? '' : null;
        if(in_array($field,array('active','price_wc','sale_price_wc','stock','max_per_account','featured','sort_order'),true)) return (int)$value;
        if(in_array($field,array('sale_start','sale_end'),true)) {
            $value = str_replace('T',' ',trim((string)$value));
            return strlen($value) >= 19 ? substr($value,0,19) : $value;
        }
        return (string)$value;
    }

    private function fetchAdminItemsByIds($ids, $lock=false, $limit=2000) {
        $ids = $this->normalizeAdminItemIds($ids, $limit);
        if(count($ids) < 1) return array();
        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $hint = $lock ? ' WITH (UPDLOCK,HOLDLOCK)' : '';
        $rows = $this->db->query_fetch('SELECT * FROM ['.self::ITEMS_TABLE.']'.$hint.' WHERE [id] IN ('.$placeholders.') ORDER BY [id] ASC', $ids);
        return is_array($rows) ? $rows : array();
    }

    private function createBulkHistory($action, $description, $fields, $items) {
        if(!$this->catalogOpsSchemaReady() || count($items) < 1) return null;
        $allowed = array_flip($this->catalogOperationFields());
        $fields = array_values(array_filter(array_unique(array_map('strval',$fields)), function($field) use ($allowed) { return isset($allowed[$field]); }));
        if(count($fields) < 1) return null;
        $payload = array('version'=>1,'realm'=>$this->realm,'fields'=>$fields,'items'=>$items);
        $json = json_encode($payload, JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES);
        if(!is_string($json)) throw new \RuntimeException('No se pudo serializar el snapshot de rollback.');
        $key = bin2hex(random_bytes(16));
        $affected = count($items);
        $inserted = $this->db->query_affected(
            'INSERT INTO ['.self::BULK_HISTORY_TABLE.'] ([operation_key],[action],[description],[affected],[snapshot]) VALUES (?,?,?,?,?)',
            array($key,(string)$action,mb_substr((string)$description,0,200),$affected,$json)
        );
        if($inserted !== 1) throw new \RuntimeException('No se pudo registrar el snapshot de rollback.');
        $row = $this->db->query_fetch_single('SELECT [id] FROM ['.self::BULK_HISTORY_TABLE.'] WHERE [operation_key]=?', array($key));
        return array('id'=>is_array($row) ? (int)($row['id'] ?? 0) : 0,'key'=>$key,'affected'=>$affected);
    }

    private function visibilitySnapshotItems($rows, $active) {
        $items = array();
        foreach($rows as $row) {
            if(!is_array($row) || (int)($row['active'] ?? -1) === (int)$active) continue;
            $items[] = array(
                'id'=>(int)$row['id'],
                'name'=>(string)($row['name'] ?? ''),
                'before'=>array('active'=>(int)$row['active']),
                'after'=>array('active'=>(int)$active),
            );
        }
        return $items;
    }

    public function setItemsActive($ids, $active) {
        if(!$this->schemaReady()) throw new \RuntimeException('Primero instala el esquema de la tienda desde Configuración.');
        $active = (int)$active;
        if(!in_array($active,array(0,1),true)) throw new \InvalidArgumentException('Estado masivo inválido.');
        $ids = $this->normalizeAdminItemIds($ids, 2000);
        if(count($ids) < 1) throw new \InvalidArgumentException('Selecciona al menos un ítem del catálogo.');

        $history = null;
        if($this->catalogOpsSchemaReady()) {
            if(!$this->db->beginTransaction()) throw new \RuntimeException('No se pudo iniciar la operación masiva.');
            try {
                $rows = $this->fetchAdminItemsByIds($ids, true, 2000);
                $snapshotItems = $this->visibilitySnapshotItems($rows, $active);
                if(count($snapshotItems) < 1) { $this->db->rollBack(); return 0; }
                $targetIds = array_column($snapshotItems,'id');
                $history = $this->createBulkHistory('visibility_selected', ($active ? 'Publicar' : 'Ocultar').' selección', array('active'), $snapshotItems);
                $placeholders = implode(',', array_fill(0, count($targetIds), '?'));
                $params = array_merge(array($active), $targetIds);
                $affected = $this->db->query_affected('UPDATE ['.self::ITEMS_TABLE.'] SET [active]=?,[updated_at]=SYSUTCDATETIME() WHERE [id] IN ('.$placeholders.')', $params);
                if($affected === false || (int)$affected !== count($targetIds)) throw new \RuntimeException('No se pudo actualizar toda la selección del catálogo.');
                if(!$this->db->commit()) throw new \RuntimeException('No se pudo confirmar la operación masiva.');
            } catch(\Throwable $e) { $this->db->rollBack(); throw $e; }
        } else {
            $placeholders = implode(',', array_fill(0, count($ids), '?'));
            $params = array_merge(array($active), $ids, array($active));
            $affected = $this->db->query_affected('UPDATE ['.self::ITEMS_TABLE.'] SET [active]=?,[updated_at]=SYSUTCDATETIME() WHERE [id] IN ('.$placeholders.') AND [active]<>?', $params);
            if($affected === false) throw new \RuntimeException('No se pudo actualizar la selección del catálogo.');
        }
        if(class_exists('NexoraAudit')) {
            $sample = array_slice($ids, 0, 30);
            \NexoraAudit::log('itemshop.bulk_visibility', 'itemshop', $this->realm, null, array(
                'scope'=>'selected','active'=>$active,'affected'=>(int)$affected,'selected'=>count($ids),
                'ids_sample'=>$sample,'ids_sha256'=>hash('sha256', implode(',', $ids)),
                'history_id'=>is_array($history) ? (int)$history['id'] : null,
            ), $this->realm);
        }
        return (int)$affected;
    }

    public function setAllItemsActive($active) {
        if(!$this->schemaReady()) throw new \RuntimeException('Primero instala el esquema de la tienda desde Configuración.');
        $active = (int)$active;
        if(!in_array($active,array(0,1),true)) throw new \InvalidArgumentException('Estado masivo inválido.');
        $history = null;
        if($this->catalogOpsSchemaReady()) {
            if(!$this->db->beginTransaction()) throw new \RuntimeException('No se pudo iniciar la operación masiva.');
            try {
                $rows = $this->db->query_fetch('SELECT * FROM ['.self::ITEMS_TABLE.'] WITH (UPDLOCK,HOLDLOCK) WHERE [active]<>? ORDER BY [id] ASC', array($active));
                if(!is_array($rows) || count($rows) < 1) { $this->db->rollBack(); return 0; }
                $snapshotItems = $this->visibilitySnapshotItems($rows, $active);
                $history = $this->createBulkHistory('visibility_all', ($active ? 'Publicar' : 'Ocultar').' todo el catálogo', array('active'), $snapshotItems);
                $affected = $this->db->query_affected('UPDATE ['.self::ITEMS_TABLE.'] SET [active]=?,[updated_at]=SYSUTCDATETIME() WHERE [active]<>?', array($active,$active));
                if($affected === false || (int)$affected !== count($snapshotItems)) throw new \RuntimeException('No se pudo actualizar el catálogo completo.');
                if(!$this->db->commit()) throw new \RuntimeException('No se pudo confirmar la operación masiva.');
            } catch(\Throwable $e) { $this->db->rollBack(); throw $e; }
        } else {
            $affected = $this->db->query_affected('UPDATE ['.self::ITEMS_TABLE.'] SET [active]=?,[updated_at]=SYSUTCDATETIME() WHERE [active]<>?', array($active,$active));
            if($affected === false) throw new \RuntimeException('No se pudo actualizar el catálogo completo.');
        }
        if(class_exists('NexoraAudit')) {
            \NexoraAudit::log('itemshop.bulk_visibility', 'itemshop', $this->realm, null, array(
                'scope'=>'all','active'=>$active,'affected'=>(int)$affected,
                'history_id'=>is_array($history) ? (int)$history['id'] : null,
            ), $this->realm);
        }
        return (int)$affected;
    }

    private function normalizeBulkEditChanges($input) {
        if(!is_array($input)) $input = array();
        $changes = array();
        $labels = array();
        if(!empty($input['edit_price_enabled'])) {
            if(filter_var($input['edit_price_wc'] ?? null, FILTER_VALIDATE_INT) === false || (int)$input['edit_price_wc'] < 1) throw new \InvalidArgumentException('Precio masivo inválido.');
            $changes['price_wc'] = (int)$input['edit_price_wc']; $labels[] = 'precio';
        }
        if(!empty($input['edit_stock_enabled'])) {
            if(filter_var($input['edit_stock'] ?? null, FILTER_VALIDATE_INT) === false || (int)$input['edit_stock'] < -1) throw new \InvalidArgumentException('Stock masivo inválido.');
            $changes['stock'] = (int)$input['edit_stock']; $labels[] = 'stock';
        }
        if(!empty($input['edit_limit_enabled'])) {
            if(filter_var($input['edit_max_per_account'] ?? null, FILTER_VALIDATE_INT) === false || (int)$input['edit_max_per_account'] < 0) throw new \InvalidArgumentException('Límite por cuenta inválido.');
            $changes['max_per_account'] = (int)$input['edit_max_per_account']; $labels[] = 'límite';
        }
        if(!empty($input['edit_category_enabled'])) {
            $category = trim((string)($input['edit_category'] ?? ''));
            if($category === '' || mb_strlen($category) > 50) throw new \InvalidArgumentException('Categoría masiva inválida.');
            $changes['category'] = $category; $labels[] = 'categoría';
        }
        if(!empty($input['edit_order_enabled'])) {
            if(filter_var($input['edit_sort_order'] ?? null, FILTER_VALIDATE_INT) === false) throw new \InvalidArgumentException('Orden masivo inválido.');
            $order = (int)$input['edit_sort_order'];
            if($order < -100000 || $order > 100000) throw new \InvalidArgumentException('Orden masivo fuera de rango.');
            $changes['sort_order'] = $order; $labels[] = 'orden';
        }
        if(!empty($input['edit_featured_enabled'])) {
            $featured = isset($input['edit_featured']) ? (int)$input['edit_featured'] : -1;
            if(!in_array($featured,array(0,1),true)) throw new \InvalidArgumentException('Estado destacado inválido.');
            $changes['featured'] = $featured; $labels[] = 'destacado';
        }
        $promotion = (string)($input['edit_promotion_mode'] ?? 'keep');
        if(!in_array($promotion,array('keep','clear','set'),true)) throw new \InvalidArgumentException('Acción de promoción inválida.');
        if($promotion === 'clear') {
            $changes['sale_price_wc'] = null; $changes['sale_start'] = null; $changes['sale_end'] = null; $labels[] = 'promoción';
        } elseif($promotion === 'set') {
            if(filter_var($input['edit_sale_price_wc'] ?? null, FILTER_VALIDATE_INT) === false || (int)$input['edit_sale_price_wc'] < 1) throw new \InvalidArgumentException('Precio promocional masivo inválido.');
            $changes['sale_price_wc'] = (int)$input['edit_sale_price_wc'];
            $changes['sale_start'] = $this->localDateToUtc($input['edit_sale_start'] ?? '');
            $changes['sale_end'] = $this->localDateToUtc($input['edit_sale_end'] ?? '');
            if($changes['sale_start'] !== null && $changes['sale_end'] !== null && strtotime($changes['sale_end'].' UTC') <= strtotime($changes['sale_start'].' UTC')) throw new \InvalidArgumentException('La promoción masiva debe terminar después de iniciar.');
            $labels[] = 'promoción';
        }
        if(count($changes) < 1) throw new \InvalidArgumentException('Activa al menos un campo para la edición masiva.');
        return array('changes'=>$changes,'labels'=>$labels,'promotion_mode'=>$promotion);
    }

    private function prepareBulkEditRows($ids, $normalized, $lock=false) {
        $ids = $this->normalizeAdminItemIds($ids, 500);
        if(count($ids) < 1) throw new \InvalidArgumentException('Selecciona al menos un ítem para editar.');
        $rows = $this->fetchAdminItemsByIds($ids, $lock, 500);
        if(count($rows) !== count($ids)) throw new \RuntimeException('Uno o más ítems seleccionados ya no existen. Recarga el catálogo.');
        $changes = $normalized['changes'];
        $prepared = array();
        foreach($rows as $row) {
            $before = array(); $after = array(); $diff = array(); $error = null;
            foreach($changes as $field=>$value) {
                $before[$field] = $this->normalizeOperationValue($field, $row[$field] ?? null);
                $after[$field] = $this->normalizeOperationValue($field, $value);
                if($before[$field] !== $after[$field]) $diff[$field] = array('before'=>$before[$field],'after'=>$after[$field]);
            }
            $finalPrice = array_key_exists('price_wc',$changes) ? (int)$changes['price_wc'] : (int)$row['price_wc'];
            $finalSale = array_key_exists('sale_price_wc',$changes) ? $changes['sale_price_wc'] : ($row['sale_price_wc'] === null || $row['sale_price_wc'] === '' ? null : (int)$row['sale_price_wc']);
            if($finalSale !== null && ((int)$finalSale < 1 || (int)$finalSale >= $finalPrice)) $error = 'La promoción resultante debe ser menor que el precio normal ('.$finalPrice.').';
            $finalStart = array_key_exists('sale_start',$changes) ? $changes['sale_start'] : ($row['sale_start'] ?? null);
            $finalEnd = array_key_exists('sale_end',$changes) ? $changes['sale_end'] : ($row['sale_end'] ?? null);
            if($error === null && $finalStart && $finalEnd && strtotime((string)$finalEnd.' UTC') <= strtotime((string)$finalStart.' UTC')) $error = 'La promoción resultante tiene un rango de fechas inválido.';
            $prepared[] = array(
                'id'=>(int)$row['id'],'name'=>(string)$row['name'],'code'=>(int)$row['item_section'].':'.(int)$row['item_index'],
                'before'=>$before,'after'=>$after,'diff'=>$diff,
                'status'=>$error !== null ? 'blocked' : (count($diff) ? 'ready' : 'unchanged'),
                'message'=>$error !== null ? $error : (count($diff) ? count($diff).' campo'.(count($diff)===1?'':'s').' cambiará'.(count($diff)===1?'':'n').'.' : 'Sin cambios para esta configuración.'),
            );
        }
        return $prepared;
    }

    public function previewBulkEdit($ids, $input) {
        $normalized = $this->normalizeBulkEditChanges($input);
        return array('rows'=>$this->prepareBulkEditRows($ids,$normalized,false),'labels'=>$normalized['labels'],'fields'=>array_keys($normalized['changes']));
    }

    public function bulkEditItems($ids, $input) {
        if(!$this->catalogOpsSchemaReady()) throw new \RuntimeException('Instala / valida las tablas de Arsenal 1.6.8 antes de usar edición masiva con rollback.');
        $normalized = $this->normalizeBulkEditChanges($input);
        if(!$this->db->beginTransaction()) throw new \RuntimeException('No se pudo iniciar la edición masiva.');
        try {
            $prepared = $this->prepareBulkEditRows($ids,$normalized,true);
            $blocked = array_values(array_filter($prepared,function($row){ return ($row['status'] ?? '') === 'blocked'; }));
            if(count($blocked)) throw new \RuntimeException('La edición fue cancelada: '.count($blocked).' configuración'.(count($blocked)===1?'':'es').' produciría'.(count($blocked)===1?'':'n').' un estado inválido. Revisa la previsualización.');
            $ready = array_values(array_filter($prepared,function($row){ return ($row['status'] ?? '') === 'ready'; }));
            if(count($ready) < 1) { $this->db->rollBack(); return array('affected'=>0,'history_id'=>0,'rows'=>$prepared); }
            $fields = array_keys($normalized['changes']);
            $snapshot = array();
            $targetIds = array();
            foreach($ready as $row) {
                $snapshot[] = array('id'=>$row['id'],'name'=>$row['name'],'before'=>$row['before'],'after'=>$row['after']);
                $targetIds[] = (int)$row['id'];
            }
            $history = $this->createBulkHistory('bulk_edit', 'Edición masiva: '.implode(', ',$normalized['labels']), $fields, $snapshot);
            $sets = array(); $params = array();
            foreach($normalized['changes'] as $field=>$value) { $sets[]='['.$field.']=?'; $params[]=$value; }
            $sets[]='[updated_at]=SYSUTCDATETIME()';
            $placeholders = implode(',', array_fill(0,count($targetIds),'?'));
            $params = array_merge($params,$targetIds);
            $affected = $this->db->query_affected('UPDATE ['.self::ITEMS_TABLE.'] SET '.implode(',',$sets).' WHERE [id] IN ('.$placeholders.')', $params);
            if($affected === false || (int)$affected !== count($targetIds)) throw new \RuntimeException('No se pudo aplicar toda la edición masiva.');
            if(!$this->db->commit()) throw new \RuntimeException('No se pudo confirmar la edición masiva.');
        } catch(\Throwable $e) { $this->db->rollBack(); throw $e; }
        if(class_exists('NexoraAudit')) {
            \NexoraAudit::log('itemshop.bulk_edit', 'itemshop', $this->realm, null, array(
                'affected'=>(int)$affected,'fields'=>$fields,'labels'=>$normalized['labels'],'history_id'=>(int)($history['id'] ?? 0),
                'ids_sha256'=>hash('sha256',implode(',',$targetIds)),
            ), $this->realm);
        }
        return array('affected'=>(int)$affected,'history_id'=>(int)($history['id'] ?? 0),'rows'=>$prepared);
    }

    public function getBulkHistory($limit=12) {
        if(!$this->catalogOpsSchemaReady()) return array();
        $limit = max(1,min(50,(int)$limit));
        $rows = $this->db->query_fetch('SELECT TOP '.$limit.' [id],[operation_key],[action],[description],[affected],[rolled_back],[rollback_count],[conflict_count],[created_at],[rolled_back_at] FROM ['.self::BULK_HISTORY_TABLE.'] ORDER BY [id] DESC');
        return is_array($rows) ? $rows : array();
    }

    public function rollbackBulkOperation($historyId) {
        if(!$this->catalogOpsSchemaReady()) throw new \RuntimeException('Instala / valida las tablas de Arsenal 1.6.8 antes de usar rollback.');
        if(filter_var($historyId, FILTER_VALIDATE_INT) === false || (int)$historyId < 1) throw new \InvalidArgumentException('Operación de rollback inválida.');
        $historyId = (int)$historyId;
        if(!$this->db->beginTransaction()) throw new \RuntimeException('No se pudo iniciar el rollback.');
        try {
            $history = $this->db->query_fetch_single('SELECT * FROM ['.self::BULK_HISTORY_TABLE.'] WITH (UPDLOCK,HOLDLOCK) WHERE [id]=?', array($historyId));
            if(!is_array($history)) throw new \RuntimeException('La operación ya no existe.');
            if(!empty($history['rolled_back'])) throw new \RuntimeException('Esta operación ya fue revertida.');
            $payload = json_decode((string)($history['snapshot'] ?? ''), true);
            if(!is_array($payload) || empty($payload['fields']) || empty($payload['items']) || !is_array($payload['items'])) throw new \RuntimeException('El snapshot de rollback no es válido.');
            if(isset($payload['realm']) && (string)$payload['realm'] !== (string)$this->realm) throw new \RuntimeException('El snapshot pertenece a otro reino.');
            $allowed = array_flip($this->catalogOperationFields());
            $fields = array_values(array_filter(array_map('strval',$payload['fields']),function($field) use($allowed){ return isset($allowed[$field]); }));
            if(count($fields) < 1) throw new \RuntimeException('El snapshot no contiene campos restaurables.');
            $ids = array(); foreach($payload['items'] as $item) if(is_array($item) && !empty($item['id'])) $ids[]=(int)$item['id'];
            $ids = array_values(array_unique($ids));
            if(count($ids) < 1 || count($ids) > 20000) throw new \RuntimeException('El snapshot contiene una selección inválida.');
            // SQL Server limita los parámetros por statement. Los snapshots de "todo el catálogo"
            // pueden superar 2.000 filas, así que bloqueamos/consultamos en lotes dentro de la misma transacción.
            $currentById = array();
            foreach(array_chunk($ids, 1000) as $chunk) {
                $currentRows = $this->fetchAdminItemsByIds($chunk,true,2000);
                foreach($currentRows as $row) $currentById[(int)$row['id']]=$row;
            }
            $restored = 0; $conflicts = 0;
            foreach($payload['items'] as $item) {
                if(!is_array($item) || empty($item['id']) || !isset($item['before'],$item['after']) || !is_array($item['before']) || !is_array($item['after'])) { $conflicts++; continue; }
                $id=(int)$item['id'];
                if(!isset($currentById[$id])) { $conflicts++; continue; }
                $current=$currentById[$id]; $matches=true;
                foreach($fields as $field) {
                    $now=$this->normalizeOperationValue($field,$current[$field] ?? null);
                    $expected=$this->normalizeOperationValue($field,$item['after'][$field] ?? null);
                    if($now !== $expected) { $matches=false; break; }
                }
                if(!$matches) { $conflicts++; continue; }
                $sets=array(); $params=array();
                foreach($fields as $field) { $sets[]='['.$field.']=?'; $params[]=$item['before'][$field] ?? null; }
                $sets[]='[updated_at]=SYSUTCDATETIME()'; $params[]=$id;
                $affected=$this->db->query_affected('UPDATE ['.self::ITEMS_TABLE.'] SET '.implode(',',$sets).' WHERE [id]=?', $params);
                if($affected !== 1) { $conflicts++; continue; }
                $restored++;
            }
            $marked=$this->db->query_affected('UPDATE ['.self::BULK_HISTORY_TABLE.'] SET [rolled_back]=1,[rollback_count]=?,[conflict_count]=?,[rolled_back_at]=SYSUTCDATETIME() WHERE [id]=?', array($restored,$conflicts,$historyId));
            if($marked !== 1) throw new \RuntimeException('No se pudo cerrar el registro de rollback.');
            if(!$this->db->commit()) throw new \RuntimeException('No se pudo confirmar el rollback.');
        } catch(\Throwable $e) { $this->db->rollBack(); throw $e; }
        if(class_exists('NexoraAudit')) {
            \NexoraAudit::log('itemshop.bulk_rollback', 'itemshop_bulk_history', $historyId, null, array('restored'=>$restored,'conflicts'=>$conflicts), $this->realm);
        }
        return array('restored'=>$restored,'conflicts'=>$conflicts);
    }

    private function persistedItemInput($row) {
        $input = is_array($row) ? $row : array();
        $input['sale_start'] = $this->localDateValue($row['sale_start'] ?? null);
        $input['sale_end'] = $this->localDateValue($row['sale_end'] ?? null);
        return $input;
    }

    public function catalogReadiness($issueLimit=250) {
        if(!$this->schemaReady()) return array('total'=>0,'ready'=>0,'blocked'=>0,'warnings'=>0,'advisories'=>0,'issues'=>array());
        $issueLimit=max(20,min(1000,(int)$issueLimit));
        $items=$this->getAdminItems();
        $issues=array(); $errorCount=0; $warningCount=0; $advisoryCount=0; $blockedIds=array();
        $codes=array();
        $addIssue=function($severity,$item,$code,$message) use (&$issues,&$errorCount,&$warningCount,&$advisoryCount,&$blockedIds,$issueLimit) {
            if($severity==='error') { $errorCount++; $blockedIds[(int)($item['id'] ?? 0)]=true; }
            elseif($severity==='warning') $warningCount++;
            else $advisoryCount++;
            if(count($issues)<$issueLimit) $issues[]=array('severity'=>$severity,'id'=>(int)($item['id'] ?? 0),'name'=>(string)($item['name'] ?? ''),'code'=>$code,'message'=>$message);
        };
        foreach($items as $item) {
            $code=(int)$item['item_section'].':'.(int)$item['item_index'];
            if(!isset($codes[$code])) $codes[$code]=array(); $codes[$code][]=(int)$item['id'];
            try { $this->validateItemInput($this->persistedItemInput($item)); }
            catch(\Throwable $e) { $addIssue('error',$item,$code,$e->getMessage()); }
            if((int)$item['stock']===0) $addIssue('warning',$item,$code,!empty($item['active']) ? 'Publicado pero agotado: no aparece en el Arsenal público hasta recuperar stock.' : 'Configuración agotada (stock 0).');
            if(!empty($item['image_file'])) {
                $file=basename((string)$item['image_file']);
                if(!is_file($this->imageDirectory().$file)) $addIssue('warning',$item,$code,'El artwork configurado no existe en disco; el frontend dependerá del render técnico.');
            }
            $render=__PATH_TEMPLATE_ROOT__.'img/itemshop-renders-v1/'.(int)$item['item_section'].'/'.(int)$item['item_index'].'.webp';
            if(empty($item['image_file']) && !is_file($render)) $addIssue('warning',$item,$code,'No se encontró artwork ni render normalizado para esta configuración.');
            if(!empty($item['sale_price_wc']) && !empty($item['sale_end'])) {
                $end=strtotime((string)$item['sale_end'].' UTC');
                if($end !== false && $end < time()) $addIssue('advisory',$item,$code,'La promoción terminó; el producto volvió al precio normal.');
            }
        }
        foreach($codes as $code=>$ids) if(count($ids)>1) {
            $sampleItem=array('id'=>$ids[0],'name'=>'Código '.$code);
            $addIssue('advisory',$sampleItem,$code,count($ids).' configuraciones comparten este Section:Index. Puede ser intencional; revisa precio/opciones si no lo es.');
        }
        return array(
            'total'=>count($items),'ready'=>max(0,count($items)-count($blockedIds)),'blocked'=>count($blockedIds),
            'errors'=>$errorCount,'warnings'=>$warningCount,'advisories'=>$advisoryCount,'issues'=>$issues,'issue_limit'=>$issueLimit,
            'truncated'=>($errorCount+$warningCount+$advisoryCount)>count($issues),
        );
    }

    /**
     * Elimina una configuración del catálogo sin destruir el historial de entregas.
     * NEXORA_ITEMSHOP_ORDERS conserva item_name + item_snapshot y no depende de FK.
     */
    public function getCollections() {
        if(!$this->campaignSchemaReady()) return array();
        $sql = 'SELECT c.*,'
            .'(SELECT COUNT(*) FROM ['.self::COLLECTION_ITEMS_TABLE.'] ci WHERE ci.[collection_id]=c.[id]) AS [item_count],'
            .'(SELECT COUNT(*) FROM ['.self::CAMPAIGNS_TABLE.'] cp WHERE cp.[collection_id]=c.[id] AND cp.[status] IN (\'scheduled\',\'active\')) AS [live_campaigns] '
            .'FROM ['.self::COLLECTIONS_TABLE.'] c ORDER BY c.[name] ASC,c.[id] ASC';
        $rows = $this->db->query_fetch($sql);
        return is_array($rows) ? $rows : array();
    }

    public function getCollection($id) {
        if(!$this->campaignSchemaReady() || filter_var($id,FILTER_VALIDATE_INT) === false || (int)$id < 1) return null;
        $row = $this->db->query_fetch_single(
            'SELECT c.*,(SELECT COUNT(*) FROM ['.self::COLLECTION_ITEMS_TABLE.'] ci WHERE ci.[collection_id]=c.[id]) AS [item_count] FROM ['.self::COLLECTIONS_TABLE.'] c WHERE c.[id]=?',
            array((int)$id)
        );
        return is_array($row) ? $row : null;
    }

    public function getCollectionItemIds($collectionId) {
        if(!$this->campaignSchemaReady() || filter_var($collectionId,FILTER_VALIDATE_INT) === false || (int)$collectionId < 1) return array();
        $rows = $this->db->query_fetch('SELECT [item_id] FROM ['.self::COLLECTION_ITEMS_TABLE.'] WHERE [collection_id]=? ORDER BY [sort_order] ASC,[item_id] ASC', array((int)$collectionId));
        if(!is_array($rows)) return array();
        return array_map(function($row){ return (int)$row['item_id']; }, $rows);
    }

    public function getCollectionItems($collectionId) {
        if(!$this->campaignSchemaReady() || filter_var($collectionId,FILTER_VALIDATE_INT) === false || (int)$collectionId < 1) return array();
        $rows = $this->db->query_fetch(
            'SELECT i.*,ci.[sort_order] AS [collection_sort_order] FROM ['.self::COLLECTION_ITEMS_TABLE.'] ci INNER JOIN ['.self::ITEMS_TABLE.'] i ON i.[id]=ci.[item_id] WHERE ci.[collection_id]=? ORDER BY ci.[sort_order] ASC,i.[id] ASC',
            array((int)$collectionId)
        );
        return is_array($rows) ? $rows : array();
    }

    public function saveCollection($input) {
        if(!$this->campaignSchemaReady()) throw new \RuntimeException('Instala / valida las tablas de Arsenal 1.7.0 antes de administrar colecciones.');
        $id = isset($input['collection_id']) && filter_var($input['collection_id'],FILTER_VALIDATE_INT) !== false ? (int)$input['collection_id'] : 0;
        $name = trim((string)($input['name'] ?? ''));
        $description = trim((string)($input['description'] ?? ''));
        if(mb_strlen($name) < 2 || mb_strlen($name) > 100) throw new \InvalidArgumentException('El nombre de la colección debe tener entre 2 y 100 caracteres.');
        if(mb_strlen($description) > 300) throw new \InvalidArgumentException('La descripción de la colección no puede superar 300 caracteres.');
        $duplicate = $this->db->query_fetch_single('SELECT [id] FROM ['.self::COLLECTIONS_TABLE.'] WHERE [name]=? AND [id]<>?', array($name,$id));
        if(is_array($duplicate)) throw new \InvalidArgumentException('Ya existe una colección con ese nombre.');
        if($id > 0) {
            $existing = $this->getCollection($id);
            if(!is_array($existing)) throw new \RuntimeException('La colección ya no existe.');
            $affected = $this->db->query_affected('UPDATE ['.self::COLLECTIONS_TABLE.'] SET [name]=?,[description]=?,[updated_at]=SYSUTCDATETIME() WHERE [id]=?', array($name,$description === '' ? null : $description,$id));
            if($affected !== 1) throw new \RuntimeException('No se pudo actualizar la colección.');
        } else {
            $affected = $this->db->query_affected('INSERT INTO ['.self::COLLECTIONS_TABLE.'] ([name],[description]) VALUES (?,?)', array($name,$description === '' ? null : $description));
            if($affected !== 1) throw new \RuntimeException('No se pudo crear la colección.');
            $row = $this->db->query_fetch_single('SELECT TOP 1 [id] FROM ['.self::COLLECTIONS_TABLE.'] WHERE [name]=? ORDER BY [id] DESC', array($name));
            $id = is_array($row) ? (int)($row['id'] ?? 0) : 0;
            if($id < 1) throw new \RuntimeException('La colección fue creada pero no se pudo resolver su identificador.');
        }
        if(class_exists('NexoraAudit')) \NexoraAudit::log('itemshop.collection_save','itemshop_collection',$id,null,array('name'=>$name,'description'=>$description),$this->realm);
        return $id;
    }

    public function setCollectionItems($collectionId, $ids) {
        if(!$this->campaignSchemaReady()) throw new \RuntimeException('Instala / valida las tablas de Arsenal 1.7.0 antes de administrar colecciones.');
        if(filter_var($collectionId,FILTER_VALIDATE_INT) === false || (int)$collectionId < 1) throw new \InvalidArgumentException('Colección inválida.');
        $collectionId = (int)$collectionId;
        if(!is_array($this->getCollection($collectionId))) throw new \RuntimeException('La colección ya no existe.');
        $ids = $this->normalizeAdminItemIds($ids, 2000);
        if(count($ids)) {
            $rows = $this->fetchAdminItemsByIds($ids,false,2000);
            if(count($rows) !== count($ids)) throw new \InvalidArgumentException('La selección contiene configuraciones que ya no existen.');
        }
        if(!$this->db->beginTransaction()) throw new \RuntimeException('No se pudo iniciar la actualización de la colección.');
        try {
            if(!$this->db->query('DELETE FROM ['.self::COLLECTION_ITEMS_TABLE.'] WHERE [collection_id]=?', array($collectionId))) throw new \RuntimeException('No se pudo limpiar la membresía anterior de la colección.');
            $order = 10;
            foreach($ids as $id) {
                $affected = $this->db->query_affected('INSERT INTO ['.self::COLLECTION_ITEMS_TABLE.'] ([collection_id],[item_id],[sort_order]) VALUES (?,?,?)', array($collectionId,(int)$id,$order));
                if($affected !== 1) throw new \RuntimeException('No se pudo guardar toda la membresía de la colección.');
                $order += 10;
            }
            $this->db->query('UPDATE ['.self::COLLECTIONS_TABLE.'] SET [updated_at]=SYSUTCDATETIME() WHERE [id]=?', array($collectionId));
            if(!$this->db->commit()) throw new \RuntimeException('No se pudo confirmar la colección.');
        } catch(\Throwable $e) { $this->db->rollBack(); throw $e; }
        if(class_exists('NexoraAudit')) \NexoraAudit::log('itemshop.collection_membership','itemshop_collection',$collectionId,null,array('count'=>count($ids),'ids_sha256'=>hash('sha256',implode(',',$ids))),$this->realm);
        return count($ids);
    }

    public function deleteCollection($collectionId) {
        if(!$this->campaignSchemaReady()) throw new \RuntimeException('Instala / valida las tablas de Arsenal 1.7.0 antes de administrar colecciones.');
        if(filter_var($collectionId,FILTER_VALIDATE_INT) === false || (int)$collectionId < 1) throw new \InvalidArgumentException('Colección inválida.');
        $collectionId=(int)$collectionId;
        $live=$this->db->query_fetch_single('SELECT COUNT(*) AS [cnt] FROM ['.self::CAMPAIGNS_TABLE.'] WHERE [collection_id]=? AND [status] IN (\'scheduled\',\'active\')', array($collectionId));
        if(is_array($live) && (int)($live['cnt'] ?? 0) > 0) throw new \RuntimeException('No puedes eliminar una colección utilizada por una campaña programada o activa. Cancela/finaliza la campaña primero.');
        if(!$this->db->beginTransaction()) throw new \RuntimeException('No se pudo iniciar la eliminación de la colección.');
        try {
            $this->db->query('DELETE FROM ['.self::COLLECTION_ITEMS_TABLE.'] WHERE [collection_id]=?', array($collectionId));
            $affected=$this->db->query_affected('DELETE FROM ['.self::COLLECTIONS_TABLE.'] WHERE [id]=?', array($collectionId));
            if($affected !== 1) throw new \RuntimeException('La colección ya no existe.');
            if(!$this->db->commit()) throw new \RuntimeException('No se pudo confirmar la eliminación de la colección.');
        } catch(\Throwable $e) { $this->db->rollBack(); throw $e; }
        if(class_exists('NexoraAudit')) \NexoraAudit::log('itemshop.collection_delete','itemshop_collection',$collectionId,null,array(),$this->realm);
        return true;
    }

    public function getCampaign($id) {
        if(!$this->campaignSchemaReady() || filter_var($id,FILTER_VALIDATE_INT) === false || (int)$id < 1) return null;
        $row=$this->db->query_fetch_single('SELECT * FROM ['.self::CAMPAIGNS_TABLE.'] WHERE [id]=?', array((int)$id));
        return is_array($row) ? $row : null;
    }

    public function getCampaigns($limit=100) {
        if(!$this->campaignSchemaReady()) return array();
        $limit=max(1,min(250,(int)$limit));
        $rows=$this->db->query_fetch('SELECT TOP '.$limit.' * FROM ['.self::CAMPAIGNS_TABLE.'] ORDER BY CASE [status] WHEN \'active\' THEN 0 WHEN \'scheduled\' THEN 1 ELSE 2 END,[start_at] DESC,[id] DESC');
        return is_array($rows) ? $rows : array();
    }

    private function findCampaignOverlap($collectionId, $startAt, $endAt, $excludeId=0) {
        if(!$this->campaignSchemaReady()) return null;
        $sql='SELECT TOP 1 cp.[id],cp.[name],cp.[status],cp.[start_at],cp.[end_at] '
            .'FROM ['.self::CAMPAIGNS_TABLE.'] cp '
            .'INNER JOIN ['.self::COLLECTION_ITEMS_TABLE.'] existing_items ON existing_items.[collection_id]=cp.[collection_id] '
            .'INNER JOIN ['.self::COLLECTION_ITEMS_TABLE.'] target_items ON target_items.[item_id]=existing_items.[item_id] AND target_items.[collection_id]=? '
            .'WHERE cp.[id]<>? AND cp.[status] IN (\'scheduled\',\'active\') AND cp.[start_at]<? AND cp.[end_at]>? '
            .'ORDER BY CASE cp.[status] WHEN \'active\' THEN 0 ELSE 1 END,cp.[start_at] ASC,cp.[id] ASC';
        $row=$this->db->query_fetch_single($sql,array((int)$collectionId,(int)$excludeId,(string)$endAt,(string)$startAt));
        return is_array($row)?$row:null;
    }

    private function normalizeCampaignInput($input, $existing=null) {
        $name=trim((string)($input['name'] ?? ''));
        if(mb_strlen($name)<2 || mb_strlen($name)>120) throw new \InvalidArgumentException('El nombre de la campaña debe tener entre 2 y 120 caracteres.');
        $collectionId=isset($input['collection_id']) && filter_var($input['collection_id'],FILTER_VALIDATE_INT)!==false ? (int)$input['collection_id'] : 0;
        $collection=$this->getCollection($collectionId);
        if(!is_array($collection)) throw new \InvalidArgumentException('Selecciona una colección válida.');
        if((int)($collection['item_count'] ?? 0)<1) throw new \InvalidArgumentException('La colección seleccionada está vacía.');
        $start=$this->localDateToUtc($input['start_at'] ?? '');
        $end=$this->localDateToUtc($input['end_at'] ?? '');
        if(!$start || !$end) throw new \InvalidArgumentException('Define inicio y fin de campaña.');
        if(strtotime($end.' UTC') <= strtotime($start.' UTC')) throw new \InvalidArgumentException('El fin de campaña debe ser posterior al inicio.');
        if(strtotime($end.' UTC') <= time()) throw new \InvalidArgumentException('El fin de campaña debe quedar en el futuro.');
        $publish=!empty($input['publish_on_start']) ? 1 : 0;
        $feature=!empty($input['feature_on_start']) ? 1 : 0;
        $discount=isset($input['discount_percent']) && $input['discount_percent']!=='' ? (int)$input['discount_percent'] : 0;
        if($discount<0 || $discount>95) throw new \InvalidArgumentException('El descuento debe estar entre 1% y 95%, o quedar vacío.');
        if($discount===0) $discount=null;
        $restore=isset($input['restore_on_end']) ? (int)!empty($input['restore_on_end']) : 1;
        if(!$publish && !$feature && $discount===null) throw new \InvalidArgumentException('La campaña debe publicar, destacar o aplicar una promoción.');
        $excludeId=is_array($existing)?(int)($existing['id']??0):0;
        $overlap=$this->findCampaignOverlap($collectionId,$start,$end,$excludeId);
        if(is_array($overlap)) throw new \InvalidArgumentException('La colección comparte ítems con la campaña #'.(int)$overlap['id'].' · '.$overlap['name'].' dentro del mismo horario. Reprograma una de las dos para evitar restauraciones ambiguas.');
        return array(
            'name'=>$name,'collection_id'=>$collectionId,'collection_name'=>(string)$collection['name'],'start_at'=>$start,'end_at'=>$end,
            'publish_on_start'=>$publish,'feature_on_start'=>$feature,'discount_percent'=>$discount,'restore_on_end'=>$restore,
        );
    }

    public function saveCampaign($input) {
        if(!$this->campaignSchemaReady()) throw new \RuntimeException('Instala / valida las tablas de Arsenal 1.7.0 antes de administrar campañas.');
        $id=isset($input['campaign_id']) && filter_var($input['campaign_id'],FILTER_VALIDATE_INT)!==false ? (int)$input['campaign_id'] : 0;
        $existing=$id>0 ? $this->getCampaign($id) : null;
        if($id>0 && !is_array($existing)) throw new \RuntimeException('La campaña ya no existe.');
        if($id>0 && (string)$existing['status']!=='scheduled') throw new \RuntimeException('Solo las campañas programadas pueden editarse.');
        $data=$this->normalizeCampaignInput($input,$existing);
        if($id>0) {
            $affected=$this->db->query_affected(
                'UPDATE ['.self::CAMPAIGNS_TABLE.'] SET [name]=?,[collection_id]=?,[collection_name]=?,[start_at]=?,[end_at]=?,[publish_on_start]=?,[feature_on_start]=?,[discount_percent]=?,[restore_on_end]=?,[last_error]=NULL,[updated_at]=SYSUTCDATETIME() WHERE [id]=? AND [status]=\'scheduled\'',
                array($data['name'],$data['collection_id'],$data['collection_name'],$data['start_at'],$data['end_at'],$data['publish_on_start'],$data['feature_on_start'],$data['discount_percent'],$data['restore_on_end'],$id)
            );
            if($affected!==1) throw new \RuntimeException('No se pudo actualizar la campaña.');
        } else {
            $affected=$this->db->query_affected(
                'INSERT INTO ['.self::CAMPAIGNS_TABLE.'] ([name],[collection_id],[collection_name],[status],[start_at],[end_at],[publish_on_start],[feature_on_start],[discount_percent],[restore_on_end]) VALUES (?,?,?,\'scheduled\',?,?,?,?,?,?)',
                array($data['name'],$data['collection_id'],$data['collection_name'],$data['start_at'],$data['end_at'],$data['publish_on_start'],$data['feature_on_start'],$data['discount_percent'],$data['restore_on_end'])
            );
            if($affected!==1) throw new \RuntimeException('No se pudo crear la campaña.');
            $row=$this->db->query_fetch_single('SELECT TOP 1 [id] FROM ['.self::CAMPAIGNS_TABLE.'] WHERE [name]=? AND [collection_id]=? ORDER BY [id] DESC', array($data['name'],$data['collection_id']));
            $id=is_array($row)?(int)($row['id']??0):0;
            if($id<1) throw new \RuntimeException('La campaña fue creada pero no se pudo resolver su identificador.');
        }
        if(class_exists('NexoraAudit')) \NexoraAudit::log('itemshop.campaign_save','itemshop_campaign',$id,null,$data,$this->realm);
        return $id;
    }

    public function cancelCampaign($id) {
        if(!$this->campaignSchemaReady() || filter_var($id,FILTER_VALIDATE_INT)===false || (int)$id<1) throw new \InvalidArgumentException('Campaña inválida.');
        $id=(int)$id;
        $affected=$this->db->query_affected('UPDATE ['.self::CAMPAIGNS_TABLE.'] SET [status]=\'cancelled\',[last_error]=NULL,[ended_at]=SYSUTCDATETIME(),[updated_at]=SYSUTCDATETIME() WHERE [id]=? AND [status]=\'scheduled\'', array($id));
        if($affected!==1) throw new \RuntimeException('Solo una campaña programada puede cancelarse sin restauración.');
        if(class_exists('NexoraAudit')) \NexoraAudit::log('itemshop.campaign_cancel','itemshop_campaign',$id,null,array(),$this->realm);
        return true;
    }

    private function campaignItemRows($collectionId, $lock=false) {
        $hint=$lock?' WITH (UPDLOCK,HOLDLOCK)':'';
        $rows=$this->db->query_fetch(
            'SELECT i.* FROM ['.self::COLLECTION_ITEMS_TABLE.'] ci INNER JOIN ['.self::ITEMS_TABLE.'] i'.$hint.' ON i.[id]=ci.[item_id] WHERE ci.[collection_id]=? ORDER BY ci.[sort_order] ASC,i.[id] ASC',
            array((int)$collectionId)
        );
        return is_array($rows)?$rows:array();
    }

    private function campaignDiscountPrice($price, $discount) {
        $price=(int)$price; $discount=(int)$discount;
        if($price<1 || $discount<1 || $discount>95) return null;
        $sale=(int)floor($price*(100-$discount)/100);
        if($sale<1) $sale=1;
        return $sale<$price ? $sale : null;
    }

    private function campaignRowPlan($row, $campaign) {
        $before=array(); $after=array(); $fields=array(); $guards=array();
        $pairs=array();
        if(!empty($campaign['publish_on_start'])) $pairs['active']=1;
        if(!empty($campaign['feature_on_start'])) $pairs['featured']=1;
        if(isset($campaign['discount_percent']) && $campaign['discount_percent']!==null && $campaign['discount_percent']!=='') {
            $sale=$this->campaignDiscountPrice((int)$row['price_wc'],(int)$campaign['discount_percent']);
            if($sale===null) throw new \RuntimeException('El producto '.$row['name'].' no admite el descuento configurado sobre su precio normal.');
            $pairs['sale_price_wc']=$sale;
            $pairs['sale_start']=(string)$campaign['start_at'];
            $pairs['sale_end']=(string)$campaign['end_at'];
            $guards['price_wc']=(int)$row['price_wc'];
        }
        foreach($pairs as $field=>$value) {
            $current=$this->normalizeOperationValue($field,$row[$field] ?? null);
            $target=$this->normalizeOperationValue($field,$value);
            if($current===$target) continue;
            $fields[]=$field;
            $before[$field]=$row[$field] ?? null;
            $after[$field]=$value;
        }
        return array('id'=>(int)$row['id'],'name'=>(string)$row['name'],'fields'=>$fields,'before'=>$before,'after'=>$after,'guards'=>$guards);
    }

    private function recordCampaignError($id, $message) {
        if(!$this->campaignSchemaReady()) return;
        $this->db->query('UPDATE ['.self::CAMPAIGNS_TABLE.'] SET [last_error]=?,[updated_at]=SYSUTCDATETIME() WHERE [id]=?', array(mb_substr((string)$message,0,500),(int)$id));
    }

    public function startCampaign($id, $manual=false) {
        if(!$this->campaignSchemaReady()) throw new \RuntimeException('Instala / valida las tablas de Arsenal 1.7.0 antes de ejecutar campañas.');
        if(filter_var($id,FILTER_VALIDATE_INT)===false || (int)$id<1) throw new \InvalidArgumentException('Campaña inválida.');
        $id=(int)$id;
        if(!$this->db->beginTransaction()) throw new \RuntimeException('No se pudo iniciar la campaña.');
        try {
            $campaign=$this->db->query_fetch_single('SELECT * FROM ['.self::CAMPAIGNS_TABLE.'] WITH (UPDLOCK,HOLDLOCK) WHERE [id]=?',array($id));
            if(!is_array($campaign)) throw new \RuntimeException('La campaña ya no existe.');
            if((string)$campaign['status']!=='scheduled') { $this->db->rollBack(); return array('affected'=>0,'status'=>(string)$campaign['status']); }
            $now=time();
            if(!$manual && strtotime((string)$campaign['start_at'].' UTC')>$now) { $this->db->rollBack(); return array('affected'=>0,'status'=>'scheduled'); }
            if(strtotime((string)$campaign['end_at'].' UTC')<=$now) throw new \RuntimeException('La campaña venció antes de poder iniciarse. Reprograma sus fechas.');
            if($manual && strtotime((string)$campaign['start_at'].' UTC')>$now) $campaign['start_at']=gmdate('Y-m-d H:i:s',$now);
            $overlap=$this->findCampaignOverlap((int)$campaign['collection_id'],(string)$campaign['start_at'],(string)$campaign['end_at'],$id);
            if(is_array($overlap)) throw new \RuntimeException('No se puede iniciar: comparte ítems y horario con la campaña #'.(int)$overlap['id'].' · '.$overlap['name'].'.');
            $rows=$this->campaignItemRows((int)$campaign['collection_id'],true);
            if(count($rows)<1) throw new \RuntimeException('La colección de la campaña está vacía.');
            $items=array();
            foreach($rows as $row) {
                $plan=$this->campaignRowPlan($row,$campaign);
                if(count($plan['fields'])) $items[]=$plan;
            }
            $payload=array('version'=>1,'realm'=>$this->realm,'campaign_id'=>$id,'collection_id'=>(int)$campaign['collection_id'],'collection_name'=>(string)$campaign['collection_name'],'items'=>$items);
            $json=json_encode($payload,JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES);
            if(!is_string($json)) throw new \RuntimeException('No se pudo serializar el snapshot de la campaña.');
            foreach($items as $item) {
                $sets=array(); $params=array();
                foreach($item['fields'] as $field) { $sets[]='['.$field.']=?'; $params[]=$item['after'][$field] ?? null; }
                $sets[]='[updated_at]=SYSUTCDATETIME()'; $params[]=(int)$item['id'];
                $affected=$this->db->query_affected('UPDATE ['.self::ITEMS_TABLE.'] SET '.implode(',',$sets).' WHERE [id]=?',$params);
                if($affected!==1) throw new \RuntimeException('No se pudo aplicar la campaña a '.$item['name'].'.');
            }
            $marked=$this->db->query_affected('UPDATE ['.self::CAMPAIGNS_TABLE.'] SET [status]=\'active\',[start_at]=?,[snapshot]=?,[affected]=?,[restored_count]=0,[conflict_count]=0,[last_error]=NULL,[started_at]=SYSUTCDATETIME(),[updated_at]=SYSUTCDATETIME() WHERE [id]=? AND [status]=\'scheduled\'',array($campaign['start_at'],$json,count($items),$id));
            if($marked!==1) throw new \RuntimeException('No se pudo marcar la campaña como activa.');
            if(!$this->db->commit()) throw new \RuntimeException('No se pudo confirmar el inicio de la campaña.');
        } catch(\Throwable $e) { $this->db->rollBack(); throw $e; }
        if(class_exists('NexoraAudit')) \NexoraAudit::log('itemshop.campaign_start','itemshop_campaign',$id,null,array('affected'=>count($items),'collection_id'=>(int)$campaign['collection_id']),$this->realm);
        return array('affected'=>count($items),'status'=>'active');
    }

    public function endCampaign($id, $manual=false) {
        if(!$this->campaignSchemaReady()) throw new \RuntimeException('Instala / valida las tablas de Arsenal 1.7.0 antes de ejecutar campañas.');
        if(filter_var($id,FILTER_VALIDATE_INT)===false || (int)$id<1) throw new \InvalidArgumentException('Campaña inválida.');
        $id=(int)$id;
        if(!$this->db->beginTransaction()) throw new \RuntimeException('No se pudo iniciar el cierre de la campaña.');
        try {
            $campaign=$this->db->query_fetch_single('SELECT * FROM ['.self::CAMPAIGNS_TABLE.'] WITH (UPDLOCK,HOLDLOCK) WHERE [id]=?',array($id));
            if(!is_array($campaign)) throw new \RuntimeException('La campaña ya no existe.');
            if((string)$campaign['status']!=='active') { $this->db->rollBack(); return array('restored'=>(int)($campaign['restored_count']??0),'conflicts'=>(int)($campaign['conflict_count']??0),'status'=>(string)$campaign['status']); }
            if(!$manual && strtotime((string)$campaign['end_at'].' UTC')>time()) { $this->db->rollBack(); return array('restored'=>0,'conflicts'=>0,'status'=>'active'); }
            $payload=json_decode((string)($campaign['snapshot']??''),true);
            if(!is_array($payload) || !isset($payload['items']) || !is_array($payload['items'])) throw new \RuntimeException('El snapshot de la campaña no es válido.');
            if(isset($payload['realm']) && (string)$payload['realm']!==(string)$this->realm) throw new \RuntimeException('El snapshot pertenece a otro reino.');
            $restored=0; $conflicts=0;
            if(!empty($campaign['restore_on_end'])) {
                $ids=array(); foreach($payload['items'] as $item) if(is_array($item) && !empty($item['id'])) $ids[]=(int)$item['id'];
                $ids=array_values(array_unique($ids));
                $currentById=array();
                foreach(array_chunk($ids,1000) as $chunk) {
                    $currentRows=$this->fetchAdminItemsByIds($chunk,true,2000);
                    foreach($currentRows as $row) $currentById[(int)$row['id']]=$row;
                }
                $allowed=array_flip($this->catalogOperationFields());
                foreach($payload['items'] as $item) {
                    if(!is_array($item) || empty($item['id']) || empty($item['fields']) || !is_array($item['fields'])) { $conflicts++; continue; }
                    $itemId=(int)$item['id'];
                    if(!isset($currentById[$itemId])) { $conflicts++; continue; }
                    $fields=array_values(array_filter(array_map('strval',$item['fields']),function($field) use($allowed){return isset($allowed[$field]);}));
                    if(count($fields)<1) continue;
                    $matches=true;
                    if(isset($item['guards']) && is_array($item['guards'])) {
                        foreach($item['guards'] as $guardField=>$guardValue) {
                            if(!isset($allowed[$guardField])) continue;
                            $now=$this->normalizeOperationValue($guardField,$currentById[$itemId][$guardField]??null);
                            $expected=$this->normalizeOperationValue($guardField,$guardValue);
                            if($now!==$expected) { $matches=false; break; }
                        }
                    }
                    if($matches) foreach($fields as $field) {
                        $now=$this->normalizeOperationValue($field,$currentById[$itemId][$field]??null);
                        $expected=$this->normalizeOperationValue($field,$item['after'][$field]??null);
                        if($now!==$expected) { $matches=false; break; }
                    }
                    if(!$matches) { $conflicts++; continue; }
                    $sets=array(); $params=array();
                    foreach($fields as $field) { $sets[]='['.$field.']=?'; $params[]=$item['before'][$field]??null; }
                    $sets[]='[updated_at]=SYSUTCDATETIME()'; $params[]=$itemId;
                    $affected=$this->db->query_affected('UPDATE ['.self::ITEMS_TABLE.'] SET '.implode(',',$sets).' WHERE [id]=?',$params);
                    if($affected!==1) { $conflicts++; continue; }
                    $restored++;
                }
            }
            $marked=$this->db->query_affected('UPDATE ['.self::CAMPAIGNS_TABLE.'] SET [status]=\'completed\',[restored_count]=?,[conflict_count]=?,[last_error]=NULL,[ended_at]=SYSUTCDATETIME(),[updated_at]=SYSUTCDATETIME() WHERE [id]=? AND [status]=\'active\'',array($restored,$conflicts,$id));
            if($marked!==1) throw new \RuntimeException('No se pudo cerrar la campaña.');
            if(!$this->db->commit()) throw new \RuntimeException('No se pudo confirmar el cierre de la campaña.');
        } catch(\Throwable $e) { $this->db->rollBack(); throw $e; }
        if(class_exists('NexoraAudit')) \NexoraAudit::log('itemshop.campaign_end','itemshop_campaign',$id,null,array('restored'=>$restored,'conflicts'=>$conflicts,'restore_on_end'=>(int)$campaign['restore_on_end']),$this->realm);
        return array('restored'=>$restored,'conflicts'=>$conflicts,'status'=>'completed');
    }

    public function processCampaigns($limit=20) {
        if(!$this->campaignSchemaReady()) return array('started'=>0,'ended'=>0,'errors'=>array());
        $limit=max(1,min(100,(int)$limit));
        $started=0; $ended=0; $errors=array();
        $due=$this->db->query_fetch('SELECT TOP '.$limit.' [id] FROM ['.self::CAMPAIGNS_TABLE.'] WHERE [status]=\'scheduled\' AND [start_at]<=SYSUTCDATETIME() ORDER BY [start_at] ASC,[id] ASC');
        if(is_array($due)) foreach($due as $row) {
            $id=(int)$row['id'];
            try { $result=$this->startCampaign($id,false); if(($result['status']??'')==='active') $started++; }
            catch(\Throwable $e) { $this->recordCampaignError($id,$e->getMessage()); $errors[]='Inicio #'.$id.': '.$e->getMessage(); }
        }
        $dueEnd=$this->db->query_fetch('SELECT TOP '.$limit.' [id] FROM ['.self::CAMPAIGNS_TABLE.'] WHERE [status]=\'active\' AND [end_at]<=SYSUTCDATETIME() ORDER BY [end_at] ASC,[id] ASC');
        if(is_array($dueEnd)) foreach($dueEnd as $row) {
            $id=(int)$row['id'];
            try { $result=$this->endCampaign($id,false); if(($result['status']??'')==='completed') $ended++; }
            catch(\Throwable $e) { $this->recordCampaignError($id,$e->getMessage()); $errors[]='Cierre #'.$id.': '.$e->getMessage(); }
        }
        return array('started'=>$started,'ended'=>$ended,'errors'=>$errors);
    }

    public function deleteItem($id) {
        if(!$this->schemaReady()) throw new \RuntimeException('Primero instala el esquema de la tienda desde Configuración.');
        if(!\Validator::UnsignedNumber($id) || (int)$id < 1) throw new \InvalidArgumentException('Ítem inválido.');

        $id = (int)$id;
        if(!$this->db->beginTransaction()) throw new \RuntimeException('No se pudo iniciar la eliminación del ítem.');

        try {
            $item = $this->db->query_fetch_single('SELECT * FROM ['.self::ITEMS_TABLE.'] WITH (UPDLOCK,HOLDLOCK) WHERE [id]=?', array($id));
            if(!is_array($item)) throw new \RuntimeException('El ítem solicitado ya no existe.');

            $orderRow = $this->db->query_fetch_single('SELECT COUNT(*) AS [total] FROM ['.self::ORDERS_TABLE.'] WHERE [item_id]=?', array($id));
            $ordersPreserved = is_array($orderRow) ? (int)($orderRow['total'] ?? 0) : 0;

            $imageFile = !empty($item['image_file']) ? basename((string)$item['image_file']) : '';
            $deleted = $this->db->query_affected('DELETE FROM ['.self::ITEMS_TABLE.'] WHERE [id]=?', array($id));
            if($deleted !== 1) throw new \RuntimeException('No se pudo eliminar el ítem del catálogo.');

            $imageReferences = 0;
            if($imageFile !== '') {
                $imageRow = $this->db->query_fetch_single('SELECT COUNT(*) AS [total] FROM ['.self::ITEMS_TABLE.'] WHERE [image_file]=?', array($imageFile));
                $imageReferences = is_array($imageRow) ? (int)($imageRow['total'] ?? 0) : 0;
            }

            if(!$this->db->commit()) throw new \RuntimeException('No se pudo confirmar la eliminación del ítem.');
        } catch(\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }

        $imageDeleted = false;
        if($imageFile !== '' && $imageReferences === 0) {
            $imagePath = $this->imageDirectory().$imageFile;
            if(is_file($imagePath)) $imageDeleted = @unlink($imagePath);
        }

        if(class_exists('NexoraAudit')) {
            \NexoraAudit::log('itemshop.item_delete', 'itemshop_item', $id, $item, array(
                'orders_preserved'=>$ordersPreserved,
                'image_deleted'=>$imageDeleted ? 1 : 0,
                'history_preserved'=>1,
            ), $this->realm);
        }

        return array(
            'id'=>$id,
            'name'=>(string)($item['name'] ?? ''),
            'orders_preserved'=>$ordersPreserved,
            'image_deleted'=>$imageDeleted,
        );
    }

    private function validateItemInput($input) {
        $name = trim((string)($input['name'] ?? ''));
        $category = trim((string)($input['category'] ?? 'Destacados'));
        $description = trim((string)($input['description'] ?? ''));
        if($name === '' || mb_strlen($name) > 100) throw new \InvalidArgumentException('Nombre de ítem inválido.');
        if($category === '' || mb_strlen($category) > 50) throw new \InvalidArgumentException('Categoría inválida.');
        if(mb_strlen($description) > 500) throw new \InvalidArgumentException('La descripción no puede superar 500 caracteres.');
        $ints = array(
            'item_section'=>array(0,15),'item_index'=>array(0,511),'item_level'=>array(0,15),
            'option_level'=>array(0,7),'excellent_mask'=>array(0,63),'ancient_option'=>array(0,15),
            'harmony_option'=>array(0,255),'durability'=>array(0,255),
            'width'=>array(1,8),'height'=>array(1,15),'price_wc'=>array(1,2147483647),
            'stock'=>array(-1,2147483647),'max_per_account'=>array(0,2147483647),'sort_order'=>array(-100000,100000)
        );
        $data = array('name'=>$name,'category'=>$category,'description'=>$description === '' ? null : $description);
        foreach($ints as $key=>$range) {
            if(!isset($input[$key]) || filter_var($input[$key], FILTER_VALIDATE_INT) === false) throw new \InvalidArgumentException('Valor inválido: '.$key.'.');
            $value = (int)$input[$key];
            if($value < $range[0] || $value > $range[1]) throw new \InvalidArgumentException('Valor fuera de rango: '.$key.'.');
            $data[$key] = $value;
        }
        foreach(array('has_skill','has_luck','active','featured','option_380') as $key) $data[$key] = !empty($input[$key]) ? 1 : 0;

        $socketFields = array('socket_option_1','socket_option_2','socket_option_3','socket_option_4','socket_option_5');
        $socketValues = array();
        $hasExplicitSockets = false;
        foreach($socketFields as $field) if(isset($input[$field])) { $hasExplicitSockets = true; break; }
        if($hasExplicitSockets) {
            foreach($socketFields as $field) {
                if(!isset($input[$field]) || filter_var($input[$field], FILTER_VALIDATE_INT) === false) throw new \InvalidArgumentException('Valor inválido: '.$field.'.');
                $value=(int)$input[$field];
                if($value < 0 || $value > 255) throw new \InvalidArgumentException('Valor fuera de rango: '.$field.'.');
                $socketValues[]=$value;
                $data[$field]=$value;
            }
        } else {
            $legacyCount = isset($input['socket_count']) && filter_var($input['socket_count'], FILTER_VALIDATE_INT) !== false ? max(0,min(5,(int)$input['socket_count'])) : 0;
            for($n=1;$n<=5;$n++) { $value=$n<=$legacyCount ? 254 : 255; $socketValues[]=$value; $data['socket_option_'.$n]=$value; }
        }
        $data['socket_count']=0;
        foreach($socketValues as $value) if($value !== 255) $data['socket_count']++;
        $data['socket_bonus'] = isset($input['socket_bonus']) && filter_var($input['socket_bonus'], FILTER_VALIDATE_INT) !== false ? (int)$input['socket_bonus'] : 0;
        if($data['socket_bonus'] < 0 || $data['socket_bonus'] > 255) throw new \InvalidArgumentException('Valor fuera de rango: socket_bonus.');

        $authoritative = self::authoritativeItem($data['item_section'], $data['item_index']);
        if(is_array($authoritative)) {
            if(empty($authoritative['encoder_compatible'])) throw new \InvalidArgumentException('Este catálogo define un Index mayor de 511 y el formato DbVersion 3 confirmado para Arsenal no puede entregarlo de forma segura.');
            if((int)$authoritative['width'] !== (int)$data['width'] || (int)$authoritative['height'] !== (int)$data['height']) {
                throw new \InvalidArgumentException('Las dimensiones no coinciden con el catálogo del Warehouse Editor para '.$authoritative['name'].': deben ser '.(int)$authoritative['width'].'x'.(int)$authoritative['height'].'.');
            }
            $smartForItem = self::smartOptionsForItem($data['item_section'], $data['item_index']);
            $allowedExcellentMask = 0;
            if(isset($smartForItem['excellent_profile']['options']) && is_array($smartForItem['excellent_profile']['options'])) {
                foreach($smartForItem['excellent_profile']['options'] as $excellentRow) {
                    if(is_array($excellentRow) && isset($excellentRow['bit'])) $allowedExcellentMask |= ((int)$excellentRow['bit'] & 0x3F);
                }
            }
            if(($data['excellent_mask'] & ~$allowedExcellentMask) !== 0) {
                if((int)$authoritative['excellent_type'] >= 3 && (int)$authoritative['excellent_type'] <= 5) {
                    throw new \InvalidArgumentException('El perfil Excellent especial de este ítem está identificado, pero sus bits no están definidos en los XML suministrados. Arsenal lo mantiene bloqueado hasta validar el mapa exacto.');
                }
                throw new \InvalidArgumentException('Las opciones Excellent seleccionadas no pertenecen al perfil permitido para este ítem.');
            }
            if((int)$authoritative['skill'] <= 0 && $data['has_skill']) throw new \InvalidArgumentException('Este ítem no declara Skill en Item.txt.');
            if((int)$authoritative['have_option'] <= 0 && $data['has_luck']) throw new \InvalidArgumentException('Este ítem no admite Luck según Item.txt.');
            if((int)$authoritative['have_option'] <= 0 && $data['option_level'] > 0) throw new \InvalidArgumentException('Este ítem no admite opción adicional según Item.txt.');
            if(empty($authoritative['level380']) && $data['option_380']) throw new \InvalidArgumentException('Este ítem no está marcado como compatible con opción 380 en Items.xml.');

            $allowedAncient = self::ancientAllowedRaw($data['item_section'], $data['item_index']);
            if(!in_array($data['ancient_option'], $allowedAncient, true)) throw new \InvalidArgumentException('La opción Ancient seleccionada no corresponde a este ítem según Ancient_Items.xml.');

            $socketCapable = !empty($authoritative['socket_capable']);
            if($socketCapable) {
                if($data['harmony_option'] !== 0) throw new \InvalidArgumentException('Los ítems Socket usan el byte compartido de Socket Bonus; Harmony debe quedar en Ninguno.');
                $allowedSockets = self::socketAllowedValues($data['item_section']);
                $socketClosed = false;
                foreach($socketValues as $socketValue) {
                    if(!in_array($socketValue, $allowedSockets, true)) throw new \InvalidArgumentException('Una opción Socket no pertenece a la familia válida para este ítem.');
                    if($socketValue === 255) $socketClosed = true;
                    elseif($socketClosed) throw new \InvalidArgumentException('Los slots Socket deben ser contiguos desde el Slot 1.');
                }
                if(!in_array($data['socket_bonus'], self::socketAllowedBonuses(), true)) throw new \InvalidArgumentException('Socket Bonus inválido.');
                if($data['socket_bonus'] !== 0 && $data['socket_count'] < 1) throw new \InvalidArgumentException('Socket Bonus requiere al menos un slot Socket configurado.');
            } else {
                foreach($socketValues as $socketValue) if($socketValue !== 255) throw new \InvalidArgumentException('Este ítem no admite sockets según Items.xml.');
                if($data['socket_bonus'] !== 0) throw new \InvalidArgumentException('Este ítem no admite Socket Bonus.');
                if(!in_array($data['harmony_option'], self::harmonyAllowedRaw($data['item_section']), true)) throw new \InvalidArgumentException('La opción Harmony no pertenece a la familia válida para este ítem.');
            }
        }
        $sale = trim((string)($input['sale_price_wc'] ?? ''));
        $data['sale_price_wc'] = $sale === '' ? null : (int)$sale;
        if($data['sale_price_wc'] !== null && ($data['sale_price_wc'] < 1 || $data['sale_price_wc'] >= $data['price_wc'])) throw new \InvalidArgumentException('El precio promocional debe ser mayor que cero y menor que el precio normal.');
        $data['sale_start'] = $this->localDateToUtc($input['sale_start'] ?? '');
        $data['sale_end'] = $this->localDateToUtc($input['sale_end'] ?? '');
        if($data['sale_price_wc'] === null) { $data['sale_start'] = null; $data['sale_end'] = null; }
        if($data['sale_start'] !== null && $data['sale_end'] !== null && strtotime($data['sale_end'].' UTC') <= strtotime($data['sale_start'].' UTC')) throw new \InvalidArgumentException('La promoción debe terminar después de iniciar.');
        return $data;
    }

    public function currentPrice($item, $at=null) {
        $price = (int)$item['price_wc'];
        $sale = isset($item['sale_price_wc']) ? (int)$item['sale_price_wc'] : 0;
        if($sale < 1 || $sale >= $price) return $price;
        $at = $at === null ? time() : (int)$at;
        $start = empty($item['sale_start']) ? null : strtotime((string)$item['sale_start'].' UTC');
        $end = empty($item['sale_end']) ? null : strtotime((string)$item['sale_end'].' UTC');
        if($start !== null && $at < $start) return $price;
        if($end !== null && $at >= $end) return $price;
        return $sale;
    }

    public function isOnSale($item) { return $this->currentPrice($item) < (int)$item['price_wc']; }

    public function discountPercent($item) {
        $normal = max(1,(int)$item['price_wc']);
        $current = $this->currentPrice($item);
        return $current < $normal ? max(1,(int)round((1-($current/$normal))*100)) : 0;
    }

    public function imageUrl($item) {
        if(!empty($item['image_file'])) {
            $file = basename((string)$item['image_file']);
            if(is_file($this->imageDirectory().$file)) return __PATH_TEMPLATE_IMG__.'itemshop/'.rawurlencode($file);
        }
        if(class_exists('ProfileEquipment')) return \ProfileEquipment::itemImageUrl((int)$item['item_section'],(int)$item['item_index']);
        return '';
    }

    public static function issuePurchaseToken($itemId=0) {
        if(session_status() !== PHP_SESSION_ACTIVE) return '';
        if(!isset($_SESSION['nexora_itemshop_tokens']) || !is_array($_SESSION['nexora_itemshop_tokens'])) $_SESSION['nexora_itemshop_tokens'] = array();
        $now = time();
        foreach($_SESSION['nexora_itemshop_tokens'] as $token=>$row) {
            if(!is_array($row) || empty($row['expires']) || (int)$row['expires'] < $now) unset($_SESSION['nexora_itemshop_tokens'][$token]);
        }
        while(count($_SESSION['nexora_itemshop_tokens']) >= 12) array_shift($_SESSION['nexora_itemshop_tokens']);
        $token = bin2hex(random_bytes(24));
        $realm = class_exists('NexoraRealm') ? \NexoraRealm::active() : '';
        $_SESSION['nexora_itemshop_tokens'][$token] = array('item_id'=>(int)$itemId,'realm'=>$realm,'expires'=>$now+900);
        return $token;
    }

    public static function consumePurchaseToken($itemId, $token) {
        if(session_status() !== PHP_SESSION_ACTIVE || !is_string($token) || $token === '' || empty($_SESSION['nexora_itemshop_tokens'][$token])) return false;
        $row = $_SESSION['nexora_itemshop_tokens'][$token];
        unset($_SESSION['nexora_itemshop_tokens'][$token]);
        if(!is_array($row) || empty($row['expires']) || (int)$row['expires'] < time()) return false;
        $boundItem = isset($row['item_id']) ? (int)$row['item_id'] : -1;
        if($boundItem !== 0 && $boundItem !== (int)$itemId) return false;
        if(isset($row['realm']) && (string)$row['realm'] !== '') {
            $realm = class_exists('NexoraRealm') ? (string)\NexoraRealm::active() : '';
            if(!hash_equals((string)$row['realm'], $realm)) return false;
        }
        return true;
    }

    public function purchase($itemId, $userId, $username) {
        if(!$this->isEnabled()) throw new \RuntimeException('La tienda está pausada en este reino.');
        if(!$this->schemaReady()) throw new \RuntimeException('La tienda todavía no está instalada en este reino.');
        if(!\Validator::UnsignedNumber($userId) || !\Validator::UsernameLength($username) || !\Validator::AlphaNumeric($username)) throw new \InvalidArgumentException('Sesión de cuenta inválida.');
        $userId=(int)$userId; $username=(string)$username;
        if(!$this->db->beginTransaction()) throw new \RuntimeException('No se pudo iniciar la compra.');
        try {
            $item = $this->getItem($itemId, true);
            if(!is_array($item) || (int)$item['active'] !== 1) throw new \RuntimeException('Este ítem ya no está disponible.');
            if((int)$item['stock'] === 0) throw new \RuntimeException('El stock de este ítem se agotó.');

            $account = $this->db->query_fetch_single('SELECT ['._CLMN_USERNM_.'] AS username FROM ['._TBL_MI_.'] WITH (UPDLOCK,HOLDLOCK) WHERE ['._CLMN_MEMBID_.']=? AND ['._CLMN_USERNM_.']=?', array($userId,$username));
            if(!is_array($account)) throw new \RuntimeException('La cuenta no pertenece al reino activo.');
            $online = $this->db->query_fetch_single('SELECT ['._CLMN_CONNSTAT_.'] AS online FROM ['._TBL_MS_.'] WITH (UPDLOCK,HOLDLOCK) WHERE ['._CLMN_MS_MEMBID_.']=?', array($username));
            if(is_array($online) && (int)$online['online'] === 1) throw new \RuntimeException('Desconecta la cuenta del juego antes de comprar. El baúl se protege mientras estás conectado.');

            $limit = (int)$item['max_per_account'];
            if($limit > 0) {
                $usage = $this->db->query_fetch_single('SELECT COUNT(*) AS total FROM ['.self::ORDERS_TABLE.'] WITH (UPDLOCK,HOLDLOCK) WHERE [account_username]=? AND [item_id]=? AND [status]=?', array($username,(int)$item['id'],'delivered'));
                if(is_array($usage) && (int)$usage['total'] >= $limit) throw new \RuntimeException('Esta cuenta ya alcanzó el límite de compra del ítem.');
            }

            $warehouse = $this->lockWarehouse($username);
            $placement = $this->findWarehousePlacement($warehouse['hex'], (int)$item['width'], (int)$item['height']);
            $price = $this->currentPrice($item);
            $balance = $this->debitCurrency($username, $price);
            $serial = $this->nextItemSerial();
            $itemHex = $this->encodeItem($item, $serial);
            $warehouseHex = substr_replace($warehouse['hex'], $itemHex, $placement*self::ITEM_BYTES*2, self::ITEM_BYTES*2);
            $saved = $this->db->query_affected('UPDATE [warehouse] SET [Items]=CONVERT(varbinary(max),?,2),[DbVersion]=3 WHERE [AccountID]=?', array($warehouseHex,$username));
            if($saved !== 1) throw new \RuntimeException('No se pudo guardar el ítem en el baúl. No se descontó WC.');

            if((int)$item['stock'] > 0) {
                $stock = $this->db->query_affected('UPDATE ['.self::ITEMS_TABLE.'] SET [stock]=[stock]-1,[updated_at]=SYSUTCDATETIME() WHERE [id]=? AND [stock]>0', array((int)$item['id']));
                if($stock !== 1) throw new \RuntimeException('El stock cambió durante la compra. Inténtalo nuevamente.');
            }
            $snapshot = json_encode($this->itemSnapshot($item), JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES);
            if($snapshot === false) throw new \RuntimeException('No se pudo registrar el detalle de la compra.');
            $logged = $this->db->query_affected(
                'INSERT INTO ['.self::ORDERS_TABLE.'] ([user_id],[account_username],[item_id],[item_name],[item_snapshot],[price_paid],[currency],[warehouse_slot],[item_serial],[status],[ip_address]) VALUES (?,?,?,?,?,?,?,?,?,?,?)',
                array($userId,$username,(int)$item['id'],(string)$item['name'],$snapshot,$price,$this->currencyColumn(),$placement,$serial,'delivered',class_exists('NexoraSecurity') ? \NexoraSecurity::clientIp() : ($_SERVER['REMOTE_ADDR'] ?? ''))
            );
            if($logged !== 1) throw new \RuntimeException('No se pudo registrar la entrega. La compra fue revertida.');
            if(!$this->db->commit()) throw new \RuntimeException('No se pudo confirmar la compra.');
            try {
                if(class_exists('NexoraAudit')) \NexoraAudit::log('itemshop.purchase','account',$username,null,array('item_id'=>(int)$item['id'],'item'=>$item['name'],'price'=>$price,'currency'=>$this->currencyColumn(),'slot'=>$placement,'serial'=>$serial,'balance'=>$balance),$this->realm);
            } catch(\Throwable $ignored) {}
            return array('item'=>$item,'price'=>$price,'balance'=>$balance,'slot'=>$placement,'serial'=>$serial);
        } catch(\Throwable $e) {
            if($this->db->inTransaction()) $this->db->rollBack();
            throw $e;
        }
    }

    public function ensureRewardDeliverySchema() {
        $row=$this->db->query_fetch_single("SELECT name FROM sysobjects WHERE xtype='U' AND name=?",array(self::REWARD_DELIVERIES_TABLE));
        if(is_array($row)) return true;
        $sql="CREATE TABLE [dbo].[".self::REWARD_DELIVERIES_TABLE."](
            [id] bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
            [reference_key] varchar(120) NOT NULL,
            [source] varchar(30) NOT NULL,
            [user_id] int NOT NULL,
            [account_username] varchar(50) NOT NULL,
            [item_id] int NOT NULL,
            [item_name] nvarchar(100) NOT NULL,
            [status] varchar(20) NOT NULL,
            [warehouse_slot] smallint NULL,
            [item_serial] bigint NULL,
            [created_at] datetime2 NOT NULL CONSTRAINT [DF_NISRD_created] DEFAULT(SYSUTCDATETIME()),
            [delivered_at] datetime2 NULL,
            CONSTRAINT [UX_NISRD_reference] UNIQUE ([reference_key])
        );
        CREATE INDEX [IX_NISRD_account] ON [dbo].[".self::REWARD_DELIVERIES_TABLE."]([account_username],[created_at] DESC);";
        if(!$this->db->query($sql)) throw new \RuntimeException('No se pudo preparar la tabla de entregas de recompensas de Arsenal.');
        return true;
    }

    public function rewardDeliveryReady() {
        $row=$this->db->query_fetch_single("SELECT name FROM sysobjects WHERE xtype='U' AND name=?",array(self::REWARD_DELIVERIES_TABLE));
        return is_array($row);
    }

    /**
     * Delivers an Arsenal item as an external system reward without charging the
     * shop currency or consuming catalog stock. referenceKey makes the operation
     * idempotent across retries (roulette claims, events, battle pass, etc.).
     */
    public function deliverReward($itemId,$userId,$username,$referenceKey,$source='reward') {
        if(!$this->schemaReady()) throw new \RuntimeException('Arsenal Nexora todavía no está instalado en este reino.');
        $this->ensureRewardDeliverySchema();
        if(!\Validator::UnsignedNumber($itemId) || (int)$itemId<1 || !\Validator::UnsignedNumber($userId) || !\Validator::UsernameLength($username) || !\Validator::AlphaNumeric($username)) throw new \InvalidArgumentException('Datos de recompensa inválidos.');
        $referenceKey=trim((string)$referenceKey); $source=trim((string)$source);
        if($referenceKey==='' || strlen($referenceKey)>120 || !preg_match('/^[A-Za-z0-9:_-]+$/D',$referenceKey)) throw new \InvalidArgumentException('Referencia de recompensa inválida.');
        if($source==='' || strlen($source)>30 || !preg_match('/^[A-Za-z0-9_-]+$/D',$source)) $source='reward';
        $itemId=(int)$itemId; $userId=(int)$userId; $username=(string)$username;

        if(!$this->db->beginTransaction()) throw new \RuntimeException('No se pudo iniciar la entrega de recompensa.');
        try {
            $existing=$this->db->query_fetch_single('SELECT * FROM ['.self::REWARD_DELIVERIES_TABLE.'] WITH (UPDLOCK,HOLDLOCK) WHERE [reference_key]=?',array($referenceKey));
            if(is_array($existing) && (string)$existing['status']==='delivered') {
                $this->db->rollBack();
                return array('duplicate'=>true,'slot'=>(int)$existing['warehouse_slot'],'serial'=>(int)$existing['item_serial'],'item_id'=>(int)$existing['item_id'],'item_name'=>(string)$existing['item_name']);
            }
            if(is_array($existing)) throw new \RuntimeException('La recompensa ya está reservada por otro proceso. Reintenta en unos segundos.');

            $item=$this->getItem($itemId,true);
            if(!is_array($item)) throw new \RuntimeException('El ítem de recompensa ya no existe en Arsenal Nexora.');
            $account=$this->db->query_fetch_single('SELECT ['._CLMN_USERNM_.'] AS username FROM ['._TBL_MI_.'] WITH (UPDLOCK,HOLDLOCK) WHERE ['._CLMN_MEMBID_.']=? AND ['._CLMN_USERNM_.']=?',array($userId,$username));
            if(!is_array($account)) throw new \RuntimeException('La cuenta no pertenece al reino activo.');
            $online=$this->db->query_fetch_single('SELECT ['._CLMN_CONNSTAT_.'] AS online FROM ['._TBL_MS_.'] WITH (UPDLOCK,HOLDLOCK) WHERE ['._CLMN_MS_MEMBID_.']=?',array($username));
            if(is_array($online) && (int)$online['online']===1) throw new \RuntimeException('Desconecta la cuenta del juego para reclamar el ítem. El baúl permanece protegido mientras estás conectado.');

            $reserved=$this->db->query_affected('INSERT INTO ['.self::REWARD_DELIVERIES_TABLE.'] ([reference_key],[source],[user_id],[account_username],[item_id],[item_name],[status]) VALUES (?,?,?,?,?,?,?)',array($referenceKey,$source,$userId,$username,$itemId,(string)$item['name'],'reserved'));
            if($reserved!==1) throw new \RuntimeException('No se pudo reservar la recompensa.');

            $warehouse=$this->lockWarehouse($username);
            $placement=$this->findWarehousePlacement($warehouse['hex'],(int)$item['width'],(int)$item['height']);
            $serial=$this->nextItemSerial();
            $itemHex=$this->encodeItem($item,$serial);
            $warehouseHex=substr_replace($warehouse['hex'],$itemHex,$placement*self::ITEM_BYTES*2,self::ITEM_BYTES*2);
            $saved=$this->db->query_affected('UPDATE [warehouse] SET [Items]=CONVERT(varbinary(max),?,2),[DbVersion]=3 WHERE [AccountID]=?',array($warehouseHex,$username));
            if($saved!==1) throw new \RuntimeException('No se pudo guardar el premio en el baúl. La entrega fue revertida.');

            $snapshot=json_encode($this->itemSnapshot($item),JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES);
            if($snapshot===false) throw new \RuntimeException('No se pudo registrar el snapshot de la recompensa.');
            $logged=$this->db->query_affected('INSERT INTO ['.self::ORDERS_TABLE.'] ([user_id],[account_username],[item_id],[item_name],[item_snapshot],[price_paid],[currency],[warehouse_slot],[item_serial],[status],[ip_address]) VALUES (?,?,?,?,?,?,?,?,?,?,?)',array($userId,$username,$itemId,(string)$item['name'],$snapshot,0,'REWARD',$placement,$serial,'reward',class_exists('NexoraSecurity')?\NexoraSecurity::clientIp():($_SERVER['REMOTE_ADDR']??'')));
            if($logged!==1) throw new \RuntimeException('No se pudo registrar la entrega de recompensa.');
            $completed=$this->db->query_affected("UPDATE [".self::REWARD_DELIVERIES_TABLE."] SET [status]='delivered',[warehouse_slot]=?,[item_serial]=?,[delivered_at]=SYSUTCDATETIME() WHERE [reference_key]=? AND [status]='reserved'",array($placement,$serial,$referenceKey));
            if($completed!==1) throw new \RuntimeException('No se pudo confirmar la referencia de recompensa.');
            if(!$this->db->commit()) throw new \RuntimeException('No se pudo confirmar la entrega de recompensa.');
            try { if(class_exists('NexoraAudit')) \NexoraAudit::log('itemshop.reward_delivery','account',$username,null,array('source'=>$source,'reference'=>$referenceKey,'item_id'=>$itemId,'item'=>$item['name'],'slot'=>$placement,'serial'=>$serial),$this->realm); } catch(\Throwable $ignored) {}
            return array('duplicate'=>false,'slot'=>$placement,'serial'=>$serial,'item_id'=>$itemId,'item_name'=>(string)$item['name']);
        } catch(\Throwable $e) {
            if($this->db->inTransaction()) $this->db->rollBack();
            throw $e;
        }
    }

    public function getBalance($username) {
        if(!\Validator::UsernameLength($username) || !\Validator::AlphaNumeric($username)) return null;
        $column = $this->currencyColumn();
        $row = $this->db->query_fetch_single('SELECT ['.$column.'] AS balance FROM ['.$this->currencyTable().'] WHERE [AccountID]=?', array($username));
        return is_array($row) ? (int)$row['balance'] : null;
    }

    public function analytics($days=30, $lowStockThreshold=5) {
        if(!$this->schemaReady()) return array();
        $allowedDays = array(7,30,90,365);
        $days = in_array((int)$days, $allowedDays, true) ? (int)$days : 30;
        $lowStockThreshold = max(1, min(100, (int)$lowStockThreshold));
        $currency = $this->currencyColumn();
        $fromUtc = gmdate('Y-m-d H:i:s', time() - ($days * 86400));

        $baseWhere = '[status]=? AND [currency]=? AND [created_at]>=?';
        $baseParams = array('delivered', $currency, $fromUtc);

        $summary = $this->db->query_fetch_single(
            'SELECT COUNT_BIG(*) AS [deliveries], COUNT(DISTINCT [account_username]) AS [buyers], COUNT(DISTINCT [item_id]) AS [products], COALESCE(SUM(CAST([price_paid] AS bigint)),0) AS [revenue], COALESCE(AVG(CAST([price_paid] AS decimal(19,2))),0) AS [avg_ticket], MIN([created_at]) AS [first_sale], MAX([created_at]) AS [last_sale] FROM ['.self::ORDERS_TABLE.'] WHERE '.$baseWhere,
            $baseParams
        );
        if(!is_array($summary)) $summary = array();

        $trend = $this->db->query_fetch(
            'SELECT CONVERT(varchar(10),[created_at],23) AS [day], COUNT_BIG(*) AS [deliveries], COUNT(DISTINCT [account_username]) AS [buyers], COALESCE(SUM(CAST([price_paid] AS bigint)),0) AS [revenue] FROM ['.self::ORDERS_TABLE.'] WHERE '.$baseWhere.' GROUP BY CONVERT(varchar(10),[created_at],23) ORDER BY [day] ASC',
            $baseParams
        );
        if(!is_array($trend)) $trend = array();

        $topItems = $this->db->query_fetch(
            'SELECT TOP 12 [item_id], MAX([item_name]) AS [item_name], COUNT_BIG(*) AS [deliveries], COUNT(DISTINCT [account_username]) AS [buyers], COALESCE(SUM(CAST([price_paid] AS bigint)),0) AS [revenue], COALESCE(AVG(CAST([price_paid] AS decimal(19,2))),0) AS [avg_price], MAX([created_at]) AS [last_sale] FROM ['.self::ORDERS_TABLE.'] WHERE '.$baseWhere.' GROUP BY [item_id] ORDER BY SUM(CAST([price_paid] AS bigint)) DESC, COUNT_BIG(*) DESC, [item_id] DESC',
            $baseParams
        );
        if(!is_array($topItems)) $topItems = array();

        $snapshotRows = $this->db->query_fetch(
            'SELECT [account_username],[price_paid],[item_snapshot] FROM ['.self::ORDERS_TABLE.'] WHERE '.$baseWhere,
            $baseParams
        );
        $promotions = array('deliveries'=>0,'revenue'=>0,'savings'=>0,'share_percent'=>0.0);
        $categoryMap = array();
        if(is_array($snapshotRows)) foreach($snapshotRows as $snapshotRow) {
            $snapshot = json_decode((string)($snapshotRow['item_snapshot'] ?? ''), true);
            if(!is_array($snapshot)) continue;
            $paid = (int)($snapshotRow['price_paid'] ?? 0);
            $normalPrice = (int)($snapshot['price_wc'] ?? 0);
            if($normalPrice > 0 && $paid > 0 && $paid < $normalPrice) {
                $promotions['deliveries']++;
                $promotions['revenue'] += $paid;
                $promotions['savings'] += ($normalPrice - $paid);
            }
            $category = trim((string)($snapshot['category'] ?? ''));
            if($category === '') $category = 'Sin categoría histórica';
            if(!isset($categoryMap[$category])) $categoryMap[$category] = array('category'=>$category,'deliveries'=>0,'buyers'=>array(),'revenue'=>0);
            $categoryMap[$category]['deliveries']++;
            $categoryMap[$category]['revenue'] += $paid;
            $buyer = trim((string)($snapshotRow['account_username'] ?? ''));
            if($buyer !== '') $categoryMap[$category]['buyers'][$buyer] = true;
        }
        $summaryDeliveries = (int)($summary['deliveries'] ?? 0);
        $promotions['share_percent'] = $summaryDeliveries > 0 ? round(($promotions['deliveries'] / $summaryDeliveries) * 100, 1) : 0.0;
        $categories = array();
        foreach($categoryMap as $categoryRow) {
            $categoryRow['buyers'] = count($categoryRow['buyers']);
            $categories[] = $categoryRow;
        }
        usort($categories, function($a,$b) {
            $revenueCompare = (int)$b['revenue'] <=> (int)$a['revenue'];
            if($revenueCompare !== 0) return $revenueCompare;
            $deliveryCompare = (int)$b['deliveries'] <=> (int)$a['deliveries'];
            if($deliveryCompare !== 0) return $deliveryCompare;
            return strcasecmp((string)$a['category'], (string)$b['category']);
        });
        if(count($categories) > 12) $categories = array_slice($categories, 0, 12);

        $topBuyers = $this->db->query_fetch(
            'SELECT TOP 10 [account_username], COUNT_BIG(*) AS [deliveries], COUNT(DISTINCT [item_id]) AS [products], COALESCE(SUM(CAST([price_paid] AS bigint)),0) AS [revenue], MAX([created_at]) AS [last_purchase] FROM ['.self::ORDERS_TABLE.'] WHERE '.$baseWhere.' GROUP BY [account_username] ORDER BY SUM(CAST([price_paid] AS bigint)) DESC, COUNT_BIG(*) DESC, [account_username] ASC',
            $baseParams
        );
        if(!is_array($topBuyers)) $topBuyers = array();

        $catalog = $this->db->query_fetch_single(
            'SELECT COUNT(*) AS [total_items], COALESCE(SUM(CASE WHEN [active]=1 THEN 1 ELSE 0 END),0) AS [active_items], COALESCE(SUM(CASE WHEN [active]=1 AND [stock]=0 THEN 1 ELSE 0 END),0) AS [sold_out], COALESCE(SUM(CASE WHEN [active]=1 AND [stock] BETWEEN 1 AND ? THEN 1 ELSE 0 END),0) AS [low_stock] FROM ['.self::ITEMS_TABLE.']',
            array($lowStockThreshold)
        );
        if(!is_array($catalog)) $catalog = array();

        $coverage = $this->db->query_fetch_single(
            'SELECT COUNT(*) AS [active_items], COALESCE(SUM(CASE WHEN s.[item_id] IS NULL THEN 0 ELSE 1 END),0) AS [sold_active] FROM ['.self::ITEMS_TABLE.'] i LEFT JOIN (SELECT DISTINCT [item_id] FROM ['.self::ORDERS_TABLE.'] WHERE '.$baseWhere.') s ON s.[item_id]=i.[id] WHERE i.[active]=1',
            $baseParams
        );
        if(!is_array($coverage)) $coverage = array();
        $catalog['sold_active'] = (int)($coverage['sold_active'] ?? 0);
        $activeItems = max(0, (int)($coverage['active_items'] ?? ($catalog['active_items'] ?? 0)));
        $catalog['rotation_percent'] = $activeItems > 0 ? round(($catalog['sold_active'] / $activeItems) * 100, 1) : 0.0;

        $unsoldParams = array('delivered', $currency, $fromUtc);
        $unsold = $this->db->query_fetch(
            'SELECT TOP 20 i.[id],i.[name],i.[category],i.[item_section],i.[item_index],i.[price_wc],i.[stock],i.[featured],i.[updated_at] FROM ['.self::ITEMS_TABLE.'] i WHERE i.[active]=1 AND NOT EXISTS (SELECT 1 FROM ['.self::ORDERS_TABLE.'] o WHERE o.[item_id]=i.[id] AND o.[status]=? AND o.[currency]=? AND o.[created_at]>=?) ORDER BY i.[featured] DESC,i.[updated_at] ASC,i.[id] ASC',
            $unsoldParams
        );
        if(!is_array($unsold)) $unsold = array();
        $unsoldCount = $this->db->query_fetch_single(
            'SELECT COUNT(*) AS [total] FROM ['.self::ITEMS_TABLE.'] i WHERE i.[active]=1 AND NOT EXISTS (SELECT 1 FROM ['.self::ORDERS_TABLE.'] o WHERE o.[item_id]=i.[id] AND o.[status]=? AND o.[currency]=? AND o.[created_at]>=?)',
            $unsoldParams
        );
        $catalog['unsold_active'] = is_array($unsoldCount) ? (int)($unsoldCount['total'] ?? 0) : 0;

        $lowStock = $this->db->query_fetch(
            'SELECT TOP 20 [id],[name],[category],[item_section],[item_index],[price_wc],[stock],[featured] FROM ['.self::ITEMS_TABLE.'] WHERE [active]=1 AND [stock] BETWEEN 0 AND ? ORDER BY [stock] ASC,[featured] DESC,[name] ASC,[id] ASC',
            array($lowStockThreshold)
        );
        if(!is_array($lowStock)) $lowStock = array();

        return array(
            'days'=>$days,
            'from_utc'=>$fromUtc,
            'currency'=>$currency,
            'currency_label'=>$this->currencyLabel(),
            'low_stock_threshold'=>$lowStockThreshold,
            'summary'=>$summary,
            'trend'=>$trend,
            'top_items'=>$topItems,
            'promotions'=>$promotions,
            'top_buyers'=>$topBuyers,
            'categories'=>$categories,
            'catalog'=>$catalog,
            'unsold'=>$unsold,
            'low_stock'=>$lowStock,
        );
    }

    private function normalizeAdminOrderIds($ids, $limit=500) {
        if(is_string($ids)) $ids = preg_split('/[\s,;]+/', trim($ids), -1, PREG_SPLIT_NO_EMPTY);
        if(!is_array($ids)) return array();
        $normalized = array();
        foreach($ids as $id) {
            if(filter_var($id, FILTER_VALIDATE_INT) === false) continue;
            $id = (int)$id;
            if($id < 1) continue;
            $normalized[$id] = $id;
            if(count($normalized) > (int)$limit) throw new \InvalidArgumentException('Demasiadas entregas seleccionadas para una sola acción.');
        }
        return array_values($normalized);
    }

    /**
     * Marca entregas ya confirmadas como datos de prueba sin destruir el historial.
     * delivered_test queda fuera de Analytics y del límite histórico por cuenta,
     * ya que ambos consumen exclusivamente status=delivered.
     * No revierte WCoinC, Warehouse, stock ni seriales.
     */
    public function setOrdersTestMode($ids, $isTest=true) {
        if(!$this->schemaReady()) throw new \RuntimeException('Las tablas de Arsenal no están instaladas en este reino.');
        $ids = $this->normalizeAdminOrderIds($ids, 500);
        if(count($ids) < 1) throw new \InvalidArgumentException('Selecciona al menos una entrega.');
        $isTest = (bool)$isTest;
        $expected = $isTest ? 'delivered' : 'delivered_test';
        $target = $isTest ? 'delivered_test' : 'delivered';
        $placeholders = implode(',', array_fill(0, count($ids), '?'));

        if(!$this->db->beginTransaction()) throw new \RuntimeException('No se pudo iniciar la operación de datos de prueba.');
        try {
            $rows = $this->db->query_fetch(
                'SELECT [id],[account_username],[item_id],[item_name],[price_paid],[currency],[status] FROM ['.self::ORDERS_TABLE.'] WITH (UPDLOCK,HOLDLOCK) WHERE [id] IN ('.$placeholders.') ORDER BY [id] ASC',
                $ids
            );
            if(!is_array($rows)) $rows = array();
            $eligible = array();
            $accounts = array();
            $totalValue = 0;
            foreach($rows as $row) {
                if((string)($row['status'] ?? '') !== $expected) continue;
                $id = (int)($row['id'] ?? 0);
                if($id < 1) continue;
                $eligible[] = $id;
                $account = (string)($row['account_username'] ?? '');
                if($account !== '') $accounts[$account] = true;
                $totalValue += (int)($row['price_paid'] ?? 0);
            }
            if(count($eligible) < 1) {
                $this->db->rollBack();
                return array('affected'=>0,'skipped'=>count($ids),'status'=>$target);
            }
            $eligiblePlaceholders = implode(',', array_fill(0, count($eligible), '?'));
            $params = array_merge(array($target), $eligible, array($expected));
            $affected = $this->db->query_affected(
                'UPDATE ['.self::ORDERS_TABLE.'] SET [status]=? WHERE [id] IN ('.$eligiblePlaceholders.') AND [status]=?',
                $params
            );
            if((int)$affected !== count($eligible)) throw new \RuntimeException('El historial cambió durante la operación. No se aplicaron cambios parciales.');
            if(!$this->db->commit()) throw new \RuntimeException('No se pudo confirmar la operación de datos de prueba.');

            if(class_exists('NexoraAudit')) {
                try {
                    \NexoraAudit::log(
                        $isTest ? 'itemshop.testdata_mark' : 'itemshop.testdata_restore',
                        'itemshop_orders',
                        $this->realm,
                        null,
                        array(
                            'affected'=>(int)$affected,
                            'selected'=>count($ids),
                            'accounts'=>array_slice(array_keys($accounts),0,20),
                            'price_paid_total'=>$totalValue,
                            'from_status'=>$expected,
                            'to_status'=>$target,
                        ),
                        $this->realm
                    );
                } catch(\Throwable $ignored) {}
            }
            return array('affected'=>(int)$affected,'skipped'=>max(0,count($ids)-(int)$affected),'status'=>$target);
        } catch(\Throwable $e) {
            if($this->db->inTransaction()) $this->db->rollBack();
            throw $e;
        }
    }

    public function getOrders($limit=300, $filters=array()) {
        if(!$this->schemaReady()) return array();
        $limit=max(1,min(1000,(int)$limit));
        $filters=is_array($filters)?$filters:array();
        $where=array();
        $params=array();
        $account=trim((string)($filters['account'] ?? ''));
        if($account !== '') {
            if(!\Validator::UsernameLength($account) || !\Validator::AlphaNumeric($account)) throw new \InvalidArgumentException('Cuenta de filtro inválida.');
            $where[]='[account_username]=?';
            $params[]=$account;
        }
        $status=(string)($filters['status'] ?? 'all');
        if($status === 'real') {
            $where[]='[status]=?';
            $params[]='delivered';
        } elseif($status === 'test') {
            $where[]='[status]=?';
            $params[]='delivered_test';
        }
        $sql='SELECT TOP '.$limit.' * FROM ['.self::ORDERS_TABLE.']';
        if(count($where)>0) $sql.=' WHERE '.implode(' AND ',$where);
        $sql.=' ORDER BY [id] DESC';
        $rows=$this->db->query_fetch($sql,$params);
        return is_array($rows)?$rows:array();
    }

    public function purchaseCenterReady() {
        $core = defined('__PATH_MODULES_USERCP__') && is_file(__PATH_MODULES_USERCP__.'purchases.php');
        $template = defined('__PATH_TEMPLATE_ROOT__') && is_file(__PATH_TEMPLATE_ROOT__.'inc/modules/usercp/purchases.php');
        $support = defined('__PATH_ADMINCP_MODULES__') ? is_file(__PATH_ADMINCP_MODULES__.'itemshop/support.php') : true;
        return $core && $template && $support && $this->hasUsercpPurchaseLink();
    }

    public function hasUsercpPurchaseLink() {
        $cfg = loadConfig('usercp');
        if(!is_array($cfg)) return false;
        foreach($cfg as $row) {
            if(!is_array($row) || !isset($row['link'])) continue;
            if(trim((string)$row['link'],'/') === 'usercp/purchases') return true;
        }
        return false;
    }

    /**
     * Registra el acceso a Mis compras sin reemplazar ni reordenar manualmente
     * las entradas existentes del UserCP. Es idempotente y usa escritura atomica.
     */
    public function addUsercpPurchaseLink() {
        $cfg = loadConfig('usercp');
        if(!is_array($cfg)) $cfg = array();
        foreach($cfg as $row) {
            if(is_array($row) && isset($row['link']) && trim((string)$row['link'],'/') === 'usercp/purchases') return false;
        }
        $cfg[] = array(
            'active'=>true,
            'type'=>'internal',
            'phrase'=>'itemshop_purchases_title',
            'link'=>'usercp/purchases',
            'icon'=>'usercp_default.png',
            'visibility'=>'user',
            'newtab'=>false,
            'order'=>115,
        );
        usort($cfg,function($a,$b){ return ((int)($a['order'] ?? 0)) <=> ((int)($b['order'] ?? 0)); });
        $json = json_encode($cfg, JSON_PRETTY_PRINT|JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES);
        if($json === false) throw new \RuntimeException('No se pudo serializar el menu UserCP.');
        $path = __PATH_CONFIGS__.'usercp.json';
        $tmp = $path.'.tmp.'.bin2hex(random_bytes(6));
        if(file_put_contents($tmp, $json."\n", LOCK_EX) === false) throw new \RuntimeException('No se pudo preparar la actualizacion del menu UserCP.');
        @chmod($tmp, 0644);
        if(!@rename($tmp, $path)) { @unlink($tmp); throw new \RuntimeException('No se pudo actualizar el menu UserCP. Revisa permisos de escritura de includes/config.'); }
        return true;
    }

    public function decodeOrderSnapshot($snapshot) {
        if(is_array($snapshot)) return $snapshot;
        $decoded = json_decode((string)$snapshot, true);
        return is_array($decoded) ? $decoded : array();
    }

    /**
     * Historial publico de la cuenta autenticada. Solo expone compras reales
     * confirmadas; delivered_test permanece reservado al flujo administrativo QA.
     */
    public function getAccountPurchaseHistory($username, $limit=100) {
        if(!$this->schemaReady()) return array();
        $username = trim((string)$username);
        if(!\Validator::UsernameLength($username) || !\Validator::AlphaNumeric($username)) throw new \InvalidArgumentException('Sesion de cuenta invalida.');
        $limit = max(1, min(200, (int)$limit));
        $rows = $this->db->query_fetch(
            'SELECT TOP '.$limit.' [id],[account_username],[item_id],[item_name],[item_snapshot],[price_paid],[currency],[status],[created_at] FROM ['.self::ORDERS_TABLE.'] WHERE [account_username]=? AND [status]=? ORDER BY [id] DESC',
            array($username,'delivered')
        );
        return is_array($rows) ? $rows : array();
    }

    public function getSupportOrder($id) {
        if(!$this->schemaReady() || filter_var($id,FILTER_VALIDATE_INT) === false || (int)$id < 1) return null;
        $row = $this->db->query_fetch_single('SELECT * FROM ['.self::ORDERS_TABLE.'] WHERE [id]=?', array((int)$id));
        return is_array($row) ? $row : null;
    }

    /**
     * Busqueda administrativa de entregas. Todos los filtros son server-side y
     * parametrizados; no permite mutar ordenes, moneda, stock ni Warehouse.
     */
    public function searchSupportOrders($filters=array(), $limit=100) {
        if(!$this->schemaReady()) return array();
        $filters = is_array($filters) ? $filters : array();
        $limit = max(1, min(500, (int)$limit));
        $where = array();
        $params = array();

        $orderId = trim((string)($filters['order_id'] ?? ''));
        if($orderId !== '') {
            if(filter_var($orderId,FILTER_VALIDATE_INT) === false || (int)$orderId < 1) throw new \InvalidArgumentException('Order ID invalido.');
            $where[]='[id]=?'; $params[]=(int)$orderId;
        }

        $account = trim((string)($filters['account'] ?? ''));
        if($account !== '') {
            if(!\Validator::UsernameLength($account) || !\Validator::AlphaNumeric($account)) throw new \InvalidArgumentException('Cuenta de busqueda invalida.');
            $where[]='[account_username]=?'; $params[]=$account;
        }

        $serial = trim((string)($filters['serial'] ?? ''));
        if($serial !== '') {
            if(!preg_match('/^[0-9]{1,19}$/',$serial)) throw new \InvalidArgumentException('Serial invalido.');
            $where[]='[item_serial]=?'; $params[]=$serial;
        }

        $item = trim((string)($filters['item'] ?? ''));
        if($item !== '') {
            if(strlen($item) > 100) throw new \InvalidArgumentException('El filtro de producto es demasiado largo.');
            $item = str_replace(array('[','%','_'), array('[[]','[%]','[_]'), $item);
            $where[]='[item_name] LIKE ?'; $params[]='%'.$item.'%';
        }

        $status = (string)($filters['status'] ?? 'all');
        if($status === 'real') { $where[]='[status]=?'; $params[]='delivered'; }
        elseif($status === 'test') { $where[]='[status]=?'; $params[]='delivered_test'; }
        elseif($status !== 'all') throw new \InvalidArgumentException('Estado de soporte invalido.');

        $sql='SELECT TOP '.$limit.' [id],[account_username],[item_id],[item_name],[price_paid],[currency],[warehouse_slot],[item_serial],[status],[created_at] FROM ['.self::ORDERS_TABLE.']';
        if(count($where)>0) $sql.=' WHERE '.implode(' AND ',$where);
        $sql.=' ORDER BY [id] DESC';
        $rows=$this->db->query_fetch($sql,$params);
        return is_array($rows) ? $rows : array();
    }

    public function addNavbarLink() {
        $cfg = loadConfig('navbar');
        if(!is_array($cfg)) $cfg=array();
        foreach($cfg as $row) if(isset($row['link']) && trim((string)$row['link'],'/') === 'shop') return false;
        $cfg[] = array('active'=>true,'type'=>'internal','phrase'=>'itemshop_title','link'=>'shop','visibility'=>'always','newtab'=>false,'order'=>35,'featured'=>true);
        usort($cfg,function($a,$b){return ((int)$a['order'])<=>((int)$b['order']);});
        $json=json_encode($cfg,JSON_PRETTY_PRINT|JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES);
        if($json===false || file_put_contents(__PATH_CONFIGS__.'navbar.json',$json,LOCK_EX)===false) throw new \RuntimeException('No se pudo agregar la tienda a la navegación.');
        return true;
    }

    public function localDateValue($utc) {
        if(empty($utc)) return '';
        try {
            $date = new \DateTimeImmutable((string)$utc, new \DateTimeZone('UTC'));
            return $date->setTimezone(new \DateTimeZone(date_default_timezone_get()))->format('Y-m-d\TH:i');
        } catch(\Throwable $e) { return ''; }
    }

    private function localDateToUtc($input) {
        $input=trim((string)$input);
        if($input==='') return null;
        try {
            $date=new \DateTimeImmutable($input,new \DateTimeZone(date_default_timezone_get()));
            return $date->setTimezone(new \DateTimeZone('UTC'))->format('Y-m-d H:i:s');
        } catch(\Throwable $e) { throw new \InvalidArgumentException('Fecha de promoción inválida.'); }
    }

    private function currencyColumn() {
        $map=array('WCOINC'=>'WCoinC','WCOINP'=>'WCoinP','GOBLINPOINT'=>'GoblinPoint');
        return $map[$this->settings['currency']];
    }

    private function currencyTable() { return 'CashShopData'; }

    public function currencyLabel() {
        $map=array('WCOINC'=>'WCoinC','WCOINP'=>'WCoinP','GOBLINPOINT'=>'Goblin Points');
        return $map[$this->settings['currency']];
    }

    private function debitCurrency($username, $price) {
        $column=$this->currencyColumn();
        $row=$this->db->query_fetch_single('SELECT ['.$column.'] AS balance FROM ['.$this->currencyTable().'] WITH (UPDLOCK,HOLDLOCK) WHERE [AccountID]=?',array($username));
        if(!is_array($row)) throw new \RuntimeException('La cuenta no tiene una billetera de '.$this->currencyLabel().'.');
        if((int)$row['balance'] < $price) throw new \RuntimeException('No tienes suficiente '.$this->currencyLabel().' para esta compra.');
        $affected=$this->db->query_affected('UPDATE ['.$this->currencyTable().'] SET ['.$column.']=['.$column.']-? WHERE [AccountID]=? AND ['.$column.']>=?',array($price,$username,$price));
        if($affected!==1) throw new \RuntimeException('No se pudo reservar el saldo. Inténtalo nuevamente.');
        return (int)$row['balance']-$price;
    }

    private function lockWarehouse($username) {
        $row=$this->db->query_fetch_single('SELECT [DbVersion],DATALENGTH([Items]) AS item_bytes,CONVERT(varchar(max),[Items],2) AS item_hex FROM [warehouse] WITH (UPDLOCK,HOLDLOCK) WHERE [AccountID]=?',array($username));
        if(!is_array($row)) {
            $hex=str_repeat('FF',self::WAREHOUSE_STORAGE_BYTES);
            $inserted=$this->db->query_affected('INSERT INTO [warehouse] ([AccountID],[Items],[Money],[EndUseDate],[DbVersion],[pw]) VALUES (?,CONVERT(varbinary(max),?,2),0,NULL,3,0)',array($username,$hex));
            if($inserted!==1) throw new \RuntimeException('No se pudo crear el baúl de la cuenta.');
            return array('hex'=>$hex,'bytes'=>self::WAREHOUSE_STORAGE_BYTES);
        }
        $dbVersion=(int)$row['DbVersion'];
        if($dbVersion!==3 && !empty($row['item_hex'])) throw new \RuntimeException('El formato del baúl no es DbVersion 3; la entrega se bloqueó para proteger los ítems existentes.');
        $hex=strtoupper(preg_replace('/[^0-9A-F]/i','',(string)$row['item_hex']));
        if($hex==='') $hex=str_repeat('FF',self::WAREHOUSE_SLOTS*self::ITEM_BYTES);
        $minimum=self::WAREHOUSE_SLOTS*self::ITEM_BYTES*2;
        if(strlen($hex)<$minimum || (strlen($hex) % (self::ITEM_BYTES*2)) !== 0) throw new \RuntimeException('El tamaño del baúl no coincide con el formato X-Team configurado.');
        return array('hex'=>$hex,'bytes'=>(int)(strlen($hex)/2));
    }

    private function findWarehousePlacement($hex,$width,$height) {
        $occupied=array_fill(0,self::WAREHOUSE_SLOTS,false);
        $recordLength=self::ITEM_BYTES*2;
        for($slot=0;$slot<self::WAREHOUSE_SLOTS;$slot++) {
            $chunk=substr($hex,$slot*$recordLength,$recordLength);
            if($chunk==='' || preg_match('/^[F]+$/D',$chunk)) continue;
            if(strlen($chunk)!==$recordLength) throw new \RuntimeException('Se detectó un registro incompleto en el baúl.');
            $indexLow=hexdec(substr($chunk,0,2));
            $excellentIndexByte=hexdec(substr($chunk,14,2));
            $index=$indexLow | (($excellentIndexByte & 0x80) << 1);
            $section=(hexdec(substr($chunk,18,2))>>4)&0x0F;
            $dimension=$this->resolveDimensions($section,$index);
            if(!$dimension) throw new \RuntimeException('No se puede validar el espacio porque el baúl contiene el ítem '.$section.':'.$index.' sin dimensiones registradas. Agrégalo al catálogo una vez con su ancho y alto.');
            $x=$slot%self::WAREHOUSE_COLUMNS; $y=(int)floor($slot/self::WAREHOUSE_COLUMNS);
            if($x+$dimension[0]>self::WAREHOUSE_COLUMNS || $y+$dimension[1]>self::WAREHOUSE_ROWS) throw new \RuntimeException('El baúl contiene un ítem fuera de la cuadrícula esperada.');
            for($dy=0;$dy<$dimension[1];$dy++) for($dx=0;$dx<$dimension[0];$dx++) $occupied[(($y+$dy)*self::WAREHOUSE_COLUMNS)+$x+$dx]=true;
        }
        for($y=0;$y<=self::WAREHOUSE_ROWS-$height;$y++) {
            for($x=0;$x<=self::WAREHOUSE_COLUMNS-$width;$x++) {
                $free=true;
                for($dy=0;$dy<$height && $free;$dy++) for($dx=0;$dx<$width;$dx++) if($occupied[(($y+$dy)*self::WAREHOUSE_COLUMNS)+$x+$dx]) { $free=false; break; }
                if($free) return ($y*self::WAREHOUSE_COLUMNS)+$x;
            }
        }
        throw new \RuntimeException('No hay espacio suficiente en el baúl para este ítem.');
    }

    private function resolveDimensions($section,$index) {
        $authoritative = self::authoritativeItem((int)$section, (int)$index);
        if(is_array($authoritative) && !empty($authoritative['encoder_compatible'])) return array((int)$authoritative['width'], (int)$authoritative['height']);
        $row=$this->db->query_fetch_single('SELECT TOP 1 [width],[height] FROM ['.self::ITEMS_TABLE.'] WHERE [item_section]=? AND [item_index]=? ORDER BY [updated_at] DESC,[id] DESC',array((int)$section,(int)$index));
        if(is_array($row) && (int)$row['width']>0 && (int)$row['height']>0) return array((int)$row['width'],(int)$row['height']);
        $asset = null;
        $base = __PATH_TEMPLATE_ROOT__.'img/item_images/'.(int)$section.'/'.(int)$index;
        foreach(array('gif','webp','png','jpg','jpeg') as $extension) {
            if(is_file($base.'.'.$extension)) { $asset = $base.'.'.$extension; break; }
        }
        if($asset === null) return null;
        $size=@getimagesize($asset);
        if(!is_array($size) || empty($size[0]) || empty($size[1])) return null;
        if(((int)$size[0] % 32) !== 0 || ((int)$size[1] % 32) !== 0) return null;
        $w=(int)($size[0]/32); $h=(int)($size[1]/32);
        if($w < 1 || $h < 1 || $w>self::WAREHOUSE_COLUMNS || $h>self::WAREHOUSE_ROWS) return null;
        return array($w,$h);
    }

    private function nextItemSerial() {
        $number=(int)$this->settings['serial_server_number'];
        $row=$this->db->query_fetch_single('SELECT [ItemCount] FROM [GameServerInfo] WITH (UPDLOCK,HOLDLOCK) WHERE [Number]=?',array($number));
        if(!is_array($row)) throw new \RuntimeException('No existe GameServerInfo.Number='.$number.'. Ajusta el generador de seriales en AdminCP.');
        $current=(int)$row['ItemCount'];
        if($current<0 || $current>=2147483646) throw new \RuntimeException('El contador de seriales alcanzó un valor no seguro.');
        $next=$current+1;
        $affected=$this->db->query_affected('UPDATE [GameServerInfo] SET [ItemCount]=? WHERE [Number]=? AND [ItemCount]=?',array($next,$number,$current));
        if($affected!==1) throw new \RuntimeException('No se pudo reservar un serial único para el ítem.');
        return $next;
    }

    private function encodeItem($item,$serial) {
        $bytes=array_fill(0,self::ITEM_BYTES,0xFF);
        $option=(int)$item['option_level'];
        $itemIndex=(int)$item['item_index'];
        $bytes[0]=$itemIndex&0xFF;
        $bytes[1]=(!empty($item['has_skill'])?0x80:0)|(((int)$item['item_level']&0x0F)<<3)|(!empty($item['has_luck'])?0x04:0)|($option&0x03);
        $bytes[2]=(int)$item['durability'];
        $bytes[3]=($serial>>24)&0xFF; $bytes[4]=($serial>>16)&0xFF; $bytes[5]=($serial>>8)&0xFF; $bytes[6]=$serial&0xFF;
        $bytes[7]=((int)$item['excellent_mask']&0x3F)|(($option&0x04)?0x40:0)|(($itemIndex&0x100)>>1);
        $bytes[8]=(int)$item['ancient_option']&0x0F;
        $bytes[9]=(((int)$item['item_section']&0x0F)<<4)|(!empty($item['option_380'])?0x08:0x00);
        $authoritative=self::authoritativeItem((int)$item['item_section'], $itemIndex);
        $socketCapable=is_array($authoritative) && !empty($authoritative['socket_capable']);
        $bytes[10]=$socketCapable ? (int)($item['socket_bonus']??0) : (int)($item['harmony_option']??0);
        $hasExplicitSockets=true;
        for($i=1;$i<=5;$i++) if(!array_key_exists('socket_option_'.$i,$item)) { $hasExplicitSockets=false; break; }
        if($hasExplicitSockets) {
            for($i=0;$i<5;$i++) $bytes[11+$i]=(int)$item['socket_option_'.($i+1)]&0xFF;
        } else {
            $sockets=(int)($item['socket_count']??0);
            for($i=0;$i<5;$i++) $bytes[11+$i]=$i<$sockets?0xFE:0xFF;
        }
        $hex=''; foreach($bytes as $byte) $hex.=strtoupper(str_pad(dechex($byte&0xFF),2,'0',STR_PAD_LEFT));
        return $hex;
    }

    private function itemSnapshot($item) {
        $keys=array('id','name','category','item_section','item_index','item_level','has_skill','has_luck','option_level','excellent_mask','ancient_option','harmony_option','socket_count','option_380','socket_bonus','socket_option_1','socket_option_2','socket_option_3','socket_option_4','socket_option_5','durability','width','height','price_wc','sale_price_wc');
        $snapshot=array(); foreach($keys as $key) $snapshot[$key]=$item[$key]??null;
        return $snapshot;
    }

    private function imageDirectory() { return __PATH_TEMPLATE_ROOT__.'img/itemshop/'; }

    private function storeImage($file,$publicKey) {
        if((int)$file['error']!==UPLOAD_ERR_OK) throw new \RuntimeException('La imagen no pudo cargarse.');
        if(empty($file['tmp_name']) || !is_uploaded_file($file['tmp_name'])) throw new \RuntimeException('La carga de imagen no es válida.');
        if((int)$file['size']<1 || (int)$file['size']>2*1024*1024) throw new \RuntimeException('La imagen debe pesar menos de 2 MB.');
        $info=@getimagesize($file['tmp_name']);
        $types=array(IMAGETYPE_JPEG=>'jpg',IMAGETYPE_PNG=>'png',IMAGETYPE_GIF=>'gif');
        if(defined('IMAGETYPE_WEBP')) $types[IMAGETYPE_WEBP]='webp';
        if(!is_array($info) || !isset($types[$info[2]])) throw new \RuntimeException('Formato de imagen no permitido. Usa JPG, PNG, GIF o WEBP.');
        if((int)$info[0] < 1 || (int)$info[1] < 1 || (int)$info[0] > 4096 || (int)$info[1] > 4096) throw new \RuntimeException('La imagen no puede superar 4096 × 4096 píxeles.');
        $dir=$this->imageDirectory();
        if(!is_dir($dir) || !is_writable($dir)) throw new \RuntimeException('La carpeta de imágenes de la tienda no tiene permiso de escritura.');
        $name='item-'.preg_replace('/[^a-f0-9]/','',strtolower($publicKey)).'.'.$types[$info[2]];
        if(!move_uploaded_file($file['tmp_name'],$dir.$name)) throw new \RuntimeException('No se pudo guardar la imagen del ítem.');
        @chmod($dir.$name,0644);
        return $name;
    }

    private function columnExists($table,$column) {
        $row=$this->db->query_fetch_single("SELECT c.name FROM sys.columns c INNER JOIN sys.objects o ON c.object_id=o.object_id WHERE o.name=? AND c.name=?",array($table,$column));
        return is_array($row);
    }
}
