import pool from "../../../../lib/db/db";
import { NextResponse } from "next/server";
import type { RowDataPacket } from "mysql2";

// ------------------ Utility: Generate unique slug ------------------
async function generateUniqueSlug(title: string, excludeId: number | null = null) {
  let slug = title
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, "-")
    .replace(/(^-|-$)/g, "");

  let uniqueSlug = slug;
  let counter = 1;

  while (true) {
    const [rows] = await pool.query<RowDataPacket[]>(
      `SELECT id FROM posts WHERE slug = ? ${excludeId ? "AND id != ?" : ""}`,
      excludeId ? [uniqueSlug, excludeId] : [uniqueSlug]
    );
    if (rows.length === 0) break; // slug is unique
    uniqueSlug = `${slug}-${counter++}`;
  }

  return uniqueSlug;
}

// ------------------ GET posts with search + date filter + pagination ------------------
export async function GET(req: Request) {
  try {
    const { searchParams } = new URL(req.url);

    const page = parseInt(searchParams.get("page") || "1", 10);
    const limit = parseInt(searchParams.get("limit") || "10", 10);
    const offset = (page - 1) * limit;

    const search = searchParams.get("search")?.trim() || "";
    const startDate = searchParams.get("start_date")?.trim() || "";
    const endDate = searchParams.get("end_date")?.trim() || "";

    let whereClauses: string[] = [];
    let values: any[] = [];

    if (search) {
      whereClauses.push(`title LIKE ?`);
      values.push(`%${search}%`);
    }

    if (startDate && endDate) {
      whereClauses.push(`created_at BETWEEN ? AND ?`);
      values.push(startDate, endDate);
    }

    const whereSQL = whereClauses.length > 0 ? `WHERE ${whereClauses.join(" AND ")}` : "";

    const [countRows] = await pool.query<RowDataPacket[]>(
      `SELECT COUNT(*) as total FROM posts ${whereSQL}`,
      values
    );
    const total = countRows[0]?.total || 0;

    const [rows] = await pool.query<RowDataPacket[]>(
      `SELECT * FROM posts ${whereSQL} ORDER BY display_order ASC, id DESC LIMIT ? OFFSET ?`,
      [...values, limit, offset]
    );

    return NextResponse.json({
      success: true,
      pagination: {
        total,
        page,
        limit,
        totalPages: Math.ceil(total / limit),
      },
      data: rows,
    });
  } catch (error: any) {
    console.error("Error fetching posts:", error);
    return NextResponse.json(
      { success: false, message: "Error fetching posts" },
      { status: 500 }
    );
  }
}

// ------------------ POST new post ------------------
export async function POST(req: Request) {
  const connection = await pool.getConnection();
  try {
    const body = await req.json();
    if (!body.title) {
      return NextResponse.json(
        { success: false, error: "Title is required" },
        { status: 400 }
      );
    }

    // Start transaction
    await connection.beginTransaction();

    const slug = await generateUniqueSlug(body.title);
    const isActive = body.is_active === false ? 0 : 1;
    const orderValue = body.order ? parseInt(body.order) : 0;

    // Insert into MySQL
    const [result] = await connection.execute(
      `INSERT INTO posts (title, slug, image, is_active, display_order, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)`,
      [
        body.title,
        slug,
        body.icon || null,
        isActive,
        orderValue,
        new Date(),
        new Date(),
      ]
    );

    const insertId = (result as any).insertId;
    const [rows] = await connection.query<RowDataPacket[]>(`SELECT * FROM posts WHERE id = ?`, [insertId]);
    const newPost = rows[0];

    // ✅ Sync to Python API (PostgreSQL) - NOW PART OF TRANSACTION
    const syncResponse = await fetch(`${process.env.PYTHON_APP_URL}/api/sync-post`, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
        'X-API-Key': process.env.PYTHON_API_KEY
      },
      body: JSON.stringify({
        id: newPost.id,
        title: newPost.title,
        created_at: newPost.created_at,
        updated_at: newPost.updated_at
      })
    });

    if (!syncResponse.ok) {
      const errorData = await syncResponse.json();
      throw new Error(`PostgreSQL sync failed: ${errorData.detail || syncResponse.statusText}`);
    }

    const syncResult = await syncResponse.json();
    
    if (!syncResult.success) {
      throw new Error(`PostgreSQL sync failed: ${syncResult.error || 'Unknown error'}`);
    }

    // Commit transaction if both operations succeed
    await connection.commit();

    return NextResponse.json({ 
      success: true, 
      data: newPost,
      sync_result: syncResult 
    });

  } catch (error: any) {
    // Rollback transaction if any error occurs
    if (connection) {
      await connection.rollback();
    }

    console.error("Error creating post:", error);
    return NextResponse.json(
      { 
        success: false, 
        error: error.message,
        message: "Transaction rolled back - no data was saved"
      },
      { status: 500 }
    );
  } finally {
    // Release connection back to pool
    if (connection) {
      connection.release();
    }
  }
}

// ------------------ PATCH edit post ------------------
export async function PATCH(req) {
  let connection;
  
  try {
    const body = await req.json();
    
    if (!body.id) {
      return NextResponse.json(
        { success: false, error: "Post ID is required" },
        { status: 400 }
      );
    }

    connection = await pool.getConnection();
    await connection.beginTransaction();

    // Build dynamic update query based on provided fields
    const updateFields = [];
    const updateValues = [];

    if (body.title !== undefined) {
      updateFields.push("title = ?");
      updateValues.push(body.title);
    }

    // Handle image update - only update if icon is explicitly provided AND not empty string
    if (body.icon !== undefined && body.icon !== '') {
      updateFields.push("image = ?");
      updateValues.push(body.icon);
    }
    // If icon is empty string, don't update the image field (keep existing image)

    if (body.user_id !== undefined) {
      updateFields.push("user_id = ?");
      updateValues.push(body.user_id || null);
    }

    if (body.is_active !== undefined) {
      updateFields.push("is_active = ?");
      updateValues.push(body.is_active === false ? 0 : 1);
    }

    if (body.order !== undefined) {
      updateFields.push("display_order = ?");
      updateValues.push(body.order ? parseInt(body.order) : 0);
    }

    // Always update the updated_at timestamp
    updateFields.push("updated_at = ?");
    updateValues.push(new Date());

    if (updateFields.length === 0) {
      return NextResponse.json(
        { success: false, error: "No fields to update" },
        { status: 400 }
      );
    }

    updateValues.push(body.id);

    const [result] = await connection.execute(
      `UPDATE posts SET ${updateFields.join(", ")} WHERE id = ?`,
      updateValues
    );

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

    // Fetch updated post
    const [rows] = await connection.execute(`SELECT * FROM posts WHERE id = ?`, [body.id]);
    const updatedPost = rows[0];

    // ✅ NEW: Sync to Python API (PostgreSQL) - PART OF TRANSACTION
    try {
      const syncResponse = await fetch(`${process.env.PYTHON_APP_URL}/api/sync-post`, {
        method: 'PUT',
        headers: {
          'Content-Type': 'application/json',
          'X-API-Key': process.env.PYTHON_API_KEY
        },
        body: JSON.stringify({
          id: updatedPost.id,
          title: updatedPost.title,
          created_at: updatedPost.created_at,
          updated_at: updatedPost.updated_at
        })
      });

      if (!syncResponse.ok) {
        const errorData = await syncResponse.json();
        throw new Error(`PostgreSQL sync failed: ${errorData.detail || syncResponse.statusText}`);
      }

      const syncResult = await syncResponse.json();
      
      if (!syncResult.success) {
        throw new Error(`PostgreSQL sync failed: ${syncResult.error || 'Unknown error'}`);
      }

    } catch (syncError) {
      await connection.rollback();
      console.error('Failed to sync to PostgreSQL:', syncError);
      return NextResponse.json(
        { 
          success: false, 
          error: syncError.message,
          message: "Transaction rolled back - no data was saved"
        },
        { status: 500 }
      );
    }

    // Commit transaction if both operations succeed
    await connection.commit();

    return NextResponse.json({ 
      success: true, 
      data: updatedPost,
      message: "Post updated successfully" 
    });
  } catch (error) {
    // Rollback transaction if any error occurs
    if (connection) {
      await connection.rollback();
    }
    
    console.error("Error updating post:", error);
    return NextResponse.json(
      { success: false, error: error.message },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}