kolaytercih/scripts/refresh.ts

/**
 * Canlı YÖK Atlas API'sinden 2025 yerleştirme verisini çekip DB'yi tazeler.
 *
 * Endpoint: POST https://yokatlas.yok.gov.tr/api/tercih-kilavuz/search
 * (yokatlas-py'nin kullandığı resmî tercih-kılavuz JSON API'si)
 *
 * - Mevcut programlarda sira/puan/kontenjan/yerlesen 2025 kolonlarını günceller
 * - DB'de olmayan (yeni açılan) programları ekler
 * - Az yerleşeni olan programlarda API basariSirasi döndürmez → NULL kalır
 *
 * Kullanım: npx tsx scripts/refresh.ts
 */
import path from "node:path";
import Database from "better-sqlite3";

const API = "https://yokatlas.yok.gov.tr/api/tercih-kilavuz/search";
const PAGE_SIZE = 500;
const DELAY_MS = 300;

const TUR_MAP: Record<string, string> = {
  SAY: "SAYISAL",
  EA: "EŞİT AĞIRLIK",
  SÖZ: "SÖZEL",
  DİL: "DİL",
  TYT: "TYT",
};

type ApiRecord = {
  kilavuzKodu: number;
  birimAdi: string;
  universiteAdi: string;
  universiteTuru: string | null;
  ilAdi: string | null;
  fymkAdi: string | null;
  puanTuru: string;
  ogrenimSuresi: number | null;
  birimTuruId: number;
  basariSirasi?: number | null;
  minPuan?: number | null;
  kontenjan?: number | null;
  gkY?: number | null;
  sgyY?: number | null;
  dprmY?: number | null;
  obkY?: number | null;
};

type ApiPage = {
  content: ApiRecord[];
  totalPages: number;
  totalElements: number;
};

const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));

async function fetchPage(page: number): Promise<ApiPage> {
  const res = await fetch(API, {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({
      filters: {
        puanTuru: null,
        universiteId: [],
        birimGrupId: [],
        ilKodu: [],
        birimTuruId: null,
        universiteTuru: null,
        bursOraniId: null,
        ogrenimTuruId: null,
        kilavuzKodu: null,
        minBasariSirasi: null,
        maxBasariSirasi: null,
      },
      page,
      size: PAGE_SIZE,
      sortBy: "kilavuzKodu",
      direction: "ASC",
    }),
  });
  if (!res.ok) {
    throw new Error(`API ${res.status} (sayfa ${page})`);
  }
  return (await res.json()) as ApiPage;
}

function yerlesen(r: ApiRecord): number | null {
  const parts = [r.gkY, r.sgyY, r.dprmY, r.obkY].filter(
    (x): x is number => typeof x === "number"
  );
  return parts.length ? parts.reduce((a, b) => a + b, 0) : null;
}

function intOrNull(v: unknown): number | null {
  const n = typeof v === "string" ? Number.parseFloat(v) : (v as number);
  return typeof n === "number" && Number.isFinite(n) && n > 0
    ? Math.round(n)
    : null;
}

function realOrNull(v: unknown): number | null {
  const n = typeof v === "string" ? Number.parseFloat(v) : (v as number);
  return typeof n === "number" && Number.isFinite(n) && n > 0 ? n : null;
}

async function main() {
  const db = new Database(path.join(process.cwd(), "data", "yokatlas.db"));
  db.pragma("journal_mode = WAL");

  const cols = db.prepare("PRAGMA table_info(programs)").all() as {
    name: string;
  }[];
  const has2025 = cols.some((c) => c.name === "sira2025");
  if (!has2025) {
    db.exec(`
      ALTER TABLE programs ADD COLUMN sira2025 INTEGER;
      ALTER TABLE programs ADD COLUMN puan2025 REAL;
      ALTER TABLE programs ADD COLUMN kontenjan2025 INTEGER;
      ALTER TABLE programs ADD COLUMN yerlesen2025 INTEGER;
      CREATE INDEX IF NOT EXISTS idx_programs_tur_sira25 ON programs (tur, sira2025);
    `);
  }

  const update = db.prepare(`
    UPDATE programs SET sira2025 = ?, puan2025 = ?, kontenjan2025 = ?, yerlesen2025 = ?
    WHERE id = ?
  `);
  const insert = db.prepare(`
    INSERT INTO programs (id, isim, universite, unitur, il, fakulte, tur, sure, onlisans,
                          sira2025, puan2025, kontenjan2025, yerlesen2025)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);
  const exists = db.prepare("SELECT 1 FROM programs WHERE id = ?");

  let updated = 0;
  let inserted = 0;
  let skipped = 0;

  const first = await fetchPage(0);
  const totalPages = first.totalPages;
  console.log(
    `Toplam ${first.totalElements} kayıt, ${totalPages} sayfa çekilecek…`
  );

  const processPage = db.transaction((records: ApiRecord[]) => {
    for (const r of records) {
      const tur = TUR_MAP[r.puanTuru?.trim() ?? ""];
      if (!r.kilavuzKodu || !tur) {
        skipped++;
        continue;
      }
      const id = String(r.kilavuzKodu);
      const vals = [
        intOrNull(r.basariSirasi),
        realOrNull(r.minPuan),
        intOrNull(r.kontenjan),
        yerlesen(r),
      ];
      if (exists.get(id)) {
        update.run(...vals, id);
        updated++;
      } else {
        insert.run(
          id,
          r.birimAdi,
          r.universiteAdi,
          r.universiteTuru ?? null,
          r.ilAdi ?? null,
          r.fymkAdi ?? null,
          tur,
          r.ogrenimSuresi ?? null,
          r.birimTuruId === 47 ? 1 : 0,
          ...vals
        );
        inserted++;
      }
    }
  });

  processPage(first.content);
  for (let p = 1; p < totalPages; p++) {
    await sleep(DELAY_MS);
    const page = await fetchPage(p);
    processPage(page.content);
    process.stdout.write(`\rsayfa ${p + 1}/${totalPages}`);
  }
  console.log();

  const stats = db
    .prepare(
      `SELECT COUNT(*) AS toplam,
              SUM(CASE WHEN sira2025 IS NOT NULL THEN 1 ELSE 0 END) AS sirali
       FROM programs`
    )
    .get() as { toplam: number; sirali: number };
  console.log(
    `Güncellenen: ${updated}, eklenen: ${inserted}, atlanan: ${skipped}`
  );
  console.log(
    `DB toplam: ${stats.toplam} program, 2025 sıralaması olan: ${stats.sirali}`
  );
  db.close();
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});