execute(" -- create new schema create schema if not exists mcp; -- template table (top level) create table if not exists mcp.template ( id serial primary key, nama varchar(255), tahun integer, keterangan text, created_at timestamp default now(), created_by integer, updated_at timestamp default now(), updated_by integer, deleted_at timestamp, deleted_by integer ); -- area table (links to template) create table if not exists mcp.area ( id serial primary key, template_id integer, nama varchar(255), bobot float, created_at timestamp default now(), created_by integer, updated_at timestamp default now(), updated_by integer, deleted_at timestamp, deleted_by integer, foreign key (template_id) references mcp.template(id) on delete set null ); -- sasaran table (links to area) create table if not exists mcp.sasaran ( id serial primary key, area_id integer, nama varchar(255), bobot float, created_at timestamp default now(), created_by integer, updated_at timestamp default now(), updated_by integer, deleted_at timestamp, deleted_by integer, foreign key (area_id) references mcp.area(id) on delete set null ); -- aspek table (links to sasaran) create table if not exists mcp.aspek ( id serial primary key, sasaran_id integer, nama varchar(255), bobot float, created_at timestamp default now(), created_by integer, updated_at timestamp default now(), updated_by integer, deleted_at timestamp, deleted_by integer, foreign key (sasaran_id) references mcp.sasaran(id) on delete set null ); -- indikator table (links to aspek) create table if not exists mcp.indikator ( id serial primary key, aspek_id integer, nama varchar(255), bobot float, penanggung_jawab text, tujuan text, info_lampiran text, catatan_penilaian text, created_at timestamp default now(), created_by integer, updated_at timestamp default now(), updated_by integer, deleted_at timestamp, deleted_by integer, foreign key (aspek_id) references mcp.aspek(id) on delete set null ); -- question table (links to indikator) create table if not exists mcp.question ( id serial primary key, indikator_id integer, keterangan text, keterangan_tambahan text, tipe integer, nilai float, lampiran text, created_at timestamp default now(), created_by integer, updated_at timestamp default now(), updated_by integer, deleted_at timestamp, deleted_by integer, foreign key (indikator_id) references mcp.indikator(id) on delete set null ); -- answer table (links to indikator) create table if not exists mcp.answer ( id serial primary key, indikator_id integer, instansi_id integer, status integer, nilai float, nilai_verifikasi float, verified_by integer, verified_at timestamp, nilai_qa float, qa_by integer, qa_at timestamp, created_at timestamp default now(), created_by integer, updated_at timestamp default now(), updated_by integer, deleted_at timestamp, deleted_by integer, foreign key (indikator_id) references mcp.indikator(id) on delete set null, foreign key (instansi_id) references jaga.instansi(id) on delete set null ); create table if not exists mcp.answer_detail ( id serial primary key, answer_id integer, question_id integer, nilai float, nilai_status integer, keterangan text, nilai_verifikasi float, nilai_verifikasi_status integer, keterangan_verifikasi text, verified_by integer, verified_at timestamp, nilai_qa float, nilai_qa_status integer, keterangan_qa text, qa_by integer, qa_at timestamp, created_at timestamp default now(), created_by integer, updated_at timestamp default now(), updated_by integer, deleted_at timestamp, deleted_by integer, foreign key (answer_id) references mcp.answer(id) on delete set null, foreign key (question_id) references mcp.question(id) on delete set null ); -- attachments table create table if not exists mcp.attachments ( id serial primary key, tipe integer, uuid varchar(255), filename varchar(255), keterangan text, metadata integer, status integer, referer integer, created_at timestamp default now(), created_by integer, updated_at timestamp default now(), updated_by integer, deleted_at timestamp, deleted_by integer ); -- answer_attachment table (links to attachment and answer) create table if not exists mcp.answer_attachment ( id serial primary key, answer_id integer, attachment_id integer, tipe integer, created_at timestamp default now(), created_by integer, updated_at timestamp default now(), updated_by integer, deleted_at timestamp, deleted_by integer, foreign key (answer_id) references mcp.answer(id) on delete set null, foreign key (attachment_id) references mcp.attachments(id) on delete set null ); -- periode table (links to template) create table if not exists mcp.periode ( id serial primary key, template_id integer, nama varchar(255), started_at timestamp, ended_at timestamp, verif_started_at timestamp, verif_ended_at timestamp, qa_started_at timestamp, qa_ended_at timestamp, created_at timestamp default now(), created_by integer, updated_at timestamp default now(), updated_by integer, deleted_at timestamp, deleted_by integer, foreign key (template_id) references mcp.template(id) on delete set null ); "); } public function down() { self::$connection->execute(" -- drop tables in reverse order to handle foreign key constraints drop table if exists mcp.periode cascade; drop table if exists mcp.answer_attachment cascade; drop table if exists mcp.attachments cascade; drop table if exists mcp.answer_detail cascade; drop table if exists mcp.answer cascade; drop table if exists mcp.question cascade; drop table if exists mcp.indikator cascade; drop table if exists mcp.aspek cascade; drop table if exists mcp.sasaran cascade; drop table if exists mcp.area cascade; drop table if exists mcp.template cascade; -- finally drop the schema itself drop schema if exists mcp cascade; "); } }