import { query } from "./db";

export type PincodeOffice = {
  pincode: string;
  office_name: string;
  district: string;
  state: string;
  office_type: string | null;
};

export type CityRow = {
  city: string;
  state: string;
};

export async function lookupPincode(pincode: string) {
  const pin = pincode.replace(/\D/g, "").slice(0, 6);
  if (pin.length !== 6) return [];

  const rows = await query<PincodeOffice[]>(
    `SELECT pincode, office_name, district, state, office_type
     FROM india_pincodes
     WHERE pincode = :pin
     ORDER BY
       CASE WHEN delivery = 'Delivery' THEN 0 ELSE 1 END,
       office_name ASC
     LIMIT 40`,
    { pin },
  );
  return rows as unknown as PincodeOffice[];
}

export async function searchCities(q: string, state?: string) {
  const term = q.trim();
  if (term.length < 2) return [];

  const params: Record<string, unknown> = {
    q: `${term}%`,
    qlike: `%${term}%`,
  };
  let stateSql = "";
  if (state) {
    stateSql = "AND state = :state";
    params.state = state;
  }

  const rows = await query<CityRow[]>(
    `SELECT city, state
     FROM india_cities
     WHERE (city LIKE :q OR city LIKE :qlike)
     ${stateSql}
     ORDER BY
       CASE WHEN city LIKE :q THEN 0 ELSE 1 END,
       city ASC
     LIMIT 20`,
    params,
  );
  return rows as unknown as CityRow[];
}

export async function districtsForState(state: string) {
  if (!state) return [];
  const rows = await query<{ district: string }[]>(
    `SELECT DISTINCT district
     FROM india_pincodes
     WHERE state = :state
     ORDER BY district ASC
     LIMIT 200`,
    { state },
  );
  return (rows as unknown as { district: string }[]).map((r) => r.district);
}