route-commerce/scripts/import-tuxedo-stops.ts

// dotenv load temporarily disabled so shell-provided DATABASE_URL wins for this run
// import { config } from "dotenv";
// config({ path: ".env.local" });
import { Pool } from "pg";
import ExcelJS from "exceljs";
import * as fs from "fs";

const TUXEDO_BRAND_ID = "64294306-5f42-463d-a5e8-2ad6c81a96de";
const YEAR = 2026;

const MONTH_MAP: Record<string, string> = {
  Jan: "01", Feb: "02", Mar: "03", Apr: "04", May: "05", Jun: "06",
  Jul: "07", Aug: "08", Sep: "09", Oct: "10", Nov: "11", Dec: "12",
};

interface ParsedStop {
  week: string;
  region: string;
  date: string;
  day: string;
  city: string;
  state: string;
  location: string;
  time: string;
  timeRange: string;
  truck: string;
  statusText: string;
  notes: string;
  address: string | null;
  phone: string | null;
  contact: string | null;
}

function parseExcelDate(s: unknown): string | null {
  if (!s) return null;
  const m = /^([A-Za-z]{3})\s+(\d{1,2})$/.exec(String(s).trim());
  if (!m) return null;
  const mm = MONTH_MAP[m[1]];
  if (!mm) return null;
  return `${YEAR}-${mm}-${parseInt(m[2], 10).toString().padStart(2, "0")}`;
}

function parseTimeRange(s: unknown): string {
  if (!s) return "";
  let c = String(s).replace(/[–—]/g, "-").replace(/\s+/g, " ").trim();
  const m = /^(\d{1,2}:\d{2}\s*[AP]M)/i.exec(c);
  // Prefer full range for display (e.g. "10:00 AM – 1:00 PM"); fall back to start only
  return c || (m ? m[1].toUpperCase().replace(" ", " ") : "");
}

function splitCityState(s: unknown): [string, string] {
  if (!s) return ["", ""];
  const parts = String(s).split(",").map((p) => p.trim());
  if (parts.length === 1) return [parts[0], ""];
  return [parts[0], parts[1]];
}

function isWeekHeader(cells: string[]): boolean {
  return /^Wk\s/i.test(cells[0] || "") && (cells[3] || "").trim() === "";
}

function isOffRow(cells: string[]): boolean {
  const d = (cells[3] || "").trim();
  return /OFF|Cross-?Dock/i.test(d);
}

function isDataRow(cells: string[]): boolean {
  const day = (cells[3] || "").trim();
  const city = (cells[4] || "").trim();
  return !!(day && city && city.includes(","));
}

function slugify(s: string): string {
  let out = (s || "").toLowerCase();
  out = out.replace(/[^a-z0-9]+/g, "-");
  return out.replace(/^-+|-+$/g, "");
}

async function loadStops(xlsxPath: string): Promise<{ stops: ParsedStop[]; skipped: Record<string, number>; dirCount: number; locations: ParsedLocation[] }> {
  const wb = new ExcelJS.Workbook();
  await wb.xlsx.readFile(xlsxPath);

  const schedule = wb.getWorksheet("Full Schedule");
  const directory = wb.getWorksheet("Stop Directory");
  if (!schedule) throw new Error("Missing 'Full Schedule' sheet");

  // Build directory lookup for address enrichment: "T1|host lower" -> info
  const dirMap = new Map<string, { address: string | null; phone: string | null; contact: string | null; state: string }>();
  if (directory) {
    for (let r = 2; r <= directory.rowCount; r++) {
      const row = directory.getRow(r);
      const truck = String(row.getCell(1).value || "").trim();
      const host = String(row.getCell(4).value || "").trim().toLowerCase();
      if (!truck || !host) continue;
      dirMap.set(`${truck}|${host}`, {
        address: String(row.getCell(5).value || "").trim() || null,
        phone: String(row.getCell(6).value || "").trim() || null,
        contact: String(row.getCell(7).value || "").trim() || null,
        state: String(row.getCell(3).value || "").trim(),
      });
    }
  }

  const stops: ParsedStop[] = [];
  const skipped: Record<string, number> = { weekHeader: 0, off: 0, invalid: 0, noDate: 0 };

  for (let r = 4; r <= schedule.rowCount; r++) {
    const row = schedule.getRow(r);
    const cells = Array.from({ length: 10 }, (_, i) => String(row.getCell(i + 1).value ?? "").trim());

    if (isWeekHeader(cells)) {
      skipped.weekHeader++;
      continue;
    }
    if (isOffRow(cells)) {
      skipped.off++;
      continue;
    }
    if (!isDataRow(cells)) {
      skipped.invalid++;
      continue;
    }

    const [wk, region, dateText, day, cityState, host, time, truck, status, notes] = cells;
    const dateIso = parseExcelDate(dateText);
    if (!dateIso) {
      skipped.noDate++;
      continue;
    }
    const [city, state] = splitCityState(cityState);
    if (!city) {
      skipped.invalid++;
      continue;
    }

    const key = `${truck}|${host.toLowerCase()}`;
    const d = dirMap.get(key);

    stops.push({
      week: wk,
      region,
      date: dateIso,
      day,
      city,
      state: state || (d?.state || ""),
      location: host,
      time: parseTimeRange(time),
      timeRange: time,
      truck,
      statusText: status,
      notes: notes || "",
      address: d?.address || null,
      phone: d?.phone || null,
      contact: d?.contact || null,
    });
  }

  return { stops, skipped, dirCount: dirMap.size, locations: loadLocations(directory) };
}

export type ParsedLocation = {
  name: string;
  address: string | null;
  city: string;
  state: string;
  phone: string | null;
  contactName: string | null;
  notes: string | null;
};

function loadLocations(directory: ExcelJS.Worksheet | undefined): ParsedLocation[] {
  if (!directory) return [];
  const byKey = new Map<string, ParsedLocation>();
  for (let r = 2; r <= directory.rowCount; r++) {
    const row = directory.getRow(r);
    const truck = String(row.getCell(1).value || "").trim();
    const city = String(row.getCell(2).value || "").trim();
    const state = String(row.getCell(3).value || "").trim();
    const host = String(row.getCell(4).value || "").trim();
    const address = String(row.getCell(5).value || "").trim() || null;
    let phone = String(row.getCell(6).value || "").trim() || null;
    const contact = String(row.getCell(7).value || "").trim() || null;
    // notes column is 12
    let notes = String(row.getCell(12).value || "").trim() || null;

    if (!host || !city) continue;

    // If phone field contains TBD email, move it to notes
    if (phone && /@/.test(phone) && (!notes || notes.length < 10)) {
      notes = phone;
      phone = null;
    }

    const key = `${host.toLowerCase()}|${city.toLowerCase()}|${state.toLowerCase()}`;
    if (!byKey.has(key)) {
      byKey.set(key, {
        name: host,
        address,
        city,
        state,
        phone,
        contactName: contact || null,
        notes,
      });
    }
  }
  return Array.from(byKey.values());
}

function toLocationRpcRow(l: ParsedLocation) {
  return {
    name: l.name,
    address: l.address,
    city: l.city,
    state: l.state,
    zip: null,
    phone: l.phone,
    contact_name: l.contactName,
    contact_email: null,
    notes: l.notes,
    active: true,
  };
}

function toRpcRow(s: ParsedStop) {
  // Date format matches the python seed script expectation for the RPC
  const dateWithTz = `${s.date} 00:00:00+00`;
  const combinedNotes = [
    s.notes,
    s.truck ? `Truck: ${s.truck}` : "",
    s.statusText ? `Status: ${s.statusText}` : "",
    s.phone ? `Phone: ${s.phone}` : "",
    s.contact ? `Contact: ${s.contact}` : "",
  ]
    .filter(Boolean)
    .join(" | ");

  return {
    city: s.city,
    state: s.state,
    location: s.location,
    date: dateWithTz,
    time: s.time || s.timeRange || "",
    address: s.address,
    zip: null,
    cutoff_time: null,
    // active=true makes them visible on the public /tuxedo/stops storefront immediately
    active: true,
    // forward notes when the RPC supports it (enriched with truck/status/contact)
    notes: combinedNotes || null,
  };
}

async function main() {
  const args = process.argv.slice(2);
  const dryRun = args.includes("--dry-run") || args.includes("-n");
  let xlsxPath = "./Tuxedo_Corn_2026_Tour_Schedule-3.xlsx";
  const xArg = args.findIndex((a) => a === "--xlsx" || a === "-x");
  if (xArg !== -1 && args[xArg + 1]) xlsxPath = args[xArg + 1];
  const doClean = !args.includes("--no-clean");
  const emitSql = args.includes("--emit-sql") || args.includes("--sql");

  console.log(`[import-tuxedo-stops] xlsx=${xlsxPath} dryRun=${dryRun} clean=${doClean} emitSql=${emitSql}`);

  const { stops, skipped, dirCount, locations: parsedLocations } = await loadStops(xlsxPath);

  console.log(`\nParsed ${stops.length} stops + ${parsedLocations.length} unique locations`);
  console.log(`Skipped: week-headers=${skipped.weekHeader}, off/cross-dock=${skipped.off}, invalid=${skipped.invalid}, no-date=${skipped.noDate}`);
  console.log(`Stop Directory raw entries: ${dirCount}\n`);

  if (!stops.length) {
    console.error("No stops to insert. Check the xlsx path and filters.");
    process.exit(1);
  }

  // Show samples
  console.log("Sample (first 3):");
  stops.slice(0, 3).forEach((s) => {
    console.log(`  ${s.date} ${s.time.padEnd(10)} | ${s.city}, ${s.state} | ${s.location.slice(0, 32).padEnd(32)} | ${s.truck} | ${s.statusText}`);
    if (s.address) console.log(`    addr: ${s.address}`);
  });
  console.log("\nSample (last 3):");
  stops.slice(-3).forEach((s) => {
    console.log(`  ${s.date} ${s.time.padEnd(10)} | ${s.city}, ${s.state} | ${s.location.slice(0, 32).padEnd(32)} | ${s.truck} | ${s.statusText}`);
  });

  // By week/region/truck
  const byWeek: Record<string, number> = {};
  const byRegion: Record<string, number> = {};
  const byTruck: Record<string, number> = {};
  for (const s of stops) {
    byWeek[s.week] = (byWeek[s.week] || 0) + 1;
    byRegion[s.region] = (byRegion[s.region] || 0) + 1;
    byTruck[s.truck] = (byTruck[s.truck] || 0) + 1;
  }
  console.log("\nBy week:", Object.fromEntries(Object.entries(byWeek).sort(([a], [b]) => Number(a) - Number(b))));
  console.log("By region:", byRegion);
  console.log("By truck:", byTruck);

  const dates = [...stops.map((s) => s.date)].sort();
  console.log(`\nDate range: ${dates[0]} → ${dates[dates.length - 1]}`);

  if (emitSql) {
    const BATCH = 50;
    let sql = `-- Tuxedo Corn 2026 Tour Data (from ${xlsxPath})\n`;
    sql += `-- Stops: ${stops.length} | Locations: ${parsedLocations.length}\n`;
    sql += `-- Preferred: npx tsx scripts/import-tuxedo-stops.ts\n`;
    sql += `-- Manual apply: psql "$DATABASE_URL" -f db/seeds/2026-tuxedo-tour-stops.sql\n\n`;
    sql += `BEGIN;\n\n`;

    // Locations first (master directory)
    if (parsedLocations.length > 0) {
      sql += `-- ===== LOCATIONS (Stop Directory master records) =====\n`;
      sql += `DELETE FROM locations WHERE brand_id = '${TUXEDO_BRAND_ID}';\n\n`;
      const locPayload = parsedLocations.map(toLocationRpcRow);
      const locJson = JSON.stringify(locPayload);
      sql += `-- ${parsedLocations.length} unique locations from Stop Directory\n`;
      sql += `SELECT admin_create_locations_batch('${TUXEDO_BRAND_ID}'::uuid, $$${locJson}$$::jsonb);\n\n`;
    }

    // Stops (dated instances)
    sql += `-- ===== STOPS (2026 tour schedule) =====\n`;
    sql += `-- Clear prior 2026 tour stops for brand (date-scoped)\n`;
    sql += `DELETE FROM stops WHERE brand_id = '${TUXEDO_BRAND_ID}' AND date >= '2026-07-01' AND date <= '2026-09-30';\n\n`;
    for (let i = 0; i < stops.length; i += BATCH) {
      const batchStops = stops.slice(i, i + BATCH);
      const payload = batchStops.map(toRpcRow);
      const payloadJson = JSON.stringify(payload);
      const bnum = Math.floor(i / BATCH) + 1;
      sql += `-- stops batch ${bnum}/${Math.ceil(stops.length / BATCH)} (${payload.length})\n`;
      sql += `SELECT admin_create_stops_batch('${TUXEDO_BRAND_ID}'::uuid, $$${payloadJson}$$::jsonb);\n\n`;
    }
    sql += `-- Make stops visible (RPC inserts as draft + active=true in payload)\n`;
    sql += `UPDATE stops SET status = 'active' WHERE brand_id = '${TUXEDO_BRAND_ID}' AND date >= '2026-07-01' AND date <= '2026-09-30';\n\n`;
    sql += `COMMIT;\n`;
    const outPath = "db/seeds/2026-tuxedo-tour-stops.sql";
    fs.writeFileSync(outPath, sql, "utf8");
    console.log(`\nWrote ${outPath} (locations: ${parsedLocations.length}, stops: ${stops.length}).`);
  }

  if (dryRun) {
    const stopBatches = Math.ceil(stops.length / 50);
    console.log(`\n[DRY RUN] Would load for brand:\n  - ${parsedLocations.length} locations (full replace)\n  - DELETE date-scoped 2026 stops then INSERT ${stops.length} stops in ${stopBatches} batch(es)\n  via admin_*_batch RPCs.`);
    return;
  }

  const pool = new Pool({
    connectionString:
      process.env.DATABASE_ADMIN_URL ??
      process.env.DATABASE_URL ??
      "postgres://postgres:postgres@localhost:5432/route_commerce",
  });

  const client = await pool.connect();
  try {
    await client.query("BEGIN");

    if (doClean) {
      const del = await client.query(
        `DELETE FROM stops WHERE brand_id = $1 AND date >= '2026-07-01' AND date <= '2026-09-30'`,
        [TUXEDO_BRAND_ID],
      );
      console.log(`\nCleared ${del.rowCount} prior 2026-dated stops for Tuxedo brand.`);
    }

    // Locations (from Stop Directory) — full replace for the brand from this xlsx
    if (parsedLocations.length > 0) {
      const locDel = await client.query(
        `DELETE FROM locations WHERE brand_id = $1`,
        [TUXEDO_BRAND_ID],
      );
      console.log(`Cleared ${locDel.rowCount} previous locations for Tuxedo brand.`);

      const LOC_BATCH = 100; // locations are fewer and smaller
      let locTotal = 0;
      const locBatches = Math.ceil(parsedLocations.length / LOC_BATCH);
      for (let i = 0; i < parsedLocations.length; i += LOC_BATCH) {
        const batchLocs = parsedLocations.slice(i, i + LOC_BATCH);
        const payload = batchLocs.map(toLocationRpcRow);
        const bnum = Math.floor(i / LOC_BATCH) + 1;
        process.stdout.write(`  Locations batch ${bnum}/${locBatches} (${payload.length}) via admin_create_locations_batch... `);
        try {
          await client.query(
            "SELECT admin_create_locations_batch($1::uuid, $2::jsonb)",
            [TUXEDO_BRAND_ID, JSON.stringify(payload)],
          );
          locTotal += payload.length;
          console.log("OK");
        } catch (e: any) {
          console.log("FAIL");
          throw e;
        }
      }
      console.log(`  Inserted ${locTotal} locations.`);
    }

    const BATCH = 50;
    let total = 0;
    const batches = Math.ceil(stops.length / BATCH);

    for (let i = 0; i < stops.length; i += BATCH) {
      const batchStops = stops.slice(i, i + BATCH);
      const payload = batchStops.map(toRpcRow);
      const bnum = Math.floor(i / BATCH) + 1;

      process.stdout.write(`  Batch ${bnum}/${batches} (${payload.length}) via admin_create_stops_batch... `);
      try {
        await client.query(
          "SELECT admin_create_stops_batch($1::uuid, $2::jsonb)",
          [TUXEDO_BRAND_ID, JSON.stringify(payload)],
        );
        total += payload.length;
        console.log("OK");
      } catch (e: any) {
        console.log("FAIL");
        throw e;
      }
    }

    // RPC inserts with status='draft' (per prior script); flip to active for public visibility.
    // Scope by the tour date range so we only touch rows from this import.
    if (total > 0) {
      const pub = await client.query(
        `UPDATE stops SET status = 'active' WHERE brand_id = $1 AND date >= '2026-07-01' AND date <= '2026-09-30'`,
        [TUXEDO_BRAND_ID],
      );
      console.log(`\nPublished (status=active): ${pub.rowCount} stops.`);
    }

    await client.query("COMMIT");
    console.log(`\nDone. Inserted ${parsedLocations.length} locations + ${total} stops for Tuxedo Corn 2026 tour (brand ${TUXEDO_BRAND_ID}).`);
    console.log("Locations are the master directory; stops are the dated schedule instances.");
    console.log("They should now appear on /tuxedo/stops and in admin locations UI.");
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("\nInsert failed (rolled back):", err);
    process.exitCode = 1;
  } finally {
    client.release();
    await pool.end();
  }
}

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