
CREATE SEQUENCE IF NOT EXISTS roles_pk_ai; COMMIT;

CREATE TABLE IF NOT EXISTS "roles" (
	pk INTEGER NOT NULL DEFAULT NEXTVAL('roles_pk_ai'),
	role VARCHAR(128) NOT NULL UNIQUE,
	note TEXT DEFAULT NULL,
	created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT roles_pk PRIMARY KEY (pk)
);
COMMIT;

ALTER SEQUENCE roles_pk_ai OWNED BY roles.pk; COMMIT;

ALTER TABLE roles ALTER COLUMN role TYPE VARCHAR(128); COMMIT;

ALTER TABLE roles ADD COLUMN IF NOT EXISTS created TIMESTAMP DEFAULT CURRENT_TIMESTAMP; COMMIT;
ALTER TABLE roles ADD COLUMN IF NOT EXISTS updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP; COMMIT;


INSERT INTO roles (pk,role,note)
SELECT 1,'ADMIN','akun dengan hakakses paling tinggi'
ON CONFLICT DO NOTHING;

INSERT INTO roles (pk,role) SELECT 50,'KOORDINATOR' ON CONFLICT DO NOTHING;


DELETE FROM roles WHERE role IN ('OPERATOR_BERKAS','OPERATOR_BERKAS_TAHUN'); COMMIT;

INSERT INTO roles (pk,role) SELECT 71,'OPERATOR' ON CONFLICT DO NOTHING;
INSERT INTO roles (pk,role) SELECT 72,'OPERATOR_TAHUN' ON CONFLICT DO NOTHING;

DELETE FROM users WHERE users.pk_roles IN (SELECT roles.pk FROM roles WHERE roles.role IN ('OPERATOR_BERKAS','OPERATOR_BERKAS_TAHUN')); COMMIT;