create-invitations-minimal.sql

-- Create organization_invitations table with minimal dependencies
-- This works with standard Supabase auth setup

-- First, let's create a simple version that references auth.users
-- If organizations table doesn't exist, we'll comment that part out

CREATE TABLE IF NOT EXISTS organization_invitations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL, -- We'll add the FK constraint later if organizations exists
  email VARCHAR(255) NOT NULL,
  role VARCHAR(50) NOT NULL CHECK (role IN ('admin', 'member')),
  token VARCHAR(255) UNIQUE NOT NULL DEFAULT encode(gen_random_bytes(32), 'hex'),
  message TEXT,
  status VARCHAR(50) DEFAULT 'pending' CHECK (status IN ('pending', 'accepted', 'expired', 'cancelled')),
  invited_by UUID NOT NULL, -- We'll add FK constraint later if needed
  accepted_by UUID,
  expires_at TIMESTAMP WITH TIME ZONE DEFAULT (NOW() + INTERVAL '7 days'),
  accepted_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_organization_invitations_token ON organization_invitations(token);
CREATE INDEX IF NOT EXISTS idx_organization_invitations_email ON organization_invitations(email);
CREATE INDEX IF NOT EXISTS idx_organization_invitations_organization_id ON organization_invitations(organization_id);
CREATE INDEX IF NOT EXISTS idx_organization_invitations_status ON organization_invitations(status);
CREATE INDEX IF NOT EXISTS idx_organization_invitations_expires_at ON organization_invitations(expires_at);

-- Enable RLS
ALTER TABLE organization_invitations ENABLE ROW LEVEL SECURITY;

-- Basic RLS policy that allows access if user is authenticated
CREATE POLICY "organization_invitations_authenticated" ON organization_invitations
  FOR ALL USING (auth.uid() IS NOT NULL);

-- Add comments
COMMENT ON TABLE organization_invitations IS 'Pending invitations to join organizations';

-- Test the table creation
SELECT 'organization_invitations table created successfully' as result;