import sql from '../db';
import { Activity } from '../types/types';

export class ActivityModel {
  static async ensureTable(): Promise<void> {
    await sql`
      CREATE TABLE IF NOT EXISTS activities (
        id TEXT PRIMARY KEY,
        label TEXT NOT NULL,
        sort_order INT NOT NULL DEFAULT 0,
        is_visible BOOLEAN NOT NULL DEFAULT TRUE,
        created_at TIMESTAMP DEFAULT NOW()
      );
    `;

    // Seed if empty (idempotent)
    const [{ count }] = await sql<{ count: number }[]>`
      SELECT COUNT(*)::int AS count FROM activities;
    `;

    if (count === 0) {
      // Keep this list in the DB, not in the mobile app.
      // IDs should match values stored in routes.uses.
      await sql`
        INSERT INTO activities (id, label, sort_order, is_visible)
        VALUES
          ('downhill', 'Skiing', 10, TRUE),
          ('nordic', 'Cross-country', 20, TRUE),
          ('skitour', 'Ski Touring', 30, TRUE),
          ('snowboard', 'Snowboarding', 40, TRUE),
          ('hike', 'Hiking', 50, TRUE),
          ('snow_park', 'Snow Park', 60, TRUE),
          ('playground', 'Playground', 900, FALSE),
          ('sleigh', 'Sleigh', 910, FALSE)
        ON CONFLICT (id) DO NOTHING;
      `;
    }
  }

  static async listVisible(): Promise<Activity[]> {
    const rows = await sql<Activity[]>`
      SELECT id, label, sort_order, is_visible
      FROM activities
      WHERE is_visible = TRUE
      ORDER BY sort_order ASC, label ASC;
    `;
    return rows;
  }

  static async listAll(): Promise<Activity[]> {
    const rows = await sql<Activity[]>`
      SELECT id, label, sort_order, is_visible
      FROM activities
      ORDER BY sort_order ASC, label ASC;
    `;
    return rows;
  }
}