import "server-only";

import { createCipheriv, createDecipheriv, createHash, createHmac, randomBytes, timingSafeEqual } from "crypto";
import type { RowDataPacket } from "mysql2/promise";
import { ensureGoogleSearchConsoleSchema, getDbPool } from "@/lib/db";
import {
  getMarketingBlogPostById,
  getPublishedMarketingBlogPosts,
  type MarketingBlogPost,
} from "@/lib/marketing-blog";

export const GOOGLE_SEARCH_CONSOLE_SCOPE = "https://www.googleapis.com/auth/webmasters.readonly";
export const GOOGLE_BLOGGER_SCOPE = "https://www.googleapis.com/auth/blogger";

const OFFICIALSITE_URL_PREFIX = "https://officialsite.kr/";
const OAUTH_STATE_TTL_MS = 10 * 60 * 1000;
const TOKEN_EXPIRY_SKEW_MS = 5 * 60 * 1000;
const SEARCH_CONSOLE_SYNC_LOCK_TIMEOUT_SECONDS = 0;
const SEARCH_CONSOLE_DB_WRITE_MAX_ATTEMPTS = 3;
const SEARCH_CONSOLE_BACKFILL_DAYS = 90;
const SEARCH_CONSOLE_FINAL_DATA_DELAY_DAYS = 3;
const SEARCH_CONSOLE_ROW_LIMIT = 25000;
const URL_INSPECTION_FRESHNESS_MS = 24 * 60 * 60 * 1000;
const URL_INSPECTION_CONCURRENCY = 3;

type GoogleSearchConsoleConnectionRow = RowDataPacket & {
  access_token_ciphertext: string;
  account_email: string;
  connected_at: Date;
  id: number;
  last_sync_error: string | null;
  last_synced_at: Date | null;
  refresh_token_ciphertext: string;
  scopes: string;
  site_url: string;
  token_expires_at: Date | null;
};

type AggregateMetricRow = RowDataPacket & {
  clicks: number | null;
  impressions: number | null;
  weighted_position: number | null;
};

type DailyMetricRow = AggregateMetricRow & {
  metric_date: string;
};

type SyncCoverageRow = RowDataPacket & {
  available_days: number | null;
};

type SuccessfulSyncDateRow = RowDataPacket & {
  sync_date: string;
};

type RankedMetricRow = RowDataPacket & {
  clicks: number;
  ctr: number;
  impressions: number;
  page_url: string | null;
  position: number;
  query_text: string | null;
};

type GoogleTokenResponse = {
  access_token?: unknown;
  error?: unknown;
  error_description?: unknown;
  expires_in?: unknown;
  refresh_token?: unknown;
  scope?: unknown;
  token_type?: unknown;
};

type SearchAnalyticsRow = {
  clicks?: unknown;
  ctr?: unknown;
  impressions?: unknown;
  keys?: unknown;
  position?: unknown;
};

type SearchAnalyticsResponse = {
  error?: { message?: unknown };
  rows?: SearchAnalyticsRow[];
};

type NamedLockRow = RowDataPacket & {
  acquired: number | null;
};

type BlogPageMetricRow = RowDataPacket & {
  clicks: number | string | null;
  impressions: number | string | null;
  page_url: string;
  weighted_position: number | string | null;
};

type UrlInspectionRow = RowDataPacket & {
  coverage_state: string | null;
  google_canonical: string | null;
  indexing_state: string | null;
  inspection_error: string | null;
  inspection_result_link: string | null;
  inspection_url: string;
  last_crawl_time: Date | null;
  last_inspected_at: Date;
  page_fetch_state: string | null;
  robots_txt_state: string | null;
  sitemaps_json: unknown;
  user_canonical: string | null;
  verdict: string;
};

type UrlInspectionResponse = {
  error?: { message?: unknown };
  inspectionResult?: {
    indexStatusResult?: {
      crawledAs?: unknown;
      coverageState?: unknown;
      googleCanonical?: unknown;
      indexingState?: unknown;
      lastCrawlTime?: unknown;
      pageFetchState?: unknown;
      referringUrls?: unknown;
      robotsTxtState?: unknown;
      sitemap?: unknown;
      userCanonical?: unknown;
      verdict?: unknown;
    };
    inspectionResultLink?: unknown;
  };
};

export type GoogleSearchConsoleAnalytics = {
  actionItems: GoogleSearchConsoleActionItem[];
  brandPerformance: {
    branded: GoogleSearchConsoleMetricSummary;
    hiddenClicks: number;
    knownQueryClicks: number;
    nonBranded: GoogleSearchConsoleMetricSummary;
  };
  connection: {
    accountEmail: string;
    connectedAt: string;
    lastSyncError: string | null;
    lastSyncedAt: string | null;
    siteUrl: string;
  } | null;
  configured: boolean;
  contentCoverage: {
    indexedPosts: number;
    inspectionErrors: number;
    pendingPosts: number;
    publishedPosts: number;
  };
  dailyMetrics: Array<GoogleSearchConsoleMetricSummary & { date: string }>;
  dataWindow: {
    availableDays: number;
    endDate: string;
    expectedDays: number;
    previousAvailableDays: number;
    startDate: string;
  };
  reconnectRequired: boolean;
  metrics: GoogleSearchConsoleMetricSummary;
  opportunityQueries: Array<{
    clicks: number;
    ctr: number;
    impressions: number;
    position: number;
    query: string;
  }>;
  previousMetrics: GoogleSearchConsoleMetricSummary;
  rangeDays: 7 | 28 | 90;
  topPages: Array<{
    clicks: number;
    ctr: number;
    impressions: number;
    page: string;
    position: number;
  }>;
  topQueries: Array<{
    clicks: number;
    ctr: number;
    impressions: number;
    position: number;
    query: string;
  }>;
  targetSiteUrl: string;
};

export type GoogleSearchConsoleMetricSummary = {
  clicks: number;
  ctr: number;
  impressions: number;
  position: number;
};

export type GoogleSearchConsoleActionItem = {
  clicks: number;
  ctr: number;
  description: string;
  editPostId: number | null;
  id: string;
  impressions: number;
  kind: "improve-position" | "improve-ctr" | "create-content";
  page: string | null;
  position: number;
  priority: "high" | "medium";
  query: string;
  queries: string[];
  title: string;
};

export type GoogleSearchConsoleBlogPostStatus = {
  clicks: number;
  coverageState: string | null;
  googleCanonical: string | null;
  googleDetectedSitemap: boolean;
  impressions: number;
  indexVerdict: string | null;
  indexingState: string | null;
  inspectionError: string | null;
  inspectionResultLink: string | null;
  isIndexed: boolean;
  lastCrawlTime: string | null;
  lastInspectedAt: string | null;
  pageFetchState: string | null;
  position: number;
  postId: number;
  published: boolean;
  robotsTxtState: string | null;
  sitemapIncluded: boolean;
  url: string;
  userCanonical: string | null;
};

export type GoogleSearchConsoleBlogStatus = {
  connected: boolean;
  posts: GoogleSearchConsoleBlogPostStatus[];
  targetSiteUrl: string;
};

type OAuthState = {
  email: string;
  issuedAt: number;
  returnTo?: "blog" | "search";
};

function getRuntimeConfig() {
  const clientId = process.env.GOOGLE_SEARCH_CONSOLE_CLIENT_ID?.trim();
  const clientSecret = process.env.GOOGLE_SEARCH_CONSOLE_CLIENT_SECRET?.trim();

  if (!clientId || !clientSecret) {
    return null;
  }

  return {
    clientId,
    clientSecret,
    // This dashboard intentionally tracks only the public canonical site.
    // A Domain property would combine mail and other operational subdomains.
    siteUrl: OFFICIALSITE_URL_PREFIX,
  };
}

function getSecretMaterial() {
  return (
    process.env.GOOGLE_SEARCH_CONSOLE_TOKEN_ENCRYPTION_KEY?.trim() ||
    process.env.OFFICIAL_MAIL_CRON_SECRET?.trim() ||
    ""
  );
}

function requireSecretMaterial() {
  const material = getSecretMaterial();

  if (!material) {
    throw new Error("GOOGLE_SEARCH_CONSOLE_TOKEN_ENCRYPTION_KEY 또는 OFFICIAL_MAIL_CRON_SECRET이 필요합니다.");
  }

  return material;
}

function getEncryptionKey() {
  return createHash("sha256").update(requireSecretMaterial()).digest();
}

function encryptSecret(value: string) {
  const iv = randomBytes(12);
  const cipher = createCipheriv("aes-256-gcm", getEncryptionKey(), iv);
  const ciphertext = Buffer.concat([cipher.update(value, "utf8"), cipher.final()]);
  const authTag = cipher.getAuthTag();

  return ["v1", iv.toString("base64url"), authTag.toString("base64url"), ciphertext.toString("base64url")].join(".");
}

function decryptSecret(value: string) {
  const [version, iv, authTag, ciphertext] = value.split(".");

  if (version !== "v1" || !iv || !authTag || !ciphertext) {
    throw new Error("Google Search Console 토큰 형식이 올바르지 않습니다.");
  }

  const decipher = createDecipheriv("aes-256-gcm", getEncryptionKey(), Buffer.from(iv, "base64url"));
  decipher.setAuthTag(Buffer.from(authTag, "base64url"));

  return Buffer.concat([
    decipher.update(Buffer.from(ciphertext, "base64url")),
    decipher.final(),
  ]).toString("utf8");
}

function stateSignature(payload: string) {
  return createHmac("sha256", requireSecretMaterial()).update(payload).digest("base64url");
}

function toIso(value: Date | null) {
  return value ? value.toISOString() : null;
}

function toNumber(value: unknown) {
  const numberValue = Number(value ?? 0);
  return Number.isFinite(numberValue) ? numberValue : 0;
}

function formatDate(value: Date) {
  return value.toISOString().slice(0, 10);
}

function shiftUtcDate(days: number) {
  const value = new Date();
  value.setUTCDate(value.getUTCDate() + days);
  return value;
}

function shiftDateString(value: string, days: number) {
  const date = new Date(`${value}T12:00:00.000Z`);
  date.setUTCDate(date.getUTCDate() + days);
  return formatDate(date);
}

function dateRange(startDate: string, endDate: string) {
  const dates: string[] = [];

  for (let date = startDate; date <= endDate; date = shiftDateString(date, 1)) {
    dates.push(date);
  }

  return dates;
}

function consecutiveDateRanges(dates: string[]) {
  const ranges: Array<{ endDate: string; startDate: string }> = [];

  dates.forEach((date) => {
    const current = ranges[ranges.length - 1];

    if (current && shiftDateString(current.endDate, 1) === date) {
      current.endDate = date;
      return;
    }

    ranges.push({ endDate: date, startDate: date });
  });

  return ranges;
}

function metricSummary(row?: Partial<AggregateMetricRow> | null): GoogleSearchConsoleMetricSummary {
  const impressions = toNumber(row?.impressions);
  const clicks = toNumber(row?.clicks);

  return {
    clicks,
    ctr: impressions > 0 ? clicks / impressions : 0,
    impressions,
    position: toNumber(row?.weighted_position),
  };
}

function isBrandedQuery(query: string) {
  const normalized = query.toLowerCase().replace(/\s+/g, "");
  return (
    normalized.includes("오피셜메일") ||
    normalized === "오피셜" ||
    normalized.includes("officialmail") ||
    normalized.includes("officialsite")
  );
}

function metricRowKey(dimensionType: string, value: string) {
  return createHash("sha256").update(`${dimensionType}:${value}`).digest("hex");
}

function inspectionUrlHash(value: string) {
  return createHash("sha256").update(value).digest();
}

function getBlogPostUrl(slug: string) {
  return new URL(`/blog/${slug}`, OFFICIALSITE_URL_PREFIX).toString();
}

function parseStoredStringList(value: unknown) {
  if (Array.isArray(value)) {
    return value.filter((item): item is string => typeof item === "string");
  }

  if (!value) {
    return [];
  }

  try {
    const parsed = JSON.parse(String(value));
    return Array.isArray(parsed)
      ? parsed.filter((item): item is string => typeof item === "string")
      : [];
  } catch {
    return [];
  }
}

function normalizeStringList(value: unknown) {
  return Array.isArray(value)
    ? value.filter((item): item is string => typeof item === "string")
    : [];
}

function toNullableString(value: unknown, maxLength = 768) {
  return typeof value === "string" && value.trim()
    ? value.trim().slice(0, maxLength)
    : null;
}

function toNullableDatabaseDate(value: unknown) {
  if (typeof value !== "string" || !value.trim()) {
    return null;
  }

  const date = new Date(value);
  return Number.isNaN(date.getTime()) ? null : date;
}

function compactError(error: unknown) {
  const value = error instanceof Error ? error.message : String(error || "Google Search Console 동기화에 실패했습니다.");
  return value.slice(0, 900);
}

function isTransientDatabaseConflict(error: unknown) {
  const message = error instanceof Error ? error.message.toLowerCase() : String(error).toLowerCase();

  return (
    message.includes("deadlock found") ||
    message.includes("er_lock_deadlock") ||
    message.includes("lock wait timeout") ||
    message.includes("er_lock_wait_timeout")
  );
}

function waitForRetry(delayMs: number) {
  return new Promise<void>((resolve) => {
    setTimeout(resolve, delayMs);
  });
}

async function retrySearchConsoleDatabaseWrite<T>(operation: () => Promise<T>) {
  let lastError: unknown;

  for (let attempt = 0; attempt < SEARCH_CONSOLE_DB_WRITE_MAX_ATTEMPTS; attempt += 1) {
    try {
      return await operation();
    } catch (error) {
      lastError = error;

      if (!isTransientDatabaseConflict(error) || attempt === SEARCH_CONSOLE_DB_WRITE_MAX_ATTEMPTS - 1) {
        throw error;
      }

      await waitForRetry(80 * 2 ** attempt);
    }
  }

  throw lastError;
}

async function withSearchConsoleSyncLock<T>(connectionId: number, operation: () => Promise<T>) {
  const databaseConnection = await getDbPool().getConnection();
  const lockName = `official-mail:gsc-sync:${connectionId}`;
  let acquired = false;

  try {
    const [rows] = await databaseConnection.query<NamedLockRow[]>(
      "SELECT GET_LOCK(?, ?) AS acquired",
      [lockName, SEARCH_CONSOLE_SYNC_LOCK_TIMEOUT_SECONDS],
    );
    acquired = Number(rows[0]?.acquired ?? 0) === 1;

    if (!acquired) {
      return { acquired: false as const };
    }

    return { acquired: true as const, result: await operation() };
  } finally {
    if (acquired) {
      await databaseConnection.query("SELECT RELEASE_LOCK(?)", [lockName]).catch(() => undefined);
    }
    databaseConnection.release();
  }
}

function normalizePageRows(rows: RankedMetricRow[]) {
  return rows.map((row) => ({
    clicks: toNumber(row.clicks),
    ctr: toNumber(row.ctr),
    impressions: toNumber(row.impressions),
    page: row.page_url ?? "-",
    position: toNumber(row.position),
  }));
}

function normalizeQueryRows(rows: RankedMetricRow[]) {
  return rows.map((row) => ({
    clicks: toNumber(row.clicks),
    ctr: toNumber(row.ctr),
    impressions: toNumber(row.impressions),
    position: toNumber(row.position),
    query: row.query_text ?? "-",
  }));
}

function normalizeQueryPageRows(rows: RankedMetricRow[]) {
  return rows.map((row) => ({
    clicks: toNumber(row.clicks),
    ctr: toNumber(row.ctr),
    impressions: toNumber(row.impressions),
    page: row.page_url ?? "",
    position: toNumber(row.position),
    query: row.query_text ?? "",
  }));
}

function rankedRowsSummary(rows: RankedMetricRow[]): GoogleSearchConsoleMetricSummary {
  const clicks = rows.reduce((total, row) => total + toNumber(row.clicks), 0);
  const impressions = rows.reduce((total, row) => total + toNumber(row.impressions), 0);
  const weightedPosition = rows.reduce(
    (total, row) => total + toNumber(row.position) * toNumber(row.impressions),
    0,
  );

  return {
    clicks,
    ctr: impressions > 0 ? clicks / impressions : 0,
    impressions,
    position: impressions > 0 ? weightedPosition / impressions : 0,
  };
}

function searchTokens(value: string) {
  return new Set(
    value
      .toLowerCase()
      .split(/[^a-z0-9가-힣]+/)
      .map((token) => token.trim())
      .filter((token) => token.length >= 2),
  );
}

function matchingBlogPostId(
  page: string,
  posts: MarketingBlogPost[],
) {
  try {
    const pathname = new URL(page).pathname;
    const slug = pathname.match(/^\/blog\/([^/]+)\/?$/)?.[1];
    return slug ? posts.find((post) => post.slug === slug)?.id ?? null : null;
  } catch {
    return null;
  }
}

function queryMatchesPublishedPost(query: string, posts: MarketingBlogPost[]) {
  const queryTokens = searchTokens(query);

  return posts.some((post) => {
    const postTokens = searchTokens([post.title, ...post.keywords].join(" "));
    return Array.from(queryTokens).filter((token) => postTokens.has(token)).length >= Math.min(2, queryTokens.size);
  });
}

function searchIntentForQuery(query: string) {
  const normalized = query.toLowerCase().replace(/\s+/g, " ").trim();
  const hasMailTerm = /(메일|이메일)/.test(normalized);

  if (/(550|spf|dkim|dmarc|dns|mx 레코드|반송|스팸)/i.test(normalized)) {
    return { id: "mail-auth", label: "메일 인증·반송 해결" };
  }

  if (hasMailTerm && /도메인/.test(normalized)) {
    return { id: "domain-email", label: "도메인 메일" };
  }

  if (/(ai|인공지능|요약|릴레이)/i.test(normalized) && hasMailTerm) {
    return { id: "ai-email", label: "AI 메일 요약" };
  }

  if (/(팀|공용|공유|그룹웨어|멤버)/.test(normalized) && hasMailTerm) {
    return { id: "team-email", label: "팀·공용 메일" };
  }

  if (hasMailTerm && /(기업|회사|사업자|업무용|무료)/.test(normalized)) {
    return { id: "business-email", label: "기업·회사 메일" };
  }

  return { id: `query:${normalized}`, label: query };
}

function buildSearchActionItems({
  opportunityRows,
  pairRows,
  posts,
}: {
  opportunityRows: RankedMetricRow[];
  pairRows: RankedMetricRow[];
  posts: MarketingBlogPost[];
}) {
  const normalizedPairs = normalizeQueryPageRows(pairRows)
    .filter((row) => row.query && row.page && !isBrandedQuery(row.query));
  const normalizedQueries = normalizeQueryRows(opportunityRows)
    .filter((row) => row.query && !isBrandedQuery(row.query));
  const actions: GoogleSearchConsoleActionItem[] = [];
  const coveredQueries = new Set<string>();
  const seen = new Set<string>();

  function addAction(action: GoogleSearchConsoleActionItem) {
    if (seen.has(action.id) || actions.length >= 12) return;
    seen.add(action.id);
    action.queries.forEach((query) => coveredQueries.add(query));
    actions.push(action);
  }

  const intentGroups = new Map<string, {
    id: string;
    label: string;
    rows: typeof normalizedQueries;
  }>();
  normalizedQueries.forEach((row) => {
    const intent = searchIntentForQuery(row.query);
    const group = intentGroups.get(intent.id) ?? { ...intent, rows: [] };
    group.rows.push(row);
    intentGroups.set(intent.id, group);
  });

  const intentSummaries = Array.from(intentGroups.values()).map((group) => {
    const queries = group.rows.map((row) => row.query);
    const querySet = new Set(queries);
    const matchingPairs = normalizedPairs.filter((row) => querySet.has(row.query));
    const pageGroups = new Map<string, { clicks: number; impressions: number; page: string }>();
    matchingPairs.forEach((row) => {
      const page = pageGroups.get(row.page) ?? { clicks: 0, impressions: 0, page: row.page };
      page.clicks += row.clicks;
      page.impressions += row.impressions;
      pageGroups.set(row.page, page);
    });
    const primaryPage = Array.from(pageGroups.values()).sort((left, right) => {
      if (left.impressions !== right.impressions) return right.impressions - left.impressions;
      return right.clicks - left.clicks;
    })[0]?.page ?? null;
    const rows = group.rows;
    const impressions = rows.reduce((total, row) => total + row.impressions, 0);
    const clicks = rows.reduce((total, row) => total + row.clicks, 0);
    const weightedPosition = rows.reduce((total, row) => total + row.position * row.impressions, 0);

    return {
      clicks,
      ctr: impressions > 0 ? clicks / impressions : 0,
      id: group.id,
      impressions,
      label: group.label,
      page: primaryPage,
      position: impressions > 0 ? weightedPosition / impressions : 0,
      queries,
    };
  });

  intentSummaries
    .filter((row) => row.page && row.impressions >= 20 && row.position >= 4 && row.position <= 50)
    .sort((left, right) => right.impressions - left.impressions)
    .forEach((row) => {
      if (!row.page) return;

      const targetRank = row.position > 10 ? 10 : 3;
      const isHomepage = (() => {
        try {
          return new URL(row.page).pathname === "/";
        } catch {
          return false;
        }
      })();

      addAction({
        clicks: row.clicks,
        ctr: row.ctr,
        description: isHomepage && row.id === "business-email"
          ? `메인 랜딩이 관련 검색어 ${row.queries.length}개로 노출되고 있지만 평균 ${row.position.toFixed(1)}위입니다. 랜딩 구조는 유지합니다. 연관 페이지·가이드의 내부 연결을 점검한 뒤 실제 외부 언급·백링크를 확보하는 것이 다음 우선순위입니다.`
          : `관련 검색어 ${row.queries.length}개의 평균 순위가 ${row.position.toFixed(1)}위입니다. 현재 페이지의 구조를 반복 수정하기보다 연관 콘텐츠와 외부 언급을 통해 검색 권위를 보강하세요.`,
        editPostId: matchingBlogPostId(row.page, posts),
        id: `position:${row.id}:${row.page}`,
        impressions: row.impressions,
        kind: "improve-position",
        page: row.page,
        position: row.position,
        priority: row.impressions >= 50 ? "high" : "medium",
        queries: row.queries,
        query: row.label,
        title: `${row.label} 검색군을 ${targetRank}위권으로 끌어올리기`,
      });
    });

  intentSummaries
    .filter((row) => row.page && row.impressions >= 100 && row.position > 0 && row.position <= 10 && row.ctr < 0.03)
    .sort((left, right) => right.impressions - left.impressions)
    .forEach((row) => {
      if (!row.page || row.queries.some((query) => coveredQueries.has(query))) return;

      addAction({
        clicks: row.clicks,
        ctr: row.ctr,
        description: "충분한 노출 표본과 1페이지권 순위가 있는데도 클릭률이 낮습니다. 이 경우에만 검색 결과 제목과 설명 개선을 우선합니다.",
        editPostId: matchingBlogPostId(row.page, posts),
        id: `ctr:${row.id}:${row.page}`,
        impressions: row.impressions,
        kind: "improve-ctr",
        page: row.page,
        position: row.position,
        priority: row.impressions >= 200 ? "high" : "medium",
        queries: row.queries,
        query: row.label,
        title: `${row.label} 검색 결과 클릭률 개선`,
      });
    });

  normalizedQueries
    .filter((row) => (
      row.query &&
      row.impressions >= 10 &&
      !queryMatchesPublishedPost(row.query, posts) &&
      !coveredQueries.has(row.query)
    ))
    .sort((left, right) => right.impressions - left.impressions)
    .forEach((row) => {
      addAction({
        ...row,
        description: "현재 발행 글의 제목과 핵심 키워드에서 직접 대응하는 콘텐츠를 찾지 못했습니다. 실제 질문에 답하는 전용 가이드를 작성할 후보입니다.",
        editPostId: null,
        id: `content:${row.query}`,
        kind: "create-content",
        page: null,
        priority: row.impressions >= 50 ? "high" : "medium",
        queries: [row.query],
        title: `${row.query} 전용 콘텐츠 작성`,
      });
    });

  return actions.sort((left, right) => {
    if (left.priority !== right.priority) return left.priority === "high" ? -1 : 1;
    return right.impressions - left.impressions;
  });
}

export function isGoogleSearchConsoleConfigured() {
  return Boolean(getRuntimeConfig() && getSecretMaterial());
}

export function createGoogleSearchConsoleOAuthState(
  email: string,
  returnTo: OAuthState["returnTo"] = "search",
) {
  const payload = Buffer.from(
    JSON.stringify({
      email: email.trim().toLowerCase(),
      issuedAt: Date.now(),
      returnTo,
    } satisfies OAuthState),
  ).toString("base64url");

  return `${payload}.${stateSignature(payload)}`;
}

export function verifyGoogleSearchConsoleOAuthState(state: string) {
  const [payload, signature] = state.split(".");

  if (!payload || !signature) {
    return null;
  }

  const expected = stateSignature(payload);
  const actualBuffer = Buffer.from(signature);
  const expectedBuffer = Buffer.from(expected);

  if (actualBuffer.length !== expectedBuffer.length || !timingSafeEqual(actualBuffer, expectedBuffer)) {
    return null;
  }

  try {
    const parsed = JSON.parse(Buffer.from(payload, "base64url").toString("utf8")) as OAuthState;

    if (!parsed.email || !Number.isFinite(parsed.issuedAt) || Date.now() - parsed.issuedAt > OAUTH_STATE_TTL_MS) {
      return null;
    }

    return parsed;
  } catch {
    return null;
  }
}

export function buildGoogleSearchConsoleAuthorizeUrl(input: {
  email: string;
  redirectUri: string;
  returnTo?: OAuthState["returnTo"];
}) {
  const config = getRuntimeConfig();

  if (!config || !getSecretMaterial()) {
    throw new Error("Google Search Console OAuth 환경변수가 설정되지 않았습니다.");
  }

  const url = new URL("https://accounts.google.com/o/oauth2/v2/auth");
  url.searchParams.set("client_id", config.clientId);
  url.searchParams.set("redirect_uri", input.redirectUri);
  url.searchParams.set("response_type", "code");
  url.searchParams.set("scope", `${GOOGLE_SEARCH_CONSOLE_SCOPE} ${GOOGLE_BLOGGER_SCOPE}`);
  url.searchParams.set("access_type", "offline");
  url.searchParams.set("include_granted_scopes", "true");
  url.searchParams.set("prompt", "consent");
  url.searchParams.set("state", createGoogleSearchConsoleOAuthState(input.email, input.returnTo));

  return url.toString();
}

async function exchangeAuthorizationCode(input: { code: string; redirectUri: string }) {
  const config = getRuntimeConfig();

  if (!config) {
    throw new Error("Google Search Console OAuth 환경변수가 설정되지 않았습니다.");
  }

  const response = await fetch("https://oauth2.googleapis.com/token", {
    method: "POST",
    headers: { "content-type": "application/x-www-form-urlencoded" },
    body: new URLSearchParams({
      client_id: config.clientId,
      client_secret: config.clientSecret,
      code: input.code,
      grant_type: "authorization_code",
      redirect_uri: input.redirectUri,
    }),
    cache: "no-store",
  });
  const payload = (await response.json().catch(() => ({}))) as GoogleTokenResponse;

  if (!response.ok || typeof payload.access_token !== "string") {
    throw new Error(String(payload.error_description || payload.error || "Google OAuth 토큰 교환에 실패했습니다."));
  }

  return {
    accessToken: payload.access_token,
    expiresIn: Math.max(60, toNumber(payload.expires_in)),
    refreshToken: typeof payload.refresh_token === "string" ? payload.refresh_token : null,
    scopes: typeof payload.scope === "string" ? payload.scope : GOOGLE_SEARCH_CONSOLE_SCOPE,
  };
}

async function getGoogleSearchConsoleProperties(accessToken: string) {
  const response = await fetch("https://www.googleapis.com/webmasters/v3/sites", {
    headers: { authorization: `Bearer ${accessToken}` },
    cache: "no-store",
  });
  const payload = (await response.json().catch(() => ({}))) as {
    error?: { message?: unknown };
    siteEntry?: Array<{ permissionLevel?: unknown; siteUrl?: unknown }>;
  };

  if (!response.ok) {
    throw new Error(String(payload.error?.message || "Search Console 속성 목록을 불러오지 못했습니다."));
  }

  return (payload.siteEntry ?? [])
    .map((entry) => ({
      permissionLevel: String(entry.permissionLevel ?? ""),
      siteUrl: String(entry.siteUrl ?? ""),
    }))
    .filter((entry) => entry.siteUrl);
}

export async function connectGoogleSearchConsole(input: {
  accessCode: string;
  accountEmail: string;
  redirectUri: string;
}) {
  await ensureGoogleSearchConsoleSchema();

  const config = getRuntimeConfig();

  if (!config) {
    throw new Error("Google Search Console OAuth 환경변수가 설정되지 않았습니다.");
  }

  const token = await exchangeAuthorizationCode({ code: input.accessCode, redirectUri: input.redirectUri });
  const properties = await getGoogleSearchConsoleProperties(token.accessToken);
  const property = properties.find((entry) => entry.siteUrl === config.siteUrl);

  if (!property) {
    throw new Error(`연결한 Google 계정에 ${config.siteUrl} Search Console 속성 권한이 없습니다.`);
  }

  const accountEmail = input.accountEmail.trim().toLowerCase();
  const [existingRows] = await getDbPool().query<
    (RowDataPacket & {
      id: number;
      refresh_token_ciphertext: string;
      site_url: string;
    })[]
  >(
    `
      SELECT id, refresh_token_ciphertext, site_url
      FROM google_search_console_connections
      WHERE account_email = ?
      LIMIT 1
    `,
    [accountEmail],
  );
  const existing = existingRows[0];
  const refreshToken = token.refreshToken
    ? encryptSecret(token.refreshToken)
    : existing?.refresh_token_ciphertext;

  if (!refreshToken) {
    throw new Error("Google OAuth 갱신 토큰을 받지 못했습니다. Google 계정 권한을 해제한 뒤 다시 연결해 주세요.");
  }

  if (existing && existing.site_url !== config.siteUrl) {
    // Domain-property rows must never be mixed with the URL-prefix dashboard.
    await Promise.all([
      getDbPool().query(
        "DELETE FROM google_search_console_daily_metrics WHERE connection_id = ?",
        [existing.id],
      ),
      getDbPool().query(
        "DELETE FROM google_search_console_sync_runs WHERE connection_id = ?",
        [existing.id],
      ),
    ]);
  }

  await getDbPool().query(
    `
      INSERT INTO google_search_console_connections (
        account_email,
        site_url,
        access_token_ciphertext,
        refresh_token_ciphertext,
        token_expires_at,
        scopes,
        connected_at,
        last_sync_error
      ) VALUES (?, ?, ?, ?, DATE_ADD(NOW(), INTERVAL ? SECOND), ?, NOW(), NULL)
      ON DUPLICATE KEY UPDATE
        site_url = VALUES(site_url),
        access_token_ciphertext = VALUES(access_token_ciphertext),
        refresh_token_ciphertext = VALUES(refresh_token_ciphertext),
        token_expires_at = VALUES(token_expires_at),
        scopes = VALUES(scopes),
        connected_at = NOW(),
        last_sync_error = NULL,
        updated_at = NOW()
    `,
    [
      accountEmail,
      config.siteUrl,
      encryptSecret(token.accessToken),
      refreshToken,
      token.expiresIn,
      token.scopes,
    ],
  );

  return { siteUrl: config.siteUrl };
}

export async function getGoogleIntegrationAccess() {
  const connection = await getConnection();

  if (!connection) {
    return null;
  }

  return {
    accessToken: await getAccessToken(connection),
    accountEmail: connection.account_email,
    scopes: connection.scopes.split(/\s+/).filter(Boolean),
  };
}

async function getConnection() {
  await ensureGoogleSearchConsoleSchema();
  const [rows] = await getDbPool().query<GoogleSearchConsoleConnectionRow[]>(
    `
      SELECT
        id,
        account_email,
        site_url,
        access_token_ciphertext,
        refresh_token_ciphertext,
        token_expires_at,
        scopes,
        connected_at,
        last_synced_at,
        last_sync_error
      FROM google_search_console_connections
      ORDER BY connected_at DESC, id DESC
      LIMIT 1
    `,
  );

  return rows[0] ?? null;
}

async function refreshAccessToken(connection: GoogleSearchConsoleConnectionRow) {
  const config = getRuntimeConfig();

  if (!config) {
    throw new Error("Google Search Console OAuth 환경변수가 설정되지 않았습니다.");
  }

  const response = await fetch("https://oauth2.googleapis.com/token", {
    method: "POST",
    headers: { "content-type": "application/x-www-form-urlencoded" },
    body: new URLSearchParams({
      client_id: config.clientId,
      client_secret: config.clientSecret,
      grant_type: "refresh_token",
      refresh_token: decryptSecret(connection.refresh_token_ciphertext),
    }),
    cache: "no-store",
  });
  const payload = (await response.json().catch(() => ({}))) as GoogleTokenResponse;

  if (!response.ok || typeof payload.access_token !== "string") {
    throw new Error(String(payload.error_description || payload.error || "Google OAuth 토큰 갱신에 실패했습니다."));
  }

  const accessToken = payload.access_token;
  const expiresIn = Math.max(60, toNumber(payload.expires_in));
  await retrySearchConsoleDatabaseWrite(() => getDbPool().query(
    `
      UPDATE google_search_console_connections
      SET access_token_ciphertext = ?, token_expires_at = DATE_ADD(NOW(), INTERVAL ? SECOND), updated_at = NOW()
      WHERE id = ?
    `,
    [encryptSecret(accessToken), expiresIn, connection.id],
  ));

  return accessToken;
}

async function getAccessToken(connection: GoogleSearchConsoleConnectionRow) {
  const expiresAt = connection.token_expires_at?.getTime() ?? 0;

  if (expiresAt > Date.now() + TOKEN_EXPIRY_SKEW_MS) {
    return decryptSecret(connection.access_token_ciphertext);
  }

  return refreshAccessToken(connection);
}

async function requestUrlInspection(input: {
  accessToken: string;
  inspectionUrl: string;
  siteUrl: string;
}) {
  const response = await fetch(
    "https://searchconsole.googleapis.com/v1/urlInspection/index:inspect",
    {
      method: "POST",
      headers: {
        authorization: `Bearer ${input.accessToken}`,
        "content-type": "application/json",
      },
      body: JSON.stringify({
        inspectionUrl: input.inspectionUrl,
        languageCode: "ko-KR",
        siteUrl: input.siteUrl,
      }),
      cache: "no-store",
    },
  );
  const payload = (await response.json().catch(() => ({}))) as UrlInspectionResponse;

  if (!response.ok || !payload.inspectionResult) {
    throw new Error(String(payload.error?.message || "Google URL 색인 상태를 확인하지 못했습니다."));
  }

  return payload.inspectionResult;
}

async function upsertUrlInspection(input: {
  connectionId: number;
  inspectionUrl: string;
  result: NonNullable<UrlInspectionResponse["inspectionResult"]>;
}) {
  const status = input.result.indexStatusResult ?? {};
  const sitemaps = normalizeStringList(status.sitemap);
  const referringUrls = normalizeStringList(status.referringUrls);

  await retrySearchConsoleDatabaseWrite(() => getDbPool().query(
    `
      INSERT INTO google_search_console_url_inspections (
        connection_id,
        inspection_url,
        url_hash,
        verdict,
        coverage_state,
        robots_txt_state,
        indexing_state,
        page_fetch_state,
        last_crawl_time,
        google_canonical,
        user_canonical,
        crawled_as,
        sitemaps_json,
        referring_urls_json,
        inspection_result_link,
        inspection_error,
        last_inspected_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL, NOW())
      ON DUPLICATE KEY UPDATE
        inspection_url = VALUES(inspection_url),
        verdict = VALUES(verdict),
        coverage_state = VALUES(coverage_state),
        robots_txt_state = VALUES(robots_txt_state),
        indexing_state = VALUES(indexing_state),
        page_fetch_state = VALUES(page_fetch_state),
        last_crawl_time = VALUES(last_crawl_time),
        google_canonical = VALUES(google_canonical),
        user_canonical = VALUES(user_canonical),
        crawled_as = VALUES(crawled_as),
        sitemaps_json = VALUES(sitemaps_json),
        referring_urls_json = VALUES(referring_urls_json),
        inspection_result_link = VALUES(inspection_result_link),
        inspection_error = NULL,
        last_inspected_at = NOW(),
        updated_at = NOW()
    `,
    [
      input.connectionId,
      input.inspectionUrl,
      inspectionUrlHash(input.inspectionUrl),
      toNullableString(status.verdict, 32) ?? "VERDICT_UNSPECIFIED",
      toNullableString(status.coverageState, 255),
      toNullableString(status.robotsTxtState, 48),
      toNullableString(status.indexingState, 64),
      toNullableString(status.pageFetchState, 64),
      toNullableDatabaseDate(status.lastCrawlTime),
      toNullableString(status.googleCanonical),
      toNullableString(status.userCanonical),
      toNullableString(status.crawledAs, 48),
      JSON.stringify(sitemaps),
      JSON.stringify(referringUrls),
      toNullableString(input.result.inspectionResultLink, 1024),
    ],
  ));
}

async function upsertUrlInspectionError(input: {
  connectionId: number;
  error: unknown;
  inspectionUrl: string;
}) {
  await retrySearchConsoleDatabaseWrite(() => getDbPool().query(
    `
      INSERT INTO google_search_console_url_inspections (
        connection_id,
        inspection_url,
        url_hash,
        inspection_error,
        last_inspected_at
      ) VALUES (?, ?, ?, ?, NOW())
      ON DUPLICATE KEY UPDATE
        inspection_url = VALUES(inspection_url),
        inspection_error = VALUES(inspection_error),
        last_inspected_at = NOW(),
        updated_at = NOW()
    `,
    [
      input.connectionId,
      input.inspectionUrl,
      inspectionUrlHash(input.inspectionUrl),
      compactError(input.error),
    ],
  ));
}

async function requestSearchAnalyticsRange(input: {
  accessToken: string;
  dimensions: string[];
  endDate: string;
  siteUrl: string;
  startDate: string;
}) {
  const rows: SearchAnalyticsRow[] = [];

  for (let startRow = 0; ; startRow += SEARCH_CONSOLE_ROW_LIMIT) {
    const response = await fetch(
      `https://www.googleapis.com/webmasters/v3/sites/${encodeURIComponent(input.siteUrl)}/searchAnalytics/query`,
      {
        method: "POST",
        headers: {
          authorization: `Bearer ${input.accessToken}`,
          "content-type": "application/json",
        },
        body: JSON.stringify({
          dataState: "final",
          dimensions: ["date", ...input.dimensions],
          endDate: input.endDate,
          rowLimit: SEARCH_CONSOLE_ROW_LIMIT,
          startDate: input.startDate,
          startRow,
        }),
        cache: "no-store",
      },
    );
    const payload = (await response.json().catch(() => ({}))) as SearchAnalyticsResponse;

    if (!response.ok) {
      throw new Error(String(payload.error?.message || "Search Console 성과 데이터를 불러오지 못했습니다."));
    }

    const pageRows = payload.rows ?? [];
    rows.push(...pageRows);

    if (pageRows.length < SEARCH_CONSOLE_ROW_LIMIT) {
      break;
    }
  }

  return rows;
}

async function upsertDatedMetrics(input: {
  connectionId: number;
  dimensionType: "page" | "property" | "query" | "query-page";
  rows: SearchAnalyticsRow[];
}) {
  if (input.rows.length === 0) {
    return new Map<string, number>();
  }

  const countsByDate = new Map<string, number>();
  const values = input.rows.flatMap((row) => {
    const keys = Array.isArray(row.keys) ? row.keys.map((key) => String(key ?? "")) : [];
    const metricDate = keys.shift() ?? "";

    if (!/^\d{4}-\d{2}-\d{2}$/.test(metricDate)) {
      return [];
    }

    const key = keys[0] ?? "";
    const pageUrl = input.dimensionType === "page"
      ? key.slice(0, 768)
      : input.dimensionType === "query-page"
        ? (keys[1] ?? "").slice(0, 768)
        : null;
    const queryText = input.dimensionType === "query"
      ? key.slice(0, 768)
      : input.dimensionType === "query-page"
        ? key.slice(0, 768)
        : null;
    const rowKey = metricRowKey(input.dimensionType, keys.join("\u0000") || "property");
    countsByDate.set(metricDate, (countsByDate.get(metricDate) ?? 0) + 1);

    return [[
      input.connectionId,
      metricDate,
      input.dimensionType,
      rowKey,
      pageUrl,
      queryText,
      Math.max(0, Math.round(toNumber(row.clicks))),
      Math.max(0, Math.round(toNumber(row.impressions))),
      Math.max(0, toNumber(row.ctr)),
      Math.max(0, toNumber(row.position)),
    ]];
  });

  for (let index = 0; index < values.length; index += 1000) {
    const chunk = values.slice(index, index + 1000);
    await retrySearchConsoleDatabaseWrite(() => getDbPool().query(
      `
        INSERT INTO google_search_console_daily_metrics (
          connection_id, metric_date, dimension_type, row_key, page_url, query_text,
          clicks, impressions, ctr, position
        ) VALUES ?
        ON DUPLICATE KEY UPDATE
          page_url = VALUES(page_url),
          query_text = VALUES(query_text),
          clicks = VALUES(clicks),
          impressions = VALUES(impressions),
          ctr = VALUES(ctr),
          position = VALUES(position),
          updated_at = NOW()
      `,
      [chunk],
    ));
  }

  return countsByDate;
}

async function syncGoogleSearchConsolePerformanceLocked(connection: GoogleSearchConsoleConnectionRow) {
  const endDate = formatDate(shiftUtcDate(-SEARCH_CONSOLE_FINAL_DATA_DELAY_DAYS));
  const startDate = shiftDateString(endDate, -(SEARCH_CONSOLE_BACKFILL_DAYS - 1));
  const [successfulRows] = await getDbPool().query<SuccessfulSyncDateRow[]>(
    `
      SELECT DISTINCT DATE_FORMAT(sync_date, '%Y-%m-%d') AS sync_date
      FROM google_search_console_sync_runs
      WHERE connection_id = ?
        AND status = 'success'
        AND sync_date BETWEEN ? AND ?
    `,
    [connection.id, startDate, endDate],
  );
  const successfulDates = new Set(successfulRows.map((row) => row.sync_date));
  const pendingDates = dateRange(startDate, endDate).filter((date) => !successfulDates.has(date));

  if (pendingDates.length === 0) {
    await retrySearchConsoleDatabaseWrite(() => getDbPool().query(
      `
        UPDATE google_search_console_connections
        SET last_synced_at = NOW(), last_sync_error = NULL, updated_at = NOW()
        WHERE id = ?
      `,
      [connection.id],
    ));

    return {
      status: "synced" as const,
      summary: {
        backfilledDays: 0,
        endDate,
        pageRowCount: 0,
        propertyRowCount: 0,
        queryPageRowCount: 0,
        queryRowCount: 0,
        startDate,
      },
    };
  }

  try {
    const accessToken = await getAccessToken(connection);
    let pageRowCount = 0;
    let propertyRowCount = 0;
    let queryPageRowCount = 0;
    let queryRowCount = 0;

    for (const range of consecutiveDateRanges(pendingDates)) {
      const [propertyRows, pageRows, queryRows, queryPageRows] = await Promise.all([
        requestSearchAnalyticsRange({ accessToken, dimensions: [], siteUrl: connection.site_url, ...range }),
        requestSearchAnalyticsRange({ accessToken, dimensions: ["page"], siteUrl: connection.site_url, ...range }),
        requestSearchAnalyticsRange({ accessToken, dimensions: ["query"], siteUrl: connection.site_url, ...range }),
        requestSearchAnalyticsRange({ accessToken, dimensions: ["query", "page"], siteUrl: connection.site_url, ...range }),
      ]);
      // Keep writes ordered by dimension so concurrent sync callers cannot invert index lock order.
      const propertyCounts = await upsertDatedMetrics({ connectionId: connection.id, dimensionType: "property", rows: propertyRows });
      const pageCounts = await upsertDatedMetrics({ connectionId: connection.id, dimensionType: "page", rows: pageRows });
      const queryCounts = await upsertDatedMetrics({ connectionId: connection.id, dimensionType: "query", rows: queryRows });
      await upsertDatedMetrics({ connectionId: connection.id, dimensionType: "query-page", rows: queryPageRows });
      const syncedDates = dateRange(range.startDate, range.endDate);

      propertyRowCount += propertyRows.length;
      pageRowCount += pageRows.length;
      queryRowCount += queryRows.length;
      queryPageRowCount += queryPageRows.length;

      await retrySearchConsoleDatabaseWrite(() => getDbPool().query(
        `
          INSERT INTO google_search_console_sync_runs (
            connection_id, sync_date, status, property_row_count, page_row_count,
            query_row_count, started_at, finished_at
          ) VALUES ?
        `,
        [syncedDates.map((syncDate) => [
          connection.id,
          syncDate,
          "success",
          propertyCounts.get(syncDate) ?? 0,
          pageCounts.get(syncDate) ?? 0,
          queryCounts.get(syncDate) ?? 0,
          new Date(),
          new Date(),
        ])],
      ));
    }

    await retrySearchConsoleDatabaseWrite(() => getDbPool().query(
      `
        UPDATE google_search_console_connections
        SET last_synced_at = NOW(), last_sync_error = NULL, updated_at = NOW()
        WHERE id = ?
      `,
      [connection.id],
    ));

    return {
      status: "synced" as const,
      summary: {
        backfilledDays: pendingDates.length,
        endDate,
        pageRowCount,
        propertyRowCount,
        queryPageRowCount,
        queryRowCount,
        startDate,
      },
    };
  } catch (error) {
    const message = compactError(error);
    await retrySearchConsoleDatabaseWrite(() => getDbPool().query(
      `
        UPDATE google_search_console_connections
        SET last_sync_error = ?, updated_at = NOW()
        WHERE id = ?
      `,
      [message, connection.id],
    )).catch(() => undefined);
    await retrySearchConsoleDatabaseWrite(() => getDbPool().query(
      `
        INSERT INTO google_search_console_sync_runs (
          connection_id, sync_date, status, error_message, started_at, finished_at
        ) VALUES (?, ?, 'failed', ?, NOW(), NOW())
      `,
      [connection.id, pendingDates[pendingDates.length - 1] ?? endDate, message],
    )).catch(() => undefined);
    throw error;
  }
}

export async function syncGoogleSearchConsolePerformance() {
  const config = getRuntimeConfig();

  if (!config || !getSecretMaterial()) {
    return { status: "not-configured" as const };
  }

  const connection = await getConnection();

  if (!connection) {
    return { status: "not-connected" as const };
  }

  if (connection.site_url !== config.siteUrl) {
    return { status: "reconnect-required" as const };
  }

  const lockedSync = await withSearchConsoleSyncLock(connection.id, () =>
    syncGoogleSearchConsolePerformanceLocked(connection),
  );

  if (!lockedSync.acquired) {
    return { status: "sync-in-progress" as const };
  }

  const blogInspection = await inspectGoogleSearchConsoleBlogPosts().catch((error) => ({
    message: compactError(error),
    status: "inspection-failed" as const,
  }));

  return {
    ...lockedSync.result,
    blogInspection,
  };
}

function isPublishedBlogPost(post: MarketingBlogPost) {
  return (
    post.status === "published" &&
    Boolean(post.publishedAt) &&
    new Date(post.publishedAt ?? 0).getTime() <= Date.now()
  );
}

export async function inspectGoogleSearchConsoleBlogPosts({
  force = false,
  postId,
}: {
  force?: boolean;
  postId?: number;
} = {}) {
  const config = getRuntimeConfig();

  if (!config || !getSecretMaterial()) {
    return { status: "not-configured" as const };
  }

  const connection = await getConnection();

  if (!connection) {
    return { status: "not-connected" as const };
  }

  if (connection.site_url !== config.siteUrl) {
    return { status: "reconnect-required" as const };
  }

  const requestedPosts = postId
    ? [await getMarketingBlogPostById(postId)].filter((post): post is MarketingBlogPost => Boolean(post))
    : await getPublishedMarketingBlogPosts();
  const posts = requestedPosts.filter(isPublishedBlogPost);

  if (posts.length === 0) {
    return { status: postId ? "not-published" as const : "no-published-posts" as const };
  }

  const urls = posts.map((post) => getBlogPostUrl(post.slug));
  const [inspectionRows] = await getDbPool().query<UrlInspectionRow[]>(
    `
      SELECT
        inspection_url,
        verdict,
        coverage_state,
        robots_txt_state,
        indexing_state,
        page_fetch_state,
        last_crawl_time,
        google_canonical,
        user_canonical,
        sitemaps_json,
        inspection_result_link,
        inspection_error,
        last_inspected_at
      FROM google_search_console_url_inspections
      WHERE connection_id = ?
        AND inspection_url IN (?)
    `,
    [connection.id, urls],
  );
  const existingInspections = new Map(
    inspectionRows.map((row) => [row.inspection_url, row]),
  );
  const accessToken = await getAccessToken(connection);
  let failed = 0;
  let inspected = 0;
  let skipped = 0;
  const pendingUrls = urls.filter((inspectionUrl) => {
    const existing = existingInspections.get(inspectionUrl);
    const isFresh = existing
      ? Date.now() - existing.last_inspected_at.getTime() < URL_INSPECTION_FRESHNESS_MS
      : false;

    if (!force && isFresh) {
      skipped += 1;
      return false;
    }

    return true;
  });

  for (let index = 0; index < pendingUrls.length; index += URL_INSPECTION_CONCURRENCY) {
    const batch = pendingUrls.slice(index, index + URL_INSPECTION_CONCURRENCY);
    await Promise.all(batch.map(async (inspectionUrl) => {
      try {
        const result = await requestUrlInspection({
          accessToken,
          inspectionUrl,
          siteUrl: connection.site_url,
        });
        await upsertUrlInspection({
          connectionId: connection.id,
          inspectionUrl,
          result,
        });
        inspected += 1;
      } catch (error) {
        await upsertUrlInspectionError({
          connectionId: connection.id,
          error,
          inspectionUrl,
        });
        failed += 1;
      }
    }));
  }

  return {
    status: failed > 0 ? "completed-with-errors" as const : "completed" as const,
    summary: { failed, inspected, skipped },
  };
}

export async function getKavenixGoogleSearchConsoleBlogStatus(
  posts: MarketingBlogPost[],
): Promise<GoogleSearchConsoleBlogStatus> {
  await ensureGoogleSearchConsoleSchema();
  const config = getRuntimeConfig();
  const connection = await getConnection();
  const targetSiteUrl = config?.siteUrl ?? OFFICIALSITE_URL_PREFIX;
  const connected = Boolean(
    connection &&
    config &&
    connection.site_url === config.siteUrl,
  );
  const urls = posts.map((post) => getBlogPostUrl(post.slug));

  if (!connection || !connected || urls.length === 0) {
    return {
      connected,
      posts: posts.map((post) => ({
        clicks: 0,
        coverageState: null,
        googleCanonical: null,
        googleDetectedSitemap: false,
        impressions: 0,
        indexVerdict: null,
        indexingState: null,
        inspectionError: null,
        inspectionResultLink: null,
        isIndexed: false,
        lastCrawlTime: null,
        lastInspectedAt: null,
        pageFetchState: null,
        position: 0,
        postId: post.id,
        published: isPublishedBlogPost(post),
        robotsTxtState: null,
        sitemapIncluded: isPublishedBlogPost(post),
        url: getBlogPostUrl(post.slug),
        userCanonical: null,
      })),
      targetSiteUrl,
    };
  }

  const startDate = formatDate(shiftUtcDate(-31));
  const [metricResult, inspectionResult] = await Promise.all([
    getDbPool().query<BlogPageMetricRow[]>(
      `
        SELECT
          page_url,
          COALESCE(SUM(clicks), 0) AS clicks,
          COALESCE(SUM(impressions), 0) AS impressions,
          COALESCE(SUM(position * impressions) / NULLIF(SUM(impressions), 0), 0) AS weighted_position
        FROM google_search_console_daily_metrics
        WHERE connection_id = ?
          AND dimension_type = 'page'
          AND metric_date >= ?
          AND page_url IN (?)
        GROUP BY page_url
      `,
      [connection.id, startDate, urls],
    ),
    getDbPool().query<UrlInspectionRow[]>(
      `
        SELECT
          inspection_url,
          verdict,
          coverage_state,
          robots_txt_state,
          indexing_state,
          page_fetch_state,
          last_crawl_time,
          google_canonical,
          user_canonical,
          sitemaps_json,
          inspection_result_link,
          inspection_error,
          last_inspected_at
        FROM google_search_console_url_inspections
        WHERE connection_id = ?
          AND inspection_url IN (?)
      `,
      [connection.id, urls],
    ),
  ]);
  const metricsByUrl = new Map(metricResult[0].map((row) => [row.page_url, row]));
  const inspectionsByUrl = new Map(
    inspectionResult[0].map((row) => [row.inspection_url, row]),
  );

  return {
    connected,
    posts: posts.map((post) => {
      const url = getBlogPostUrl(post.slug);
      const metric = metricsByUrl.get(url);
      const inspection = inspectionsByUrl.get(url);
      const sitemaps = parseStoredStringList(inspection?.sitemaps_json ?? null);
      const published = isPublishedBlogPost(post);

      return {
        clicks: toNumber(metric?.clicks),
        coverageState: inspection?.coverage_state ?? null,
        googleCanonical: inspection?.google_canonical ?? null,
        googleDetectedSitemap: sitemaps.length > 0,
        impressions: toNumber(metric?.impressions),
        indexVerdict: inspection?.verdict ?? null,
        indexingState: inspection?.indexing_state ?? null,
        inspectionError: inspection?.inspection_error ?? null,
        inspectionResultLink: inspection?.inspection_result_link ?? null,
        isIndexed: inspection?.verdict === "PASS",
        lastCrawlTime: toIso(inspection?.last_crawl_time ?? null),
        lastInspectedAt: toIso(inspection?.last_inspected_at ?? null),
        pageFetchState: inspection?.page_fetch_state ?? null,
        position: toNumber(metric?.weighted_position),
        postId: post.id,
        published,
        robotsTxtState: inspection?.robots_txt_state ?? null,
        sitemapIncluded: published,
        url,
        userCanonical: inspection?.user_canonical ?? null,
      };
    }),
    targetSiteUrl,
  };
}

export async function getKavenixGoogleSearchConsoleAnalytics(
  requestedRangeDays: number = 28,
): Promise<GoogleSearchConsoleAnalytics> {
  await ensureGoogleSearchConsoleSchema();
  const config = getRuntimeConfig();
  const connection = await getConnection();
  const targetSiteUrl = config?.siteUrl ?? OFFICIALSITE_URL_PREFIX;
  const rangeDays = ([7, 28, 90] as const).find((value) => value === requestedRangeDays) ?? 28;
  const emptyMetrics = metricSummary();
  const reconnectRequired = Boolean(
    connection && config && connection.site_url !== config.siteUrl,
  );

  if (!connection || reconnectRequired) {
    return {
      actionItems: [],
      brandPerformance: {
        branded: emptyMetrics,
        hiddenClicks: 0,
        knownQueryClicks: 0,
        nonBranded: emptyMetrics,
      },
      connection: null,
      configured: isGoogleSearchConsoleConfigured(),
      contentCoverage: { indexedPosts: 0, inspectionErrors: 0, pendingPosts: 0, publishedPosts: 0 },
      dailyMetrics: [],
      dataWindow: {
        availableDays: 0,
        endDate: "",
        expectedDays: rangeDays,
        previousAvailableDays: 0,
        startDate: "",
      },
      metrics: emptyMetrics,
      opportunityQueries: [],
      previousMetrics: emptyMetrics,
      rangeDays,
      reconnectRequired,
      topPages: [],
      topQueries: [],
      targetSiteUrl,
    };
  }

  const endDate = formatDate(shiftUtcDate(-SEARCH_CONSOLE_FINAL_DATA_DELAY_DAYS));
  const startDate = shiftDateString(endDate, -(rangeDays - 1));
  const previousEndDate = shiftDateString(startDate, -1);
  const previousStartDate = shiftDateString(previousEndDate, -(rangeDays - 1));
  const posts = await getPublishedMarketingBlogPosts();
  const postUrls = posts.map((post) => getBlogPostUrl(post.slug));
  const [
    aggregateRows,
    previousAggregateRows,
    dailyRows,
    pageRows,
    queryRows,
    queryPageRows,
    coverageRows,
    previousCoverageRows,
    inspectionRows,
  ] = await Promise.all([
    getDbPool().query<AggregateMetricRow[]>(
      `
        SELECT
          COALESCE(SUM(clicks), 0) AS clicks,
          COALESCE(SUM(impressions), 0) AS impressions,
          COALESCE(SUM(position * impressions) / NULLIF(SUM(impressions), 0), 0) AS weighted_position
        FROM google_search_console_daily_metrics
        WHERE connection_id = ?
          AND dimension_type = 'property'
          AND metric_date BETWEEN ? AND ?
      `,
      [connection.id, startDate, endDate],
    ),
    getDbPool().query<AggregateMetricRow[]>(
      `
        SELECT
          COALESCE(SUM(clicks), 0) AS clicks,
          COALESCE(SUM(impressions), 0) AS impressions,
          COALESCE(SUM(position * impressions) / NULLIF(SUM(impressions), 0), 0) AS weighted_position
        FROM google_search_console_daily_metrics
        WHERE connection_id = ?
          AND dimension_type = 'property'
          AND metric_date BETWEEN ? AND ?
      `,
      [connection.id, previousStartDate, previousEndDate],
    ),
    getDbPool().query<DailyMetricRow[]>(
      `
        SELECT
          DATE_FORMAT(metric_date, '%Y-%m-%d') AS metric_date,
          COALESCE(SUM(clicks), 0) AS clicks,
          COALESCE(SUM(impressions), 0) AS impressions,
          COALESCE(SUM(position * impressions) / NULLIF(SUM(impressions), 0), 0) AS weighted_position
        FROM google_search_console_daily_metrics
        WHERE connection_id = ?
          AND dimension_type = 'property'
          AND metric_date BETWEEN ? AND ?
        GROUP BY metric_date
        ORDER BY metric_date ASC
      `,
      [connection.id, startDate, endDate],
    ),
    getDbPool().query<RankedMetricRow[]>(
      `
        SELECT
          page_url,
          SUM(clicks) AS clicks,
          SUM(impressions) AS impressions,
          SUM(clicks) / NULLIF(SUM(impressions), 0) AS ctr,
          SUM(position * impressions) / NULLIF(SUM(impressions), 0) AS position
        FROM google_search_console_daily_metrics
        WHERE connection_id = ?
          AND dimension_type = 'page'
          AND metric_date BETWEEN ? AND ?
        GROUP BY page_url
        ORDER BY impressions DESC, clicks DESC
        LIMIT 20
      `,
      [connection.id, startDate, endDate],
    ),
    getDbPool().query<RankedMetricRow[]>(
      `
        SELECT
          query_text,
          SUM(clicks) AS clicks,
          SUM(impressions) AS impressions,
          SUM(clicks) / NULLIF(SUM(impressions), 0) AS ctr,
          SUM(position * impressions) / NULLIF(SUM(impressions), 0) AS position
        FROM google_search_console_daily_metrics
        WHERE connection_id = ?
          AND dimension_type = 'query'
          AND metric_date BETWEEN ? AND ?
        GROUP BY query_text
        ORDER BY impressions DESC, clicks DESC
        LIMIT 2000
      `,
      [connection.id, startDate, endDate],
    ),
    getDbPool().query<RankedMetricRow[]>(
      `
        SELECT
          query_text,
          page_url,
          SUM(clicks) AS clicks,
          SUM(impressions) AS impressions,
          SUM(clicks) / NULLIF(SUM(impressions), 0) AS ctr,
          SUM(position * impressions) / NULLIF(SUM(impressions), 0) AS position
        FROM google_search_console_daily_metrics
        WHERE connection_id = ?
          AND dimension_type = 'query-page'
          AND metric_date BETWEEN ? AND ?
        GROUP BY query_text, page_url
        ORDER BY impressions DESC
        LIMIT 500
      `,
      [connection.id, startDate, endDate],
    ),
    getDbPool().query<SyncCoverageRow[]>(
      `
        SELECT COUNT(DISTINCT sync_date) AS available_days
        FROM google_search_console_sync_runs
        WHERE connection_id = ?
          AND status = 'success'
          AND sync_date BETWEEN ? AND ?
      `,
      [connection.id, startDate, endDate],
    ),
    getDbPool().query<SyncCoverageRow[]>(
      `
        SELECT COUNT(DISTINCT sync_date) AS available_days
        FROM google_search_console_sync_runs
        WHERE connection_id = ?
          AND status = 'success'
          AND sync_date BETWEEN ? AND ?
      `,
      [connection.id, previousStartDate, previousEndDate],
    ),
    postUrls.length > 0
      ? getDbPool().query<UrlInspectionRow[]>(
        `
          SELECT
            inspection_url,
            verdict,
            coverage_state,
            robots_txt_state,
            indexing_state,
            page_fetch_state,
            last_crawl_time,
            google_canonical,
            user_canonical,
            sitemaps_json,
            inspection_result_link,
            inspection_error,
            last_inspected_at
          FROM google_search_console_url_inspections
          WHERE connection_id = ?
            AND inspection_url IN (?)
        `,
        [connection.id, postUrls],
      )
      : Promise.resolve([[] as UrlInspectionRow[]]),
  ]);
  const metrics = metricSummary(aggregateRows[0][0]);
  const previousMetrics = metricSummary(previousAggregateRows[0][0]);
  const normalizedQueries = normalizeQueryRows(queryRows[0]);
  const brandedRows = queryRows[0].filter((row) => isBrandedQuery(row.query_text ?? ""));
  const nonBrandedRows = queryRows[0].filter((row) => !isBrandedQuery(row.query_text ?? ""));
  const knownQueryClicks = normalizedQueries.reduce((total, row) => total + row.clicks, 0);
  const inspections = inspectionRows[0];
  const indexedPosts = inspections.filter((row) => row.verdict === "PASS").length;
  const inspectionErrors = inspections.filter((row) => Boolean(row.inspection_error)).length;
  const opportunityRows = queryRows[0]
    .filter((row) => {
      const query = row.query_text ?? "";
      const position = toNumber(row.position);
      return !isBrandedQuery(query) && toNumber(row.impressions) >= 1 && position >= 4 && position <= 40;
    })
    .slice(0, 12);

  return {
    actionItems: buildSearchActionItems({
      opportunityRows,
      pairRows: queryPageRows[0],
      posts,
    }),
    brandPerformance: {
      branded: rankedRowsSummary(brandedRows),
      hiddenClicks: Math.max(0, metrics.clicks - knownQueryClicks),
      knownQueryClicks,
      nonBranded: rankedRowsSummary(nonBrandedRows),
    },
    connection: {
      accountEmail: connection.account_email,
      connectedAt: connection.connected_at.toISOString(),
      lastSyncError: connection.last_sync_error,
      lastSyncedAt: toIso(connection.last_synced_at),
      siteUrl: connection.site_url,
    },
    configured: isGoogleSearchConsoleConfigured(),
    contentCoverage: {
      indexedPosts,
      inspectionErrors,
      pendingPosts: Math.max(0, posts.length - indexedPosts - inspectionErrors),
      publishedPosts: posts.length,
    },
    dailyMetrics: dailyRows[0].map((row) => ({
      date: row.metric_date,
      ...metricSummary(row),
    })),
    dataWindow: {
      availableDays: toNumber(coverageRows[0][0]?.available_days),
      endDate,
      expectedDays: rangeDays,
      previousAvailableDays: toNumber(previousCoverageRows[0][0]?.available_days),
      startDate,
    },
    metrics,
    opportunityQueries: normalizeQueryRows(opportunityRows),
    previousMetrics,
    rangeDays,
    reconnectRequired: false,
    topPages: normalizePageRows(pageRows[0]).slice(0, 10),
    topQueries: normalizedQueries.slice(0, 10),
    targetSiteUrl,
  };
}