---

/* ==========================================
   ERD: ХУДОЖЕСТВЕННАЯ ГАЛЕРЕЯ
   Только создание таблиц внутри текущей БД
   ========================================== */


/* 1. Художники */
CREATE TABLE Artists44
(
    ArtistID INT IDENTITY(1,1) PRIMARY KEY,
    FullName NVARCHAR(150) NOT NULL,
    BirthDate DATE NULL,
    Country NVARCHAR(100) NULL,
    Biography NVARCHAR(500) NULL
);
GO


/* 2. Жанры */
CREATE TABLE Genres44
(
    GenreID INT IDENTITY(1,1) PRIMARY KEY,
    GenreName NVARCHAR(100) NOT NULL
);
GO


/* 3. Произведения */
CREATE TABLE Artworks44
(
    ArtworkID INT IDENTITY(1,1) PRIMARY KEY,
    ArtistID INT NOT NULL,
    GenreID INT NOT NULL,

    Title NVARCHAR(150) NOT NULL,
    CreationYear INT NULL,
    Price DECIMAL(12,2) NULL,
    Status NVARCHAR(50) NOT NULL DEFAULT N'В галерее',

    CONSTRAINT FK_Artworks44_Artists44
        FOREIGN KEY (ArtistID)
        REFERENCES Artists44(ArtistID),

    CONSTRAINT FK_Artworks44_Genres44
        FOREIGN KEY (GenreID)
        REFERENCES Genres44(GenreID)
);
GO


/* 4. Выставки */
CREATE TABLE Exhibitions44
(
    ExhibitionID INT IDENTITY(1,1) PRIMARY KEY,
    ExhibitionName NVARCHAR(150) NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL,
    Description NVARCHAR(500) NULL
);
GO


/* 5. Связь выставок и произведений */
CREATE TABLE ExhibitionArtworks44
(
    ExhibitionID INT NOT NULL,
    ArtworkID INT NOT NULL,

    CONSTRAINT PK_ExhibitionArtworks44
        PRIMARY KEY (ExhibitionID, ArtworkID),

    CONSTRAINT FK_ExhibitionArtworks44_Exhibitions44
        FOREIGN KEY (ExhibitionID)
        REFERENCES Exhibitions44(ExhibitionID),

    CONSTRAINT FK_ExhibitionArtworks44_Artworks44
        FOREIGN KEY (ArtworkID)
        REFERENCES Artworks44(ArtworkID)
);
GO


/* 6. Посетители */
CREATE TABLE Visitors44
(
    VisitorID INT IDENTITY(1,1) PRIMARY KEY,
    FullName NVARCHAR(150) NOT NULL,
    Phone NVARCHAR(30) NULL,
    Email NVARCHAR(120) NULL
);
GO


/* 7. Билеты */
CREATE TABLE Tickets44
(
    TicketID INT IDENTITY(1,1) PRIMARY KEY,
    VisitorID INT NOT NULL,
    ExhibitionID INT NOT NULL,

    VisitDate DATE NOT NULL,
    TicketPrice DECIMAL(10,2) NOT NULL,

    CONSTRAINT FK_Tickets44_Visitors44
        FOREIGN KEY (VisitorID)
        REFERENCES Visitors44(VisitorID),

    CONSTRAINT FK_Tickets44_Exhibitions44
        FOREIGN KEY (ExhibitionID)
        REFERENCES Exhibitions44(ExhibitionID)
);
GO