---

-- Удаляем старые таблицы, если они уже существуют

DROP TABLE IF EXISTS BookIssues;
DROP TABLE IF EXISTS BookKnowledgeAreas;
DROP TABLE IF EXISTS BookCopies;
DROP TABLE IF EXISTS Books;
DROP TABLE IF EXISTS KnowledgeAreas;
DROP TABLE IF EXISTS Publishers;
DROP TABLE IF EXISTS Readers;


-- Читатели
CREATE TABLE Readers (
    id_reader INT PRIMARY KEY IDENTITY(1,1),
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),
    home_address VARCHAR(150) NOT NULL,
    work_phone VARCHAR(20),
    home_phone VARCHAR(20),
    birth_date DATE NOT NULL,
    ticket_number INT NOT NULL UNIQUE,

    CHECK (work_phone IS NOT NULL OR home_phone IS NOT NULL)
);


-- Области знаний
CREATE TABLE KnowledgeAreas (
    id_knowledge_area INT PRIMARY KEY IDENTITY(1,1),
    unique_number INT NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL
);


-- Издательства
CREATE TABLE Publishers (
    id_publisher INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(100) NOT NULL,
    city VARCHAR(100) NOT NULL
);


-- Книги
CREATE TABLE Books (
    id_book INT PRIMARY KEY IDENTITY(1,1),
    unique_code VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(150) NOT NULL,
    author VARCHAR(100),
    coauthors VARCHAR(255),
    id_publisher INT NOT NULL,
    publication_year INT NOT NULL,
    page_count INT NOT NULL,

    FOREIGN KEY (id_publisher)
        REFERENCES Publishers(id_publisher),

    CHECK (publication_year >= 1960),
    CHECK (page_count > 0),

    CHECK (
        author IS NOT NULL
        OR coauthors IS NULL
    )
);


-- Связь книг и областей знаний
CREATE TABLE BookKnowledgeAreas (
    id_book INT NOT NULL,
    id_knowledge_area INT NOT NULL,

    PRIMARY KEY (id_book, id_knowledge_area),

    FOREIGN KEY (id_book)
        REFERENCES Books(id_book),

    FOREIGN KEY (id_knowledge_area)
        REFERENCES KnowledgeAreas(id_knowledge_area)
);


-- Экземпляры книг
CREATE TABLE BookCopies (
    id_copy INT PRIMARY KEY IDENTITY(1,1),
    inventory_number INT NOT NULL UNIQUE,
    id_book INT NOT NULL,
    is_available BIT NOT NULL DEFAULT 1,

    FOREIGN KEY (id_book)
        REFERENCES Books(id_book)
);


-- Выдача книг
CREATE TABLE BookIssues (
    id_issue INT PRIMARY KEY IDENTITY(1,1),
    id_reader INT NOT NULL,
    id_copy INT NOT NULL,
    issue_date DATE NOT NULL,
    return_date DATE,

    FOREIGN KEY (id_reader)
        REFERENCES Readers(id_reader),

    FOREIGN KEY (id_copy)
        REFERENCES BookCopies(id_copy),

    CHECK (
        return_date IS NULL
        OR return_date >= issue_date
    )
);