← back to Bertha

src/app/api/weather/route.js

121 lines

import { NextResponse } from 'next/server';
const { query } = require('../../../lib/db');
const { getGridPoint, getForecast, getHourlyForecast, getCityCoords, geomHash, extractForecastFeatures, CITY_COORDS } = require('../../../lib/weather');

export const dynamic = 'force-dynamic';

export async function GET() {
  try {
    const res = await query('SELECT * FROM forecast_runs ORDER BY run_ts DESC LIMIT 200');
    return NextResponse.json({ forecasts: res.rows });
  } catch (err) {
    return NextResponse.json({ error: err.message }, { status: 500 });
  }
}

async function fetchCityForecast(cityName) {
  const coords = getCityCoords(cityName);
  if (!coords) return { error: `Unknown city: ${cityName}` };

  try {
    // Step 1: Get grid mapping
    const point = await getGridPoint(coords.lat, coords.lon);
    const props = point.properties;
    const office = props.gridId;
    const gridX = props.gridX;
    const gridY = props.gridY;

    // Step 2: Get forecast
    const forecast = await getForecast(office, gridX, gridY);
    const periods = forecast?.properties?.periods || [];
    const gHash = geomHash(coords.lat, coords.lon);
    const now = new Date();

    let stored = 0;
    for (const period of periods) {
      const temp = period.temperature;
      const windMatch = period.windSpeed?.match(/(\d+)/);
      const windSpeed = windMatch ? parseInt(windMatch[1]) : null;
      const precipProb = period.probabilityOfPrecipitation?.value || 0;
      const startTime = new Date(period.startTime);
      const leadHours = Math.round((startTime - now) / 3600000);

      // Store temperature
      if (temp != null) {
        await query(`
          INSERT INTO forecast_runs (source, run_ts, geom_hash, location_name, lat, lon, lead_hours, variable, value_mean, value_min, value_max, raw_meta)
          VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $9, $9, $10)
          ON CONFLICT (source, run_ts, geom_hash, lead_hours, variable) DO UPDATE SET
            value_mean = EXCLUDED.value_mean, raw_meta = EXCLUDED.raw_meta
        `, ['nws', now, gHash, cityName, coords.lat, coords.lon, leadHours, 'temperature', temp, period]);
        stored++;
      }

      // Store wind
      if (windSpeed != null) {
        await query(`
          INSERT INTO forecast_runs (source, run_ts, geom_hash, location_name, lat, lon, lead_hours, variable, value_mean, value_min, value_max, raw_meta)
          VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $9, $9, $10)
          ON CONFLICT (source, run_ts, geom_hash, lead_hours, variable) DO UPDATE SET
            value_mean = EXCLUDED.value_mean, raw_meta = EXCLUDED.raw_meta
        `, ['nws', now, gHash, cityName, coords.lat, coords.lon, leadHours, 'wind_speed', windSpeed, period]);
        stored++;
      }

      // Store precip probability
      await query(`
        INSERT INTO forecast_runs (source, run_ts, geom_hash, location_name, lat, lon, lead_hours, variable, value_mean, value_min, value_max, raw_meta)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $9, $9, $10)
        ON CONFLICT (source, run_ts, geom_hash, lead_hours, variable) DO UPDATE SET
          value_mean = EXCLUDED.value_mean, raw_meta = EXCLUDED.raw_meta
      `, ['nws', now, gHash, cityName, coords.lat, coords.lon, leadHours, 'precip_probability', precipProb, period]);
      stored++;
    }

    return { city: cityName, periods: periods.length, stored, office, gridX, gridY };
  } catch (err) {
    return { city: cityName, error: err.message };
  }
}

export async function POST(req) {
  const body = await req.json();

  if (body.action === 'test_nws') {
    try {
      const point = await getGridPoint(40.7128, -74.0060); // NYC
      return NextResponse.json({ success: true, office: point.properties?.gridId });
    } catch (err) {
      return NextResponse.json({ success: false, error: err.message });
    }
  }

  if (body.action === 'fetch') {
    const result = await fetchCityForecast(body.city || 'New York');
    if (result.error) return NextResponse.json({ error: result.error }, { status: 400 });

    await query(`INSERT INTO alerts_log (alert_type, severity, message, details) VALUES ($1, $2, $3, $4)`,
      ['weather_fetch', 'info', `Fetched ${result.periods} forecast periods for ${result.city}`, result]);

    return NextResponse.json(result);
  }

  if (body.action === 'fetch_all') {
    const cities = Object.keys(CITY_COORDS);
    const results = [];
    for (const city of cities) {
      const result = await fetchCityForecast(city);
      results.push(result);
      await new Promise(r => setTimeout(r, 300)); // rate limit NWS
    }

    const success = results.filter(r => !r.error);
    await query(`INSERT INTO alerts_log (alert_type, severity, message, details) VALUES ($1, $2, $3, $4)`,
      ['weather_fetch_all', 'info', `Fetched forecasts for ${success.length}/${cities.length} cities`, { results: results.map(r => ({ city: r.city, ok: !r.error, periods: r.periods })) }]);

    return NextResponse.json({ cities_fetched: success.length, total: cities.length, results });
  }

  return NextResponse.json({ error: 'Unknown action' }, { status: 400 });
}