`backend/src/models/Organization.js`

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

class Organization {
  // Create new organization
  static async create(orgData) {
    const {
      name,
      website_url,
      cypres_page_url,
      mission_statement,
      logo_url,
      ein,
      founded_year,
      location_city,
      location_state,
      location_country = 'USA'
    } = orgData;

    const sql = `
      INSERT INTO organizations
      (name, website_url, cypres_page_url, mission_statement, logo_url, ein,
       founded_year, location_city, location_state, location_country)
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
      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,
        founded_year = EXCLUDED.founded_year,
        location_city = EXCLUDED.location_city,
        location_state = EXCLUDED.location_state,
        location_country = EXCLUDED.location_country,
        updated_at = CURRENT_TIMESTAMP,
        last_verified = CURRENT_TIMESTAMP
      RETURNING *
    `;

    const result = await query(sql, [
      name, website_url, cypres_page_url, mission_statement, logo_url,
      ein, founded_year, location_city, location_state, location_country
    ]);

    return result.rows[0];
  }

  // Get organization by ID
  static async findById(id) {
    const sql = 'SELECT * FROM organizations WHERE id = $1';
    const result = await query(sql, [id]);
    return result.rows[0];
  }

  // Search organizations with filters
  static async search(filters = {}) {
    const {
      searchTerm,
      legalTopics,
      categories,
      state,
      limit = 50,
      offset = 0
    } = filters;

    let sql = `
      SELECT DISTINCT o.*,
        COALESCE(array_agg(DISTINCT c.name) FILTER (WHERE c.name IS NOT NULL), ARRAY[]::text[]) as categories,
        COALESCE(array_agg(DISTINCT lt.name) FILTER (WHERE lt.name IS NOT NULL), ARRAY[]::text[]) as legal_topics
      FROM organizations o
      LEFT JOIN organization_categories oc ON o.id = oc.organization_id
      LEFT JOIN categories c ON oc.category_id = c.id
      LEFT JOIN organization_legal_topics olt ON o.id = olt.organization_id
      LEFT JOIN legal_topics lt ON olt.legal_topic_id = lt.id
      WHERE o.is_active = true
    `;

    const params = [];
    let paramCount = 1;

    if (searchTerm) {
      sql += ` AND (
        to_tsvector('english', o.name) @@ plainto_tsquery('english', $${paramCount})
        OR to_tsvector('english', o.mission_statement) @@ plainto_tsquery('english', $${paramCount})
      )`;
      params.push(searchTerm);
      paramCount++;
    }

    if (legalTopics && legalTopics.length > 0) {
      sql += ` AND EXISTS (
        SELECT 1 FROM organization_legal_topics olt2
        JOIN legal_topics lt2 ON olt2.legal_topic_id = lt2.id
        WHERE olt2.organization_id = o.id
        AND lt2.name = ANY($${paramCount})
      )`;
      params.push(legalTopics);
      paramCount++;
    }

    if (categories && categories.length > 0) {
      sql += ` AND EXISTS (
        SELECT 1 FROM organization_categories oc2
        JOIN categories c2 ON oc2.category_id = c2.id
        WHERE oc2.organization_id = o.id
        AND c2.name = ANY($${paramCount})
      )`;
      params.push(categories);
      paramCount++;
    }

    if (state) {
      sql += ` AND o.location_state = $${paramCount}`;
      params.push(state);
      paramCount++;
    }

    sql += `
      GROUP BY o.id
      ORDER BY o.name
      LIMIT $${paramCount} OFFSET $${paramCount + 1}
    `;
    params.push(limit, offset);

    const result = await query(sql, params);
    return result.rows;
  }

  // Add categories to organization
  static async addCategories(orgId, categoryNames) {
    for (const categoryName of categoryNames) {
      const categorySql = 'SELECT id FROM categories WHERE name = $1';
      const categoryResult = await query(categorySql, [categoryName]);

      if (categoryResult.rows.length > 0) {
        const categoryId = categoryResult.rows[0].id;
        const linkSql = `
          INSERT INTO organization_categories (organization_id, category_id)
          VALUES ($1, $2)
          ON CONFLICT (organization_id, category_id) DO NOTHING
        `;
        await query(linkSql, [orgId, categoryId]);
      }
    }
  }

  // Add legal topics to organization
  static async addLegalTopics(orgId, legalTopicNames) {
    for (const topicName of legalTopicNames) {
      const topicSql = 'SELECT id FROM legal_topics WHERE name = $1';
      const topicResult = await query(topicSql, [topicName]);

      if (topicResult.rows.length > 0) {
        const topicId = topicResult.rows[0].id;
        const linkSql = `
          INSERT INTO organization_legal_topics (organization_id, legal_topic_id)
          VALUES ($1, $2)
          ON CONFLICT (organization_id, legal_topic_id) DO NOTHING
        `;
        await query(linkSql, [orgId, topicId]);
      }
    }
  }

  // Save cy pres statement
  static async saveCyPresStatement(orgId, statementData) {
    const { statement_text, page_title, html_content } = statementData;

    const sql = `
      INSERT INTO cypres_statements (organization_id, statement_text, page_title, html_content)
      VALUES ($1, $2, $3, $4)
      ON CONFLICT (organization_id)
      DO UPDATE SET
        statement_text = EXCLUDED.statement_text,
        page_title = EXCLUDED.page_title,
        html_content = EXCLUDED.html_content,
        updated_at = CURRENT_TIMESTAMP
      RETURNING *
    `;

    const result = await query(sql, [orgId, statement_text, page_title, html_content]);
    return result.rows[0];
  }

  // Get all categories (only those with active organizations)
  static async getAllCategories() {
    const sql = `
      SELECT DISTINCT c.id, c.name, c.description, COUNT(oc.organization_id) as org_count
      FROM categories c
      INNER JOIN organization_categories oc ON c.id = oc.category_id
      INNER JOIN organizations o ON oc.organization_id = o.id
      WHERE o.is_active = true
      GROUP BY c.id, c.name, c.description
      HAVING COUNT(oc.organization_id) > 0
      ORDER BY c.name
    `;
    const result = await query(sql);
    return result.rows;
  }

  // Get all legal topics (only those with active organizations)
  static async getAllLegalTopics() {
    const sql = `
      SELECT DISTINCT lt.id, lt.name, lt.description, COUNT(olt.organization_id) as org_count
      FROM legal_topics lt
      INNER JOIN organization_legal_topics olt ON lt.id = olt.legal_topic_id
      INNER JOIN organizations o ON olt.organization_id = o.id
      WHERE o.is_active = true
      GROUP BY lt.id, lt.name, lt.description
      HAVING COUNT(olt.organization_id) > 0
      ORDER BY lt.name
    `;
    const result = await query(sql);
    return result.rows;
  }

  // Get organization count
  static async getCount() {
    const sql = 'SELECT COUNT(*) as count FROM organizations WHERE is_active = true';
    const result = await query(sql);
    return parseInt(result.rows[0].count);
  }
}

module.exports = Organization;