/*
  # Add Reel and Story Interactions

  1. New Tables
    - `reel_likes`
      - `id` (serial, primary key)
      - `reel_id` (integer, references reels)
      - `user_id` (integer, references users)
      - `created_at` (timestamptz)
    - `story_likes`
      - `id` (serial, primary key)
      - `story_id` (integer, references stories)
      - `user_id` (integer, references users)
      - `created_at` (timestamptz)

  2. Changes
    - Add `reel_id` and `story_id` to comments table
    - Update comment constraints

  3. Security
    - Enable RLS on all new tables
    - Add policies for authenticated users
*/

-- Create reel likes table
CREATE TABLE IF NOT EXISTS reel_likes (
  id SERIAL PRIMARY KEY,
  reel_id INTEGER NOT NULL REFERENCES reels(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(reel_id, user_id)
);

-- Create story likes table
CREATE TABLE IF NOT EXISTS story_likes (
  id SERIAL PRIMARY KEY,
  story_id INTEGER NOT NULL REFERENCES stories(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(story_id, user_id)
);

-- Update comments table to support reels and stories
ALTER TABLE comments
  ADD COLUMN IF NOT EXISTS reel_id INTEGER REFERENCES reels(id) ON DELETE CASCADE,
  ADD COLUMN IF NOT EXISTS story_id INTEGER REFERENCES stories(id) ON DELETE CASCADE,
  DROP CONSTRAINT IF EXISTS comment_parent_check,
  ADD CONSTRAINT comment_parent_check CHECK (
    (post_id IS NOT NULL AND reel_id IS NULL AND story_id IS NULL) OR
    (post_id IS NULL AND reel_id IS NOT NULL AND story_id IS NULL) OR
    (post_id IS NULL AND reel_id IS NULL AND story_id IS NOT NULL)
  );

-- Create indexes
CREATE INDEX IF NOT EXISTS reel_likes_reel_id_idx ON reel_likes(reel_id);
CREATE INDEX IF NOT EXISTS reel_likes_user_id_idx ON reel_likes(user_id);
CREATE INDEX IF NOT EXISTS story_likes_story_id_idx ON story_likes(story_id);
CREATE INDEX IF NOT EXISTS story_likes_user_id_idx ON story_likes(user_id);
CREATE INDEX IF NOT EXISTS comments_reel_id_idx ON comments(reel_id);
CREATE INDEX IF NOT EXISTS comments_story_id_idx ON comments(story_id);

-- Enable RLS
ALTER TABLE reel_likes ENABLE ROW LEVEL SECURITY;
ALTER TABLE story_likes ENABLE ROW LEVEL SECURITY;

-- Policies for reel likes
CREATE POLICY "Users can read all reel likes"
  ON reel_likes FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Users can create their own reel likes"
  ON reel_likes FOR INSERT
  TO authenticated
  WITH CHECK (user_id = auth.uid()::integer);

CREATE POLICY "Users can delete their own reel likes"
  ON reel_likes FOR DELETE
  TO authenticated
  USING (user_id = auth.uid()::integer);

-- Policies for story likes
CREATE POLICY "Users can read all story likes"
  ON story_likes FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Users can create their own story likes"
  ON story_likes FOR INSERT
  TO authenticated
  WITH CHECK (user_id = auth.uid()::integer);

CREATE POLICY "Users can delete their own story likes"
  ON story_likes FOR DELETE
  TO authenticated
  USING (user_id = auth.uid()::integer);

-- Update comment policies for reels and stories
DROP POLICY IF EXISTS "Users can read all comments" ON comments;
CREATE POLICY "Users can read all comments"
  ON comments FOR SELECT
  TO authenticated
  USING (
    CASE
      WHEN post_id IS NOT NULL THEN true
      WHEN reel_id IS NOT NULL THEN true
      WHEN story_id IS NOT NULL THEN EXISTS (
        SELECT 1 FROM stories s
        WHERE s.id = story_id
        AND s.expires_at > CURRENT_TIMESTAMP
      )
      ELSE false
    END
  );