/*
  # Create Stories Tables

  1. New Tables
    - `stories`
      - `id` (SERIAL, primary key)
      - `user_id` (INTEGER, references users)
      - `media_url` (TEXT, required)
      - `media_type` (TEXT, either 'image' or 'video')
      - `thumbnail_url` (TEXT, for video previews)
      - `caption` (TEXT)
      - `duration` (INTEGER, in seconds)
      - `expires_at` (TIMESTAMPTZ, auto-calculated)
      - `created_at` (TIMESTAMPTZ)
      - `updated_at` (TIMESTAMPTZ)
    
  2. Security
    - Enable RLS on stories table
    - Add policies for authenticated users
*/

-- Create stories table
CREATE TABLE IF NOT EXISTS stories (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  media_url TEXT NOT NULL,
  media_type TEXT NOT NULL CHECK (media_type IN ('image', 'video')),
  thumbnail_url TEXT,
  caption TEXT,
  duration INTEGER,
  expires_at TIMESTAMPTZ GENERATED ALWAYS AS (created_at + INTERVAL '24 hours') STORED,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes
CREATE INDEX IF NOT EXISTS stories_user_id_idx ON stories(user_id);
CREATE INDEX IF NOT EXISTS stories_created_at_idx ON stories(created_at);
CREATE INDEX IF NOT EXISTS stories_expires_at_idx ON stories(expires_at);

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

-- Create policies
CREATE POLICY "Users can view all active stories"
  ON stories FOR SELECT
  TO authenticated
  USING (expires_at > CURRENT_TIMESTAMP);

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

CREATE POLICY "Users can update their own stories"
  ON stories FOR UPDATE
  TO authenticated
  USING (user_id = auth.uid()::integer);

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