drop DATABASE proiect1;
CREATE DATABASE proiect13;
CREATE TABLE Asociatie(
    id INT NOT NULL PRIMARY KEY,
    nume VARCHAR(255) NOT NULL UNIQUE,
    administrator VARCHAR(255) NOT NULL,
    presedinte VARCHAR(255) NOT NULL,
    oras VARCHAR(255) NOT NULL,
    iban VARCHAR(25) NOT NULL UNIQUE,
    CONSTRAINT chk_iban CHECK (DATALENGTH([iban]) = 24)
)

CREATE TABLE Bloc(
    id INT NOT NULL PRIMARY KEY,
    id_asociatie INT FOREIGN KEY REFERENCES Asociatie(id),
    strada VARCHAR(255) NOT NULL,
    nr VARCHAR(11) NOT NULL,
    etaje INT NOT NULL,
    apartamente INT NOT NULL,
    CONSTRAINT chk_etaje CHECK (etaje >= 0),
    CONSTRAINT chk_apartamente CHECK (apartamente >=0)
)

CREATE TABLE Proprietar(
    id INT NOT NULL PRIMARY KEY,
    nume VARCHAR(255) NOT NULL,
    prenume VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    cnp VARCHAR(14) NOT NULL UNIQUE
    CONSTRAINT chk_cnp CHECK (DATALENGTH([cnp]) = 13)
)

CREATE TABLE Apartament(
    id INT NOT NULL PRIMARY KEY,
    id_bloc INT FOREIGN KEY REFERENCES Bloc(id),
    id_proprietar INT FOREIGN KEY REFERENCES Proprietar(id),
    nr INT NOT NULL,
    etaj INT NOT NULL,
    suprafata INT NOT NULL,
    persoane INT NOT NULL,
    CONSTRAINT chk_nr CHECK (nr > 0),
    CONSTRAINT chk_etaj CHECK (etaj >= 0),
    CONSTRAINT chk_suprafata CHECK (suprafata > 0),
    CONSTRAINT chk_persoane CHECK (persoane >= 0)
)

CREATE TABLE Consum(
    id INT NOT NULL PRIMARY KEY,
    id_apartament INT FOREIGN KEY REFERENCES Apartament(id),
    gaz INT NOT NULL,
    apa_calda INT NOT NULL,
    apa_rece INT NOT NULL,
    luna CHAR(10) NOT NULL,
    an INT NOT NULL,
    CONSTRAINT chk_gaz CHECK (gaz >= 0),
    CONSTRAINT chk_apa_calda CHECK (apa_calda >= 0),
    CONSTRAINT chk_apa_rece CHECK (apa_rece >= 0),
    CONSTRAINT chk_luna_consum CHECK (luna in ('Ianuarie','Februarie','Martie','Aprilie','Mai','Iunie','Iulie','August','Septembrie','Octombrie','Noiembrie','Decembrie')),
    CONSTRAINT chk_an_consum CHECK (an >= 2010)
)

CREATE TABLE Cheltuieli(
    id INT NOT NULL PRIMARY KEY,
    id_apartament INT FOREIGN KEY REFERENCES Apartament(id),
    luna CHAR(10) NOT NULL,
    an INT NOT NULL,
    total INT NOT NULL,
    platit BIT NOT NULL,
    CONSTRAINT chk_luna CHECK (luna in ('Ianuarie','Februarie','Martie','Aprilie','Mai','Iunie','Iulie','August','Septembrie','Octombrie','Noiembrie','Decembrie')),
    CONSTRAINT chk_an CHECK (an >= 2010),
    CONSTRAINT chk_total CHECK (total >= 0)
)

CREATE TABLE Servicii(
    id INT NOT NULL PRIMARY KEY,
    id_asociatie INT FOREIGN KEY REFERENCES Asociatie(id) UNIQUE,
    apa VARCHAR(255) NOT NULL,
    energie VARCHAR(255) NOT NULL,
    gaz VARCHAR(255) NOT NULL,
    salubritate VARCHAR(255) NOT NULL
)

CREATE TABLE Parcare(
    id INT NOT NULL PRIMARY KEY,
    id_apartament INT FOREIGN KEY REFERENCES Apartament(id),
    nr INT NOT NULL
)

 -- Creates the login test_user with password 'test'.
CREATE LOGIN radu WITH PASSWORD = 'Test12345';
-- Creates a database user for the login created above.
CREATE USER radu FOR LOGIN radu;
-- Create roles
ALTER role db_owner ADD MEMBER radu;

CREATE LOGIN ilie WITH PASSWORD = 'Test12345';
CREATE USER ilie FOR LOGIN ilie;
ALTER role db_securityadmin ADD MEMBER ilie;

CREATE LOGIN andrei WITH PASSWORD = 'Test12345';
CREATE USER andrei FOR LOGIN andrei;
ALTER role db_datareader ADD MEMBER andrei;

SELECT * FROM Asociatie;
INSERT INTO Asociatie VALUES
    --(1,'a','b','c','d','RO49AAAA1B31007593840000');
    (1, 'Asociatia Strada Plopilor nr 2','Andrei Popescu','Bogdan Marchis','Cluj','RO49AAAA1B31007593840000'),
    (2, 'Asociatia Strada Pliesti nr 17','Bogdan Tanase','Ioan Manole','Cluj','RO49AAAA1B31007593844343'),
    (3, 'Asociatia Strada Lalelelor nr 4','Bogdan Schim','Catalin Ves','Bistrita','RO49AAAA1B31007593833545'),
    (4, 'Asociatia Strada Eroilor nr 11','Cristi Tomescu','Adi Circu','Bistrita','RO49AAAA1C31007593848434'),
    (5, 'Asociatia Strada Fabricii nr 34','Tudor Vlad','Mihai Pasca','Bistrita','RO49ABTA1B31007593845445'),
    (6, 'Asociatia Strada Replubicii nr 8','Marius Pavel','Luci Pinti','Cluj','RO49BTLRO1B3100759384000');

INSERT INTO Bloc VALUES
    (1, 1, 'Plopilor','nr 2A',4, 20),
    (2, 1, 'Plopilor','nr 2B',6, 30),
    (3, 2, 'Ploiesti','nr 17',10, 40),
    (4, 2, 'Ploiesti','nr 17C',5, 60),
    (5, 3, 'Eroilor','nr 1',2, 8),
    (6, 4, 'Fabricii','nr 34',3, 30),
    (7, 5, 'Republicii','nr 8',4, 20),
    (8, 5, 'Republicii','nr 9',4, 24);

INSERT INTO Proprietar VALUES
    (1, 'Andrei','Andrei','andrei@gmail.com','1900627103482'),
    (2, 'Marcus','Cristi','cristi@gmail.com','1890722063851'),
    (3, 'Paltis','Marius','marius@yahoo.com','1970517410013'),
    (4, 'Mimo','Bogdan','bogdan@gmail.com','1890503216748'),
    (5, 'Cosmo','Marcel','marcel@yahoo.com','1690722292645');

INSERT INTO Apartament VALUES
    (1, 2, 1, 27, 5, 54, 2),
    (2, 1, 3, 4, 1, 35, 1),
    (3, 3, 4, 12, 4, 44, 1),
    (4, 2, 1, 1, 1, 62, 2),
    (5, 4, 2, 5, 1, 89, 3),
    (6, 2, 2, 12, 3, 78, 3),
    (7, 3, 5, 10, 3, 56, 3),
    (8, 1, 1, 14, 3, 78, 2),
    (9, 5, 5, 2, 1, 102, 5),
    (10, 6, 2, 20, 4, 96, 3),
    (11, 1, 2, 14, 3, 46, 2),
    (12, 3, 3, 16, 4, 26, 1),
    (13, 7, 5, 12, 3, 44, 2),
    (14, 8, 5, 24, 4, 50, 2),
    (15, 5, 4, 8, 4, 63, 3),
    (16, 8, 2, 3, 1, 51, 2);


INSERT INTO Consum VALUES
    (1, 1, 5, 4, 2, 'Decembrie',2018),
    (2, 1, 3, 2, 4, 'Noiembrie',2018),
    (3, 1, 3, 3, 5, 'Octombrie',2018),
    (4, 2, 7, 1, 7, 'Decembrie',2018),
    (5, 2, 5, 6, 8, 'Noiembrie',2018),
    (6, 2, 5, 7, 3, 'Octombrie',2018),
    (7, 3, 3, 3, 5, 'Decembrie',2018),
    (8, 3, 2, 9, 6, 'Noiembrie',2018),
    (9, 3, 1, 2, 6, 'Octombrie',2018),
    (10, 4, 5, 7, 7, 'Decembrie',2018),
    (11, 4, 2, 6, 4, 'Noiembrie',2018),
    (12, 4, 4, 6, 1, 'Octombrie',2018),
    (13, 5, 8, 4, 3, 'Decembrie',2018),
    (14, 5, 7, 2, 7, 'Noiembrie',2018),
    (15, 5, 6, 3, 8, 'Octombrie',2018),
    (16, 6, 6, 8, 5, 'Decembrie',2018),
    (17, 6, 5, 5, 6, 'Noiembrie',2018),
    (18, 6, 3, 3, 9, 'Octombrie',2018),
    (19, 7, 5, 6, 2, 'Decembrie',2018),
    (20, 8, 8, 7, 2, 'Decembrie',2018);


INSERT INTO Cheltuieli VALUES
    (1, 1, 'Decembrie',2018, 234,1),
    (2, 1, 'Noiembrie',2018, 143,1),
    (3, 1, 'Octombrie',2018, 213,1),
    (4, 2, 'Decembrie',2018, 187,0),
    (5, 2, 'Noiembrie',2018, 342,0),
    (6, 2, 'Octombrie',2018, 98,1),
    (7, 3, 'Decembrie',2018,341,1),
    (8, 3, 'Noiembrie',2018,123,1),
    (9, 3, 'Octombrie',2018,238,1),
    (10, 4, 'Decembrie',2018,276,0),
    (11, 4, 'Noiembrie',2018,167,1),
    (12, 4, 'Octombrie',2018,145,1),
    (13, 5, 'Decembrie',2018,190,1),
    (14, 5, 'Noiembrie',2018,302,1),
    (15, 5, 'Octombrie',2018,129,1),
    (16, 6, 'Decembrie',2018,264,0),
    (17, 6, 'Noiembrie',2018,175,1),
    (18, 6, 'Octombrie',2018,154,1),
    (19, 7, 'Decembrie',2018,187,1),
    (20, 8, 'Decembrie',2018,292,1);

INSERT INTO Parcare VALUES
    (1, 1, 23),
    (2, 2, 10),
    (3, 3, 2),
    (4, 3, 7),
    (5, 4, 20),
    (6, 3, 5),
    (7, 6, 24),
    (8, 6, 9),
    (9, 7, 33),
    (10, 5, 21);

INSERT INTO Servicii VALUES
    (1,1,'Aquabis','Electrica', 'Eon','Rosal'),
    (2,2,'Aquabis','Electrica', 'Eon','Rosal'),
    (3,3,'Aquabis','Electrica', 'Eon','Rosal'),
    (4,4,'Aquabis','Electrica', 'Eon','Rosal'),
    (5,5,'Aquabis','Electrica', 'Eon','Rosal');


CREATE VIEW [Proprietari care au mai multe apartamente] AS
SELECT p.Nume, p.Prenume
FROM Proprietar as p
INNER JOIN Apartament as a
ON p.id=a.id_proprietar
GROUP BY P.ID
HAVING(a.id_proprietar) > 1;