WITH uma_name AS (
	SELECT "index" AS id, "text" AS name
	FROM text_data
	WHERE category = 4
), uma_variant AS (
	SELECT "index" AS id, "text" AS variant
	FROM text_data
	WHERE category = 5
), chara_name AS (
	SELECT "index" AS id, "text" AS name
	FROM text_data
	WHERE category = 6
), skills AS (
	SELECT
		uma.id,
		s.skill_id,
		s.need_rank,
		ROW_NUMBER() OVER (PARTITION BY s.available_skill_set_id, s.need_rank) AS idx
	FROM card_data uma
		LEFT JOIN available_skill_set s ON uma.available_skill_set_id = s.available_skill_set_id
)
SELECT
	uma.card_id,
	card_data.chara_id,
	n.name,
	v.variant,
	c.name AS chara_name,
	uma.proper_distance_short,
	uma.proper_distance_mile,
	uma.proper_distance_middle,
	uma.proper_distance_long,
	uma.proper_running_style_nige,
	uma.proper_running_style_senko,
	uma.proper_running_style_sashi,
	uma.proper_running_style_oikomi,
	uma.proper_ground_turf,
	uma.proper_ground_dirt,
	su.skill_id1 AS unique_skill,
	s1.skill_id AS skill1,
	s2.skill_id AS skill2,
	s3.skill_id AS skill3,
	sp2.skill_id AS skill_pl2,
	sp3.skill_id AS skill_pl3,
	sp4.skill_id AS skill_pl4,
	sp5.skill_id AS skill_pl5
FROM card_data
	JOIN card_rarity_data uma ON card_data.id = uma.card_id
	JOIN chara_name c ON card_data.chara_id = c.id
	JOIN skill_set su ON uma.skill_set = su.id
	JOIN skills s1 ON uma.card_id = s1.id AND s1.need_rank = 0 AND s1.idx = 1
	JOIN skills s2 ON uma.card_id = s2.id AND s2.need_rank = 0 AND s2.idx = 2
	JOIN skills s3 ON uma.card_id = s3.id AND s3.need_rank = 0 AND s3.idx = 3
	JOIN skills sp2 ON uma.card_id = sp2.id AND sp2.need_rank = 2
	JOIN skills sp3 ON uma.card_id = sp3.id AND sp3.need_rank = 3
	JOIN skills sp4 ON uma.card_id = sp4.id AND sp4.need_rank = 4
	JOIN skills sp5 ON uma.card_id = sp5.id AND sp5.need_rank = 5
	LEFT JOIN uma_name n ON uma.card_id = n.id
	LEFT JOIN uma_variant v ON uma.card_id = v.id
WHERE uma.rarity = 5