import { NextRequest } from 'next/server';
import pool from '../../../../../lib/db/db';
import { RowDataPacket } from 'mysql2';

export async function GET(request: NextRequest, { params }) {
  try {
    const { slug } = await params;

    if (!slug) {
      return new Response(JSON.stringify({ 
        success: false, 
        error: 'Slug is required' 
      }), { status: 400 });
    }

    // Fetch guide with author information
    const [guideResult] = await pool.query<RowDataPacket[]>(`
      SELECT 
        g.*,
        a.id as author_id,
        a.full_name as author_name,
        a.slug as author_slug,
        a.profile_image as author_image,
        a.bio as author_bio,
        a.website as author_website,
        a.social_facebook as author_facebook,
        a.social_twitter as author_twitter,
        a.social_instagram as author_instagram,
        a.social_linkedin as author_linkedin,
        a.created_at as author_created_at
      FROM guides g
      LEFT JOIN authors a ON g.author_id = a.id AND a.status = 'active'
      WHERE g.slug = ? AND g.is_active = 1
      GROUP BY g.id
      LIMIT 1
    `, [slug]);

    let guide = guideResult[0];

    // If not found by exact slug, try to find by normalized title
    if (!guide) {
      console.log('🔍 Exact slug not found, trying normalized title match...');
      
      const [allGuides] = await pool.query<RowDataPacket[]>(`
        SELECT g.*,
          a.id as author_id,
          a.full_name as author_name,
          a.slug as author_slug,
          a.profile_image as author_image
        FROM guides g
        LEFT JOIN authors a ON g.author_id = a.id AND a.status = 'active'
        WHERE g.is_active = 1
      `);

      // Normalize the slug for comparison
      const normalizeSlug = (str) => str.toLowerCase().replace(/[-_]+/g, ' ').trim();
      const normalizedSlug = normalizeSlug(slug);

      // Try to find the guide by normalized slug or title
      guide = allGuides.find(g => {
        // Check if guide has a slug and it matches
        if (g.slug) {
          const guideSlug = normalizeSlug(g.slug);
          return guideSlug === normalizedSlug;
        }
        // If no slug, check if title matches
        if (g.title) {
          const guideTitle = normalizeSlug(g.title);
          return guideTitle === normalizedSlug;
        }
        return false;
      });

      if (guide) {
        console.log('✅ Guide found by normalized title/slug match');
      }
    }

    if (!guide) {
      console.log('❌ Guide not found for slug:', slug);
      return new Response(JSON.stringify({ 
        success: false, 
        error: 'Guide not found' 
      }), { status: 404 });
    }

    // Format author data
    const authorData = guide.author_id ? {
      id: guide.author_id,
      full_name: guide.author_name,
      slug: guide.author_slug,
      profile_image: guide.author_image,
      bio: guide.author_bio,
      website: guide.author_website,
      social_facebook: guide.author_facebook,
      social_twitter: guide.author_twitter,
      social_instagram: guide.author_instagram,
      social_linkedin: guide.author_linkedin,
      created_at: guide.author_created_at
    } : null;

    // Remove author fields from guide data
    const {
      author_id, author_name, author_slug, author_image, author_bio,
      author_website, author_facebook, author_twitter, author_instagram,
      author_linkedin, author_created_at,
      ...cleanGuideData
    } = guide;

    return new Response(JSON.stringify({
      success: true,
      data: {
        ...cleanGuideData,
        author: authorData
      }
    }));

  } catch (err) {
    console.error("❌ GET Guide by slug API Error:", err);
    return new Response(JSON.stringify({ 
      success: false, 
      error: "Failed to fetch guide",
      details: err.message 
    }), { status: 500 });
  }
}