---

CREATE DATABASE IF NOT EXISTS CartridgeDB;
USE CartridgeDB;

CREATE TABLE expenses (
	id_type INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT
);

CREATE TABLE buildings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL
);

CREATE TABLE printer_types (
    id_type_print INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(70) NOT NULL,
    comsumption_count INT DEFAULT 0
);

CREATE TABLE printers (
    id_print INT AUTO_INCREMENT PRIMARY KEY,
    id_type_print INT,
    id_build INT,
    description TEXT,
    inventory_number VARCHAR(50) UNIQUE NOT NULL,
    accouting_number VARCHAR(50) UNIQUE NOT NULL,
    FOREIGN KEY (id_type_print) REFERENCES printer_types(id_type_print),
    FOREIGN KEY (id_build) REFERENCES buildings(id)
);

CREATE TABLE cartridges (
    id_cart INT AUTO_INCREMENT PRIMARY KEY,
    name_cart VARCHAR(100) NOT NULL,
    id_type INT,
    FOREIGN KEY (id_type) REFERENCES expenses(id_type)
);

CREATE TABLE stock_cabinets (
    id_cart INT PRIMARY KEY,
    quanity INT CHECK (quantity >= 0),
    FOREIGN KEY (id_cart) REFERENCES cartridges(id_cart)
);

CREATE TABLE histories (
    history_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    id_print INT,
    id_type_cat INT,
    FOREIGN KEY (id_print) REFERENCES printers(id_print),
    FOREIGN KEY (id_type_cat) REFERENCES expenses(id_type)
);

CREATE TABLE users (
    user_name VARCHAR(50) PRIMARY KEY,
    password_hash CHAR(64) NOT NULL
);