// app/api/frontend/userdashboard/book-appointment/route.js
import pool from '../../../../../lib/db/db';
import { NextRequest, NextResponse } from 'next/server';
import { ResultSetHeader, RowDataPacket } from 'mysql2';
import jwt from 'jsonwebtoken';
import { z } from 'zod';

// Validation schema
const bookingSchema = z.object({
  foreignStudentId: z.number().min(1),
  sessionDate: z.string().regex(/^\d{4}-\d{2}-\d{2}$/, 'Invalid date format'),
  startTime: z.string().regex(/^([0-1][0-9]|2[0-3]):[0-5][0-9]$/, 'Invalid time format'),
  endTime: z.string().regex(/^([0-1][0-9]|2[0-3]):[0-5][0-9]$/, 'Invalid time format'),
  amount: z.number().min(0).optional() // Add amount to validation
}).refine(data => data.startTime < data.endTime, {
  message: "End time must be after start time",
  path: ["endTime"]
});

// Helper function to calculate duration in hours
const calculateDurationHours = (startTime, endTime) => {
  const [startHour, startMinute] = startTime.split(':').map(Number);
  const [endHour, endMinute] = endTime.split(':').map(Number);
  
  const startTotal = startHour * 60 + startMinute;
  const endTotal = endHour * 60 + endMinute;
  
  return (endTotal - startTotal) / 60;
};

// Helper function to calculate amount
const calculateAmount = (hourRate, startTime, endTime) => {
  const durationHours = calculateDurationHours(startTime, endTime);
  return (hourRate || 0) * durationHours;
};

export async function POST(req: NextRequest) {
  const connection = await pool.getConnection();
  
  try {
    // Get token from cookie
    const token = req.cookies.get('university-token')?.value;
    
    if (!token) {
      connection.release();
      return NextResponse.json(
        { success: false, message: 'Authentication required' },
        { status: 401 }
      );
    }

    // Verify token
    let decoded;
    try {
      decoded = jwt.verify(token, process.env.JWT_SECRET as string);
    } catch (err) {
      connection.release();
      return NextResponse.json(
        { success: false, message: 'Invalid or expired token' },
        { status: 401 }
      );
    }

    if (decoded.role !== 'student') {
      connection.release();
      return NextResponse.json(
        { success: false, message: 'Access denied' },
        { status: 403 }
      );
    }

    const body = await req.json();
    
    // Validate input
    const validatedData = bookingSchema.parse(body);

    const studentId = decoded.id;
    const { foreignStudentId, sessionDate, startTime, endTime } = validatedData;

    // Start transaction
    await connection.beginTransaction();

    // 1. Check if foreign student exists and is active
    const [foreignStudent] = await connection.query<RowDataPacket[]>(
      'SELECT id, name, hour_rate, is_active FROM foreign_students WHERE id = ?',
      [foreignStudentId]
    );

    if (foreignStudent.length === 0) {
      await connection.rollback();
      connection.release();
      return NextResponse.json(
        { success: false, message: 'Foreign student not found' },
        { status: 404 }
      );
    }

    const tutor = foreignStudent[0];
    
    if (!tutor.is_active) {
      await connection.rollback();
      connection.release();
      return NextResponse.json(
        { success: false, message: 'This tutor is not currently available for bookings' },
        { status: 400 }
      );
    }

    // 2. Check if time slot is available
    const sessionDay = new Date(sessionDate).getDay();
    
    // Check weekly availability
    const [weeklySchedule] = await connection.query<RowDataPacket[]>(
      `SELECT ws.id, ws.is_available
       FROM foreign_student_weekly_schedule ws
       LEFT JOIN foreign_student_time_slots ts ON ws.id = ts.weekly_schedule_id
       WHERE ws.foreign_student_id = ?
       AND ws.day_of_week = ?
       AND ws.is_available = 1
       AND ts.start_time <= ?
       AND ts.end_time >= ?`,
      [foreignStudentId, sessionDay, `${endTime}:00`, `${startTime}:00`]
    );

    // Check special dates
    const [specialDate] = await connection.query<RowDataPacket[]>(
      `SELECT sd.id
       FROM foreign_student_special_dates sd
       LEFT JOIN foreign_student_special_date_slots sds ON sd.id = sds.special_date_id
       WHERE sd.foreign_student_id = ?
       AND sd.date = ?
       AND sd.type = 'available'
       AND sds.start_time <= ?
       AND sds.end_time >= ?`,
      [foreignStudentId, sessionDate, `${endTime}:00`, `${startTime}:00`]
    );

    if (weeklySchedule.length === 0 && specialDate.length === 0) {
      await connection.rollback();
      connection.release();
      return NextResponse.json(
        { success: false, message: 'Selected time slot is not available' },
        { status: 400 }
      );
    }

    // 3. Check for existing bookings at same time
    const [existingBooking] = await connection.query<RowDataPacket[]>(
      `SELECT id FROM foreign_student_booked_sessions 
       WHERE foreign_student_id = ?
       AND session_date = ?
       AND (
         (start_time < ? AND end_time > ?) OR
         (start_time >= ? AND start_time < ?)
       )
       AND status IN ('pending', 'completed', 'confirmed')`,
      [
        foreignStudentId,
        sessionDate,
        `${endTime}:00`,
        `${startTime}:00`,
        `${startTime}:00`,
        `${endTime}:00`
      ]
    );

    if (existingBooking.length > 0) {
      await connection.rollback();
      connection.release();
      return NextResponse.json(
        { success: false, message: 'Time slot is already booked' },
        { status: 400 }
      );
    }

    // 4. Calculate amount based on current hour_rate
    const durationHours = calculateDurationHours(startTime, endTime);
    const amount = (tutor.hour_rate || 0) * durationHours;

    // 5. Create booking with amount
    const [insertNewAppointment] = await connection.query<ResultSetHeader>(
      `INSERT INTO foreign_student_booked_sessions 
       (foreign_student_id, student_id, session_date, start_time, end_time, amount, status)
       VALUES (?, ?, ?, ?, ?, ?, 'pending')`,
      [
        foreignStudentId,
        studentId,
        sessionDate,
        `${startTime}:00`,
        `${endTime}:00`,
        amount
      ]
    );

    if(insertNewAppointment.affectedRows === 0) {
      await connection.rollback();
      connection.release();
      return NextResponse.json(
        { success: false, message: 'Failed to book appointment' },
        { status: 500 }
      );
    }

    insertNewAppointment.insertId

    // Prepare payload for CRM
    const crmPayload = {
      "appointment_id": insertNewAppointment.insertId,
      "foreign_student_id": foreignStudentId,
      "student_id": studentId,
      "session_date": sessionDate,
      "start_time": `${startTime}:00`,
      "end_time": `${endTime}:00`,
      "amount": amount,
      "status": "pending"
    };

    // Send to CRM API
    let crmResponse;
    try {
      crmResponse = await fetch(
        "https://crm-universitiespage.com/reactapis/api/website_foreign_student_session/store",
        {
          method: "POST",
          body: JSON.stringify(crmPayload),
          headers: {
            'Content-Type': 'application/json',
            'Authorization': `${process.env.CRM_API_KEY}`,
          },
        }
      );

      if (!crmResponse.ok) {
        throw new Error(`CRM API responded with status: ${crmResponse.status}`);
      }

      const crmData = await crmResponse.json();

      if (crmData?.status_code !== 200) {
        throw new Error('CRM API returned non-success status');
      }

    } catch (crmError) {
      await connection.rollback();
      console.error('CRM API Error:', crmError);
      
      return NextResponse.json(
        {
          success: false,
          message: "Failed to submit your form to CRM. Please try again later."
        },
        { status: 502 } // Bad Gateway
      );
    }

    // Commit transaction
    await connection.commit();
    connection.release();

    return NextResponse.json({
      success: true,
      message: 'Appointment booked successfully',
      data: {
        tutorName: tutor.name,
        sessionDate,
        startTime,
        endTime,
        amount: amount,
        rate: tutor.hour_rate,
        durationHours: durationHours
      }
    }, { status: 200 });

  } catch (err: any) {
    // Rollback on any error
    if (connection) {
      await connection.rollback();
      connection.release();
    }
    
    console.error('Booking error:', err);
    
    // Zod validation errors
    if (err instanceof z.ZodError) {
      const errors = {};
      err.errors.forEach(error => {
        const path = error.path.join('.');
        errors[path] = error.message;
      });
      
      return NextResponse.json(
        { 
          success: false, 
          message: 'Validation failed', 
          errors 
        },
        { status: 400 }
      );
    }
    
    // Database or other errors
    return NextResponse.json(
      { 
        success: false, 
        message: err.message || 'Failed to book appointment' 
      },
      { status: 500 }
    );
  }
}