DROP TABLE IF EXISTS ARTWORK CASCADE;
DROP TABLE IF EXISTS TYPEOFWORK CASCADE;
DROP TABLE IF EXISTS ARTIST CASCADE;
DROP TABLE IF EXISTS AUCTION CASCADE;
DROP TABLE IF EXISTS AUCTIONHOUSE CASCADE;
DROP TABLE IF EXISTS INCLUDED CASCADE;

-- Table: AUCTIONHOUSE
CREATE TABLE AUCTIONHOUSE (
	AuctionHouseID TEXT NOT NULL,
	Name TEXT NOT NULL UNIQUE,
	Address TEXT,
	Website TEXT,
	primary key (AuctionHouseID)

);

-- Table: AUCTION
CREATE TABLE AUCTION (
	AuctionID TEXT NOT NULL,
	Name TEXT NOT NULL UNIQUE,
	Overview TEXT,
	Date TEXT NOT NULL,
	Location TEXT,
	WebLink TEXT,
	AuctionHouseID TEXT references AUCTIONHOUSE(AuctionHouseID) ON DELETE CASCADE,
	primary key(AuctionID)
);

-- Table: ARTIST
CREATE TABLE ARTIST (
	ArtistID TEXT NOT NULL,
	Name TEXT NOT NULL,
	Nationality TEXT,
	YearOfBirth TEXT NOT NULL,
	YearOfDeath TEXT,
	ShortCV TEXT,
	WebLink TEXT,
    primary key (ArtistID)
);

-- Table: TYPEOFWORK
CREATE TABLE TYPEOFWORK (
	TypeID TEXT NOT NULL,
	Name TEXT NOT NULL UNIQUE,
	WebLink TEXT,
	primary key (TypeID)
);

-- Table: ARTWORK
CREATE TABLE ARTWORK (
	ArtWorkID TEXT NOT NULL,
	Name TEXT NOT NULL,
	Description TEXT,
	YearOfCreation TEXT,
	Photo TEXT,
	ArtistID TEXT references ARTIST(ArtistID) ON DELETE CASCADE,
	TypeID TEXT references TYPEOFWORK(TypeID) ON DELETE CASCADE,
	primary key (ArtWorkID)
);

CREATE TABLE INCLUDED (
	ArtWorkID TEXT references AUCTION(AuctionID) ON DELETE CASCADE,
	AuctionID TEXT references ARTWORK(ArtWorkID) ON DELETE CASCADE,
	ValuedFrom INT,
	ValuedTo INT,
	SoldFor INT,
	primary key(ArtWorkID,AuctionID)

);


select Name, YearOfBirth from ARTIST where YearOfDeath IS NULL;\
select a.Name from ARTWORK a, TYPEOFWORK t where a.TypeID = t.TypeID and t.Name='oil on panel';
select artist.Name from ARTIST artist, ARTWORK a, TYPEOFWORK t where a.TypeID = t.TypeID and t.Name='metal object' and artist.ArtistID=a.ArtistID;