← back to Jill Website

src/models/Inquiry.ts

204 lines

import { Pool, QueryResult } from 'pg';
import { getPool } from '../config/database';
import { validatePhoneNumber, formatPhoneNumber } from '../utils/phoneFormatter';

export interface Inquiry {
  id?: number;
  name: string;
  email: string;
  phone: string;
  check_in_date: Date;
  check_out_date: Date;
  guest_count: number;
  message: string;
  created_at?: Date;
  updated_at?: Date;
}

export interface InquiryValidationError {
  field: string;
  message: string;
}

export class InquiryModel {
  private pool: Pool;

  constructor() {
    this.pool = getPool();
  }

  /**
   * Validate inquiry data
   */
  private validate(inquiry: Partial<Inquiry>): InquiryValidationError[] {
    const errors: InquiryValidationError[] = [];

    if (!inquiry.name || inquiry.name.trim().length === 0) {
      errors.push({ field: 'name', message: 'Name is required' });
    }

    if (!inquiry.email || !this.isValidEmail(inquiry.email.trim())) {
      errors.push({ field: 'email', message: 'Valid email is required' });
    }

    if (!inquiry.phone || inquiry.phone.trim().length === 0) {
      errors.push({ field: 'phone', message: 'Phone number is required' });
    } else {
      const phoneValidation = validatePhoneNumber(inquiry.phone);
      if (!phoneValidation.isValid) {
        errors.push({ field: 'phone', message: phoneValidation.error || 'Invalid phone number' });
      }
    }

    if (!inquiry.check_in_date) {
      errors.push({ field: 'check_in_date', message: 'Check-in date is required' });
    }

    if (!inquiry.check_out_date) {
      errors.push({ field: 'check_out_date', message: 'Check-out date is required' });
    }

    if (inquiry.check_in_date && inquiry.check_out_date) {
      const checkIn = new Date(inquiry.check_in_date);
      const checkOut = new Date(inquiry.check_out_date);

      if (checkOut <= checkIn) {
        errors.push({ field: 'check_out_date', message: 'Check-out date must be after check-in date' });
      }
    }

    if (!inquiry.guest_count || inquiry.guest_count < 1) {
      errors.push({ field: 'guest_count', message: 'Guest count must be at least 1' });
    }

    if (!inquiry.message || inquiry.message.trim().length === 0) {
      errors.push({ field: 'message', message: 'Message is required' });
    }

    return errors;
  }

  /**
   * Validate email format
   */
  private isValidEmail(email: string): boolean {
    const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
    return emailRegex.test(email);
  }

  /**
   * Create a new inquiry
   */
  async create(inquiry: Omit<Inquiry, 'id' | 'created_at' | 'updated_at'>): Promise<Inquiry> {
    const errors = this.validate(inquiry);

    if (errors.length > 0) {
      throw new Error(`Validation failed: ${errors.map(e => `${e.field}: ${e.message}`).join(', ')}`);
    }

    try {
      const query = `
        INSERT INTO inquiries (name, email, phone, check_in_date, check_out_date, guest_count, message)
        VALUES ($1, $2, $3, $4, $5, $6, $7)
        RETURNING *
      `;

      const values = [
        inquiry.name.trim(),
        inquiry.email.trim().toLowerCase(),
        formatPhoneNumber(inquiry.phone.trim()),
        inquiry.check_in_date,
        inquiry.check_out_date,
        inquiry.guest_count,
        inquiry.message.trim()
      ];

      const result: QueryResult<Inquiry> = await this.pool.query(query, values);
      return result.rows[0];
    } catch (error) {
      console.error('Error creating inquiry:', error);
      throw new Error(`Failed to create inquiry: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }
  }

  /**
   * Get inquiry by ID
   */
  async findById(id: number): Promise<Inquiry | null> {
    try {
      const query = 'SELECT * FROM inquiries WHERE id = $1';
      const result: QueryResult<Inquiry> = await this.pool.query(query, [id]);
      return result.rows[0] || null;
    } catch (error) {
      console.error('Error finding inquiry:', error);
      throw new Error(`Failed to find inquiry: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }
  }

  /**
   * Get all inquiries
   */
  async findAll(limit: number = 100, offset: number = 0): Promise<Inquiry[]> {
    try {
      const query = 'SELECT * FROM inquiries ORDER BY created_at DESC LIMIT $1 OFFSET $2';
      const result: QueryResult<Inquiry> = await this.pool.query(query, [limit, offset]);
      return result.rows;
    } catch (error) {
      console.error('Error finding inquiries:', error);
      throw new Error(`Failed to find inquiries: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }
  }

  /**
   * Update inquiry
   */
  async update(id: number, inquiry: Partial<Inquiry>): Promise<Inquiry | null> {
    const errors = this.validate(inquiry);

    if (errors.length > 0) {
      throw new Error(`Validation failed: ${errors.map(e => `${e.field}: ${e.message}`).join(', ')}`);
    }

    try {
      const query = `
        UPDATE inquiries
        SET name = $1, email = $2, phone = $3, check_in_date = $4, check_out_date = $5,
            guest_count = $6, message = $7, updated_at = NOW()
        WHERE id = $8
        RETURNING *
      `;

      const values = [
        inquiry.name?.trim(),
        inquiry.email?.trim().toLowerCase(),
        inquiry.phone ? formatPhoneNumber(inquiry.phone.trim()) : null,
        inquiry.check_in_date,
        inquiry.check_out_date,
        inquiry.guest_count,
        inquiry.message?.trim(),
        id
      ];

      const result: QueryResult<Inquiry> = await this.pool.query(query, values);
      return result.rows[0] || null;
    } catch (error) {
      console.error('Error updating inquiry:', error);
      throw new Error(`Failed to update inquiry: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }
  }

  /**
   * Delete inquiry
   */
  async delete(id: number): Promise<boolean> {
    try {
      const query = 'DELETE FROM inquiries WHERE id = $1';
      const result = await this.pool.query(query, [id]);
      return result.rowCount !== null && result.rowCount > 0;
    } catch (error) {
      console.error('Error deleting inquiry:', error);
      throw new Error(`Failed to delete inquiry: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }
  }
}