3 files changed, 99 insertions, 5 deletions
diff --git a/postgres/schema.sql b/postgres/schema.sql
index efe9133..08837c5 100644
--- a/postgres/schema.sql
+++ b/postgres/schema.sql
@@ -2,6 +2,8 @@
 -- the database in some partial state. Also, notice we don't use `IF NOT EXISTS`
 -- because it could also leave the database in a bad state if for example, some
 -- old version of a table already existed.
+--==============================================================================
+
 BEGIN;
 
 -- IMPORTANT(fusion): PosgreSQL doesn't have a defined `NOCASE` collation like
@@ -78,9 +80,6 @@ CREATE TABLE Characters (
     AccountID INTEGER NOT NULL,
     Name TEXT NOT NULL COLLATE NOCASE,
     Sex SMALLINT NOT NULL,
-    Guild TEXT NOT NULL COLLATE NOCASE DEFAULT '',
-    Rank TEXT NOT NULL COLLATE NOCASE DEFAULT '',
-    Title TEXT NOT NULL DEFAULT '',
     Level SMALLINT NOT NULL DEFAULT 0,
     Profession TEXT NOT NULL DEFAULT '',
     Residence TEXT NOT NULL DEFAULT '',
@@ -93,7 +92,6 @@ CREATE TABLE Characters (
 );
 CREATE INDEX CharactersWorldIndex   ON Characters(WorldID, IsOnline);
 CREATE INDEX CharactersAccountIndex ON Characters(AccountID, IsOnline);
-CREATE INDEX CharactersGuildIndex   ON Characters(Guild, Rank);
 
 -- NOTE(fusion): It seems `RIGHT` is a reserved keyword and trying to use
 -- it as a column name will generate an error.
@@ -136,6 +134,46 @@ CREATE TABLE LoginAttempts (
 CREATE INDEX LoginAttemptsAccountIndex ON LoginAttempts(AccountID, Timestamp);
 CREATE INDEX LoginAttemptsAddressIndex ON LoginAttempts(IPAddress, Timestamp);
 
+-- Guild Tables
+--==============================================================================
+CREATE TABLE Guilds (
+    WorldID INTEGER NOT NULL,
+    GuildID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
+    Name TEXT NOT NULL COLLATE NOCASE,
+    LeaderID INTEGER NOT NULL,
+    Created TIMESTAMPTZ NOT NULL,
+    PRIMARY KEY (GuildID),
+    UNIQUE (Name),
+    UNIQUE (LeaderID)
+);
+
+CREATE TABLE GuildRanks (
+    GuildID INTEGER NOT NULL,
+    Rank SMALLINT NOT NULL,
+    Name TEXT NOT NULL,
+    PRIMARY KEY (GuildID, Rank)
+);
+
+CREATE TABLE GuildMembers (
+    GuildID INTEGER NOT NULL,
+    CharacterID INTEGER NOT NULL,
+    Rank SMALLINT NOT NULL,
+    Title TEXT NOT NULL,
+    Joined TIMESTAMPTZ NOT NULL,
+    PRIMARY KEY (CharacterID)
+);
+CREATE INDEX GuildMembersGuildIndex ON GuildMembers(GuildID, Rank);
+
+CREATE TABLE GuildInvites (
+    GuildID INTEGER NOT NULL,
+    CharacterID INTEGER NOT NULL,
+    RecruiterID INTEGER NOT NULL,
+    Timestamp TIMESTAMPTZ NOT NULL,
+    PRIMARY KEY (GuildID, CharacterID)
+);
+CREATE INDEX GuildInvitesCharacterIndex ON GuildInvites(CharacterID);
+CREATE INDEX GuildInvitesRecruiterIndex ON GuildInvites(RecruiterID);
+
 -- House Tables
 --==============================================================================
 CREATE TABLE Houses (
diff --git a/postgres/z-002-migrate-v02-to-v03.sql b/postgres/z-002-migrate-v02-to-v03.sql
new file mode 100644
index 0000000..52b02df
--- /dev/null
+++ b/postgres/z-002-migrate-v02-to-v03.sql
@@ -0,0 +1,54 @@
+-- NOTE(fusion): This file contains the migration script from v0.2 to v0.3. It's
+-- put inside a transaction to avoid errors from leaving the database in some
+-- partial state.
+--  These changes are already present in the latest `schema.sql`, so trying to
+-- apply them on a newly created database will result in errors.
+--==============================================================================
+
+BEGIN;
+
+DROP INDEX CharactersGuildIndex;
+ALTER TABLE Characters DROP COLUMN Guild;
+ALTER TABLE Characters DROP COLUMN Rank;
+ALTER TABLE Characters DROP COLUMN Title;
+
+CREATE TABLE Guilds (
+    WorldID INTEGER NOT NULL,
+    GuildID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
+    Name TEXT NOT NULL COLLATE NOCASE,
+    LeaderID INTEGER NOT NULL,
+    Created TIMESTAMPTZ NOT NULL,
+    PRIMARY KEY (GuildID),
+    UNIQUE (Name),
+    UNIQUE (LeaderID)
+);
+
+CREATE TABLE GuildRanks (
+    GuildID INTEGER NOT NULL,
+    Rank SMALLINT NOT NULL,
+    Name TEXT NOT NULL,
+    PRIMARY KEY (GuildID, Rank)
+);
+
+CREATE TABLE GuildMembers (
+    GuildID INTEGER NOT NULL,
+    CharacterID INTEGER NOT NULL,
+    Rank SMALLINT NOT NULL,
+    Title TEXT NOT NULL,
+    Joined TIMESTAMPTZ NOT NULL,
+    PRIMARY KEY (CharacterID)
+);
+CREATE INDEX GuildMembersGuildIndex ON GuildMembers(GuildID, Rank);
+
+CREATE TABLE GuildInvites (
+    GuildID INTEGER NOT NULL,
+    CharacterID INTEGER NOT NULL,
+    RecruiterID INTEGER NOT NULL,
+    Timestamp TIMESTAMPTZ NOT NULL,
+    PRIMARY KEY (GuildID, CharacterID)
+);
+CREATE INDEX GuildInvitesCharacterIndex ON GuildInvites(CharacterID);
+CREATE INDEX GuildInvitesRecruiterIndex ON GuildInvites(RecruiterID);
+
+COMMIT;
+
diff --git a/postgres/initial-data.sql b/postgres/z-999-initial-data.sql
index fbe40c7..139c4e1 100644
--- a/postgres/initial-data.sql
+++ b/postgres/z-999-initial-data.sql
@@ -1,5 +1,7 @@
--- NOTE(fusion): This file contains sample initial data. It's wrapped into a
+-- NOTE(fusion): This file contains sample initial data. It's put inside a
 -- transaction to avoid errors from leaving the database in some partial state.
+--==============================================================================
+
 BEGIN;
 
 -- 111111/tibia