/*
  # Add Save and Forward Posts Features

  1. New Tables
    - `saved_posts`
      - `id` (serial, primary key)
      - `user_id` (integer, references users)
      - `post_id` (integer, references posts)
      - `created_at` (timestamptz)
    - `forwarded_posts`
      - `id` (serial, primary key)
      - `user_id` (integer, references users)
      - `post_id` (integer, references posts)
      - `forwarded_to_user_id` (integer, references users)
      - `created_at` (timestamptz)

  2. Security
    - Enable RLS on both tables
    - Add policies for authenticated users
*/

-- Create saved posts table
CREATE TABLE IF NOT EXISTS saved_posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(user_id, post_id)
);

-- Create indexes for saved posts
CREATE INDEX IF NOT EXISTS saved_posts_user_id_idx ON saved_posts(user_id);
CREATE INDEX IF NOT EXISTS saved_posts_post_id_idx ON saved_posts(post_id);

-- Enable RLS on saved posts
ALTER TABLE saved_posts ENABLE ROW LEVEL SECURITY;

-- Create policies for saved posts
CREATE POLICY "Users can read their own saved posts" ON saved_posts
  FOR SELECT TO authenticated
  USING (user_id = auth.uid()::integer);

CREATE POLICY "Users can save posts" ON saved_posts
  FOR INSERT TO authenticated
  WITH CHECK (user_id = auth.uid()::integer);

CREATE POLICY "Users can unsave posts" ON saved_posts
  FOR DELETE TO authenticated
  USING (user_id = auth.uid()::integer);

-- Create forwarded posts table
CREATE TABLE IF NOT EXISTS forwarded_posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  forwarded_to_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for forwarded posts
CREATE INDEX IF NOT EXISTS forwarded_posts_user_id_idx ON forwarded_posts(user_id);
CREATE INDEX IF NOT EXISTS forwarded_posts_post_id_idx ON forwarded_posts(post_id);
CREATE INDEX IF NOT EXISTS forwarded_posts_forwarded_to_user_id_idx ON forwarded_posts(forwarded_to_user_id);

-- Enable RLS on forwarded posts
ALTER TABLE forwarded_posts ENABLE ROW LEVEL SECURITY;

-- Create policies for forwarded posts
CREATE POLICY "Users can read posts forwarded to them" ON forwarded_posts
  FOR SELECT TO authenticated
  USING (forwarded_to_user_id = auth.uid()::integer OR user_id = auth.uid()::integer);

CREATE POLICY "Users can forward posts" ON forwarded_posts
  FOR INSERT TO authenticated
  WITH CHECK (user_id = auth.uid()::integer);