import { NextRequest, NextResponse } from 'next/server';
import postgresClient from '@/lib/services/postgres-client';

export async function GET(request: NextRequest) {
  try {
    const { searchParams } = new URL(request.url);

    const limit = parseInt(searchParams.get('limit') || '100');
    const offset = parseInt(searchParams.get('offset') || '0');
    const search = searchParams.get('search') || '';
    const ticketId = searchParams.get('ticket_id');
    const creatorId = searchParams.get('creator_resource_id');
    const sortBy = searchParams.get('sort_by') || 'create_date_time';
    const sortOrder = searchParams.get('sort_order')?.toLowerCase() === 'asc' ? 'ASC' : 'DESC';

    const conditions: string[] = ['tn.is_deleted = false'];
    const params: any[] = [];
    let p = 1;

    if (search) {
      conditions.push(`(tn.title ILIKE $${p} OR tn.description ILIKE $${p})`);
      params.push(`%${search}%`);
      p++;
    }
    if (ticketId) {
      conditions.push(`tn.ticket_id = $${p}`);
      params.push(ticketId);
      p++;
    }
    if (creatorId) {
      conditions.push(`tn.creator_resource_id = $${p}`);
      params.push(creatorId);
      p++;
    }

    const validSort = ['create_date_time', 'last_activity_date', 'ticket_id', 'creator_resource_id', 'note_type'];
    const safeSort = validSort.includes(sortBy) ? sortBy : 'create_date_time';
    const where = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';

    const query = `
      SELECT
        tn.id,
        tn.ticket_id,
        t.ticket_number,
        tn.title,
        tn.description,
        tn.note_type,
        tn.publish,
        tn.creator_resource_id,
        r.first_name || ' ' || r.last_name AS creator_name,
        tn.creator_type,
        tn.create_date_time,
        tn.last_activity_date,
        tn.synced_at
      FROM ticket_notes tn
      LEFT JOIN tickets t ON tn.ticket_id = t.id
      LEFT JOIN resources r ON tn.creator_resource_id = r.id
      ${where}
      ORDER BY tn.${safeSort} ${sortOrder}
      LIMIT $${p} OFFSET $${p + 1}
    `;
    params.push(limit, offset);

    const countQuery = `SELECT COUNT(*) AS total FROM ticket_notes tn ${where}`;

    const [result, countResult] = await Promise.all([
      postgresClient.query(query, params),
      postgresClient.query(countQuery, params.slice(0, -2)),
    ]);

    return NextResponse.json({
      ticketNotes: result.rows,
      pagination: {
        total: parseInt(countResult.rows[0].total),
        limit,
        offset,
        hasMore: offset + limit < parseInt(countResult.rows[0].total),
      },
    });
  } catch (error) {
    console.error('Error fetching ticket notes:', error);
    return NextResponse.json({ error: 'Failed to fetch ticket notes' }, { status: 500 });
  }
}