import Database from 'better-sqlite3';


const db = new Database('orl.db');
db.pragma('foreign_keys = ON');
db.pragma('journal_mode = WAL');
db.pragma('temp_store = MEMORY');


db.exec(`
  CREATE TABLE IF NOT EXISTS rating (
    user_id        INTEGER NOT NULL,
    mode           INTEGER NOT NULL, -- 0 = std, 1 = taiko, 2 = ctb, 3 = mania

    s1_scores      INTEGER DEFAULT 0,
    s2_scores      INTEGER DEFAULT 0,
    s3_scores      INTEGER NOT NULL,
    total_scores   INTEGER NOT NULL,
    elo            REAL    NOT NULL,
    division       TEXT    NOT NULL,
    s1_division    TEXT,
    s2_division    TEXT,

    UNIQUE(user_id, mode) ON CONFLICT ROLLBACK
  );

  CREATE INDEX IF NOT EXISTS rating_elo_idx ON rating (elo, mode);


  CREATE TABLE IF NOT EXISTS map (
    map_id        INTEGER PRIMARY KEY,

    -- info from .osu file
    name          TEXT    NOT NULL,
    mode          INTEGER NOT NULL,           -- (0 = std, 1 = taiko, 2 = ctb, 3 = mania)
    ar            REAL    NOT NULL,
    cs            REAL    NOT NULL,
    hp            REAL    NOT NULL,
    od            REAL    NOT NULL,
    bpm           REAL    NOT NULL,

    -- info from osu!api or from osu.db scan (https://git.kiwec.net/kiwec/orl-maps-db-generator)
    set_id        INTEGER NOT NULL,
    length        REAL    NOT NULL,
    ranked        INTEGER NOT NULL,           -- not a boolean but an enum
    dmca          INTEGER NOT NULL
  );

  CREATE TABLE IF NOT EXISTS pp (
    map_id INTEGER NOT NULL,
    mods   INTEGER NOT NULL,
    stars  REAL    NOT NULL,
    pp     REAL    NOT NULL,

    FOREIGN KEY(map_id) REFERENCES map(map_id),

    UNIQUE(map_id, mods) ON CONFLICT ROLLBACK
  );


  CREATE TABLE IF NOT EXISTS user (
    user_id         INTEGER PRIMARY KEY,
    username        TEXT    NOT NULL,
    country_code    TEXT    NOT NULL,
    profile_data    TEXT    NOT NULL,
    discord_user_id TEXT
  );


  CREATE TABLE IF NOT EXISTS map_pool (
    season        INTEGER NOT NULL,
    collection_id INTEGER NOT NULL,
    user_id       INTEGER NOT NULL,
    added_tms     INTEGER NOT NULL,
    data          TEXT    NOT NULL
  );


  CREATE TABLE IF NOT EXISTS match (
    match_id    INTEGER PRIMARY KEY,
    invite_id   INTEGER,
    name        TEXT,
    ruleset     INTEGER NOT NULL DEFAULT 0,
    data        TEXT    NOT NULL DEFAULT '{}',
    start_time  INTEGER NOT NULL,
    end_time    INTEGER,
    reopened_as INTEGER
  );

  CREATE INDEX IF NOT EXISTS match_endtms_idx ON match (end_time);


  CREATE TABLE IF NOT EXISTS game (
    game_id      INTEGER PRIMARY KEY,
    match_id     INTEGER NOT NULL, -- "match" means lobby
    start_time   INTEGER NOT NULL,
    end_time     INTEGER NOT NULL,
    beatmap_id   INTEGER NOT NULL,
    play_mode    INTEGER NOT NULL,
    scoring_type TEXT    NOT NULL,
    team_type    TEXT    NOT NULL,
    mods         TEXT    NOT NULL,

    FOREIGN KEY(match_id)   REFERENCES match(match_id),
    FOREIGN KEY(beatmap_id) REFERENCES map(map_id)
  );


  CREATE TABLE IF NOT EXISTS score (
    game_id      INTEGER NOT NULL,
    user_id      INTEGER NOT NULL,
    mode         INTEGER NOT NULL,
    score        INTEGER NOT NULL,
    accuracy     REAL,
    max_combo    INTEGER,
    count_50     INTEGER,
    count_100    INTEGER,
    count_300    INTEGER,
    count_miss   INTEGER,
    count_geki   INTEGER,
    count_katu   INTEGER,
    perfect      INTEGER,
    enabled_mods TEXT    NOT NULL,
    placement    INTEGER NOT NULL,
    dodged       INTEGER NOT NULL,
    elo_diff     REAL    NOT NULL,

    created_at   INTEGER NOT NULL,
    beatmap_id   INTEGER NOT NULL,

    FOREIGN KEY(game_id)    REFERENCES game(game_id),
    FOREIGN KEY(user_id)    REFERENCES user(user_id),
    FOREIGN KEY(beatmap_id) REFERENCES map(map_id),

    UNIQUE(game_id, user_id) ON CONFLICT ROLLBACK
  );

  -- We get all scores from user X on their profile page
  CREATE INDEX IF NOT EXISTS score_userid_idx ON score (user_id, created_at);

  -- For MAX(placement)
  CREATE INDEX IF NOT EXISTS score_placement_idx ON score (game_id);


  CREATE TABLE IF NOT EXISTS token (
    token      TEXT    NOT NULL,
    created_at INTEGER NOT NULL,
    osu_id     INTEGER,
    last_match INTEGER,
    discord_id TEXT
  );
`);

export default db;