import { v4 as uuidv4 } from 'uuid';
import { db } from '../application/database';
import { ResponseError } from '../error/response-error';
import {
  HotelResponse,
  CreateHotelRequest,
  UpdateHotelRequest,
  HotelQueryParams,
  BulkUpdateHotelRequest,
  Hotel,
} from '../model/hotel-model';
import { HotelValidation } from '../validation/hotel-validation';
import { Validation } from '../validation/validation';
import { Period } from '../model/period-model';

export class HotelService {
  static async create(request: CreateHotelRequest): Promise<HotelResponse> {
    // Validate the create request
    const createRequest = Validation.validate(HotelValidation.CREATE, request);

    // Create a new hotel in the database
    const hotelId = await this.#createHotel(createRequest);

    // Retrieve the created hotel by the ID
    return this.getById(hotelId);
  }

  static async update(id: string, request: UpdateHotelRequest): Promise<HotelResponse> {
    // Validate the update request
    const updateRequest = Validation.validate(HotelValidation.UPDATE, request);

    // Check if the hotel exists
    await this.#checkHotelExist(id);

    // Update the hotel in the database
    await db.query('UPDATE hotel SET name = ?, city = ?, order_number = ? WHERE id = ?', [
      updateRequest.name,
      updateRequest.city,
      updateRequest.order_number,
      id,
    ]);

    // Retrieve the updated hotel by the ID
    return this.getById(id);
  }

  static async bulkUpdate(request: BulkUpdateHotelRequest): Promise<void> {
    // Validate the bulk update request
    const { modified, deleted } = Validation.validate(HotelValidation.BULK_UPDATE, request);

    // If modified is not empty, update or create the hotel
    if (modified && modified.length > 0) {
      await Promise.all(
        modified.map(async (hotel) => {
          // Check if the hotel already exists
          const existingHotel = await db.queryOne<Hotel>('SELECT * FROM hotel WHERE id = ?', [hotel.hotel_id]);

          // If the hotel does not exist, create a new hotel
          if (!existingHotel) {
            await this.#createHotel({
              vendor_id: hotel.vendor_id,
              name: hotel.name,
              city: hotel.city,
              order_number: hotel.order_number,
              prices: {
                double: hotel.price_double,
                triple: hotel.price_triple,
                quad: hotel.price_quad,
              },
            });
          } else {
            // Update the hotel
            await db.query('UPDATE hotel SET name = ?, order_number = ? WHERE id = ?', [
              hotel.name,
              hotel.order_number,
              hotel.hotel_id,
            ]);

            // Update the hotel period prices
            await db.query(
              'UPDATE hotel_period_price SET price_double = ?, price_triple = ?, price_quad = ? WHERE hotel_id = ? AND period_id = ?',
              [hotel.price_double, hotel.price_triple, hotel.price_quad, hotel.hotel_id, hotel.period_id]
            );
          }
        })
      );
    }

    // If deleted is not empty, delete the hotels
    if (deleted && deleted.length > 0) {
      await db.query('DELETE FROM hotel WHERE id IN (?)', [deleted]);
    }
  }

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

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

  static async getById(id: string): Promise<HotelResponse> {
    // Construct the query with specific field selection for a single hotel by ID
    const query = `
      SELECT 
        h.id AS hotel_id, h.name AS hotel_name, h.vendor_id, h.city, h.order_number,
        h.created_at AS hotel_created_at, h.updated_at AS hotel_updated_at,
        v.id AS vendor_id, v.name AS vendor_name, v.created_at AS vendor_created_at,
        v.updated_at AS vendor_updated_at,
        hp.id AS hotel_period_price_id, hp.hotel_id, hp.period_id, hp.price_double,
        hp.price_triple, hp.price_quad, hp.created_at AS hotel_period_price_created_at,
        hp.updated_at AS hotel_period_price_updated_at,
        p.id AS period_id, p.category, p.start_date, p.end_date,
        p.created_at AS period_created_at, p.updated_at AS period_updated_at
      FROM hotel h
      LEFT JOIN vendor v ON h.vendor_id = v.id
      LEFT JOIN hotel_period_price hp ON h.id = hp.hotel_id
      LEFT JOIN period p ON hp.period_id = p.id
      WHERE h.id = ?
      ORDER BY h.order_number ASC, h.created_at ASC
    `;

    // Execute the query with the hotel ID as the parameter
    const values = [id];
    const rows = await db.query(query, values);

    if (rows.length === 0) {
      throw new ResponseError(404, 'Hotel not found');
    }

    // Aggregate data for a single hotel
    const hotel = rows.reduce<HotelResponse | null>((acc, row: any) => {
      if (!acc) {
        // Create the hotel object if it doesn't exist in the accumulator
        acc = {
          id: row.hotel_id,
          name: row.hotel_name,
          city: row.city,
          order_number: row.order_number,
          vendor: {
            id: row.vendor_id,
            name: row.vendor_name,
            created_at: row.vendor_created_at,
            updated_at: row.vendor_updated_at,
          },
          periods: [],
          created_at: row.hotel_created_at,
          updated_at: row.hotel_updated_at,
        };
      }

      // Append period and price details to the hotel periods array
      acc.periods.push({
        price_double: Number(row.price_double),
        price_triple: Number(row.price_triple),
        price_quad: Number(row.price_quad),
        period: {
          id: row.period_id,
          category: row.category,
          start_date: row.start_date,
          end_date: row.end_date,
          created_at: row.period_created_at,
          updated_at: row.period_updated_at,
        },
      });

      return acc;
    }, null);

    if (!hotel) {
      throw new ResponseError(404, 'Hotel not found');
    }

    return hotel;
  }

  static async getAll(queryParams: HotelQueryParams): Promise<HotelResponse[]> {
    // Validate the query parameters
    const queryRequest = Validation.validate(HotelValidation.QUERY, queryParams);

    // Construct the query with specific field selection
    const query = `
      SELECT 
        h.id AS hotel_id, h.name AS hotel_name, h.vendor_id, h.city, h.order_number,
        h.created_at AS hotel_created_at, h.updated_at AS hotel_updated_at,
        v.id AS vendor_id, v.name AS vendor_name, v.created_at AS vendor_created_at,
        v.updated_at AS vendor_updated_at,
        hp.id AS hotel_period_price_id, hp.hotel_id, hp.period_id, hp.price_double,
        hp.price_triple, hp.price_quad, hp.created_at AS hotel_period_price_created_at,
        hp.updated_at AS hotel_period_price_updated_at,
        p.id AS period_id, p.category, p.start_date, p.end_date,
        p.created_at AS period_created_at, p.updated_at AS period_updated_at
      FROM hotel h
      LEFT JOIN vendor v ON h.vendor_id = v.id
      LEFT JOIN hotel_period_price hp ON h.id = hp.hotel_id
      LEFT JOIN period p ON hp.period_id = p.id
      WHERE 1=1
      ${queryRequest.vendor_id ? 'AND h.vendor_id = ?' : ''}
      ${queryRequest.city ? 'AND h.city = ?' : ''}
      ${queryRequest.period_id ? 'AND hp.period_id = ?' : ''}
      ORDER BY h.order_number ASC, h.created_at ASC
    `;

    const values = [
      ...(queryRequest.vendor_id ? [queryRequest.vendor_id] : []),
      ...(queryRequest.city ? [queryRequest.city] : []),
      ...(queryRequest.period_id ? [queryRequest.period_id] : []),
    ];

    const rows = await db.query(query, values);

    const hotels = rows.reduce((acc: HotelResponse[], row: any) => {
      let hotel = acc.find((h) => h.id === row.hotel_id);
      if (!hotel) {
        hotel = {
          id: row.hotel_id,
          name: row.hotel_name,
          city: row.city,
          order_number: row.order_number,
          vendor: {
            id: row.vendor_id,
            name: row.vendor_name,
            created_at: row.vendor_created_at,
            updated_at: row.vendor_updated_at,
          },
          periods: [],
          created_at: row.hotel_created_at,
          updated_at: row.hotel_updated_at,
        };
        acc.push(hotel);
      }

      hotel.periods.push({
        price_double: Number(row.price_double),
        price_triple: Number(row.price_triple),
        price_quad: Number(row.price_quad),
        period: {
          id: row.period_id,
          category: row.category,
          start_date: row.start_date,
          end_date: row.end_date,
          created_at: row.period_created_at,
          updated_at: row.period_updated_at,
        },
      });

      return acc;
    }, []);

    return hotels;
  }

  static async getAllHotelPeriodPrices(): Promise<HotelResponse[]> {
    // Construct the query to fetch hotels, vendors, and their period prices
    const query = `
      SELECT 
        h.id AS hotel_id, h.name AS hotel_name, h.vendor_id, h.city, h.order_number,
        h.created_at AS hotel_created_at, h.updated_at AS hotel_updated_at,
        v.id AS vendor_id, v.name AS vendor_name, v.created_at AS vendor_created_at,
        v.updated_at AS vendor_updated_at,
        hp.id AS hotel_period_price_id, hp.hotel_id, hp.period_id, hp.price_double,
        hp.price_triple, hp.price_quad, hp.created_at AS hotel_period_price_created_at,
        hp.updated_at AS hotel_period_price_updated_at,
        p.id AS period_id, p.category, p.start_date, p.end_date,
        p.created_at AS period_created_at, p.updated_at AS period_updated_at
      FROM hotel h
      LEFT JOIN vendor v ON h.vendor_id = v.id
      LEFT JOIN hotel_period_price hp ON h.id = hp.hotel_id
      LEFT JOIN period p ON hp.period_id = p.id
      ORDER BY h.order_number ASC
    `;

    // Execute the query to retrieve the data
    const rows = await db.query(query);

    // Map rows to response format, grouping by hotel and handling nested relationships
    const hotels = rows.reduce((acc: HotelResponse[], row: any) => {
      let hotel = acc.find((h) => h.id === row.hotel_id);
      if (!hotel) {
        // Create a new hotel entry if it doesn't already exist
        hotel = {
          id: row.hotel_id,
          name: row.hotel_name,
          city: row.city,
          order_number: row.order_number,
          vendor: {
            id: row.vendor_id,
            name: row.vendor_name,
            created_at: row.vendor_created_at,
            updated_at: row.vendor_updated_at,
          },
          periods: [],
          created_at: row.hotel_created_at,
          updated_at: row.hotel_updated_at,
        };
        acc.push(hotel);
      }

      // Add period pricing information to the hotel's periods array
      hotel.periods.push({
        price_double: Number(row.price_double),
        price_triple: Number(row.price_triple),
        price_quad: Number(row.price_quad),
        period: {
          id: row.period_id,
          category: row.category,
          start_date: row.start_date,
          end_date: row.end_date,
          created_at: row.period_created_at,
          updated_at: row.period_updated_at,
        },
      });

      return acc;
    }, []);

    return hotels;
  }

  static async #checkHotelExist(id: string): Promise<void> {
    // Check if the hotel exists
    const hotel = await db.queryOne<Hotel>('SELECT * FROM hotel WHERE id = ?', [id]);

    if (!hotel) {
      throw new ResponseError(404, 'Hotel not found');
    }
  }

  static async #createHotel(data: CreateHotelRequest): Promise<string> {
    // Create a new hotel in the database
    const hotelId = uuidv4();
    await db.query('INSERT INTO hotel (id, vendor_id, name, city, order_number) VALUES (?, ?, ?, ?, ?)', [
      hotelId,
      data.vendor_id,
      data.name,
      data.city,
      data.order_number,
    ]);

    // Retrieve all periods from the database
    const periods = await db.query<Period>('SELECT * FROM period');

    // Create the hotel period prices in the database
    const periodPricesData = periods.map((period) => [
      uuidv4(),
      hotelId,
      period.id,
      data.prices.double,
      data.prices.triple,
      data.prices.quad,
    ]);
    await db.query(
      'INSERT INTO hotel_period_price (id, hotel_id, period_id, price_double, price_triple, price_quad) VALUES ?',
      [periodPricesData]
    );

    return hotelId;
  }
}
