import { randomUUID } from "crypto";
import type { RowDataPacket } from "mysql2/promise";
import { execute, queryRows } from "@/lib/db/pool";
import type { ProviderPostStats } from "@/lib/storage/provider-post-stats";

interface StatsRow extends RowDataPacket {
  project_id: string;
  platform: string;
  success_count: number;
  failure_count: number;
  last_success_at: string | null;
  last_failure_at: string | null;
  last_error: string | null;
  last_post_id: string | null;
  last_permalink: string | null;
}

function mapRow(row: StatsRow): ProviderPostStats {
  return {
    projectId: row.project_id,
    platform: row.platform,
    successCount: row.success_count,
    failureCount: row.failure_count,
    lastSuccessAt: row.last_success_at ?? undefined,
    lastFailureAt: row.last_failure_at ?? undefined,
    lastError: row.last_error ?? undefined,
    lastPostId: row.last_post_id ?? undefined,
    lastPermalink: row.last_permalink ?? undefined,
  };
}

export async function loadProviderPostStatsFromDb(projectId: string): Promise<ProviderPostStats[]> {
  const rows = await queryRows<StatsRow[]>(
    "SELECT * FROM provider_post_stats WHERE project_id = ?",
    [projectId],
  );
  return rows.map(mapRow);
}

export async function recordProviderPostSuccessToDb(input: {
  projectId: string;
  platform: string;
  postId?: string;
  permalink?: string;
}): Promise<ProviderPostStats> {
  const id = randomUUID();
  await execute(
    `INSERT INTO provider_post_stats (
       id, project_id, platform, success_count, failure_count, last_success_at, last_post_id, last_permalink
     ) VALUES (?, ?, ?, 1, 0, CURRENT_TIMESTAMP, ?, ?)
     ON DUPLICATE KEY UPDATE
       success_count = success_count + 1,
       last_success_at = CURRENT_TIMESTAMP,
       last_post_id = VALUES(last_post_id),
       last_permalink = VALUES(last_permalink)`,
    [id, input.projectId, input.platform, input.postId ?? null, input.permalink ?? null],
  );
  const rows = await loadProviderPostStatsFromDb(input.projectId);
  return rows.find((r) => r.platform === input.platform)!;
}

export async function recordProviderPostFailureToDb(input: {
  projectId: string;
  platform: string;
  error: string;
}): Promise<ProviderPostStats> {
  const id = randomUUID();
  await execute(
    `INSERT INTO provider_post_stats (
       id, project_id, platform, success_count, failure_count, last_failure_at, last_error
     ) VALUES (?, ?, ?, 0, 1, CURRENT_TIMESTAMP, ?)
     ON DUPLICATE KEY UPDATE
       failure_count = failure_count + 1,
       last_failure_at = CURRENT_TIMESTAMP,
       last_error = VALUES(last_error)`,
    [id, input.projectId, input.platform, input.error],
  );
  const rows = await loadProviderPostStatsFromDb(input.projectId);
  return rows.find((r) => r.platform === input.platform)!;
}