CREATE TABLE OccupationInventoryRoomTypes (
id INT AUTO_INCREMENT PRIMARY KEY,
occupationInventoryId INT NOT NULL,
roomTypeId INT,
roomTypeName VARCHAR(255),
totalRoomType INT,
occRoomType INT,
availRoomType INT,
outOfOrderRoomType INT,
FOREIGN KEY (occupationInventoryId) REFERENCES OccupationInventory(id)
);

CREATE TABLE OccupationInventory (
id INT AUTO_INCREMENT PRIMARY KEY,
propertyId INT NOT NULL,
date DATETIME NOT NULL,
totalRoom INT,
occ INT,
outOfOrder INT,
avail INT,
UNIQUE KEY unique_property_date (propertyId, date)
);