-- 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);

-- Enrichment config table (API keys, limits, etc.)
CREATE TABLE IF NOT EXISTS enrichment_config (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  source TEXT NOT NULL UNIQUE,
  api_key_encrypted TEXT, -- Encrypted API key
  is_enabled BOOLEAN DEFAULT false,
  monthly_limit INTEGER DEFAULT 1000,
  credits_used_this_month INTEGER DEFAULT 0,
  auto_enrich BOOLEAN DEFAULT false, -- Auto-enrich new leads
  enrich_on_events TEXT[] DEFAULT '{}', -- Events that trigger enrichment
  cache_ttl_days INTEGER DEFAULT 90, -- How long to cache enrichments
  settings JSONB DEFAULT '{}', -- Source-specific settings
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Seed default config for Apollo
INSERT INTO enrichment_config (source, is_enabled, monthly_limit, cache_ttl_days, settings) VALUES
  ('apollo', false, 1000, 90, '{"endpoint": "https://api.apollo.io/v1", "enrichFields": ["person", "company"]}')
ON CONFLICT (source) DO NOTHING;

-- Function to reset monthly credits on first of month
CREATE OR REPLACE FUNCTION reset_enrichment_credits()
RETURNS void AS $$
BEGIN
  UPDATE enrichment_config
  SET credits_used_this_month = 0, updated_at = NOW()
  WHERE date_trunc('month', updated_at) < date_trunc('month', NOW());
END;
$$ LANGUAGE plpgsql;

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

-- Allow read access to enrichment data for authenticated users
CREATE POLICY "read_enrichment_cache" ON enrichment_cache
  FOR SELECT USING (auth.role() = 'authenticated');

CREATE POLICY "read_enrichment_usage" ON enrichment_usage
  FOR SELECT USING (auth.role() = 'authenticated');

-- Only admins can modify config
CREATE POLICY "read_enrichment_config" ON enrichment_config
  FOR SELECT USING (auth.role() = 'authenticated');
