38980-vm/app-9w9pd00g5j41/supabase/migrations/00009_create_provider_system.sql

-- Extend profiles table with provider role
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS role TEXT DEFAULT 'user' CHECK (role IN ('user', 'admin', 'provider'));

-- Create provider_services table (destinations and activity categories they serve)
CREATE TABLE IF NOT EXISTS provider_services (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  provider_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  
  -- Service areas
  destinations TEXT[] DEFAULT '{}', -- Array of destination names
  activity_categories TEXT[] DEFAULT '{}', -- Array of activity types they provide
  
  -- Business info
  business_name TEXT,
  business_description TEXT,
  business_logo TEXT,
  
  -- Metadata
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  
  UNIQUE(provider_id)
);

-- Create provider_wallets table
CREATE TABLE IF NOT EXISTS provider_wallets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  provider_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  
  -- Balance
  credit_balance INTEGER DEFAULT 0 CHECK (credit_balance >= 0),
  
  -- Metadata
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  
  UNIQUE(provider_id)
);

-- Create credit_transactions table
CREATE TABLE IF NOT EXISTS credit_transactions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  provider_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  
  -- Transaction details
  amount INTEGER NOT NULL, -- Positive for credit purchase, negative for lead purchase
  transaction_type TEXT NOT NULL CHECK (transaction_type IN ('credit_purchase', 'lead_purchase', 'refund')),
  description TEXT,
  
  -- Reference
  reference_id UUID, -- lead_purchase_id if type is lead_purchase
  
  -- Balance after transaction
  balance_after INTEGER NOT NULL,
  
  -- Metadata
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create lead_purchases table
CREATE TABLE IF NOT EXISTS lead_purchases (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  provider_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE NOT NULL,
  
  -- Purchase details
  credits_spent INTEGER NOT NULL,
  purchased_at TIMESTAMPTZ DEFAULT NOW(),
  
  -- Prevent duplicate purchases
  UNIQUE(provider_id, lead_id)
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_provider_services_provider_id ON provider_services(provider_id);
CREATE INDEX IF NOT EXISTS idx_provider_wallets_provider_id ON provider_wallets(provider_id);
CREATE INDEX IF NOT EXISTS idx_credit_transactions_provider_id ON credit_transactions(provider_id DESC);
CREATE INDEX IF NOT EXISTS idx_lead_purchases_provider_id ON lead_purchases(provider_id);
CREATE INDEX IF NOT EXISTS idx_lead_purchases_lead_id ON lead_purchases(lead_id);

-- Enable RLS
ALTER TABLE provider_services ENABLE ROW LEVEL SECURITY;
ALTER TABLE provider_wallets ENABLE ROW LEVEL SECURITY;
ALTER TABLE credit_transactions ENABLE ROW LEVEL SECURITY;
ALTER TABLE lead_purchases ENABLE ROW LEVEL SECURITY;

-- RLS Policies for provider_services
CREATE POLICY "Providers can view own services"
  ON provider_services FOR SELECT
  USING (auth.uid() = provider_id);

CREATE POLICY "Providers can update own services"
  ON provider_services FOR UPDATE
  USING (auth.uid() = provider_id);

CREATE POLICY "Providers can insert own services"
  ON provider_services FOR INSERT
  WITH CHECK (auth.uid() = provider_id);

CREATE POLICY "Admins can view all provider services"
  ON provider_services FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

-- RLS Policies for provider_wallets
CREATE POLICY "Providers can view own wallet"
  ON provider_wallets FOR SELECT
  USING (auth.uid() = provider_id);

CREATE POLICY "Admins can view all wallets"
  ON provider_wallets FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

-- RLS Policies for credit_transactions
CREATE POLICY "Providers can view own transactions"
  ON credit_transactions FOR SELECT
  USING (auth.uid() = provider_id);

CREATE POLICY "Admins can view all transactions"
  ON credit_transactions FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

-- RLS Policies for lead_purchases
CREATE POLICY "Providers can view own purchases"
  ON lead_purchases FOR SELECT
  USING (auth.uid() = provider_id);

CREATE POLICY "Providers can insert own purchases"
  ON lead_purchases FOR INSERT
  WITH CHECK (auth.uid() = provider_id);

CREATE POLICY "Admins can view all purchases"
  ON lead_purchases FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

-- Add updated_at triggers
CREATE OR REPLACE FUNCTION update_provider_services_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER provider_services_updated_at
  BEFORE UPDATE ON provider_services
  FOR EACH ROW
  EXECUTE FUNCTION update_provider_services_updated_at();

CREATE OR REPLACE FUNCTION update_provider_wallets_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER provider_wallets_updated_at
  BEFORE UPDATE ON provider_wallets
  FOR EACH ROW
  EXECUTE FUNCTION update_provider_wallets_updated_at();