import db from "./db";

export interface Blog {
  id: number;
  title: string;
  blogImage: string | null;
  blogCategoryId: number | null;
  description: string | null;
  viewer: number;
  author: string | null;
  postedOn: string | null;
  isActive: number;
  isDelete: number;
  created_at: string;
  updated_at: string;
  createdBy: number | null;
  modifiedBy: number | null;
}

export interface BlogPagination {
  blogs: Blog[];
  currentPage: number;
  perPage: number;
  totalBlogs: number;
  totalPages: number;
}

export async function getBlogs(
  page: number = 1,
  perPage: number = 12
): Promise<BlogPagination> {
  // Validate page
  let currentPage = Number(page);

  if (!Number.isInteger(currentPage) || currentPage < 1) {
    currentPage = 1;
  }

  // Validate per page
  const itemsPerPage = 12;

  // Get total blogs
  const [countRows] = await db.execute(`
    SELECT COUNT(*) AS total
    FROM blogs
    WHERE isActive = 1
      AND isDelete = 0
  `);

  const totalBlogs = Number(
    (countRows as { total: number }[])[0]?.total || 0
  );

  const totalPages = Math.ceil(
    totalBlogs / itemsPerPage
  );

  // If no blogs
  if (totalBlogs === 0) {
    return {
      blogs: [],
      currentPage: 1,
      perPage: itemsPerPage,
      totalBlogs: 0,
      totalPages: 0,
    };
  }

  // Prevent page greater than total pages
  if (currentPage > totalPages) {
    currentPage = totalPages;
  }

  const offset = (currentPage - 1) * itemsPerPage;

  // IMPORTANT:
  // LIMIT/OFFSET are validated integers before being
  // inserted into the SQL query.
  const limit = Number(itemsPerPage);
  const safeOffset = Number(offset);

  const [rows] = await db.query(`
    SELECT
      id,
      title,
      blogImage,
      blogCategoryId,
      description,
      viewer,
      author,
      postedOn,
      isActive,
      isDelete,
      created_at,
      updated_at,
      createdBy,
      modifiedBy
    FROM blogs
    WHERE isActive = 1
      AND isDelete = 0
    ORDER BY postedOn DESC, id DESC
    LIMIT ${limit} OFFSET ${safeOffset}
  `);

  return {
    blogs: rows as Blog[],
    currentPage,
    perPage: itemsPerPage,
    totalBlogs,
    totalPages,
  };
}