
CREATE SEQUENCE IF NOT EXISTS menus_pk_ai; COMMIT;

CREATE TABLE IF NOT EXISTS menus (
	pk INTEGER NOT NULL DEFAULT nextval('menus_pk_ai'),
	pk_parent INTEGER NOT NULL DEFAULT 0,
	"type" VARCHAR(64) NOT NULL,
	"text" VARCHAR(256) DEFAULT NULL,
	"href" VARCHAR(512) DEFAULT NULL,
	"icon" VARCHAR(128) DEFAULT NULL,
	"sort" INTEGER NOT NULL DEFAULT 0,
	"is" BOOLEAN NOT NULL DEFAULT 't',
	extra JSON DEFAULT NULL,
	created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT menus_pk PRIMARY KEY (pk),
	CONSTRAINT menus_idx_unique UNIQUE ("type",href)
);
COMMIT;

ALTER SEQUENCE menus_pk_ai OWNED BY menus.pk; COMMIT;


/* ganti extra:icon menjadi icon */
ALTER TABLE menus ADD COLUMN IF NOT EXISTS icon VARCHAR(128) DEFAULT NULL; COMMIT;


/* HARUS DISINI SEBELUM CREATE,UPDATE DATA. */
TRUNCATE TABLE menus RESTART IDENTITY CASCADE; COMMIT;


INSERT INTO menus("type",href) VALUES('__auth__','/') ON CONFLICT DO NOTHING; COMMIT;

INSERT INTO menus(pk_parent,"type","is","text",href,sort,extra) VALUES(
	0,'sidebar','t','Index','/dash',0,
	'{"icon":"fas fa-fw fa-tachometer-alt"}'::JSON
) ON CONFLICT DO NOTHING; COMMIT;


INSERT INTO menus("type",href) VALUES('__auth__','/categories') ON CONFLICT DO NOTHING; COMMIT;

INSERT INTO menus(pk_parent,"type","is","text",href,sort,extra) VALUES(
	0,'sidebar','t','Pengkategorian','/categories/list',30,
	'{"icon":"fas fa-fw fa-file"}'::JSON
) ON CONFLICT DO NOTHING; COMMIT;


INSERT INTO menus("type",href) VALUES('__auth__','/berkas') ON CONFLICT DO NOTHING; COMMIT;

INSERT INTO menus(pk_parent,"type","is","text",href,sort,extra) VALUES(
	0,'sidebar','t','Unggah Berkas','/berkas/form/create',50,
	'{"icon":"fas fa-fw fa-file"}'::JSON
) ON CONFLICT DO NOTHING; COMMIT;

INSERT INTO menus(pk_parent,"type","is","text",href,sort,extra) VALUES(
	0,'sidebar','t','Pencarian Berkas','/berkas/list',51,
	'{"icon":"fas fa-fw fa-file"}'::JSON
) ON CONFLICT DO NOTHING; COMMIT;


INSERT INTO menus("type",href) VALUES('__auth__','/surat') ON CONFLICT DO NOTHING; COMMIT;

DELETE FROM menus WHERE href LIKE 'persuratan%';
INSERT INTO menus(pk_parent,"type","is","text",href,sort,extra) VALUES(
	0,'sidebar','t','Surat Masuk','/surat/masuk/list',60,
	'{"icon":"fas fa-fw fa-mail-bulk"}'::JSON
) ON CONFLICT DO NOTHING; COMMIT;
INSERT INTO menus(pk_parent,"type","is","text",href,sort,extra) VALUES(
	0,'sidebar','t','Surat Keluar','/surat/keluar/list',61,
	'{"icon":"fas fa-fw fa-envelope-open-text"}'::JSON
) ON CONFLICT DO NOTHING; COMMIT;


INSERT INTO menus("type",href) VALUES('__auth__','/disposisi') ON CONFLICT DO NOTHING; COMMIT;

INSERT INTO menus(pk_parent,"type","is","text",href,sort,icon)
VALUES(0,'sidebar','t','Disposisi','/disposisi/list',62,'fas fa-fw fa-paper-plane')
ON CONFLICT DO NOTHING; COMMIT;


INSERT INTO menus(pk_parent,"type","is","text",href,sort,extra) VALUES(
	0,'sidebar','t','Pengaturan','/settings',99,
	'{"icon":"fas fa-fw fa-cog"}'::JSON
) ON CONFLICT DO NOTHING; COMMIT;

INSERT INTO menus("type",href) VALUES('__auth__','/users') ON CONFLICT DO NOTHING; COMMIT;

INSERT INTO menus(pk_parent,"type","is","text",href,sort,extra)
SELECT pk,'sidebar','t','Users Pengguna','/users/list',0, '{"icon":"fas fa-fw fa-users"}'::JSON
FROM menus WHERE "type"='sidebar' AND href='/settings'
ON CONFLICT DO NOTHING; COMMIT;

INSERT INTO menus(pk_parent,"type","is","text",href,sort,extra)
SELECT pk,'sidebar','t','Informasi Aplikasi','/settings/main',0, '{"icon":"fas fa-fw fa-sign"}'::JSON
FROM menus WHERE "type"='sidebar' AND href='/settings'
ON CONFLICT DO NOTHING; COMMIT;


/* LOKASI SKRIP HARUS DISINI SETELAH INSERT */
UPDATE menus SET href='/'||href WHERE href NOT LIKE '/%'; COMMIT;
UPDATE menus SET icon = extra->>'icon' WHERE icon IS NULL AND extra IS NOT NULL AND extra->>'icon' IS NOT NULL;
