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

interface AnnouncementRow extends RowDataPacket {
  id: number;
  title: string;
  description: string;
  image: string | null;
  created_at: Date;
}

// GET: Fetch announcements with filters
export async function GET(request: NextRequest) {
  try {
    const { searchParams } = new URL(request.url);
    const page = parseInt(searchParams.get('page') || '1');
    const limit = parseInt(searchParams.get('limit') || '20');
    const startDate = searchParams.get('startDate') || '';
    const endDate = searchParams.get('endDate') || '';
    const month = searchParams.get('month') || '';
    const year = searchParams.get('year') || '';

    const offset = (page - 1) * limit;
    let whereConditions = "WHERE 1=1";
    const params: any[] = [];

    // Date range filter
    if (startDate && endDate) {
      whereConditions += ` AND DATE(created_at) BETWEEN ? AND ?`;
      params.push(startDate, endDate);
    } else if (startDate) {
      whereConditions += ` AND DATE(created_at) >= ?`;
      params.push(startDate);
    } else if (endDate) {
      whereConditions += ` AND DATE(created_at) <= ?`;
      params.push(endDate);
    }

    // Month and Year filter
    if (month && year) {
      whereConditions += ` AND MONTH(created_at) = ? AND YEAR(created_at) = ?`;
      params.push(parseInt(month), parseInt(year));
    } else if (month) {
      whereConditions += ` AND MONTH(created_at) = ?`;
      params.push(parseInt(month));
    } else if (year) {
      whereConditions += ` AND YEAR(created_at) = ?`;
      params.push(parseInt(year));
    }

    // Get total count
    const [countResult] = await pool.query<RowDataPacket[]>(
      `SELECT COUNT(*) as total FROM tbl_announcements ${whereConditions}`,
      params
    );
    const totalCount = countResult[0].total;
    const totalPages = Math.ceil(totalCount / limit);

    // Get announcements
    const [announcements] = await pool.query<AnnouncementRow[]>(
      `SELECT 
        id, 
        title, 
        description, 
        image, 
        created_at,
        DATE_FORMAT(created_at, '%Y-%m-%d') as date,
        DATE_FORMAT(created_at, '%M %Y') as monthYear
      FROM tbl_announcements 
      ${whereConditions} 
      ORDER BY created_at DESC 
      LIMIT ? OFFSET ?`,
      [...params, limit, offset]
    );

    // Get available years and months for filters
    const [availableYears] = await pool.query<RowDataPacket[]>(
      `SELECT DISTINCT YEAR(created_at) as year FROM tbl_announcements ORDER BY year DESC`
    );
    
    const [availableMonths] = await pool.query<RowDataPacket[]>(
      `SELECT DISTINCT MONTH(created_at) as month FROM tbl_announcements ORDER BY month`
    );

    // Format announcements
    const formattedAnnouncements = announcements.map(announcement => ({
      id: announcement.id,
      title: announcement.title,
      description: announcement.description,
      image: announcement.image,
      createdAt: announcement.created_at,
      date: announcement.date,
      monthYear: announcement.monthYear
    }));

    return NextResponse.json({
      success: true,
      data: {
        announcements: formattedAnnouncements,
        totalCount,
        currentPage: page,
        totalPages,
        limit,
        filters: {
          years: availableYears.map(y => y.year),
          months: availableMonths.map(m => m.month)
        }
      }
    }, { status: 200 });

  } catch (error: any) {
    console.error('Announcements API error:', error);
    return NextResponse.json({
      success: false,
      message: "Internal Server Error",
      error: error.message
    }, { status: 500 });
  }
}

// POST: Create new announcement
export async function POST(request: NextRequest) {
  try {
    const body = await request.json();
    const { title, description, image } = body;

    if (!title || !description) {
      return NextResponse.json({
        success: false,
        message: "Title and description are required"
      }, { status: 400 });
    }

    const [result] = await pool.query<ResultSetHeader>(
      `INSERT INTO tbl_announcements (title, description, image, created_at, updated_at) 
       VALUES (?, ?, ?, NOW(), NOW())`,
      [title, description, image || null]
    );

    if (result.affectedRows === 0) {
      return NextResponse.json({
        success: false,
        message: "Failed to create announcement"
      }, { status: 500 });
    }

    const [newAnnouncement] = await pool.query<AnnouncementRow[]>(
      `SELECT * FROM tbl_announcements WHERE id = ?`,
      [result.insertId]
    );

    return NextResponse.json({
      success: true,
      message: "Announcement created successfully",
      data: newAnnouncement[0]
    }, { status: 201 });

  } catch (error: any) {
    console.error('Create announcement error:', error);
    return NextResponse.json({
      success: false,
      message: "Internal Server Error",
      error: error.message
    }, { status: 500 });
  }
}

// PATCH: Update announcement
export async function PATCH(request: NextRequest) {
  try {
    const body = await request.json();
    const { id, title, description, image } = body;

    if (!id) {
      return NextResponse.json({
        success: false,
        message: "Announcement ID is required"
      }, { status: 400 });
    }

    const updates: string[] = [];
    const values: any[] = [];

    if (title) {
      updates.push("title = ?");
      values.push(title);
    }
    if (description) {
      updates.push("description = ?");
      values.push(description);
    }
    if (image !== undefined) {
      updates.push("image = ?");
      values.push(image);
    }

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

    updates.push("updated_at = NOW()");
    values.push(id);

    const [result] = await pool.query<ResultSetHeader>(
      `UPDATE tbl_announcements SET ${updates.join(", ")} WHERE id = ?`,
      values
    );

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

    return NextResponse.json({
      success: true,
      message: "Announcement updated successfully"
    }, { status: 200 });

  } catch (error: any) {
    console.error('Update announcement error:', error);
    return NextResponse.json({
      success: false,
      message: "Internal Server Error",
      error: error.message
    }, { status: 500 });
  }
}

// DELETE: Delete announcement
export async function DELETE(request: NextRequest) {
  try {
    const { searchParams } = new URL(request.url);
    const id = searchParams.get('id');

    if (!id) {
      return NextResponse.json({
        success: false,
        message: "Announcement ID is required"
      }, { status: 400 });
    }

    const [result] = await pool.query<ResultSetHeader>(
      `DELETE FROM tbl_announcements WHERE id = ?`,
      [id]
    );

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

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

  } catch (error: any) {
    console.error('Delete announcement error:', error);
    return NextResponse.json({
      success: false,
      message: "Internal Server Error",
      error: error.message
    }, { status: 500 });
  }
}