// app/api/internal/authors/route.js

import { NextResponse } from "next/server";
import pool from "../../../../lib/db/db";
import slugify from "slugify";

export async function GET(request) {
  let connection;
  
  try {
    connection = await pool.getConnection();

    const { searchParams } = new URL(request.url);
    const page = Number.parseInt(searchParams.get("page") || "1", 10) || 1;
    const limit = Number.parseInt(searchParams.get("limit") || "15", 10) || 15;
    const search = (searchParams.get("search") || "").trim();
    const status = searchParams.get("status") || "";
    const skip = (page - 1) * limit;

    // Build WHERE clause
    let whereSql = "";
    const whereParams = [];
    const conditions = [];
    
    if (search) {
      conditions.push("(full_name LIKE ? OR bio LIKE ? OR website LIKE ?)");
      whereParams.push(`%${search}%`, `%${search}%`, `%${search}%`);
    }
    
    if (status) {
      conditions.push("status = ?");
      whereParams.push(status);
    }
    
    if (conditions.length > 0) {
      whereSql = `WHERE ${conditions.join(" AND ")}`;
    }

    // Count query
    const countQuery = `
      SELECT COUNT(*) AS totalCount 
      FROM authors 
      ${whereSql}
    `;
    
    const [countRows] = await connection.query(countQuery, whereParams);
    const totalCount = countRows[0]?.totalCount ?? 0;

    // Data query
    const dataQuery = `
      SELECT 
        id,
        full_name,
        slug,
        bio,
        profile_image,
        website,
        social_facebook,
        social_twitter,
        social_instagram,
        social_linkedin,
        status,
        created_at,
        updated_at
      FROM authors 
      ${whereSql}
      ORDER BY created_at DESC
      LIMIT ? OFFSET ?
    `;
    
    const dataParams = [...whereParams, limit, skip];
    const [authors] = await connection.query(dataQuery, dataParams);

    const totalPages = Math.ceil(totalCount / limit);

    return NextResponse.json({
      success: true,
      data: authors,
      meta: {
        totalItems: totalCount,
        totalPages,
        currentPage: page,
        itemsPerPage: limit,
        hasNextPage: page < totalPages,
        hasPreviousPage: page > 1,
      },
    });
  } catch (error) {
    console.error("GET /authors error:", error);
    return NextResponse.json(
      { 
        success: false,
        error: "Failed to fetch authors", 
        details: error.message 
      },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}

export async function POST(req) {
  let connection;

  try {
    connection = await pool.getConnection();
    const body = await req.json();
    
    // Required field validation
    if (!body.full_name || !body.full_name.trim()) {
      return NextResponse.json(
        { success: false, error: "Author name is required" },
        { status: 400 }
      );
    }

    // Generate slug from full_name if not provided
    let slug = body.slug;
    if (!slug || !slug.trim()) {
      slug = slugify(body.full_name, {
        lower: true,
        strict: true,
        trim: true
      });
    }

    // Check if slug already exists
    const [existingAuthor] = await connection.query(
      'SELECT id FROM authors WHERE slug = ?',
      [slug]
    );

    if (existingAuthor.length > 0) {
      return NextResponse.json(
        { success: false, error: "Slug already exists. Please use a different slug." },
        { status: 400 }
      );
    }

    // Insert author
    const [result] = await connection.query(
      `INSERT INTO authors (
        full_name, 
        slug, 
        bio,
        profile_image,
        website,
        social_facebook,
        social_twitter,
        social_instagram,
        social_linkedin,
        status,
        created_at, 
        updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        body.full_name.trim(),
        slug,
        body.bio || "",
        body.profile_image || "",
        body.website || "",
        body.social_facebook || "",
        body.social_twitter || "",
        body.social_instagram || "",
        body.social_linkedin || "",
        body.status || "active",
        new Date(),
        new Date(),
      ]
    );

    const authorId = result.insertId;

    // Fetch the created author
    const [rows] = await connection.query(
      `SELECT * FROM authors WHERE id = ?`,
      [authorId]
    );

    return NextResponse.json({ 
      success: true, 
      data: rows[0],
      message: 'Author created successfully'
    }, { status: 201 });

  } catch (error) {
    console.error("Error creating author:", error);
    
    // Handle specific database errors
    if (error.code === 'ER_DUP_ENTRY') {
      return NextResponse.json(
        { success: false, error: "An author with this slug already exists" },
        { status: 400 }
      );
    }

    return NextResponse.json(
      { 
        success: false, 
        error: "Failed to create author",
        details: process.env.NODE_ENV === 'development' ? error.message : undefined
      },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}

export async function PATCH(req) {
  let connection;
  
  try {
    connection = await pool.getConnection();
    const { searchParams } = new URL(req.url);
    const id = searchParams.get('id');
    
    if (!id) {
      return NextResponse.json(
        { success: false, error: "Author ID is required" },
        { status: 400 }
      );
    }

    // Fetch single author
    const [authors] = await connection.query(
      `SELECT * FROM authors WHERE id = ?`,
      [id]
    );

    if (authors.length === 0) {
      return NextResponse.json(
        { success: false, error: "Author not found" },
        { status: 404 }
      );
    }

    return NextResponse.json({
      success: true,
      data: authors[0]
    });
  } catch (error) {
    console.error("PATCH /authors error:", error);
    return NextResponse.json(
      { 
        success: false,
        error: "Failed to fetch author", 
        details: error.message 
      },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}

export async function PUT(req) {
  let connection;
  
  try {
    connection = await pool.getConnection();
    const { searchParams } = new URL(req.url);
    const id = searchParams.get('id');
    
    if (!id) {
      return NextResponse.json(
        { success: false, error: "Author ID is required" },
        { status: 400 }
      );
    }

    const body = await req.json();

    // Validation
    if (!body.full_name || !body.full_name.trim()) {
      return NextResponse.json(
        { success: false, error: "Author name is required" },
        { status: 400 }
      );
    }

    // Generate slug from full_name if not provided
    let slug = body.slug;
    if (!slug || !slug.trim()) {
      slug = slugify(body.full_name, {
        lower: true,
        strict: true,
        trim: true
      });
    }

    // Check if slug already exists (excluding current author)
    const [existingAuthor] = await connection.query(
      'SELECT id FROM authors WHERE slug = ? AND id != ?',
      [slug, id]
    );

    if (existingAuthor.length > 0) {
      return NextResponse.json(
        { success: false, error: "Slug already exists. Please use a different slug." },
        { status: 400 }
      );
    }

    // Update author
    const [result] = await connection.query(
      `UPDATE authors SET 
        full_name = ?, 
        slug = ?, 
        bio = ?,
        profile_image = ?,
        website = ?,
        social_facebook = ?,
        social_twitter = ?,
        social_instagram = ?,
        social_linkedin = ?,
        status = ?,
        updated_at = ?
       WHERE id = ?`,
      [
        body.full_name.trim(),
        slug,
        body.bio || "",
        body.profile_image || "",
        body.website || "",
        body.social_facebook || "",
        body.social_twitter || "",
        body.social_instagram || "",
        body.social_linkedin || "",
        body.status || "active",
        new Date(),
        id
      ]
    );

    if (result.affectedRows === 0) {
      return NextResponse.json(
        { success: false, error: "Author not found" },
        { status: 404 }
      );
    }

    // Fetch updated author
    const [rows] = await connection.query(
      `SELECT * FROM authors WHERE id = ?`,
      [id]
    );

    return NextResponse.json({ 
      success: true, 
      data: rows[0],
      message: 'Author updated successfully'
    });
  } catch (error) {
    console.error("Error updating author:", error);
    
    // Handle specific database errors
    if (error.code === 'ER_DUP_ENTRY') {
      return NextResponse.json(
        { success: false, error: "An author with this slug already exists" },
        { status: 400 }
      );
    }

    return NextResponse.json(
      { 
        success: false, 
        error: "Failed to update author",
        details: error.message 
      },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}

export async function DELETE(req) {
  let connection;
  
  try {
    connection = await pool.getConnection();
    const { searchParams } = new URL(req.url);
    const id = searchParams.get('id');
    
    if (!id) {
      return NextResponse.json(
        { success: false, error: "Author ID is required" },
        { status: 400 }
      );
    }

    // Check if the author exists
    const [authorExists] = await connection.query(
      'SELECT id FROM authors WHERE id = ?',
      [id]
    );

    if (authorExists.length === 0) {
      return NextResponse.json(
        { success: false, error: "Author not found" },
        { status: 404 }
      );
    }

    // Delete the author
    const [result] = await connection.query(
      `DELETE FROM authors WHERE id = ?`,
      [id]
    );

    if (result.affectedRows === 0) {
      return NextResponse.json(
        { success: false, error: "Author not found" },
        { status: 404 }
      );
    }

    return NextResponse.json({ 
      success: true, 
      message: "Author deleted successfully" 
    });

  } catch (error) {
    console.error("Error deleting author:", error);
    
    return NextResponse.json(
      { 
        success: false, 
        error: "Failed to delete author",
        details: process.env.NODE_ENV === 'development' ? error.message : undefined
      },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}