/**
 * Read-only: builds a Google Ads keyword research file for one bank-statement site.
 *
 * 1. Starts from the seed sections below, written for each site's positioning.
 * 2. Round 1: expands every section through KeywordPlanIdeaService.GenerateKeywordIdeas
 *    (20 seeds per call).
 * 3. Round 2: feeds the highest-volume relevant ideas of 3+ words back in as seeds,
 *    which is where Google surfaces the long-tail variants.
 * 4. Drops off-topic ideas, fetches GenerateKeywordHistoricalMetrics for the rest,
 *    and writes ../<site repo>/ai/seo/keywords/keywords.md.
 *
 * Run: pnpm tsx scripts/bank-keyword-research.ts <site>
 *   site: autobankconvert | cheapbankconvert | fastbankconvert | precisebankflow | simplebankconvert | truebankflow
 */

import "@/config/load-env";
import { mkdirSync, writeFileSync } from "node:fs";
import { resolve } from "node:path";
import { db } from "@/db/client";
import { googleAdsConnections, users } from "@/db/schema";
import { generateKeywordIdeasForRefreshToken } from "@/lib/google-ads/keyword-ideas";
import {
  generateKeywordHistoricalMetricsForRefreshToken,
  type KeywordMetric,
} from "@/lib/google-ads/keyword-metrics";
import { eq } from "drizzle-orm";

const USER_EMAIL = "masaok@gmail.com";

type Section = { title: string; seeds: string[] };

const CORE: Section = {
  title: "Bank statement conversion",
  seeds: [
    "bank statement converter",
    "convert bank statement to excel",
    "pdf bank statement to excel",
    "bank statement to csv",
    "bank statement pdf to csv",
    "convert pdf bank statement",
    "bank statement converter free",
    "bank statement parser",
    "bank statement ocr",
    "pdf to excel converter",
    "bank statement to excel converter",
    "credit card statement to excel",
  ],
};

const SITES: Record<
  string,
  { name: string; repo: string; pitch: string; sections: Section[] }
> = {
  autobankconvert: {
    name: "AutoBankConvert",
    repo: "autobankconvert.com",
    pitch: "automated bank statement conversion for bookkeepers",
    sections: [
      CORE,
      {
        title: "Automation and bulk processing",
        seeds: [
          "automated bank statement conversion",
          "bank statement automation",
          "automatic bank statement processing",
          "bulk bank statement converter",
          "batch pdf to excel",
          "bank statement extraction software",
          "bank statement data extraction",
          "bank statement api",
          "ai bank statement converter",
          "bank statement processing software",
        ],
      },
      {
        title: "Bookkeeping workflows",
        seeds: [
          "bookkeeping automation software",
          "accounting automation for bookkeepers",
          "bookkeeping software for accountants",
          "bank statement to quickbooks",
          "data entry automation accounting",
          "bookkeeping data entry",
          "automate bookkeeping",
          "catch up bookkeeping",
        ],
      },
    ],
  },
  cheapbankconvert: {
    name: "CheapBankConvert",
    repo: "cheapbankconvert.com",
    pitch: "cheap, pay-per-use bank statement converter with no subscription",
    sections: [
      CORE,
      {
        title: "Price and free",
        seeds: [
          "cheap bank statement converter",
          "free bank statement converter",
          "affordable bank statement converter",
          "bank statement converter no subscription",
          "bank statement converter pay per page",
          "convert bank statement to excel free",
          "free pdf to csv converter",
          "free pdf to excel converter",
          "bank statement converter online free",
        ],
      },
      {
        title: "Competitor alternatives",
        seeds: [
          "docuclipper alternative",
          "docuclipper pricing",
          "bankstatementconverter alternative",
          "moneythumb alternative",
          "docuclipper",
          "moneythumb",
          "pdf2qbo",
          "bank statement converter reviews",
        ],
      },
    ],
  },
  fastbankconvert: {
    name: "FastBankConvert",
    repo: "fastbankconvert.com",
    pitch: "instant bank statement converter, PDF to Excel in seconds",
    sections: [
      CORE,
      {
        title: "Speed and online",
        seeds: [
          "instant bank statement converter",
          "fast pdf to excel converter",
          "online bank statement converter",
          "pdf bank statement to excel online",
          "convert pdf to excel online",
          "quick pdf to excel",
          "convert pdf table to excel",
          "extract table from pdf",
        ],
      },
      {
        title: "Scanned and image statements",
        seeds: [
          "convert scanned bank statement to excel",
          "scanned pdf to excel",
          "image to excel converter",
          "ocr pdf to excel",
          "bank statement image to excel",
          "convert scanned pdf to excel",
        ],
      },
    ],
  },
  precisebankflow: {
    name: "PreciseBankFlow",
    repo: "precisebankflow.com",
    pitch: "bank statement reconciliation and audit software",
    sections: [
      CORE,
      {
        title: "Reconciliation",
        seeds: [
          "bank reconciliation software",
          "bank statement reconciliation",
          "automated bank reconciliation",
          "reconciliation software for accountants",
          "account reconciliation tool",
          "transaction matching software",
          "bank reconciliation template",
          "how to reconcile bank statements",
          "bank reconciliation excel",
        ],
      },
      {
        title: "Audit and forensic accounting",
        seeds: [
          "bank statement audit",
          "audit bank statements",
          "forensic accounting software",
          "bank statement analysis forensic",
          "fraud detection bank statements",
          "audit software for accountants",
          "detect fake bank statements",
        ],
      },
    ],
  },
  simplebankconvert: {
    name: "SimpleBankConvert",
    repo: "simplebankconvert.com",
    pitch: "import PDF bank statements into QuickBooks or Xero",
    sections: [
      CORE,
      {
        title: "QuickBooks import",
        seeds: [
          "import bank statement into quickbooks",
          "pdf bank statement to quickbooks",
          "convert pdf to qbo",
          "pdf to qbo converter",
          "csv to qbo",
          "quickbooks bank statement import",
          "quickbooks online import bank transactions",
          "qbo file converter",
          "upload bank statement to quickbooks",
        ],
      },
      {
        title: "Xero and other formats",
        seeds: [
          "bank statement to xero",
          "import pdf bank statement xero",
          "xero bank statement import csv",
          "ofx converter",
          "pdf to ofx",
          "qfx converter",
          "csv to ofx",
          "import bank statement into xero",
        ],
      },
    ],
  },
  truebankflow: {
    name: "TrueBankFlow",
    repo: "truebankflow.com",
    pitch: "bank statement analysis software for lenders",
    sections: [
      CORE,
      {
        title: "Bank statement analysis",
        seeds: [
          "bank statement analysis software",
          "bank statement analyzer",
          "bank statement analysis",
          "cash flow analysis software",
          "bank statement income verification",
          "bank statement analysis tool",
          "ocrolus alternative",
          "ocrolus",
        ],
      },
      {
        title: "Lending and underwriting",
        seeds: [
          "bank statement underwriting",
          "mca underwriting software",
          "cash flow underwriting",
          "small business loan underwriting",
          "bank statement loan analysis",
          "underwriting software for lenders",
          "merchant cash advance underwriting",
          "bank statement loans",
        ],
      },
    ],
  },
};

const siteKey = process.argv[2];
if (!(siteKey in SITES)) {
  console.error(
    `Usage: pnpm tsx scripts/bank-keyword-research.ts <${Object.keys(SITES).join(" | ")}>`,
  );
  process.exit(1);
}
const SITE = SITES[siteKey];
const KEYWORDS_DIR = resolve(process.cwd(), `../${SITE.repo}/ai/seo/keywords`);
const OUTPUT = resolve(KEYWORDS_DIR, "keywords.md");
const SEEDS_PER_CALL = 20;
const ROUND_TWO_SEEDS_PER_SECTION = 40;
const HIGH_TRAFFIC_MIN = 1000;
const LONGTAIL_MIN = 10;
const LONGTAIL_MIN_WORDS = 3;

// Google expands toward the broadest related queries ("quickbooks payroll", "personal loan",
// "excel to pdf"). Domain words keep an idea on their own; brand, bookkeeping and lending
// words only count next to a word about getting statement data in, out or analyzed.
const DOMAIN =
  /bank statement|card statement|statements? (to|into|convert|pdf|csv|excel|import|analy|reconcil|audit|verif)|\bqbo\b|\bofx\b|\bqfx\b|reconcil|underwrit|docuclipper|ocrolus|moneythumb|pdf2qbo|forensic account|audit software|(pdf|scan\w*|image|table)\b.*\b(excel|xlsx?|csv|spreadsheet)/;
const BRAND = /quickbooks|xero/;
const BRAND_ACTION =
  /import|convert|upload|pdf|csv|excel|automat|data entry|extract|ocr|bank|transaction/;
const CONTEXT =
  /\bbank\b|bookkeep|accounting|ledger|audit|forensic|loan|lend|\bmca\b|merchant cash advance|cash ?flow|transaction/;
const CONTEXT_ACTION =
  /automat|data entry|extract|ocr|analy|verif|income|underwrit|fraud|convert|import|export|download|feed|categoriz|pdf|csv|excel/;
const REVERSE = /(excel|xlsx?|csv|word)\s+(to|into)\s+pdf/;
const isRelevant = (kw: string) =>
  !REVERSE.test(kw) &&
  (DOMAIN.test(kw) ||
    (BRAND.test(kw) && BRAND_ACTION.test(kw)) ||
    (CONTEXT.test(kw) && CONTEXT_ACTION.test(kw)));
const words = (kw: string) => kw.split(/\s+/).length;

function chunk<T>(arr: T[], size: number): T[][] {
  const out: T[][] = [];
  for (let i = 0; i < arr.length; i += size) out.push(arr.slice(i, i + size));
  return out;
}

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

const usd = (micros: number | null) =>
  micros == null ? "–" : `$${(micros / 1e6).toFixed(2)}`;
const bidRange = (lo: number | null, hi: number | null) =>
  lo == null && hi == null ? "–" : `${usd(lo)} – ${usd(hi)}`;
const pct = (v: number | null) =>
  v == null ? "–" : `${v > 0 ? "+" : ""}${v}%`;
const vol = (v: number | null) => (v == null ? "–" : v.toLocaleString("en-US"));
const comp = (c: string | null) =>
  c == null || c === "UNSPECIFIED" || c === "UNKNOWN"
    ? "–"
    : c.charAt(0) + c.slice(1).toLowerCase();

type Source = "seed" | "idea" | "longtail idea";
type Row = KeywordMetric & { source: Source; section: string };

function table(rows: Row[], withSection = false): string {
  const head =
    `| Keyword | Avg monthly searches | Competition | Top-of-page bid (low–high) | 3-mo change | YoY change |${withSection ? " Section |" : ""} Source |\n` +
    `| --- | ---: | --- | --- | ---: | ---: |${withSection ? " --- |" : ""} --- |`;
  const body = rows.map(
    (r) =>
      `| ${r.text} | ${vol(r.avgMonthlySearches)} | ${comp(r.competition)} | ${bidRange(r.lowTopOfPageBidMicros, r.highTopOfPageBidMicros)} | ${pct(r.threeMonthChangePct)} | ${pct(r.yoyChangePct)} |${withSection ? ` ${r.section} |` : ""} ${r.source} |`,
  );
  return [head, ...body].join("\n");
}

async function main() {
  const [conn] = await db
    .select({ refreshToken: googleAdsConnections.refreshToken })
    .from(googleAdsConnections)
    .innerJoin(users, eq(googleAdsConnections.userId, users.id))
    .where(eq(users.email, USER_EMAIL))
    .limit(1);
  if (!conn) throw new Error(`No Google Ads connection for ${USER_EMAIL}`);
  const token = conn.refreshToken;

  const sectionOf = new Map<string, string>();
  const sourceOf = new Map<string, Source>();
  const ideaVolume = new Map<string, number>();
  for (const s of SITE.sections)
    for (const kw of s.seeds) {
      if (!sectionOf.has(kw)) sectionOf.set(kw, s.title);
      sourceOf.set(kw, "seed");
    }
  const seedCount = sectionOf.size;

  let customerId = "";
  let ideaCalls = 0;
  let ideaFailures = 0;
  let offTopic = 0;

  async function expand(section: string, seeds: string[], source: Source) {
    for (const batch of chunk(seeds, SEEDS_PER_CALL)) {
      ideaCalls += 1;
      const r = await generateKeywordIdeasForRefreshToken(token, batch);
      if (!r.ok) {
        ideaFailures += 1;
        console.error(
          `  ideas failed for "${section}": ${JSON.stringify(r.error).slice(0, 300)}`,
        );
        continue;
      }
      customerId = r.customerId;
      let added = 0;
      for (const idea of r.ideas) {
        const kw = idea.text.trim().toLowerCase();
        if (!kw || sectionOf.has(kw)) continue;
        if (!isRelevant(kw)) {
          offTopic += 1;
          continue;
        }
        sectionOf.set(kw, section);
        sourceOf.set(kw, source);
        ideaVolume.set(kw, idea.avgMonthlySearches ?? 0);
        added += 1;
      }
      console.log(
        `  [${section}] ${source} seeds=${batch.length} ideas=${r.ideas.length} new=${added}`,
      );
      await sleep(400);
    }
  }

  for (const s of SITE.sections) await expand(s.title, s.seeds, "idea");

  for (const s of SITE.sections) {
    const roundTwo = [...sectionOf.entries()]
      .filter(
        ([kw, title]) =>
          title === s.title &&
          sourceOf.get(kw) === "idea" &&
          words(kw) >= LONGTAIL_MIN_WORDS,
      )
      .map(([kw]) => kw)
      .sort((a, b) => (ideaVolume.get(b) ?? 0) - (ideaVolume.get(a) ?? 0))
      .slice(0, ROUND_TWO_SEEDS_PER_SECTION);
    await expand(s.title, roundTwo, "longtail idea");
  }

  const union = Array.from(sectionOf.keys());
  console.log(
    `Union: ${union.length} relevant keywords (${offTopic} off-topic ideas dropped). Fetching historical metrics…`,
  );

  const metrics = await generateKeywordHistoricalMetricsForRefreshToken(
    token,
    union,
  );
  if (!metrics.ok)
    throw new Error(
      `historical metrics failed: ${JSON.stringify(metrics.error)}`,
    );
  customerId ||= metrics.customerId;

  const seen = new Set<string>();
  const rows: Row[] = [];
  for (const m of metrics.metrics) {
    if (seen.has(m.text)) continue;
    seen.add(m.text);
    rows.push({
      ...m,
      source: sourceOf.get(m.text) ?? "idea",
      section: sectionOf.get(m.text) ?? "Other",
    });
  }
  const missing = union.filter((kw) => !seen.has(kw));

  const byVolume = (a: Row, b: Row) =>
    (b.avgMonthlySearches ?? 0) - (a.avgMonthlySearches ?? 0) ||
    a.text.localeCompare(b.text);
  const v = (r: Row) => r.avgMonthlySearches ?? 0;
  const highTraffic = rows
    .filter((r) => v(r) >= HIGH_TRAFFIC_MIN)
    .sort(byVolume);
  const longtail = rows
    .filter(
      (r) =>
        v(r) >= LONGTAIL_MIN &&
        v(r) < HIGH_TRAFFIC_MIN &&
        words(r.text) >= LONGTAIL_MIN_WORDS,
    )
    .sort(byVolume);
  const midShort = rows
    .filter(
      (r) =>
        v(r) >= LONGTAIL_MIN &&
        v(r) < HIGH_TRAFFIC_MIN &&
        words(r.text) < LONGTAIL_MIN_WORDS,
    )
    .sort(byVolume);
  const low = rows.filter((r) => v(r) < LONGTAIL_MIN).sort(byVolume);
  const totalVolume = rows.reduce((n, r) => n + v(r), 0);
  const today = new Date().toISOString().slice(0, 10);
  const n = (x: number) => x.toLocaleString("en-US");

  const out: string[] = [];
  out.push(`# ${SITE.name} SEO keywords`);
  out.push("");
  out.push(
    `Generated ${today} from the Google Ads API (Keyword Planner) for ${SITE.name}: ${SITE.pitch}. Seed keywords for each section went through \`GenerateKeywordIdeas\`. The highest-volume relevant ideas of ${LONGTAIL_MIN_WORDS}+ words then went through it a second time to find long-tail variants, and \`GenerateKeywordHistoricalMetrics\` was fetched for every keyword. English, United States, Google Search network only. Avg monthly searches is Google's trailing 12-month average, rounded to its usual buckets.`,
  );
  out.push("");
  out.push("| | |");
  out.push("| --- | ---: |");
  out.push(`| Seed keywords | ${n(seedCount)} |`);
  out.push(`| Relevant keywords with metrics | ${n(rows.length)} |`);
  out.push(
    `| High-traffic (≥ ${n(HIGH_TRAFFIC_MIN)} searches/month) | ${n(highTraffic.length)} |`,
  );
  out.push(
    `| Long-tail (${LONGTAIL_MIN_WORDS}+ words, ${LONGTAIL_MIN}–${n(HIGH_TRAFFIC_MIN - 1)} searches/month) | ${n(longtail.length)} |`,
  );
  out.push(
    `| Short mid-volume (1–2 words, ${LONGTAIL_MIN}–${n(HIGH_TRAFFIC_MIN - 1)} searches/month) | ${n(midShort.length)} |`,
  );
  out.push(`| Under ${LONGTAIL_MIN} searches/month | ${n(low.length)} |`);
  out.push(`| Combined avg monthly searches | ${n(totalVolume)} |`);
  out.push(`| Off-topic Google ideas dropped | ${n(offTopic)} |`);
  out.push(`| Idea calls (failures) | ${ideaCalls} (${ideaFailures}) |`);
  out.push("");
  out.push("Columns:");
  out.push("");
  out.push(
    "- **Source**: `seed` was written for the site; `idea` came from Keyword Planner in round 1; `longtail idea` came from round 2.",
  );
  out.push(
    "- **Section** is the seed section whose expansion first produced the keyword.",
  );
  out.push(
    "- **Competition** is Google Ads advertiser competition (Low / Medium / High), not organic difficulty.",
  );
  out.push(
    "- **Top-of-page bid** is the range advertisers pay for the top of page one, a proxy for commercial value.",
  );
  out.push(
    "- **3-mo / YoY change** compare the latest complete month with 3 and 12 months earlier.",
  );
  out.push("- A dash means Google returned no value.");
  out.push("");
  out.push("---");
  out.push("");
  out.push(
    `## High-traffic keywords (≥ ${n(HIGH_TRAFFIC_MIN)} searches/month)`,
  );
  out.push("");
  out.push(
    "Head terms to build pillar pages around. Many are broad, so check intent before targeting.",
  );
  out.push("");
  out.push(highTraffic.length > 0 ? table(highTraffic, true) : "_None._");
  out.push("");
  out.push(
    `## Long-tail keywords (${LONGTAIL_MIN_WORDS}+ words, ${LONGTAIL_MIN}–${n(HIGH_TRAFFIC_MIN - 1)} searches/month)`,
  );
  out.push("");
  out.push(
    "Specific queries with clearer intent and usually less competition. Good targets for landing pages, FAQs and blog posts.",
  );
  out.push("");
  out.push(longtail.length > 0 ? table(longtail, true) : "_None._");
  out.push("");
  if (midShort.length > 0) {
    out.push(
      `## Short mid-volume keywords (1–2 words, ${LONGTAIL_MIN}–${n(HIGH_TRAFFIC_MIN - 1)} searches/month)`,
    );
    out.push("");
    out.push(table(midShort, true));
    out.push("");
  }
  out.push("---");
  out.push("");
  out.push("## By section");
  out.push("");
  for (const s of SITE.sections) {
    const list = rows
      .filter((r) => r.section === s.title && v(r) >= LONGTAIL_MIN)
      .sort(byVolume);
    out.push(`### ${s.title}`);
    out.push("");
    out.push(`Seeds: ${s.seeds.join(" · ")}`);
    out.push("");
    out.push(
      `${n(list.length)} keywords with at least ${LONGTAIL_MIN} searches a month.`,
    );
    out.push("");
    if (list.length > 0) {
      out.push(table(list));
      out.push("");
    }
  }
  if (low.length > 0) {
    out.push(`## Keywords under ${LONGTAIL_MIN} searches a month`);
    out.push("");
    out.push("<details><summary>Show</summary>");
    out.push("");
    out.push(
      low.map((r) => `${r.text} (${r.avgMonthlySearches ?? "–"})`).join(" · "),
    );
    out.push("");
    out.push("</details>");
    out.push("");
  }
  if (missing.length > 0) {
    out.push("## Keywords Google returned no metrics for");
    out.push("");
    out.push(
      "Google skips keywords it will not serve ads on or has never seen enough of to measure.",
    );
    out.push("");
    out.push(missing.sort().join(" · "));
    out.push("");
  }

  mkdirSync(KEYWORDS_DIR, { recursive: true });
  writeFileSync(OUTPUT, out.join("\n"));
  console.log(
    `Wrote ${OUTPUT} (${rows.length} rows: ${highTraffic.length} high-traffic, ${longtail.length} long-tail; customer ${customerId})`,
  );
}

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