
CREATE SEQUENCE IF NOT EXISTS suratd_pk_ai; COMMIT;

CREATE TABLE IF NOT EXISTS suratd (
	pk INTEGER NOT NULL DEFAULT nextval('suratd_pk_ai'),
	pk_surat INTEGER NOT NULL,
	kepada VARCHAR(256) DEFAULT NULL,
	catatan TEXT DEFAULT NULL,
	created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT suratd_pk PRIMARY KEY (pk),
	CONSTRAINT suratd_surat_fk FOREIGN KEY (pk_surat) REFERENCES surat (pk) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMIT;

ALTER SEQUENCE suratd_pk_ai OWNED BY suratd.pk; COMMIT;

ALTER TABLE suratd ADD COLUMN IF NOT EXISTS kepada VARCHAR(256) DEFAULT NULL; COMMIT;


/* ================================================== */

/* COPAS suratd ke disposisi //
INSERT INTO disposisi (pk_surat,kepada,catatan,created,updated,tanggal)
SELECT pk_surat,kepada,catatan,created,updated,CURRENT_TIMESTAMP FROM suratd
*/

/* ================================================== */


CREATE SEQUENCE IF NOT EXISTS disposisi_pk_ai; COMMIT;

CREATE TABLE IF NOT EXISTS disposisi (
	pk INTEGER NOT NULL DEFAULT nextval('disposisi_pk_ai'),
	pk_surat INTEGER DEFAULT NULL,
	tanggal DATE NOT NULL,
	sifat VARCHAR(64) DEFAULT NULL,
	kepada TEXT DEFAULT NULL,
	catatan TEXT DEFAULT NULL,
	created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	user_input INTEGER DEFAULT 0,
	CONSTRAINT disposisi_pk PRIMARY KEY (pk),
	CONSTRAINT disposisi_surat_fk FOREIGN KEY (pk_surat) REFERENCES surat (pk) ON DELETE SET NULL ON UPDATE CASCADE
);
COMMIT;

ALTER SEQUENCE disposisi_pk_ai OWNED BY disposisi.pk; COMMIT;

ALTER TABLE disposisi ADD COLUMN IF NOT EXISTS sifat VARCHAR(64) DEFAULT NULL; COMMIT;
ALTER TABLE disposisi ADD COLUMN IF NOT EXISTS tanggal DATE NOT NULL; COMMIT;
ALTER TABLE disposisi ADD COLUMN IF NOT EXISTS user_input INTEGER NOT NULL DEFAULT 0; COMMIT;
