`src/models/Activity.ts`

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

export interface Activity {
  id?: number;
  name: string;
  category: string;
  description?: string;
  phone?: string;
  website?: string;
  mapsLink?: string;
  reviewLink?: string;
  businessId?: string;
  rating?: number;
  contactStatus?: 'available' | 'unavailable';
  created_at?: Date;
  updated_at?: Date;
}

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

export class ActivityModel {
  private pool: Pool;

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

  /**
   * Validate activity data
   */
  private validate(activity: Partial<Activity>): ActivityValidationError[] {
    const errors: ActivityValidationError[] = [];

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

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

    // Validate URL format for website if provided
    if (activity.website && !this.isValidUrl(activity.website)) {
      errors.push({ field: 'website', message: 'Website must be a valid URL' });
    }

    // Validate URL format for mapsLink if provided
    if (activity.mapsLink && !this.isValidUrl(activity.mapsLink)) {
      errors.push({ field: 'mapsLink', message: 'Maps link must be a valid URL' });
    }

    // Validate URL format for reviewLink if provided
    if (activity.reviewLink && !this.isValidUrl(activity.reviewLink)) {
      errors.push({ field: 'reviewLink', message: 'Review link must be a valid URL' });
    }

    // Validate contactStatus if provided
    if (activity.contactStatus && !['available', 'unavailable'].includes(activity.contactStatus)) {
      errors.push({ field: 'contactStatus', message: 'Contact status must be either "available" or "unavailable"' });
    }

    // Validate rating if provided
    if (activity.rating !== undefined && activity.rating !== null) {
      if (typeof activity.rating !== 'number' || activity.rating < 0 || activity.rating > 5) {
        errors.push({ field: 'rating', message: 'Rating must be a number between 0 and 5' });
      }
    }

    // Validate phone if provided
    if (activity.phone && activity.phone.trim().length > 0) {
      const phoneValidation = validatePhoneNumber(activity.phone);
      if (!phoneValidation.isValid) {
        errors.push({ field: 'phone', message: phoneValidation.error || 'Invalid phone number' });
      }
    }

    return errors;
  }

  /**
   * Validate URL format
   */
  private isValidUrl(url: string): boolean {
    try {
      new URL(url);
      return true;
    } catch {
      return false;
    }
  }

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

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

    try {
      const query = `
        INSERT INTO activities (name, category, description, phone, website, mapsLink, reviewLink, businessId, rating, contactStatus)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
        RETURNING *
      `;

      const values = [
        activity.name.trim(),
        activity.category.trim(),
        activity.description?.trim() || null,
        activity.phone ? formatPhoneNumber(activity.phone.trim()) : null,
        activity.website?.trim() || null,
        activity.mapsLink?.trim() || null,
        activity.reviewLink?.trim() || null,
        activity.businessId?.trim() || null,
        activity.rating || null,
        activity.contactStatus || null
      ];

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

  /**
   * Get activity by ID
   */
  async findById(id: number): Promise<Activity | null> {
    try {
      const query = 'SELECT * FROM activities WHERE id = $1';
      const result: QueryResult<Activity> = await this.pool.query(query, [id]);

      if (result.rows.length === 0) {
        return null;
      }

      return result.rows[0];
    } catch (error) {
      console.error('Error finding activity:', error);
      throw new Error(`Failed to find activity: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }
  }

  /**
   * Get all activities
   */
  async findAll(limit: number = 100, offset: number = 0): Promise<Activity[]> {
    try {
      const query = 'SELECT * FROM activities ORDER BY created_at DESC LIMIT $1 OFFSET $2';
      const result: QueryResult<Activity> = await this.pool.query(query, [limit, offset]);

      return result.rows;
    } catch (error) {
      console.error('Error finding activities:', error);
      throw new Error(`Failed to find activities: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }
  }

  /**
   * Get activities by category
   */
  async findByCategory(category: string, limit: number = 100, offset: number = 0): Promise<Activity[]> {
    try {
      const query = 'SELECT * FROM activities WHERE category = $1 ORDER BY created_at DESC LIMIT $2 OFFSET $3';
      const result: QueryResult<Activity> = await this.pool.query(query, [category, limit, offset]);

      return result.rows;
    } catch (error) {
      console.error('Error finding activities by category:', error);
      throw new Error(`Failed to find activities by category: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }
  }

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

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

    try {
      const query = `
        UPDATE activities
        SET name = COALESCE($1, name),
            category = COALESCE($2, category),
            description = COALESCE($3, description),
            phone = COALESCE($4, phone),
            website = COALESCE($5, website),
            mapsLink = COALESCE($6, mapsLink),
            reviewLink = COALESCE($7, reviewLink),
            businessId = COALESCE($8, businessId),
            rating = COALESCE($9, rating),
            contactStatus = COALESCE($10, contactStatus),
            updated_at = NOW()
        WHERE id = $11
        RETURNING *
      `;

      const values = [
        activity.name?.trim() || null,
        activity.category?.trim() || null,
        activity.description?.trim() || null,
        activity.phone ? formatPhoneNumber(activity.phone.trim()) : null,
        activity.website?.trim() || null,
        activity.mapsLink?.trim() || null,
        activity.reviewLink?.trim() || null,
        activity.businessId?.trim() || null,
        activity.rating || null,
        activity.contactStatus || null,
        id
      ];

      const result: QueryResult<Activity> = await this.pool.query(query, values);

      if (result.rows.length === 0) {
        return null;
      }

      return result.rows[0];
    } catch (error) {
      console.error('Error updating activity:', error);
      throw new Error(`Failed to update activity: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }
  }

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

  /**
   * Bulk create activities
   * Returns results with successes and failures
   */
  async bulkCreate(activities: Omit<Activity, 'id' | 'created_at' | 'updated_at'>[]): Promise<BulkImportResult> {
    const results: BulkImportResult = {
      totalCount: activities.length,
      successCount: 0,
      failureCount: 0,
      successes: [],
      failures: []
    };

    for (let i = 0; i < activities.length; i++) {
      const activity = activities[i];

      try {
        // Validate the activity
        const errors = this.validate(activity);

        if (errors.length > 0) {
          results.failures.push({
            index: i,
            activity,
            error: `Validation failed: ${errors.map(e => `${e.field}: ${e.message}`).join(', ')}`
          });
          results.failureCount++;
          continue;
        }

        // Create the activity
        const created = await this.create(activity);
        results.successes.push({
          index: i,
          activity: created
        });
        results.successCount++;
      } catch (error) {
        results.failures.push({
          index: i,
          activity,
          error: error instanceof Error ? error.message : 'Unknown error'
        });
        results.failureCount++;
      }
    }

    return results;
  }
}

export interface BulkImportResult {
  totalCount: number;
  successCount: number;
  failureCount: number;
  successes: Array<{
    index: number;
    activity: Activity;
  }>;
  failures: Array<{
    index: number;
    activity: Partial<Activity>;
    error: string;
  }>;
}