CREATE TABLE IF NOT EXISTS [Developer]
(
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [name] TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS [Genre]
(
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [name] TEXT UNIQUE
);

CREATE TABLE IF NOT EXISTS [Publisher]
(
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [name] TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS [Year]
(
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [name] TEXT UNIQUE
);

CREATE TABLE IF NOT EXISTS [RCBSetting]
(
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [lastSelectedView] TEXT,
    [lastSelectedConsoleId] INTEGER,
    [lastSelectedGenreId] INTEGER,
    [lastSelectedPublisherId] INTEGER,
    [lastSelectedDeveloperId] INTEGER,
    [lastSelectedYearId] INTEGER,
    [lastSelectedGameIndex] INTEGER,
    [dbVersion] TEXT,
    [lastFocusedControlMainView] INTEGER,
    [lastFocusedControlGameInfoView] INTEGER,
    [lastSelectedCharacter] TEXT,
    [lastSelectedMaxPlayers] TEXT,
    [lastSelectedRating] REAL,
    [lastSelectedRegion] TEXT,
    [sortMethod] TEXT,
    [sortDirection] TEXT
);


CREATE TABLE IF NOT EXISTS [Game] 
(
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [name] TEXT NOT NULL,
    [description] TEXT,
    [gameCmd] TEXT,
    [alternateGameCmd] TEXT,
    [romCollectionId] INTEGER,
    [publisherId] INTEGER REFERENCES [Publisher]
    (
        id
    ),
    [developerId] INTEGER REFERENCES [Developer]
    (
        id
    ),
    [yearId] INTEGER REFERENCES [Year]
    (
        id
    ),
    [maxPlayers] TEXT,
    [rating] TEXT,
    [numVotes] INTEGER,
    [url] TEXT,
    [region] TEXT,
    [media] TEXT,
    [perspective] TEXT,
    [controllerType] TEXT,
    [isFavorite] INTEGER,
    [launchCount] INTEGER,
    [originalTitle] TEXT,
    [alternateTitle] TEXT,
    [translatedBy] TEXT,
    [version] TEXT,
    [fileType1] TEXT,
    [fileType2] TEXT,
    [fileType3] TEXT,
    [fileType4] TEXT,
    [fileType5] TEXT,
    [fileType6] TEXT,
    [fileType7] TEXT,
    [fileType8] TEXT,
    [fileType9] TEXT,
    [fileType10] TEXT,
    [fileType11] TEXT,
    [fileType12] TEXT,
    [fileType13] TEXT,
    [fileType14] TEXT,
    [fileType15] TEXT,
    UNIQUE (name, romCollectionId)
);


CREATE TABLE IF NOT EXISTS [File]
(
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [name] TEXT NOT NULL,
    [fileTypeId] INTEGER,
    [parentId] INTEGER
);


CREATE TABLE IF NOT EXISTS [GenreGame]
(
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [genreId] INTEGER REFERENCES [Genre]
    (
        id
    ),
    [gameId] INTEGER REFERENCES [Game]
    (
        id
    )
);

CREATE INDEX IF NOT EXISTS genregame_genreid ON [GenreGame](genreid);


CREATE VIEW IF NOT EXISTS [GameView] AS
    SELECT     Ga.id,
            Ga.name,
            Ga.description,
            Ga.gameCmd,
            Ga.alternateGameCmd,
            Ga.isFavorite,
            Ga.launchCount,
            Ga.version,
            Ga.romCollectionId,
            Ga.developerId,
            Developer.name AS developer,
            Ga.publisherId,
            Publisher.name AS publisher,
            Ga.yearId,
            Year.name AS year,
            Ga.region,
            Ga.maxPlayers,
            Ga.rating,
            Ga.numVotes,
            Ga.url,
            Ga.media,
            Ga.controllerType,
            Ga.originalTitle,
            Ga.alternateTitle,
            Ga.translatedBy,
            Ga.perspective,
            GROUP_CONCAT(Ge.name, ', ') AS genres,
            Ga.fileType1,
            Ga.fileType2,
            Ga.fileType3,
            Ga.fileType4,
            Ga.fileType5,
            Ga.fileType6,
            Ga.fileType7,
            Ga.fileType8,
            Ga.fileType9,
            Ga.fileType10,
            Ga.fileType11,
            Ga.fileType12,
            Ga.fileType13,
            Ga.fileType14,
            Ga.fileType15

            FROM [Game] Ga
                LEFT OUTER JOIN Developer ON Ga.developerId = Developer.id
                LEFT OUTER JOIN Publisher ON Ga.publisherId = Publisher.id
                LEFT OUTER JOIN Year ON Ga.yearId = Year.id
                LEFT OUTER JOIN GenreGame Gg ON Ga.id = Gg.gameid
                LEFT OUTER JOIN Genre Ge ON Ge.id = Gg.genreid
                
            GROUP BY Ga.id;