-- Deploy jmp:plan_log_with_range to pg
-- requires: plan_log
-- requires: customer_plans

BEGIN;

DROP VIEW customer_plans;

ALTER TABLE plan_log ADD date_range tsrange;

UPDATE plan_log SET date_range=tsrange(starts_at, expires_at);
ALTER TABLE plan_log ALTER date_range SET NOT NULL;

ALTER TABLE plan_log DROP starts_at;
ALTER TABLE plan_log DROP expires_at;
ALTER TABLE plan_log ADD PRIMARY KEY (customer_id, date_range);

ALTER TABLE plan_log ADD EXCLUDE USING GIST
	(customer_id WITH =, date_range WITH &&);

CREATE VIEW customer_plans AS
	SELECT DISTINCT ON (customer_id)
		customer_id,
		plan_name,
		upper(date_range) AS expires_at
	FROM plan_log
	WHERE NOT (tsrange(LOCALTIMESTAMP, LOCALTIMESTAMP, '[]') << date_range)
	ORDER BY customer_id, date_range DESC;

COMMIT;