-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `db_sipp`
--

-- --------------------------------------------------------

--
-- Table structure for table `aspects`
--

CREATE TABLE `aspects` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `num` tinyint(2) UNSIGNED NOT NULL DEFAULT '1',
  `nm` text,
  `weight` float NOT NULL DEFAULT '0' COMMENT 'Bobot Aspek',
  `weight2` float NOT NULL DEFAULT '0',
  `f1_only` tinyint(1) DEFAULT '0' COMMENT 'Penandaan aspek khusus untuk F01.',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `aspects`
--

INSERT INTO `aspects` (`id`, `num`, `nm`, `weight`, `weight2`, `f1_only`, `created_at`, `updated_at`, `deleted_at`) VALUES
(9, 1, 'Pecel', 0, 0, 0, '2024-02-13 08:20:09', '2024-02-13 08:20:09', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `configs`
--

CREATE TABLE `configs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `data` varchar(50) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `description` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='App System Configuration';

--
-- Dumping data for table `configs`
--

INSERT INTO `configs` (`id`, `data`, `name`, `description`) VALUES
(1, '1', 'OPEN-EVALUATION', 'Opsi untuk membuka Form F-02 Evaluator pada Form F-01 yang belum di submit'),
(2, '1', 'EXCLUDE-F03-DENOMINATOR', 'Kecualikan Penilaian Form 03'),
(3, '1', 'INSTRUMENT-TYPE-A', 'Tipe Instrumen A'),
(4, '0', 'INSTRUMENT-TYPE-B', 'Tipe Instrumen B'),
(5, '1', 'CENSORED-RESPONDEN-NAME', 'Sensor Nama Responden'),
(6, '1', 'CENSORED-RESPONDEN-PHONE', 'Sensor Nomor Ponsel Responden');

-- --------------------------------------------------------

--
-- Table structure for table `config_values`
--

CREATE TABLE `config_values` (
  `id` tinyint(2) UNSIGNED NOT NULL,
  `config_id` int(4) UNSIGNED DEFAULT NULL,
  `data` tinyint(1) NOT NULL DEFAULT '0',
  `year` year(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Data Pengaturan';

--
-- Dumping data for table `config_values`
--

INSERT INTO `config_values` (`id`, `config_id`, `data`, `year`) VALUES
(1, 1, 1, '2022'),
(2, 2, 1, '2022'),
(3, 3, 1, '2022'),
(4, 4, 0, '2022'),
(5, 4, 0, '2023'),
(6, 3, 1, '2023'),
(7, 2, 0, '2023'),
(8, 1, 1, '2023'),
(9, 6, 0, '2024'),
(10, 5, 0, '2024'),
(11, 4, 0, '2024'),
(12, 3, 1, '2024'),
(13, 2, 0, '2024'),
(14, 1, 1, '2024');

-- --------------------------------------------------------

--
-- Table structure for table `consultation_forms`
--

CREATE TABLE `consultation_forms` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `unit_id` bigint(20) UNSIGNED NOT NULL,
  `from_unit_id` bigint(20) DEFAULT NULL,
  `problem` text,
  `suggestion` text,
  `applicants_id` varchar(18) DEFAULT NULL,
  `applicants_name` varchar(50) DEFAULT NULL,
  `applicants_sign` tinytext,
  `applicants_position` varchar(125) DEFAULT NULL COMMENT 'Jabatan Pemohon',
  `service_officer_id` bigint(20) UNSIGNED NOT NULL,
  `service_officer_sign` tinytext,
  `consult_on` varchar(20) DEFAULT NULL COMMENT 'Waktu Konsultasi YYYYMMDD|HH:ii|HH:ii',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Form Konsultasi';

-- --------------------------------------------------------

--
-- Table structure for table `f1qas`
--

CREATE TABLE `f1qas` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `f1qq_id` bigint(20) UNSIGNED DEFAULT NULL,
  `f1qo_id` bigint(20) UNSIGNED DEFAULT NULL,
  `form_id` bigint(20) UNSIGNED DEFAULT NULL,
  `answer` text,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `f1qfs`
--

CREATE TABLE `f1qfs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `form_id` bigint(20) UNSIGNED DEFAULT NULL,
  `f1q_id` bigint(20) UNSIGNED DEFAULT NULL,
  `f1qq_id` bigint(20) UNSIGNED DEFAULT NULL,
  `file` text,
  `url` text,
  `client_name` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `f1qos`
--

CREATE TABLE `f1qos` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `f1qq_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Referensi Id f1qqs',
  `next_step` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Akan Digunakan',
  `serial_number` tinyint(2) DEFAULT '1' COMMENT 'Nomor Urut',
  `option` text,
  `type` tinyint(1) DEFAULT '0' COMMENT '1 => Radio Button\r\n2 => Checkbox\r\n3 => Text\r\n4 => Number\r\n5 => Textarea\r\n6 => Date',
  `is_hidden` tinyint(1) DEFAULT '0',
  `is_vertical` tinyint(1) DEFAULT '0' COMMENT 'Ditampilkan secara vertikal',
  `is_table` tinyint(1) DEFAULT '0' COMMENT 'Ditampilkan dalam bentuk tabel',
  `table_attr` text COMMENT 'Attribut Tabel',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `f1qqs`
--

CREATE TABLE `f1qqs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `parent` bigint(20) UNSIGNED DEFAULT NULL,
  `f1q_id` bigint(20) UNSIGNED DEFAULT NULL,
  `serial_number` tinyint(2) DEFAULT NULL,
  `display_number` varchar(2) DEFAULT NULL,
  `question` text,
  `is_file_upload` tinyint(1) UNSIGNED ZEROFILL NOT NULL DEFAULT '0',
  `is_number_hidden` tinyint(1) UNSIGNED ZEROFILL NOT NULL DEFAULT '0',
  `is_mandatory` tinyint(1) UNSIGNED ZEROFILL NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `f1_manual_assessments`
--

CREATE TABLE `f1_manual_assessments` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `form_id` bigint(20) UNSIGNED DEFAULT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `f1_questionnaire_id` bigint(20) UNSIGNED DEFAULT NULL,
  `f1_manual_assessment_option_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Referensi apabila ada perubahan di tabel utama',
  `answer` tinyint(1) DEFAULT NULL COMMENT 'Diisi point (Skala Nilai)',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `f1_manual_assessment_options`
--

CREATE TABLE `f1_manual_assessment_options` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `f1_questionnaire_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Referensi Item Kuesioner',
  `point` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Nilai / Bobot (Skala Nilai)',
  `description` text COMMENT 'Keterangan dari Skala Nilai',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `f1_questionnaires`
--

CREATE TABLE `f1_questionnaires` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `aspect_id` bigint(20) DEFAULT NULL,
  `num` tinyint(2) DEFAULT '1',
  `indicator_id` bigint(20) UNSIGNED DEFAULT NULL,
  `deskripsi` text,
  `supporting_evidence` text,
  `explanation` text,
  `instrument_type` enum('U','A','B','N') DEFAULT 'U',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `f1_questionnaires`
--

INSERT INTO `f1_questionnaires` (`id`, `aspect_id`, `num`, `indicator_id`, `deskripsi`, `supporting_evidence`, `explanation`, `instrument_type`, `created_at`, `updated_at`, `deleted_at`) VALUES
(82, 9, 2, 164, '<p>BASIKAL MERDEKA</p>', '<p>Gak MINAJ&nbsp;&nbsp;&nbsp;&nbsp;</p>', '<p>APA LOPH</p>', 'U', '2024-02-13 08:56:18', '2024-02-13 08:56:18', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `f1_questionnaire_answers`
--

CREATE TABLE `f1_questionnaire_answers` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `form_id` bigint(20) UNSIGNED DEFAULT NULL,
  `f1_questionnaire_question_id` bigint(20) UNSIGNED DEFAULT NULL,
  `f1_questionnaire_option_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'FK f1_questionnaire_answers.id',
  `answer` text COMMENT 'Jawaban',
  `description` text COMMENT 'Keterangan tambahan',
  `support_data` text COMMENT 'Data Dukung',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Tabel Jawaban dari Pertanyaan Form Kuesioner 1';

-- --------------------------------------------------------

--
-- Table structure for table `f1_questionnaire_files`
--

CREATE TABLE `f1_questionnaire_files` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `form_id` bigint(20) UNSIGNED DEFAULT NULL,
  `f1_questionnaire_id` bigint(20) UNSIGNED DEFAULT NULL,
  `f1_questionnaire_question_id` bigint(20) UNSIGNED DEFAULT NULL,
  `f1_questionnaire_option_id` bigint(20) UNSIGNED DEFAULT NULL,
  `file` text NOT NULL,
  `url` text,
  `client_name` tinytext,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `f1_questionnaire_options`
--

CREATE TABLE `f1_questionnaire_options` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `parent` bigint(20) UNSIGNED DEFAULT NULL,
  `f1_questionnaire_question_id` bigint(20) UNSIGNED DEFAULT NULL,
  `num` int(3) UNSIGNED DEFAULT NULL COMMENT 'Nomor Urut',
  `type` tinyint(1) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'Tipe Pertanyaan (1 = pilihan abc, 2 = checkbox, 3 = radio button, 4 = text, 5 = number, 6 = textarea)',
  `option` text COMMENT 'Bisa dikosongi',
  `value` tinyint(1) NOT NULL DEFAULT '0',
  `score` float DEFAULT '0',
  `is_table` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Apakah berbentuk tabel',
  `is_vertical` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Apakah vertikal',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `f1_questionnaire_questions`
--

CREATE TABLE `f1_questionnaire_questions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `parent` bigint(20) UNSIGNED DEFAULT NULL,
  `f1_questionnaire_id` bigint(20) UNSIGNED DEFAULT NULL,
  `num` int(11) DEFAULT NULL,
  `type` tinyint(1) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'Tipe Nomor (1 = none, 2 = huruf, 3 = angka, 4 = file upload)',
  `question` text,
  `is_file_upload` tinyint(1) NOT NULL DEFAULT '0',
  `score` float NOT NULL DEFAULT '0' COMMENT 'Bobot Penilaian',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Tabel pertanyaan Form Kuesioner 1';

--
-- Dumping data for table `f1_questionnaire_questions`
--

INSERT INTO `f1_questionnaire_questions` (`id`, `parent`, `f1_questionnaire_id`, `num`, `type`, `question`, `is_file_upload`, `score`, `created_at`, `updated_at`, `deleted_at`) VALUES
(120, NULL, 4, 1, 2, 'APA BOLEH Maling?', 0, 0, '2024-02-13 08:58:22', '2024-02-13 08:58:22', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `f2_questionnaires`
--

CREATE TABLE `f2_questionnaires` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `aspect_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Referensi Aspek',
  `indicator_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Referensi Indikator',
  `num` tinyint(2) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'Sebagai Nomor Urut',
  `code` varchar(10) DEFAULT NULL COMMENT 'Kode Indikator',
  `source` text COMMENT 'Data Sumber / Lokasi Data (hanya text karena kelihatannya tidak perlu diindex)',
  `definition` text COMMENT 'Definisi',
  `instrument_type` char(1) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `f2_questionnaire_answers`
--

CREATE TABLE `f2_questionnaire_answers` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `form_id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `f2_questionnaire_id` bigint(20) UNSIGNED NOT NULL,
  `f2_questionnaire_option_id` bigint(20) UNSIGNED NOT NULL COMMENT 'Referensi apabila ada perubahan di tabel utama',
  `answer` tinyint(1) DEFAULT NULL COMMENT 'Diisi point (Skala Nilai)',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `f2_questionnaire_options`
--

CREATE TABLE `f2_questionnaire_options` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `f2_questionnaire_id` bigint(20) UNSIGNED NOT NULL COMMENT 'Referensi Item Kuesioner',
  `point` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Nilai / Bobot (Skala Nilai)',
  `description` text COMMENT 'Keterangan dari Skala Nilai',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `f3_questionnaires`
--

CREATE TABLE `f3_questionnaires` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `aspect_id` bigint(20) UNSIGNED DEFAULT NULL,
  `indicator_id` bigint(20) UNSIGNED DEFAULT NULL,
  `num` tinyint(2) NOT NULL DEFAULT '1',
  `code` varchar(10) DEFAULT NULL,
  `isu` text COMMENT 'Diisi sesuai dengan kolom isu. Isian bisa berupa HTML karena terdapat data yang di BOLD',
  `instrument_type` char(1) DEFAULT NULL COMMENT 'Kode Form atau Instrumen',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `f3_questionnaire_answers`
--

CREATE TABLE `f3_questionnaire_answers` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `form_id` bigint(20) UNSIGNED NOT NULL,
  `f3_responden_id` bigint(20) UNSIGNED NOT NULL,
  `f3_questionnaire_id` bigint(20) UNSIGNED NOT NULL,
  `point` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Derajat penilaian 0 - 5',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `f3_respondens`
--

CREATE TABLE `f3_respondens` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `unit_id` bigint(20) UNSIGNED DEFAULT NULL,
  `nm` tinytext,
  `alamat` text,
  `ntlp` varchar(20) DEFAULT NULL,
  `email` varchar(125) DEFAULT NULL,
  `pekerjaan` varchar(125) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `forms`
--

CREATE TABLE `forms` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `unit_id` bigint(20) UNSIGNED NOT NULL,
  `type` tinyint(1) UNSIGNED DEFAULT NULL COMMENT '1 = Form 1, 2 = Form 2, 3 = Form 3',
  `year_implementation` year(4) DEFAULT NULL,
  `sent_to_assessor` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Flag telah dikirim untuk penilaian',
  `total_score` float NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `groups`
--

CREATE TABLE `groups` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `aspect_id` bigint(20) UNSIGNED DEFAULT NULL,
  `num` int(2) NOT NULL DEFAULT '1',
  `nm` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `groups`
--

INSERT INTO `groups` (`id`, `aspect_id`, `num`, `nm`, `created_at`, `updated_at`, `deleted_at`) VALUES
(20, 9, 2, 'Kelompok Pecel Lele', '2024-02-13 08:20:26', '2024-02-13 08:20:26', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `indicators`
--

CREATE TABLE `indicators` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `aspect_id` bigint(20) UNSIGNED DEFAULT NULL,
  `group_id` bigint(20) UNSIGNED DEFAULT NULL,
  `num` tinyint(2) UNSIGNED DEFAULT '1',
  `code` varchar(10) DEFAULT NULL,
  `nm` text,
  `desc` text,
  `weight` float DEFAULT '0',
  `denominator` tinyint(1) DEFAULT '2',
  `denominator_wof3` tinyint(1) DEFAULT '2',
  `instrument_type` char(1) DEFAULT NULL,
  `year` year(4) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `indicators`
--

INSERT INTO `indicators` (`id`, `aspect_id`, `group_id`, `num`, `code`, `nm`, `desc`, `weight`, `denominator`, `denominator_wof3`, `instrument_type`, `year`, `created_at`, `updated_at`, `deleted_at`) VALUES
(164, 9, 20, 3, '133789', 'asd', 'aassdaasdasd', 0.03, 2, 2, 'A', '2024', '2024-02-13 08:55:21', '2024-02-13 08:55:21', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `indicator_denominators`
--

CREATE TABLE `indicator_denominators` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `indicator_id` bigint(20) UNSIGNED DEFAULT NULL,
  `f2u` tinyint(2) NOT NULL DEFAULT '0',
  `f2a` tinyint(2) NOT NULL DEFAULT '0',
  `f2b` tinyint(2) NOT NULL DEFAULT '0',
  `f3a` tinyint(2) NOT NULL DEFAULT '0',
  `f3b` tinyint(2) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Indikator Denominator';

-- --------------------------------------------------------

--
-- Table structure for table `migrations`
--

CREATE TABLE `migrations` (
  `id` int(10) UNSIGNED NOT NULL,
  `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `office_nomenclatures`
--

CREATE TABLE `office_nomenclatures` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `unit_id` bigint(20) UNSIGNED NOT NULL COMMENT 'Unit Kerja atau OPD',
  `officials` varchar(225) DEFAULT NULL COMMENT 'Nama Pegawai atau Pejabat',
  `name` varchar(225) DEFAULT NULL COMMENT 'Nomenklatur Jabatan',
  `code` varchar(50) DEFAULT NULL COMMENT 'Kode Jabatan atau Kode Organisasi',
  `work_units` varchar(225) DEFAULT NULL COMMENT 'Unit Kerja Eselon'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Nomenklatur Jabatan';

-- --------------------------------------------------------

--
-- Table structure for table `qrcodes`
--

CREATE TABLE `qrcodes` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `code` enum('A','B') NOT NULL DEFAULT 'A',
  `unit_id` bigint(20) UNSIGNED DEFAULT NULL,
  `filename` tinytext,
  `bg_color` varchar(10) DEFAULT NULL,
  `color` varchar(10) DEFAULT NULL,
  `eye_position` tinyint(1) DEFAULT NULL,
  `eye_color` varchar(10) DEFAULT NULL,
  `style` tinyint(1) DEFAULT NULL,
  `year` year(4) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `roles`
--

CREATE TABLE `roles` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `nm` varchar(150) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `short_urls`
--

CREATE TABLE `short_urls` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `destination_url` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `url_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `default_short_url` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `single_use` tinyint(1) NOT NULL,
  `track_visits` tinyint(1) NOT NULL,
  `redirect_status_code` int(11) NOT NULL DEFAULT '301',
  `track_ip_address` tinyint(1) NOT NULL DEFAULT '0',
  `track_operating_system` tinyint(1) NOT NULL DEFAULT '0',
  `track_operating_system_version` tinyint(1) NOT NULL DEFAULT '0',
  `track_browser` tinyint(1) NOT NULL DEFAULT '0',
  `track_browser_version` tinyint(1) NOT NULL DEFAULT '0',
  `track_referer_url` tinyint(1) NOT NULL DEFAULT '0',
  `track_device_type` tinyint(1) NOT NULL DEFAULT '0',
  `activated_at` timestamp NULL DEFAULT '2022-07-15 18:01:14',
  `deactivated_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `short_url_visits`
--

CREATE TABLE `short_url_visits` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `short_url_id` bigint(20) UNSIGNED NOT NULL,
  `ip_address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `operating_system` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `operating_system_version` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `browser` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `browser_version` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `referer_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `device_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `visited_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `units`
--

CREATE TABLE `units` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `parent_id` bigint(20) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Data parent untuk OPD dan UPTD',
  `type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 = SKPD, 1 = OPD, 2 = UPT, 4 = Kecamatan',
  `code` varchar(15) DEFAULT NULL,
  `nm` varchar(125) NOT NULL COMMENT 'Nama Unit',
  `snm` varchar(50) DEFAULT NULL,
  `jp` varchar(125) NOT NULL COMMENT 'Jabatan Pemangku',
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `units`
--

INSERT INTO `units` (`id`, `parent_id`, `type`, `code`, `nm`, `snm`, `jp`, `deleted_at`) VALUES
(1, 0, 0, '312312133', 'AYAM', 'AYA', '', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `unit_scores`
--

CREATE TABLE `unit_scores` (
  `id` int(10) UNSIGNED NOT NULL,
  `unit_id` bigint(20) UNSIGNED DEFAULT NULL,
  `year` year(4) DEFAULT NULL,
  `score` double NOT NULL DEFAULT '0',
  `A1` double NOT NULL DEFAULT '0',
  `A2` double NOT NULL DEFAULT '0',
  `A3` double NOT NULL DEFAULT '0',
  `A4` double NOT NULL DEFAULT '0',
  `A5` double NOT NULL DEFAULT '0',
  `A6` double NOT NULL DEFAULT '0',
  `F2` double NOT NULL DEFAULT '0',
  `F3` double NOT NULL DEFAULT '0',
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Unit Score';

--
-- Indexes for dumped tables
--

--
-- Indexes for table `aspects`
--
ALTER TABLE `aspects`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `configs`
--
ALTER TABLE `configs`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `config_values`
--
ALTER TABLE `config_values`
  ADD PRIMARY KEY (`id`),
  ADD KEY `FK_CONFIG_ID` (`config_id`);

--
-- Indexes for table `consultation_forms`
--
ALTER TABLE `consultation_forms`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `f1qas`
--
ALTER TABLE `f1qas`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `f1qfs`
--
ALTER TABLE `f1qfs`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `f1qos`
--
ALTER TABLE `f1qos`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `f1qqs`
--
ALTER TABLE `f1qqs`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `f1_manual_assessments`
--
ALTER TABLE `f1_manual_assessments`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `f1_manual_assessment_options`
--
ALTER TABLE `f1_manual_assessment_options`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `f1_questionnaires`
--
ALTER TABLE `f1_questionnaires`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `f1_questionnaire_answers`
--
ALTER TABLE `f1_questionnaire_answers`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `f1_questionnaire_files`
--
ALTER TABLE `f1_questionnaire_files`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `f1_questionnaire_options`
--
ALTER TABLE `f1_questionnaire_options`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `f1_questionnaire_questions`
--
ALTER TABLE `f1_questionnaire_questions`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `f2_questionnaires`
--
ALTER TABLE `f2_questionnaires`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `f2_questionnaire_answers`
--
ALTER TABLE `f2_questionnaire_answers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `form_id` (`form_id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `f2_questionnaire_id` (`f2_questionnaire_id`),
  ADD KEY `f2_questionnaire_option_id` (`f2_questionnaire_option_id`);

--
-- Indexes for table `f2_questionnaire_options`
--
ALTER TABLE `f2_questionnaire_options`
  ADD PRIMARY KEY (`id`),
  ADD KEY `f2_questionnaire_id` (`f2_questionnaire_id`);

--
-- Indexes for table `f3_questionnaires`
--
ALTER TABLE `f3_questionnaires`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `f3_questionnaire_answers`
--
ALTER TABLE `f3_questionnaire_answers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `form_id` (`form_id`),
  ADD KEY `f3_questionnaire_id` (`f3_questionnaire_id`);

--
-- Indexes for table `f3_respondens`
--
ALTER TABLE `f3_respondens`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `forms`
--
ALTER TABLE `forms`
  ADD PRIMARY KEY (`id`),
  ADD KEY `unit_id` (`unit_id`);

--
-- Indexes for table `groups`
--
ALTER TABLE `groups`
  ADD PRIMARY KEY (`id`),
  ADD KEY `aspect_id` (`aspect_id`);

--
-- Indexes for table `indicators`
--
ALTER TABLE `indicators`
  ADD PRIMARY KEY (`id`),
  ADD KEY `aspect_id` (`aspect_id`),
  ADD KEY `group_id` (`group_id`);

--
-- Indexes for table `indicator_denominators`
--
ALTER TABLE `indicator_denominators`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `migrations`
--
ALTER TABLE `migrations`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `office_nomenclatures`
--
ALTER TABLE `office_nomenclatures`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `qrcodes`
--
ALTER TABLE `qrcodes`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `roles`
--
ALTER TABLE `roles`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `short_urls`
--
ALTER TABLE `short_urls`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `short_urls_url_key_unique` (`url_key`);

--
-- Indexes for table `short_url_visits`
--
ALTER TABLE `short_url_visits`
  ADD PRIMARY KEY (`id`),
  ADD KEY `short_url_visits_short_url_id_foreign` (`short_url_id`);

--
-- Indexes for table `units`
--
ALTER TABLE `units`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `unit_scores`
--
ALTER TABLE `unit_scores`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `aspects`
--
ALTER TABLE `aspects`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT for table `configs`
--
ALTER TABLE `configs`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `config_values`
--
ALTER TABLE `config_values`
  MODIFY `id` tinyint(2) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

--
-- AUTO_INCREMENT for table `consultation_forms`
--
ALTER TABLE `consultation_forms`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `f1qas`
--
ALTER TABLE `f1qas`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `f1qfs`
--
ALTER TABLE `f1qfs`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `f1qos`
--
ALTER TABLE `f1qos`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `f1qqs`
--
ALTER TABLE `f1qqs`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `f1_manual_assessments`
--
ALTER TABLE `f1_manual_assessments`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `f1_manual_assessment_options`
--
ALTER TABLE `f1_manual_assessment_options`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `f1_questionnaires`
--
ALTER TABLE `f1_questionnaires`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=83;

--
-- AUTO_INCREMENT for table `f1_questionnaire_answers`
--
ALTER TABLE `f1_questionnaire_answers`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `f1_questionnaire_files`
--
ALTER TABLE `f1_questionnaire_files`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `f1_questionnaire_options`
--
ALTER TABLE `f1_questionnaire_options`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `f1_questionnaire_questions`
--
ALTER TABLE `f1_questionnaire_questions`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=121;

--
-- AUTO_INCREMENT for table `f2_questionnaires`
--
ALTER TABLE `f2_questionnaires`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `f2_questionnaire_answers`
--
ALTER TABLE `f2_questionnaire_answers`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `f2_questionnaire_options`
--
ALTER TABLE `f2_questionnaire_options`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `f3_questionnaires`
--
ALTER TABLE `f3_questionnaires`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `f3_questionnaire_answers`
--
ALTER TABLE `f3_questionnaire_answers`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `f3_respondens`
--
ALTER TABLE `f3_respondens`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `forms`
--
ALTER TABLE `forms`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `groups`
--
ALTER TABLE `groups`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

--
-- AUTO_INCREMENT for table `indicators`
--
ALTER TABLE `indicators`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=165;

--
-- AUTO_INCREMENT for table `indicator_denominators`
--
ALTER TABLE `indicator_denominators`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `migrations`
--
ALTER TABLE `migrations`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `office_nomenclatures`
--
ALTER TABLE `office_nomenclatures`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `qrcodes`
--
ALTER TABLE `qrcodes`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `roles`
--
ALTER TABLE `roles`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `short_urls`
--
ALTER TABLE `short_urls`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `short_url_visits`
--
ALTER TABLE `short_url_visits`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `units`
--
ALTER TABLE `units`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `unit_scores`
--
ALTER TABLE `unit_scores`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `f2_questionnaire_answers`
--
ALTER TABLE `f2_questionnaire_answers`
  ADD CONSTRAINT `f2_questionnaire_answers_ibfk_1` FOREIGN KEY (`form_id`) REFERENCES `forms` (`id`) ON DELETE NO ACTION,
  ADD CONSTRAINT `f2_questionnaire_answers_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION,
  ADD CONSTRAINT `f2_questionnaire_answers_ibfk_3` FOREIGN KEY (`f2_questionnaire_id`) REFERENCES `f2_questionnaires` (`id`) ON DELETE NO ACTION;

--
-- Constraints for table `f3_questionnaire_answers`
--
ALTER TABLE `f3_questionnaire_answers`
  ADD CONSTRAINT `f3_questionnaire_answers_ibfk_1` FOREIGN KEY (`form_id`) REFERENCES `forms` (`id`) ON DELETE NO ACTION,
  ADD CONSTRAINT `f3_questionnaire_answers_ibfk_2` FOREIGN KEY (`f3_questionnaire_id`) REFERENCES `f3_questionnaires` (`id`) ON DELETE NO ACTION;

--
-- Constraints for table `forms`
--
ALTER TABLE `forms`
  ADD CONSTRAINT `forms_ibfk_1` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`) ON DELETE NO ACTION;

--
-- Constraints for table `groups`
--
ALTER TABLE `groups`
  ADD CONSTRAINT `groups_ibfk_1` FOREIGN KEY (`aspect_id`) REFERENCES `aspects` (`id`) ON DELETE NO ACTION;

--
-- Constraints for table `indicators`
--
ALTER TABLE `indicators`
  ADD CONSTRAINT `indicators_ibfk_1` FOREIGN KEY (`aspect_id`) REFERENCES `aspects` (`id`) ON DELETE NO ACTION,
  ADD CONSTRAINT `indicators_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE NO ACTION;
COMMIT;

