← back to Cypress Awards

backend/src/api/routes.js

452 lines

const express = require('express');
const Organization = require('../models/Organization');

const router = express.Router();

// GET /api/organizations - Search and filter organizations
router.get('/organizations', async (req, res) => {
  try {
    const {
      search,
      legalTopics,
      categories,
      state,
      limit = 50,
      offset = 0
    } = req.query;

    const filters = {
      searchTerm: search,
      legalTopics: legalTopics ? legalTopics.split(',') : undefined,
      categories: categories ? categories.split(',') : undefined,
      state,
      limit: parseInt(limit),
      offset: parseInt(offset)
    };

    const organizations = await Organization.search(filters);
    const totalCount = await Organization.getCount();

    res.json({
      success: true,
      data: organizations,
      meta: {
        total: totalCount,
        limit: filters.limit,
        offset: filters.offset,
        count: organizations.length
      }
    });
  } catch (error) {
    console.error('Error fetching organizations:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to fetch organizations'
    });
  }
});

// GET /api/organizations/:id - Get single organization
router.get('/organizations/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const organization = await Organization.findById(id);

    if (!organization) {
      return res.status(404).json({
        success: false,
        error: 'Organization not found'
      });
    }

    res.json({
      success: true,
      data: organization
    });
  } catch (error) {
    console.error('Error fetching organization:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to fetch organization'
    });
  }
});

// GET /api/categories - Get all categories
router.get('/categories', async (req, res) => {
  try {
    const categories = await Organization.getAllCategories();
    res.json({
      success: true,
      data: categories
    });
  } catch (error) {
    console.error('Error fetching categories:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to fetch categories'
    });
  }
});

// GET /api/legal-topics - Get all legal topics
router.get('/legal-topics', async (req, res) => {
  try {
    const topics = await Organization.getAllLegalTopics();
    res.json({
      success: true,
      data: topics
    });
  } catch (error) {
    console.error('Error fetching legal topics:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to fetch legal topics'
    });
  }
});

// GET /api/stats - Get database statistics
router.get('/stats', async (req, res) => {
  try {
    const totalOrgs = await Organization.getCount();
    const categories = await Organization.getAllCategories();
    const legalTopics = await Organization.getAllLegalTopics();

    res.json({
      success: true,
      data: {
        totalOrganizations: totalOrgs,
        totalCategories: categories.length,
        totalLegalTopics: legalTopics.length
      }
    });
  } catch (error) {
    console.error('Error fetching stats:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to fetch statistics'
    });
  }
});

// GET /api/scraping-logs - Get scraping statistics
router.get('/scraping-logs', async (req, res) => {
  try {
    const { query } = require('../models/database');

    // Get counts by status
    const statusCounts = await query(`
      SELECT status, COUNT(*) as count
      FROM scraping_logs
      GROUP BY status
    `);

    // Get recent logs
    const recentLogs = await query(`
      SELECT url, status, error_message, scraped_at
      FROM scraping_logs
      ORDER BY scraped_at DESC
      LIMIT 20
    `);

    const counts = statusCounts.rows.reduce((acc, row) => {
      acc[row.status] = parseInt(row.count);
      return acc;
    }, {});

    const total = statusCounts.rows.reduce((sum, row) => sum + parseInt(row.count), 0);

    res.json({
      success: true,
      data: {
        success: counts.success || 0,
        no_cypres_page: counts.no_cypres_page || 0,
        blocked: counts.blocked || 0,
        failed: counts.failed || 0,
        total: total,
        recent: recentLogs.rows
      }
    });
  } catch (error) {
    console.error('Error fetching scraping logs:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to fetch scraping logs'
    });
  }
});

// GET /api/legal-cases - Get all legal cases with cy pres awards
router.get('/legal-cases', async (req, res) => {
  try {
    const { query } = require('../models/database');
    const {
      court,
      yearFrom,
      yearTo,
      classActionOnly,
      limit = 50,
      offset = 0
    } = req.query;

    let sql = `
      SELECT
        id, case_name, case_number, court, filed_date,
        docket_url, opinion_url, judge, cy_pres_amount,
        recipients, is_class_action, case_summary,
        created_at
      FROM legal_cases
      WHERE 1=1
    `;
    const params = [];
    let paramIndex = 1;

    if (court) {
      sql += ` AND court ILIKE $${paramIndex}`;
      params.push(`%${court}%`);
      paramIndex++;
    }

    if (yearFrom) {
      sql += ` AND EXTRACT(YEAR FROM filed_date) >= $${paramIndex}`;
      params.push(parseInt(yearFrom));
      paramIndex++;
    }

    if (yearTo) {
      sql += ` AND EXTRACT(YEAR FROM filed_date) <= $${paramIndex}`;
      params.push(parseInt(yearTo));
      paramIndex++;
    }

    if (classActionOnly === 'true') {
      sql += ` AND is_class_action = true`;
    }

    sql += ` ORDER BY filed_date DESC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
    params.push(parseInt(limit), parseInt(offset));

    const result = await query(sql, params);

    // Get total count
    const countResult = await query('SELECT COUNT(*) as count FROM legal_cases');

    res.json({
      success: true,
      data: result.rows,
      meta: {
        total: parseInt(countResult.rows[0].count),
        limit: parseInt(limit),
        offset: parseInt(offset),
        count: result.rows.length
      }
    });
  } catch (error) {
    console.error('Error fetching legal cases:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to fetch legal cases'
    });
  }
});

// GET /api/legal-cases/:id - Get single legal case
router.get('/legal-cases/:id', async (req, res) => {
  try {
    const { query } = require('../models/database');
    const { id } = req.params;

    const result = await query('SELECT * FROM legal_cases WHERE id = $1', [id]);

    if (result.rows.length === 0) {
      return res.status(404).json({
        success: false,
        error: 'Legal case not found'
      });
    }

    res.json({
      success: true,
      data: result.rows[0]
    });
  } catch (error) {
    console.error('Error fetching legal case:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to fetch legal case'
    });
  }
});

// GET /api/outreach/no-cypres-page - Get organizations WITHOUT cy pres pages for outreach
router.get('/outreach/no-cypres-page', async (req, res) => {
  try {
    const { query } = require('../models/database');
    const { limit = 100, offset = 0 } = req.query;

    const result = await query(`
      SELECT url, scraped_at
      FROM scraping_logs
      WHERE status = 'no_cypres_page'
      ORDER BY scraped_at DESC
      LIMIT $1 OFFSET $2
    `, [parseInt(limit), parseInt(offset)]);

    const countResult = await query(`
      SELECT COUNT(*) as count
      FROM scraping_logs
      WHERE status = 'no_cypres_page'
    `);

    res.json({
      success: true,
      data: result.rows,
      meta: {
        total: parseInt(countResult.rows[0].count),
        limit: parseInt(limit),
        offset: parseInt(offset),
        count: result.rows.length
      }
    });
  } catch (error) {
    console.error('Error fetching organizations without cy pres:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to fetch organizations'
    });
  }
});

// POST /api/organizations/submit - Manual submission of organization
router.post('/organizations/submit', async (req, res) => {
  try {
    const {
      name,
      website_url,
      cypres_page_url,
      mission_statement,
      logo_url,
      ein,
      location_city,
      location_state,
      email,
      phone,
      contact_name
    } = req.body;

    // Validate required fields
    if (!name || !website_url) {
      return res.status(400).json({
        success: false,
        error: 'Name and website URL are required'
      });
    }

    const { query } = require('../models/database');

    // Insert into organizations table
    const result = await query(`
      INSERT INTO organizations
      (name, website_url, cypres_page_url, mission_statement, logo_url, ein,
       location_city, location_state, location_country)
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 'USA')
      ON CONFLICT (website_url)
      DO UPDATE SET
        name = EXCLUDED.name,
        cypres_page_url = EXCLUDED.cypres_page_url,
        mission_statement = EXCLUDED.mission_statement,
        logo_url = EXCLUDED.logo_url,
        ein = EXCLUDED.ein,
        location_city = EXCLUDED.location_city,
        location_state = EXCLUDED.location_state,
        updated_at = CURRENT_TIMESTAMP
      RETURNING *
    `, [name, website_url, cypres_page_url || null, mission_statement || null,
        logo_url || null, ein || null, location_city || null, location_state || null]);

    // Log the submission
    await query(`
      INSERT INTO scraping_logs (url, status, error_message)
      VALUES ($1, 'manual_submission', $2)
    `, [website_url, `Submitted by: ${contact_name || 'Unknown'}, Email: ${email || 'N/A'}`]);

    res.json({
      success: true,
      message: 'Organization submitted successfully',
      data: result.rows[0]
    });
  } catch (error) {
    console.error('Error submitting organization:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to submit organization'
    });
  }
});

// GET /api/outreach/stats - Get outreach statistics
router.get('/outreach/stats', async (req, res) => {
  try {
    const { query } = require('../models/database');

    const stats = await query(`
      SELECT
        COUNT(CASE WHEN status = 'success' THEN 1 END) as with_cypres,
        COUNT(CASE WHEN status = 'no_cypres_page' THEN 1 END) as without_cypres,
        COUNT(CASE WHEN status = 'blocked' THEN 1 END) as blocked,
        COUNT(CASE WHEN status = 'failed' THEN 1 END) as failed,
        COUNT(CASE WHEN status = 'manual_submission' THEN 1 END) as manual_submissions,
        COUNT(*) as total
      FROM scraping_logs
    `);

    res.json({
      success: true,
      data: stats.rows[0]
    });
  } catch (error) {
    console.error('Error fetching outreach stats:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to fetch statistics'
    });
  }
});

// GET /api/news - Get news articles about cy pres settlements
router.get('/news', async (req, res) => {
  try {
    const { query } = require('../models/database');
    const { limit = 20, offset = 0 } = req.query;

    const result = await query(`
      SELECT id, title, url, published_at, description, source, created_at
      FROM news_articles
      ORDER BY published_at DESC
      LIMIT $1 OFFSET $2
    `, [parseInt(limit), parseInt(offset)]);

    const countResult = await query('SELECT COUNT(*) as count FROM news_articles');

    res.json({
      success: true,
      data: result.rows,
      meta: {
        total: parseInt(countResult.rows[0].count),
        limit: parseInt(limit),
        offset: parseInt(offset),
        count: result.rows.length
      }
    });
  } catch (error) {
    console.error('Error fetching news:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to fetch news articles'
    });
  }
});

module.exports = router;