-- Ruleta Nexora 1.0.0 - instalación manual alternativa.
-- Normalmente el plugin crea estas tablas automáticamente desde AdminCP.
IF OBJECT_ID(N'[dbo].[NEXORA_ROULETTE_PRIZES]', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[NEXORA_ROULETTE_PRIZES](
        [id] int IDENTITY(1,1) NOT NULL PRIMARY KEY,[realm] varchar(20) NOT NULL,[name] nvarchar(120) NOT NULL,
        [description] nvarchar(400) NULL,[prize_type] varchar(30) NOT NULL,[rarity] varchar(20) NOT NULL,[weight] int NOT NULL,
        [amount] int NOT NULL DEFAULT(0),[credit_config_id] int NULL,[item_id] int NULL,[daily_global_limit] int NOT NULL DEFAULT(0),
        [daily_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_NRP_realm_active] ON [dbo].[NEXORA_ROULETTE_PRIZES]([realm],[active],[sort_order],[id]);
END;
IF OBJECT_ID(N'[dbo].[NEXORA_ROULETTE_STATE]', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[NEXORA_ROULETTE_STATE]([realm] varchar(20) NOT NULL,[account_username] varchar(50) NOT NULL,[free_spins] int NOT NULL DEFAULT(0),[pity_streak] int NOT NULL DEFAULT(0),[total_spins] bigint NOT NULL DEFAULT(0),[updated_at] datetime2 NOT NULL DEFAULT(SYSUTCDATETIME()),CONSTRAINT [PK_NRS] PRIMARY KEY([realm],[account_username]));
END;
IF OBJECT_ID(N'[dbo].[NEXORA_ROULETTE_SPINS]', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[NEXORA_ROULETTE_SPINS]([id] bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,[spin_token] char(32) NOT NULL UNIQUE,[realm] varchar(20) NOT NULL,[user_id] int NOT NULL,[account_username] varchar(50) NOT NULL,[wallet_config_id] int NULL,[wallet_identifier] nvarchar(190) NULL,[cost] int NOT NULL,[used_free_spin] bit NOT NULL DEFAULT(0),[prize_id] int NOT NULL,[prize_name] nvarchar(120) NOT NULL,[prize_description] nvarchar(400) NULL,[prize_type] varchar(30) NOT NULL,[rarity] varchar(20) NOT NULL,[prize_amount] int NOT NULL DEFAULT(0),[prize_credit_config_id] int NULL,[prize_item_id] int NULL,[status] varchar(30) NOT NULL,[roll_value] int NOT NULL,[total_weight] int NOT NULL,[pity_before] int NOT NULL,[pity_after] int 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_NRX_account] ON [dbo].[NEXORA_ROULETTE_SPINS]([realm],[account_username],[created_at] DESC);
    CREATE INDEX [IX_NRX_prize_day] ON [dbo].[NEXORA_ROULETTE_SPINS]([realm],[prize_id],[created_at] DESC);
    CREATE INDEX [IX_NRX_status] ON [dbo].[NEXORA_ROULETTE_SPINS]([realm],[status],[created_at] DESC);
END;

-- V1.4.0 - Progression & Economy
IF COL_LENGTH('dbo.NEXORA_ROULETTE_SPINS','fragments_earned') IS NULL
BEGIN
    ALTER TABLE [dbo].[NEXORA_ROULETTE_SPINS] ADD [fragments_earned] int NOT NULL CONSTRAINT [DF_NRX_fragments] DEFAULT(0) WITH VALUES;
END;
IF OBJECT_ID(N'[dbo].[NEXORA_ROULETTE_PROGRESS]', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[NEXORA_ROULETTE_PROGRESS](
        [realm] varchar(20) NOT NULL,
        [account_username] varchar(50) NOT NULL,
        [fragments] bigint NOT NULL CONSTRAINT [DF_NRG_fragments] DEFAULT(0),
        [lifetime_fragments] bigint NOT NULL CONSTRAINT [DF_NRG_lifetime] DEFAULT(0),
        [updated_at] datetime2 NOT NULL CONSTRAINT [DF_NRG_updated] DEFAULT(SYSUTCDATETIME()),
        CONSTRAINT [PK_NRG] PRIMARY KEY([realm],[account_username])
    );
END;
IF OBJECT_ID(N'[dbo].[NEXORA_ROULETTE_MILESTONES]', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[NEXORA_ROULETTE_MILESTONES](
        [id] bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
        [realm] varchar(20) NOT NULL,
        [account_username] varchar(50) NOT NULL,
        [milestone_no] tinyint NOT NULL,
        [spin_threshold] int NOT NULL,
        [fragments_reward] int NOT NULL CONSTRAINT [DF_NRM_fragments] DEFAULT(0),
        [free_spins_reward] int NOT NULL CONSTRAINT [DF_NRM_spins] DEFAULT(0),
        [created_at] datetime2 NOT NULL CONSTRAINT [DF_NRM_created] DEFAULT(SYSUTCDATETIME()),
        CONSTRAINT [UX_NRM_account_milestone] UNIQUE([realm],[account_username],[milestone_no])
    );
    CREATE INDEX [IX_NRM_realm_created] ON [dbo].[NEXORA_ROULETTE_MILESTONES]([realm],[created_at] DESC);
END;

-- V1.5.0 - Fragment Exchange
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 CONSTRAINT [DF_NRE_amount] DEFAULT(0),
        [credit_config_id] int NULL,
        [item_id] int NULL,
        [global_limit] int NOT NULL CONSTRAINT [DF_NRE_global] DEFAULT(0),
        [account_limit] int NOT NULL CONSTRAINT [DF_NRE_account] DEFAULT(0),
        [active] bit NOT NULL CONSTRAINT [DF_NRE_active] DEFAULT(1),
        [sort_order] int NOT NULL CONSTRAINT [DF_NRE_sort] DEFAULT(100),
        [created_at] datetime2 NOT NULL CONSTRAINT [DF_NRE_created] DEFAULT(SYSUTCDATETIME()),
        [updated_at] datetime2 NOT NULL CONSTRAINT [DF_NRE_updated] DEFAULT(SYSUTCDATETIME()),
        CONSTRAINT [CK_NRE_cost] CHECK([fragment_cost] > 0),
        CONSTRAINT [CK_NRE_limits] CHECK([global_limit] >= 0 AND [account_limit] >= 0)
    );
    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,
        [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 CONSTRAINT [DF_NRL_amount] 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 CONSTRAINT [DF_NRL_created] DEFAULT(SYSUTCDATETIME()),
        [claimed_at] datetime2 NULL,
        CONSTRAINT [UX_NRL_reference] UNIQUE([reference_key]),
        CONSTRAINT [CK_NRL_fragments] CHECK([fragment_cost] > 0 AND [fragments_before] >= 0 AND [fragments_after] >= 0)
    );
    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;
