-- ============================================================
-- GrabThoseLeads (Grab Those Leads) — Supabase Database Schema
-- ============================================================
-- Run this in the Supabase SQL Editor to create all tables.
--
-- Table creation order respects foreign-key dependencies:
--   profiles → searches → leads → lead_lists → lead_list_items
-- ============================================================


-- ============================================================
-- 0. SHARED UTILITY: auto-update updated_at column
-- ============================================================
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;


-- ============================================================
-- 1. PROFILES (extends Supabase Auth users)
-- ============================================================
CREATE TABLE profiles (
  id UUID REFERENCES auth.users ON DELETE CASCADE PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  first_name TEXT,
  last_name TEXT,
  company TEXT,
  avatar_url TEXT,
  timezone TEXT DEFAULT 'UTC',
  language TEXT DEFAULT 'en',
  role TEXT DEFAULT 'user' CHECK (role IN ('user', 'admin')),
  credits INTEGER DEFAULT 0 CHECK (credits >= 0),
  -- Plan / subscription tracking
  plan TEXT DEFAULT 'starter' CHECK (plan IN ('starter', 'growth', 'enterprise')),
  stripe_customer_id TEXT,
  -- Two-Factor Authentication
  mfa_enabled BOOLEAN DEFAULT FALSE,
  mfa_secret TEXT DEFAULT NULL,
  mfa_pending_secret TEXT DEFAULT NULL,
  -- Notification preferences (JSONB for flexibility)
  notification_preferences JSONB DEFAULT '{
    "search_completed": true,
    "credit_alerts": true,
    "weekly_digest": false,
    "product_updates": true
  }'::jsonb,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own profile"
  ON profiles FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Users can update their own profile"
  ON profiles FOR UPDATE USING (auth.uid() = id);
CREATE POLICY "Users can insert their own profile"
  ON profiles FOR INSERT WITH CHECK (auth.uid() = id);

CREATE INDEX idx_profiles_email ON profiles (email);
CREATE INDEX idx_profiles_stripe_customer ON profiles (stripe_customer_id) WHERE stripe_customer_id IS NOT NULL;

CREATE TRIGGER profiles_updated_at
  BEFORE UPDATE ON profiles
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();


-- ============================================================
-- 2. SEARCHES (must come before leads — leads.search_id FK)
-- ============================================================
CREATE TABLE searches (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES auth.users ON DELETE CASCADE NOT NULL,
  query TEXT NOT NULL,
  filters JSONB,
  results_count INTEGER DEFAULT 0,
  credits_used INTEGER DEFAULT 0,
  status TEXT DEFAULT 'pending'
    CHECK (status IN ('pending', 'searching_db', 'searching_apollo', 'verifying', 'completed', 'failed')),
  error_message TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

ALTER TABLE searches ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own searches"
  ON searches FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert their own searches"
  ON searches FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own searches"
  ON searches FOR UPDATE USING (auth.uid() = user_id);

CREATE INDEX idx_searches_user_id ON searches (user_id);
CREATE INDEX idx_searches_created_at ON searches (user_id, created_at DESC);
CREATE INDEX idx_searches_status ON searches (status);


-- ============================================================
-- 3. LEADS
-- ============================================================
--   • `status`       → pipeline status (new / contacted / qualified / lost)
--   • `email_status` → verification result from ZeroBounce
--   • `verified`     → convenience boolean (TRUE when email_status = 'verified')
-- ============================================================
CREATE TABLE leads (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES auth.users ON DELETE CASCADE NOT NULL,

  first_name TEXT,
  last_name TEXT,
  full_name TEXT,
  email TEXT,
  phone TEXT,
  title TEXT,
  company_name TEXT,
  linkedin_url TEXT,
  location TEXT,

  -- Pipeline status
  status TEXT DEFAULT 'new' CHECK (status IN ('new', 'contacted', 'qualified', 'lost')),

  -- Email verification
  email_status TEXT DEFAULT 'pending'
    CHECK (email_status IN ('pending', 'verified', 'invalid', 'catch-all', 'unknown', 'spamtrap', 'disposable')),
  verified BOOLEAN DEFAULT FALSE,

  -- Origin
  source TEXT DEFAULT 'apollo' CHECK (source IN ('apollo', 'ai_search', 'manual', 'csv_import')),

  -- Raw data from Apollo / other sources
  metadata JSONB,

  -- Link back to the search that produced this lead
  search_id UUID REFERENCES searches (id) ON DELETE SET NULL,

  -- Caching and Freshness
  last_verified_at TIMESTAMP WITH TIME ZONE,
  is_cached BOOLEAN DEFAULT FALSE,

  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Ensure a user doesn't have duplicate leads by email
CREATE UNIQUE INDEX idx_leads_user_email ON leads (user_id, email);

ALTER TABLE leads ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own leads"
  ON leads FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert their own leads"
  ON leads FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own leads"
  ON leads FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own leads"
  ON leads FOR DELETE USING (auth.uid() = user_id);

CREATE INDEX idx_leads_user_id ON leads (user_id);
CREATE INDEX idx_leads_email ON leads (user_id, email);
CREATE INDEX idx_leads_status ON leads (user_id, status);
CREATE INDEX idx_leads_email_status ON leads (user_id, email_status);
CREATE INDEX idx_leads_source ON leads (user_id, source);
CREATE INDEX idx_leads_company ON leads (user_id, company_name);
CREATE INDEX idx_leads_created_at ON leads (user_id, created_at DESC);
CREATE INDEX idx_leads_search_id ON leads (search_id) WHERE search_id IS NOT NULL;

CREATE TRIGGER leads_updated_at
  BEFORE UPDATE ON leads
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();


-- ============================================================
-- 4. CREDIT TRANSACTIONS
-- ============================================================
CREATE TABLE credit_transactions (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES auth.users ON DELETE CASCADE NOT NULL,
  amount INTEGER NOT NULL,  -- positive = added, negative = spent
  type TEXT NOT NULL CHECK (type IN (
    'signup_bonus', 'usage', 'purchase', 'refund', 'admin_adjustment', 'plan_reset'
  )),
  description TEXT,
  reference_type TEXT CHECK (reference_type IN ('search', 'lead', 'subscription', 'manual')),
  reference_id UUID,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

ALTER TABLE credit_transactions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own transactions"
  ON credit_transactions FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert their own transactions"
  ON credit_transactions FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE INDEX idx_credit_tx_user_id ON credit_transactions (user_id);
CREATE INDEX idx_credit_tx_created_at ON credit_transactions (user_id, created_at DESC);
CREATE INDEX idx_credit_tx_type ON credit_transactions (user_id, type);


-- ============================================================
-- 5. SUBSCRIPTIONS (Stripe integration)
-- ============================================================
CREATE TABLE subscriptions (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES auth.users ON DELETE CASCADE NOT NULL,
  stripe_customer_id TEXT,
  stripe_subscription_id TEXT UNIQUE,
  stripe_price_id TEXT,
  plan TEXT NOT NULL CHECK (plan IN ('starter', 'growth', 'enterprise')),
  status TEXT NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'past_due', 'cancelled', 'incomplete', 'trialing', 'paused')),
  current_period_start TIMESTAMP WITH TIME ZONE,
  current_period_end TIMESTAMP WITH TIME ZONE,
  cancel_at_period_end BOOLEAN DEFAULT FALSE,
  credits_allocated INTEGER NOT NULL DEFAULT 0,
  credits_used INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own subscriptions"
  ON subscriptions FOR SELECT USING (auth.uid() = user_id);

CREATE INDEX idx_subscriptions_user_id ON subscriptions (user_id);
CREATE INDEX idx_subscriptions_stripe_customer ON subscriptions (stripe_customer_id) WHERE stripe_customer_id IS NOT NULL;
CREATE INDEX idx_subscriptions_stripe_sub ON subscriptions (stripe_subscription_id) WHERE stripe_subscription_id IS NOT NULL;
CREATE INDEX idx_subscriptions_status ON subscriptions (status);

CREATE TRIGGER subscriptions_updated_at
  BEFORE UPDATE ON subscriptions
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();


-- ============================================================
-- 6. INVOICES
-- ============================================================
CREATE TABLE invoices (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES auth.users ON DELETE CASCADE NOT NULL,
  stripe_invoice_id TEXT UNIQUE,
  invoice_number TEXT,
  amount_cents INTEGER NOT NULL DEFAULT 0,
  currency TEXT DEFAULT 'usd',
  status TEXT NOT NULL DEFAULT 'draft'
    CHECK (status IN ('draft', 'open', 'paid', 'void', 'uncollectible')),
  plan TEXT,
  description TEXT,
  invoice_date TIMESTAMP WITH TIME ZONE,
  due_date TIMESTAMP WITH TIME ZONE,
  paid_at TIMESTAMP WITH TIME ZONE,
  stripe_hosted_url TEXT,
  stripe_pdf_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own invoices"
  ON invoices FOR SELECT USING (auth.uid() = user_id);

CREATE INDEX idx_invoices_user_id ON invoices (user_id);
CREATE INDEX idx_invoices_stripe_id ON invoices (stripe_invoice_id) WHERE stripe_invoice_id IS NOT NULL;
CREATE INDEX idx_invoices_date ON invoices (user_id, invoice_date DESC);


-- ============================================================
-- 7. API KEYS
-- ============================================================
CREATE TABLE api_keys (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES auth.users ON DELETE CASCADE NOT NULL,
  name TEXT DEFAULT 'Default',
  key_hash TEXT NOT NULL UNIQUE,   -- never store the raw key
  key_prefix TEXT NOT NULL,        -- e.g. lf_live_••••4x7k
  last_used_at TIMESTAMP WITH TIME ZONE,
  expires_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

ALTER TABLE api_keys ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own API keys"
  ON api_keys FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert their own API keys"
  ON api_keys FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can delete their own API keys"
  ON api_keys FOR DELETE USING (auth.uid() = user_id);

CREATE INDEX idx_api_keys_user_id ON api_keys (user_id);
CREATE INDEX idx_api_keys_hash ON api_keys (key_hash);


-- ============================================================
-- 8. LEAD LISTS  +  lead_list_items (many-to-many)
-- ============================================================
CREATE TABLE lead_lists (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES auth.users ON DELETE CASCADE NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  color TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

ALTER TABLE lead_lists ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can manage their own lists"
  ON lead_lists FOR ALL USING (auth.uid() = user_id);

CREATE INDEX idx_lead_lists_user_id ON lead_lists (user_id);

CREATE TABLE lead_list_items (
  lead_id UUID REFERENCES leads ON DELETE CASCADE NOT NULL,
  list_id UUID REFERENCES lead_lists ON DELETE CASCADE NOT NULL,
  added_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  PRIMARY KEY (lead_id, list_id)
);

ALTER TABLE lead_list_items ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can manage their own list items"
  ON lead_list_items FOR ALL USING (
    EXISTS (SELECT 1 FROM leads WHERE leads.id = lead_list_items.lead_id AND leads.user_id = auth.uid())
  );


-- ============================================================
-- 9. SAVED FILTERS
-- ============================================================
CREATE TABLE saved_filters (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES auth.users ON DELETE CASCADE NOT NULL,
  name TEXT NOT NULL,
  filters JSONB NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

ALTER TABLE saved_filters ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can manage their own saved filters"
  ON saved_filters FOR ALL USING (auth.uid() = user_id);

CREATE INDEX idx_saved_filters_user_id ON saved_filters (user_id);


-- ============================================================
-- TRIGGER: auto-create profile + signup bonus on new user
-- ============================================================
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, email, first_name, last_name, credits, role)
  VALUES (
    new.id,
    new.email,
    COALESCE(
      new.raw_user_meta_data->>'first_name',
      split_part(new.raw_user_meta_data->>'full_name', ' ', 1)
    ),
    COALESCE(
      new.raw_user_meta_data->>'last_name',
      CASE
        WHEN array_length(string_to_array(new.raw_user_meta_data->>'full_name', ' '), 1) > 1
        THEN array_to_string((string_to_array(new.raw_user_meta_data->>'full_name', ' '))[2:], ' ')
        ELSE NULL
      END
    ),
    0, -- Users start with 0 credits
    COALESCE(new.raw_user_meta_data->>'role', 'user')
  );

  RETURN new;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();


-- ============================================================
-- HELPER: deduct credits atomically
-- Returns TRUE on success, FALSE if insufficient balance
-- ============================================================
CREATE OR REPLACE FUNCTION deduct_credits(
  p_user_id UUID,
  p_amount INTEGER,
  p_type TEXT,
  p_description TEXT DEFAULT NULL,
  p_reference_type TEXT DEFAULT NULL,
  p_reference_id UUID DEFAULT NULL
)
RETURNS BOOLEAN AS $$
DECLARE
  new_balance INTEGER;
BEGIN
  UPDATE profiles
  SET credits = credits - p_amount
  WHERE id = p_user_id AND credits >= p_amount
  RETURNING credits INTO new_balance;

  IF NOT FOUND THEN
    RETURN FALSE;
  END IF;

  INSERT INTO credit_transactions (user_id, amount, type, description, reference_type, reference_id)
  VALUES (p_user_id, -p_amount, p_type, p_description, p_reference_type, p_reference_id);

  RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;


-- ============================================================
-- HELPER: add credits (purchase / refund / admin)
-- ============================================================
CREATE OR REPLACE FUNCTION add_credits(
  p_user_id UUID,
  p_amount INTEGER,
  p_type TEXT,
  p_description TEXT DEFAULT NULL,
  p_reference_type TEXT DEFAULT NULL,
  p_reference_id UUID DEFAULT NULL
)
RETURNS VOID AS $$
BEGIN
  UPDATE profiles SET credits = credits + p_amount WHERE id = p_user_id;

  INSERT INTO credit_transactions (user_id, amount, type, description, reference_type, reference_id)
  VALUES (p_user_id, p_amount, p_type, p_description, p_reference_type, p_reference_id);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;


-- ============================================================
-- GRANTS
-- ============================================================
-- Grant USAGE to both, but restrict data access to authenticated users
GRANT USAGE ON SCHEMA public TO anon, authenticated;

-- Only authenticated users should interact with tables/sequences
GRANT ALL ON ALL TABLES IN SCHEMA public TO authenticated;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO authenticated;

-- Anon should have no access by default (security best practice)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO authenticated;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO authenticated;