/*
  # Create Follow System

  1. New Tables
    - `user_follows`
      - `id` (serial, primary key)
      - `follower_id` (integer, references users)
      - `following_id` (integer, references users)
      - `created_at` (timestamptz)

  2. Security
    - Enable RLS on `user_follows` table
    - Add policies for authenticated users to:
      - Read follow relationships
      - Create/delete their own follow relationships
*/

-- Create user follows table
CREATE TABLE IF NOT EXISTS user_follows (
  id SERIAL PRIMARY KEY,
  follower_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  following_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(follower_id, following_id)
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS user_follows_follower_id_idx ON user_follows(follower_id);
CREATE INDEX IF NOT EXISTS user_follows_following_id_idx ON user_follows(following_id);
CREATE INDEX IF NOT EXISTS user_follows_created_at_idx ON user_follows(created_at);

-- Enable Row Level Security
ALTER TABLE user_follows ENABLE ROW LEVEL SECURITY;

-- Create policies
CREATE POLICY "Users can read all follow relationships"
  ON user_follows FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Users can create their own follow relationships"
  ON user_follows FOR INSERT
  TO authenticated
  WITH CHECK (follower_id = auth.uid()::integer);

CREATE POLICY "Users can delete their own follow relationships"
  ON user_follows FOR DELETE
  TO authenticated
  USING (follower_id = auth.uid()::integer);