-- upgrade script to migrate the Koji database schema
-- from version 1.14 to 1.16


BEGIN;

-- create host_config table
SELECT 'Creating table host_config';
CREATE TABLE host_config (
        host_id INTEGER NOT NULL REFERENCES host(id),
        arches TEXT,
        capacity FLOAT CHECK (capacity > 1) NOT NULL DEFAULT 2.0,
        description TEXT,
        comment TEXT,
        enabled BOOLEAN NOT NULL DEFAULT 'true',
-- versioned - see desc above
        create_event INTEGER NOT NULL REFERENCES events(id) DEFAULT get_event(),
        revoke_event INTEGER REFERENCES events(id),
        creator_id INTEGER NOT NULL REFERENCES users(id),
        revoker_id INTEGER REFERENCES users(id),
        active BOOLEAN DEFAULT 'true' CHECK (active),
        CONSTRAINT active_revoke_sane CHECK (
                (active IS NULL AND revoke_event IS NOT NULL AND revoker_id IS NOT NULL)
                OR (active IS NOT NULL AND revoke_event IS NULL AND revoker_id IS NULL)),
        PRIMARY KEY (create_event, host_id),
        UNIQUE (host_id, active)
) WITHOUT OIDS;
CREATE INDEX host_config_by_active_and_enabled ON host_config(active, enabled);

-- copy starting data
-- CREATE FUNCTION pg_temp.user() returns INTEGER as $$ select id from users where name='nobody' $$ language SQL;
CREATE FUNCTION pg_temp.user() returns INTEGER as $$ select 1 $$ language SQL;
-- If you would like to use an existing user instead, then:
--   1. edit the temporary function to look for the alternate user name

SELECT 'Copying data from host to host_config';
INSERT INTO host_config (host_id, arches, capacity, description, comment, enabled, creator_id)
        SELECT id, arches, capacity, description, comment, enabled, pg_temp.user() FROM host;

-- alter original table
SELECT 'Dropping moved columns';
ALTER TABLE host DROP COLUMN arches;
ALTER TABLE host DROP COLUMN capacity;
ALTER TABLE host DROP COLUMN description;
ALTER TABLE host DROP COLUMN comment;
ALTER TABLE host DROP COLUMN enabled;

-- history for host_channels
SELECT 'Adding versions to host_channels';
ALTER TABLE host_channels ADD COLUMN create_event INTEGER NOT NULL REFERENCES events(id) DEFAULT get_event();
ALTER TABLE host_channels ADD COLUMN revoke_event INTEGER REFERENCES events(id);
-- we need some default for alter table, but drop it after
ALTER TABLE host_channels ADD COLUMN creator_id INTEGER NOT NULL REFERENCES users(id) DEFAULT pg_temp.user();
ALTER TABLE host_channels ALTER COLUMN creator_id DROP DEFAULT;
ALTER TABLE host_channels ADD COLUMN revoker_id INTEGER REFERENCES users(id);
ALTER TABLE host_channels ADD COLUMN active BOOLEAN DEFAULT 'true' CHECK (active);
ALTER TABLE host_channels ADD CONSTRAINT active_revoke_sane CHECK (
                                         (active IS NULL AND revoke_event IS NOT NULL AND revoker_id IS NOT NULL)
                                         OR (active IS NOT NULL AND revoke_event IS NULL AND revoker_id IS NULL));
ALTER TABLE host_channels ADD PRIMARY KEY (create_event, host_id, channel_id);
ALTER TABLE host_channels ADD UNIQUE (host_id, channel_id, active);
-- ALTER TABLE host_channels DROP CONSTRAINT host_channels_host_id_channel_id_key;

COMMIT;