--up

CREATE TABLE user_engagement_score (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id integer UNIQUE REFERENCES users (id) ON UPDATE RESTRICT ON DELETE CASCADE,
    engagement_score float(24) NOT NULL,
    created_at timestamp NOT NULL DEFAULT NOW(),
    updated_at timestamp NOT NULL DEFAULT NOW()
);

ALTER TYPE sync_type ADD VALUE 'engagement_score';

-- This is similar to the update_updated_at_column trigger but
-- will only update if the engagement_score is different
CREATE OR REPLACE FUNCTION update_updated_at_column_user_engagement()
RETURNS trigger AS $$
BEGIN
  IF NEW.engagement_score <> OLD.engagement_score THEN
    NEW.updated_at = NOW();
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER conditional_set_timestamp
BEFORE UPDATE ON user_engagement_score FOR EACH ROW
EXECUTE PROCEDURE update_updated_at_column_user_engagement();

--down

DROP TABLE user_engagement_score;
DROP FUNCTION update_updated_at_column_user_engagement();

-- Postgres doesn't allow deleting enums from types
-- In order to delete the sync_type 'engagement_score' from zoho_sync_status, we need to
-- create a new type, delete all invalid columns, and then swap the new type with the old type
CREATE TYPE new_sync_type AS ENUM('manual', 'user_signup', 'subscription_update');
-- delete all rows with sync_type 'engagement_score'
DELETE FROM zoho_sync_status WHERE sync_type = 'engagement_score';
ALTER TABLE zoho_sync_status ALTER COLUMN sync_type TYPE new_sync_type USING sync_type::text::new_sync_type;
DROP TYPE sync_type;
ALTER TYPE new_sync_type RENAME TO sync_type;
