thrilltrack-explorer/supabase/migrations/20251111154758_572c208e-a9fd-4c78-b160-1e71e0f7f703.sql

-- Function to backfill missing company data from submission data
CREATE OR REPLACE FUNCTION backfill_company_data()
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_companies_updated INTEGER := 0;
  v_headquarters_added INTEGER := 0;
  v_website_added INTEGER := 0;
  v_founded_year_added INTEGER := 0;
  v_description_added INTEGER := 0;
  v_logo_added INTEGER := 0;
  v_company RECORD;
  v_submission RECORD;
BEGIN
  -- Find companies with missing data that have approved submissions
  FOR v_company IN 
    SELECT DISTINCT c.id, c.name, c.slug, 
           c.headquarters_location, c.website_url, c.founded_year,
           c.description, c.logo_url
    FROM companies c
    WHERE c.headquarters_location IS NULL 
       OR c.website_url IS NULL 
       OR c.founded_year IS NULL
       OR c.description IS NULL
       OR c.logo_url IS NULL
  LOOP
    -- Find the most recent approved submission for this company with the missing data
    SELECT 
      cs.headquarters_location,
      cs.website_url,
      cs.founded_year,
      cs.description,
      cs.logo_url
    INTO v_submission
    FROM company_submissions cs
    JOIN content_submissions sub ON sub.id = cs.submission_id
    WHERE cs.company_id = v_company.id
      AND sub.status = 'approved'
      AND (
        (v_company.headquarters_location IS NULL AND cs.headquarters_location IS NOT NULL) OR
        (v_company.website_url IS NULL AND cs.website_url IS NOT NULL) OR
        (v_company.founded_year IS NULL AND cs.founded_year IS NOT NULL) OR
        (v_company.description IS NULL AND cs.description IS NOT NULL) OR
        (v_company.logo_url IS NULL AND cs.logo_url IS NOT NULL)
      )
    ORDER BY sub.created_at DESC
    LIMIT 1;

    -- If we found submission data, update the company
    IF FOUND THEN
      DECLARE
        v_updated BOOLEAN := FALSE;
      BEGIN
        -- Update headquarters_location if missing
        IF v_company.headquarters_location IS NULL AND v_submission.headquarters_location IS NOT NULL THEN
          UPDATE companies
          SET headquarters_location = v_submission.headquarters_location
          WHERE id = v_company.id;
          v_headquarters_added := v_headquarters_added + 1;
          v_updated := TRUE;
          RAISE NOTICE 'Added headquarters for company: % (id: %)', v_company.name, v_company.id;
        END IF;

        -- Update website_url if missing
        IF v_company.website_url IS NULL AND v_submission.website_url IS NOT NULL THEN
          UPDATE companies
          SET website_url = v_submission.website_url
          WHERE id = v_company.id;
          v_website_added := v_website_added + 1;
          v_updated := TRUE;
          RAISE NOTICE 'Added website for company: % (id: %)', v_company.name, v_company.id;
        END IF;

        -- Update founded_year if missing
        IF v_company.founded_year IS NULL AND v_submission.founded_year IS NOT NULL THEN
          UPDATE companies
          SET founded_year = v_submission.founded_year
          WHERE id = v_company.id;
          v_founded_year_added := v_founded_year_added + 1;
          v_updated := TRUE;
          RAISE NOTICE 'Added founded year for company: % (id: %)', v_company.name, v_company.id;
        END IF;

        -- Update description if missing
        IF v_company.description IS NULL AND v_submission.description IS NOT NULL THEN
          UPDATE companies
          SET description = v_submission.description
          WHERE id = v_company.id;
          v_description_added := v_description_added + 1;
          v_updated := TRUE;
          RAISE NOTICE 'Added description for company: % (id: %)', v_company.name, v_company.id;
        END IF;

        -- Update logo_url if missing
        IF v_company.logo_url IS NULL AND v_submission.logo_url IS NOT NULL THEN
          UPDATE companies
          SET logo_url = v_submission.logo_url
          WHERE id = v_company.id;
          v_logo_added := v_logo_added + 1;
          v_updated := TRUE;
          RAISE NOTICE 'Added logo for company: % (id: %)', v_company.name, v_company.id;
        END IF;

        IF v_updated THEN
          v_companies_updated := v_companies_updated + 1;
        END IF;
      END;
    END IF;
  END LOOP;

  RETURN jsonb_build_object(
    'success', true,
    'companies_updated', v_companies_updated,
    'headquarters_added', v_headquarters_added,
    'website_added', v_website_added,
    'founded_year_added', v_founded_year_added,
    'description_added', v_description_added,
    'logo_added', v_logo_added
  );
END;
$$;