// Next.js API route support: https://nextjs.org/docs/api-routes/introduction
import { connection } from "@/lib/duckdb";
import type { NextApiRequest, NextApiResponse } from "next";

export default async function handler(
  req: NextApiRequest,
  res: NextApiResponse,
) {
  const result = await connection.run(`
        SELECT 
          menu_item.ID AS menu_item_id,
          menu_item.post_title AS menu_title,
          menu_item.menu_order,
          menu_item.post_parent,
          -- Get the actual page/post content
          actual_post.ID AS actual_post_id,
          actual_post.post_title AS actual_post_title,
          actual_post.post_type,
          actual_post.post_status,
          actual_post.post_name AS slug,
          -- Get the URL (for custom links)
          url_meta.meta_value AS custom_url,
          -- Get the object type (post, page, category, custom, etc.)
          object_type_meta.meta_value AS object_type,
          -- Get parent menu item info
          menu_parent_meta.meta_value AS parent_menu_item_id,
      FROM wp_posts AS menu_item
      INNER JOIN wp_term_relationships r ON menu_item.ID = r.object_id
      -- Join to get the actual post ID
      INNER JOIN wp_postmeta AS object_id_meta ON menu_item.ID = object_id_meta.post_id 
          AND object_id_meta.meta_key = '_menu_item_object_id'
      -- Join to get the actual post content
      INNER JOIN wp_posts AS actual_post ON object_id_meta.meta_value = actual_post.ID
      -- Left joins for additional info
      LEFT JOIN wp_postmeta AS url_meta ON menu_item.ID = url_meta.post_id 
          AND url_meta.meta_key = '_menu_item_url'
      LEFT JOIN wp_postmeta AS object_type_meta ON menu_item.ID = object_type_meta.post_id 
          AND object_type_meta.meta_key = '_menu_item_object'
      LEFT JOIN wp_postmeta AS menu_parent_meta ON menu_item.ID = menu_parent_meta.post_id 
          AND menu_parent_meta.meta_key = '_menu_item_menu_item_parent'
      WHERE r.term_taxonomy_id = 24
        AND menu_item.post_type = 'nav_menu_item'
        AND menu_item.post_status = 'publish'
      ORDER BY menu_item.menu_order;`)
  const rows = await result.getRowObjectsJson()
  res.status(200).json(rows);
}
