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

// API category data interface
interface APICategory {
  external_id: string;
  name?: string;
  japanese: string;
  english: string;
  slug: string;
  description?: 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 (create or update category)
export async function POST(request: NextRequest) {
  try {
    // 1. Authentication
    const apiSecret = request.headers.get('X-API-Secret');
    const expectedSecret = process.env.SYNC_API_SECRET;

    if (!apiSecret || apiSecret !== expectedSecret) {
      return NextResponse.json(
        { success: false, error: 'Unauthorized' },
        { status: 401, headers: corsHeaders }
      );
    }

    // 2. Parse request data (support both body and query params)
    let category: APICategory;
    
    try {
      // Try to parse JSON body first
      const body = await request.json();
      
      // If body is empty or doesn't have required fields, check query params
      if (!body || Object.keys(body).length === 0 || !body.external_id) {
        const { searchParams } = new URL(request.url);
        category = {
          external_id: searchParams.get('external_id') || '',
          name: searchParams.get('name') || undefined,
          japanese: searchParams.get('japanese') || '',
          english: searchParams.get('english') || '',
          slug: searchParams.get('slug') || '',
          description: searchParams.get('description') || undefined,
        };
      } else {
        category = body;
      }
    } catch (jsonError) {
      // If JSON parsing fails, try query params
      const { searchParams } = new URL(request.url);
      category = {
        external_id: searchParams.get('external_id') || '',
        name: searchParams.get('name') || undefined,
        japanese: searchParams.get('japanese') || '',
        english: searchParams.get('english') || '',
        slug: searchParams.get('slug') || '',
        description: searchParams.get('description') || undefined,
      };
    }

    // 3. Validate required fields
    const requiredFields = ['external_id', 'japanese', 'english', 'slug'];
    for (const field of requiredFields) {
      if (!category[field as keyof APICategory]) {
        return NextResponse.json(
          { success: false, error: `Missing required field: ${field}` },
          { status: 400, headers: corsHeaders }
        );
      }
    }

    // 4. Prepare category data
    const categoryData = {
      siteId: 'ten-sura-game.com',
      externalId: category.external_id,
      name: category.name || category.japanese || category.english || category.slug,
      japanese: category.japanese,
      english: category.english,
      slug: category.slug,
      description: category.description || null,
      updatedAt: new Date(),
    };

    // 5. Upsert (Create or Update)
    const existing = await db
      .select({ id: categories.id })
      .from(categories)
      .where(
        and(
          eq(categories.externalId, category.external_id),
          eq(categories.siteId, 'ten-sura-game.com')
        )
      )
      .limit(1);

    let result;
    if (existing.length > 0) {
      // Update existing
      result = await db
        .update(categories)
        .set(categoryData)
        .where(
          and(
            eq(categories.externalId, category.external_id),
            eq(categories.siteId, 'ten-sura-game.com')
          )
        )
        .returning({ id: categories.id, slug: categories.slug });
    } else {
      // Create new
      result = await db
        .insert(categories)
        .values({ ...categoryData, createdAt: new Date() })
        .returning({ id: categories.id, slug: categories.slug });
    }

    // 6. Invalidate cache to update UI immediately
    revalidatePath('/', 'layout');

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

  } catch (error) {
    console.error('Sync Categories API Error:', error);
    console.error('Request URL:', request.url);
    console.error('Request method:', request.method);
    
    // Log more details about the error
    if (error instanceof Error) {
      console.error('Error message:', error.message);
      console.error('Error stack:', error.stack);
    }
    
    return NextResponse.json(
      { 
        success: false, 
        error: 'Internal server error',
        details: error instanceof Error ? error.message : 'Unknown error'
      },
      { status: 500, headers: corsHeaders }
    );
  }
}

// Handle DELETE (delete category)
export async function DELETE(request: NextRequest) {
  try {
    // 1. Authentication
    const apiSecret = request.headers.get('X-API-Secret');
    const expectedSecret = process.env.SYNC_API_SECRET;

    if (!apiSecret || apiSecret !== expectedSecret) {
      return NextResponse.json(
        { success: false, error: 'Unauthorized' },
        { status: 401, headers: corsHeaders }
      );
    }

    // 2. Get external_id from query params
    const { searchParams } = new URL(request.url);
    const externalId = searchParams.get('external_id');

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

    // 3. Check if category exists
    const existing = await db
      .select({ id: categories.id })
      .from(categories)
      .where(
        and(
          eq(categories.externalId, externalId),
          eq(categories.siteId, 'ten-sura-game.com')
        )
      )
      .limit(1);

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

    // 4. Delete category
    // Note: Articles referencing this category will NOT be deleted
    await db
      .delete(categories)
      .where(
        and(
          eq(categories.externalId, externalId),
          eq(categories.siteId, 'ten-sura-game.com')
        )
      );

    // 5. Invalidate cache to update UI immediately
    revalidatePath('/', 'layout');

    return NextResponse.json({
      success: true,
      message: 'Category deleted successfully',
    }, { headers: corsHeaders });

  } catch (error) {
    console.error('Delete Category API Error:', error);
    console.error('Request URL:', request.url);
    
    if (error instanceof Error) {
      console.error('Error message:', error.message);
      console.error('Error stack:', error.stack);
    }
    
    return NextResponse.json(
      { 
        success: false, 
        error: 'Internal server error',
        details: error instanceof Error ? error.message : 'Unknown error'
      },
      { status: 500, headers: corsHeaders }
    );
  }
}
