`cypressaward/scraper/setup-database.js`

147 lines
require('dotenv').config();
const { createClient } = require('@supabase/supabase-js');

const supabaseUrl = process.env.SUPABASE_URL;
const supabaseKey = process.env.SUPABASE_ANON_KEY;
const supabase = createClient(supabaseUrl, supabaseKey);

async function createTables() {
  console.log('Creating Supabase tables...');
  
  const tables = [
    {
      name: 'scraped_cases',
      sql: `
        CREATE TABLE IF NOT EXISTS scraped_cases (
          id SERIAL PRIMARY KEY,
          hash VARCHAR(32) UNIQUE NOT NULL,
          title TEXT,
          court TEXT,
          date_filed TIMESTAMP,
          docket_number TEXT,
          summary TEXT,
          url TEXT,
          source TEXT,
          data_type VARCHAR(50),
          created_at TIMESTAMP DEFAULT NOW(),
          updated_at TIMESTAMP DEFAULT NOW()
        )
      `
    },
    {
      name: 'scraped_awards',
      sql: `
        CREATE TABLE IF NOT EXISTS scraped_awards (
          id SERIAL PRIMARY KEY,
          hash VARCHAR(32) UNIQUE NOT NULL,
          title TEXT,
          recipient TEXT,
          organization TEXT,
          date_awarded TIMESTAMP,
          category TEXT,
          description TEXT,
          url TEXT,
          source TEXT,
          data_type VARCHAR(50),
          created_at TIMESTAMP DEFAULT NOW(),
          updated_at TIMESTAMP DEFAULT NOW()
        )
      `
    },
    {
      name: 'scraped_law_firms',
      sql: `
        CREATE TABLE IF NOT EXISTS scraped_law_firms (
          id SERIAL PRIMARY KEY,
          hash VARCHAR(32) UNIQUE NOT NULL,
          name TEXT,
          ranking TEXT,
          location TEXT,
          size TEXT,
          practice_areas TEXT,
          revenue TEXT,
          website TEXT,
          source TEXT,
          data_type VARCHAR(50),
          created_at TIMESTAMP DEFAULT NOW(),
          updated_at TIMESTAMP DEFAULT NOW()
        )
      `
    },
    {
      name: 'scraped_nonprofits',
      sql: `
        CREATE TABLE IF NOT EXISTS scraped_nonprofits (
          id SERIAL PRIMARY KEY,
          hash VARCHAR(32) UNIQUE NOT NULL,
          name TEXT,
          category TEXT,
          location TEXT,
          mission TEXT,
          rating TEXT,
          website TEXT,
          source TEXT,
          data_type VARCHAR(50),
          created_at TIMESTAMP DEFAULT NOW(),
          updated_at TIMESTAMP DEFAULT NOW()
        )
      `
    },
    {
      name: 'scraped_news',
      sql: `
        CREATE TABLE IF NOT EXISTS scraped_news (
          id SERIAL PRIMARY KEY,
          hash VARCHAR(32) UNIQUE NOT NULL,
          title TEXT,
          description TEXT,
          url TEXT,
          published_date TIMESTAMP,
          author TEXT,
          category TEXT,
          source TEXT,
          data_type VARCHAR(50),
          created_at TIMESTAMP DEFAULT NOW(),
          updated_at TIMESTAMP DEFAULT NOW()
        )
      `
    },
    {
      name: 'scraping_logs',
      sql: `
        CREATE TABLE IF NOT EXISTS scraping_logs (
          id SERIAL PRIMARY KEY,
          timestamp TIMESTAMP DEFAULT NOW(),
          results JSONB,
          error TEXT,
          status VARCHAR(50)
        )
      `
    }
  ];

  // Test connection first
  const { data: testData, error: testError } = await supabase
    .from('scraping_logs')
    .select('count')
    .limit(1);
    
  if (testError && testError.code === '42P01') {
    console.log('Tables do not exist yet. Please create them manually in Supabase SQL Editor.');
    console.log('\nGo to: https://supabase.com/dashboard/project/jeihhunsjixzartumtbn/sql/new');
    console.log('And run the SQL from: /root/cypressaward/scraper/create-tables.sql');
    return false;
  }
  
  console.log('Tables are ready or already exist!');
  return true;
}

createTables().then(success => {
  if (success) {
    console.log('Database setup complete!');
  } else {
    console.log('\nPlease create the tables manually in Supabase.');
  }
  process.exit(0);
});