use app_db;

create table roles
(
    id      int auto_increment
        primary key,
    description    varchar(100) not null
);

create table units
(
    id int auto_increment 
        primary key,
    description varchar(100) not null,
    unitCode varchar(100) not null
);

create table budgets 
(
    id      int auto_increment
        primary key,
    program varchar(20),
    kegiatan varchar(20),
    output varchar(20),
    rincian_output varchar(20),
    komponen varchar(20),
    sub_komponen varchar(20),
    akun varchar(20),
    detail varchar(20),
    description varchar(200),
    budget double,
    lock_budget double,
    unitCode varchar(100)
);

INSERT INTO roles (id, description)
    VALUES (1, 'Administrator');
INSERT INTO roles (id, description)
    VALUES (2, 'Unit');

INSERT INTO units (id, description, unitCode) VALUES (1, 'Direktur', '1');
INSERT INTO units (id, description, unitCode) VALUES (2, 'Wakil Direktur I', '2');
INSERT INTO units (id, description, unitCode) VALUES (3, 'Wakil Direktur II', '3');
INSERT INTO units (id, description, unitCode) VALUES (4, 'Wakil Direktur III', '4');
INSERT INTO units (id, description, unitCode) VALUES (5, 'Satuan Pengawas Internal', '5');
INSERT INTO units (id, description, unitCode) VALUES (6, 'Ka. Subbag Administrasi Akademik', '6');
INSERT INTO units (id, description, unitCode) VALUES (7, 'Ka. Urusan Akademik', '7');
INSERT INTO units (id, description, unitCode) VALUES (8, 'Ka. Urusan Sistem Informasi', '8');
INSERT INTO units (id, description, unitCode) VALUES (9, 'Ka. Urusan Kegiatan & Kesejahteraan Mahasiswa', '9');
INSERT INTO units (id, description, unitCode) VALUES (10, 'Ka. Urusan Pengembangan Alumni & Kerjasama', '10');
INSERT INTO units (id, description, unitCode) VALUES (11, 'Ka. Subbag Administrasi Umum', '11');
INSERT INTO units (id, description, unitCode) VALUES (12, 'Ka. Urusan Keuangan', '12');
INSERT INTO units (id, description, unitCode) VALUES (13, 'Ka. Urusan Kepegawaian', '13');
INSERT INTO units (id, description, unitCode) VALUES (14, 'Ka. Urusan Umum', '14');
INSERT INTO units (id, description, unitCode) VALUES (15, 'Ka. Urusan AKLAP', '15');
INSERT INTO units (id, description, unitCode) VALUES (16, 'Ka. Urusan BMN', '16');
INSERT INTO units (id, description, unitCode) VALUES (17, 'Ketua Jurusan Keperawatan', '17');
INSERT INTO units (id, description, unitCode) VALUES (18, 'Ka. Prodi DIII Keperawatan Palembang', '18');
INSERT INTO units (id, description, unitCode) VALUES (19, 'Ka. Prodi DIII Keperawatan Baturaja', '19');
INSERT INTO units (id, description, unitCode) VALUES (20, 'Ka. Prodi DIII Keperawatan Lubuklinggau', '20');
INSERT INTO units (id, description, unitCode) VALUES (21, 'Ka. Prodi DIII Keperawatan Lahat', '21');
INSERT INTO units (id, description, unitCode) VALUES (22, 'Ka. Prodi DIII Farmasi', '22');
INSERT INTO units (id, description, unitCode) VALUES (23, 'Ka. Prodi DIII Kesehatan Gigi', '23');
INSERT INTO units (id, description, unitCode) VALUES (24, 'Ka. Jurusan Teknologi Laboratorium Medis', '24');
INSERT INTO units (id, description, unitCode) VALUES (25, 'Ka. Prodi DIII Teknologi Laboratorium Medis', '25');
INSERT INTO units (id, description, unitCode) VALUES (26, 'Ka. Prodi Sarjana Teknologi Laboratorium Medis', '26');
INSERT INTO units (id, description, unitCode) VALUES (27, 'Ka. Jurusan Kesehatan Lingkungan', '27');
INSERT INTO units (id, description, unitCode) VALUES (28, 'Ka. Prodi DIII Sanitasi', '28');
INSERT INTO units (id, description, unitCode) VALUES (29, 'Ka. Prodi DIII Pengawasan Epidemiologi', '29');
INSERT INTO units (id, description, unitCode) VALUES (30, 'Ka. Pusat Penelitian dan Pengabmas', '30');
INSERT INTO units (id, description, unitCode) VALUES (31, 'Ka. Pusat Pengembangan Pendidikan', '31');
INSERT INTO units (id, description, unitCode) VALUES (32, 'Ka. Pusat Penjaminan Mutu', '32');
INSERT INTO units (id, description, unitCode) VALUES (33, 'Ka. Instalasi Sarana & Prasarana', '33');
INSERT INTO units (id, description, unitCode) VALUES (34, 'Ka. Instalasi Asrama', '34');
INSERT INTO units (id, description, unitCode) VALUES (35, 'Ka. Unit Informasi dan Teknologi', '35');
INSERT INTO units (id, description, unitCode) VALUES (36, 'Ka. Unit Laboratorium Terpadu', '36');
INSERT INTO units (id, description, unitCode) VALUES (37, 'Ka. Unit Perpustakaan Terpadu', '37');
INSERT INTO units (id, description, unitCode) VALUES (38, 'Ka. Unit Pengembangan Bahasa', '38');
INSERT INTO units (id, description, unitCode) VALUES (39, 'Ka. Unit Perencanaan', '39');
INSERT INTO units (id, description, unitCode) VALUES (40, 'Ka. Unit Bisnis', '40');
INSERT INTO units (id, description, unitCode) VALUES (41, 'Ka. Unit Pemilik Risiko', '41');
INSERT INTO units (id, description, unitCode) VALUES (42, 'Ketua Jurusan Gizi', '42');
INSERT INTO units (id, description, unitCode) VALUES (43, 'Ka. Prodi DIII Gizi', '43');
INSERT INTO units (id, description, unitCode) VALUES (44, 'Ka. Prodi Sarjana Terapan Gizi & Dietetika', '44');
INSERT INTO units (id, description, unitCode) VALUES (45, 'Ketua Jurusan Kebidanan', '45');
INSERT INTO units (id, description, unitCode) VALUES (46, 'Ka. Prodi DIII Kebidanan Muara Enim', '46');
INSERT INTO units (id, description, unitCode) VALUES (47, 'Ka. Prodi Sarjana Terapan Kebidanan & Profesi Bidan', '47');
INSERT INTO units (id, description, unitCode) VALUES (48, 'Ka. Prodi Profesi Keperawatan', '48');
INSERT INTO units (id, description, unitCode) VALUES (49, 'Unit Layanan Pengadaan', '49');

create table permohonanhs
(
    id        int auto_increment
        primary key,
    unitID  varchar(100) not null,
    tanggal  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    tujuan varchar(300) not null,
    status  varchar(100) not null,
    dikirim  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    diperbaiki    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    disetujui    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    dilaporkan  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    keteranganStatus varchar(300) not null
);

create table permohonands
(
    id        int auto_increment
        primary key,
    permohonanhID  int not null,
    budgetID int,
    nilai DECIMAL(19,2) NOT NULL,
    keterangan varchar(300) not null
);
