CREATE TABLE grape_varieties (
    grape_variety_code INTEGER PRIMARY KEY autoincrement,
    grape_variety_name TEXT
);

CREATE TABLE colors (
    color_code INTEGER PRIMARY KEY autoincrement,
    color_name TEXT
);

CREATE TABLE countries (
    country_code INTEGER PRIMARY KEY autoincrement,
    country_name TEXT
);

CREATE TABLE regions (
    region_code INTEGER PRIMARY KEY autoincrement,
    country_code INTEGER,
    region_name TEXT,
    FOREIGN KEY (country_code) REFERENCES countries (country_code)
);

CREATE TABLE vineyards (
    vineyard_code INTEGER PRIMARY KEY autoincrement,
    vineyard_name TEXT
);

CREATE TABLE wines (
    wine_id INTEGER PRIMARY KEY autoincrement,
    color_code INTEGER,
    country_code INTEGER,
    wine_grape_variety_code INTEGER,
    region_code INTEGER,
    vineyard_code INTEGER,
    year_number INTEGER,
    noble_yn TEXT,
    wine_name TEXT,
    origin TEXT,
    price real,
    wine_description TEXT,
    wine_taste_profile TEXT,
    other_details TEXT,
    FOREIGN KEY (color_code) REFERENCES colors (color_code),
    FOREIGN KEY (country_code) REFERENCES countries (country_code),
    FOREIGN KEY (wine_grape_variety_code) REFERENCES grape_varieties (grape_variety_code),
    FOREIGN KEY (region_code) REFERENCES regions (region_code),
    FOREIGN KEY (vineyard_code) REFERENCES vineyards (vineyard_code)
);

CREATE TABLE food_categories (
    food_category_code INTEGER PRIMARY KEY autoincrement,
    food_category_name TEXT
);

CREATE TABLE foods (
    food_id INTEGER PRIMARY KEY autoincrement,
    food_category_code INTEGER,
    food_name TEXT,
    food_description TEXT,
    other_details TEXT,
    food_preparation TEXT,
    food_taste_profile TEXT,
    food_temp TEXT,
    food_notes TEXT,
    FOREIGN KEY (food_category_code) REFERENCES food_categories (food_category_code)
);

CREATE TABLE ratings (
    rating_code INTEGER PRIMARY KEY autoincrement,
    rating_points INTEGER,
    rating_description TEXT
);

CREATE TABLE wine_and_food_pairings (
    wine_id INTEGER,
    food_id INTEGER,
    rating_code INTEGER,
    PRIMARY KEY (wine_id, food_id),
    FOREIGN KEY (wine_id) REFERENCES wines (wine_id),
    FOREIGN KEY (food_id) REFERENCES foods (food_id),
    FOREIGN KEY (rating_code) REFERENCES ratings (rating_code)
);

INSERT INTO grape_varieties (grape_variety_name) VALUES
('Pinot Grigio'),
('Merlot'),
('Chardonnay'),
('Sauvignon Blanc'),
('Cabernet Sauvignon'),
('Syrah'),
('Riesling'),
('Zinfandel'),
('Malbec'),
('Grenache');

INSERT INTO colors (color_name) VALUES
('White'),
('Red'),
('Rose'),
('Sparkling'),
('Dessert');

INSERT INTO countries (country_name) VALUES
('France'),
('USA'),
('Italy'),
('Spain'),
('Australia');

INSERT INTO regions (country_code, region_name) VALUES
(1, 'Bordeaux'),
(1, 'Burgundy'),
(2, 'Napa Valley'),
(2, 'Sonoma'),
(3, 'Tuscany'),
(3, 'Veneto'),
(4, 'Rioja'),
(4, 'Catalonia'),
(5, 'Barossa Valley'),
(5, 'Yarra Valley');

INSERT INTO vineyards (vineyard_name) VALUES
('Chateau Margaux'),
('Opus One'),
('Antinori'),
('Penfolds'),
('Bodegas Torres'),
('Villa Maria'),
('Clos du Val'),
('Domaine de la Romanee-Conti'),
('Beringer'),
('Louis Jadot');

INSERT INTO wines (color_code, country_code, wine_grape_variety_code, region_code, vineyard_code, year_number, noble_yn, wine_name, origin, price, wine_description, wine_taste_profile, other_details) VALUES
(1, 1, 1, 1, 1, 2020, 'Y', 'Chateau Margaux 2020', 'Margaux', 350.00, 'A rich and elegant wine.', 'Fruity, Tannic', 'Limited Edition'),
(2, 2, 2, 3, 2, 2018, 'N', 'Opus One 2018', 'Napa Valley', 300.00, 'A bold and balanced wine.', 'Fruity, Oaky', 'Popular Choice'),
(1, 3, 3, 5, 3, 2019, 'N', 'Antinori Tignanello 2019', 'Tuscany', 150.00, 'A smooth and complex wine.', 'Crisp, Citrus', 'High Demand'),
(2, 4, 4, 7, 4, 2021, 'Y', 'Penfolds Grange 2021', 'Barossa Valley', 400.00, 'A full-bodied and intense wine.', 'Spicy, Rich', 'Award Winner'),
(1, 5, 5, 9, 5, 2017, 'N', 'Bodegas Torres Mas La Plana 2017', 'Rioja', 100.00, 'A fresh and vibrant wine.', 'Floral, Dry', 'Best Seller'),
(3, 1, 6, 2, 6, 2022, 'N', 'Villa Maria Private Bin 2022', 'Burgundy', 25.00, 'A light and refreshing wine.', 'Sweet, Floral', 'Affordable'),
(2, 2, 7, 4, 7, 2020, 'N', 'Clos du Val 2020', 'Sonoma', 75.00, 'A smooth and balanced wine.', 'Fruity, Earthy', 'New Release'),
(3, 3, 8, 6, 8, 2018, 'Y', 'Domaine de la Romanee-Conti 2018', 'Veneto', 1200.00, 'A luxurious and refined wine.', 'Bold, Rich', 'Exclusive'),
(1, 4, 9, 8, 9, 2019, 'N', 'Beringer Private Reserve 2019', 'Catalonia', 90.00, 'A crisp and clean wine.', 'Citrus, Dry', 'Popular'),
(2, 5, 10, 10, 10, 2021, 'N', 'Louis Jadot Beaujolais 2021', 'Yarra Valley', 50.00, 'A lively and fruity wine.', 'Juicy, Light', 'Seasonal Favorite'),
(1, 1, 1, 1, 1, 2020, 'Y', 'Chateau Margaux 2019', 'Margaux', 350.00, 'A rich and elegant wine.', 'Fruity, Tannic', 'Limited Edition'),
(2, 2, 2, 3, 2, 2017, 'N', 'Opus One 2017', 'Napa Valley', 300.00, 'A bold and balanced wine.', 'Fruity, Oaky', 'Popular Choice'),
(1, 3, 3, 5, 3, 2018, 'N', 'Antinori Tignanello 2018', 'Tuscany', 150.00, 'A smooth and complex wine.', 'Crisp, Citrus', 'High Demand'),
(2, 4, 4, 7, 4, 2020, 'Y', 'Penfolds Grange 2020', 'Barossa Valley', 400.00, 'A full-bodied and intense wine.', 'Spicy, Rich', 'Award Winner'),
(1, 5, 5, 9, 5, 2018, 'N', 'Bodegas Torres Mas La Plana 2018', 'Rioja', 100.00, 'A fresh and vibrant wine.', 'Floral, Dry', 'Best Seller'),
(3, 1, 6, 2, 6, 2021, 'N', 'Villa Maria Private Bin 2021', 'Burgundy', 25.00, 'A light and refreshing wine.', 'Sweet, Floral', 'Affordable'),
(2, 2, 7, 4, 7, 2019, 'N', 'Clos du Val 2019', 'Sonoma', 75.00, 'A smooth and balanced wine.', 'Fruity, Earthy', 'New Release'),
(3, 3, 8, 6, 8, 2017, 'Y', 'Domaine de la Romanee-Conti 2017', 'Veneto', 1200.00, 'A luxurious and refined wine.', 'Bold, Rich', 'Exclusive'),
(1, 4, 9, 8, 9, 2018, 'N', 'Beringer Private Reserve 2018', 'Catalonia', 90.00, 'A crisp and clean wine.', 'Citrus, Dry', 'Popular'),
(2, 5, 10, 10, 10, 2020, 'N', 'Louis Jadot Beaujolais 2020', 'Yarra Valley', 50.00, 'A lively and fruity wine.', 'Juicy, Light', 'Seasonal Favorite'),
(1, 1, 1, 1, 1, 2019, 'Y', 'Chateau Margaux 2018', 'Margaux', 350.00, 'A rich and elegant wine.', 'Fruity, Tannic', 'Limited Edition'),
(2, 2, 2, 3, 2, 2016, 'N', 'Opus One 2016', 'Napa Valley', 300.00, 'A bold and balanced wine.', 'Fruity, Oaky', 'Popular Choice'),
(1, 3, 3, 5, 3, 2017, 'N', 'Antinori Tignanello 2017', 'Tuscany', 150.00, 'A smooth and complex wine.', 'Crisp, Citrus', 'High Demand'),
(2, 4, 4, 7, 4, 2019, 'Y', 'Penfolds Grange 2019', 'Barossa Valley', 400.00, 'A full-bodied and intense wine.', 'Spicy, Rich', 'Award Winner'),
(1, 5, 5, 9, 5, 2016, 'N', 'Bodegas Torres Mas La Plana 2016', 'Rioja', 100.00, 'A fresh and vibrant wine.', 'Floral, Dry', 'Best Seller'),
(3, 1, 6, 2, 6, 2020, 'N', 'Villa Maria Private Bin 2020', 'Burgundy', 25.00, 'A light and refreshing wine.', 'Sweet, Floral', 'Affordable'),
(2, 2, 7, 4, 7, 2018, 'N', 'Clos du Val 2018', 'Sonoma', 75.00, 'A smooth and balanced wine.', 'Fruity, Earthy', 'New Release'),
(3, 3, 8, 6, 8, 2016, 'Y', 'Domaine de la Romanee-Conti 2016', 'Veneto', 1200.00, 'A luxurious and refined wine.', 'Bold, Rich', 'Exclusive'),
(1, 4, 9, 8, 9, 2017, 'N', 'Beringer Private Reserve 2017', 'Catalonia', 90.00, 'A crisp and clean wine.', 'Citrus, Dry', 'Popular'),
(2, 5, 10, 10, 10, 2019, 'N', 'Louis Jadot Beaujolais 2019', 'Yarra Valley', 50.00, 'A lively and fruity wine.', 'Juicy, Light', 'Seasonal Favorite'),
(1, 1, 1, 1, 1, 2018, 'Y', 'Chateau Margaux 2017', 'Margaux', 350.00, 'A rich and elegant wine.', 'Fruity, Tannic', 'Limited Edition'),
(2, 2, 2, 3, 2, 2015, 'N', 'Opus One 2015', 'Napa Valley', 300.00, 'A bold and balanced wine.', 'Fruity, Oaky', 'Popular Choice'),
(1, 3, 3, 5, 3, 2016, 'N', 'Antinori Tignanello 2016', 'Tuscany', 150.00, 'A smooth and complex wine.', 'Crisp, Citrus', 'High Demand'),
(2, 4, 4, 7, 4, 2018, 'Y', 'Penfolds Grange 2018', 'Barossa Valley', 400.00, 'A full-bodied and intense wine.', 'Spicy, Rich', 'Award Winner'),
(1, 5, 5, 9, 5, 2015, 'N', 'Bodegas Torres Mas La Plana 2015', 'Rioja', 100.00, 'A fresh and vibrant wine.', 'Floral, Dry', 'Best Seller'),
(3, 1, 6, 2, 6, 2019, 'N', 'Villa Maria Private Bin 2019', 'Burgundy', 25.00, 'A light and refreshing wine.', 'Sweet, Floral', 'Affordable'),
(2, 2, 7, 4, 7, 2017, 'N', 'Clos du Val 2017', 'Sonoma', 75.00, 'A smooth and balanced wine.', 'Fruity, Earthy', 'New Release'),
(3, 3, 8, 6, 8, 2015, 'Y', 'Domaine de la Romanee-Conti 2015', 'Veneto', 1200.00, 'A luxurious and refined wine.', 'Bold, Rich', 'Exclusive'),
(1, 4, 9, 8, 9, 2016, 'N', 'Beringer Private Reserve 2016', 'Catalonia', 90.00, 'A crisp and clean wine.', 'Citrus, Dry', 'Popular'),
(2, 5, 10, 10, 10, 2018, 'N', 'Louis Jadot Beaujolais 2018', 'Yarra Valley', 50.00, 'A lively and fruity wine.', 'Juicy, Light', 'Seasonal Favorite'),
(1, 1, 1, 1, 1, 2017, 'Y', 'Chateau Margaux 2016', 'Margaux', 350.00, 'A rich and elegant wine.', 'Fruity, Tannic', 'Limited Edition'),
(2, 2, 2, 3, 2, 2014, 'N', 'Opus One 2014', 'Napa Valley', 300.00, 'A bold and balanced wine.', 'Fruity, Oaky', 'Popular Choice'),
(1, 3, 3, 5, 3, 2015, 'N', 'Antinori Tignanello 2015', 'Tuscany', 150.00, 'A smooth and complex wine.', 'Crisp, Citrus', 'High Demand'),
(2, 4, 4, 7, 4, 2017, 'Y', 'Penfolds Grange 2017', 'Barossa Valley', 400.00, 'A full-bodied and intense wine.', 'Spicy, Rich', 'Award Winner'),
(1, 5, 5, 9, 5, 2014, 'N', 'Bodegas Torres Mas La Plana 2014', 'Rioja', 100.00, 'A fresh and vibrant wine.', 'Floral, Dry', 'Best Seller');

INSERT INTO food_categories (food_category_name) VALUES
('Meat'),
('Fish'),
('Vegetarian'),
('Dessert'),
('Appetizer');

INSERT INTO foods (food_category_code, food_name, food_description, other_details, food_preparation, food_taste_profile, food_temp, food_notes) VALUES
(1, 'Grilled Steak', 'Juicy and tender grilled steak.', 'Best with red wine.', 'Grilled', 'Savory, Juicy', 'Hot', 'Medium Rare'),
(2, 'Salmon Fillet', 'Fresh and flaky salmon fillet.', 'Best with white wine.', 'Baked', 'Tender, Flaky', 'Warm', 'Lemon Butter Sauce'),
(3, 'Vegetable Stir Fry', 'Crispy and colorful vegetable stir fry.', 'Best with white wine.', 'Stir-Fried', 'Crunchy, Savory', 'Hot', 'Soy Sauce'),
(4, 'Chocolate Cake', 'Rich and moist chocolate cake.', 'Best with dessert wine.', 'Baked', 'Sweet, Rich', 'Cold', 'Dark Chocolate'),
(5, 'Bruschetta', 'Crispy bread with fresh tomato topping.', 'Best with rose wine.', 'Toasted', 'Crispy, Fresh', 'Cold', 'Garlic and Basil'),
(1, 'Roast Lamb', 'Flavorful and tender roast lamb.', 'Best with red wine.', 'Roasted', 'Savory, Tender', 'Hot', 'Herb Crust'),
(2, 'Shrimp Cocktail', 'Chilled shrimp with cocktail sauce.', 'Best with sparkling wine.', 'Boiled', 'Tender, Savory', 'Cold', 'Cocktail Sauce'),
(3, 'Caesar Salad', 'Crisp romaine with Caesar dressing.', 'Best with white wine.', 'Raw', 'Crunchy, Savory', 'Cold', 'Parmesan Cheese'),
(4, 'Tiramisu', 'Creamy and coffee-flavored tiramisu.', 'Best with dessert wine.', 'Assembled', 'Sweet, Creamy', 'Cold', 'Coffee and Cocoa'),
(5, 'Stuffed Mushrooms', 'Savory mushrooms stuffed with cheese.', 'Best with red wine.', 'Baked', 'Savory, Cheesy', 'Warm', 'Breadcrumbs and Herbs'),
(1, 'Beef Wellington', 'Tender beef wrapped in pastry.', 'Best with red wine.', 'Baked', 'Savory, Rich', 'Hot', 'Medium Rare'),
(2, 'Tuna Tartare', 'Fresh tuna with Asian flavors.', 'Best with white wine.', 'Raw', 'Fresh, Savory', 'Cold', 'Soy Sauce and Avocado'),
(3, 'Caprese Salad', 'Tomato and mozzarella salad.', 'Best with rose wine.', 'Raw', 'Fresh, Savory', 'Cold', 'Basil and Balsamic'),
(4, 'Cheesecake', 'Creamy and tangy cheesecake.', 'Best with dessert wine.', 'Baked', 'Sweet, Creamy', 'Cold', 'Fruit Topping'),
(5, 'Stuffed Peppers', 'Peppers stuffed with rice and cheese.', 'Best with red wine.', 'Baked', 'Savory, Cheesy', 'Hot', 'Tomato Sauce'),
(1, 'Duck Confit', 'Slow-cooked duck leg.', 'Best with red wine.', 'Slow-Cooked', 'Savory, Tender', 'Hot', 'Crispy Skin'),
(2, 'Crab Cakes', 'Savory crab cakes with aioli.', 'Best with white wine.', 'Fried', 'Savory, Crispy', 'Hot', 'Lemon Aioli'),
(3, 'Ratatouille', 'Mixed vegetable stew.', 'Best with white wine.', 'Stewed', 'Savory, Tender', 'Hot', 'Herbs and Olive Oil'),
(4, 'Pavlova', 'Meringue dessert with fruit.', 'Best with dessert wine.', 'Baked', 'Sweet, Light', 'Cold', 'Berries and Cream'),
(5, 'Deviled Eggs', 'Eggs stuffed with a tangy filling.', 'Best with sparkling wine.', 'Boiled', 'Savory, Creamy', 'Cold', 'Paprika Sprinkle'),
(1, 'Braised Short Ribs', 'Tender beef short ribs.', 'Best with red wine.', 'Braised', 'Savory, Rich', 'Hot', 'Red Wine Sauce'),
(2, 'Grilled Lobster', 'Fresh grilled lobster.', 'Best with white wine.', 'Grilled', 'Tender, Savory', 'Hot', 'Garlic Butter'),
(3, 'Mushroom Risotto', 'Creamy mushroom risotto.', 'Best with white wine.', 'Stirred', 'Savory, Creamy', 'Hot', 'Parmesan Cheese'),
(4, 'Creme Brulee', 'Rich custard with caramelized sugar.', 'Best with dessert wine.', 'Baked', 'Sweet, Creamy', 'Cold', 'Caramel Top'),
(5, 'Spring Rolls', 'Crispy vegetable spring rolls.', 'Best with sparkling wine.', 'Fried', 'Crispy, Fresh', 'Warm', 'Sweet Chili Sauce'),
(1, 'Osso Buco', 'Braised veal shanks.', 'Best with red wine.', 'Braised', 'Savory, Tender', 'Hot', 'Gremolata'),
(2, 'Grilled Octopus', 'Tender grilled octopus.', 'Best with white wine.', 'Grilled', 'Tender, Savory', 'Hot', 'Lemon and Olive Oil'),
(3, 'Vegetable Paella', 'Mixed vegetable paella.', 'Best with rose wine.', 'Cooked', 'Savory, Rich', 'Hot', 'Saffron and Peppers'),
(4, 'Apple Pie', 'Classic apple pie.', 'Best with dessert wine.', 'Baked', 'Sweet, Tart', 'Warm', 'Cinnamon'),
(5, 'Mini Quiches', 'Savory mini quiches.', 'Best with sparkling wine.', 'Baked', 'Savory, Cheesy', 'Warm', 'Spinach and Cheese'),
(1, 'Ribeye Steak', 'Juicy ribeye steak.', 'Best with red wine.', 'Grilled', 'Savory, Juicy', 'Hot', 'Medium Rare'),
(2, 'Fish Tacos', 'Fresh fish tacos.', 'Best with white wine.', 'Grilled', 'Tender, Savory', 'Warm', 'Cilantro Lime'),
(3, 'Falafel', 'Crispy chickpea balls.', 'Best with white wine.', 'Fried', 'Crispy, Savory', 'Warm', 'Tahini Sauce'),
(4, 'Baklava', 'Sweet pastry with nuts.', 'Best with dessert wine.', 'Baked', 'Sweet, Crunchy', 'Cold', 'Honey Syrup'),
(5, 'Crostini', 'Toasted bread with toppings.', 'Best with rose wine.', 'Toasted', 'Crispy, Savory', 'Cold', 'Various Toppings'),
(1, 'Lamb Chops', 'Tender lamb chops.', 'Best with red wine.', 'Grilled', 'Savory, Tender', 'Hot', 'Mint Sauce'),
(2, 'Seared Scallops', 'Tender seared scallops.', 'Best with white wine.', 'Seared', 'Tender, Savory', 'Hot', 'Lemon Butter'),
(3, 'Eggplant Parmesan', 'Eggplant with cheese and tomato.', 'Best with red wine.', 'Baked', 'Savory, Cheesy', 'Hot', 'Marinara Sauce'),
(4, 'Mousse', 'Light and airy mousse.', 'Best with dessert wine.', 'Whipped', 'Sweet, Light', 'Cold', 'Chocolate'),
(5, 'Cheese Plate', 'Assorted cheeses.', 'Best with sparkling wine.', 'Arranged', 'Savory, Rich', 'Cold', 'Various Cheeses');

INSERT INTO ratings (rating_points, rating_description) VALUES
(5, 'Excellent'),
(4, 'Very Good'),
(3, 'Good'),
(2, 'Fair'),
(1, 'Poor');

INSERT INTO wine_and_food_pairings (wine_id, food_id, rating_code) VALUES
(1, 3, 5),
(2, 5, 4),
(3, 7, 4),
(4, 9, 5),
(5, 11, 3),
(6, 13, 4),
(7, 15, 5),
(8, 17, 4),
(9, 19, 3),
(10, 21, 4),
(11, 23, 5),
(12, 25, 4),
(13, 27, 4),
(14, 29, 5),
(15, 31, 3),
(16, 33, 4),
(17, 35, 5),
(18, 37, 4),
(19, 39, 3),
(20, 41, 4),
(21, 43, 5),
(22, 45, 4),
(23, 47, 4),
(24, 49, 5),
(25, 2, 3),
(26, 4, 4),
(27, 6, 5),
(28, 8, 4),
(29, 10, 3),
(30, 12, 4),
(31, 14, 5),
(32, 16, 4),
(33, 18, 4),
(34, 20, 5),
(35, 22, 3),
(36, 24, 4),
(37, 26, 5),
(38, 28, 4),
(39, 30, 3),
(40, 32, 4),
(41, 34, 5),
(42, 36, 4),
(43, 38, 4),
(44, 40, 5),
(45, 42, 3),
(46, 44, 4),
(47, 46, 5),
(48, 48, 4),
(49, 50, 3),
(50, 1, 4),
(1, 5, 4),
(2, 7, 5),
(3, 9, 4),
(4, 11, 3),
(5, 13, 4),
(6, 15, 5),
(7, 17, 4),
(8, 19, 3),
(9, 21, 4),
(10, 23, 5),
(11, 25, 4),
(12, 27, 4),
(13, 29, 5),
(14, 31, 3),
(15, 33, 4),
(16, 35, 5),
(17, 37, 4),
(18, 39, 3),
(19, 41, 4),
(20, 43, 5),
(21, 45, 4),
(22, 47, 4),
(23, 49, 5),
(24, 2, 3),
(25, 4, 4),
(26, 6, 5),
(27, 8, 4),
(28, 10, 3),
(29, 12, 4),
(30, 14, 5),
(31, 16, 4),
(32, 18, 4),
(33, 20, 5),
(34, 22, 3),
(35, 24, 4),
(36, 26, 5),
(37, 28, 4),
(38, 30, 3),
(39, 32, 4),
(40, 34, 5),
(41, 36, 4),
(42, 38, 4),
(43, 40, 5),
(44, 42, 3),
(45, 44, 4),
(46, 46, 5),
(47, 48, 4),
(48, 50, 3),
(49, 1, 4);

-- SELECT fc.food_category_name, COUNT(wfp.wine_id) AS excellent_pairings
-- FROM wine_and_food_pairings wfp
-- JOIN foods f ON wfp.food_id = f.food_id
-- JOIN food_categories fc ON f.food_category_code = fc.food_category_code
-- JOIN ratings r ON wfp.rating_code = r.rating_points
-- WHERE r.rating_points = 5
-- GROUP BY fc.food_category_name;

-- WITH MostExpensiveWines AS (
--     SELECT
--         wine_grape_variety_code,
--         region_code,
--         MAX(price) AS max_price
--     FROM wines
--     GROUP BY wine_grape_variety_code, region_code
-- )
-- SELECT
--     gv.grape_variety_name,
--     r.region_name,
--     w.wine_name,
--     mew.max_price
-- FROM MostExpensiveWines mew
-- JOIN wines w ON mew.wine_grape_variety_code = w.wine_grape_variety_code
-- AND mew.region_code = w.region_code
-- AND mew.max_price = w.price
-- JOIN grape_varieties gv ON w.wine_grape_variety_code = gv.grape_variety_code
-- JOIN regions r ON w.region_code = r.region_code
-- ORDER BY gv.grape_variety_name, r.region_name;

-- WITH RatingRanges AS (
--     SELECT
--         food_id,
--         MAX(r.rating_points) - MIN(r.rating_points) AS rating_range
--     FROM wine_and_food_pairings wfp
--     JOIN ratings r ON wfp.rating_code = r.rating_code
--     GROUP BY food_id
-- )
-- SELECT
--     f.food_name,
--     rr.rating_range
-- FROM RatingRanges rr
-- JOIN foods f ON rr.food_id = f.food_id
-- ORDER BY rr.rating_range DESC
-- LIMIT 1;

WITH BestWines AS (
    SELECT
        food_id,
        MAX(rating_points) AS best_rating
    FROM wine_and_food_pairings wfp
    JOIN ratings r ON wfp.rating_code = r.rating_code
    GROUP BY food_id
)
SELECT
    f.food_name,
    w.wine_name,
    bw.best_rating
FROM BestWines bw
JOIN wine_and_food_pairings wfp ON bw.food_id = wfp.food_id
AND bw.best_rating = wfp.rating_code
JOIN wines w ON wfp.wine_id = w.wine_id
JOIN foods f ON bw.food_id = f.food_id
JOIN ratings r ON wfp.rating_code = r.rating_code
ORDER BY f.food_name;