--up
CREATE TABLE plans (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    is_trial boolean NOT NULL DEFAULT false,
    created_at timestamp NOT NULL DEFAULT NOW(),
    updated_at timestamp NOT NULL DEFAULT NOW(),
    active_until timestamp DEFAULT NULL
);

CREATE TABLE app_features (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code text NOT NULL,
    created_at timestamp NOT NULL DEFAULT NOW(),
    updated_at timestamp NOT NULL DEFAULT NOW()
);

CREATE TABLE plan_app_features (
    plan_id integer NOT NULL REFERENCES plans(id),
    app_feature_id integer NOT NULL REFERENCES app_features(id)
);

INSERT INTO plans (name, is_trial) VALUES ('Pro', false);
INSERT INTO plans (name, is_trial) VALUES ('Pro (Trial)', true);
INSERT INTO plans (name, is_trial) VALUES ('Indie', false);

INSERT INTO app_features (code) VALUES
('ExportMP3'),
('ExportMIDI'),
('MIDIOut'),
('Composer'),
('StemSplit'),
('AnalyzeChord'),
('BasicSkills'),
('GenerateAudio'),
('AudioToMIDI'),
('TrainPrototype'),
('ExecutePrototype'),
('Multiplayer'),
('UnlimitedConductor');

-- Pro & Pro (Trial) get everything, Indie gets everything except the features listed in the join condition
INSERT INTO plan_app_features (plan_id, app_feature_id)
SELECT plans.id, app_features.id
FROM plans JOIN app_features ON
    NOT (plans.name = 'Indie' AND app_features.code IN ('GenerateAudio', 'AudioToMIDI', 'TrainPrototype', 'ExecutePrototype', 'Multiplayer', 'UnlimitedConductor', 'StemSplit'));

CREATE TRIGGER set_timestamp
BEFORE UPDATE ON plans FOR EACH ROW
EXECUTE PROCEDURE update_updated_at_column();

CREATE TRIGGER set_timestamp
BEFORE UPDATE ON app_features FOR EACH ROW
EXECUTE PROCEDURE update_updated_at_column();

ALTER TABLE subscription_updates ADD COLUMN plan_id INTEGER REFERENCES plans(id);

UPDATE subscription_updates SET plan_id = (SELECT id FROM plans WHERE name = 'Pro');

ALTER TABLE subscription_updates ALTER COLUMN plan_id SET NOT NULL;

--down
ALTER TABLE subscription_updates DROP COLUMN plan_id;
DROP TABLE plan_app_features;
DROP TABLE app_features;
DROP TABLE plans;
