-- Vote & Reward Nexora v2 Anti-Farm - manual fallback migration
-- Execute in the logical Platform DB used by the realm if automatic schema creation is denied.

IF COL_LENGTH('dbo.WEBENGINE_VOTES','device_hash') IS NULL ALTER TABLE dbo.WEBENGINE_VOTES ADD device_hash varchar(64) NULL;
IF COL_LENGTH('dbo.WEBENGINE_VOTE_LOGS','points_reward') IS NULL ALTER TABLE dbo.WEBENGINE_VOTE_LOGS ADD points_reward int NULL;
IF COL_LENGTH('dbo.WEBENGINE_VOTE_LOGS','device_hash') IS NULL ALTER TABLE dbo.WEBENGINE_VOTE_LOGS ADD device_hash varchar(64) NULL;
IF COL_LENGTH('dbo.WEBENGINE_VOTE_LOGS','reward_status') IS NULL ALTER TABLE dbo.WEBENGINE_VOTE_LOGS ADD reward_status varchar(30) NULL;

IF OBJECT_ID(N'dbo.NEXORA_VOTE_ACCOUNT_META',N'U') IS NULL
CREATE TABLE dbo.NEXORA_VOTE_ACCOUNT_META(
 realm varchar(20) NOT NULL,user_id int NOT NULL,account_username varchar(50) NOT NULL,
 created_at datetime2 NOT NULL,source varchar(30) NOT NULL,
 CONSTRAINT PK_NEXORA_VOTE_ACCOUNT_META PRIMARY KEY(realm,user_id)
);
IF OBJECT_ID(N'dbo.NEXORA_VOTE_POINTS',N'U') IS NULL
CREATE TABLE dbo.NEXORA_VOTE_POINTS(
 realm varchar(20) NOT NULL,user_id int NOT NULL,account_username varchar(50) NOT NULL,
 points int NOT NULL CONSTRAINT DF_NVP_points DEFAULT(0),lifetime_earned int NOT NULL CONSTRAINT DF_NVP_earned DEFAULT(0),
 lifetime_spent int NOT NULL CONSTRAINT DF_NVP_spent DEFAULT(0),created_at datetime2 NOT NULL,updated_at datetime2 NOT NULL,
 CONSTRAINT PK_NEXORA_VOTE_POINTS PRIMARY KEY(realm,user_id)
);
IF OBJECT_ID(N'dbo.NEXORA_VOTE_DEVICES',N'U') IS NULL
CREATE TABLE dbo.NEXORA_VOTE_DEVICES(
 device_hash varchar(64) NOT NULL,first_seen_at datetime2 NOT NULL,last_seen_at datetime2 NOT NULL,
 first_ip varchar(50) NULL,last_ip varchar(50) NULL,ua_hash varchar(64) NULL,
 CONSTRAINT PK_NEXORA_VOTE_DEVICES PRIMARY KEY(device_hash)
);
IF OBJECT_ID(N'dbo.NEXORA_VOTE_CONVERSIONS',N'U') IS NULL
CREATE TABLE dbo.NEXORA_VOTE_CONVERSIONS(
 id bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,realm varchar(20) NOT NULL,user_id int NOT NULL,
 account_username varchar(50) NOT NULL,points_spent int NOT NULL,credits_granted int NOT NULL,
 credit_config int NOT NULL,device_hash varchar(64) NULL,user_ip varchar(50) NULL,created_at datetime2 NOT NULL
);
IF OBJECT_ID(N'dbo.NEXORA_VOTE_SECURITY_EVENTS',N'U') IS NULL
CREATE TABLE dbo.NEXORA_VOTE_SECURITY_EVENTS(
 id bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,realm varchar(20) NOT NULL,user_id int NULL,
 account_username varchar(50) NULL,event_type varchar(50) NOT NULL,detail varchar(500) NULL,
 user_ip varchar(50) NULL,device_hash varchar(64) NULL,created_at datetime2 NOT NULL
);
