/*
  # Personal Gallery Schema

  1. New Tables
    - `personal_events`
      - `id` (uuid, primary key)
      - `title` (text, required)
      - `slug` (text, unique)
      - `description` (text, optional)
      - `event_date` (date, optional)
      - `location` (text, optional)
      - `featured_image` (text, optional)
      - `display_order` (integer, default 0)
      - `published` (boolean, default false)
      - `created_at` (timestamp)
      - `updated_at` (timestamp)
    
    - `personal_media`
      - `id` (uuid, primary key)
      - `event_id` (uuid, foreign key)
      - `title` (text, optional)
      - `description` (text, optional)
      - `media_url` (text, required)
      - `media_type` (text, required - image/video)
      - `file_size` (bigint, optional)
      - `dimensions` (text, optional)
      - `display_order` (integer, default 0)
      - `tags` (text array, optional)
      - `created_at` (timestamp)

  2. Security
    - Enable RLS on both tables
    - Add policies for public read access to published events
    - Add policies for authenticated users to manage content
*/

CREATE TABLE IF NOT EXISTS personal_events (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title text NOT NULL,
  slug text UNIQUE NOT NULL,
  description text,
  event_date date,
  location text,
  featured_image text,
  display_order integer DEFAULT 0,
  published boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TABLE IF NOT EXISTS personal_media (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id uuid REFERENCES personal_events(id) ON DELETE CASCADE,
  title text,
  description text,
  media_url text NOT NULL,
  media_type text NOT NULL CHECK (media_type IN ('image', 'video')),
  file_size bigint,
  dimensions text,
  display_order integer DEFAULT 0,
  tags text[],
  created_at timestamptz DEFAULT now()
);

-- Enable RLS
ALTER TABLE personal_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE personal_media ENABLE ROW LEVEL SECURITY;

-- Policies for personal_events
CREATE POLICY "Published personal events are publicly readable"
  ON personal_events
  FOR SELECT
  TO public
  USING (published = true);

CREATE POLICY "Authenticated users can manage personal events"
  ON personal_events
  FOR ALL
  TO authenticated
  USING (true)
  WITH CHECK (true);

-- Policies for personal_media
CREATE POLICY "Personal media for published events are publicly readable"
  ON personal_media
  FOR SELECT
  TO public
  USING (
    EXISTS (
      SELECT 1 FROM personal_events
      WHERE personal_events.id = personal_media.event_id
      AND personal_events.published = true
    )
  );

CREATE POLICY "Authenticated users can manage personal media"
  ON personal_media
  FOR ALL
  TO authenticated
  USING (true)
  WITH CHECK (true);

-- Add updated_at trigger for personal_events
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.triggers
    WHERE trigger_name = 'update_personal_events_updated_at'
  ) THEN
    CREATE TRIGGER update_personal_events_updated_at
      BEFORE UPDATE ON personal_events
      FOR EACH ROW
      EXECUTE FUNCTION update_updated_at_column();
  END IF;
END $$;