
CREATE SEQUENCE IF NOT EXISTS access_pk_ai; COMMIT;

CREATE TABLE IF NOT EXISTS access (
	pk INTEGER NOT NULL DEFAULT nextval('access_pk_ai'),
	roles_pk INTEGER NOT NULL REFERENCES roles(pk) ON DELETE CASCADE ON UPDATE CASCADE,
	menus_pk INTEGER NOT NULL REFERENCES menus(pk) ON DELETE CASCADE ON UPDATE CASCADE,
	users_pk INTEGER DEFAULT NULL REFERENCES users(pk) ON DELETE CASCADE ON UPDATE CASCADE,
	"is" BOOLEAN,
	created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
); COMMIT;

ALTER SEQUENCE access_pk_ai OWNED BY access.pk; COMMIT;


/* https://stackoverflow.com/a/8289253 */
CREATE UNIQUE INDEX IF NOT EXISTS access_rolemenuuser_unique ON access(roles_pk,menus_pk,users_pk)
WHERE users_pk IS NOT NULL; COMMIT;
CREATE UNIQUE INDEX IF NOT EXISTS access_rolemenu_unique ON access(roles_pk,menus_pk)
WHERE users_pk IS NULL; COMMIT;


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


/* Terjemahan: SEMUA bisa akses /dash; */
INSERT INTO access (roles_pk,menus_pk,"is")
SELECT roles.pk,menus.pk,'t'
FROM roles,menus WHERE menus.type='sidebar' AND menus.href = '/dash'
ON CONFLICT DO NOTHING;

/* Terjemahan: ADMIN bisa akses SEMUA; */
INSERT INTO access (roles_pk,menus_pk,"is")
SELECT roles.pk,menus.pk,'t'
FROM roles,menus WHERE roles.role = 'ADMIN'
ON CONFLICT DO NOTHING;

/* role=KOORDINATOR ALLOW menu=( /berkas% | /surat% | /disposisi% ) */
INSERT INTO access (roles_pk,menus_pk,"is")
SELECT roles.pk,menus.pk,'t'
FROM roles,menus WHERE roles.role='KOORDINATOR'
AND (menus.href LIKE '/berkas%' OR menus.href LIKE '/surat%' OR menus.href LIKE '/disposisi%')
ON CONFLICT DO NOTHING;

/* role=OPERATOR% ALLOW menu=( /berkas% | /surat% | /disposisi% ) */
INSERT INTO access (roles_pk,menus_pk,"is")
SELECT roles.pk,menus.pk,'t'
FROM roles,menus WHERE roles.role LIKE 'OPERATOR%'
AND (menus.href LIKE '/berkas%' OR menus.href LIKE '/surat%' OR menus.href LIKE '/disposisi%')
ON CONFLICT DO NOTHING;
