WITH RecipeBase AS (
	SELECT
		r.id,
		r.name,
		r.date_published,
		r.description,
		r.recipe_cuisine,
		r.prep_time,
		r.cook_time,
		r.total_time,
		r.recipe_yield,
		r.recipe_category,
		u.name AS author_name,
		n.calories
	FROM
		recipes r
		LEFT JOIN users u ON r.author_id = u.id
		LEFT JOIN nutrition_information n ON r.nutrition_id = n.id
	WHERE
		r.id = 1
),
RecipeImages AS (
	SELECT
		ri.recipe_id,
		JSON_GROUP_ARRAY(ri.image_url) AS images
	FROM
		recipe_images ri
	WHERE
		ri.recipe_id = 1
	GROUP BY
		ri.recipe_id
),
RecipeKeywords AS (
	SELECT
		rk.recipe_id,
		JSON_GROUP_ARRAY(k.keyword) AS keywords
	FROM
		recipe_keywords rk
		JOIN keywords k ON rk.keyword_id = k.id
	WHERE
		rk.recipe_id = 1
	GROUP BY
		rk.recipe_id
),
RecipeIngredients AS (
	SELECT
		ri.recipe_id,
		JSON_GROUP_ARRAY(ri.ingredient) AS ingredients
	FROM
		recipe_ingredients ri
	WHERE
		ri.recipe_id = 1
	GROUP BY
		ri.recipe_id
),
HowToSteps AS (
	SELECT
		ss.recipe_instruction_section_id,
		JSON_GROUP_ARRAY(
			JSON_OBJECT(
				'@type', 'HowToStep', 'name', st.name,
				'text', st.text, 'image', st.image
			)
		) AS steps
	FROM
		recipe_instruction_section_steps ss
		JOIN recipe_instruction_steps st ON ss.recipe_instruction_step_id = st.id
	GROUP BY
		ss.recipe_instruction_section_id
),
RecipeInstructions AS (
	SELECT
		ri.recipe_id,
		JSON_GROUP_ARRAY(
			CASE WHEN ri.instruction_type = 'text' THEN ri.text WHEN ri.instruction_type = 'recipe_instruction_step' THEN JSON_OBJECT(
				'@type', 'HowToStep', 'name', s.name,
				'text', s.text, 'image', s.image
			) WHEN ri.instruction_type = 'recipe_instruction_section' THEN JSON_OBJECT(
				'@type',
				'HowToSection',
				'name',
				sec.name,
				'itemListElement',
				COALESCE(
					hts.steps,
					JSON_ARRAY()
				)
			) END
		) AS instructions
	FROM
		recipe_instructions ri
		LEFT JOIN recipe_instruction_steps s ON ri.instruction_id = s.id
		LEFT JOIN recipe_instruction_sections sec ON ri.instruction_id = sec.id
		LEFT JOIN HowToSteps hts ON sec.id = hts.recipe_instruction_section_id
	WHERE
		ri.recipe_id = 1
	GROUP BY
		ri.recipe_id
),
AggregateRatings AS (
	SELECT
		rr.recipe_id,
		AVG(rr.rating_value) AS rating_value,
		COUNT(rr.rating_value) AS rating_count
	FROM
		recipe_ratings rr
	WHERE
		rr.recipe_id = 1
	GROUP BY
		rr.recipe_id
)
SELECT
	JSON_OBJECT(
		'@context',
		'https://schema.org/',
		'@type',
		'Recipe',
		'name',
		rb.name,
		'image',
		COALESCE(
			RecipeImages.images,
			JSON_ARRAY()
		),
		'author',
		CASE WHEN rb.author_name IS NOT NULL THEN JSON_OBJECT(
			'@type', 'Person', 'name', rb.author_name
		) ELSE NULL END,
		'datePublished',
		rb.date_published,
		'description',
		rb.description,
		'recipeCuisine',
		rb.recipe_cuisine,
		'prepTime',
		rb.prep_time,
		'cookTime',
		rb.cook_time,
		'totalTime',
		rb.total_time,
		'keywords',
		COALESCE(
			RecipeKeywords.keywords,
			JSON_ARRAY()
		),
		'recipeYield',
		rb.recipe_yield,
		'recipeCategory',
		rb.recipe_category,
		'nutrition',
		CASE WHEN rb.calories IS NOT NULL THEN JSON_OBJECT(
			'@type', 'NutritionInformation',
			'calories', rb.calories
		) ELSE NULL END,
		'aggregateRating',
		CASE WHEN ar.rating_value IS NOT NULL THEN JSON_OBJECT(
			'@type', 'AggregateRating', 'ratingValue',
			ar.rating_value, 'ratingCount',
			ar.rating_count
		) ELSE NULL END,
		'recipeIngredient',
		COALESCE(
			RecipeIngredients.ingredients,
			JSON_ARRAY()
		),
		'recipeInstructions',
		COALESCE(
			JSON(
				RecipeInstructions.instructions
			),
			JSON_ARRAY()
		)
	) AS recipe
FROM
	RecipeBase rb
	LEFT JOIN RecipeImages ON rb.id = RecipeImages.recipe_id
	LEFT JOIN RecipeKeywords ON rb.id = RecipeKeywords.recipe_id
	LEFT JOIN RecipeIngredients ON rb.id = RecipeIngredients.recipe_id
	LEFT JOIN RecipeInstructions ON rb.id = RecipeInstructions.recipe_id
	LEFT JOIN AggregateRatings ar ON rb.id = ar.recipe_id;

-- Table to store users
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);

-- Table to store nutrition information
CREATE TABLE nutrition_information (id INTEGER PRIMARY KEY, calories TEXT NOT NULL);

-- Table to store individual ratings
CREATE TABLE recipe_ratings (
  id INTEGER PRIMARY KEY,
  recipe_id INTEGER,
  user_id INTEGER,
  rating_value INTEGER NOT NULL,
  FOREIGN KEY (recipe_id) REFERENCES recipes (id),
  FOREIGN KEY (user_id) REFERENCES users (id)
);

-- Table to store recipe instruction steps
CREATE TABLE recipe_instruction_steps (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  text TEXT NOT NULL,
  image TEXT NOT NULL
);

-- Table to store recipe instruction sections
CREATE TABLE recipe_instruction_sections (id INTEGER PRIMARY KEY, name TEXT NOT NULL);

-- Junction table for recipe instruction sections and steps (many-to-many relationship)
CREATE TABLE recipe_instruction_section_steps (
  recipe_instruction_section_id INTEGER,
  recipe_instruction_step_id INTEGER,
  FOREIGN KEY (recipe_instruction_section_id) REFERENCES recipe_instruction_sections (id),
  FOREIGN KEY (recipe_instruction_step_id) REFERENCES recipe_instruction_steps (id),
  PRIMARY KEY (
    recipe_instruction_section_id,
    recipe_instruction_step_id
  )
);

-- Table to store recipes
CREATE TABLE recipes (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  date_published TEXT NOT NULL,
  description TEXT NOT NULL,
  recipe_cuisine TEXT NOT NULL,
  prep_time TEXT NOT NULL,
  cook_time TEXT NOT NULL,
  total_time TEXT NOT NULL,
  recipe_yield TEXT NOT NULL,
  recipe_category TEXT NOT NULL,
  author_id INTEGER,
  nutrition_id INTEGER,
  FOREIGN KEY (author_id) REFERENCES users (id),
  FOREIGN KEY (nutrition_id) REFERENCES nutrition_information (id)
);

-- Table to store recipe images (one-to-many relationship)
CREATE TABLE recipe_images (
  id INTEGER PRIMARY KEY,
  recipe_id INTEGER,
  image_url TEXT NOT NULL,
  FOREIGN KEY (recipe_id) REFERENCES recipes (id)
);

-- Table to store keywords
CREATE TABLE keywords (id INTEGER PRIMARY KEY, keyword TEXT NOT NULL);

-- Junction table to link recipes to keywords (many-to-many relationship)
CREATE TABLE recipe_keywords (
  recipe_id INTEGER,
  keyword_id INTEGER,
  FOREIGN KEY (recipe_id) REFERENCES recipes (id),
  FOREIGN KEY (keyword_id) REFERENCES keywords (id),
  PRIMARY KEY (recipe_id, keyword_id)
);

-- Table to store recipe ingredients (one-to-many relationship)
CREATE TABLE recipe_ingredients (
  id INTEGER PRIMARY KEY,
  recipe_id INTEGER,
  ingredient TEXT NOT NULL,
  FOREIGN KEY (recipe_id) REFERENCES recipes (id)
);

-- Table to store recipe instructions (one-to-many relationship)
CREATE TABLE recipe_instructions (
  id INTEGER PRIMARY KEY,
  recipe_id INTEGER,
  instruction_type TEXT NOT NULL,
  instruction_id INTEGER,
  text TEXT,
  FOREIGN KEY (recipe_id) REFERENCES recipes (id)
);

-- Insert sample data into related tables first
INSERT INTO
  users (id, name)
VALUES
  (1, 'John Doe');

INSERT INTO
  nutrition_information (id, calories)
VALUES
  (1, '200 calories');

INSERT INTO
  recipes (
    id,
    name,
    date_published,
    description,
    recipe_cuisine,
    prep_time,
    cook_time,
    total_time,
    recipe_yield,
    recipe_category,
    author_id,
    nutrition_id
  )
VALUES
  (
    1,
    'Sample Recipe',
    '2024-07-18',
    'A delicious sample recipe.',
    'International',
    'PT20M',
    'PT30M',
    'PT50M',
    '4 servings',
    'Dessert',
    1,
    1
  );

INSERT INTO
  recipe_instruction_steps (id, name, text, image)
VALUES
  (1, 'Step 1', 'Mix ingredients.', 'image1.jpg');

INSERT INTO
  recipe_instruction_sections (id, name)
VALUES
  (1, 'Section 1');

-- Insert records into recipe_instructions
INSERT INTO
  recipe_instructions (recipe_id, instruction_type, instruction_id, text)
VALUES
  (1, 'text', NULL, 'Preheat oven to 350 degrees.'),
  (1, 'recipe_instruction_step', 1, NULL),
  (1, 'recipe_instruction_section', 1, NULL);

-- Insert sample rating
INSERT INTO
  recipe_ratings (recipe_id, user_id, rating_value)
VALUES
  (1, 1, 5);