import { v4 as uuidv4 } from 'uuid';
import { db } from '../application/database';
import { ResponseError } from '../error/response-error';
import {
  CreateMitraPackageOrderRequest,
  MitraPackageOrder,
  MitraPackageOrderQueryParams,
  MitraPackageOrderResponse,
} from '../model/mitra-package-order-model';
import { User } from '../model/user-model';
import { MitraPackageOrderValidation } from '../validation/mitra-package-order-validation';
import { Validation } from '../validation/validation';
import { Pageable } from '../model/page';

export class MitraPackageOrderService {
  static async create(user: User, request: CreateMitraPackageOrderRequest): Promise<void> {
    // Validate the create request
    const createRequest = Validation.validate(MitraPackageOrderValidation.CREATE, request);

    // Check if the mitra package exists
    const mitraPackage = await db.queryOne<{ id: string }>('SELECT id FROM mitra_package WHERE id = ? LIMIT 1', [
      createRequest.mitra_package_id,
    ]);

    if (!mitraPackage) {
      throw new ResponseError(404, 'Mitra package not found');
    }

    // Generate a UUID for the new mitra package order
    const mitraPackageOrderId = uuidv4();

    // Insert the new mitra package order into the database with the UUID
    await db.query('INSERT INTO mitra_package_order (id, user_id, mitra_package_id) VALUES (?, ?, ?)', [
      mitraPackageOrderId,
      user.id,
      createRequest.mitra_package_id,
    ]);
  }

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

    // Calculate the offset and limit for pagination
    const skip = (queryRequest.page - 1) * queryRequest.limit;
    const limit = queryRequest.limit;
    let filterQuery = '';
    let filterParams: any[] = [];

    // Build the WHERE clause for filtering by search term
    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];
    }

    // Build the ORDER BY clause for sorting
    const sortFieldMap: { [key: string]: string } = {
      'User.email': 'User.email',
      'User.full_name': 'User.full_name',
      'User.whatsapp_number': 'User.whatsapp_number',
      'mitra_package.per_pax_price_double': 'mitra_package.per_pax_price_double',
      'mitra_package.per_pax_price_triple': 'mitra_package.per_pax_price_triple',
      'mitra_package.per_pax_price_quad': 'mitra_package.per_pax_price_quad',
      created_at: 'mitra_package_order.created_at',
      updated_at: 'mitra_package_order.updated_at',
    };

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

    // Build the main query to fetch the mitra package orders
    const mainQuery = `
      SELECT mitra_package_order.*, 
             user.id as user_id, user.full_name, user.email, user.whatsapp_number, user.role,
             mitra_package.id as mitra_package_id, mitra_package.per_pax_price_double, 
             mitra_package.per_pax_price_triple, mitra_package.per_pax_price_quad
      FROM mitra_package_order
      JOIN user ON mitra_package_order.user_id = user.id
      JOIN mitra_package ON mitra_package_order.mitra_package_id = mitra_package.id
      ${filterQuery}
      ORDER BY ${sortField} ${sortOrder}
      LIMIT ? OFFSET ?
    `;

    // Build the query to fetch the total number of results
    const totalCountQuery = `
      SELECT COUNT(*) as total
      FROM mitra_package_order
      JOIN User ON mitra_package_order.user_id = User.id
      ${filterQuery}
    `;

    try {
      // Fetch the mitra package orders and total number of results from the database
      const mitraPackageOrders = await db.query<MitraPackageOrder>(mainQuery, [...filterParams, limit, skip]);
      const totalResult = await db.query<{ total: number }>(totalCountQuery, filterParams);
      const total = totalResult[0].total;

      // Transform the database rows to match the expected MitraPackageOrderResponse structure
      const formattedOrders = mitraPackageOrders.map((order) => ({
        id: order.id,
        user: {
          id: order.user_id,
          full_name: order.full_name,
          email: order.email,
          whatsapp_number: order.whatsapp_number,
          role: order.role,
          created_at: new Date(),
          updated_at: new Date(),
        },
        mitra_package_id: order.mitra_package_id,
        per_pax_price_double: Number(order.per_pax_price_double),
        per_pax_price_triple: Number(order.per_pax_price_triple),
        per_pax_price_quad: Number(order.per_pax_price_quad),
        created_at: order.created_at,
        updated_at: order.updated_at,
      }));

      return {
        data: formattedOrders,
        pagination: {
          total,
          current_page: queryRequest.page,
          total_pages: Math.ceil(total / queryRequest.limit),
          limit: queryRequest.limit,
        },
      };
    } catch (error) {
      console.error(error);
      throw new Error('Failed to fetch MitraPackage orders');
    }
  }

  static async getAllByUser(user: User): Promise<MitraPackageOrderResponse[]> {
    // Fetch the mitra package orders for the user from the
    const mitraPackageOrders = await db.query<MitraPackageOrder>(
      `
      SELECT mitra_package_order.*, 
             user.id as user_id, user.full_name, user.email, user.whatsapp_number, user.role,
             mitra_package.id as mitra_package_id, mitra_package.per_pax_price_double, 
             mitra_package.per_pax_price_triple, mitra_package.per_pax_price_quad
      FROM mitra_package_order
      JOIN user ON mitra_package_order.user_id = user.id
      JOIN mitra_package ON mitra_package_order.mitra_package_id = mitra_package.id
      WHERE user.id = ?
    `,
      [user.id]
    );

    // Transform the database rows to match the expected MitraPackageOrderResponse structure
    return mitraPackageOrders.map((order) => ({
      id: order.id,
      user: {
        id: order.user_id,
        full_name: order.full_name,
        email: order.email,
        whatsapp_number: order.whatsapp_number,
        role: order.role,
        created_at: new Date(),
        updated_at: new Date(),
      },
      mitra_package_id: order.mitra_package_id,
      per_pax_price_double: Number(order.per_pax_price_double),
      per_pax_price_triple: Number(order.per_pax_price_triple),
      per_pax_price_quad: Number(order.per_pax_price_quad),
      created_at: order.created_at,
      updated_at: order.updated_at,
    }));
  }

  static async get(id: string): Promise<MitraPackageOrderResponse> {
    // Fetch the mitra package order from the database
    const mitraPackageOrder = await db.queryOne<MitraPackageOrder>(
      `
      SELECT mitra_package_order.*, 
             user.id as user_id, user.full_name, user.email, user.whatsapp_number, user.role,
             mitra_package.id as mitra_package_id, mitra_package.per_pax_price_double, 
             mitra_package.per_pax_price_triple, mitra_package.per_pax_price_quad
      FROM mitra_package_order
      JOIN user ON mitra_package_order.user_id = user.id
      JOIN mitra_package ON mitra_package_order.mitra_package_id = mitra_package.id
      WHERE mitra_package_order.id = ?
    `,
      [id]
    );

    if (!mitraPackageOrder) {
      throw new ResponseError(404, 'Mitra package order not found');
    }

    // Transform the database row to match the expected MitraPackageOrderResponse structure
    return {
      id: mitraPackageOrder.id,
      user: {
        id: mitraPackageOrder.user_id,
        full_name: mitraPackageOrder.full_name,
        email: mitraPackageOrder.email,
        whatsapp_number: mitraPackageOrder.whatsapp_number,
        role: mitraPackageOrder.role,
        created_at: new Date(),
        updated_at: new Date(),
      },
      mitra_package_id: mitraPackageOrder.mitra_package_id,
      per_pax_price_double: Number(mitraPackageOrder.per_pax_price_double),
      per_pax_price_triple: Number(mitraPackageOrder.per_pax_price_triple),
      per_pax_price_quad: Number(mitraPackageOrder.per_pax_price_quad),
      created_at: mitraPackageOrder.created_at,
      updated_at: mitraPackageOrder.updated_at,
    };
  }

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

    if (!mitraPackageOrder) {
      throw new ResponseError(404, 'Mitra package order not found');
    }

    // Delete the mitra package order from the database
    await db.query('DELETE FROM mitra_package_order WHERE id = ?', [id]);
  }
}
