
CREATE SEQUENCE IF NOT EXISTS contents_pk_ai; COMMIT;

CREATE TABLE IF NOT EXISTS contents (
	pk INTEGER NOT NULL DEFAULT nextval('contents_pk_ai'),
	pk_categories INTEGER DEFAULT NULL,
	tahun INTEGER DEFAULT NULL,
	judul VARCHAR(256) DEFAULT NULL,
	deskripsi TEXT DEFAULT NULL,
	attributes JSON DEFAULT NULL,
	created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	user_input INTEGER DEFAULT 0,
	CONSTRAINT contents_pk PRIMARY KEY (pk),
	CONSTRAINT contents_ta_fk FOREIGN KEY (tahun) REFERENCES ta (tahun) ON UPDATE CASCADE ON DELETE SET NULL,
	CONSTRAINT contents_categories_fk FOREIGN KEY (pk_categories) REFERENCES categories(pk) ON UPDATE CASCADE ON DELETE SET NULL
);
COMMIT;

ALTER SEQUENCE contents_pk_ai OWNED BY contents.pk; COMMIT;

ALTER TABLE contents DROP CONSTRAINT IF EXISTS contents_ta_fk; COMMIT;
ALTER TABLE contents ALTER COLUMN tahun SET DEFAULT NULL; COMMIT;
ALTER TABLE contents ADD CONSTRAINT contents_ta_fk FOREIGN KEY (tahun) REFERENCES ta (tahun) ON UPDATE CASCADE ON DELETE SET NULL;

ALTER TABLE contents ALTER COLUMN deskripsi TYPE TEXT; COMMIT;
ALTER TABLE contents ALTER COLUMN deskripsi SET DEFAULT NULL; COMMIT;

ALTER TABLE contents ADD COLUMN IF NOT EXISTS pk_categories INTEGER DEFAULT NULL; COMMIT;
ALTER TABLE contents ADD COLUMN IF NOT EXISTS pk_skpd_dg INTEGER DEFAULT NULL; COMMIT;

ALTER TABLE contents ADD COLUMN IF NOT EXISTS user_input INTEGER DEFAULT NULL; COMMIT;
/* https://stackoverflow.com/a/58954108/3036312 */
/* https://stackoverflow.com/a/6801977/3036312 */
DO $exec$ BEGIN
	-- /* debug */ ALTER TABLE contents DROP CONSTRAINT IF EXISTS contents_categories_fk; COMMIT;
	IF NOT EXISTS (SELECT conname FROM pg_constraint WHERE conname='contents_skpd_dg_fk') THEN
		ALTER TABLE contents ADD CONSTRAINT contents_skpd_dg_fk 
		FOREIGN KEY (pk_skpd_dg) REFERENCES skpd_dg(pk) 
		ON UPDATE CASCADE ON DELETE SET NULL;
	END IF;
END $exec$;
DO $exec$ BEGIN
	-- /* debug */ ALTER TABLE contents DROP CONSTRAINT IF EXISTS contents_categories_fk; COMMIT;
	IF NOT EXISTS (SELECT conname FROM pg_constraint WHERE conname='contents_categories_fk') THEN
		ALTER TABLE contents ADD CONSTRAINT contents_categories_fk
		FOREIGN KEY (pk_categories) REFERENCES categories (pk)
		ON DELETE SET NULL;
	END IF;
END $exec$;
