import { NextResponse } from 'next/server';
import pool from "../../../../lib/db/db"
import { verifyAuthentication } from '../../../utils/verifyAuthentication';

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

  try {
    const { searchParams } = new URL(request.url);
    const page = parseInt(searchParams.get('page')) || 1;
    const limit = parseInt(searchParams.get('limit')) || 10;

    const offset = (page - 1) * limit;

    // Count query for pagination
    const countQuery = `SELECT COUNT(*) as total FROM success_stories`;
    
    // Main data query
    const dataQuery = `
      SELECT 
        id,
        image,
        alt_text,
        story_date,
        is_featured,
        created_at,
        updated_at
      FROM success_stories 
      ORDER BY 
        is_featured DESC,
        created_at DESC
      LIMIT ? OFFSET ?
    `;

    // Execute queries
    const [countResult] = await connection.execute(countQuery);
    const [stories] = await connection.execute(dataQuery, [limit, offset]);

    const totalItems = countResult[0].total;
    const totalPages = Math.ceil(totalItems / limit);

    return NextResponse.json({
      success: true,
      stories,
      meta: {
        currentPage: page,
        itemsPerPage: limit,
        totalItems,
        totalPages,
        hasNextPage: page < totalPages,
        hasPrevPage: page > 1
      }
    });

  } catch (error) {
    console.error('Success stories fetch error:', error);
    return NextResponse.json(
      { 
        success: false, 
        error: 'Failed to fetch success stories',
        message: error.message 
      },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}

export async function POST(request) {
  const connection = await pool.getConnection();

  try {

    const token = request.cookies.get("university-token")?.value || null;
    
    if (!token) {
      return NextResponse.json(
        {
          message: "Please Login to continue"
        },
        {
          status: 400
        }
      );
    }

    const decoded = await verifyAuthentication(token)

    if (decoded instanceof NextResponse) {
      return NextResponse.json(
        {
          message: "You are not authorized to do this action"
        },
        {
          status: 400
        }
      );
    }

    if(decoded){

      const { alt_text, story_date, is_featured, image } = await request.json();

      if (!alt_text || !story_date) {
        return NextResponse.json(
          { success: false, error: 'Alt text and story data are required' },
          { status: 400 }
        );
      }

      await connection.beginTransaction();

      const query = `
        INSERT INTO success_stories (image, alt_text, story_date, is_featured) 
        VALUES (?, ?, ?, ?)
      `;

      const [result] = await connection.execute(query, [
        image || null,
        alt_text,
        story_date,
        is_featured || false
      ]);

      await connection.query(`
        INSERT INTO activity_logs (user_id, action)
        VALUES (?, ?)
      `, [decoded.id, `Success story created with ID: ${result.insertId} By ${decoded.name} with the email ${decoded.email}`])

      await connection.commit();

      return NextResponse.json({
        success: true,
        storyId: result.insertId,
        message: 'Success story created successfully'
      });

    } else {
      return NextResponse.json(
        {
          message: "You are not authorized to do this action"
        },
        {
          status: 400
        }
      );
    }

  } catch (error) {
    console.error('Success story creation error:', error);

    await connection.rollback();

    return NextResponse.json(
      { 
        success: false, 
        error: 'Failed to create success story',
        message: error.message 
      },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}

export async function PUT(request) {
  const connection = await pool.getConnection();

  try {

    const token = request.cookies.get("university-token")?.value || null;
    
    if (!token) {
      return NextResponse.json(
        {
          message: "Please Login to continue"
        },
        {
          status: 400
        }
      );
    }

    const decoded = await verifyAuthentication(token)

    if (decoded instanceof NextResponse) {
      return NextResponse.json(
        {
          message: "You are not authorized to do this action"
        },
        {
          status: 400
        }
      );
    }

    if(decoded){

      await connection.beginTransaction();

      const { searchParams } = new URL(request.url);
      const id = searchParams.get('id');
      
      const { alt_text, story_date, is_featured, image } = await request.json();

      if (!id) {
        return NextResponse.json(
          { success: false, error: 'Story ID is required' },
          { status: 400 }
        );
      }

      if (!alt_text || !story_date) {
        return NextResponse.json(
          { success: false, error: 'Alt text and story data are required' },
          { status: 400 }
        );
      }

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

      if (image !== undefined) {
        updateFields.push('image = ?');
        queryParams.push(image);
      }

      updateFields.push('alt_text = ?');
      queryParams.push(alt_text);

      updateFields.push('story_date = ?');
      queryParams.push(story_date);

      updateFields.push('is_featured = ?');
      queryParams.push(is_featured || false);

      queryParams.push(id);

      const query = `
        UPDATE success_stories 
        SET ${updateFields.join(', ')}, updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
      `;

      const [result] = await connection.execute(query, queryParams);

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

      await connection.query(`
        INSERT INTO activity_logs (user_id, action)
        VALUES (?, ?)
      `, [decoded.id, `Success story updated with ID: ${id} By ${decoded.name} with the email ${decoded.email}`])

      await connection.commit();

      return NextResponse.json({
        success: true,
        message: 'Success story updated successfully'
      });
      
    } else {
      return NextResponse.json(
        {
          message: "You are not authorized to do this action"
        },
        {
          status: 400
        }
      );
    }

  } catch (error) {
    console.error('Success story update error:', error);
    await connection.rollback();
    return NextResponse.json(
      { 
        success: false, 
        error: 'Failed to update success story',
        message: error.message 
      },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}

export async function DELETE(request) {
  const connection = await pool.getConnection();

  try {

    const token = request.cookies.get("university-token")?.value || null;
    
    if (!token) {
      return NextResponse.json(
        {
          message: "Please Login to continue"
        },
        {
          status: 400
        }
      );
    }

    const decoded = await verifyAuthentication(token)

    if (decoded instanceof NextResponse) {
      return NextResponse.json(
        {
          message: "You are not authorized to do this action"
        },
        {
          status: 400
        }
      );
    }

    if(decoded){

      await connection.beginTransaction();

      const { searchParams } = new URL(request.url);
      const id = searchParams.get('id');

      if (!id) {
        return NextResponse.json(
          { success: false, error: 'Story ID is required' },
          { status: 400 }
        );
      }

      const query = `DELETE FROM success_stories WHERE id = ?`;
      const [result] = await connection.execute(query, [id]);

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

      await connection.query(`
        INSERT INTO activity_logs (user_id, action)
        VALUES (?, ?)
      `, [decoded.id, `Success story deleted with ID: ${id} By ${decoded.name} with the email ${decoded.email}`])

      await connection.commit();

      return NextResponse.json({
        success: true,
        message: 'Success story deleted successfully'
      });
    }
    else {
      return NextResponse.json(
        {
          message: "You are not authorized to do this action"
        },
        {
          status: 400
        }
      );
    }

  } catch (error) {
    console.error('Success story deletion error:', error);
    await connection.rollback();
    return NextResponse.json(
      { 
        success: false, 
        error: 'Failed to delete success story',
        message: error.message 
      },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}