/*
 MU Nexora - Platform database bootstrap (TEST/STAGING FIRST)
 ------------------------------------------------------------
 Purpose:
   - Separate WebEngine/platform state from realm/game state.
   - Preserve the existing muonline WebEngine data as the initial source of truth.
   - Add security/audit/realm/RBAC/ledger tables required by the Nexora test build.

 Assumptions:
   - [muonline] is the current Legado database on the same SQL Server instance.
   - Run using an account allowed to CREATE DATABASE and read [muonline].
   - Review/backup before production use.
*/
SET NOCOUNT ON;
GO

IF DB_ID(N'nexora_platform') IS NULL
BEGIN
    CREATE DATABASE [nexora_platform];
END
GO

USE [nexora_platform];
GO

/* Clone current WebEngine platform data from Legado. SELECT INTO intentionally
   preserves the currently deployed columns (including local custom columns). */
IF OBJECT_ID(N'dbo.WEBENGINE_BANS', N'U') IS NULL AND OBJECT_ID(N'muonline.dbo.WEBENGINE_BANS', N'U') IS NOT NULL
    SELECT * INTO dbo.WEBENGINE_BANS FROM muonline.dbo.WEBENGINE_BANS;
IF OBJECT_ID(N'dbo.WEBENGINE_BAN_LOG', N'U') IS NULL AND OBJECT_ID(N'muonline.dbo.WEBENGINE_BAN_LOG', N'U') IS NOT NULL
    SELECT * INTO dbo.WEBENGINE_BAN_LOG FROM muonline.dbo.WEBENGINE_BAN_LOG;
IF OBJECT_ID(N'dbo.WEBENGINE_BLOCKED_IP', N'U') IS NULL AND OBJECT_ID(N'muonline.dbo.WEBENGINE_BLOCKED_IP', N'U') IS NOT NULL
    SELECT * INTO dbo.WEBENGINE_BLOCKED_IP FROM muonline.dbo.WEBENGINE_BLOCKED_IP;
IF OBJECT_ID(N'dbo.WEBENGINE_CREDITS_CONFIG', N'U') IS NULL AND OBJECT_ID(N'muonline.dbo.WEBENGINE_CREDITS_CONFIG', N'U') IS NOT NULL
    SELECT * INTO dbo.WEBENGINE_CREDITS_CONFIG FROM muonline.dbo.WEBENGINE_CREDITS_CONFIG;
IF OBJECT_ID(N'dbo.WEBENGINE_CREDITS_LOGS', N'U') IS NULL AND OBJECT_ID(N'muonline.dbo.WEBENGINE_CREDITS_LOGS', N'U') IS NOT NULL
    SELECT * INTO dbo.WEBENGINE_CREDITS_LOGS FROM muonline.dbo.WEBENGINE_CREDITS_LOGS;
IF OBJECT_ID(N'dbo.WEBENGINE_CRON', N'U') IS NULL AND OBJECT_ID(N'muonline.dbo.WEBENGINE_CRON', N'U') IS NOT NULL
    SELECT * INTO dbo.WEBENGINE_CRON FROM muonline.dbo.WEBENGINE_CRON;
IF OBJECT_ID(N'dbo.WEBENGINE_DOWNLOADS', N'U') IS NULL AND OBJECT_ID(N'muonline.dbo.WEBENGINE_DOWNLOADS', N'U') IS NOT NULL
    SELECT * INTO dbo.WEBENGINE_DOWNLOADS FROM muonline.dbo.WEBENGINE_DOWNLOADS;
IF OBJECT_ID(N'dbo.WEBENGINE_FLA', N'U') IS NULL AND OBJECT_ID(N'muonline.dbo.WEBENGINE_FLA', N'U') IS NOT NULL
    SELECT * INTO dbo.WEBENGINE_FLA FROM muonline.dbo.WEBENGINE_FLA;
IF OBJECT_ID(N'dbo.WEBENGINE_NEWS', N'U') IS NULL AND OBJECT_ID(N'muonline.dbo.WEBENGINE_NEWS', N'U') IS NOT NULL
    SELECT * INTO dbo.WEBENGINE_NEWS FROM muonline.dbo.WEBENGINE_NEWS;
IF OBJECT_ID(N'dbo.WEBENGINE_NEWS_TRANSLATIONS', N'U') IS NULL AND OBJECT_ID(N'muonline.dbo.WEBENGINE_NEWS_TRANSLATIONS', N'U') IS NOT NULL
    SELECT * INTO dbo.WEBENGINE_NEWS_TRANSLATIONS FROM muonline.dbo.WEBENGINE_NEWS_TRANSLATIONS;
/* Do NOT copy legacy pending password changes: that table can contain plaintext pending passwords. */
IF OBJECT_ID(N'dbo.WEBENGINE_PASSCHANGE_REQUEST', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.WEBENGINE_PASSCHANGE_REQUEST (
        user_id INT NOT NULL PRIMARY KEY,
        new_password VARCHAR(200) NOT NULL,
        auth_code VARCHAR(50) NOT NULL,
        request_date VARCHAR(50) NOT NULL
    );
END
IF OBJECT_ID(N'dbo.WEBENGINE_PAYPAL_TRANSACTIONS', N'U') IS NULL AND OBJECT_ID(N'muonline.dbo.WEBENGINE_PAYPAL_TRANSACTIONS', N'U') IS NOT NULL
    SELECT * INTO dbo.WEBENGINE_PAYPAL_TRANSACTIONS FROM muonline.dbo.WEBENGINE_PAYPAL_TRANSACTIONS;
IF OBJECT_ID(N'dbo.WEBENGINE_PLUGINS', N'U') IS NULL AND OBJECT_ID(N'muonline.dbo.WEBENGINE_PLUGINS', N'U') IS NOT NULL
    SELECT * INTO dbo.WEBENGINE_PLUGINS FROM muonline.dbo.WEBENGINE_PLUGINS;
/* Do NOT copy legacy pending registrations: registration_password is plaintext in the legacy schema. */
IF OBJECT_ID(N'dbo.WEBENGINE_REGISTER_ACCOUNT', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.WEBENGINE_REGISTER_ACCOUNT (
        registration_account VARCHAR(50) NOT NULL PRIMARY KEY,
        registration_password VARCHAR(50) NOT NULL,
        registration_email VARCHAR(50) NOT NULL,
        registration_date VARCHAR(50) NOT NULL,
        registration_ip VARCHAR(50) NOT NULL,
        registration_key VARCHAR(50) NOT NULL
    );
END
IF OBJECT_ID(N'dbo.WEBENGINE_VOTES', N'U') IS NULL AND OBJECT_ID(N'muonline.dbo.WEBENGINE_VOTES', N'U') IS NOT NULL
    SELECT * INTO dbo.WEBENGINE_VOTES FROM muonline.dbo.WEBENGINE_VOTES;
IF OBJECT_ID(N'dbo.WEBENGINE_VOTE_LOGS', N'U') IS NULL AND OBJECT_ID(N'muonline.dbo.WEBENGINE_VOTE_LOGS', N'U') IS NOT NULL
    SELECT * INTO dbo.WEBENGINE_VOTE_LOGS FROM muonline.dbo.WEBENGINE_VOTE_LOGS;
IF OBJECT_ID(N'dbo.WEBENGINE_VOTE_SITES', N'U') IS NULL AND OBJECT_ID(N'muonline.dbo.WEBENGINE_VOTE_SITES', N'U') IS NOT NULL
    SELECT * INTO dbo.WEBENGINE_VOTE_SITES FROM muonline.dbo.WEBENGINE_VOTE_SITES;
IF OBJECT_ID(N'dbo.WEBENGINE_ACCOUNT_COUNTRY', N'U') IS NULL AND OBJECT_ID(N'muonline.dbo.WEBENGINE_ACCOUNT_COUNTRY', N'U') IS NOT NULL
    SELECT * INTO dbo.WEBENGINE_ACCOUNT_COUNTRY FROM muonline.dbo.WEBENGINE_ACCOUNT_COUNTRY;
IF OBJECT_ID(N'dbo.WEBENGINE_STAFF', N'U') IS NULL AND OBJECT_ID(N'muonline.dbo.WEBENGINE_STAFF', N'U') IS NOT NULL
    SELECT * INTO dbo.WEBENGINE_STAFF FROM muonline.dbo.WEBENGINE_STAFF;
GO

/* Realm scope for sanctions after centralizing WebEngine tables. Migrated rows came from Legado. */
IF OBJECT_ID(N'dbo.WEBENGINE_BAN_LOG', N'U') IS NOT NULL AND COL_LENGTH('dbo.WEBENGINE_BAN_LOG', 'realm') IS NULL
BEGIN
    ALTER TABLE dbo.WEBENGINE_BAN_LOG ADD realm VARCHAR(32) NULL;
    UPDATE dbo.WEBENGINE_BAN_LOG SET realm = 'legacy' WHERE realm IS NULL;
    CREATE INDEX IX_WEBENGINE_BAN_LOG_realm ON dbo.WEBENGINE_BAN_LOG(realm, ban_date DESC);
END
GO
IF OBJECT_ID(N'dbo.WEBENGINE_BANS', N'U') IS NOT NULL AND COL_LENGTH('dbo.WEBENGINE_BANS', 'realm') IS NULL
BEGIN
    ALTER TABLE dbo.WEBENGINE_BANS ADD realm VARCHAR(32) NULL;
    UPDATE dbo.WEBENGINE_BANS SET realm = 'legacy' WHERE realm IS NULL;
    CREATE INDEX IX_WEBENGINE_BANS_realm ON dbo.WEBENGINE_BANS(realm, ban_date DESC);
END
GO

IF OBJECT_ID(N'dbo.WEBENGINE_STAFF', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.WEBENGINE_STAFF (
        staff_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        character_name VARCHAR(10) NOT NULL,
        account_username VARCHAR(10) NOT NULL,
        staff_rank VARCHAR(20) NOT NULL,
        added_by VARCHAR(50) NULL,
        added_date BIGINT NULL,
        realm VARCHAR(32) NOT NULL CONSTRAINT DF_WEBENGINE_STAFF_realm DEFAULT 'legacy',
        CONSTRAINT UQ_WEBENGINE_STAFF_realm_character UNIQUE(realm, character_name)
    );
END
GO
IF OBJECT_ID(N'dbo.WEBENGINE_STAFF', N'U') IS NOT NULL AND COL_LENGTH('dbo.WEBENGINE_STAFF', 'realm') IS NULL
BEGIN
    ALTER TABLE dbo.WEBENGINE_STAFF ADD realm VARCHAR(32) NULL;
    UPDATE dbo.WEBENGINE_STAFF SET realm = 'legacy' WHERE realm IS NULL;
    CREATE INDEX IX_WEBENGINE_STAFF_realm ON dbo.WEBENGINE_STAFF(realm, staff_rank, character_name);
END
GO

IF OBJECT_ID(N'dbo.NEXORA_PASSWORD_RESETS', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.NEXORA_PASSWORD_RESETS (
        id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        realm VARCHAR(32) NOT NULL,
        user_id INT NOT NULL,
        account_name VARCHAR(50) NOT NULL,
        email VARCHAR(200) NOT NULL,
        token_hash CHAR(64) NOT NULL,
        requested_ip VARCHAR(64) NOT NULL,
        created_at DATETIME2(0) NOT NULL CONSTRAINT DF_NEXORA_PASSWORD_RESETS_created DEFAULT SYSUTCDATETIME(),
        expires_at DATETIME2(0) NOT NULL,
        used_at DATETIME2(0) NULL
    );
    CREATE UNIQUE INDEX UX_NEXORA_PASSWORD_RESETS_token_hash ON dbo.NEXORA_PASSWORD_RESETS(token_hash);
    CREATE INDEX IX_NEXORA_PASSWORD_RESETS_account ON dbo.NEXORA_PASSWORD_RESETS(realm, account_name, created_at DESC);
    CREATE INDEX IX_NEXORA_PASSWORD_RESETS_ip ON dbo.NEXORA_PASSWORD_RESETS(requested_ip, created_at DESC);
END
GO

IF OBJECT_ID(N'dbo.NEXORA_EMAIL_CHANGE_REQUESTS', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.NEXORA_EMAIL_CHANGE_REQUESTS (
        id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        realm VARCHAR(32) NOT NULL,
        user_id INT NOT NULL,
        account_name VARCHAR(50) NOT NULL,
        old_email VARCHAR(200) NOT NULL,
        new_email VARCHAR(200) NOT NULL,
        token_hash CHAR(64) NOT NULL,
        requested_ip VARCHAR(64) NOT NULL,
        created_at DATETIME2(0) NOT NULL CONSTRAINT DF_NEXORA_EMAIL_CHANGE_created DEFAULT SYSUTCDATETIME(),
        expires_at DATETIME2(0) NOT NULL,
        used_at DATETIME2(0) NULL
    );
    CREATE UNIQUE INDEX UX_NEXORA_EMAIL_CHANGE_token_hash ON dbo.NEXORA_EMAIL_CHANGE_REQUESTS(token_hash);
    CREATE INDEX IX_NEXORA_EMAIL_CHANGE_account ON dbo.NEXORA_EMAIL_CHANGE_REQUESTS(realm, account_name, created_at DESC);
END
GO

IF OBJECT_ID(N'dbo.NEXORA_AUDIT_LOG', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.NEXORA_AUDIT_LOG (
        id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        actor VARCHAR(100) NOT NULL,
        realm VARCHAR(32) NULL,
        action VARCHAR(150) NOT NULL,
        target_type VARCHAR(80) NULL,
        target_id VARCHAR(200) NULL,
        old_data NVARCHAR(MAX) NULL,
        new_data NVARCHAR(MAX) NULL,
        ip_address VARCHAR(64) NULL,
        user_agent NVARCHAR(500) NULL,
        created_at DATETIME2(0) NOT NULL CONSTRAINT DF_NEXORA_AUDIT_LOG_created DEFAULT SYSUTCDATETIME()
    );
    CREATE INDEX IX_NEXORA_AUDIT_LOG_created ON dbo.NEXORA_AUDIT_LOG(created_at DESC);
    CREATE INDEX IX_NEXORA_AUDIT_LOG_actor ON dbo.NEXORA_AUDIT_LOG(actor, created_at DESC);
    CREATE INDEX IX_NEXORA_AUDIT_LOG_action ON dbo.NEXORA_AUDIT_LOG(action, created_at DESC);
END
GO

IF OBJECT_ID(N'dbo.NEXORA_PAYMENT_EVENTS', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.NEXORA_PAYMENT_EVENTS (
        transaction_id VARCHAR(255) NOT NULL PRIMARY KEY,
        realm VARCHAR(32) NOT NULL,
        user_id INT NOT NULL,
        order_id VARCHAR(255) NULL,
        status VARCHAR(32) NOT NULL CONSTRAINT DF_NEXORA_PAYMENT_EVENTS_status DEFAULT 'completed',
        reversed_at DATETIME2(0) NULL,
        created_at DATETIME2(0) NOT NULL CONSTRAINT DF_NEXORA_PAYMENT_EVENTS_created DEFAULT SYSUTCDATETIME()
    );
    CREATE INDEX IX_NEXORA_PAYMENT_EVENTS_user ON dbo.NEXORA_PAYMENT_EVENTS(user_id, created_at DESC);
END
GO

IF OBJECT_ID(N'dbo.NEXORA_WALLET_LEDGER', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.NEXORA_WALLET_LEDGER (
        id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        realm VARCHAR(32) NOT NULL,
        wallet_config INT NOT NULL,
        account_identifier VARCHAR(100) NOT NULL,
        direction VARCHAR(10) NOT NULL,
        amount BIGINT NOT NULL,
        balance_after BIGINT NULL,
        source VARCHAR(80) NOT NULL,
        reference_key VARCHAR(200) NULL,
        actor VARCHAR(100) NULL,
        ip_address VARCHAR(64) NULL,
        created_at DATETIME2(0) NOT NULL CONSTRAINT DF_NEXORA_WALLET_LEDGER_created DEFAULT SYSUTCDATETIME(),
        CONSTRAINT CK_NEXORA_WALLET_LEDGER_direction CHECK (direction IN ('credit','debit')),
        CONSTRAINT CK_NEXORA_WALLET_LEDGER_amount CHECK (amount >= 0)
    );
    CREATE INDEX IX_NEXORA_WALLET_LEDGER_account ON dbo.NEXORA_WALLET_LEDGER(realm, account_identifier, created_at DESC);
    CREATE UNIQUE INDEX UX_NEXORA_WALLET_LEDGER_reference
        ON dbo.NEXORA_WALLET_LEDGER(source, reference_key)
        WHERE reference_key IS NOT NULL;
END
GO

IF OBJECT_ID(N'dbo.NEXORA_REALM_CONFIG', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.NEXORA_REALM_CONFIG (
        realm VARCHAR(32) NOT NULL,
        config_key VARCHAR(150) NOT NULL,
        config_value NVARCHAR(MAX) NULL,
        value_type VARCHAR(20) NOT NULL CONSTRAINT DF_NEXORA_REALM_CONFIG_type DEFAULT 'string',
        updated_by VARCHAR(100) NULL,
        updated_at DATETIME2(0) NOT NULL CONSTRAINT DF_NEXORA_REALM_CONFIG_updated DEFAULT SYSUTCDATETIME(),
        CONSTRAINT PK_NEXORA_REALM_CONFIG PRIMARY KEY (realm, config_key)
    );
END
GO

IF OBJECT_ID(N'dbo.NEXORA_ROLES', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.NEXORA_ROLES (
        role_key VARCHAR(64) NOT NULL PRIMARY KEY,
        role_label VARCHAR(100) NOT NULL,
        created_at DATETIME2(0) NOT NULL CONSTRAINT DF_NEXORA_ROLES_created DEFAULT SYSUTCDATETIME()
    );
END
GO
IF OBJECT_ID(N'dbo.NEXORA_ROLE_PERMISSIONS', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.NEXORA_ROLE_PERMISSIONS (
        role_key VARCHAR(64) NOT NULL,
        permission_key VARCHAR(100) NOT NULL,
        CONSTRAINT PK_NEXORA_ROLE_PERMISSIONS PRIMARY KEY(role_key, permission_key)
    );
END
GO
IF OBJECT_ID(N'dbo.NEXORA_USER_ROLES', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.NEXORA_USER_ROLES (
        username VARCHAR(100) NOT NULL,
        role_key VARCHAR(64) NOT NULL,
        created_by VARCHAR(100) NULL,
        created_at DATETIME2(0) NOT NULL CONSTRAINT DF_NEXORA_USER_ROLES_created DEFAULT SYSUTCDATETIME(),
        CONSTRAINT PK_NEXORA_USER_ROLES PRIMARY KEY(username, role_key)
    );
END
GO

/* Recreate the most important uniqueness guarantee lost by SELECT INTO. */
IF OBJECT_ID(N'dbo.WEBENGINE_PAYPAL_TRANSACTIONS', N'U') IS NOT NULL
   AND NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.WEBENGINE_PAYPAL_TRANSACTIONS') AND name = N'UX_WEBENGINE_PAYPAL_transaction_id')
BEGIN
    IF NOT EXISTS (SELECT transaction_id FROM dbo.WEBENGINE_PAYPAL_TRANSACTIONS GROUP BY transaction_id HAVING COUNT(*) > 1)
        CREATE UNIQUE INDEX UX_WEBENGINE_PAYPAL_transaction_id ON dbo.WEBENGINE_PAYPAL_TRANSACTIONS(transaction_id);
    ELSE
        PRINT 'WARNING: historical duplicate PayPal transaction_id values detected; unique legacy index was not created. NEXORA_PAYMENT_EVENTS still protects new payments.';
END
GO

PRINT 'Nexora Platform bootstrap complete. Review row counts before enabling NEXORA_PLATFORM_ENABLED.';
GO

/* Seed baseline RBAC roles. Existing webengine.json admins remain superadmin in PHP for compatibility. */
MERGE dbo.NEXORA_ROLES AS target
USING (VALUES
    ('superadmin','Super Admin'),
    ('administrator','Administrator'),
    ('gm','Game Master'),
    ('support','Support'),
    ('content','Content')
) AS source(role_key, role_label)
ON target.role_key = source.role_key
WHEN MATCHED THEN UPDATE SET role_label = source.role_label
WHEN NOT MATCHED THEN INSERT(role_key, role_label) VALUES(source.role_key, source.role_label);
GO

;WITH permissions(role_key, permission_key) AS (
    SELECT * FROM (VALUES
        ('superadmin','*'),
        ('administrator','dashboard.view'),('administrator','accounts.view'),('administrator','accounts.edit'),
        ('administrator','characters.view'),('administrator','characters.edit'),('administrator','bans.view'),
        ('administrator','bans.manage'),('administrator','credits.view'),('administrator','credits.manage'),
        ('administrator','payments.view'),('administrator','content.view'),('administrator','content.manage'),
        ('administrator','system.view'),('administrator','system.manage'),('administrator','plugins.manage'),
        ('gm','dashboard.view'),('gm','accounts.view'),('gm','characters.view'),('gm','bans.view'),('gm','bans.manage'),
        ('support','dashboard.view'),('support','accounts.view'),('support','characters.view'),('support','bans.view'),
        ('content','dashboard.view'),('content','content.view'),('content','content.manage')
    ) v(role_key, permission_key)
)
INSERT INTO dbo.NEXORA_ROLE_PERMISSIONS(role_key, permission_key)
SELECT p.role_key, p.permission_key
FROM permissions p
WHERE NOT EXISTS (
    SELECT 1 FROM dbo.NEXORA_ROLE_PERMISSIONS x
    WHERE x.role_key = p.role_key AND x.permission_key = p.permission_key
);
GO
