/*USE master
GO

IF DB_ID('EstateAgency') IS NOT NULL
CREATE DATABASE EstateAgency
GO

USE EstateAgency
*/

-- ------------------------------------------------------
-- Usuwanie tabel (w odwrotnej kolejnoci do tworzenia!)
-- ------------------------------------------------------

IF OBJECT_ID('Parking_slots','U') IS NOT NULL
    DROP TABLE  parking_slots
IF OBJECT_ID('Flats_low','U') IS NOT NULL
    DROP TABLE Flats_low
IF OBJECT_ID('Flats_high','U') IS NOT NULL
    DROP TABLE Flats_high


IF OBJECT_ID('Payments','U') IS NOT NULL
    DROP TABLE Payments

IF OBJECT_ID('Lease','U') IS NOT NULL
    DROP TABLE Lease

IF OBJECT_ID('Agents','U') IS NOT NULL
    DROP TABLE  Agents
IF OBJECT_ID('Renters','U') IS NOT NULL
    DROP TABLE  Renters
GO

-- --------------------------------
-- Tworzenie tabel
-- --------------------------------
IF OBJECT_ID('Renters','U') IS NULL -- If the table does not exist
CREATE TABLE Renters( -- Create table
    renter_ID VARCHAR(10) NOT NULL,
    first_Name VARCHAR(20),
    surname VARCHAR(20),
    born_on DATE,
    email VARCHAR(30),
    ID_card_number varchar(20) ,
        PRIMARY KEY (renter_ID),
 )

 IF OBJECT_ID('Agents','U') IS NULL -- If the table does not exist
CREATE TABLE Agents( -- Create table
    agent_ID VARCHAR(10) NOT NULL,
    first_Name VARCHAR(20),
    surname VARCHAR(20),
    born_on DATE,
    email VARCHAR(30),
    ID_card_number varchar(20) ,
        PRIMARY KEY (agent_ID),
 )

IF OBJECT_ID('Lease','U') IS NULL -- If the table does not exist
CREATE TABLE Lease( -- Create table
  flat_ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
  flat_standard VARCHAR(10),
  renter_ID VARCHAR(10),
  agent_ID VARCHAR(10),
  signing_date DATE,
  expiration_date DATE,
    CONSTRAINT FK_Renters FOREIGN KEY(renter_ID)
    REFERENCES Renters(renter_ID),
    CONSTRAINT FK_Agents FOREIGN KEY(agent_ID)
    REFERENCES Agents(agent_ID)

 )

IF OBJECT_ID('Payments','U') IS NULL -- If the table does not exist
CREATE TABLE Payments( -- Create table
    flat_ID INT IDENTITY(1,1) NOT NULL,
    rent_z³ INT,
    hire_value_z³ INT,
    total_z³ INT,
        PRIMARY KEY(flat_ID),
        FOREIGN KEY (flat_ID) REFERENCES Lease(flat_ID),
 )

 IF OBJECT_ID('Flats_low','U') IS NULL -- If the table does not exist
CREATE TABLE Flats_low( -- Create table
    flat_ID INT IDENTITY(1,1) NOT NULL,
    city VARCHAR(20),
    postal_code VARCHAR(10),
    street VARCHAR(40),
    building_number varchar(5),
    flat_number INT,
    gate_code VARCHAR(10),
        PRIMARY KEY (flat_ID),
        FOREIGN KEY (flat_ID) REFERENCES Lease(flat_ID),
 )
 IF OBJECT_ID('Flats_high','U') IS NULL -- If the table does not exist
    CREATE TABLE Flats_high( -- Create table
    flat_ID INT IDENTITY(1,1) NOT NULL,
    city VARCHAR(20),
    postal_code VARCHAR(10),
    street VARCHAR(40),
    building_number varchar(5),
    flat_number INT,
    gate_code VARCHAR(10),
        PRIMARY KEY (flat_ID),
        FOREIGN KEY (flat_ID) REFERENCES Lease(flat_ID),
 )
IF OBJECT_ID('Parking_slots','U') IS NULL -- If the table does not exist
CREATE TABLE Parking_slots( -- Create table
    flat_ID INT IDENTITY(1,1) NOT NULL,
    slot_ID VARCHAR(10),
    PRIMARY KEY (flat_ID),
    FOREIGN KEY (flat_ID) REFERENCES Lease(flat_ID),
 )
 GO

 -- ---------------------------------
-- Wstawianie wartoci do tabel
-- ---------------------------------

--Renters insert
INSERT INTO Renters ( renter_ID, first_Name,surname, born_on , email, ID_card_number   ) VALUES ( 'mackow97', 'Maciej',  'Kowalczyk' ,'1997-08-05', 'mackow97@gmail.com', 'CAS 43256');
INSERT INTO Renters ( renter_ID, first_Name,surname, born_on , email, ID_card_number   ) VALUES ( 'agnkoz95', 'Agnieszka',  'Kozio³' ,'1995-12-07', 'agnkoz95@wp.pl', 'CKL 69847');
INSERT INTO Renters ( renter_ID, first_Name,surname, born_on , email, ID_card_number   ) VALUES ( 'katnow68', 'Katarzyna',  'Nowak' ,'1968-11-13', 'katnowak68@gmail.com', 'KTR 78347');
INSERT INTO Renters ( renter_ID, first_Name,surname, born_on , email, ID_card_number   ) VALUES ( 'wojarc70', 'Wojciech',  'Arczyñski' ,'1970-01-09', 'wojciecharczyñski@interia.pl', 'CHG 48999');
INSERT INTO Renters ( renter_ID, first_Name,surname, born_on , email, ID_card_number   ) VALUES ( 'iwozgr00', 'Iwona',  'Zgorza³ek' ,'2000-04-07', 'iwonazgorzalek2001@gmail.com', 'CFG 40728');
INSERT INTO Renters ( renter_ID, first_Name,surname, born_on , email, ID_card_number   ) VALUES ( 'robkul83', 'Robert',  'Kula' ,'1983-09-27', 'rkula83@gmail.com', 'LGH 58369');
INSERT INTO Renters ( renter_ID, first_Name,surname, born_on , email, ID_card_number   ) VALUES ( 'marper99', 'Marcin',  'Perliñski' ,'1999-12-02', 'marcinperliñski1999@wp.pl', 'CAS 57398');
INSERT INTO Renters ( renter_ID, first_Name,surname, born_on , email, ID_card_number   ) VALUES ( 'anntat92', 'Anna',  'Tatoñ' ,'1992-03-15', 'annataton92@gmail.com', 'CTL 36829');
INSERT INTO Renters ( renter_ID, first_Name,surname, born_on , email, ID_card_number   ) VALUES ( 'domstê97', 'Dominik',  'Stêpieñ' ,'1997-04-28', 'dstêpieñ97@gmail.com', 'CND 34686');
INSERT INTO Renters ( renter_ID, first_Name,surname, born_on , email, ID_card_number   ) VALUES ( 'erysta87', 'Eryk',  'Stañski' ,'1987-05-30', 'erykstañski87@onet.pl', 'CTU 63875');


--Agents insert
INSERT INTO Agents ( agent_ID, first_Name,surname, born_on , email, ID_card_number ) VALUES ( 'krydmo90' , 'Krystian' , 'Dmochowski' , '1990-06-14', 'kdmochowski90@gmail.com', ' CAS 57392' );
INSERT INTO Agents ( agent_ID, first_Name,surname, born_on , email, ID_card_number ) VALUES ( 'karm³o88' , 'Karolina' , 'M³oda' , '1988-08-24', 'karolinam³oda88@wp.com', ' KBT 36252' );
INSERT INTO Agents ( agent_ID, first_Name,surname, born_on , email, ID_card_number ) VALUES ( 'barlap86' , 'Bartosz' , 'Lapis' , '1986-09-17', 'bartoszlapis86@gmail.com', ' CHT 84362' );

--Lease insert

INSERT INTO Lease (  flat_standard, renter_ID, agent_ID, signing_date, expiration_date ) VALUES ( 'HIGH', 'mackow97', 'barlap86', '2017-09-22', '2018-06-01' );
INSERT INTO Lease (  flat_standard, renter_ID, agent_ID, signing_date, expiration_date ) VALUES ( 'HIGH', 'agnkoz95', 'barlap86', '2018-04-22', '2018-10-01' );
INSERT INTO Lease (  flat_standard, renter_ID, agent_ID, signing_date, expiration_date ) VALUES ( 'LOW', 'katnow68', 'barlap86', '2017-04-01', '2018-06-01' );
INSERT INTO Lease (  flat_standard, renter_ID, agent_ID, signing_date, expiration_date ) VALUES ( 'HIGH', 'wojarc70', 'barlap86', '2018-01-01', '2018-05-20' );
INSERT INTO Lease (  flat_standard, renter_ID, agent_ID, signing_date, expiration_date ) VALUES ( 'HIGH', 'iwozgr00', 'barlap86', '2018-02-01', '2018-07-01' );
INSERT INTO Lease (  flat_standard, renter_ID, agent_ID, signing_date, expiration_date ) VALUES ( 'LOW', 'robkul83', 'karm³o88', '2018-03-01', '2018-10-01' );
INSERT INTO Lease (  flat_standard, renter_ID, agent_ID, signing_date, expiration_date ) VALUES ( 'LOW', 'marper99', 'karm³o88', '2017-10-01', '2018-08-01' );
INSERT INTO Lease (  flat_standard, renter_ID, agent_ID, signing_date, expiration_date ) VALUES ( 'LOW', 'anntat92', 'karm³o88', '2017-11-01', '2018-06-01' );
INSERT INTO Lease (  flat_standard, renter_ID, agent_ID, signing_date, expiration_date ) VALUES ( 'LOW', 'domstê97', 'krydmo90', '2017-07-01', '2018-07-01' );
INSERT INTO Lease (  flat_standard, renter_ID, agent_ID, signing_date, expiration_date ) VALUES ( 'HIGH', 'erysta87', 'krydmo90', '2017-12-01', '2018-12-01' );

--Payments insert
INSERT INTO Payments ( rent_z³, hire_value_z³, total_z³ ) VALUES ( 400, 1200, 1600 );
 INSERT INTO Payments ( rent_z³, hire_value_z³, total_z³) VALUES ( 500, 1400, 1900 );
 INSERT INTO Payments ( rent_z³, hire_value_z³, total_z³ ) VALUES ( 200, 600, 800 );
 INSERT INTO Payments ( rent_z³, hire_value_z³, total_z³ ) VALUES ( 600, 1500, 2100 );
 INSERT INTO Payments ( rent_z³, hire_value_z³, total_z³ ) VALUES ( 750, 2000, 2750 );
 INSERT INTO Payments ( rent_z³, hire_value_z³, total_z³ ) VALUES ( 300, 500, 800 );
 INSERT INTO Payments ( rent_z³, hire_value_z³, total_z³ ) VALUES ( 150, 500, 650 );
 INSERT INTO Payments ( rent_z³, hire_value_z³, total_z³ ) VALUES ( 250, 400, 650 );
 INSERT INTO Payments ( rent_z³, hire_value_z³, total_z³ ) VALUES ( 300, 400, 700 );
 INSERT INTO Payments ( rent_z³, hire_value_z³ ,total_z³ ) VALUES ( 800, 1700, 2500 );

 --Flats_low insert
 SET IDENTITY_INSERT Flats_low ON
 INSERT INTO Flats_low (flat_ID)
  SELECT flat_ID FROM Lease WHERE flat_standard='LOW';
  SET IDENTITY_INSERT Flats_low OFF
UPDATE Flats_low
    SET city = 'Szczecin' , postal_code='73-323' , street='Goliñskiego' , building_number='48A', flat_number=2, gate_code='482#482#'
WHERE flat_ID=3;
UPDATE Flats_low
    SET city = 'Poznañ' , postal_code='61-443' , street='Ró¿ana' , building_number='22C', flat_number=3, gate_code='223#223#'
WHERE flat_ID=6;
UPDATE Flats_low
    SET city = 'Poznañ' , postal_code='69-532' , street='Zuzanny' , building_number='3B', flat_number=1, gate_code='313#131#'
WHERE flat_ID=7;
UPDATE Flats_low
    SET city = 'Poznañ' , postal_code='66-654' , street='Wiejska' , building_number='14E', flat_number=1, gate_code='141#141#'
WHERE flat_ID=8;
UPDATE Flats_low
    SET city = 'Szczecin' , postal_code='71-642' , street='Lepka' , building_number='34D', flat_number=7, gate_code='347#347#'
WHERE flat_ID=9;

--Flats_high insert
 SET IDENTITY_INSERT Flats_high ON
 INSERT INTO Flats_high (flat_ID)
  SELECT flat_ID FROM Lease WHERE flat_standard='HIGH';
  SET IDENTITY_INSERT Flats_high OFF
  UPDATE Flats_high
    SET city = 'Szczecin' , postal_code='71-043' , street='Wojska Polskiego' , building_number='68A', flat_number=4, gate_code='684#684#'
WHERE flat_ID=1;
 UPDATE Flats_high
    SET city = 'Poznañ' , postal_code='65-779' , street='Naramowicka' , building_number='36F', flat_number=2, gate_code='362#362#'
WHERE flat_ID=2;
 UPDATE Flats_high
    SET city = 'Szczecin' , postal_code='71-089' , street='wiêtej Kingi' , building_number='16B', flat_number=8, gate_code='168#168#'
WHERE flat_ID=4;
 UPDATE Flats_high
    SET city = 'Szczecin' , postal_code='72-053' , street='Okulickiego' , building_number='98A', flat_number=7, gate_code='987#987#'
WHERE flat_ID=5;
 UPDATE Flats_high
    SET city = 'Szczecin' , postal_code='75-531' , street='Maciejowa' , building_number='21K', flat_number=2, gate_code='212#212'
WHERE flat_ID=10;

-- Parking_slots insert
SET IDENTITY_INSERT Parking_slots ON
 INSERT INTO Parking_slots (flat_ID)
 SELECT flat_ID FROM Lease
 SET IDENTITY_INSERT Parking_slots OFF
 UPDATE Parking_slots
    SET slot_ID='68A04'
WHERE flat_ID=1;
 UPDATE Parking_slots
    SET slot_ID='36F02'
WHERE flat_ID=2;
 UPDATE Parking_slots
    SET slot_ID='48A02'
WHERE flat_ID=3;
 UPDATE Parking_slots
    SET slot_ID='16B08'
WHERE flat_ID=4;
 UPDATE Parking_slots
    SET slot_ID='98A07'
WHERE flat_ID=5;
 UPDATE Parking_slots
    SET slot_ID='22C03'
WHERE flat_ID=6;
 UPDATE Parking_slots
    SET slot_ID='03B01'
WHERE flat_ID=7;
 UPDATE Parking_slots
    SET slot_ID='14E01'
WHERE flat_ID=8;
 UPDATE Parking_slots
    SET slot_ID='34D07'
WHERE flat_ID=9;
 UPDATE Parking_slots
    SET slot_ID='21K02'
WHERE flat_ID=10;



-- ---------------------------------
-- Views - creating
-- ---------------------------------
IF OBJECT_ID('Expiration_date','V') IS NOT NULL
    DROP VIEW Expiration_date
GO
CREATE VIEW Expiration_date AS (
    SELECT TOP (100) flat_ID, flat_standard,expiration_date FROM Lease
    ORDER BY expiration_date ASC
)
GO

IF OBJECT_ID('Rent_values','V') IS NOT NULL
    DROP VIEW rent_values
GO
CREATE VIEW Rent_values AS (
    SELECT TOP (100) * FROM Payments
    ORDER BY total_z³ ASC
)
GO

IF OBJECT_ID('Szczecin_flats','V') IS NOT NULL
    DROP VIEW Szczecin_flats
GO
CREATE VIEW Szczecin_flats AS (
    SELECT TOP (100) flat_ID,city,postal_code,street,building_number,flat_number FROM Flats_high
    WHERE city='Szczecin'
    UNION ALL
    SELECT TOP (100) flat_ID,city,postal_code,street,building_number,flat_number FROM Flats_low
    WHERE city='Szczecin'
)
GO

IF OBJECT_ID('Poznañ_flats','V') IS NOT NULL
    DROP VIEW Poznañ_flats
GO
CREATE VIEW Poznañ_flats AS (
    SELECT TOP (100) flat_ID,city,postal_code,street,building_number,flat_number FROM Flats_high
    WHERE city='Poznañ'
    UNION ALL
    SELECT TOP (100) flat_ID,city,postal_code,street,building_number,flat_number FROM Flats_low
    WHERE city='Poznañ'
)
GO






-- ---------------------------------
-- Procedures - creating
-- ---------------------------------
IF OBJECT_ID('Top_agents','P') IS NOT NULL
    DROP PROCEDURE Top_agents
GO
CREATE PROCEDURE Top_agents AS (
    SELECT  agent_ID, count(agent_ID) AS best_agents FROM Lease GROUP BY agent_ID
)
GO

IF OBJECT_ID('Count_flats','P') IS NOT NULL
    DROP PROCEDURE Count_flats
GO
CREATE PROCEDURE Count_flats AS
    SELECT a.City,SUM(a.flat_numbers) AS flat_numbers FROM (
        SELECT  city,count(City) AS flat_numbers FROM Flats_high GROUP BY city
        UNION ALL
        SELECT  city,count(City) AS flat_numbers FROM Flats_low GROUP BY city )  a
    GROUP BY a.City

GO


    --Inserting new renter
GO
IF OBJECT_ID('Insert_renter','P') IS NOT NULL
    DROP PROCEDURE Insert_renter
GO
CREATE PROCEDURE Insert_renter
    @renter_ID VARCHAR(10),
    @first_Name VARCHAR(20),
    @surname VARCHAR(20),
    @born_on DATE,
    @email VARCHAR(30),
    @ID_card_number varchar(20)
    AS
    SET NOCOUNT ON
    INSERT INTO Renters ( renter_ID, first_Name,surname, born_on , email, ID_card_number   ) VALUES ( @renter_ID, @first_Name,@surname, @born_on , @email, @ID_card_number)
    GO

    --Inserting new agent
IF OBJECT_ID('Insert_agent','P') IS NOT NULL
    DROP PROCEDURE Insert_agent
GO
CREATE PROCEDURE Insert_agent
    @agent_ID VARCHAR(10),
    @first_Name VARCHAR(20),
    @surname VARCHAR(20),
    @born_on DATE,
    @email VARCHAR(30),
    @ID_card_number varchar(20)
    AS
    SET NOCOUNT ON
    INSERT INTO Agents( agent_ID, first_Name,surname, born_on , email, ID_card_number   ) VALUES ( @agent_ID, @first_Name,@surname, @born_on , @email, @ID_card_number)
    GO



-- ---------------------------------------------
-- Reports - creating
-- ---------------------------------------------

SELECT * FROM Expiration_date
SELECT * FROM Rent_values
SELECT * FROM Szczecin_flats
SELECT * FROM Poznañ_flats
EXEC Count_flats
EXECUTE Insert_renter @renter_ID='markow77',@first_Name='Marek',@surname='Kownacki',@born_on='1977-04-29',@email='markow77@gmail.com',@ID_card_number='CTL 31284'
GO
EXECUTE Insert_agent @agent_ID='³uklu87',@first_Name='£ukasz',@surname='lusek',@born_on='1987-11-23',@email='³lusek87@interia.pl',@ID_card_number='CJK 62434'
EXEC Top_agents

 -- --------------------------------
 SELECT * FROM Lease
 SELECT * FROM Payments
 SELECT * FROM Flats_low
 SELECT * FROM Flats_high
 SELECT * FROM Parking_slots
 SELECT * FROM Renters
 SELECT * FROM Agents


 -- ---------------------------------------------
-- Database - deleting
-- Comment this script part, if you run the script on the faculty MSSQL server.
-- ---------------------------------------------
/*USE master
GO

IF DB_ID('EstateAgency') IS NOT NULL
DROP DATABASE EstateAgency
GO
*/