-- Database Schema for Student Culinary Portfolio

-- 1. Student Profile Table
CREATE TABLE IF NOT EXISTS student_profile (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    bio TEXT,
    tagline VARCHAR(255),
    profile_pic_url VARCHAR(255),
    admin_password_hash VARCHAR(255) NOT NULL, -- For admin login
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Culinary Journey
CREATE TABLE IF NOT EXISTS culinary_journey (
    id SERIAL PRIMARY KEY,
    food_name VARCHAR(150) NOT NULL,
    image_url VARCHAR(255) NOT NULL,
    category VARCHAR(50) CHECK (category IN ('Appetizers', 'Soups', 'Main Course', 'Desserts', 'Technical Skills/Pastry')),
    learning_notes TEXT,
    date_cooked DATE,
    difficulty_level INTEGER CHECK (difficulty_level BETWEEN 1 AND 5),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3. Blog Posts
CREATE TABLE IF NOT EXISTS blog_posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    image_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Note: The initial student_profile seed is now handled by server/server.js 
-- to ensure a valid bcrypt password hash ('admin123') is generated.