/**
 * City catalog queries.
 *
 * queryActiveCities — distinct cities with active deals, centroid, deal count.
 * Edge-cached 1 h by caller (GET /api/cities).
 */

import { executeRows } from '../execute-rows.js';
import { sql } from 'drizzle-orm';
import type { DrizzleClient } from '../client.js';

export interface ActiveCity {
  city: string;
  cityCode: string;
  lat: number;
  lng: number;
  dealCount: number;
}

/**
 * Returns cities that have at least one ACTIVE deal from a public vendor,
 * ordered by deal count descending.
 *
 * Centroid = arithmetic mean of vendor_addresses lat/lng for that city.
 */
export async function queryActiveCities(db: DrizzleClient): Promise<ActiveCity[]> {
  type CityRow = {
    city: string;
    city_code: string;
    lat: string;
    lng: string;
    deal_count: string;
  };

  const result = await db.execute(sql`
    SELECT
      MIN(va.city) AS city,
      va.city_code,
      AVG(va.lat::numeric)::numeric(10, 7) AS lat,
      AVG(va.lng::numeric)::numeric(10, 7) AS lng,
      COUNT(DISTINCT d.id)::int AS deal_count
    FROM vendor_addresses va
    JOIN vendors v ON v.id = va.vendor_id
      AND v.account_state IN ('ACTIVE', 'VETERAN')
    JOIN deals d ON d.vendor_id = va.vendor_id
    WHERE va.is_public = true
      AND va.city != ''
      AND va.city_code != ''
      AND d.deal_state = 'ACTIVE'
      AND (d.window_end IS NULL OR d.window_end > now())
    GROUP BY va.city_code
    HAVING COUNT(DISTINCT d.id) > 0
    ORDER BY deal_count DESC
  `);

  const rows = executeRows(result) as CityRow[];

  return rows.map((r) => ({
    city: r.city,
    cityCode: r.city_code,
    lat: parseFloat(r.lat),
    lng: parseFloat(r.lng),
    dealCount: Number(r.deal_count),
  }));
}