
/* 20200504042048 */

CREATE SEQUENCE IF NOT EXISTS settings_pk_ai; COMMIT;

CREATE TABLE IF NOT EXISTS settings (
	pk INTEGER NOT NULL DEFAULT NEXTVAL('settings_pk_ai'),
	type VARCHAR DEFAULT NULL,
	k VARCHAR,
	v VARCHAR,
	note VARCHAR DEFAULT NULL,
	created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT settings_pk PRIMARY KEY (pk)
);
COMMIT;

/* https://stackoverflow.com/a/58954108/3036312 */
DO $exec$ BEGIN
	IF EXISTS(SELECT * FROM information_schema.columns WHERE table_name='settings' AND column_name='kind')
	THEN ALTER TABLE settings RENAME COLUMN "kind" TO "type"; COMMIT; END IF;
END $exec$;

ALTER SEQUENCE settings_pk_ai OWNED BY settings.pk; COMMIT;


INSERT INTO settings ("type",k,v) SELECT NULL,'PEMDA_NAME','Kabupaten Dogiyai'
WHERE NOT EXISTS (SELECT pk FROM settings WHERE "type" IS NULL AND k='PEMDA_NAME');

INSERT INTO settings ("type",k,v) SELECT NULL,'PEMDA_COPYRIGHT_BY','GIN'
WHERE NOT EXISTS (SELECT pk FROM settings WHERE "type" IS NULL AND k='PEMDA_COPYRIGHT_BY');

INSERT INTO settings ("type",k,v) SELECT NULL,'PEMDA_COPYRIGHT_YEAR',2020
WHERE NOT EXISTS (SELECT pk FROM settings WHERE "type" IS NULL AND k='PEMDA_COPYRIGHT_YEAR');

INSERT INTO settings ("type",k,v) SELECT NULL,'APPNAME','Sistem Manajemen File'
WHERE NOT EXISTS (SELECT pk FROM settings WHERE "type" IS NULL AND k='APPNAME');

INSERT INTO settings ("type",k,v) SELECT NULL,'APPNAME_SHORT','SMF'
WHERE NOT EXISTS (SELECT pk FROM settings WHERE "type" IS NULL AND k='APPNAME_SHORT');

INSERT INTO settings ("type",k,v) SELECT NULL,'PEMDA_ADDR','Gedung Bina Graha, Jalan Veteran No. 16, Central Jakarta, Jakarta, Indonesia'
WHERE NOT EXISTS (SELECT pk FROM settings WHERE "type" IS NULL AND k='PEMDA_ADDR');

INSERT INTO settings ("type",k,v) SELECT NULL,'PEMDA_MAIL','webmaster@ksp.go.id'
WHERE NOT EXISTS (SELECT pk FROM settings WHERE "type" IS NULL AND k='PEMDA_MAIL');

/* Berdasarkan Permen 2009/54 Pasal 15 Laman 9-10 */
DO $exec$
DECLARE
   i VARCHAR;
   l VARCHAR[] := array[
   	'Surat Edaran',
   	'Surat Biasa',
   	'Surat Keterangan',
   	'Surat Perintah',
   	'Surat Izin',
   	'Surat Perjanjian',
   	'Surat Perintah Tugas',
   	'Surat Kuasa',
   	'Surat Undangan',
   	'Surat Keterangan Melaksanakan Tugas',
   	'Surat Undangan',
   	'Lainnya'
   ];
BEGIN
   FOREACH i IN ARRAY l LOOP
   	INSERT INTO settings ("type",k,v) SELECT 'JENIS_SURAT','PERMEN_54_2009',i
   	WHERE NOT EXISTS (SELECT pk FROM settings WHERE "type"='JENIS_SURAT' AND k='PERMEN_54_2009' AND v=i);
   END LOOP;
END $exec$;


/* Berdasarkan Permen 2009/54 Pasal 10 Laman 8 */
DO $exec$
DECLARE
   i VARCHAR;
   l VARCHAR[] := array[
   	'Biasa',
   	'Penting',
   	'Segera',
   	'Amat Segera / Kilat'
   ];
BEGIN
   FOREACH i IN ARRAY l LOOP
   	INSERT INTO settings ("type",k,v) SELECT 'SIFAT_SURAT','PERMEN_54_2009',i
   	WHERE NOT EXISTS (SELECT pk FROM settings WHERE "type"='SIFAT_SURAT' AND k='PERMEN_54_2009' AND v=i);
   END LOOP;
END $exec$;


UPDATE settings SET "type"=NULL WHERE "type"='None';
UPDATE settings SET note=NULL WHERE note='None';
