
CREATE SEQUENCE IF NOT EXISTS skpd_pk_ai; COMMIT;

CREATE TABLE IF NOT EXISTS skpd (
	pk INTEGER NOT NULL DEFAULT nextval('skpd_pk_ai'),
	urai VARCHAR(256) DEFAULT NULL,
	kode INTEGER[] DEFAULT NULL,
	tahun INTEGER NOT NULL,
	extra JSON DEFAULT NULL,
	created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT skpd_pk PRIMARY KEY (pk),
	CONSTRAINT skpd_ta_fk FOREIGN KEY (tahun) REFERENCES ta (tahun) ON DELETE RESTRICT ON UPDATE RESTRICT
);
COMMIT;

ALTER SEQUENCE skpd_pk_ai OWNED BY skpd.pk; COMMIT;


-- ALTER TABLE skpd ADD COLUMN IF NOT EXISTS nama VARCHAR(256) DEFAULT NULL; COMMIT;
ALTER TABLE skpd DROP COLUMN IF EXISTS nama; COMMIT;

ALTER TABLE skpd ADD COLUMN IF NOT EXISTS urai VARCHAR(256) DEFAULT NULL; COMMIT;

DO $exec$ BEGIN
	IF NOT EXISTS(SELECT pk FROM skpd JOIN ta ON (skpd.tahun=ta.tahun AND ta.current='t')) THEN
		INSERT INTO skpd (kode,urai,tahun) SELECT ARRAY[0,0,0],'Test',tahun FROM ta WHERE "current"='t';
	END IF;
END $exec$;
