`lib/data-import.js`

401 lines
/**
 * Data Import — Parse master CSV, split multi-value columns, normalize, score candidates
 */
const fs = require('fs');
const path = require('path');

const MASTER_CSV = '/root/DW-Agents/sdcc/sheet1-master.csv';
const FRESH_CSV = '/tmp/sdcc-sheet2-fresh.csv';
const SHEET_URL = 'https://docs.google.com/spreadsheets/d/1sojrO2NtRb9p-W_sJiCOyEbB-1kRr86yU8v0I0-qlnE/export?format=csv&gid=1691047120';

// ── Column Mapping: Full question headers → short field names ──
const QUESTION_MAP = {
  'Do you currently have FEDERAL student loans?': 'has_federal',
  'What type(s) of federal student loans do you have?': 'loan_types',
  'How much do you currently owe?': 'amount_owed',
  'what year did you take out your FIRST federal student loan?': 'first_loan_year',
  'Which repayment plan are you currently enrolled in?': 'repayment_plan',
  'Are you aware that some repayment plans': 'aware_phaseout',
  'What is your monthly student loan payment?': 'monthly_payment',
  'Describe your current repayment status': 'repayment_status',
  'contacted by the government about your loans being in default': 'contacted_default',
  'notified by the government about any potential collections': 'notified_collections',
  'experienced because of your delinquent or defaulted': 'default_experiences',
  'receive resources about bringing your loans': 'want_resources',
  'counting on any sort of student loan forgiveness': 'counting_forgiveness',
  'specify what program you are counting on': 'forgiveness_program',
  'concerned that rising costs': 'concern_inflation',
  'emotional impact of your student loan debt': 'emotional_impact',
  'impacted your ability to afford': 'affordability_impacts',
  'what would you be spending that money on instead': 'alt_spending',
  'logged into your student loan servicer': 'logged_in_recently',
  'information I receive from my student loan servicer': 'trust_servicer',
  'information I receive from the U.S. Department': 'trust_dept_ed',
  'What source do you trust': 'trusted_sources',
  'received conflicting or confusing information': 'conflicting_info',
  'Which sources did you receive conflicting': 'conflicting_sources',
  'What type of cancellation or forgiveness did you receive': 'forgiveness_type_received',
  'What type(s) of loans did you receive cancellation': 'forgiveness_loan_type',
  'experience with student': 'civic_action_impact',
  'Are you a voter?': 'is_voter',
  'vote in the 2024': 'voted_2024',
  'vote in the 2026': 'plan_vote_2026',
  'student debt landscape impact how you feel about the government': 'govt_trust_impact',
  'First name': 'first_name',
  'Last name': 'last_name',
  'What is your email': 'email',
  'What is your zip code': 'zip_code',
  'How old are you?': 'age',
  'marital status': 'marital_status',
  'How many people are in your household': 'household_size',
  'employment situation': 'employment',
  'income level': 'income',
  'race/ethnicity': 'race_ethnicity',
  'gender': 'gender',
  'highest level of education': 'education',
  'share your student loan story': 'story',
  'share your story with lawmakers': 'sharing_consent',
  'utm_campaign': 'utm_campaign',
  'Submitted At': 'submitted_at',
  'Token': 'token'
};

// Map a full question header to a short field name
function mapHeader(header) {
  // Exact match first
  if (QUESTION_MAP[header]) return QUESTION_MAP[header];
  // Partial match (question text is often truncated)
  const lower = header.toLowerCase();
  for (const [question, field] of Object.entries(QUESTION_MAP)) {
    if (lower.includes(question.toLowerCase())) return field;
  }
  // Already a short name (from processed CSV)
  if (/^[a-z_]+$/.test(header)) return header;
  return header;
}

// Download fresh data from Google Sheet
async function downloadFreshData() {
  const https = require('https');
  return new Promise((resolve, reject) => {
    const follow = (url, depth) => {
      if (depth > 5) return reject(new Error('Too many redirects'));
      const mod = url.startsWith('https') ? https : require('http');
      mod.get(url, (res) => {
        if (res.statusCode >= 300 && res.statusCode < 400 && res.headers.location) {
          return follow(res.headers.location, depth + 1);
        }
        let body = '';
        res.on('data', c => body += c);
        res.on('end', () => {
          if (body.includes('<!DOCTYPE html>')) return reject(new Error('Got HTML instead of CSV — sheet may require auth'));
          fs.writeFileSync(FRESH_CSV, body);
          resolve(FRESH_CSV);
        });
      }).on('error', reject);
    };
    follow(SHEET_URL, 0);
  });
}

// ── Multi-Value Column Splitters ──

// Income: "Household - 20,000-50,000" → { income_type: "Household", income_range: "20,000-50,000" }
function splitIncome(raw) {
  if (!raw) return { income_type: '', income_range: '', income_amount_low: 0, income_amount_high: 0 };
  const m = raw.match(/^(Household|Individual)\s*-\s*(.+)$/i);
  if (!m) return { income_type: '', income_range: raw, income_amount_low: 0, income_amount_high: 0 };

  const type = m[1].trim();
  const range = m[2].trim();

  let low = 0, high = 0;
  if (range.includes('Less than')) {
    low = 0; high = 20000;
  } else if (range.includes('More than')) {
    low = 100000; high = 999999;
  } else {
    const nums = range.replace(/[$,]/g, '').match(/(\d+)\s*-\s*(\d+)/);
    if (nums) { low = parseInt(nums[1]); high = parseInt(nums[2]); }
  }
  return { income_type: type, income_range: range, income_amount_low: low, income_amount_high: high };
}

// Amount owed: "$100,001 - $150,000" → { amount_low, amount_high, amount_display }
function splitAmount(raw) {
  if (!raw) return { amount_low: 0, amount_high: 0, amount_display: '' };
  const clean = raw.replace(/[$,]/g, '');

  if (raw.includes('More than')) {
    return { amount_low: 200000, amount_high: 999999, amount_display: raw };
  }
  if (raw.includes('Less than')) {
    return { amount_low: 0, amount_high: 20000, amount_display: raw };
  }
  const nums = clean.match(/(\d+)\s*-\s*(\d+)/);
  if (nums) {
    return { amount_low: parseInt(nums[1]), amount_high: parseInt(nums[2]), amount_display: raw };
  }
  return { amount_low: 0, amount_high: 0, amount_display: raw };
}

// Affordability impacts: comma-separated list → array + individual boolean columns
function splitAffordability(raw) {
  if (!raw) return { affordability_list: [], affordability_count: 0 };
  const items = raw.split(',').map(s => s.trim()).filter(Boolean);
  return {
    affordability_list: items,
    affordability_count: items.length,
    aff_food: items.some(i => /food|groceries/i.test(i)),
    aff_healthcare: items.some(i => /health/i.test(i)),
    aff_housing: items.some(i => /rent|mortgage|hous/i.test(i)),
    aff_savings: items.some(i => /saving|retirement|invest/i.test(i)),
    aff_childcare: items.some(i => /child/i.test(i)),
    aff_education: items.some(i => /tuition|school/i.test(i)),
    aff_debt: items.some(i => /credit|debt/i.test(i)),
    aff_charity: items.some(i => /charit|donat/i.test(i))
  };
}

// Loan types: comma-separated → array
function splitLoanTypes(raw) {
  if (!raw) return { loan_type_list: [], has_undergraduate: false, has_graduate: false, has_parent_plus: false, has_perkins: false, has_consolidation: false };
  const items = raw.split(',').map(s => s.trim()).filter(Boolean);
  return {
    loan_type_list: items,
    has_undergraduate: items.some(i => /undergraduate/i.test(i)),
    has_graduate: items.some(i => /graduate\s*plus|grad/i.test(i)),
    has_parent_plus: items.some(i => /parent/i.test(i)),
    has_perkins: items.some(i => /perkins/i.test(i)),
    has_consolidation: items.some(i => /consolidat/i.test(i))
  };
}

// ── Candidate Scoring ──
// Higher score = better story candidate
function scoreCandidate(record) {
  let score = 0;

  // P: Emotional impact (richest stories have detailed emotional narratives)
  const emoLen = (record.emotional_impact || '').length;
  if (emoLen > 200) score += 30;
  else if (emoLen > 100) score += 20;
  else if (emoLen > 30) score += 10;

  // Q: Affordability impacts (more impacts = more compelling)
  score += Math.min((record.affordability_count || 0) * 5, 25);

  // R: Alt spending (concrete alternatives make stories relatable)
  const altLen = (record.alt_spending || '').length;
  if (altLen > 100) score += 20;
  else if (altLen > 30) score += 10;

  // Story field present
  if ((record.story || '').length > 50) score += 15;

  // Has consent
  if (/yes/i.test(record.sharing_consent)) score += 10;
  if (/yes/i.test(record.press_ready)) score += 10;

  // Key quote exists
  if ((record.key_quote || '').length > 20) score += 10;

  // Has name + zip (needed for lawmaker mapping)
  if (record.first_name) score += 5;
  if (record.zip_code) score += 5;

  // Amount owed (higher debt = more impactful)
  if (record.amount_high > 100000) score += 10;
  else if (record.amount_high > 50000) score += 5;

  // Voter status (civic engagement adds credibility)
  if (/yes/i.test(record.is_voter)) score += 5;

  return Math.min(score, 100);
}

// ── Candidate Tier ──
function getTier(score) {
  if (score >= 70) return 'A';  // Press + social media + lawmakers
  if (score >= 45) return 'B';  // Social media + lawmakers
  if (score >= 25) return 'C';  // Social media only
  return 'D';  // Needs enrichment
}

// ── Parse CSV Manually (no external deps) ──
function parseCSV(text) {
  const rows = [];
  let current = '';
  let inQuotes = false;
  const lines = [];

  // Split into lines respecting quoted fields
  for (let i = 0; i < text.length; i++) {
    const ch = text[i];
    if (ch === '"') {
      if (inQuotes && text[i + 1] === '"') { current += '"'; i++; }
      else { inQuotes = !inQuotes; current += ch; }
    } else if ((ch === '\n' || ch === '\r') && !inQuotes) {
      if (current.length > 0) lines.push(current);
      current = '';
      if (ch === '\r' && text[i + 1] === '\n') i++;
    } else {
      current += ch;
    }
  }
  if (current.length > 0) lines.push(current);

  if (lines.length === 0) return [];

  // Parse header
  const headers = splitCSVLine(lines[0]);

  // Parse data rows
  for (let i = 1; i < lines.length; i++) {
    const vals = splitCSVLine(lines[i]);
    const row = {};
    for (let j = 0; j < headers.length; j++) {
      row[headers[j]] = (vals[j] || '').replace(/^"|"$/g, '').trim();
    }
    rows.push(row);
  }
  return rows;
}

function splitCSVLine(line) {
  const result = [];
  let current = '';
  let inQuotes = false;
  for (let i = 0; i < line.length; i++) {
    const ch = line[i];
    if (ch === '"') {
      if (inQuotes && line[i + 1] === '"') { current += '"'; i++; }
      else { inQuotes = !inQuotes; }
    } else if (ch === ',' && !inQuotes) {
      result.push(current);
      current = '';
    } else {
      current += ch;
    }
  }
  result.push(current);
  return result;
}

// ── Main Import Function ──
function importData(csvPath) {
  // Try fresh CSV first, then master CSV
  const filePath = csvPath || (fs.existsSync(FRESH_CSV) ? FRESH_CSV : MASTER_CSV);
  const text = fs.readFileSync(filePath, 'utf8');
  const rawRows = parseCSV(text);

  // Detect if headers are full questions or short names, and normalize
  if (rawRows.length > 0) {
    const firstRow = rawRows[0];
    const keys = Object.keys(firstRow);
    // If first key looks like a question, remap all rows
    if (keys[0] && keys[0].length > 30) {
      const headerMap = {};
      for (const k of keys) headerMap[k] = mapHeader(k);
      for (const row of rawRows) {
        for (const [oldKey, newKey] of Object.entries(headerMap)) {
          if (oldKey !== newKey) {
            row[newKey] = row[oldKey];
            delete row[oldKey];
          }
        }
      }
    }
  }

  const processed = rawRows.map((raw, idx) => {
    const income = splitIncome(raw.income);
    const amount = splitAmount(raw.amount_owed);
    const aff = splitAffordability(raw.affordability_impacts);
    const loans = splitLoanTypes(raw.loan_types);

    const record = {
      id: idx + 1,
      // Core demographics
      first_name: raw.first_name || '',
      last_name: raw.last_name || '',
      email: raw.email || '',
      zip_code: raw.zip_code || '',
      age: raw.age || '',
      marital_status: raw.marital_status || '',
      household_size: raw.household_size || '',
      employment: raw.employment || '',
      race_ethnicity: raw.race_ethnicity || '',
      gender: raw.gender || '',
      education: raw.education || '',

      // Income — SPLIT
      income_raw: raw.income || '',
      ...income,

      // Loan info
      has_federal: raw.has_federal || '',
      loan_types_raw: raw.loan_types || '',
      ...loans,
      first_loan_year: raw.first_loan_year || '',
      repayment_plan: raw.repayment_plan || '',
      monthly_payment: raw.monthly_payment || '',
      repayment_status: raw.repayment_status || '',

      // Amount — SPLIT
      amount_owed_raw: raw.amount_owed || '',
      ...amount,

      // P, Q, R — THE STORY ESSENTIALS
      emotional_impact: raw.emotional_impact || '',      // P
      affordability_impacts_raw: raw.affordability_impacts || '',  // Q (raw)
      ...aff,                                            // Q (split)
      alt_spending: raw.alt_spending || '',               // R

      // Story
      story: raw.story || '',
      key_quote: raw.key_quote || '',
      story_type: raw.story_type || '',
      story_category: raw.story_category || '',
      platform_fit: raw.platform_fit || '',
      story_strength: parseInt(raw.story_strength) || 0,
      press_pitch_angle: raw.press_pitch_angle || '',

      // Consent & readiness
      sharing_consent: raw.sharing_consent || '',
      press_ready: raw.press_ready || '',

      // Civic
      is_voter: raw.is_voter || '',
      voted_2024: raw.voted_2024 || '',
      plan_vote_2026: raw.plan_vote_2026 || '',
      civic_action_impact: raw.civic_action_impact || '',
      govt_trust_impact: raw.govt_trust_impact || '',

      // Trust
      trust_servicer: raw.trust_servicer || '',
      trust_dept_ed: raw.trust_dept_ed || '',
      trusted_sources: raw.trusted_sources || '',

      // Other
      aware_phaseout: raw.aware_phaseout || '',
      counting_forgiveness: raw.counting_forgiveness || '',
      forgiveness_program: raw.forgiveness_program || '',
      concern_inflation: raw.concern_inflation || '',
      default_experiences: raw.default_experiences || '',
      utm_campaign: raw.utm_campaign || '',
      submitted_at: raw.submitted_at || ''
    };

    // Score and tier
    record.candidate_score = scoreCandidate(record);
    record.tier = getTier(record.candidate_score);

    return record;
  });

  return processed;
}

module.exports = { importData, downloadFreshData, scoreCandidate, getTier, splitIncome, splitAmount, splitAffordability, splitLoanTypes };