CREATE TABLE hdb_program_registration_rm_rating_type(
	id BIGINT PRIMARY KEY,
	rating_key VARCHAR(255) UNIQUE NOT NULL,
	name VARCHAR(255) NOT NULL,
	created_at TIMESTAMP WITHOUT TIME ZONE,
	updated_at TIMESTAMP WITHOUT TIME ZONE,
	created_by BIGINT,
	updated_by BIGINT,
	FOREIGN KEY (created_by) REFERENCES users(id),
	FOREIGN KEY (updated_by) REFERENCES users(id)
);

INSERT INTO hdb_program_registration_rm_rating_type VALUES
	(1, 'passion', 'Passion', '2025-06-14 12:02:58.831501'),
	(2, 'growth', 'Growth', '2025-06-14 12:02:58.831501'),
	(3, 'infinitheist', 'Infinitheist', '2025-06-14 12:02:58.831501'),
	(4, 'continuity', 'Continuity', '2025-06-14 12:02:58.831501');

CREATE TABLE hdb_program_registration_rm_rating(
	id SERIAL PRIMARY KEY,
	program_registration_id BIGINT, 
	rm_id BIGINT NOT NULL,
	rating_key VARCHAR(255) NOT NULL,
	rating DECIMAL DEFAULT 0,
	created_at TIMESTAMP WITHOUT TIME ZONE,
	updated_at TIMESTAMP WITHOUT TIME ZONE,
	deleted_at TIMESTAMP WITHOUT TIME ZONE,
	created_by BIGINT,
	updated_by BIGINT,
	FOREIGN KEY (program_registration_id) REFERENCES hdb_program_registration(id),
	FOREIGN KEY (rm_id) REFERENCES users(id),
	FOREIGN KEY (rating_key) REFERENCES hdb_program_registration_rm_rating_type(rating_key),
	FOREIGN KEY (created_by) REFERENCES users(id),
	FOREIGN KEY (updated_by) REFERENCES users(id)
);

ALTER TABLE hdb_program_registration
	ADD COLUMN rm_review TEXT;