import "server-only";

import { createCipheriv, createDecipheriv, createHash, createHmac, randomBytes, timingSafeEqual } from "crypto";
import type { ResultSetHeader, RowDataPacket } from "mysql2/promise";
import { ensureGoogleSearchConsoleSchema, getDbPool } from "@/lib/db";

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

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;

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;
  site_url: string;
  token_expires_at: Date | null;
};

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

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;
};

export type GoogleSearchConsoleAnalytics = {
  connection: {
    accountEmail: string;
    connectedAt: string;
    lastSyncError: string | null;
    lastSyncedAt: string | null;
    siteUrl: string;
  } | null;
  configured: boolean;
  reconnectRequired: boolean;
  metrics: {
    clicks: number;
    ctr: number;
    impressions: number;
    position: number;
  };
  opportunityQueries: Array<{
    clicks: number;
    ctr: number;
    impressions: number;
    position: number;
    query: string;
  }>;
  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;
};

type OAuthState = {
  email: string;
  issuedAt: number;
};

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 metricRowKey(dimensionType: string, value: string) {
  return createHash("sha256").update(`${dimensionType}:${value}`).digest("hex");
}

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 ?? "-",
  }));
}

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

export function createGoogleSearchConsoleOAuthState(email: string) {
  const payload = Buffer.from(
    JSON.stringify({ email: email.trim().toLowerCase(), issuedAt: Date.now() } 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 }) {
  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);
  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));

  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 };
}

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,
        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 requestSearchAnalytics(input: {
  accessToken: string;
  dimensions: string[];
  siteUrl: string;
  syncDate: string;
}) {
  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: input.dimensions,
        endDate: input.syncDate,
        rowLimit: 25000,
        startDate: input.syncDate,
      }),
      cache: "no-store",
    },
  );
  const payload = (await response.json().catch(() => ({}))) as SearchAnalyticsResponse;

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

  return payload.rows ?? [];
}

async function upsertMetrics(input: {
  connectionId: number;
  dimensionType: "page" | "property" | "query";
  rows: SearchAnalyticsRow[];
  syncDate: string;
}) {
  if (input.rows.length === 0) {
    return 0;
  }

  const values = input.rows.map((row) => {
    const key = Array.isArray(row.keys) ? String(row.keys[0] ?? "") : "";
    const pageUrl = input.dimensionType === "page" ? key.slice(0, 768) : null;
    const queryText = input.dimensionType === "query" ? key.slice(0, 768) : null;
    const rowKey = metricRowKey(input.dimensionType, key || "property");

    return [
      input.connectionId,
      input.syncDate,
      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)),
    ];
  });

  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()
    `,
    [values],
  ));

  return values.length;
}

async function syncGoogleSearchConsolePerformanceLocked(connection: GoogleSearchConsoleConnectionRow) {
  const syncDate = formatDate(shiftUtcDate(-3));
  const [run] = await retrySearchConsoleDatabaseWrite(() => getDbPool().query<ResultSetHeader>(
    `
      INSERT INTO google_search_console_sync_runs (connection_id, sync_date, status)
      VALUES (?, ?, 'failed')
    `,
    [connection.id, syncDate],
  ));

  try {
    const accessToken = await getAccessToken(connection);
    const [propertyRows, pageRows, queryRows] = await Promise.all([
      requestSearchAnalytics({ accessToken, dimensions: [], siteUrl: connection.site_url, syncDate }),
      requestSearchAnalytics({ accessToken, dimensions: ["page"], siteUrl: connection.site_url, syncDate }),
      requestSearchAnalytics({ accessToken, dimensions: ["query"], siteUrl: connection.site_url, syncDate }),
    ]);
    // Keep the unique-index write order stable for every sync process.
    const propertyRowCount = await upsertMetrics({ connectionId: connection.id, dimensionType: "property", rows: propertyRows, syncDate });
    const pageRowCount = await upsertMetrics({ connectionId: connection.id, dimensionType: "page", rows: pageRows, syncDate });
    const queryRowCount = await upsertMetrics({ connectionId: connection.id, dimensionType: "query", rows: queryRows, syncDate });

    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],
    ));
    await retrySearchConsoleDatabaseWrite(() => getDbPool().query(
      `
        UPDATE google_search_console_sync_runs
        SET
          status = 'success',
          property_row_count = ?,
          page_row_count = ?,
          query_row_count = ?,
          finished_at = NOW()
        WHERE id = ?
      `,
      [propertyRowCount, pageRowCount, queryRowCount, run.insertId],
    ));

    return {
      status: "synced" as const,
      summary: { pageRowCount, propertyRowCount, queryRowCount, syncDate },
    };
  } 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(
      `
        UPDATE google_search_console_sync_runs
        SET error_message = ?, finished_at = NOW()
        WHERE id = ?
      `,
      [message, run.insertId],
    )).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 };
  }

  return lockedSync.result;
}

export async function getKavenixGoogleSearchConsoleAnalytics(): Promise<GoogleSearchConsoleAnalytics> {
  await ensureGoogleSearchConsoleSchema();
  const config = getRuntimeConfig();
  const connection = await getConnection();
  const targetSiteUrl = config?.siteUrl ?? OFFICIALSITE_URL_PREFIX;
  const reconnectRequired = Boolean(
    connection && config && connection.site_url !== config.siteUrl,
  );

  if (!connection || reconnectRequired) {
    return {
      connection: null,
      configured: isGoogleSearchConsoleConfigured(),
      metrics: { clicks: 0, ctr: 0, impressions: 0, position: 0 },
      opportunityQueries: [],
      reconnectRequired,
      topPages: [],
      topQueries: [],
      targetSiteUrl,
    };
  }

  const startDate = formatDate(shiftUtcDate(-31));
  const [aggregateRows, pageRows, queryRows, opportunityRows] = 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 >= ?
      `,
      [connection.id, startDate],
    ),
    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 >= ?
        GROUP BY page_url
        ORDER BY clicks DESC, impressions DESC
        LIMIT 10
      `,
      [connection.id, startDate],
    ),
    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 >= ?
        GROUP BY query_text
        ORDER BY clicks DESC, impressions DESC
        LIMIT 10
      `,
      [connection.id, startDate],
    ),
    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 >= ?
        GROUP BY query_text
        HAVING SUM(impressions) >= 10
          AND SUM(position * impressions) / NULLIF(SUM(impressions), 0) BETWEEN 6 AND 20
        ORDER BY impressions DESC
        LIMIT 10
      `,
      [connection.id, startDate],
    ),
  ]);
  const aggregate = aggregateRows[0][0];
  const impressions = toNumber(aggregate?.impressions);
  const clicks = toNumber(aggregate?.clicks);

  return {
    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(),
    metrics: {
      clicks,
      ctr: impressions > 0 ? clicks / impressions : 0,
      impressions,
      position: toNumber(aggregate?.weighted_position),
    },
    opportunityQueries: normalizeQueryRows(opportunityRows[0]),
    reconnectRequired: false,
    topPages: normalizePageRows(pageRows[0]),
    topQueries: normalizeQueryRows(queryRows[0]),
    targetSiteUrl,
  };
}