--USE[[email protected]]
--GO

--SET CHARACTER SET 'utf8';
-- SET SESSION collation_connection = 'utf8_general_ci';

if OBJECT_ID('prices', 'U') is not null drop table prices
if OBJECT_ID('products_in_bag', 'U') is not null drop table products_in_bag
if OBJECT_ID('cities', 'U') is not null drop table cities
if OBJECT_ID('products', 'U') is not null drop table products


create table products
(
	name nvarchar(30) NOT NULL,
	id int NOT NULL primary key IDENTITY(1,1)
)
GO

create table cities
(
	name nvarchar(30) NOT NULL,
	id int NOT NULL primary key IDENTITY(1,1)
)
GO

create table prices
(
	id int NOT NULL primary key IDENTITY(1,1),
	city int NOT NULL foreign key references cities(id),
	product int NOT NULL foreign key references products(id),
	price int NOT NULL
)
GO

create table products_in_bag
(
	id int NOT NULL primary key IDENTITY(1,1),
	product int NOT NULL foreign key references products(id),
	quantity float NOT NULL,
)
GO

insert into cities (name)
values (N'Тверь'), (N'Липецк'), (N'Барнаул')

insert into products (name)
values (N'Пшеничный хлеб (батон)'),
	(N'Молоко (1 литр)'),
	(N'Картофель (1 кг)'),
	(N'Сыр (1 кг)'),
	(N'Мясо (говядина) (1 кг)'),
	(N'Подсолнечное масло (1 литр)')

insert into prices (city, product, price)
values
	(1, 1, 11),
	(1, 2, 26),
	(1, 3, 9),
	(1, 4, 240),
	(1, 5, 260),
	(1, 6, 38),

	(2, 1, 12),
	(2, 2, 23),
	(2, 3, 13),
	(2, 4, 215),
	(2, 5, 280),
	(2, 6, 44),

	(3, 1, 14),
	(3, 2, 25),
	(3, 3, 16),
	(3, 4, 260),
	(3, 5, 300),
	(3, 6, 50)

insert into products_in_bag (product, quantity)
values (1, 2), (3, 3), (5, 1.5), (6, 1)


IF OBJECT_ID (N'GetPrice', N'FN') IS NOT NULL
    DROP FUNCTION GetPrice;
GO

create function GetPrice(@City nvarchar(30), @ProductID int, @Quantity float)
returns float
as
begin
    declare @ans float;
	declare @cityId int;
	set @cityId = (select id from cities where name = @City)
    select @ans = price
    from prices
    where city = @CityID AND product = @ProductID;
    if (@ans IS NULL)
       set @ans = 0;
    return @ans * @Quantity;
end;
go


select sum(dbo.GetPrice(N'Тверь', product, quantity)) as Сумма
from products_in_bag






select product_name as 'Наименование продукта', [Тверь], [Липецк], [Барнаул]
from (select prdcts.name as product_name, cts.name, prcs.price
          from prices prcs
          join
          cities cts
          on cts.id = prcs.city
          join
          products prdcts
          on prdcts.id = prcs.product
) r
pivot
(
        max(price) for name in ([Тверь], [Липецк], [Барнаул])
) as pvt





--select
--	(select name from products where id = product) as Продукт,
--	quantity as Количество
--from products_in_bag