/* Arsenal Nexora — ejecutar en cada base Me_MuOnline (Legacy y Seasonal).
   Alternativa recomendada: AdminCP > Arsenal Nexora > Configuración > Instalar / validar tablas. */

IF OBJECT_ID(N'[dbo].[NEXORA_ITEMSHOP_ITEMS]', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[NEXORA_ITEMSHOP_ITEMS](
        [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].[NEXORA_ITEMSHOP_ITEMS]([active],[featured],[sort_order],[id]);
    CREATE INDEX [IX_NIS_code] ON [dbo].[NEXORA_ITEMSHOP_ITEMS]([item_section],[item_index]);
END;

-- Arsenal V2 Fase 4: ampliar el índice DbVersion 3 confirmado a 0-511 en instalaciones existentes.
IF OBJECT_ID(N'[dbo].[NEXORA_ITEMSHOP_ITEMS]', 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].[NEXORA_ITEMSHOP_ITEMS]') AND [definition] NOT LIKE '%511%')
        ALTER TABLE [dbo].[NEXORA_ITEMSHOP_ITEMS] 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].[NEXORA_ITEMSHOP_ITEMS]'))
    BEGIN
        ALTER TABLE [dbo].[NEXORA_ITEMSHOP_ITEMS] WITH CHECK ADD CONSTRAINT [CK_NIS_index] CHECK ([item_index] BETWEEN 0 AND 511);
        ALTER TABLE [dbo].[NEXORA_ITEMSHOP_ITEMS] CHECK CONSTRAINT [CK_NIS_index];
    END
END;

-- Arsenal V2 Fase 5: persistir opciones inteligentes sin perder compatibilidad con socket_count legacy.
IF OBJECT_ID(N'[dbo].[NEXORA_ITEMSHOP_ITEMS]', N'U') IS NOT NULL
BEGIN
    IF COL_LENGTH(N'dbo.NEXORA_ITEMSHOP_ITEMS', N'option_380') IS NULL ALTER TABLE [dbo].[NEXORA_ITEMSHOP_ITEMS] ADD [option_380] bit NOT NULL CONSTRAINT [DF_NIS_380] DEFAULT(0);
    IF COL_LENGTH(N'dbo.NEXORA_ITEMSHOP_ITEMS', N'socket_bonus') IS NULL ALTER TABLE [dbo].[NEXORA_ITEMSHOP_ITEMS] ADD [socket_bonus] tinyint NOT NULL CONSTRAINT [DF_NIS_socket_bonus] DEFAULT(0);
    IF COL_LENGTH(N'dbo.NEXORA_ITEMSHOP_ITEMS', N'socket_option_1') IS NULL ALTER TABLE [dbo].[NEXORA_ITEMSHOP_ITEMS] ADD [socket_option_1] tinyint NOT NULL CONSTRAINT [DF_NIS_socket_1] DEFAULT(255);
    IF COL_LENGTH(N'dbo.NEXORA_ITEMSHOP_ITEMS', N'socket_option_2') IS NULL ALTER TABLE [dbo].[NEXORA_ITEMSHOP_ITEMS] ADD [socket_option_2] tinyint NOT NULL CONSTRAINT [DF_NIS_socket_2] DEFAULT(255);
    IF COL_LENGTH(N'dbo.NEXORA_ITEMSHOP_ITEMS', N'socket_option_3') IS NULL ALTER TABLE [dbo].[NEXORA_ITEMSHOP_ITEMS] ADD [socket_option_3] tinyint NOT NULL CONSTRAINT [DF_NIS_socket_3] DEFAULT(255);
    IF COL_LENGTH(N'dbo.NEXORA_ITEMSHOP_ITEMS', N'socket_option_4') IS NULL ALTER TABLE [dbo].[NEXORA_ITEMSHOP_ITEMS] ADD [socket_option_4] tinyint NOT NULL CONSTRAINT [DF_NIS_socket_4] DEFAULT(255);
    IF COL_LENGTH(N'dbo.NEXORA_ITEMSHOP_ITEMS', N'socket_option_5') IS NULL ALTER TABLE [dbo].[NEXORA_ITEMSHOP_ITEMS] ADD [socket_option_5] tinyint NOT NULL CONSTRAINT [DF_NIS_socket_5] DEFAULT(255);
END;

IF OBJECT_ID(N'[dbo].[NEXORA_ITEMSHOP_ITEMS]', N'U') IS NOT NULL
BEGIN
    EXEC sp_executesql N'UPDATE [dbo].[NEXORA_ITEMSHOP_ITEMS] 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].[NEXORA_ITEMSHOP_ORDERS]', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[NEXORA_ITEMSHOP_ORDERS](
        [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].[NEXORA_ITEMSHOP_ORDERS]([account_username],[item_id],[status]);
    CREATE INDEX [IX_NISO_created] ON [dbo].[NEXORA_ITEMSHOP_ORDERS]([created_at] DESC);
END;

-- Arsenal Nexora 1.6.8: presets comerciales y snapshots de rollback administrativo.
IF OBJECT_ID(N'[dbo].[NEXORA_ITEMSHOP_PRESETS]', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[NEXORA_ITEMSHOP_PRESETS](
        [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].[NEXORA_ITEMSHOP_PRESETS]([name]);
END;

IF OBJECT_ID(N'[dbo].[NEXORA_ITEMSHOP_BULK_HISTORY]', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[NEXORA_ITEMSHOP_BULK_HISTORY](
        [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].[NEXORA_ITEMSHOP_BULK_HISTORY]([created_at] DESC);
END;

-- Arsenal Nexora 1.7.0: colecciones reutilizables y Campaign Manager programado por reino.
IF OBJECT_ID(N'[dbo].[NEXORA_ITEMSHOP_COLLECTIONS]', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[NEXORA_ITEMSHOP_COLLECTIONS](
        [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].[NEXORA_ITEMSHOP_COLLECTIONS]([name]);
END;

IF OBJECT_ID(N'[dbo].[NEXORA_ITEMSHOP_COLLECTION_ITEMS]', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[NEXORA_ITEMSHOP_COLLECTION_ITEMS](
        [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].[NEXORA_ITEMSHOP_COLLECTIONS]([id]) ON DELETE CASCADE,
        CONSTRAINT [FK_NISCI_item] FOREIGN KEY ([item_id]) REFERENCES [dbo].[NEXORA_ITEMSHOP_ITEMS]([id]) ON DELETE CASCADE
    );
    CREATE INDEX [IX_NISCI_item] ON [dbo].[NEXORA_ITEMSHOP_COLLECTION_ITEMS]([item_id],[collection_id]);
END;

IF OBJECT_ID(N'[dbo].[NEXORA_ITEMSHOP_COLLECTION_ITEMS]', 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].[NEXORA_ITEMSHOP_COLLECTION_ITEMS]'))
        ALTER TABLE [dbo].[NEXORA_ITEMSHOP_COLLECTION_ITEMS] WITH CHECK ADD CONSTRAINT [FK_NISCI_collection] FOREIGN KEY ([collection_id]) REFERENCES [dbo].[NEXORA_ITEMSHOP_COLLECTIONS]([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].[NEXORA_ITEMSHOP_COLLECTION_ITEMS]'))
        ALTER TABLE [dbo].[NEXORA_ITEMSHOP_COLLECTION_ITEMS] WITH CHECK ADD CONSTRAINT [FK_NISCI_item] FOREIGN KEY ([item_id]) REFERENCES [dbo].[NEXORA_ITEMSHOP_ITEMS]([id]) ON DELETE CASCADE;
END;

IF OBJECT_ID(N'[dbo].[NEXORA_ITEMSHOP_CAMPAIGNS]', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[NEXORA_ITEMSHOP_CAMPAIGNS](
        [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].[NEXORA_ITEMSHOP_CAMPAIGNS]([status],[start_at],[end_at]);
    CREATE INDEX [IX_NISCAM_collection] ON [dbo].[NEXORA_ITEMSHOP_CAMPAIGNS]([collection_id],[status]);
END;

-- Arsenal Nexora: canal idempotente de recompensas externas (Ruleta, eventos, battle pass).
IF OBJECT_ID(N'[dbo].[NEXORA_ITEMSHOP_REWARD_DELIVERIES]', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[NEXORA_ITEMSHOP_REWARD_DELIVERIES](
        [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].[NEXORA_ITEMSHOP_REWARD_DELIVERIES]([account_username],[created_at] DESC);
END;
