jws-backend/src/utils/thailand-area.ts

import { parseFile } from "@fast-csv/parse";

import prisma from "../db";

export async function initThailandAreaDatabase() {
  const data = await new Promise<Record<string, string>[]>((resolve, reject) => {
    const r: Record<string, string>[] = [];
    const stream = parseFile("./assets/thailand-area.csv", { headers: true });
    stream.on("data", (v) => r.push(v));
    stream.on("error", (e) => reject(e));
    stream.on("end", () => resolve(r));
  }).catch((e) => console.error(e));

  if (!data) return;

  type Base = { id: string; name: string; nameEN: string };

  const province: Base[] = [];
  const district: (Base & { provinceId: string })[] = [];
  const subDistrict: (Base & { zipCode: string; districtId: string })[] = [];

  data.forEach((record) => {
    const provinceId = record["TambonID"].slice(0, 2);
    const districtId = record["TambonID"].slice(0, 4);
    const subDistrictId = record["TambonID"];

    if (!province.find((v) => v.id === provinceId)) {
      province.push({
        id: provinceId,
        name: record["ProvinceThai"],
        nameEN: record["ProvinceEng"],
      });
    }
    if (!district.find((v) => v.id === districtId)) {
      district.push({
        id: districtId,
        name: record["DistrictThaiShort"],
        nameEN: record["DistrictEngShort"],
        provinceId,
      });
    }
    if (!subDistrict.find((v) => v.id === subDistrictId)) {
      subDistrict.push({
        id: subDistrictId,
        name: record["TambonThaiShort"],
        nameEN: record["TambonEngShort"],
        zipCode: record["PostCodeMain"],
        districtId,
      });
    }
  });

  function splitChunk<DataType, ResponseType>(
    array: DataType[],
    chunkSize: number,
    callback: (data: DataType[]) => ResponseType,
  ) {
    const result: ReturnType<typeof callback>[] = [];
    for (let i = 0; i < array.length; i += chunkSize) {
      result.push(callback(array.slice(i, i + chunkSize)));
    }
    return result;
  }

  await prisma.$transaction(async (tx) => {
    const meta = {
      createdBy: null,
      createdAt: new Date(),
      updatedBy: null,
      updatedAt: new Date(),
    };

    await Promise.all(
      splitChunk(province, 1000, async (r) => {
        return await tx.$kysely
          .insertInto("Province")
          .columns(["id", "name", "nameEN", "createdBy", "createdAt", "updatedBy", "updatedAt"])
          .values(r.map((v) => ({ ...v, ...meta })))
          .onConflict((oc) =>
            oc.column("id").doUpdateSet({
              name: (eb) => eb.ref("excluded.name"),
              nameEN: (eb) => eb.ref("excluded.nameEN"),
              updatedAt: (eb) => eb.ref("excluded.updatedAt"),
            }),
          )
          .execute();
      }),
    );

    await Promise.all(
      splitChunk(district, 2000, async (r) => {
        return await tx.$kysely
          .insertInto("District")
          .columns([
            "id",
            "name",
            "nameEN",
            "provinceId",
            "createdBy",
            "createdAt",
            "updatedBy",
            "updatedAt",
          ])
          .values(r.map((v) => ({ ...v, ...meta })))
          .onConflict((oc) =>
            oc.column("id").doUpdateSet({
              name: (eb) => eb.ref("excluded.name"),
              nameEN: (eb) => eb.ref("excluded.nameEN"),
              provinceId: (eb) => eb.ref("excluded.provinceId"),
              updatedAt: (eb) => eb.ref("excluded.updatedAt"),
            }),
          )
          .execute();
      }),
    );

    await Promise.all(
      splitChunk(subDistrict, 1000, async (r) => {
        return await tx.$kysely
          .insertInto("SubDistrict")
          .columns([
            "id",
            "name",
            "nameEN",
            "districtId",
            "createdBy",
            "createdAt",
            "updatedBy",
            "updatedAt",
          ])
          .values(r.map((v) => ({ ...v, ...meta })))
          .onConflict((oc) =>
            oc.column("id").doUpdateSet({
              name: (eb) => eb.ref("excluded.name"),
              nameEN: (eb) => eb.ref("excluded.nameEN"),
              districtId: (eb) => eb.ref("excluded.districtId"),
              updatedAt: (eb) => eb.ref("excluded.updatedAt"),
            }),
          )
          .execute();
      }),
    );
  });

  console.log("[INFO]: Sync thailand province, district and subdistrict, OK.");
}

export async function initEmploymentOffice() {
  const name = (provinceName: string) => `สำนักงานจัดหางานจังหวัด${provinceName}`;
  const nameEN = (provinceNameEN: string) => `${provinceNameEN} Employment Office`;
  const nameSpecial = (provinceName: string, areaNo: number) =>
    `สำนักงานจัดหางาน${provinceName} พื้นที่ ${areaNo}`;
  const nameSpecialEN = (provinceNameEN: string, areaNo: number) =>
    `${provinceNameEN} Employment Office Area ${areaNo}`;

  const special: Record<string, { [key: string]: string[] }> = {
    "10": {
      "1": ["1004", "1007", "1012", "1028", "1031"],
      "2": ["1021", "1024", "1035", "1049", "1050"],
      "3": ["1009", "1032", "1033", "1034", "1039", "1047"],
      "4": ["1006", "1038", "1043", "1027", "1045"],
      "5": ["1003", "1010", "1011", "1042", "1044", "1046"],
      "6": ["1015", "1016", "1018", "1020", "1025"],
      "7": ["1019", "1022", "1023", "1040", "1048"],
      "8": ["1001", "1002", "1008", "1013"],
      "9": ["1005", "1029", "1030", "1041"],
      "10": ["1014", "1017", "1026", "1036", "1037"],
    },
  };

  const list = await prisma.province.findMany();

  await prisma.$transaction(async (tx) => {
    await Promise.all(
      list
        .map(async (province) => {
          if (special[province.id]) {
            await tx.employmentOffice.deleteMany({
              where: { provinceId: province.id, district: { none: {} } },
            });
            return await Promise.all(
              Object.entries(special[province.id]).map(async ([key, val]) => {
                const id = province.id + "-" + key.padStart(2, "0");
                return tx.employmentOffice.upsert({
                  where: { id },
                  create: {
                    id,
                    name: nameSpecial(province.name, +key),
                    nameEN: nameSpecialEN(province.nameEN, +key),
                    provinceId: province.id,
                    district: {
                      createMany: {
                        data: val.map((districtId) => ({ districtId })),
                        skipDuplicates: true,
                      },
                    },
                  },
                  update: {
                    id,
                    name: nameSpecial(province.name, +key),
                    nameEN: nameSpecialEN(province.nameEN, +key),
                    provinceId: province.id,
                    district: {
                      deleteMany: { districtId: { notIn: val } },
                      createMany: {
                        data: val.map((districtId) => ({ districtId })),
                        skipDuplicates: true,
                      },
                    },
                  },
                });
              }),
            );
          }

          return tx.employmentOffice.upsert({
            where: { id: province.id },
            create: {
              id: province.id,
              name: name(province.name),
              nameEN: nameEN(province.nameEN),
              provinceId: province.id,
            },
            update: {
              name: name(province.name),
              nameEN: nameEN(province.nameEN),
              provinceId: province.id,
            },
          });
        })
        .flat(),
    );
  });

  console.log("[INFO]: Sync employment office, OK.");
}