-- upgrade script to migrate the Koji database schema
-- from version 1.20 to 1.21


BEGIN;

ALTER TABLE events ALTER COLUMN time TYPE TIMESTAMPTZ USING
    timezone(current_setting('TIMEZONE'), time::timestamptz);
ALTER TABLE sessions ALTER COLUMN start_time TYPE TIMESTAMPTZ USING
    timezone(current_setting('TIMEZONE'), start_time::timestamptz);
ALTER TABLE sessions ALTER COLUMN update_time TYPE TIMESTAMPTZ USING
    timezone(current_setting('TIMEZONE'), update_time::timestamptz);
ALTER TABLE task ALTER COLUMN create_time TYPE TIMESTAMPTZ USING
    timezone(current_setting('TIMEZONE'), create_time::timestamptz);
ALTER TABLE task ALTER COLUMN start_time TYPE TIMESTAMPTZ USING
    timezone(current_setting('TIMEZONE'), start_time::timestamptz);
ALTER TABLE task ALTER COLUMN completion_time TYPE TIMESTAMPTZ USING
    timezone(current_setting('TIMEZONE'), completion_time::timestamptz);
ALTER TABLE build ALTER COLUMN start_time TYPE TIMESTAMPTZ USING
    timezone(current_setting('TIMEZONE'), start_time::timestamptz);
ALTER TABLE build ALTER COLUMN completion_time TYPE TIMESTAMPTZ USING
    timezone(current_setting('TIMEZONE'), completion_time::timestamptz);
ALTER TABLE build_reservations ALTER COLUMN created TYPE TIMESTAMPTZ USING
    timezone(current_setting('TIMEZONE'), created::timestamptz);

-- input type has to be specified on PostgreSQL 9.x
DROP FUNCTION IF EXISTS get_event_time(INTEGER);
CREATE FUNCTION get_event_time(INTEGER) RETURNS TIMESTAMPTZ AS '
	SELECT time FROM events WHERE id=$1;
' LANGUAGE SQL;

DROP INDEX IF EXISTS sessions_active_and_recent;
CREATE INDEX sessions_active_and_recent ON sessions(expired, master, update_time) WHERE (expired = FALSE AND master IS NULL);

COMMIT;
-- upgrade script to migrate the Koji database schema
-- from version 1.22 to 1.23


BEGIN;

CREATE INDEX task_by_no_parent_state_method ON task(parent, state, method) WHERE parent IS NULL;

ALTER TABLE tag_extra ALTER COLUMN value DROP NOT NULL;

-- Message queue for the protonmsg plugin
CREATE TABLE proton_queue (
        id SERIAL PRIMARY KEY,
        created_ts TIMESTAMPTZ DEFAULT NOW(),
        address TEXT NOT NULL,
        props JSON NOT NULL,
        body JSON NOT NULL
) WITHOUT OIDS;


COMMIT;
-- upgrade script to migrate the Koji database schema
-- from version 1.23 to 1.24


BEGIN;

ALTER TABLE tag_external_repos ADD COLUMN arches TEXT;

COMMIT;
-- upgrade script to migrate the Koji database schema
-- from version 1.24 to 1.25


BEGIN;

ALTER TABLE repo ADD COLUMN task_id INTEGER NULL REFERENCES task(id);

COMMIT;
-- upgrade script to migrate the Koji database schema
-- from version 1.25 to 1.26


BEGIN;

ALTER TABLE channels ADD COLUMN description TEXT;
ALTER TABLE channels ADD COLUMN enabled BOOLEAN NOT NULL DEFAULT 'true';
ALTER TABLE channels ADD COLUMN comment TEXT;

COMMIT;
-- upgrade script to migrate the Koji database schema
-- from version 1.27 to 1.28


BEGIN;

ALTER TABLE permissions ADD COLUMN description TEXT;

UPDATE permissions set description='Full administrator access. Perform all actions.' WHERE name = 'admin';
UPDATE permissions set description='Create appliance builds - deprecated.' WHERE name = 'appliance';
UPDATE permissions set description='Create a dist-repo.' WHERE name = 'dist-repo';
UPDATE permissions set description='Add, remove, enable, disable hosts and channels.' WHERE name = 'host';
UPDATE permissions set description='Start image tasks.' WHERE name = 'image';
UPDATE permissions set description='Import image archives.' WHERE name = 'image-import';
UPDATE permissions set description='Start livecd tasks.' WHERE name = 'livecd';
UPDATE permissions set description='Import maven archives.' WHERE name = 'maven-import';
UPDATE permissions set description='Manage repos: newRepo, repoExpire, repoDelete, repoProblem.' WHERE name = 'repo';
UPDATE permissions set description='Import RPM signatures and write signed RPMs.' WHERE name = 'sign';
UPDATE permissions set description='Manage packages in tags: add, block, remove, and clone tags.' WHERE name = 'tag';
UPDATE permissions set description='Add, edit, and remove targets.' WHERE name = 'target';
UPDATE permissions set description='The default hub policy rule for "vm" requires this permission to trigger Windows builds.' WHERE name = 'win-admin';
UPDATE permissions set description='Import win archives.' WHERE name = 'win-import';

COMMIT;
-- upgrade script to migrate the Koji database schema
-- from version 1.28 to 1.29


-- This version introduced no changes in db schema
-- upgrade script to migrate the Koji database schema
-- from version 1.29 to 1.30


BEGIN;

ALTER TABLE archivetypes ADD COLUMN compression_type TEXT;

UPDATE archivetypes set compression_type='zip' WHERE name = 'jar';
UPDATE archivetypes set compression_type='zip' WHERE name = 'zip';
UPDATE archivetypes set compression_type='tar' WHERE name = 'tar';

-- clean some unused old indices if they still exist
-- https://pagure.io/koji/issue/3160
DROP INDEX IF EXISTS image_listing_archives;
DROP INDEX IF EXISTS image_listing_rpms;
DROP INDEX IF EXISTS imageinfo_listing_rpms;

COMMIT;
-- upgrade script to migrate the Koji database schema
-- from version 1.30 to 1.31

BEGIN;
    -- index for default search method for rpms, PG11+ can benefit from new include method
    DO $$
       DECLARE version integer;
       BEGIN
           SELECT current_setting('server_version_num')::integer INTO version;
           IF version >= 110000 THEN
               EXECUTE 'CREATE INDEX rpminfo_filename ON rpminfo((name || ''-'' || version || ''-'' || release || ''.'' || arch || ''.rpm'')) INCLUDE (id);';
           ELSE
               EXECUTE 'CREATE INDEX rpminfo_filename ON rpminfo((name || ''-'' || version || ''-'' || release || ''.'' || arch || ''.rpm''));';
           END IF;
       END
    $$;
COMMIT;
-- upgrade script to migrate the Koji database schema
-- from version 1.31 to 1.32

BEGIN;

    -- fix duplicate extension in archivetypes
    UPDATE archivetypes SET extensions = 'vhdx.gz vhdx.xz' WHERE name = 'vhdx-compressed';

    -- for tag if session is closed or not
    ALTER TABLE sessions ADD COLUMN closed BOOLEAN NOT NULL DEFAULT FALSE;
    ALTER TABLE sessions ADD CONSTRAINT no_closed_exclusive CHECK (closed IS FALSE OR "exclusive" IS NULL);
    ALTER TABLE sessions DROP CONSTRAINT exclusive_expired_sane;

    -- track checksum of rpms
    CREATE TABLE rpm_checksum (
            rpm_id INTEGER NOT NULL REFERENCES rpminfo(id),
            sigkey TEXT NOT NULL,
            checksum TEXT NOT NULL UNIQUE,
            checksum_type SMALLINT NOT NULL,
            PRIMARY KEY (rpm_id, sigkey, checksum_type)
    ) WITHOUT OIDS;
    CREATE INDEX rpm_checksum_rpm_id ON rpm_checksum(rpm_id);
COMMIT;
-- upgrade script to migrate the Koji database schema
-- from version 1.32 to 1.33

BEGIN;
    ALTER TABLE sessions ADD COLUMN renew_time TIMESTAMPTZ;
    INSERT INTO archivetypes (name, description, extensions) VALUES ('checksum', 'Checksum file', 'sha256') ON CONFLICT DO NOTHING;
    INSERT INTO archivetypes (name, description, extensions) VALUES ('changes', 'Kiwi changes file', 'changes.xz changes') ON CONFLICT DO NOTHING;
    INSERT INTO archivetypes (name, description, extensions) VALUES ('packages', 'Kiwi packages listing', 'packages') ON CONFLICT DO NOTHING;
    INSERT INTO archivetypes (name, description, extensions) VALUES ('verified', 'Kiwi verified package list', 'verified') ON CONFLICT DO NOTHING;
    ALTER TABLE host ADD COLUMN update_time TIMESTAMPTZ;
    CREATE TABLE locks (
        name TEXT NOT NULL PRIMARY KEY
    ) WITHOUT OIDS;
    INSERT INTO locks(name) VALUES('protonmsg-plugin');
COMMIT;
