Node.jsとPostgreSQLで fitnessアプリのゲーム化：連続記録、バッジ、リーダーボードを構築

mkdir fitness-gamification-backend
cd fitness-gamification-backend
npm init -y
npm install express pg

-- usersテーブル
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- ユーザーの日次アクティビティを追跡するworkout_activitiesテーブル
CREATE TABLE workout_activities (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    activity_date DATE NOT NULL,
    calories_burned INTEGER,
    UNIQUE(user_id, activity_date)
);

-- 利用可能なバッジを定義するbadgesテーブル
CREATE TABLE badges (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    criteria_value INTEGER
);

-- 獲得したバッジを追跡するuser_badgesテーブル
CREATE TABLE user_badges (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    badge_id INTEGER REFERENCES badges(id),
    earned_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, badge_id)
);

// db.js
const { Pool } = require('pg');

const pool = new Pool({
  user: 'your_db_user',
  host: 'localhost',
  database: 'your_db_name',
  password: 'your_db_password',
  port: 5432,
});

module.exports = {
  query: (text, params) => pool.query(text, params),
};

// streaks.js
const db = require('./db');

async function getUserStreak(userId) {
  const query = `
    WITH date_diffs AS (
      SELECT
        activity_date,
        activity_date - ROW_NUMBER() OVER (ORDER BY activity_date) * INTERVAL '1 day' as date_group
      FROM workout_activities
      WHERE user_id = $1
      ORDER BY activity_date DESC
    )
    SELECT
      COUNT(*) AS streak
    FROM date_diffs
    WHERE date_group = (SELECT date_group FROM date_diffs LIMIT 1);
  `;
  const { rows } = await db.query(query, [userId]);
  return rows[0] ? parseInt(rows[0].streak, 10) : 0;
}

module.exports = { getUserStreak };

// index.js
const express = require('express');
const { getUserStreak } = require('./streaks');

const app = express();
const port = 3000;

app.get('/users/:userId/streak', async (req, res) => {
  try {
    const streak = await getUserStreak(req.params.userId);
    res.json({ streak });
  } catch (err) {
    console.error(err);
    res.status(500).send('Server Error');
  }
});

app.listen(port, () => {
  console.log(`Server running on port ${port}`);
});

INSERT INTO badges (name, description, criteria_value) VALUES
('Calorie Crusher 10k', 'Burn a total of 10,000 calories', 10000);

// badges.js
const db = require('./db');

async function checkAndAwardBadges(userId) {
  const { rows: badges } = await db.query('SELECT * FROM badges');
  const { rows: userBadges } = await db.query('SELECT badge_id FROM user_badges WHERE user_id = $1', [userId]);
  const earnedBadgeIds = userBadges.map(b => b.badge_id);

  for (const badge of badges) {
    if (earnedBadgeIds.includes(badge.id)) continue;

    if (badge.name === 'Calorie Crusher 10k') {
      const { rows } = await db.query(
        'SELECT SUM(calories_burned) as total_calories FROM workout_activities WHERE user_id = $1',
        [userId]
      );
      const totalCalories = parseInt(rows[0].total_calories, 10) || 0;

      if (totalCalories >= badge.criteria_value) {
        await db.query('INSERT INTO user_badges (user_id, badge_id) VALUES ($1, $2)', [userId, badge.id]);
        console.log(`Awarded badge "${badge.name}" to user ${userId}`);
      }
    }
  }
}

module.exports = { checkAndAwardBadges };

// leaderboard.js
const db = require('./db');

async function getLeaderboard() {
  const query = `
    SELECT
      u.username,
      SUM(wa.calories_burned) as total_calories,
      RANK() OVER (ORDER BY SUM(wa.calories_burned) DESC) as rank
    FROM users u
    JOIN workout_activities wa ON u.id = wa.user_id
    GROUP BY u.id
    ORDER BY rank;
  `;
  const { rows } = await db.query(query);
  return rows;
}

module.exports = { getLeaderboard };

// index.js（既存のファイルに追加）
const { getLeaderboard } = require('./leaderboard');

app.get('/leaderboard', async (req, res) => {
  try {
    const leaderboard = await getLeaderboard();
    res.json(leaderboard);
  } catch (err) {
    console.error(err);
    res.status(500).send('Server Error');
  }
});