import { v4 as uuidv4 } from 'uuid';
import { db } from '../application/database';
import { ResponseError } from '../error/response-error';
import {
  MitraRequestResponse,
  toMitraRequestResponse,
  CreateMitraRequestRequest,
  MitraRequestQueryParams,
  UpdateMitraRequestStatusRequest,
  MitraRequest,
} from '../model/mitra-request-model';
import { MitraRequestValidation } from '../validation/mitra-request-validation';
import { Validation } from '../validation/validation';
import { Pageable } from '../model/page';
import { User } from '../model/user-model';

export class MitraRequestService {
  static async create(user: User, request: CreateMitraRequestRequest): Promise<String> {
    // Validate the create request
    const createRequest = Validation.validate(MitraRequestValidation.CREATE, request);

    // Generate a UUID for the new mitra request
    const mitraRequestId = uuidv4();

    // Insert the new mitra request into the database with the UUID
    await db.query(
      'INSERT INTO mitra_request (id, ktp, upgrade_amount, payment_proof, status, user_id) VALUES (?, ?, ?, ?, ?, ?)',
      [mitraRequestId, createRequest.ktp, createRequest.upgrade_amount, createRequest.payment_proof, 'PENDING', user.id]
    );

    // Return the UUID of the new mitra request
    return mitraRequestId;
  }

  static async get(id: string): Promise<MitraRequestResponse> {
    // Query the database for the mitra request with the given ID
    const mitraRequest = await db.queryOne<MitraRequest & { user: User }>('SELECT * FROM mitra_request WHERE id = ?', [
      id,
    ]);

    // If no mitra request is found, throw a 404 error
    if (!mitraRequest) {
      throw new ResponseError(404, 'Mitra request tidak ditemukan');
    }

    // get the user data from the database
    const user = await db.queryOne<User>('SELECT * FROM user WHERE id = ?', [mitraRequest.user_id]);

    // If no user is found, throw a 404 error
    if (!user) {
      throw new ResponseError(404, 'User tidak ditemukan');
    }

    // Add the user data to the mitra request
    mitraRequest.User = user;

    // Return the mitra request response
    return toMitraRequestResponse(mitraRequest);
  }

  static async getStatusByUser(user: User): Promise<String> {
    // Query the database for the most recent mitra request for the given user
    const mitraRequest = await db.queryOne<MitraRequest>(
      'SELECT status FROM mitra_request WHERE user_id = ? ORDER BY created_at DESC LIMIT 1',
      [user.id]
    );

    // If no mitra request is found, throw a 404 error
    if (!mitraRequest) {
      throw new ResponseError(404, 'Mitra request tidak ditemukan');
    }

    // Return the status of the mitra request
    return mitraRequest.status;
  }

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

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

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

    if (queryRequest.status) {
      filterQuery += ' AND status = ?';
      filterParams.push(queryRequest.status);
    }

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

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

    // Execute the query to get the mitra requests
    const mitraPackages = await db.query<any>(
      `
      SELECT 
        mr.*,
        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
      FROM mitra_request mr
      JOIN user u ON mr.user_id = u.id
      ${filterQuery}
      ORDER BY ${sortField} ${sortOrder}
      LIMIT ? OFFSET ?
    `,
      [...filterParams, queryRequest.limit, skip]
    );

    // Parse the user data from JSON
    mitraPackages.forEach((mitraPackage) => {
      mitraPackage.user = JSON.parse(mitraPackage.user);
    });

    // Get the total count
    const count = await db.queryOne<{ total: number }>(
      `SELECT COUNT(*) AS total FROM mitra_request mr JOIN user u ON mr.user_id = u.id ${filterQuery}`,
      filterParams
    );

    if (!count) {
      throw new ResponseError(404, 'Mitra request tidak ditemukan');
    }

    return {
      data: mitraPackages.map(toMitraRequestResponse),
      pagination: {
        total: count.total,
        current_page: queryRequest.page,
        total_pages: Math.ceil(count.total / queryRequest.limit),
        limit: queryRequest.limit,
      },
    };
  }

  static async updateStatus(id: string, status: UpdateMitraRequestStatusRequest): Promise<void> {
    // Validate the update status request
    const statusRequest = Validation.validate(MitraRequestValidation.UPDATE_STATUS, status);

    // Check if the mitra request exists
    await this.#checkMitraRequestExist(id);

    // Update the status of the mitra request in the database
    await db.query('UPDATE mitra_request SET status = ? WHERE id = ?', [statusRequest.status, id]);

    // If the status is 'APPROVED', update the user role to 'MITRA'
    if (statusRequest.status === 'APPROVED') {
      await db.query('UPDATE user SET role = ? WHERE id = (SELECT user_id FROM mitra_request WHERE id = ?)', [
        'MITRA',
        id,
      ]);
    }
  }

  static async delete(id: string): Promise<void> {
    // Check if the mitra request exists
    await this.#checkMitraRequestExist(id);

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

  static async #checkMitraRequestExist(id: string): Promise<void> {
    // Query the database for the mitra request with the given ID
    const mitraRequest = await db.queryOne<MitraRequest>('SELECT * FROM mitra_request WHERE id = ?', [id]);

    if (!mitraRequest) {
      throw new ResponseError(404, 'Mitra request tidak ditemukan');
    }
  }
}
