CREATE TABLE hotel_inventory (
	hotel_inventory_id serial NOT NULL PRIMARY KEY,
	hotel_id int NOT NULL REFERENCES hotel(hotel_id) ON UPDATE CASCADE ON DELETE CASCADE,
	hotel_category_id int NOT NULL REFERENCES hotel_category(hotel_category_id) ON UPDATE CASCADE ON DELETE CASCADE,
	agent_id int REFERENCES agent(agent_id) ON UPDATE CASCADE ON DELETE CASCADE,
	supplier_id int REFERENCES supplier(supplier_id) ON UPDATE CASCADE ON DELETE CASCADE,
	provider_id int REFERENCES provider(provider_id) ON UPDATE CASCADE ON DELETE CASCADE,
	date date NOT NULL,
	allotment int NOT NULL DEFAULT 0,
	deadline int NOT NULL DEFAULT 0,
	cut_off int NOT NULL DEFAULT 0,
	cut_off_time time,
	min_stay int NOT NULL DEFAULT 0,
	reset_key varchar(10),
	adjust jsonb, -- {"allotment": 20, "deadline": 7, "cut_off": 0, "cut_off_time": null, "min_stay": 0}
	updated_datetime timestamp NOT NULL DEFAULT now(),
	status smallint NOT NULL DEFAULT 1
);
CREATE INDEX hotel_inventory_all_key ON hotel_inventory USING btree (hotel_id, hotel_category_id, agent_id, supplier_id, date, status, min_stay, cut_off, cut_off_time, deadline, allotment, reset_key, (date - cut_off + cut_off_time));
CREATE INDEX hotel_inventory_date_from_key ON hotel_inventory USING btree (date);
CREATE UNIQUE INDEX hotel_inventory_hotel_id_hotel_category_id_agent_id_date_ukey ON hotel_inventory (hotel_id, hotel_category_id, agent_id, date) WHERE hotel_id is not null and hotel_category_id is not null and agent_id is not null and date is not null;
CREATE UNIQUE INDEX hotel_inventory_hotel_id_hotel_category_id_date_ukey ON hotel_inventory (hotel_id, hotel_category_id, date) WHERE hotel_id is not null and hotel_category_id is not null and agent_id is null and date is not null;

CREATE INDEX hotel_inventory_status_key ON hotel_inventory USING btree (status);
CREATE INDEX hotel_inventory_agent_id_key ON hotel_inventory USING btree (agent_id);
CREATE INDEX hotel_inventory_min_stay_key ON hotel_inventory USING btree (min_stay);
CREATE INDEX hotel_inventory_cut_off_time_key ON hotel_inventory USING btree (cut_off_time);
CREATE INDEX hotel_inventory_date_cut_off_cut_off_time_key ON hotel_inventory USING btree ((date - cut_off + cut_off_time));
CREATE INDEX hotel_inventory_updated_datetime_key ON hotel_inventory USING btree (updated_datetime);
CREATE INDEX hotel_inventory_updated_datetime_day_key ON hotel_inventory USING btree (date_trunc('day', updated_datetime));
CREATE INDEX hotel_inventory_hotel_id_key ON hotel_inventory USING btree (hotel_id);
CREATE INDEX hotel_inventory_hotel_category_id_key ON hotel_inventory USING btree (hotel_category_id);
CREATE INDEX hotel_inventory_supplier_id_key ON hotel_inventory USING btree (supplier_id);
CREATE INDEX hotel_inventory_provider_id_key ON hotel_inventory USING btree (provider_id);
CREATE INDEX hotel_inventory_hotel_id_updated_datetime_key ON hotel_inventory USING btree (hotel_id, updated_datetime) where provider_id is not null;
--SELECT audit.audit_table('hotel_inventory', true, true, array['updated_datetime']);
CREATE TRIGGER audit_trigger_row_update AFTER INSERT OR UPDATE ON hotel_inventory FOR EACH ROW WHEN (NEW.provider_id is null) EXECUTE PROCEDURE audit.if_modified_func('true');
CREATE TRIGGER audit_trigger_row_delete AFTER DELETE ON hotel_inventory FOR EACH ROW WHEN (OLD.provider_id is null) EXECUTE PROCEDURE audit.if_modified_func('true');
CREATE TRIGGER audit_trigger_stm AFTER TRUNCATE ON hotel_inventory FOR EACH STATEMENT EXECUTE PROCEDURE audit.if_modified_func('true');