:VERSION 1

CREATE TABLE UserAccounts (
  "PrincipalID" uuid NOT NULL Primary key,
  "ScopeID" uuid NOT NULL,
  "FirstName" varchar(64) NOT NULL,
  "LastName" varchar(64) NOT NULL,
  "Email" varchar(64) NULL,
  "ServiceURLs" text NULL,
  "Created" int default NULL
);


:VERSION 2

BEGIN TRANSACTION;

INSERT INTO UserAccounts ("PrincipalID", "ScopeID", "FirstName", "LastName", "Email", "ServiceURLs", "Created") 
SELECT UUID AS "PrincipalID", '00000000-0000-0000-0000-000000000000' AS "ScopeID", 
username AS "FirstName", 
lastname AS "LastName", 
email as "Email", (
'AssetServerURI=' +  
userAssetURI + ' InventoryServerURI='  + userInventoryURI + ' GatewayURI= HomeURI=') AS "ServiceURLs", 
created as "Created" FROM users;

COMMIT;

:VERSION 3

BEGIN TRANSACTION;

CREATE UNIQUE INDEX "PrincipalID" ON UserAccounts("PrincipalID");
CREATE INDEX "Email" ON UserAccounts("Email");
CREATE INDEX "FirstName" ON UserAccounts("FirstName");
CREATE INDEX "LastName" ON UserAccounts("LastName");
CREATE INDEX Name ON UserAccounts("FirstName","LastName");

COMMIT;

:VERSION 4

BEGIN TRANSACTION;

ALTER TABLE UserAccounts ADD "UserLevel" integer NOT NULL DEFAULT 0;
ALTER TABLE UserAccounts ADD "UserFlags" integer NOT NULL DEFAULT 0;
ALTER TABLE UserAccounts ADD "UserTitle" varchar(64) NOT NULL DEFAULT '';

COMMIT;

:VERSION 5

BEGIN TRANSACTION;

ALTER TABLE UserAccounts ADD "active" integer NOT NULL DEFAULT 1;

COMMIT;