const LEGACY_STORAGE_KEY = "wsda-pesticide-records";
const DB_NAME = "wsda-pesticide-records-db";
const DB_VERSION = 2;
const RECORD_STORE = "records";
const CUSTOMER_STORE = "customers";

const form = document.querySelector("#recordForm");
const recordList = document.querySelector("#records");
const pesticideRows = document.querySelector("#pesticideRows");
const pesticideTemplate = document.querySelector("#pesticideTemplate");
const pesticideProducts = document.querySelector("#pesticideProducts");
const printSheet = document.querySelector("#printSheet");
const storageStatus = document.querySelector("#storageStatus");
const recordSearch = document.querySelector("#recordSearch");
const customerPicker = document.querySelector("#customerPicker");
const savedCustomers = document.querySelector("#savedCustomers");
const customerSitePicker = document.querySelector("#customerSitePicker");
const weatherLookupEnabled = document.querySelector("#weatherLookupEnabled");
const weatherLookupPanel = document.querySelector("#weatherLookupPanel");
const weatherZip = document.querySelector("#weatherZip");
const weatherLat = document.querySelector("#weatherLat");
const weatherLon = document.querySelector("#weatherLon");
const weatherStatus = document.querySelector("#weatherStatus");

const newButton = document.querySelector("#newRecord");
const saveButton = document.querySelector("#saveRecord");
const printButton = document.querySelector("#printRecord");
const deleteButton = document.querySelector("#deleteRecord");
const addPesticideButton = document.querySelector("#addPesticide");
const saveCustomerButton = document.querySelector("#saveCustomer");
const useGpsWeatherButton = document.querySelector("#useGpsWeather");
const lookupWeatherButton = document.querySelector("#lookupWeather");

let db;
let records = [];
let customers = [];
let activeId = "";
let saveTimer;
let isFillingForm = false;
let storageMode = "local";

const POPULAR_PESTICIDES = [
  { name: "Roundup PRO Concentrate Herbicide", epaRegNo: "93236-6", type: "Herbicide" },
  { name: "Ranger Pro Herbicide", epaRegNo: "524-517", type: "Herbicide" },
  { name: "Crossbow", epaRegNo: "62719-260", type: "Herbicide" },
  { name: "Garlon 4 Ultra Specialty Herbicide", epaRegNo: "62719-527", type: "Herbicide" },
  { name: "Surflan AS Specialty Herbicide", epaRegNo: "70506-44", type: "Herbicide" },
  { name: "Barricade 4FL", epaRegNo: "100-1139", type: "Herbicide" },
  { name: "SpeedZone EW Broadleaf Herbicide for Turf", epaRegNo: "2217-1053", type: "Herbicide" },
  { name: "Talstar P Professional Insecticide", epaRegNo: "279-3206", type: "Insecticide" },
  { name: "Tempo SC Ultra Premise Spray", epaRegNo: "11556-124", type: "Insecticide" }
];

const pesticideLookup = new Map(
  POPULAR_PESTICIDES.flatMap((product) => [
    [product.name.toLowerCase(), product],
    [product.epaRegNo.toLowerCase(), product]
  ])
);

function openDatabase() {
  return new Promise((resolve, reject) => {
    if (!("indexedDB" in window)) {
      reject(new Error("This browser does not support IndexedDB."));
      return;
    }

    const request = indexedDB.open(DB_NAME, DB_VERSION);

    request.onupgradeneeded = () => {
      const database = request.result;
      if (!database.objectStoreNames.contains(RECORD_STORE)) {
        const store = database.createObjectStore(RECORD_STORE, { keyPath: "id" });
        store.createIndex("applicationDate", "applicationDate", { unique: false });
        store.createIndex("updatedAt", "updatedAt", { unique: false });
      }
      if (!database.objectStoreNames.contains(CUSTOMER_STORE)) {
        const store = database.createObjectStore(CUSTOMER_STORE, { keyPath: "id" });
        store.createIndex("name", "name", { unique: false });
      }
    };

    request.onsuccess = () => resolve(request.result);
    request.onerror = () => reject(request.error);
  });
}

function dbStore(storeName, mode = "readonly") {
  return db.transaction(storeName, mode).objectStore(storeName);
}

async function apiRequest(path, options = {}) {
  const response = await fetch(`api/${path}`, {
    headers: {
      "Content-Type": "application/json",
      ...(options.headers || {})
    },
    ...options
  });

  if (!response.ok) {
    const error = await response.json().catch(() => ({}));
    throw new Error(error.error || "Server database request failed.");
  }

  return response.json();
}

async function detectServerStorage() {
  try {
    await apiRequest("records.php");
    storageMode = "server";
    return true;
  } catch {
    storageMode = "local";
    return false;
  }
}

function getAllRecordsLocal() {
  return new Promise((resolve, reject) => {
    const request = dbStore(RECORD_STORE).getAll();
    request.onsuccess = () => resolve(request.result || []);
    request.onerror = () => reject(request.error);
  });
}

async function getAllRecords() {
  if (storageMode === "server") return apiRequest("records.php");
  return getAllRecordsLocal();
}

function putRecordLocal(record) {
  return new Promise((resolve, reject) => {
    const request = dbStore(RECORD_STORE, "readwrite").put(record);
    request.onsuccess = () => resolve(record);
    request.onerror = () => reject(request.error);
  });
}

async function putRecord(record) {
  if (storageMode === "server") {
    return apiRequest("records.php", {
      method: "POST",
      body: JSON.stringify(record)
    });
  }
  return putRecordLocal(record);
}

function removeRecordLocal(id) {
  return new Promise((resolve, reject) => {
    const request = dbStore(RECORD_STORE, "readwrite").delete(id);
    request.onsuccess = () => resolve();
    request.onerror = () => reject(request.error);
  });
}

async function removeRecord(id) {
  if (storageMode === "server") {
    return apiRequest(`records.php?id=${encodeURIComponent(id)}`, { method: "DELETE" });
  }
  return removeRecordLocal(id);
}

function getAllCustomersLocal() {
  return new Promise((resolve, reject) => {
    const request = dbStore(CUSTOMER_STORE).getAll();
    request.onsuccess = () => resolve(request.result || []);
    request.onerror = () => reject(request.error);
  });
}

async function getAllCustomers() {
  if (storageMode === "server") return apiRequest("customers.php");
  return getAllCustomersLocal();
}

function putCustomerLocal(customer) {
  return new Promise((resolve, reject) => {
    const request = dbStore(CUSTOMER_STORE, "readwrite").put(customer);
    request.onsuccess = () => resolve(customer);
    request.onerror = () => reject(request.error);
  });
}

async function putCustomer(customer) {
  if (storageMode === "server") {
    return apiRequest("customers.php", {
      method: "POST",
      body: JSON.stringify(customer)
    });
  }
  return putCustomerLocal(customer);
}

function setStatus(message) {
  storageStatus.textContent = message;
}

function sortRecords(items) {
  return [...items].sort((a, b) => {
    const customerCompare = (a.customerName || "").localeCompare(b.customerName || "", undefined, {
      sensitivity: "base"
    });
    if (customerCompare !== 0) return customerCompare;

    const nameCompare = (a.recordName || "").localeCompare(b.recordName || "", undefined, {
      sensitivity: "base"
    });
    if (nameCompare !== 0) return nameCompare;

    const dateA = a.applicationDate || a.updatedAt || a.createdAt || "";
    const dateB = b.applicationDate || b.updatedAt || b.createdAt || "";
    return dateB.localeCompare(dateA);
  });
}

function newBlankRecord() {
  return {
    id: crypto.randomUUID(),
    createdAt: new Date().toISOString(),
    updatedAt: new Date().toISOString(),
    applicationDate: new Date().toISOString().slice(0, 10),
    method: "Ground",
    pesticides: [{}]
  };
}

async function createRecord() {
  const record = newBlankRecord();
  records.unshift(record);
  activeId = record.id;
  await putRecord(record);
  fillForm(record);
  renderRecordList();
  setStatus("New record ready.");
}

function activeRecord() {
  return records.find((record) => record.id === activeId);
}

function setValue(name, value = "") {
  const field = form.elements[name];
  if (field) field.value = value || "";
}

function customerLabel(customer) {
  return customer.name || customer.firm || "Unnamed customer";
}

function siteLabel(site) {
  return [
    site.street,
    site.city,
    site.state,
    site.zip
  ].filter(Boolean).join(", ") || "Saved address";
}

function currentCustomerSite() {
  return {
    street: form.elements.customerStreet.value.trim(),
    city: form.elements.customerCity.value.trim(),
    state: form.elements.customerState.value.trim(),
    zip: form.elements.customerZip.value.trim(),
    location: form.elements.location.value.trim()
  };
}

function sameSite(a, b) {
  return ["street", "city", "state", "zip", "location"].every((key) => {
    return String(a[key] || "").trim().toLowerCase() === String(b[key] || "").trim().toLowerCase();
  });
}

function activeCustomerFromForm() {
  const name = form.elements.customerName.value.trim();
  const firm = form.elements.customerFirm.value.trim();
  if (!name && !firm) return null;

  const match = customers.find((customer) => {
    return customerLabel(customer).toLowerCase() === (name || firm).toLowerCase()
      || (customer.name || "").toLowerCase() === name.toLowerCase()
      || (customer.firm || "").toLowerCase() === firm.toLowerCase();
  });

  const site = currentCustomerSite();
  const sites = match?.sites ? [...match.sites] : [];
  if ((site.street || site.city || site.zip || site.location) && !sites.some((savedSite) => sameSite(savedSite, site))) {
    sites.push(site);
  }

  return {
    id: match?.id || crypto.randomUUID(),
    name,
    firm,
    sites,
    updatedAt: new Date().toISOString(),
    createdAt: match?.createdAt || new Date().toISOString()
  };
}

function renderCustomerOptions() {
  savedCustomers.innerHTML = "";
  customers
    .slice()
    .sort((a, b) => customerLabel(a).localeCompare(customerLabel(b), undefined, { sensitivity: "base" }))
    .forEach((customer) => {
      const option = document.createElement("option");
      option.value = customerLabel(customer);
      option.label = customer.firm && customer.firm !== customer.name ? customer.firm : "";
      savedCustomers.appendChild(option);
    });
}

function renderCustomerSites(customer) {
  customerSitePicker.innerHTML = "";
  const placeholder = document.createElement("option");
  placeholder.value = "";
  placeholder.textContent = customer ? "Select saved site / address" : "Select after choosing customer";
  customerSitePicker.appendChild(placeholder);

  if (!customer) return;

  (customer.sites || []).forEach((site, index) => {
    const option = document.createElement("option");
    option.value = String(index);
    option.textContent = siteLabel(site);
    customerSitePicker.appendChild(option);
  });
}

function findCustomerByPickerValue(value) {
  const term = value.trim().toLowerCase();
  return customers.find((customer) => {
    return customerLabel(customer).toLowerCase() === term
      || (customer.name || "").toLowerCase() === term
      || (customer.firm || "").toLowerCase() === term;
  });
}

async function saveCustomerFromForm() {
  const customer = activeCustomerFromForm();
  if (!customer) {
    setStatus("Enter a customer name or firm first.");
    return;
  }

  await putCustomer(customer);
  customers = await getAllCustomers();
  renderCustomerOptions();
  customerPicker.value = customerLabel(customer);
  renderCustomerSites(customer);
  setStatus("Customer saved.");
}

function applyCustomer(customer) {
  if (!customer) return;

  form.elements.customerName.value = customer.name || "";
  form.elements.customerFirm.value = customer.firm || "";
  customerPicker.value = customerLabel(customer);
  renderCustomerSites(customer);

  const firstSite = customer.sites?.[0];
  if (firstSite) {
    applyCustomerSite(firstSite);
    customerSitePicker.value = "0";
  }

  scheduleSave();
}

function applyCustomerSite(site) {
  form.elements.customerStreet.value = site.street || "";
  form.elements.customerCity.value = site.city || "";
  form.elements.customerState.value = site.state || "";
  form.elements.customerZip.value = site.zip || "";
  if (site.location) form.elements.location.value = site.location;
}

function renderPesticideRows(pesticides = [{}]) {
  pesticideRows.innerHTML = "";
  const rows = pesticides.length ? pesticides : [{}];
  rows.forEach((pesticide) => addPesticideRow(pesticide));
}

function addPesticideRow(values = {}) {
  const fragment = pesticideTemplate.content.cloneNode(true);
  const card = fragment.querySelector(".pesticide-card");

  card.querySelectorAll("[data-field]").forEach((input) => {
    input.value = values[input.dataset.field] || "";
  });

  const productInput = card.querySelector('[data-field="productName"]');
  productInput.addEventListener("change", () => applyPesticideSelection(card));
  productInput.addEventListener("blur", () => applyPesticideSelection(card));

  card.querySelector(".remove-pesticide").addEventListener("click", () => {
    if (pesticideRows.children.length > 1) {
      card.remove();
      scheduleSave();
    }
  });

  card.addEventListener("input", scheduleSave);
  pesticideRows.appendChild(fragment);
}

function readPesticides() {
  return [...pesticideRows.querySelectorAll(".pesticide-card")].map((card) => {
    const pesticide = {};
    card.querySelectorAll("[data-field]").forEach((input) => {
      pesticide[input.dataset.field] = input.value.trim();
    });
    return pesticide;
  });
}

function readForm() {
  const data = Object.fromEntries(
    [...new FormData(form).entries()].map(([key, value]) => [key, String(value).trim()])
  );
  data.weatherLookupEnabled = weatherLookupEnabled.checked;
  data.pesticides = readPesticides();
  return data;
}

function fillForm(record) {
  isFillingForm = true;
  form.reset();
  [
    "recordName",
    "applicationDate",
    "method",
    "siteType",
    "areaTreated",
    "permitNumber",
    "location",
    "customerName",
    "customerFirm",
    "customerStreet",
    "customerCity",
    "customerState",
    "customerZip",
    "applicatorName",
    "applicatorLicense",
    "applicatorFirm",
    "applicatorPhone",
    "applicatorStreet",
    "applicatorCityStateZip",
    "startTime",
    "endTime",
    "temperature",
    "windDirection",
    "windVelocity",
    "equipment",
    "weatherZip",
    "weatherLat",
    "weatherLon",
    "notes"
  ].forEach((name) => setValue(name, record[name]));
  customerPicker.value = record.customerName || record.customerFirm || "";
  renderCustomerSites(findCustomerByPickerValue(customerPicker.value));
  weatherLookupEnabled.checked = Boolean(record.weatherLookupEnabled);
  syncWeatherPanel();
  renderPesticideRows(record.pesticides);
  isFillingForm = false;
}

async function saveActiveRecord({ quiet = false } = {}) {
  const record = activeRecord();
  if (!record || isFillingForm) return;

  Object.assign(record, readForm(), { updatedAt: new Date().toISOString() });
  await putRecord(record);
  const customer = activeCustomerFromForm();
  if (customer) {
    await putCustomer(customer);
    customers = await getAllCustomers();
    renderCustomerOptions();
  }
  records = sortRecords(records);
  renderRecordList();

  if (!quiet) setStatus(`Saved ${new Date().toLocaleTimeString([], { hour: "numeric", minute: "2-digit" })}.`);
}

function scheduleSave() {
  if (isFillingForm) return;
  window.clearTimeout(saveTimer);
  saveTimer = window.setTimeout(() => saveActiveRecord({ quiet: false }), 350);
}

function recordTitle(record) {
  return record.recordName || record.customerName || record.siteType || record.location || "Untitled record";
}

function customerGroup(record) {
  return record.customerName || "No Customer";
}

function searchableText(record) {
  const pesticideText = (record.pesticides || [])
    .flatMap((pesticide) => Object.values(pesticide || {}))
    .join(" ");
  return [
    record.applicationDate,
    record.recordName,
    record.method,
    record.siteType,
    record.location,
    record.customerName,
    record.customerFirm,
    record.applicatorName,
    pesticideText
  ].filter(Boolean).join(" ").toLowerCase();
}

function visibleRecords() {
  const term = recordSearch.value.trim().toLowerCase();
  if (!term) return records;
  return records.filter((record) => searchableText(record).includes(term));
}

function renderRecordList() {
  const items = visibleRecords();
  recordList.innerHTML = "";

  if (!items.length) {
    const empty = document.createElement("p");
    empty.className = "empty-state";
    empty.textContent = "No matching records.";
    recordList.appendChild(empty);
    return;
  }

  let currentGroup = "";

  items.forEach((record) => {
    const groupName = customerGroup(record);
    if (groupName !== currentGroup) {
      currentGroup = groupName;
      const group = document.createElement("div");
      group.className = "record-group";
      group.textContent = groupName;
      recordList.appendChild(group);
    }

    const item = document.createElement("div");
    item.className = `record-button${record.id === activeId ? " active" : ""}`;
    item.innerHTML = `
      <input class="record-name-input" type="text" aria-label="Record name">
      <button class="record-open-button" type="button"><span></span></button>
    `;

    const nameInput = item.querySelector(".record-name-input");
    const openButton = item.querySelector(".record-open-button");

    nameInput.value = recordTitle(record);
    openButton.querySelector("span").textContent = [record.applicationDate, record.method, record.siteType]
      .filter(Boolean)
      .join(" | ");

    const saveRecordName = async () => {
      const nextName = nameInput.value.trim();
      record.recordName = nextName;
      if (record.id === activeId) {
        form.elements.recordName.value = nextName;
      }
      await putRecord({ ...record, updatedAt: new Date().toISOString() });
      setStatus("Record name saved.");
    };

    nameInput.addEventListener("change", saveRecordName);
    nameInput.addEventListener("keydown", (event) => {
      if (event.key === "Enter") {
        event.preventDefault();
        nameInput.blur();
      }
    });

    openButton.addEventListener("click", async () => {
      await saveActiveRecord({ quiet: true });
      activeId = record.id;
      fillForm(record);
      renderRecordList();
      setStatus("Record loaded.");
    });

    recordList.appendChild(item);
  });
}

function populatePesticideOptions() {
  pesticideProducts.innerHTML = "";
  POPULAR_PESTICIDES.forEach((product) => {
    const option = document.createElement("option");
    option.value = product.name;
    option.label = `${product.epaRegNo} | ${product.type}`;
    pesticideProducts.appendChild(option);
  });
}

function applyPesticideSelection(card) {
  const productInput = card.querySelector('[data-field="productName"]');
  const epaInput = card.querySelector('[data-field="epaNumber"]');
  const selected = pesticideLookup.get(productInput.value.trim().toLowerCase());

  if (selected) {
    productInput.value = selected.name;
    epaInput.value = selected.epaRegNo;
    scheduleSave();
  }
}

function syncWeatherPanel() {
  weatherLookupPanel.hidden = !weatherLookupEnabled.checked;
}

function setWeatherStatus(message) {
  weatherStatus.textContent = message;
}

function windDirectionName(degrees) {
  const directions = ["N", "NE", "E", "SE", "S", "SW", "W", "NW"];
  const index = Math.round(Number(degrees) / 45) % 8;
  return directions[index] || "";
}

function selectedWeatherTime() {
  const date = form.elements.applicationDate.value || new Date().toISOString().slice(0, 10);
  const time = form.elements.startTime.value || form.elements.endTime.value || "12:00";
  return { date, hour: `${date}T${time.slice(0, 2)}:00` };
}

function currentTimeValue() {
  const now = new Date();
  return `${String(now.getHours()).padStart(2, "0")}:${String(now.getMinutes()).padStart(2, "0")}`;
}

function nearestHourlyIndex(times, targetHour) {
  let bestIndex = 0;
  let bestDistance = Infinity;
  const target = new Date(targetHour).getTime();

  times.forEach((time, index) => {
    const distance = Math.abs(new Date(time).getTime() - target);
    if (distance < bestDistance) {
      bestDistance = distance;
      bestIndex = index;
    }
  });

  return bestIndex;
}

async function geocodeZip(zip) {
  const url = new URL("https://geocoding-api.open-meteo.com/v1/search");
  url.searchParams.set("name", zip);
  url.searchParams.set("count", "1");
  url.searchParams.set("country_code", "US");
  url.searchParams.set("format", "json");

  const response = await fetch(url);
  if (!response.ok) throw new Error("ZIP lookup failed.");

  const data = await response.json();
  const place = data.results?.[0];
  if (!place) throw new Error("No weather location found for that ZIP.");

  weatherLat.value = String(place.latitude);
  weatherLon.value = String(place.longitude);
  return {
    latitude: place.latitude,
    longitude: place.longitude,
    label: [place.name, place.admin1, place.postcodes?.[0]].filter(Boolean).join(", ")
  };
}

async function coordinatesForWeather() {
  const lat = Number(weatherLat.value.trim());
  const lon = Number(weatherLon.value.trim());

  if (Number.isFinite(lat) && Number.isFinite(lon)) {
    return { latitude: lat, longitude: lon, label: "GPS coordinates" };
  }

  const zip = weatherZip.value.trim();
  if (zip) return geocodeZip(zip);

  throw new Error("Enter a ZIP code or GPS coordinates first.");
}

async function fetchWeather({ latitude, longitude }) {
  const { date, hour } = selectedWeatherTime();
  const today = new Date().toISOString().slice(0, 10);
  const isPastDate = date < today;
  const endpoint = isPastDate
    ? "https://archive-api.open-meteo.com/v1/archive"
    : "https://api.open-meteo.com/v1/forecast";

  const url = new URL(endpoint);
  url.searchParams.set("latitude", String(latitude));
  url.searchParams.set("longitude", String(longitude));
  url.searchParams.set("start_date", date);
  url.searchParams.set("end_date", date);
  url.searchParams.set("hourly", "temperature_2m,wind_speed_10m,wind_direction_10m");
  url.searchParams.set("temperature_unit", "fahrenheit");
  url.searchParams.set("wind_speed_unit", "mph");
  url.searchParams.set("timezone", "auto");

  const response = await fetch(url);
  if (!response.ok) throw new Error("Weather lookup failed for that date/location.");

  const data = await response.json();
  const times = data.hourly?.time || [];
  if (!times.length) throw new Error("No hourly weather was returned.");

  const index = nearestHourlyIndex(times, hour);
  return {
    time: times[index],
    temperature: data.hourly.temperature_2m?.[index],
    windSpeed: data.hourly.wind_speed_10m?.[index],
    windDirection: data.hourly.wind_direction_10m?.[index]
  };
}

async function fillWeatherFromLookup() {
  try {
    lookupWeatherButton.disabled = true;
    setWeatherStatus("Looking up weather...");

    const location = await coordinatesForWeather();
    const weather = await fetchWeather(location);

    form.elements.temperature.value = Number.isFinite(weather.temperature)
      ? `${Math.round(weather.temperature)} F`
      : "";
    form.elements.windVelocity.value = Number.isFinite(weather.windSpeed)
      ? `${Math.round(weather.windSpeed)} mph`
      : "";
    form.elements.windDirection.value = Number.isFinite(weather.windDirection)
      ? `${Math.round(weather.windDirection)} deg ${windDirectionName(weather.windDirection)}`
      : "";

    setWeatherStatus(`Filled from ${location.label || "location"} for ${weather.time}.`);
    scheduleSave();
  } catch (error) {
    setWeatherStatus(error.message || "Weather lookup failed.");
  } finally {
    lookupWeatherButton.disabled = false;
  }
}

function useGpsForWeather() {
  if (!navigator.geolocation) {
    setWeatherStatus("GPS is not available in this browser.");
    return;
  }

  setWeatherStatus("Waiting for GPS permission...");
  navigator.geolocation.getCurrentPosition(
    (position) => {
      if (!form.elements.startTime.value) {
        form.elements.startTime.value = currentTimeValue();
      }
      weatherLat.value = position.coords.latitude.toFixed(6);
      weatherLon.value = position.coords.longitude.toFixed(6);
      setWeatherStatus("GPS added. Tap Fill Weather.");
      scheduleSave();
    },
    () => setWeatherStatus("GPS permission was not granted."),
    { enableHighAccuracy: true, timeout: 10000, maximumAge: 300000 }
  );
}

function escapeHtml(value = "") {
  return String(value)
    .replaceAll("&", "&amp;")
    .replaceAll("<", "&lt;")
    .replaceAll(">", "&gt;")
    .replaceAll('"', "&quot;");
}

function printCell(label, value, span = 4) {
  return `
    <div class="print-cell span-${span}">
      <span class="print-label">${escapeHtml(label)}</span>
      <span class="print-value">${escapeHtml(value)}</span>
    </div>
  `;
}

function buildPrintSheet(record) {
  const permit = record.permitNumber ? `Yes - ${record.permitNumber}` : "No";
  const pesticides = [...(record.pesticides || [])];
  while (pesticides.length < 6) pesticides.push({});

  printSheet.innerHTML = `
    <div class="print-title">
      <div>
        <h1>Pesticide Application Record</h1>
        <p>${escapeHtml(record.recordName || "")}</p>
        <p>Version 1 - Single Application</p>
        <p>This form must be completed the same day as the application and retained for 7 years.</p>
      </div>
      <div>
        <p>Pesticide Management Division</p>
        <p>PO Box 42560</p>
        <p>Olympia WA 98504-2560</p>
      </div>
    </div>

    <div class="print-box">
      <div class="print-row">
        ${printCell("1. Date of Application", record.applicationDate, 4)}
        ${printCell("4. Application Method / Type", record.method, 4)}
        ${printCell("7. WSDA Permit", permit, 4)}
      </div>
      <div class="print-row">
        ${printCell("5. Crop or Type of Site", record.siteType, 6)}
        ${printCell("6. Total Area Treated", record.areaTreated, 6)}
      </div>
      <div class="print-row">
        ${printCell("Application Location", record.location, 12)}
      </div>
    </div>

    <div class="print-box">
      <div class="print-row">
        ${printCell("2. Name of person for whom pesticide was applied", record.customerName, 6)}
        ${printCell("Firm Name", record.customerFirm, 6)}
      </div>
      <div class="print-row">
        ${printCell("Street Address", record.customerStreet, 6)}
        ${printCell("City", record.customerCity, 3)}
        ${printCell("State", record.customerState, 1)}
        ${printCell("Zip Code", record.customerZip, 2)}
      </div>
    </div>

    <div class="print-box">
      <div class="print-row">
        ${printCell("3. Licensed Applicator's Name", record.applicatorName, 5)}
        ${printCell("License Number", record.applicatorLicense, 3)}
        ${printCell("Phone Number", record.applicatorPhone, 4)}
      </div>
      <div class="print-row">
        ${printCell("Firm Name", record.applicatorFirm, 6)}
        ${printCell("Street Address", record.applicatorStreet, 6)}
      </div>
      <div class="print-row">
        ${printCell("City / State / Zip", record.applicatorCityStateZip, 12)}
      </div>
    </div>

    <table class="print-table">
      <thead>
        <tr>
          <th>8a. Full Product Name</th>
          <th>8b. EPA Reg. No.</th>
          <th>8c. Total Amount Applied</th>
          <th>8d. Pesticide Applied / Acre</th>
          <th>8e. Concentration Applied</th>
          <th>8f. Depth of Application</th>
        </tr>
      </thead>
      <tbody>
        ${pesticides.map((pesticide) => `
          <tr>
            <td>${escapeHtml(pesticide.productName)}</td>
            <td>${escapeHtml(pesticide.epaNumber)}</td>
            <td>${escapeHtml(pesticide.totalAmount)}</td>
            <td>${escapeHtml(pesticide.rate)}</td>
            <td>${escapeHtml(pesticide.concentration)}</td>
            <td>${escapeHtml(pesticide.depth)}</td>
          </tr>
        `).join("")}
      </tbody>
    </table>

    <div class="print-box">
      <div class="print-row">
        ${printCell("Start Time", record.startTime, 2)}
        ${printCell("End Time", record.endTime, 2)}
        ${printCell("Temperature", record.temperature, 2)}
        ${printCell("Wind Direction", record.windDirection, 3)}
        ${printCell("Wind Velocity", record.windVelocity, 3)}
      </div>
      <div class="print-row">
        ${printCell("Equipment", record.equipment, 6)}
        ${printCell("Additional Information", record.notes, 6)}
      </div>
    </div>
  `;
}

async function migrateLegacyRecords() {
  const existingRecords = await getAllRecords();
  if (existingRecords.length) {
    await syncCustomersFromRecords(existingRecords);
    return existingRecords;
  }

  try {
    const legacy = JSON.parse(localStorage.getItem(LEGACY_STORAGE_KEY)) || [];
    for (const record of legacy) {
      await putRecord({
        ...record,
        id: record.id || crypto.randomUUID(),
        createdAt: record.createdAt || new Date().toISOString(),
        updatedAt: record.updatedAt || record.createdAt || new Date().toISOString(),
        pesticides: record.pesticides?.length ? record.pesticides : [{}]
      });
    }
  } catch {
    return [];
  }

  const migrated = await getAllRecords();
  await syncCustomersFromRecords(migrated);
  return migrated;
}

async function syncCustomersFromRecords(sourceRecords) {
  for (const record of sourceRecords) {
    const name = record.customerName || "";
    const firm = record.customerFirm || "";
    if (!name && !firm) continue;

    const existing = customers.find((customer) => {
      return (customer.name || "").toLowerCase() === name.toLowerCase()
        && (customer.firm || "").toLowerCase() === firm.toLowerCase();
    });

    const site = {
      street: record.customerStreet || "",
      city: record.customerCity || "",
      state: record.customerState || "",
      zip: record.customerZip || "",
      location: record.location || ""
    };

    const sites = existing?.sites ? [...existing.sites] : [];
    if ((site.street || site.city || site.zip || site.location) && !sites.some((savedSite) => sameSite(savedSite, site))) {
      sites.push(site);
    }

    await putCustomer({
      id: existing?.id || crypto.randomUUID(),
      name,
      firm,
      sites,
      createdAt: existing?.createdAt || record.createdAt || new Date().toISOString(),
      updatedAt: new Date().toISOString()
    });
  }

  customers = await getAllCustomers();
}

async function deleteActiveRecord() {
  const record = activeRecord();
  if (!record) return;

  await removeRecord(record.id);
  records = records.filter((item) => item.id !== record.id);

  if (!records.length) {
    await createRecord();
    return;
  }

  activeId = records[0].id;
  fillForm(activeRecord());
  renderRecordList();
  setStatus("Record deleted.");
}

async function init() {
  try {
    db = await openDatabase();
    await detectServerStorage();
    customers = await getAllCustomers();
    records = sortRecords(await migrateLegacyRecords());
    renderCustomerOptions();

    if (!records.length) {
      await createRecord();
    } else {
      activeId = records[0].id;
      fillForm(activeRecord());
      renderRecordList();
      const databaseName = storageMode === "server" ? "MySQL database" : "local browser database";
      setStatus(`${databaseName} ready. ${records.length} record${records.length === 1 ? "" : "s"} stored.`);
    }
  } catch (error) {
    setStatus(error.message || "Database could not be opened.");
    saveButton.disabled = true;
    printButton.disabled = true;
    deleteButton.disabled = true;
    newButton.disabled = true;
  }
}

newButton.addEventListener("click", async () => {
  await saveActiveRecord({ quiet: true });
  await createRecord();
});

saveButton.addEventListener("click", () => saveActiveRecord());
saveCustomerButton.addEventListener("click", saveCustomerFromForm);

deleteButton.addEventListener("click", deleteActiveRecord);

addPesticideButton.addEventListener("click", () => {
  addPesticideRow();
  scheduleSave();
});

printButton.addEventListener("click", async () => {
  await saveActiveRecord({ quiet: true });
  buildPrintSheet(activeRecord());
  window.print();
});

form.addEventListener("input", scheduleSave);
recordSearch.addEventListener("input", renderRecordList);
customerPicker.addEventListener("change", () => {
  applyCustomer(findCustomerByPickerValue(customerPicker.value));
});
customerSitePicker.addEventListener("change", () => {
  const customer = findCustomerByPickerValue(customerPicker.value);
  const site = customer?.sites?.[Number(customerSitePicker.value)];
  if (site) {
    applyCustomerSite(site);
    scheduleSave();
  }
});
weatherLookupEnabled.addEventListener("change", () => {
  syncWeatherPanel();
  scheduleSave();
});
lookupWeatherButton.addEventListener("click", fillWeatherFromLookup);
useGpsWeatherButton.addEventListener("click", useGpsForWeather);

init();
populatePesticideOptions();