`src/migrations/002_create_page_content.sql`

30 lines
-- Create page_content table for CMS
CREATE TABLE IF NOT EXISTS page_content (
  id SERIAL PRIMARY KEY,
  page_slug VARCHAR(100) NOT NULL UNIQUE,
  page_title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  gallery_images JSONB DEFAULT '[]'::JSONB,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create index on page_slug for fast lookups
CREATE INDEX IF NOT EXISTS idx_page_content_slug ON page_content(page_slug);

-- Create trigger to update updated_at timestamp
CREATE TRIGGER update_page_content_updated_at
  BEFORE UPDATE ON page_content
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- Insert default content for existing pages
INSERT INTO page_content (page_slug, page_title, content, gallery_images) VALUES
  ('beaches', 'Beaches', 'Discover the stunning beaches of Nosara, where pristine sands meet crystal-clear waters.', '[]'::JSONB),
  ('surfing', 'Surfing', 'Experience world-class surfing at some of Costa Rica''s most famous breaks.', '[]'::JSONB),
  ('yoga-wellness', 'Yoga & Wellness', 'Find your zen with our curated selection of yoga retreats and wellness experiences.', '[]'::JSONB),
  ('nature-adventures', 'Nature Adventures', 'Explore the incredible biodiversity of Costa Rica''s rainforests and wildlife.', '[]'::JSONB),
  ('culinary-delights', 'Culinary Delights', 'Savor the flavors of Costa Rica with farm-to-table dining and local cuisine.', '[]'::JSONB),
  ('adventure-activities', 'Adventure Activities', 'Get your adrenaline pumping with zip-lining, ATV tours, and more.', '[]'::JSONB)
ON CONFLICT (page_slug) DO NOTHING;