← back to Jill Website

src/models/LinkCheck.ts

165 lines

import { Pool, QueryResult } from 'pg';
import { getPool } from '../config/database';

export interface LinkCheck {
  id?: number;
  url: string;
  source_type: string;
  source_id: number;
  http_status?: number;
  is_valid: boolean;
  error_message?: string;
  response_time_ms: number;
  last_checked_at?: Date;
  created_at?: Date;
  updated_at?: Date;
}

export class LinkCheckModel {
  private pool: Pool;

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

  /**
   * Create or update a link check record
   */
  async upsert(linkCheck: Omit<LinkCheck, 'id' | 'created_at' | 'updated_at' | 'last_checked_at'>): Promise<LinkCheck> {
    try {
      const query = `
        INSERT INTO link_checks (url, source_type, source_id, http_status, is_valid, error_message, response_time_ms, last_checked_at)
        VALUES ($1, $2, $3, $4, $5, $6, $7, NOW())
        ON CONFLICT (url, source_type, source_id)
        DO UPDATE SET
          http_status = EXCLUDED.http_status,
          is_valid = EXCLUDED.is_valid,
          error_message = EXCLUDED.error_message,
          response_time_ms = EXCLUDED.response_time_ms,
          last_checked_at = NOW(),
          updated_at = NOW()
        RETURNING *
      `;

      const values = [
        linkCheck.url,
        linkCheck.source_type,
        linkCheck.source_id,
        linkCheck.http_status || null,
        linkCheck.is_valid,
        linkCheck.error_message || null,
        linkCheck.response_time_ms,
      ];

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

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

  /**
   * Get broken links only
   */
  async findBrokenLinks(limit: number = 100, offset: number = 0): Promise<LinkCheck[]> {
    try {
      const query = 'SELECT * FROM link_checks WHERE is_valid = false ORDER BY last_checked_at DESC LIMIT $1 OFFSET $2';
      const result: QueryResult<LinkCheck> = await this.pool.query(query, [limit, offset]);
      return result.rows;
    } catch (error) {
      console.error('Error finding broken links:', error);
      throw new Error(`Failed to find broken links: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }
  }

  /**
   * Get link checks by source
   */
  async findBySource(sourceType: string, sourceId: number): Promise<LinkCheck[]> {
    try {
      const query = 'SELECT * FROM link_checks WHERE source_type = $1 AND source_id = $2 ORDER BY last_checked_at DESC';
      const result: QueryResult<LinkCheck> = await this.pool.query(query, [sourceType, sourceId]);
      return result.rows;
    } catch (error) {
      console.error('Error finding link checks by source:', error);
      throw new Error(`Failed to find link checks by source: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }
  }

  /**
   * Get link check by URL
   */
  async findByUrl(url: string): Promise<LinkCheck | null> {
    try {
      const query = 'SELECT * FROM link_checks WHERE url = $1 ORDER BY last_checked_at DESC LIMIT 1';
      const result: QueryResult<LinkCheck> = await this.pool.query(query, [url]);

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

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

  /**
   * Delete old link check records (cleanup)
   */
  async deleteOldRecords(daysOld: number = 90): Promise<number> {
    try {
      const query = `DELETE FROM link_checks WHERE last_checked_at < NOW() - INTERVAL '${daysOld} days'`;
      const result = await this.pool.query(query);
      return result.rowCount || 0;
    } catch (error) {
      console.error('Error deleting old link checks:', error);
      throw new Error(`Failed to delete old link checks: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }
  }

  /**
   * Get summary statistics
   */
  async getStats(): Promise<{ total: number; valid: number; invalid: number; avgResponseTime: number }> {
    try {
      const query = `
        SELECT
          COUNT(*) as total,
          SUM(CASE WHEN is_valid = true THEN 1 ELSE 0 END) as valid,
          SUM(CASE WHEN is_valid = false THEN 1 ELSE 0 END) as invalid,
          COALESCE(AVG(response_time_ms), 0) as avg_response_time
        FROM link_checks
      `;
      const result = await this.pool.query(query);
      const row = result.rows[0];

      return {
        total: parseInt(row.total, 10),
        valid: parseInt(row.valid, 10),
        invalid: parseInt(row.invalid, 10),
        avgResponseTime: parseFloat(row.avg_response_time),
      };
    } catch (error) {
      console.error('Error getting link check stats:', error);
      throw new Error(`Failed to get link check stats: ${error instanceof Error ? error.message : 'Unknown error'}`);
    }
  }
}