/*
  # Add gallery and contact tables for personal features

  1. New Tables
    - `gallery_events` for organizing photo/video collections
    - `gallery_media` for storing media files with metadata
    - `contact_submissions` for contact form submissions
    - `newsletter_subscribers` for newsletter management

  2. Security
    - Enable RLS on all tables
    - Add appropriate policies for public and authenticated access
*/

-- Create gallery_events table
CREATE TABLE IF NOT EXISTS gallery_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 gallery_media table
CREATE TABLE IF NOT EXISTS gallery_media (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id uuid REFERENCES gallery_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, -- e.g., "1920x1080"
  display_order integer DEFAULT 0,
  tags text[], -- Array of tags for filtering
  created_at timestamptz DEFAULT now()
);

-- Create contact_submissions table
CREATE TABLE IF NOT EXISTS contact_submissions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  email text NOT NULL,
  subject text,
  message text NOT NULL,
  phone text,
  organization text,
  inquiry_type text DEFAULT 'general',
  status text DEFAULT 'new' CHECK (status IN ('new', 'read', 'replied', 'archived')),
  created_at timestamptz DEFAULT now()
);

-- Create newsletter_subscribers table
CREATE TABLE IF NOT EXISTS newsletter_subscribers (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text UNIQUE NOT NULL,
  name text,
  interests text[], -- Array of interests
  subscribed boolean DEFAULT true,
  confirmed boolean DEFAULT false,
  confirmation_token text,
  subscribed_at timestamptz DEFAULT now(),
  unsubscribed_at timestamptz,
  created_at timestamptz DEFAULT now()
);

-- Enable RLS
ALTER TABLE gallery_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE gallery_media ENABLE ROW LEVEL SECURITY;
ALTER TABLE contact_submissions ENABLE ROW LEVEL SECURITY;
ALTER TABLE newsletter_subscribers ENABLE ROW LEVEL SECURITY;

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

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

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

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

-- Policies for contact_submissions
CREATE POLICY "Anyone can submit contact forms"
  ON contact_submissions
  FOR INSERT
  TO public
  WITH CHECK (true);

CREATE POLICY "Authenticated users can manage contact submissions"
  ON contact_submissions
  FOR ALL
  TO authenticated
  USING (true)
  WITH CHECK (true);

-- Policies for newsletter_subscribers
CREATE POLICY "Anyone can subscribe to newsletter"
  ON newsletter_subscribers
  FOR INSERT
  TO public
  WITH CHECK (true);

CREATE POLICY "Subscribers can update their own subscription"
  ON newsletter_subscribers
  FOR UPDATE
  TO public
  USING (true)
  WITH CHECK (true);

CREATE POLICY "Authenticated users can manage newsletter subscribers"
  ON newsletter_subscribers
  FOR ALL
  TO authenticated
  USING (true)
  WITH CHECK (true);

-- Create updated_at trigger for gallery_events
CREATE TRIGGER update_gallery_events_updated_at
  BEFORE UPDATE ON gallery_events
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- Insert sample gallery events
INSERT INTO gallery_events (title, slug, description, event_date, location, published, display_order) VALUES
  ('Family Celebrations', 'family-celebrations', 'Precious moments with family and loved ones', '2024-01-01', 'Home', true, 1),
  ('Numismatic Exhibitions', 'numismatic-exhibitions', 'Memorable moments from various numismatic exhibitions and events', '2024-02-01', 'Various Locations', true, 2),
  ('Travel Adventures', 'travel-adventures', 'Beautiful places visited and experiences shared', '2024-03-01', 'Various Destinations', true, 3),
  ('Professional Milestones', 'professional-milestones', 'Important achievements and professional moments', '2024-04-01', 'Bangalore', true, 4);

-- Insert sample gallery media
INSERT INTO gallery_media (event_id, title, media_url, media_type, display_order, tags) 
SELECT 
  ge.id,
  'Sample Image ' || generate_series(1, 3),
  'https://images.pexels.com/photos/' || (4000000 + (random() * 1000000)::int) || '/pexels-photo-' || (4000000 + (random() * 1000000)::int) || '.jpeg?auto=compress&cs=tinysrgb&w=800',
  'image',
  generate_series(1, 3),
  ARRAY['memories', 'family']
FROM gallery_events ge
WHERE ge.published = true;