
CREATE SEQUENCE IF NOT EXISTS categories_pk_ai; COMMIT;

CREATE TABLE IF NOT EXISTS categories (
	pk INTEGER NOT NULL DEFAULT nextval('categories_pk_ai'),
	pk_parent INTEGER DEFAULT 0,
	"text" VARCHAR(256) DEFAULT NULL,
	"sort" INTEGER NOT NULL DEFAULT 0,
	attributes JSON DEFAULT NULL,
	created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT categories_pk PRIMARY KEY (pk)
);
COMMIT;

ALTER SEQUENCE categories_pk_ai OWNED BY categories.pk; COMMIT;

ALTER TABLE categories ALTER COLUMN "text" SET DEFAULT NULL; COMMIT;

/* https://stackoverflow.com/a/58954108/3036312 */
DO $exec$ BEGIN
	IF NOT EXISTS(SELECT pk_parent FROM categories WHERE pk_parent IS NULL)
		THEN INSERT INTO categories (pk_parent,"text") VALUES (NULL,'Induk Utama');
	END IF;
END $exec$;

/* DIHARUSKAN EXECUTE MANUAL */
/*
INSERT INTO categories (pk_parent,"text","sort") VALUES (0,'Lainnya',999);
INSERT INTO categories (pk_parent,"text","sort") VALUES (0,'Peraturan Daerah',0);
INSERT INTO categories (pk_parent,"text","sort")
SELECT pk,'Peraturan Bupati',0 FROM categories WHERE "text"='Peraturan Daerah';
INSERT INTO categories (pk_parent,"text","sort")
SELECT pk,'Peraturan Wakil Bupati',0 FROM categories WHERE "text"='Peraturan Daerah';
INSERT INTO categories (pk_parent,"text","sort")
SELECT pk,'Peraturan Sekda',0 FROM categories WHERE "text"='Peraturan Daerah';
*/
