-- Suprixx Music server schema. Safe to run more than once.
-- All times are Unix time in milliseconds (UTC), except sessions.expires_at which is also milliseconds.

CREATE TABLE IF NOT EXISTS users (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  google_sub    VARCHAR(64)  NOT NULL,
  email         VARCHAR(255) NULL,
  created_at    BIGINT UNSIGNED NOT NULL,
  last_login_at BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_google_sub (google_sub)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sessions (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id      BIGINT UNSIGNED NOT NULL,
  token_hash   CHAR(64) NOT NULL,
  created_at   BIGINT UNSIGNED NOT NULL,
  expires_at   BIGINT UNSIGNED NOT NULL,
  last_used_at BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_sessions_token (token_hash),
  KEY idx_sessions_user (user_id),
  KEY idx_sessions_expires (expires_at),
  CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Song details only. The audio itself stays in the user's Google Drive.
CREATE TABLE IF NOT EXISTS tracks (
  user_id       BIGINT UNSIGNED NOT NULL,
  track_id      VARCHAR(191) NOT NULL,
  title         VARCHAR(300) NOT NULL,
  artist        VARCHAR(300) NOT NULL DEFAULT '',
  duration_ms   BIGINT UNSIGNED NOT NULL DEFAULT 0,
  size_bytes    BIGINT UNSIGNED NOT NULL DEFAULT 0,
  drive_file_id VARCHAR(128) NULL,
  stream_url    VARCHAR(1000) NULL,
  favorite      TINYINT(1) NOT NULL DEFAULT 0,
  updated_at    BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, track_id),
  CONSTRAINT fk_tracks_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS playlists (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id    BIGINT UNSIGNED NOT NULL,
  name       VARCHAR(120) NOT NULL,
  created_at BIGINT UNSIGNED NOT NULL,
  updated_at BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  KEY idx_playlists_user (user_id),
  CONSTRAINT fk_playlists_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS playlist_tracks (
  playlist_id BIGINT UNSIGNED NOT NULL,
  position    INT UNSIGNED NOT NULL,
  user_id     BIGINT UNSIGNED NOT NULL,
  track_id    VARCHAR(191) NOT NULL,
  PRIMARY KEY (playlist_id, position),
  CONSTRAINT fk_pt_playlist FOREIGN KEY (playlist_id) REFERENCES playlists (id) ON DELETE CASCADE,
  CONSTRAINT fk_pt_track FOREIGN KEY (user_id, track_id) REFERENCES tracks (user_id, track_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rate_limits (
  k            CHAR(64) NOT NULL,
  window_start BIGINT UNSIGNED NOT NULL,
  hits         INT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (k, window_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;