-- Ruleta Nexora V1.5.0 - Fragment Exchange
-- Alternativa manual. El plugin ejecuta la misma migración automáticamente mediante ensureSchema().

IF OBJECT_ID(N'[dbo].[NEXORA_ROULETTE_EXCHANGE_REWARDS]', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[NEXORA_ROULETTE_EXCHANGE_REWARDS](
        [id] int IDENTITY(1,1) NOT NULL PRIMARY KEY,
        [realm] varchar(20) NOT NULL,[name] nvarchar(120) NOT NULL,[description] nvarchar(400) NULL,
        [reward_type] varchar(30) NOT NULL,[fragment_cost] int NOT NULL,[amount] int NOT NULL DEFAULT(0),
        [credit_config_id] int NULL,[item_id] int NULL,[global_limit] int NOT NULL DEFAULT(0),[account_limit] int NOT NULL DEFAULT(0),
        [active] bit NOT NULL DEFAULT(1),[sort_order] int NOT NULL DEFAULT(100),
        [created_at] datetime2 NOT NULL DEFAULT(SYSUTCDATETIME()),[updated_at] datetime2 NOT NULL DEFAULT(SYSUTCDATETIME())
    );
    CREATE INDEX [IX_NRE_realm_active] ON [dbo].[NEXORA_ROULETTE_EXCHANGE_REWARDS]([realm],[active],[sort_order],[id]);
END;

IF OBJECT_ID(N'[dbo].[NEXORA_ROULETTE_EXCHANGE_LOG]', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[NEXORA_ROULETTE_EXCHANGE_LOG](
        [id] bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,[reference_key] varchar(120) NOT NULL UNIQUE,
        [realm] varchar(20) NOT NULL,[user_id] int NOT NULL,[account_username] varchar(50) NOT NULL,
        [reward_id] int NOT NULL,[reward_name] nvarchar(120) NOT NULL,[reward_type] varchar(30) NOT NULL,
        [fragment_cost] int NOT NULL,[reward_amount] int NOT NULL DEFAULT(0),[reward_credit_config_id] int NULL,[reward_item_id] int NULL,
        [status] varchar(30) NOT NULL,[fragments_before] bigint NOT NULL,[fragments_after] bigint NOT NULL,
        [balance_before] bigint NULL,[balance_after] bigint NULL,[ip_address] varchar(45) NULL,[admin_note] nvarchar(500) NULL,
        [created_at] datetime2 NOT NULL DEFAULT(SYSUTCDATETIME()),[claimed_at] datetime2 NULL
    );
    CREATE INDEX [IX_NRL_account] ON [dbo].[NEXORA_ROULETTE_EXCHANGE_LOG]([realm],[account_username],[created_at] DESC);
    CREATE INDEX [IX_NRL_reward] ON [dbo].[NEXORA_ROULETTE_EXCHANGE_LOG]([realm],[reward_id],[created_at] DESC);
    CREATE INDEX [IX_NRL_status] ON [dbo].[NEXORA_ROULETTE_EXCHANGE_LOG]([realm],[status],[created_at] DESC);
END;
