USE hobbydev_bookstorage;

CREATE TABLE users(
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    register_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    password_hash VARCHAR(50) NOT NULL,
    api_key VARCHAR(60) NOT NULL
);

CREATE TABLE messages(
    id INT AUTO_INCREMENT PRIMARY KEY,
    from_user_id INT NOT NULL,
    to_user_id INT NOT NULL,
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_messages_from_user
        FOREIGN KEY (from_user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    
    CONSTRAINT fk_messages_to_user
        FOREIGN KEY (to_user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDb;

CREATE INDEX idx_messages_from_user ON messages(from_user_id);
CREATE INDEX idx_messages_to_user ON messages(to_user_id);

CREATE TABLE book_sales(
    id INT AUTO_INCREMENT PRIMARY KEY,
    isbn VARCHAR(13) NOT NULL,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(200) NULL,
    categories VARCHAR(200) NULL,
    description TEXT NULL,
    seller_comment TEXT NULL,
    sell_price INT NOT NULL,
    type VARCHAR(20) NOT NULL,
    seller_user_id INT NOT NULL,
    status VARCHAR(20) NOT NULL, 

    CONSTRAINT fk_book_sales_seller_user
        FOREIGN KEY (seller_user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDb;