CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS tools (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, code VARCHAR(40) NOT NULL UNIQUE, category VARCHAR(80) NOT NULL, quantity INT NOT NULL, maintenance INT NOT NULL DEFAULT 0, rate DECIMAL(12,2) NOT NULL, CHECK(quantity >= 0 AND maintenance >= 0 AND maintenance <= quantity), CHECK(rate >= 0)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, phone VARCHAR(50) NOT NULL, company VARCHAR(150) NOT NULL DEFAULT '') ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS rentals (id INT AUTO_INCREMENT PRIMARY KEY, tool_id INT NOT NULL, customer_id INT NOT NULL, quantity INT NOT NULL, rate DECIMAL(12,2) NOT NULL, start_date DATE NOT NULL, due_date DATE NOT NULL, returned_date DATE NULL, total DECIMAL(12,2) NOT NULL, paid DECIMAL(12,2) NOT NULL DEFAULT 0, FOREIGN KEY(tool_id) REFERENCES tools(id), FOREIGN KEY(customer_id) REFERENCES customers(id), CHECK(quantity > 0), CHECK(due_date >= start_date), CHECK(paid >= 0 AND paid <= total)) ENGINE=InnoDB;
