import { v4 as uuidv4 } from 'uuid';
import { db } from '../application/database';
import { ResponseError } from '../error/response-error';
import {
  UserPackageOrderResponse,
  toUserPackageOrderResponse,
  CreateUserPackageOrderRequest,
  UserPackageOrderQueryParams,
  UserPackageOrder,
} from '../model/user-package-order-model';
import { Validation } from '../validation/validation';
import { UserPackageOrderValidation } from '../validation/user-package-order-validation';
import { Pageable } from '../model/page';

export class UserPackageOrderService {
  static async create(request: CreateUserPackageOrderRequest): Promise<void> {
    // Validate the create request
    const createRequest = Validation.validate(UserPackageOrderValidation.CREATE, request);

    // Generate a UUID for the new user package order
    const userPackageOrderId = uuidv4();

    // Create the user package order
    await db.query(
      `
        INSERT INTO user_package_order (id, full_name, email, whatsapp_number, user_package_id)
        VALUES (?, ?, ?, ?, ?)
      `,
      [
        userPackageOrderId,
        createRequest.full_name,
        createRequest.email,
        createRequest.whatsapp_number,
        createRequest.user_package_id,
      ]
    );
  }

  static async getAll(queryParams: UserPackageOrderQueryParams): Promise<Pageable<UserPackageOrderResponse>> {
    // Validate the query parameters
    const queryRequest = Validation.validate(UserPackageOrderValidation.QUERY, queryParams);

    // Calculate the skip value
    const skip = (queryRequest.page - 1) * queryRequest.limit;

    // Create the WHERE clause
    let filterQuery = 'WHERE 1 = 1';
    const filterParams: any[] = [];

    if (queryRequest.search) {
      filterQuery += ' AND (full_name LIKE ? OR email LIKE ? OR whatsapp_number LIKE ?)';
      filterParams.push(`%${queryRequest.search}%`, `%${queryRequest.search}%`, `%${queryRequest.search}%`);
    }

    // Create the ORDER BY clause
    const sortField = queryRequest.sort || 'created_at';
    const sortOrder = queryRequest.order || 'desc';

    // Execute the query
    const userPackageOrders = await db.query<UserPackageOrder>(
      `
        SELECT
          upo.*,
          JSON_OBJECT(
            'id', up.id,
            'number_of_pax', up.number_of_pax,
            'transportation', JSON_OBJECT('name', t.name),
            'flight', JSON_OBJECT('name', f.name),
            'travel_duration', up.travel_duration,
            'mekkah_duration', up.mekkah_duration,
            'madinah_duration', up.madinah_duration,
            'hotelMekkah', JSON_OBJECT('name', hm.name),
            'hotelMadinah', JSON_OBJECT('name', hmd.name),
            'muthawif', JSON_OBJECT('name', m.name),
            'handling', JSON_OBJECT('name', hd.name),
            'total_price', up.total_price,
            'per_pax_price', up.per_pax_price
          ) AS user_package
        FROM user_package_order upo
        JOIN user_package up ON upo.user_package_id = up.id
        LEFT JOIN user_package_option t ON up.transportation_id = t.id
        LEFT JOIN user_package_option f ON up.flight_id = f.id
        LEFT JOIN user_package_option hm ON up.hotel_mekkah_id = hm.id
        LEFT JOIN user_package_option hmd ON up.hotel_madinah_id = hmd.id
        LEFT JOIN user_package_option m ON up.muthawif_id = m.id
        LEFT JOIN user_package_option hd ON up.handling_id = hd.id
        ${filterQuery}
        ORDER BY ${sortField} ${sortOrder}
        LIMIT ? OFFSET ?
      `,
      [...filterParams, queryRequest.limit, skip]
    );

    // Get total count of userPackageOrders matching the filter criteria
    const count = await db.queryOne<{ total: number }>(
      `
        SELECT COUNT(*) AS total
        FROM user_package_order upo
        JOIN user_package up ON upo.user_package_id = up.id
        LEFT JOIN user_package_option t ON up.transportation_id = t.id
        LEFT JOIN user_package_option f ON up.flight_id = f.id
        LEFT JOIN user_package_option hm ON up.hotel_mekkah_id = hm.id
        LEFT JOIN user_package_option hmd ON up.hotel_madinah_id = hmd.id
        LEFT JOIN user_package_option m ON up.muthawif_id = m.id
        LEFT JOIN user_package_option hd ON up.handling_id = hd.id
        ${filterQuery}
      `,
      filterParams
    );

    if (!count) {
      throw new ResponseError(404, 'User package orders tidak ditemukan');
    }

    // Map the results to the response format
    return {
      data: userPackageOrders.map((order) => toUserPackageOrderResponse(order, JSON.parse(order.user_package))),
      pagination: {
        total: count.total,
        current_page: queryRequest.page,
        total_pages: Math.ceil(count.total / queryRequest.limit),
        limit: queryRequest.limit,
      },
    };
  }

  static async delete(id: string): Promise<void> {
    // Check if the user package order exists
    const userPackageOrder = await db.queryOne<{ id: string }>(
      'SELECT id FROM user_package_order WHERE id = ? LIMIT 1',
      [id]
    );

    if (!userPackageOrder) {
      throw new ResponseError(404, 'User package order tidak ditemukan');
    }

    // Delete the user package order
    await db.query('DELETE FROM user_package_order WHERE id = ?', [id]);
  }
}
