-- A5

-- brands:cars; 1 brands:N cars; 1 cars:1 brands => 1:N
-- brands:extras; NO RELATION
-- cars:extras; 1 cars:N extras; 1 extras:N cars => N:M
-- brands, cars, extras, conn_cars_extras
-- references: cars->brands, conn->cars, conn->extras
-- CREATE: brands, cars, extras, conn
-- DROP: conn, extras, cars, brands

IF object_id('conn_cars_extras', 'U') is not null DROP TABLE conn_cars_extras;
IF object_id('extras', 'U') is not null DROP TABLE extras;
IF object_id('cars', 'U') is not null DROP TABLE cars;
IF object_id('brands', 'U') is not null DROP TABLE brands;
GO

CREATE TABLE brands (
	brand_id int primary key,
	brand_name nvarchar(100) -- +column constraints
	-- table constraints
	-- CONSTRAINT pk_brand PRIMARY KEY (brand_id, X)
);
CREATE TABLE cars (
	car_id int primary key,
	car_brand int NOT NULL references brands(brand_id),
	car_model nvarchar(100) NOT NULL,
	car_baseprice int NOT NULL
	-- CONSTRAINT fk_car_brand FOREIGN KEY (car_brand, X) REFERENCES brands(brand_id, Y)
);
CREATE TABLE extras (
	ext_id int primary key,
	ext_name nvarchar(100),
	ext_price int,
	CONSTRAINT chk_price CHECK (ext_price > 1000)
);
CREATE TABLE conn_cars_extras (
	conn_id int identity primary key,
	conn_car int references cars(car_id),
	conn_extra int references extras(ext_id),
	CONSTRAINT uq_conn_cars_extras UNIQUE (conn_car, conn_extra)
);
-- IDENTITY_INSERT
GO

INSERT INTO brands VALUES (1, 'BMW');
INSERT INTO brands VALUES (2, 'Audi');
INSERT INTO brands VALUES (3, 'Suzuki');

INSERT INTO cars VALUES (1, 1, '116d', 30000);
INSERT INTO cars VALUES (2, 1, 'X5', 60000);
INSERT INTO cars VALUES (3, 1, 'i8', 80000);
INSERT INTO cars VALUES (4, 2, 'A2', 40000);
INSERT INTO cars VALUES (5, 2, 'S4', 50000);
INSERT INTO cars VALUES (6, 3, 'Swift', 10000);
-- 3 brands, 6 cars, 6 extras ....
INSERT INTO extras VALUES (1, 'Sunroof', 2000),
							(2, 'Turbo engine', 6000),
							(3, 'Leather seats', 4000),
							(4, 'GPS Tracking', 1100),
							(5, 'Sports gearbox', 5000),
							(6, 'Super Insurance', 1001);
-- Swift => without extras, average number of extras => float
INSERT INTO conn_cars_extras (conn_car, conn_extra) VALUES 
	(1, 1), (1, 3), (1, 5), (2, 4), (3, 6), (4, 3), (4, 4), (5, 5);
GO
/*
-- P3
SELECT brand_name, car_model, car_baseprice,
	isnull(ext_name, '= NO EXTRA =') as extra_name,
	isnull(convert(varchar, ext_price), '= NO EXTRA =') as extra_price
FROM cars 
	INNER JOIN brands ON (car_brand = brand_id)
	LEFT JOIN conn_cars_extras ON (conn_car = car_id)
	LEFT JOIN extras ON (conn_extra = ext_id);
-- decomposition, normalization
-- big table (redundancy+anomalies) => smaller tables (consistency)
*/

BEGIN tran;

PRINT 'BEFORE UPDATE';

UPDATE extras 
	SET ext_price = 2*ext_price
	WHERE ext_price = (SELECT MIN(ext_price) FROM extras); 

UPDATE extras 
    SET ext_price = 2*ext_price 
    FROM extras INNER JOIN (SELECT min(ext_price) as minprice FROM extras) sub ON (extras.ext_price = minprice);

PRINT 'AFTER UPDATE';

DELETE FROM conn_cars_extras
	WHERE conn_car IN ( -- NOT =
		SELECT car_id
		FROM cars
		WHERE car_baseprice = (SELECT max(car_baseprice) FROM cars)
	);

DELETE FROM cars
	WHERE car_baseprice = (SELECT max(car_baseprice) FROM cars); 



-- SELECT * FROM cars; -- acId = Isolated
ROLLBACK;
-- SELECT * FROM cars;

/*
UPDATE extras 
SET ext_price = 2*ext_price
FROM extras, (SELECT MIN(ext_price) as minPrice FROM extras) subQ
WHERE ext_price = minPrice;
*/


-- P2/a
-- view: cheapCars
IF object_id('cheapCars', 'V') is not null DROP VIEW cheapCars;
IF object_id('carExtrasPrices', 'V') is not null DROP VIEW carExtrasPrices;
IF object_id('carTotalPrices', 'V') is not null DROP VIEW carTotalPrices;
GO

/*
CREATE VIEW cheapCars AS 
	SELECT brand_name, car_model, car_baseprice
	FROM cars INNER JOIN brands ON (car_brand=brand_id)
	WHERE car_baseprice <= (SELECT avg(car_baseprice) FROM cars);
*/
CREATE VIEW cheapCars AS 
	SELECT brand_name, car_model, car_baseprice
	FROM cars INNER JOIN brands ON (car_brand=brand_id), 
		(SELECT avg(car_baseprice) as avgCarPrice FROM cars) sub
	WHERE car_baseprice <= avgCarPrice;
GO
-- SELECT * FROM cheapCars;

-- P2/b
-- views: carExtrasPrices, carTotalPrices
IF object_id('carExtrasPrices', 'V') is not null DROP VIEW carExtrasPrices;
IF object_id('carTotalPrices', 'V') is not null DROP VIEW carTotalPrices;
GO
CREATE VIEW carExtrasPrices AS 
	SELECT conn_car, sum(ext_price) as sumExtras
	FROM extras INNER JOIN conn_cars_extras ON (ext_id = conn_extra)
	GROUP BY conn_car;
GO
-- SELECT * FROM carExtrasPrices;
CREATE VIEW carTotalPrices AS
	SELECT cars.*, car_baseprice+isnull(sumExtras, 0) as car_totalprice
	FROM cars LEFT JOIN carExtrasPrices ON (car_id=conn_car);
GO
-- NEVER use * in views => column list saved in view metadata
-- SELECT * FROM carTotalPrices;
-- GO

-- P2/cd
-- views: extrasPerCar, extrasPerBrand, fancyCars
IF object_id('extrasPerCar', 'V') is not null DROP VIEW extrasPerCar;
IF object_id('extrasPerBrand', 'V') is not null DROP VIEW extrasPerBrand;
IF object_id('fancyCars', 'V') is not null DROP VIEW fancyCars;
GO
CREATE VIEW extrasPerCar AS
	SELECT conn_car, count(1) as cntExtras
	FROM conn_cars_extras
	GROUP BY conn_car;
GO -- SELECT * FROM extrasPerCar;
CREATE VIEW extrasPerBrand AS
	SELECT car_brand as brandId, avg(convert(float, isnull(cntExtras, 0))) as cntBrandExtras
	FROM cars LEFT JOIN extrasPerCar ON (car_id=conn_car)
	GROUP BY car_brand;
GO -- SELECT * FROM extrasPerBrand;
CREATE VIEW fancyCars AS
	SELECT car_id
	FROM cars 
		LEFT JOIN extrasPerCar ON (car_id=conn_car)
		INNER JOIN extrasPerBrand ON (car_brand=brandId)
	WHERE isnull(cntExtras, 0) >= cntBrandExtras;
GO
-- SELECT * FROM fancyCars;
ALTER TABLE cars ADD car_isfancy tinyint; -- bit?
GO
UPDATE cars SET car_isfancy = 0;

UPDATE cars SET car_isfancy = 1
WHERE car_id IN (select * from fancyCars);

UPDATE cars 
SET car_isfancy = 1
FROM cars INNER JOIN fancyCars ON (cars.car_id=fancyCars.car_id);

-- SELECT * FROM cars;

-- NEVER use * in views
-- 1. WITH SCHEMABINDING
-- 2. DROP + CREATE VIEW -> NEVER
-- 3. EXEC sp_refreshview viewName;
-- 4. sys.objects + sys.sql_expression_dependencies + recursive CTE + dynamic sql

ALTER TABLE cars ADD car_total int;
GO
EXEC sp_refreshview carTotalPrices;
-- SELECT * FROM carTotalPrices;

UPDATE cars
SET car_total = car_totalprice
FROM cars INNER JOIN carTotalPrices ON (cars.car_id = carTotalPrices.car_id);

SELECT * FROM cars;

-- fancyCars, carTotalPrices => VIEWS => ALWAYS return up-to-date data
-- car_isfancy, car_total => COLUMNS => NOT AUTOMATICALLY up-to-date
-- [TotalDue]  AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))),
-- Trigger