-- RateRight Growth Engine - Intel Schema
-- Company and Person research caching
-- Run this in Supabase SQL Editor

-- ============================================
-- COMPANY INTEL TABLE
-- ============================================

CREATE TABLE IF NOT EXISTS company_intel (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_name TEXT NOT NULL,
  company_name_normalized TEXT,
  summary TEXT,
  size VARCHAR(50),
  industry VARCHAR(100),
  location TEXT,
  recent_projects JSONB,
  hiring_signals JSONB,
  ai_sales_angle TEXT,
  searched_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================
-- LEAD INTEL TABLE
-- ============================================

CREATE TABLE IF NOT EXISTS lead_intel (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  linkedin_url TEXT,
  job_title TEXT,
  years_at_company INTEGER,
  previous_companies JSONB,
  summary TEXT,
  ai_talking_points TEXT,
  searched_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================
-- INDEXES
-- ============================================

CREATE INDEX IF NOT EXISTS idx_company_intel_name ON company_intel(company_name_normalized);
CREATE INDEX IF NOT EXISTS idx_lead_intel_lead ON lead_intel(lead_id);
