USE KN301_Ilinykh;

go
DROP TABLE ProductPrices;
DROP TABLE Cart;
DROP TABLE Product;
DROP TABLE Cities;
DROP FUNCTION GetCartPrice;
DROP FUNCTION GetPricesInCities;
DROP FUNCTION IsEnough;
DROP FUNCTION IsPositive;

go
CREATE TABLE Product(
    id int IDENTITY(1,1) primary key,
    name nvarchar(80)
)

CREATE TABLE Cities(
    id int IDENTITY(1,1) primary key,
    name nvarchar(80)
)

CREATE TABLE ProductPrices(
    id int IDENTITY(1,1) primary key,
    productId int foreign key references Product(id),
    cityId int foreign key references Cities(id),
    avgPrice money
)

CREATE TABLE Cart(
    id int IDENTITY(1,1) primary key,
    productId int foreign key references Product(id),
    amount float
)

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

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

go
insert into ProductPrices values
    (1, 1, 11),
    (1, 2, 12),
    (1, 3, 14),

    (2, 1, 26),
    (2, 2, 23),
    (2, 3, 25),

    (3, 1, 9),
    (3, 2, 13),
    (3, 3, 16),

    (4, 1, 240),
    (4, 2, 215),
    (4, 3, 260),

    (5, 1, 260),
    (5, 2, 280),
    (5, 3, 300),

    (6, 1, 38),
    (6, 2, 44),
    (6, 3, 50)

go
insert into Cart values
    (1, 2),
    (3, 3),
    (5, 1.5),
    (6, 1);

go
CREATE FUNCTION GetCartPrice(@city nvarchar(80)) RETURNS FLOAT
BEGIN
    DECLARE @cityId int;
    SELECT @cityId = Cities.id
    FROM Cities
    WHERE Cities.name = @city

    DECLARE @price int;

    SELECT @price = SUM(ProductPrices.avgPrice * Cart.amount) FROM Cart
        JOIN Product ON Cart.productId = Product.id
        JOIN ProductPrices ON Cart.productId = ProductPrices.productId WHERE ProductPrices.cityId = @cityId;

    RETURN @price;
END;

go
CREATE FUNCTION GetPricesInCities(@product nvarchar(80))
RETURNS @rtnTable TABLE
(
    Name nvarchar(255),
    avgPrices money,
    DiffWithMaxPrice money,
    DiffWithMinPrice money
)
BEGIN
    DECLARE @maxPrice money;
    SELECT @maxPrice = Max(ProductPrices.avgPrice)  FROM ProductPrices
    JOIN Cities ON Cities.id = ProductPrices.cityId
    JOIN Product ON ProductPrices.productId = Product.id WHERE Product.name = @product

    DECLARE @minPrice money;
    SELECT @minPrice = Min(ProductPrices.avgPrice)  FROM ProductPrices
    JOIN Cities ON Cities.id = ProductPrices.cityId
    JOIN Product ON ProductPrices.productId = Product.id WHERE Product.name = @product

    insert into @rtnTable
    SELECT Cities.name, ProductPrices.avgPrice, ProductPrices.avgPrice - @maxPrice, ProductPrices.avgPrice - @minPrice FROM ProductPrices
    JOIN Cities ON Cities.id = ProductPrices.cityId
    JOIN Product ON ProductPrices.productId = Product.id WHERE Product.name = @product
    RETURN
END

go
CREATE FUNCTION IsPositive(@money money) RETURNS nvarchar(3)
BEGIN
    IF @money >= 0
        return 'YES'
    return 'NO'
END;

go
CREATE FUNCTION IsEnough(@moneyAmount money)
RETURNS @rtnTable TABLE
(
    city nvarchar(255),
    isEnough nvarchar(3),
    moneyDiff money
)
BEGIN
    insert into @rtnTable
    SELECT Cities.name, dbo.IsPositive(@moneyAmount - dbo.GetCartPrice(Cities.name)), @moneyAmount - dbo.GetCartPrice(Cities.name) FROM Cities
    RETURN
END

go


select dbo.GetCartPrice(N'Тверь');
select * from dbo.GetPricesInCities(N'Молоко');
select * from dbo.IsEnough(500);
drop FUNCTION CitiesStr
go

CREATE FUNCTION CitiesStr() RETURNS nvarchar(255)
BEGIN
    DECLARE @str nvarchar(255)
    set @str = ''
    select @str=@str+'['+name+'], ' FROM Cities

    return SUBSTRING(@str, 1, LEN(@str)-1)
end;
go
print dbo.CitiesStr()

DECLARE @str2 nvarchar(355)
set  @str2='select * from (select Product.name as [Наименование продукта], Cities.name, avgPrice from ProductPrices
    JOIN Product ON ProductPrices.productId = Product.id
    JOIN Cities ON Cities.id = ProductPrices.cityId ) p
pivot (avg(avgPrice) for p.name in ('+dbo.CitiesStr()+')) as pvt'

exec(@str2)