-- Apollo Enrichment System
-- Enrich leads with decision-maker data from Apollo.io

-- Enrichment cache table
CREATE TABLE IF NOT EXISTS enrichment_cache (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  source TEXT NOT NULL, -- 'apollo', 'clearbit', etc.
  enriched_data JSONB NOT NULL,
  confidence_score NUMERIC(3,2), -- 0.00-1.00
  credits_used INTEGER DEFAULT 1,
  enriched_at TIMESTAMPTZ DEFAULT NOW(),
  expires_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Unique constraint to prevent duplicate enrichments per source
CREATE UNIQUE INDEX IF NOT EXISTS idx_enrichment_cache_lead_source
  ON enrichment_cache(lead_id, source);

-- Index for finding stale enrichments
CREATE INDEX IF NOT EXISTS idx_enrichment_cache_expires
  ON enrichment_cache(expires_at) WHERE expires_at IS NOT NULL;

-- Enrichment usage tracking (for credit management)
CREATE TABLE IF NOT EXISTS enrichment_usage (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID,
  source TEXT NOT NULL,
  credits_used INTEGER NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for usage queries
CREATE INDEX IF NOT EXISTS idx_enrichment_usage_period
  ON enrichment_usage(source, period_start, period_end);

-- RLS policies
ALTER TABLE enrichment_cache ENABLE ROW LEVEL SECURITY;
ALTER TABLE enrichment_usage ENABLE ROW LEVEL SECURITY;
