---

CREATE DATABASE IF NOT EXISTS book_exchange
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE book_exchange;


-- =========================
-- Пользователи
-- =========================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    login VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    username VARCHAR(100) NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'active'
);


-- =========================
-- Книги
-- =========================
CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) DEFAULT 0.00,
    quantity INT NOT NULL DEFAULT 0,
    cover VARCHAR(255)
);


-- =========================
-- Состояние книги
-- =========================
CREATE TABLE conditions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);


-- =========================
-- Конкретные экземпляры книг
-- =========================
CREATE TABLE copies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT NOT NULL,
    owner_id INT NOT NULL,
    condition_id INT NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'available',

    FOREIGN KEY (book_id)
        REFERENCES books(id)
        ON DELETE CASCADE,

    FOREIGN KEY (owner_id)
        REFERENCES users(id)
        ON DELETE CASCADE,

    FOREIGN KEY (condition_id)
        REFERENCES conditions(id)
        ON DELETE RESTRICT
);


-- =========================
-- Акт передачи
-- =========================
CREATE TABLE transfers (
    id INT AUTO_INCREMENT PRIMARY KEY,

    source_id INT NOT NULL,
    destination_id INT NOT NULL,
    copy_id INT NOT NULL,

    transfer_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    return_date DATETIME NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'active',

    FOREIGN KEY (source_id)
        REFERENCES users(id)
        ON DELETE RESTRICT,

    FOREIGN KEY (destination_id)
        REFERENCES users(id)
        ON DELETE RESTRICT,

    FOREIGN KEY (copy_id)
        REFERENCES copies(id)
        ON DELETE RESTRICT
);