`src/migrations/002_create_cms_tables.sql`

56 lines
-- Create admin users table for CMS authentication
CREATE TABLE IF NOT EXISTS admin_users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_login TIMESTAMP
);

-- Create page_content table for managing category page content
CREATE TABLE IF NOT EXISTS page_content (
  id SERIAL PRIMARY KEY,
  page_slug VARCHAR(100) NOT NULL UNIQUE,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  meta_description TEXT,
  images JSONB DEFAULT '[]'::jsonb,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_page_content_slug ON page_content(page_slug);
CREATE INDEX IF NOT EXISTS idx_admin_users_username ON admin_users(username);

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

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

-- Insert default page content for existing category pages
INSERT INTO page_content (page_slug, title, content, meta_description, images) VALUES
  ('beaches', 'Beaches', '<p>Discover the pristine beaches of Nosara, Costa Rica. Our beachfront properties offer direct access to some of the most beautiful coastlines in the world.</p>', 'Explore the beautiful beaches of Nosara, Costa Rica', '[]'::jsonb),
  ('surfing', 'Surfing', '<p>Experience world-class surfing at Playa Guiones and surrounding beaches. Perfect waves for all skill levels await you.</p>', 'World-class surfing in Nosara, Costa Rica', '[]'::jsonb),
  ('yoga-wellness', 'Yoga & Wellness', '<p>Rejuvenate your mind and body with yoga and wellness activities in the tranquil setting of Nosara.</p>', 'Yoga and wellness retreats in Nosara', '[]'::jsonb),
  ('nature-adventures', 'Nature Adventures', '<p>Explore the lush rainforests, wildlife, and natural wonders of Nosara and the surrounding Nicoya Peninsula.</p>', 'Nature adventures and wildlife in Nosara', '[]'::jsonb),
  ('culinary-delights', 'Culinary Delights', '<p>Savor the flavors of Costa Rica with fresh local cuisine, farm-to-table dining, and international restaurants.</p>', 'Culinary experiences in Nosara, Costa Rica', '[]'::jsonb),
  ('adventure-activities', 'Adventure Activities', '<p>From zip-lining to horseback riding, Nosara offers endless adventure activities for thrill-seekers.</p>', 'Adventure activities in Nosara, Costa Rica', '[]'::jsonb)
ON CONFLICT (page_slug) DO NOTHING;

-- Insert default admin user (username: admin, password: admin123)
-- Password hash generated with bcrypt, rounds=10
-- IMPORTANT: Change this password immediately in production!
INSERT INTO admin_users (username, password_hash, email) VALUES
  ('admin', '$2b$10$rKvVZYgKgqGXqLjR3k5vXO4kZJdVQxY8H9K4H.tqLFBkZQYKZLpUm', 'admin@nosarabeachfront.com')
ON CONFLICT (username) DO NOTHING;