/* Should probably have a prefix, perhaps customizable, to prevent table collisions */

/**
 * The table containing servers for local portals on this server
 */
CREATE TABLE Portal (
    name NVARCHAR(180),
    network NVARCHAR(180),
    destination NVARCHAR(180),
    world NVARCHAR(255) NOT NULL,
    x INTEGER,
    y INTEGER,
    z INTEGER,
    ownerUUID VARCHAR(36),
    isBungee BOOLEAN,
    PRIMARY KEY (name, network)
) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';

/**
 * The table containing inter-portals for all servers
 */
CREATE TABLE InterPortal (
    name NVARCHAR(180),
    network NVARCHAR(180),
    destination NVARCHAR(180),
    world NVARCHAR(255) NOT NULL,
    x INTEGER,
    y INTEGER,
    z INTEGER,
    ownerUUID VARCHAR(36),
    isOnline BOOLEAN,
    homeServerId VARCHAR(36),
    PRIMARY KEY (name, network)
) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';

/**
 * The table storing the last known name connected to a UUID
 */
CREATE TABLE LastKnownName (
    uuid VARCHAR(36),
    lastKnownName VARCHAR(16),
    PRIMARY KEY (uuid)
);

/* 
 * The table to store all available flags
 *
 * This table should be shared between all servers as this only stores the available flags
 */
CREATE TABLE Flag (
    id INTEGER AUTO_INCREMENT, /* Use AUTOINCREMENT for SQLite */
    character CHAR(1) UNIQUE NOT NULL,
    PRIMARY KEY (id)
);

/* A table containing known server information */
CREATE TABLE ServerInfo (
    serverId VARCHAR(36),
    serverName NVARCHAR(255),
    serverPrefix VARCHAR(50),
    PRIMARY KEY (serverId)
);

/*
 * The table to store which portals have which flags
 */
CREATE TABLE PortalFlagRelation (
    name NVARCHAR(180),
    network NVARCHAR(180),
    flag INTEGER,
    PRIMARY KEY (name, network, flag),
    FOREIGN KEY (name) REFERENCES Portal (name),
    FOREIGN KEY (network) REFERENCES Portal (network),
    FOREIGN KEY (flag) REFERENCES Flag (id)
);


/*
 * Views to prevent having to combine tables on every query
 */
CREATE VIEW PortalView AS SELECT Portal.*, GROUP_CONCAT(Flag.character) FROM Portal 
LEFT OUTER JOIN PortalFlagRelation ON 
PortalFlagRelation.name = Portal.name AND PortalFlagRelation.network = Portal.network LEFT OUTER JOIN Flag ON PortalFlagRelation.flag = Flag.id;
CREATE VIEW InterPortalView AS SELECT InterPortal.*, GROUP_CONCAT(Flag.character), ServerInfo.serverName FROM InterPortal 
INNER JOIN ServerInfo ON ServerName.serverId = InterPortal.homeServerId ON  
LEFT OUTER JOIN PortalFlagRelation ON 
PortalFlagRelation.name = InterPortal.name AND PortalFlagRelation.network = InterPortal.network LEFT OUTER JOIN Flag ON PortalFlagRelation.flag = Flag.id;

/*
 * The query used to get all portals
 */
SELECT Portal.* FROM PortalView GROUP BY name, network;

/*
 * The query used to get all inter-portals
 */
SELECT InterPortal.* FROM InterPortalView GROUP BY name, network;

/**
 * The query for updating the last known name
 *
 * Input: New last known name, UUID, New last known name
 */
UPDATE LastKnownName set lastKnownName = ? WHERE uuid = ? AND lastKnownName != ?;```