Points System

// From server/services/points-service.ts:3
export type PointsSource =
  | "QUIZ_CORRECT"      // Earned for correct quiz answers
  | "LESSON_COMPLETE"   // Earned for completing lessons
  | "ACHIEVEMENT"       // Bonus points from achievements
  | "REDEMPTION"        // Points deducted for rewards
  | "ADMIN_ADJUST";     // Manual parent/admin adjustments

// From server/services/points-service.ts:28
const insertLedgerSQL = `
  INSERT INTO points_ledger (
    learner_id, 
    amount, 
    source_type, 
    source_id, 
    description
  )
  VALUES ($1, $2, $3, $4, $5)
  RETURNING id`;

await client.query(insertLedgerSQL, [
  opts.learnerId,
  opts.amount,
  opts.sourceType,
  opts.sourceId || null,
  opts.description,
]);

// From server/services/points-service.ts:19
async awardPoints(opts: AwardPointsOptions): Promise<{ newBalance: number }> {
  if (opts.amount <= 0) throw new Error("Amount must be positive");

  const client = await pool.connect();
  try {
    await client.query("BEGIN");

    // 1. Insert ledger entry
    await client.query(insertLedgerSQL, [...]);

    // 2. Update or create learner_points balance
    const upsertSQL = `
      INSERT INTO learner_points (learner_id, current_balance, total_earned, total_redeemed)
      VALUES ($1, $2, $2, 0)
      ON CONFLICT (learner_id)
      DO UPDATE SET 
        current_balance = learner_points.current_balance + EXCLUDED.current_balance,
        total_earned = learner_points.total_earned + EXCLUDED.current_balance,
        last_updated = NOW()
      RETURNING current_balance`;
      
    const { rows } = await client.query(upsertSQL, [opts.learnerId, opts.amount]);

    await client.query("COMMIT");
    return { newBalance: rows[0].current_balance };
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

// Database: learner_points table
interface LearnerPoints {
  learner_id: number;
  current_balance: number;    // Points available to spend
  total_earned: number;       // Lifetime points earned
  total_redeemed: number;     // Lifetime points spent
  last_updated: Date;
}

// From server/services/points-service.ts:62
async getBalance(learnerId: number | string): Promise<number> {
  const sql = `SELECT current_balance FROM learner_points WHERE learner_id = $1`;
  const { rows } = await pool.query(sql, [learnerId]);
  return rows.length ? Number(rows[0].current_balance) : 0;
}

// From server/services/points-service.ts:68
async getHistory(learnerId: number | string, limit = 50) {
  const sql = `
    SELECT * FROM points_ledger 
    WHERE learner_id = $1 
    ORDER BY created_at DESC 
    LIMIT $2`;
  const { rows } = await pool.query(sql, [learnerId, limit]);
  return rows;
}

[
  {
    "id": "123",
    "learner_id": 42,
    "amount": 10,
    "source_type": "QUIZ_CORRECT",
    "source_id": "lesson_abc123",
    "description": "Correct answer on Math quiz",
    "created_at": "2026-03-12T10:30:00Z"
  },
  {
    "id": "122",
    "learner_id": 42,
    "amount": 50,
    "source_type": "LESSON_COMPLETE",
    "source_id": "lesson_abc123",
    "description": "Completed lesson: Introduction to Fractions",
    "created_at": "2026-03-12T10:28:00Z"
  }
]

// From server/services/rewards-service.ts:539
export async function applyDoubleOrLossDeduction(
  learnerId: number,
  wrongCount: number
) {
  if (wrongCount <= 0) return;
  const deduction = wrongCount;

  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // Get current balance
    const { rows: balRows } = await client.query(
      `SELECT current_balance FROM learner_points WHERE learner_id=$1`,
      [learnerId]
    );
    const balance = balRows[0]?.current_balance ?? 0;
    
    // Never deduct more than current balance
    const actualDeduction = Math.min(deduction, balance);
    if (actualDeduction <= 0) {
      await client.query('COMMIT');
      return;
    }

    // Deduct points
    await client.query(
      `UPDATE learner_points
       SET current_balance = current_balance - $1, last_updated=NOW()
       WHERE learner_id=$2`,
      [actualDeduction, learnerId]
    );
    
    // Record in ledger
    await client.query(
      `INSERT INTO points_ledger (learner_id, amount, source_type, description)
       VALUES ($1, $2, 'DOUBLE_OR_LOSS_DEDUCTION', 'Wrong answer penalty')`,
      [learnerId, -actualDeduction]
    );

    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

import { pointsService } from './services/points-service';

await pointsService.awardPoints({
  learnerId: 42,
  amount: 10,
  sourceType: 'QUIZ_CORRECT',
  sourceId: 'lesson_123_question_5',
  description: 'Correct answer on multiplication question'
});

const balance = await pointsService.getBalance(42);
console.log(`Learner has ${balance} points`);

const history = await pointsService.getHistory(42, 20);
history.forEach(tx => {
  console.log(`${tx.created_at}: ${tx.amount} points - ${tx.description}`);
});