import { ensureSchema } from "@/db/ensure-schema";
import { env } from "@/lib/runtime-env";
import { publicCmsMediaUrl } from "@/lib/public-media-url";

export type PublicPortfolioProject = {
  id: number;
  title: string;
  location: string;
  category: string;
  description: string;
  image_url: string;
  alt_text: string;
  featured: boolean | number;
  published_at: number;
  updated_at: number;
};

const publicFields = "id, title, location, category, description, image_url, alt_text, featured, published_at, updated_at";

export async function getPublishedPortfolioProjects() {
  await ensureSchema();
  const projects = await env.DB.prepare(
    `SELECT ${publicFields} FROM cms_portfolio WHERE status = 'published' ORDER BY published_at DESC, id DESC`,
  ).all<PublicPortfolioProject>();
  return projects.results.map((project) => ({ ...project, image_url: publicCmsMediaUrl(project.image_url) }));
}

export async function getPublishedPortfolioProject(id: number) {
  if (!Number.isSafeInteger(id) || id <= 0) return null;
  await ensureSchema();
  const project = await env.DB.prepare(
    `SELECT ${publicFields} FROM cms_portfolio WHERE id = ? AND status = 'published' LIMIT 1`,
  ).bind(id).first<PublicPortfolioProject>();
  return project ? { ...project, image_url: publicCmsMediaUrl(project.image_url) } : null;
}