/*
  # Add support for multiple media in stories

  1. New Tables
    - `story_media`
      - `id` (serial, primary key)
      - `story_id` (integer, references stories)
      - `media_url` (text)
      - `media_type` (text)
      - `thumbnail_url` (text)
      - `duration` (integer)
      - `order` (integer)
      - `created_at` (timestamptz)

  2. Changes
    - Move media fields from stories table to story_media table
    - Add order field to maintain media sequence

  3. Security
    - Enable RLS on story_media table
    - Add policies for CRUD operations
*/

-- Create story media table
CREATE TABLE IF NOT EXISTS story_media (
  id SERIAL PRIMARY KEY,
  story_id INTEGER NOT NULL REFERENCES stories(id) ON DELETE CASCADE,
  media_url TEXT NOT NULL,
  media_type TEXT NOT NULL CHECK (media_type IN ('image', 'video')),
  thumbnail_url TEXT,
  duration INTEGER,
  "order" INTEGER NOT NULL,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes
CREATE INDEX IF NOT EXISTS story_media_story_id_idx ON story_media(story_id);
CREATE INDEX IF NOT EXISTS story_media_order_idx ON story_media("order");

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

-- Create policies
CREATE POLICY "Users can view all story media"
  ON story_media FOR SELECT
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM stories s
      WHERE s.id = story_id
      AND s.expires_at > CURRENT_TIMESTAMP
    )
  );

CREATE POLICY "Users can create media for their own stories"
  ON story_media FOR INSERT
  TO authenticated
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM stories s
      WHERE s.id = story_id
      AND s.user_id = auth.uid()::integer
    )
  );

CREATE POLICY "Users can update media for their own stories"
  ON story_media FOR UPDATE
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM stories s
      WHERE s.id = story_id
      AND s.user_id = auth.uid()::integer
    )
  );

CREATE POLICY "Users can delete media from their own stories"
  ON story_media FOR DELETE
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM stories s
      WHERE s.id = story_id
      AND s.user_id = auth.uid()::integer
    )
  );

-- Remove media columns from stories table
ALTER TABLE stories
  DROP COLUMN IF EXISTS media_url,
  DROP COLUMN IF EXISTS media_type,
  DROP COLUMN IF EXISTS thumbnail_url,
  DROP COLUMN IF EXISTS duration;