-- This migrates the old, handwritten database to the first revision managed by Alembic.
-- Please run `flask db upgrade` as soon as possible!

------------
-- Config --
------------

-- As config now uses a DATETIME, we will delete the old table entirely.
DROP TABLE config;

CREATE TABLE config (
    network INTEGER NOT NULL,
    backend_uptime DATETIME,
    PRIMARY KEY (network)
);

-- Provide default uptime for our two defined networks.
INSERT INTO config VALUES (0, NULL);
INSERT INTO config VALUES (1, NULL);

-------------
-- Friends --
-------------
CREATE TABLE new_friends (
     friend_code VARCHAR NOT NULL,
     network INTEGER NOT NULL,
     online BOOLEAN NOT NULL,
     title_id VARCHAR NOT NULL,
     upd_id VARCHAR NOT NULL,
     last_accessed BIGINT NOT NULL,
     account_creation BIGINT NOT NULL,
     username VARCHAR,
     message VARCHAR,
     mii VARCHAR,
     joinable BOOLEAN,
     game_description VARCHAR,
     last_online BIGINT NOT NULL,
     favorite_game BIGINT NOT NULL,
     PRIMARY KEY (friend_code),
     UNIQUE (friend_code)
);

-- The primary difference between these two tables are the column names.
-- `jeuFavori` was renamed to its English version, `favorite_game`.
-- `friend_code` is no longer unique.
INSERT INTO new_friends
    (friend_code, network, online, title_id,
     upd_id, last_accessed, account_creation, username,
     message, mii, joinable, game_description,
     last_online, favorite_game)
SELECT
    friendCode, network, online, titleID,
    updID, lastAccessed, accountCreation,
    username, message, mii, joinable,
    gameDescription, lastOnline, jeuFavori
FROM friends;

-- Swap out.
DROP TABLE friends;
ALTER TABLE new_friends RENAME TO friends;

--------------------
-- discordFriends --
--------------------

-- This table was renamed to discordFriends.
CREATE TABLE discord_friends (
     id BIGINT NOT NULL,
     friend_code VARCHAR NOT NULL,
     network INTEGER NOT NULL,
     active BOOLEAN NOT NULL,
     PRIMARY KEY (id, friend_code)
);

-- Note that we use `friend_code` instead of `friendCode`.
INSERT INTO discord_friends
    (id, friend_code, network, active)
SELECT
    ID, friendCode, network, active
FROM discordFriends;

-- Remove our old, poorly-named table.
DROP TABLE discordFriends;

-------------
-- discord --
-------------
CREATE TABLE new_discord (
     id BIGINT NOT NULL,
     refresh VARCHAR NOT NULL,
     bearer VARCHAR NOT NULL,
     session VARCHAR,
     token VARCHAR,
     last_accessed BIGINT NOT NULL,
     generation_date BIGINT NOT NULL,
     show_profile_button BOOLEAN NOT NULL,
     show_small_image BOOLEAN NOT NULL,
     PRIMARY KEY (id),
     UNIQUE (id),
     UNIQUE (token)
);

INSERT INTO new_discord
    (id, refresh, bearer, session,
     token, last_accessed, generation_date,
     show_profile_button, show_small_image)
SELECT
    ID, refresh, bearer, session, token, lastAccessed,
    generationDate, showProfileButton, showSmallImage
FROM discord;


-- Swap them out.
DROP TABLE discord;
ALTER TABLE new_discord RENAME TO discord;

-------------
-- Alembic --
-------------

-- Lastly, spoof having our initial Alembic revision.
CREATE TABLE alembic_version (
     version_num VARCHAR(32) NOT NULL
         CONSTRAINT alembic_version_pkc
             PRIMARY KEY
);
INSERT INTO alembic_version VALUES ('f2475122ee84');