// app/api/sync-articles/route.ts
// API endpoint to sync articles from Bubble.io to Neon PostgreSQL
import { NextRequest, NextResponse } from 'next/server';
import { db } from '@/lib/db';
import { blogPosts } from '@/shared/schema';
import { eq, and } from 'drizzle-orm';

// API article data interface
interface APIArticle {
  _id: string;
  title: string;
  slug: string;
  content?: string;
  html?: string;
  article_html?: string;
  introduction?: string;
  metaTitle?: string;
  metaDesc?: string;
  'thumbnailImage(Image)'?: string;
  image?: string;
  category?: string;
  'Created Date'?: string;
  'Modified Date'?: string;
  articleStatus?: string;
  appName: string;
  authorName?: string;
  author?: string;
  authorProfileUrl?: string;
  authorBio?: string;
  featured?: boolean;
  viewsCount?: number;
  likesCount?: number;
  
  // Extended fields
  faq?: Array<{ question: string; answer: string }>;
  aioBox?: { html: string; plainText: string };
  authorData?: {
    bio: string;
    name: string;
    role: string;
    slug: string;
    credentials: string;
    social_links: Array<{ platform: string; url: string }>;
  };
  images?: Array<{
    id: string;
    url: string;
    title: string;
    width: number;
    height: number;
    altText: string;
    sectionId: string;
  }>;
  schema?: object;
  videos?: Array<{
    url: string;
    title: string;
    videoId: string;
    embedCode: string;
    sectionId: string;
  }>;
  metrics?: {
    totalWords: number;
    contentHash: string;
    readingTimeMinutes: number;
    [key: string]: any;
  };
  metadata?: {
    slug: string;
    title: string;
    author: {
      bio: string;
      name: string;
      title: string;
      credentials: string;
    };
    excerpt: string;
    publishDate: string;
    readingTime: number;
    metaDescription: string;
    [key: string]: any;
  };
  sections?: Array<{
    id: string;
    title: string;
    wordCount: number;
    [key: string]: any;
  }>;
  keyTakeaways?: string[];
  keywords?: string;
}

// CORS headers
const corsHeaders = {
  'Access-Control-Allow-Origin': '*',
  'Access-Control-Allow-Methods': 'GET, POST, DELETE, OPTIONS',
  'Access-Control-Allow-Headers': 'Content-Type, Authorization, X-API-Secret',
};

// Handle OPTIONS (preflight)
export async function OPTIONS() {
  return new Response(null, { status: 204, headers: corsHeaders });
}

// Handle POST (sync article)
export async function POST(request: NextRequest) {
  // Log at the very start - this will ALWAYS show in logs
  console.log('🚀 SYNC API CALLED - START');
  console.log('Time:', new Date().toISOString());
  
  try {
    // 0. Validate environment variables
    console.log('✓ Step 0: Validating environment variables');
    if (!process.env.DATABASE_URL) {
      console.error('❌ CRITICAL: DATABASE_URL is not defined');
      return NextResponse.json(
        { success: false, error: 'Server configuration error: DATABASE_URL missing' },
        { status: 500, headers: corsHeaders }
      );
    }
    console.log('✓ DATABASE_URL exists');
    
    if (!process.env.SYNC_API_SECRET) {
      console.error('❌ CRITICAL: SYNC_API_SECRET is not defined');
      return NextResponse.json(
        { success: false, error: 'Server configuration error: SYNC_API_SECRET missing' },
        { status: 500, headers: corsHeaders }
      );
    }
    console.log('✓ SYNC_API_SECRET exists');
    
    // 1. Authentication
    console.log('✓ Step 1: Checking authentication');
    const apiSecret = request.headers.get('X-API-Secret');
    const expectedSecret = process.env.SYNC_API_SECRET;

    if (!apiSecret || apiSecret !== expectedSecret) {
      console.error('❌ Authentication failed');
      return NextResponse.json(
        { success: false, error: 'Unauthorized' },
        { status: 401, headers: corsHeaders }
      );
    }
    console.log('✓ Authentication passed');

    // 2. Parse request body with size validation
    console.log('✓ Step 2: Parsing request body');
    console.log('Content-Type:', request.headers.get('Content-Type'));
    console.log('Content-Length:', request.headers.get('Content-Length'));
    
    let article: APIArticle;
    try {
      article = await request.json();
      console.log('✓ Payload parsed successfully');
      console.log('Article ID:', article._id);
      console.log('Article Title:', article.title);
      console.log('Content size:', article.content?.length || 0, 'chars');
      console.log('HTML size:', article.html?.length || 0, 'chars');
      console.log('Article HTML size:', article.article_html?.length || 0, 'chars');
    } catch (parseError) {
      console.error('❌ JSON PARSE ERROR');
      console.error('Error:', parseError instanceof Error ? parseError.message : 'Unknown parse error');
      return NextResponse.json(
        { 
          success: false, 
          error: 'Invalid JSON payload',
          details: parseError instanceof Error ? parseError.message : 'Failed to parse request body'
        },
        { status: 400, headers: corsHeaders }
      );
    }

    // 3. Validate app name (ten-sura-game.com)
    if (!article.appName || article.appName !== 'ten-sura-game.com') {
      return NextResponse.json(
        { success: false, error: 'Invalid app name - must be ten-sura-game.com' },
        { status: 400, headers: corsHeaders }
      );
    }

    // 4. Validate required fields
    const requiredFields = ['_id', 'title', 'slug'];
    for (const field of requiredFields) {
      if (!article[field as keyof APIArticle]) {
        return NextResponse.json(
          { success: false, error: `Missing required field: ${field}` },
          { status: 400, headers: corsHeaders }
        );
      }
    }

    // Must have either content, html, or article_html
    if (!article.content && !article.html && !article.article_html) {
      return NextResponse.json(
        { success: false, error: 'Missing content, html, or article_html field' },
        { status: 400, headers: corsHeaders }
      );
    }

    // 5. Data Transformation
    const imageUrl = article['thumbnailImage(Image)'] || article.image || null;
    let heroImage = imageUrl;
    
    // Normalize protocol-relative URLs (Cloudinary)
    if (heroImage?.startsWith('//')) {
      heroImage = `https:${heroImage}`;
    }

    const articleContent = article.content || article.html || article.article_html || '';
    const publicationDate = article['Created Date'] 
      ? new Date(article['Created Date']) 
      : new Date();

    // Build TOC from sections
    const tableOfContents = article.sections?.map(section => ({
      id: section.id,
      title: section.title,
      wordCount: section.wordCount
    })) || null;

    // Build FAQ schema
    const schemaFaq = article.faq?.length ? {
      "@context": "https://schema.org",
      "@type": "FAQPage",
      "mainEntity": article.faq.map(item => ({
        "@type": "Question",
        "name": item.question,
        "acceptedAnswer": {
          "@type": "Answer",
          "text": item.answer
        }
      }))
    } : null;

    // Hero image data
    const firstImage = article.images?.[0];
    const heroImageData = firstImage ? {
      url: firstImage.url,
      width: firstImage.width,
      height: firstImage.height,
      alt: firstImage.altText,
      title: firstImage.title
    } : null;

    // Prepare article data
    const articleData = {
      siteId: 'ten-sura-game.com',
      // Core
      externalId: article._id,
      title: article.title,
      slug: article.slug,
      content: articleContent,
      excerpt: article.introduction || article.metadata?.excerpt || null,
      category: article.category || null,
      
      // Images
      heroImage,
      cardImage: heroImage,
      
      // Author (default: 編集部)
      author: article.authorName || article.author || article.metadata?.author?.name || '編集部',
      authorProfileUrl: article.authorProfileUrl || null,
      authorBio: article.authorBio || article.metadata?.author?.bio || null,
      
      // Dates
      publicationDate,
      updatedAt: new Date(),
      
      // SEO
      metaTitle: article.metaTitle || article.metadata?.title || null,
      metaDescription: article.metaDesc || article.metadata?.metaDescription || null,
      seoTitle: article.metadata?.title?.substring(0, 100) || null,
      snippetIntro: article.aioBox?.plainText || null,
      
      // Metrics
      readingTime: article.metrics?.readingTimeMinutes || article.metadata?.readingTime || null,
      wordCount: article.metrics?.totalWords || null,
      viewsCount: article.viewsCount || 0,
      likesCount: article.likesCount || 0,
      
      // Flags
      featured: article.featured || false,
      isActive: true,
      
      // Structured data
      schemaJson: article.schema || null,
      schemaFaq,
      tableOfContents,
      faqItems: article.faq || null,
      keyTakeaways: article.keyTakeaways ? JSON.stringify(article.keyTakeaways) : null,
      aioBox: article.aioBox || null,
      heroImageData,
      authorData: article.authorData || (article.metadata?.author ? {
        name: article.metadata.author.name,
        bio: article.metadata.author.bio,
        title: article.metadata.author.title,
        credentials: article.metadata.author.credentials
      } : null),
      
      // Media
      images: article.images || null,
      videos: article.videos || null,
      
      // Metadata
      contentHash: article.metrics?.contentHash || null,
      processingTime: null,
      
      // Japanese-specific
      language: 'ja',
      keywords: article.keywords || null,
    };

    // 6. Upsert (Create or Update)
    console.log('Checking for existing article with external_id:', article._id);
    const existing = await db
      .select({ id: blogPosts.id })
      .from(blogPosts)
      .where(
        and(
          eq(blogPosts.externalId, article._id),
          eq(blogPosts.siteId, 'ten-sura-game.com')
        )
      )
      .limit(1);
    
    console.log('Existing article found:', existing.length > 0);

    let result;
    if (existing.length > 0) {
      // Update existing
      console.log('Updating existing article ID:', existing[0].id);
      result = await db
        .update(blogPosts)
        .set(articleData)
        .where(
          and(
            eq(blogPosts.externalId, article._id),
            eq(blogPosts.siteId, 'ten-sura-game.com')
          )
        )
        .returning({ id: blogPosts.id, slug: blogPosts.slug });
      console.log('Article updated successfully:', result[0]);
    } else {
      // Create new
      console.log('Creating new article');
      result = await db
        .insert(blogPosts)
        .values({ ...articleData, createdAt: new Date() })
        .returning({ id: blogPosts.id, slug: blogPosts.slug });
      console.log('Article created successfully:', result[0]);
    }
    
    console.log('=== SYNC API REQUEST END (SUCCESS) ===');

    // On-demand revalidation: Invalidate cache for affected pages
    try {
      const { revalidatePath } = await import('next/cache');
      
      // Revalidate homepage (shows latest articles)
      revalidatePath('/', 'page');
      
      // Revalidate blogs page
      revalidatePath('/blogs', 'page');
      
      // Revalidate the specific article page
      revalidatePath(`/post/${result[0].slug}`, 'page');
      
      // Revalidate category page if category exists
      if (article.category) {
        revalidatePath(`/category/${article.category}`, 'page');
      }
      
      console.log('✅ Cache revalidated for affected pages');
    } catch (revalidateError) {
      console.error('⚠️  Cache revalidation failed (non-critical):', revalidateError);
      // Don't fail the request if revalidation fails
    }

    return NextResponse.json({
      success: true,
      action: existing.length > 0 ? 'updated' : 'created',
      article: result[0],
    }, { headers: corsHeaders });

  } catch (error) {
    // Enhanced error logging for debugging
    const errorDetails = {
      timestamp: new Date().toISOString(),
      message: error instanceof Error ? error.message : 'Unknown error',
      stack: error instanceof Error ? error.stack : undefined,
      name: error instanceof Error ? error.name : 'UnknownError',
      // Log the error object itself for more details
      errorObject: error,
    };
    
    console.error('=== SYNC API ERROR START ===');
    console.error('Error Details:', JSON.stringify(errorDetails, null, 2));
    console.error('=== SYNC API ERROR END ===');
    
    return NextResponse.json(
      { 
        success: false, 
        error: 'Internal server error',
        // Include error details in production for debugging
        details: error instanceof Error ? error.message : 'Unknown error',
        errorName: error instanceof Error ? error.name : 'UnknownError'
      },
      { status: 500, headers: corsHeaders }
    );
  }
}

// Handle DELETE (delete article)
export async function DELETE(request: NextRequest) {
  console.log('🗑️  DELETE ARTICLE API CALLED - START');
  console.log('Time:', new Date().toISOString());
  
  try {
    // 1. Authentication
    console.log('✓ Step 1: Checking authentication');
    const apiSecret = request.headers.get('X-API-Secret');
    const expectedSecret = process.env.SYNC_API_SECRET;

    if (!apiSecret || apiSecret !== expectedSecret) {
      console.error('❌ Authentication failed');
      return NextResponse.json(
        { success: false, error: 'Unauthorized' },
        { status: 401, headers: corsHeaders }
      );
    }
    console.log('✓ Authentication passed');

    // 2. Get external_id from query params
    console.log('✓ Step 2: Extracting external_id from query params');
    const { searchParams } = new URL(request.url);
    const externalId = searchParams.get('external_id');

    if (!externalId) {
      console.error('❌ Missing external_id parameter');
      return NextResponse.json(
        { success: false, error: 'Missing external_id parameter' },
        { status: 400, headers: corsHeaders }
      );
    }
    console.log('✓ External ID:', externalId);

    // 3. Check if article exists
    console.log('✓ Step 3: Checking if article exists');
    const existing = await db
      .select({ 
        id: blogPosts.id, 
        slug: blogPosts.slug,
        category: blogPosts.category 
      })
      .from(blogPosts)
      .where(
        and(
          eq(blogPosts.externalId, externalId),
          eq(blogPosts.siteId, 'ten-sura-game.com')
        )
      )
      .limit(1);

    if (existing.length === 0) {
      console.error('❌ Article not found');
      return NextResponse.json(
        { success: false, error: 'Article not found' },
        { status: 404, headers: corsHeaders }
      );
    }

    const articleToDelete = existing[0];
    console.log('✓ Article found:', articleToDelete);

    // 4. Delete the article
    console.log('✓ Step 4: Deleting article from database');
    await db
      .delete(blogPosts)
      .where(
        and(
          eq(blogPosts.externalId, externalId),
          eq(blogPosts.siteId, 'ten-sura-game.com')
        )
      );
    
    console.log('✅ Article deleted successfully');

    // 5. Revalidate cache
    console.log('✓ Step 5: Revalidating cache');
    try {
      const { revalidatePath } = await import('next/cache');
      
      // Revalidate homepage (shows latest articles)
      revalidatePath('/', 'page');
      
      // Revalidate blogs page
      revalidatePath('/blogs', 'page');
      
      // Revalidate the specific article page (will now show 404)
      revalidatePath(`/post/${articleToDelete.slug}`, 'page');
      
      // Revalidate category page if category exists
      if (articleToDelete.category) {
        revalidatePath(`/category/${articleToDelete.category}`, 'page');
      }
      
      console.log('✅ Cache revalidated for affected pages');
    } catch (revalidateError) {
      console.error('⚠️  Cache revalidation failed (non-critical):', revalidateError);
      // Don't fail the request if revalidation fails
    }

    console.log('=== DELETE ARTICLE API END (SUCCESS) ===');

    return NextResponse.json({
      success: true,
      message: 'Article deleted successfully',
      deletedArticle: {
        externalId,
        slug: articleToDelete.slug,
      },
    }, { headers: corsHeaders });

  } catch (error) {
    // Enhanced error logging for debugging
    const errorDetails = {
      timestamp: new Date().toISOString(),
      message: error instanceof Error ? error.message : 'Unknown error',
      stack: error instanceof Error ? error.stack : undefined,
      name: error instanceof Error ? error.name : 'UnknownError',
      errorObject: error,
    };
    
    console.error('=== DELETE ARTICLE API ERROR START ===');
    console.error('Error Details:', JSON.stringify(errorDetails, null, 2));
    console.error('=== DELETE ARTICLE API ERROR END ===');
    
    return NextResponse.json(
      { 
        success: false, 
        error: 'Internal server error',
        details: error instanceof Error ? error.message : 'Unknown error',
        errorName: error instanceof Error ? error.name : 'UnknownError'
      },
      { status: 500, headers: corsHeaders }
    );
  }
}
