import type { APIRoute } from "astro";

interface UsageCount {
  template_name: string;
  copies: number;
  downloads: number;
  carts: number;
}

export const GET: APIRoute = async ({ url, locals }) => {
  try {
    // Get D1 binding from Cloudflare runtime
    const db = (locals as any).runtime?.env?.DB;
    if (!db) {
      // Fallback: return empty stats (dev mode without D1)
      return new Response(JSON.stringify({}), {
        status: 200,
        headers: {
          "Content-Type": "application/json",
          "Cache-Control": "public, max-age=60", // Cache 1 minute
        },
      });
    }

    // Parse query params
    const templates = url.searchParams.get("templates");

    let result: Record<
      string,
      { copies: number; downloads: number; carts: number; total: number }
    >;

    if (templates) {
      // Fetch specific templates
      const names = templates
        .split(",")
        .map((n) => n.trim())
        .filter(Boolean);
      if (names.length === 0) {
        return new Response(JSON.stringify({}), {
          status: 200,
          headers: { "Content-Type": "application/json" },
        });
      }

      // Build placeholders for IN clause
      const placeholders = names.map(() => "?").join(",");
      const { results } = await db
        .prepare(
          `SELECT template_name, copies, downloads, carts FROM usage_counts WHERE template_name IN (${placeholders})`,
        )
        .bind(...names)
        .all<UsageCount>();

      result = {};
      for (const row of results || []) {
        const total =
          (row.copies || 0) + (row.downloads || 0) + (row.carts || 0);
        result[row.template_name] = {
          copies: row.copies || 0,
          downloads: row.downloads || 0,
          carts: row.carts || 0,
          total,
        };
      }
    } else {
      // Fetch all (for leaderboard/sorting) - compute total in query
      const { results } = await db
        .prepare(
          "SELECT template_name, copies, downloads, carts FROM usage_counts ORDER BY (copies + downloads + carts) DESC LIMIT 100",
        )
        .all<UsageCount>();

      result = {};
      for (const row of results || []) {
        const total =
          (row.copies || 0) + (row.downloads || 0) + (row.carts || 0);
        result[row.template_name] = {
          copies: row.copies || 0,
          downloads: row.downloads || 0,
          carts: row.carts || 0,
          total,
        };
      }
    }

    return new Response(JSON.stringify(result), {
      status: 200,
      headers: {
        "Content-Type": "application/json",
        "Cache-Control": "public, max-age=60", // Cache 1 minute
      },
    });
  } catch (error) {
    // Silently return empty stats in dev mode (no D1 database)
    const errorMessage = String(error);
    if (!errorMessage.includes("no such table")) {
      console.error("Stats error:", error);
    }

    // Return empty stats gracefully
    return new Response(JSON.stringify({}), {
      status: 200,
      headers: {
        "Content-Type": "application/json",
        "Cache-Control": "public, max-age=60",
      },
    });
  }
};

// Disable prerendering for this API route
export const prerender = false;