import { v4 as uuidv4 } from 'uuid';
import { db } from '../application/database';
import { ResponseError } from '../error/response-error';
import {
  ProductOrderResponse,
  CreateProductOrderRequest,
  ProductOrderQueryParams,
  toProductOrderResponse,
  ProductOrder,
  ProductVariation,
} from '../model/product-order-model';
import { OrderValidation } from '../validation/product-order-validation';
import { Validation } from '../validation/validation';
import { Pageable } from '../model/page';
import { User } from '../model/user-model';

export class ProductOrderService {
  static async create(user: User, request: CreateProductOrderRequest): Promise<ProductOrderResponse> {
    // Validate the create request
    const createRequest = Validation.validate(OrderValidation.CREATE, request);

    // Generate a UUID for the new product order
    const productOrderId = uuidv4();

    // Insert the new product order into the database with the UUID
    await db.query(
      'INSERT INTO product_order (id, user_id, product_id, variation_id, departure, number_of_pax, per_pax_price, total_price) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
      [
        productOrderId,
        user.id,
        createRequest.product_id,
        createRequest.variation_id,
        createRequest.departure,
        createRequest.number_of_pax,
        createRequest.per_pax_price,
        createRequest.total_price,
      ]
    );

    // Return the UUID of the new product order
    return await this.get(productOrderId);
  }
  
  static async getAll(queryParams: ProductOrderQueryParams): Promise<Pageable<ProductOrderResponse>> {
    // Validate the query parameters
    const queryRequest = Validation.validate(OrderValidation.QUERY, queryParams);

    // Calculate the offset and limit for pagination
    const skip = (queryRequest.page - 1) * queryRequest.limit;
    const limit = queryRequest.limit;

    // Initialize the filter query and parameters
    let filterQuery = '';
    let filterParams: any[] = [];

    // If the search query is provided, add the search query to the filter
    if (queryRequest.search) {
      const searchTerm = `%${queryRequest.search.toLowerCase()}%`;
      filterQuery = `
        WHERE LOWER(user.full_name) LIKE ? 
        OR LOWER(user.email) LIKE ? 
        OR LOWER(user.whatsapp_number) LIKE ?
      `;
      filterParams = [searchTerm, searchTerm, searchTerm];
    }

    const sortField = queryRequest.sort || 'created_at';
    const sortOrder = queryRequest.order === 'asc' ? 'ASC' : 'DESC';

    // Build the main query to fetch the product orders
    const productOrders = await db.query<
      ProductOrder & { user: User; productVariation: ProductVariation | null; product: { name: string } }
    >(
      `
      SELECT
        po.*,
        JSON_OBJECT(
          'id', user.id,
          'full_name', user.full_name,
          'email', user.email,
          'whatsapp_number', user.whatsapp_number,
          'role', user.role,
          'created_at', user.created_at,
          'updated_at', user.updated_at
        ) AS user,
        JSON_OBJECT(
          'id', IFNULL(pv.id, ''),
          'name', IFNULL(pv.name, ''),
          'price', IFNULL(pv.price, 0)
        ) AS productVariation,
        JSON_OBJECT(
          'name', p.name
        ) AS product
      FROM product_order po
      JOIN user ON po.user_id = user.id
      JOIN product p ON po.product_id = p.id
      LEFT JOIN product_variation pv ON po.variation_id = pv.id
      ${filterQuery}
      ORDER BY ${sortField} ${sortOrder}
      LIMIT ? OFFSET ?
    `,
      [...filterParams, limit, skip]
    );

    // Get the total count of product orders matching the filter criteria
    const count = await db.queryOne<{ total: number }>(
      `
      SELECT COUNT(*) AS total
      FROM product_order po
      JOIN user u ON po.user_id = u.id
      JOIN product p ON po.product_id = p.id
      LEFT JOIN product_variation pv ON po.variation_id = pv.id
      ${filterQuery}
    `,
      filterParams
    );

    // If no product orders are found, throw an error
    if (!count) {
      throw new ResponseError(404, 'Produk order tidak ditemukan');
    }

    // Parse the JSON objects from the database
    productOrders.forEach((order: any) => {
      order.user = JSON.parse(order.user);
      order.productVariation = JSON.parse(order.productVariation);
      order.product = JSON.parse(order.product);
    });

    // Remove the productVariation field if it is empty
    productOrders.forEach((order) => {
      if (order.productVariation && !order.productVariation.id) {
        order.productVariation = null;
      }
    });

    // Return the pageable response
    return {
      data: productOrders.map(toProductOrderResponse),
      pagination: {
        total: count.total,
        current_page: queryRequest.page,
        total_pages: Math.ceil(count.total / queryRequest.limit),
        limit: queryRequest.limit,
      },
    };
  }

  static async getAllByUser(user: User): Promise<ProductOrderResponse[]> {
    // Fetch all product orders for the given user
    const productOrders = await db.query<
      ProductOrder & { user: User; productVariation: ProductVariation | null; product: { name: string } }
    >(
      `
      SELECT
        po.*,
        JSON_OBJECT(
          'id', u.id,
          'full_name', u.full_name,
          'email', u.email,
          'whatsapp_number', u.whatsapp_number,
          'role', u.role,
          'created_at', u.created_at,
          'updated_at', u.updated_at
        ) AS user,
        JSON_OBJECT(
          'id', pv.id,
          'name', pv.name,
          'price', pv.price
        ) AS productVariation,
        JSON_OBJECT(
          'name', p.name
        ) AS product
      FROM product_order po
      JOIN user u ON po.user_id = u.id
      JOIN product p ON po.product_id = p.id
      LEFT JOIN product_variation pv ON po.variation_id = pv.id
      WHERE u.id = ?
    `,
      [user.id]
    );

    // Parse the JSON objects from the database
    productOrders.forEach((order: any) => {
      order.user = JSON.parse(order.user);
      order.productVariation = JSON.parse(order.productVariation);
      order.product = JSON.parse(order.product);
    });

    // Remove the productVariation field if it is empty
    productOrders.forEach((order) => {
      if (order.productVariation && !order.productVariation.id) {
        order.productVariation = null;
      }
    });

    // Return the product orders
    return productOrders.map(toProductOrderResponse);
  }

  static async get(id: string): Promise<ProductOrderResponse> {
    // Fetch the product order from the database
    const productOrder = await db.queryOne<
      ProductOrder & { user: User; productVariation: ProductVariation | null; product: { name: string } }
    >(
      `
        SELECT
          po.*,
          JSON_OBJECT(
            'id', u.id,
            'full_name', u.full_name,
            'email', u.email,
            'whatsapp_number', u.whatsapp_number,
            'role', u.role,
            'created_at', u.created_at,
            'updated_at', u.updated_at
          ) AS user,
           JSON_OBJECT(
            'id', pv.id,
            'name', pv.name,
            'price', pv.price
          ) AS productVariation,
          JSON_OBJECT(
            'name', p.name
          ) AS product
        FROM product_order po
        JOIN user u ON po.user_id = u.id
        JOIN product p ON po.product_id = p.id
        LEFT JOIN product_variation pv ON po.variation_id = pv.id
        WHERE po.id = ?
      `,
      [id]
    );

    // If the product order does not exist, throw an error
    if (!productOrder) {
      throw new ResponseError(404, 'Produk order tidak ditemukan');
    }

    // Parse the JSON objects from the database
    productOrder.user = typeof productOrder.user === 'string' ? JSON.parse(productOrder.user) : productOrder.user;
    productOrder.productVariation =
      typeof productOrder.productVariation === 'string'
        ? JSON.parse(productOrder.productVariation)
        : productOrder.productVariation;
    productOrder.product =
      typeof productOrder.product === 'string' ? JSON.parse(productOrder.product) : productOrder.product;

    // Remove the productVariation field if it is empty
    if (productOrder.productVariation && !productOrder.productVariation.id) {
      productOrder.productVariation = null;
    }

    // Return the product order response
    return toProductOrderResponse(productOrder);
  }

  static async delete(id: string): Promise<void> {
    // Fetch the product order from the database
    const productOrder = await db.query(
      `
      SELECT product_order.id
      FROM product_order
      WHERE product_order.id = ?
    `,
      [id]
    );

    // If the product order does not exist, throw an error
    if (productOrder.length === 0) {
      throw new ResponseError(404, 'Produk order tidak ditemukan');
    }

    // Delete the product order from the database
    await db.query(
      `
      DELETE FROM product_order
      WHERE id = ?
    `,
      [id]
    );
  }
}
