--
-- Host: localhost    Database: spmb
-- ------------------------------------------------------


--
-- Table structure for table `academic_years`
--

CREATE TABLE `academic_years` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `foundation_id` bigint unsigned DEFAULT '0',
  `name` varchar(20) NOT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_academic_years_unique_id` (`unique_id`),
  KEY `idx_academic_years_foundation_id` (`foundation_id`),
  KEY `idx_academic_years_is_deleted` (`is_deleted`),
  KEY `idx_academic_years_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `academic_years`
--


--
-- Table structure for table `admission_documents`
--

CREATE TABLE `admission_documents` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `max_size` int DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_admission_documents_unique_id` (`unique_id`),
  KEY `idx_admission_documents_is_deleted` (`is_deleted`),
  KEY `idx_admission_documents_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `admission_documents`
--


--
-- Table structure for table `admission_offerings`
--

CREATE TABLE `admission_offerings` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `admission_period_id` bigint unsigned NOT NULL DEFAULT '0',
  `branch_id` bigint unsigned NOT NULL DEFAULT '0',
  `entry_grade_id` bigint unsigned NOT NULL DEFAULT '0',
  `education_track_id` bigint unsigned NOT NULL DEFAULT '0',
  `education_program_id` bigint unsigned DEFAULT '0',
  `applicant_type_id` bigint unsigned DEFAULT '0',
  `admission_pathway_id` bigint unsigned DEFAULT '0',
  `quota` int DEFAULT NULL,
  `sort_order` int DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_admission_offerings_unique_id` (`unique_id`),
  KEY `idx_admission_offerings_period_id` (`admission_period_id`),
  KEY `idx_admission_offerings_branch_id` (`branch_id`),
  KEY `idx_admission_offerings_entry_grade_id` (`entry_grade_id`),
  KEY `idx_admission_offerings_track_id` (`education_track_id`),
  KEY `idx_admission_offerings_program_id` (`education_program_id`),
  KEY `idx_admission_offerings_applicant_type_id` (`applicant_type_id`),
  KEY `idx_admission_offerings_pathway_id` (`admission_pathway_id`),
  KEY `idx_admission_offerings_is_deleted` (`is_deleted`),
  KEY `idx_admission_offerings_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `admission_offerings`
--


--
-- Table structure for table `admission_pathways`
--

CREATE TABLE `admission_pathways` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_admission_pathways_unique_id` (`unique_id`),
  KEY `idx_admission_pathways_is_deleted` (`is_deleted`),
  KEY `idx_admission_pathways_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `admission_pathways`
--


--
-- Table structure for table `admission_periods`
--

CREATE TABLE `admission_periods` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `foundation_id` bigint unsigned DEFAULT '0',
  `academic_year_id` bigint unsigned NOT NULL DEFAULT '0',
  `code` varchar(50) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `registration_start_at` date DEFAULT NULL,
  `registration_end_at` date DEFAULT NULL,
  `announcement_at` date DEFAULT NULL,
  `registration_fee` decimal(15,2) DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_admission_periods_unique_id` (`unique_id`),
  KEY `idx_admission_periods_academic_year_id` (`academic_year_id`),
  KEY `idx_admission_periods_foundation_id` (`foundation_id`),
  KEY `idx_admission_periods_is_deleted` (`is_deleted`),
  KEY `idx_admission_periods_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `admission_periods`
--


--
-- Table structure for table `applicant_types`
--

CREATE TABLE `applicant_types` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_applicant_types_unique_id` (`unique_id`),
  KEY `idx_applicant_types_is_deleted` (`is_deleted`),
  KEY `idx_applicant_types_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `applicant_types`
--


--
-- Table structure for table `application_settings`
--

CREATE TABLE `application_settings` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `institution_name` varchar(255) DEFAULT NULL,
  `tagline` varchar(255) DEFAULT NULL,
  `address` text,
  `email` varchar(255) DEFAULT NULL,
  `telephone` varchar(50) DEFAULT NULL,
  `mobile_phone` varchar(50) DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  `timezone` varchar(50) DEFAULT NULL,
  `smtp_host` varchar(255) DEFAULT NULL,
  `smtp_user` varchar(255) DEFAULT NULL,
  `smtp_pass` varchar(255) DEFAULT NULL,
  `smtp_port` varchar(10) DEFAULT NULL,
  `turnstile_site_key` varchar(255) DEFAULT NULL,
  `turnstile_secret_key` varchar(255) DEFAULT NULL,
  `payment_method` text,
  `registration_terms` text,
  `allow_multiple_registration` tinyint(1) DEFAULT '0',
  `max_registrations` tinyint unsigned DEFAULT '3',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `application_settings`
--

INSERT INTO `application_settings` VALUES (1,'Nama Instansi',NULL,'Jl. Pendidikan No. 1 Kel. Karang Raharja, Kec. Cikarang Utara Kab. Bekasi, Jawa Barat','info@sekolahku.web.id','+62-21 1234 5678','+62 813-1234-5678',NULL,'Asia/Jakarta',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,3,'2026-07-11 15:07:37',NULL);

--
-- Table structure for table `branches`
--

CREATE TABLE `branches` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `foundation_id` bigint unsigned DEFAULT '0',
  `code` varchar(50) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `education_level` varchar(20) DEFAULT NULL COMMENT 'TK, RA, SD, MI, SMP, MTs, SMA, MA, SMK',
  `school_type` varchar(20) DEFAULT NULL,
  `address` text,
  `phone` varchar(50) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_branches_unique_id` (`unique_id`),
  KEY `idx_branches_foundation_id` (`foundation_id`),
  KEY `idx_branches_is_deleted` (`is_deleted`),
  KEY `idx_branches_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `branches`
--


--
-- Table structure for table `education_programs`
--

CREATE TABLE `education_programs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `branch_id` bigint unsigned DEFAULT '0',
  `code` varchar(50) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `category` varchar(30) DEFAULT NULL,
  `display_label` varchar(100) DEFAULT NULL,
  `description` text,
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_education_programs_unique_id` (`unique_id`),
  KEY `idx_education_programs_branch_id` (`branch_id`),
  KEY `idx_education_programs_is_deleted` (`is_deleted`),
  KEY `idx_education_programs_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `education_programs`
--


--
-- Table structure for table `education_tracks`
--

CREATE TABLE `education_tracks` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `branch_id` bigint unsigned DEFAULT '0',
  `code` varchar(50) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `description` text,
  `is_default` tinyint(1) DEFAULT '0',
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_education_tracks_unique_id` (`unique_id`),
  KEY `idx_education_tracks_branch_id` (`branch_id`),
  KEY `idx_education_tracks_is_deleted` (`is_deleted`),
  KEY `idx_education_tracks_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `education_tracks`
--


--
-- Table structure for table `entry_grades`
--

CREATE TABLE `entry_grades` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `branch_id` bigint unsigned NOT NULL DEFAULT '0',
  `code` varchar(50) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `grade_number` int DEFAULT NULL,
  `sort_order` int DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_entry_grades_unique_id` (`unique_id`),
  KEY `idx_entry_grades_branch_id` (`branch_id`),
  KEY `idx_entry_grades_is_deleted` (`is_deleted`),
  KEY `idx_entry_grades_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `entry_grades`
--


--
-- Table structure for table `foundations`
--

CREATE TABLE `foundations` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `code` varchar(50) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `address` text,
  `phone` varchar(50) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_foundations_unique_id` (`unique_id`),
  KEY `idx_foundations_is_deleted` (`is_deleted`),
  KEY `idx_foundations_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `foundations`
--


--
-- Table structure for table `general_document_requirements`
--

CREATE TABLE `general_document_requirements` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `academic_year_id` bigint unsigned NOT NULL DEFAULT '0',
  `document_id` bigint unsigned NOT NULL DEFAULT '0',
  `required` tinyint(1) DEFAULT '1',
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_general_doc_req_unique_id` (`unique_id`),
  KEY `idx_general_doc_req_academic_year_id` (`academic_year_id`),
  KEY `idx_general_doc_req_document_id` (`document_id`),
  KEY `idx_general_doc_req_is_deleted` (`is_deleted`),
  KEY `idx_general_doc_req_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `general_document_requirements`
--


--
-- Table structure for table `general_entrance_exam_scores`
--

CREATE TABLE `general_entrance_exam_scores` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `academic_year_id` bigint unsigned NOT NULL DEFAULT '0',
  `subject_id` bigint unsigned NOT NULL DEFAULT '0',
  `threshold_score` int DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_general_exam_score_unique_id` (`unique_id`),
  KEY `idx_general_exam_score_academic_year_id` (`academic_year_id`),
  KEY `idx_general_exam_score_subject_id` (`subject_id`),
  KEY `idx_general_exam_score_is_deleted` (`is_deleted`),
  KEY `idx_general_exam_score_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `general_entrance_exam_scores`
--


--
-- Table structure for table `general_subject_grade_requirements`
--

CREATE TABLE `general_subject_grade_requirements` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `academic_year_id` bigint unsigned NOT NULL DEFAULT '0',
  `subject_id` bigint unsigned NOT NULL DEFAULT '0',
  `threshold_score` int DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_general_subj_grade_unique_id` (`unique_id`),
  KEY `idx_general_subj_grade_academic_year_id` (`academic_year_id`),
  KEY `idx_general_subj_grade_subject_id` (`subject_id`),
  KEY `idx_general_subj_grade_is_deleted` (`is_deleted`),
  KEY `idx_general_subj_grade_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `general_subject_grade_requirements`
--


--
-- Table structure for table `login_attempts`
--

CREATE TABLE `login_attempts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(45) NOT NULL,
  `identity` varchar(255) NOT NULL,
  `attempt_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_login_attempts_timestamp` (`attempt_time`),
  KEY `idx_login_attempts_ip` (`ip_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `login_attempts`
--


--
-- Table structure for table `offering_document_requirements`
--

CREATE TABLE `offering_document_requirements` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `admission_offering_id` bigint unsigned NOT NULL DEFAULT '0',
  `document_id` bigint unsigned NOT NULL DEFAULT '0',
  `required` tinyint(1) DEFAULT '1',
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_offering_doc_req_unique_id` (`unique_id`),
  KEY `idx_offering_doc_req_offering_id` (`admission_offering_id`),
  KEY `idx_offering_doc_req_document_id` (`document_id`),
  KEY `idx_offering_doc_req_is_deleted` (`is_deleted`),
  KEY `idx_offering_doc_req_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `offering_document_requirements`
--


--
-- Table structure for table `offering_entrance_exam_scores`
--

CREATE TABLE `offering_entrance_exam_scores` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `admission_offering_id` bigint unsigned NOT NULL DEFAULT '0',
  `subject_id` bigint unsigned NOT NULL DEFAULT '0',
  `threshold_score` int DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_offering_exam_score_unique_id` (`unique_id`),
  KEY `idx_offering_exam_score_offering_id` (`admission_offering_id`),
  KEY `idx_offering_exam_score_subject_id` (`subject_id`),
  KEY `idx_offering_exam_score_is_deleted` (`is_deleted`),
  KEY `idx_offering_exam_score_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `offering_entrance_exam_scores`
--


--
-- Table structure for table `offering_subject_grade_requirements`
--

CREATE TABLE `offering_subject_grade_requirements` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `admission_offering_id` bigint unsigned NOT NULL DEFAULT '0',
  `subject_id` bigint unsigned NOT NULL DEFAULT '0',
  `threshold_score` decimal(10,2) DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_offering_subj_grade_unique_id` (`unique_id`),
  KEY `idx_offering_subj_grade_offering_id` (`admission_offering_id`),
  KEY `idx_offering_subj_grade_subject_id` (`subject_id`),
  KEY `idx_offering_subj_grade_is_deleted` (`is_deleted`),
  KEY `idx_offering_subj_grade_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `offering_subject_grade_requirements`
--


--
-- Table structure for table `previous_schools`
--

CREATE TABLE `previous_schools` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `school_name` varchar(255) NOT NULL,
  `school_address` text,
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_previous_schools_unique_id` (`unique_id`),
  KEY `idx_previous_schools_is_deleted` (`is_deleted`),
  KEY `idx_previous_schools_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `previous_schools`
--


--
-- Table structure for table `registration_documents`
--

CREATE TABLE `registration_documents` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `registration_id` bigint unsigned NOT NULL,
  `document_id` bigint unsigned NOT NULL,
  `file` varchar(255) DEFAULT NULL,
  `uploaded_at` datetime DEFAULT NULL,
  `verified` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_reg_doc_reg_id` (`registration_id`),
  KEY `idx_reg_doc_document_id` (`document_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `registration_documents`
--


--
-- Table structure for table `registration_exam_scores`
--

CREATE TABLE `registration_exam_scores` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `registration_id` bigint unsigned NOT NULL,
  `subject_id` bigint unsigned NOT NULL,
  `score` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_reg_exam_reg_id` (`registration_id`),
  KEY `idx_reg_exam_subject_id` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `registration_exam_scores`
--


--
-- Table structure for table `registration_subject_grades`
--

CREATE TABLE `registration_subject_grades` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `registration_id` bigint unsigned NOT NULL,
  `subject_id` bigint unsigned NOT NULL,
  `score` decimal(10,2) DEFAULT NULL,
  `threshold_score` decimal(10,2) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_reg_subj_grade_reg_id` (`registration_id`),
  KEY `idx_reg_subj_grade_subject_id` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `registration_subject_grades`
--


--
-- Table structure for table `registrations`
--

CREATE TABLE `registrations` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `student_id` bigint unsigned NOT NULL DEFAULT '0',
  `admission_offering_id` bigint unsigned NOT NULL DEFAULT '0',
  `admission_period_id` bigint unsigned DEFAULT '0',
  `admission_year` int DEFAULT NULL,
  `registration_number` varchar(100) DEFAULT NULL,
  `submitted` tinyint(1) DEFAULT '0',
  `submitted_at` datetime DEFAULT NULL,
  `submitted_by` bigint unsigned DEFAULT '0',
  `selection_status` varchar(30) DEFAULT '0',
  `selection_notes` text,
  `admission_fee` decimal(15,2) DEFAULT NULL,
  `transfer_proof` varchar(255) DEFAULT NULL,
  `transfer_proof_uploaded_at` datetime DEFAULT NULL,
  `transfer_proof_verified` tinyint(1) DEFAULT '0',
  `transfer_proof_verified_at` datetime DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `restored_at` datetime DEFAULT NULL,
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `restored_by` bigint unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_registrations_unique_id` (`unique_id`),
  KEY `idx_registrations_student_id` (`student_id`),
  KEY `idx_registrations_offering_id` (`admission_offering_id`),
  KEY `idx_registrations_period_id` (`admission_period_id`),
  KEY `idx_registrations_admission_year` (`admission_year`),
  KEY `idx_registrations_selection_status` (`selection_status`),
  KEY `idx_registrations_is_deleted` (`is_deleted`),
  KEY `idx_registrations_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `registrations`
--


--
-- Table structure for table `sessions`
--

CREATE TABLE `sessions` (
  `id` varchar(128) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `data` blob NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_sessions_timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `sessions`
--


--
-- Table structure for table `subjects`
--

CREATE TABLE `subjects` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_subjects_unique_id` (`unique_id`),
  KEY `idx_subjects_is_deleted` (`is_deleted`),
  KEY `idx_subjects_modified_at` (`modified_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `subjects`
--


--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(120) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `full_name` varchar(255) DEFAULT NULL,
  `gender` varchar(1) DEFAULT NULL,
  `birth_place` varchar(100) DEFAULT NULL,
  `birth_place_country_id` varchar(2) DEFAULT NULL,
  `birth_place_province_id` varchar(100) DEFAULT NULL,
  `birth_place_district_id` varchar(100) DEFAULT NULL,
  `birth_date` date DEFAULT NULL,
  `nik` varchar(20) DEFAULT NULL,
  `npwp` varchar(30) DEFAULT NULL,
  `citizenship` varchar(2) DEFAULT NULL,
  `mother_name` varchar(255) DEFAULT NULL,
  `religion_id` tinyint unsigned DEFAULT NULL,
  `kps_receiver` tinyint(1) DEFAULT '0',
  `kps` varchar(50) DEFAULT NULL,
  `mobile_phone` varchar(50) DEFAULT NULL,
  `address` text,
  `village` varchar(100) DEFAULT NULL,
  `rt` varchar(5) DEFAULT NULL,
  `rw` varchar(5) DEFAULT NULL,
  `urban_village` varchar(100) DEFAULT NULL,
  `district_id` varchar(100) DEFAULT NULL,
  `residence_id` tinyint unsigned DEFAULT NULL,
  `transportation_id` tinyint unsigned DEFAULT NULL,
  `post_code` varchar(10) DEFAULT NULL,
  `telephone` varchar(50) DEFAULT NULL,
  `whatsapp` varchar(50) DEFAULT NULL,
  `telegram` varchar(50) DEFAULT NULL,
  `facebook` varchar(100) DEFAULT NULL,
  `twitter` varchar(100) DEFAULT NULL,
  `instagram` varchar(100) DEFAULT NULL,
  `tiktok` varchar(100) DEFAULT NULL,
  `is_admin` tinyint(1) DEFAULT '0',
  `is_student` tinyint(1) DEFAULT '0',
  `is_operator` tinyint(1) DEFAULT '0',
  `account_verified` tinyint(1) DEFAULT '0',
  `account_verified_at` datetime DEFAULT NULL,
  `account_verified_by` bigint unsigned DEFAULT '0',
  `last_logged_in` int unsigned DEFAULT '0',
  `last_logged_in_ip` varchar(50) DEFAULT NULL,
  `password_reset_token` varchar(255) DEFAULT NULL,
  `password_reset_token_request_at` datetime DEFAULT NULL,
  `selector` varchar(255) DEFAULT '',
  `hashed_validator` varchar(255) DEFAULT '',
  `expiry` datetime DEFAULT NULL,
  `nisn` varchar(50) DEFAULT NULL,
  `npsn` varchar(50) DEFAULT NULL,
  `previous_school_id` bigint unsigned DEFAULT NULL,
  `school_name` varchar(255) DEFAULT NULL,
  `school_major` varchar(255) DEFAULT NULL,
  `school_graduation_year` varchar(4) DEFAULT NULL,
  `school_address` text,
  `father_nik` varchar(20) DEFAULT NULL,
  `father_name` varchar(255) DEFAULT NULL,
  `father_birth_date` date DEFAULT NULL,
  `father_education_id` tinyint unsigned DEFAULT NULL,
  `father_income_id` tinyint unsigned DEFAULT NULL,
  `father_occupation_id` tinyint unsigned DEFAULT NULL,
  `mother_nik` varchar(20) DEFAULT NULL,
  `mother_name_parent` varchar(255) DEFAULT NULL,
  `mother_birth_date` date DEFAULT NULL,
  `mother_education_id` tinyint unsigned DEFAULT NULL,
  `mother_income_id` tinyint unsigned DEFAULT NULL,
  `mother_occupation_id` tinyint unsigned DEFAULT NULL,
  `guardian_nik` varchar(20) DEFAULT NULL,
  `guardian_name` varchar(255) DEFAULT NULL,
  `guardian_birth_date` date DEFAULT NULL,
  `guardian_education_id` tinyint unsigned DEFAULT NULL,
  `guardian_occupation_id` tinyint unsigned DEFAULT NULL,
  `guardian_income_id` tinyint unsigned DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `restored_at` datetime DEFAULT NULL,
  `modified_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` bigint unsigned DEFAULT '0',
  `updated_by` bigint unsigned DEFAULT '0',
  `deleted_by` bigint unsigned DEFAULT '0',
  `restored_by` bigint unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_users_email` (`email`),
  UNIQUE KEY `uk_users_unique_id` (`unique_id`),
  KEY `idx_users_is_deleted` (`is_deleted`),
  KEY `idx_users_modified_at` (`modified_at`),
  KEY `idx_users_is_student` (`is_student`),
  KEY `idx_users_is_admin` (`is_admin`),
  KEY `idx_users_is_operator` (`is_operator`),
  KEY `idx_users_religion_id` (`religion_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` VALUES (1,'user:admin@sekolah.sch.id','admin@sekolah.sch.id','$2y$12$QFYLEApqM.dIw4aWophU9Oid54wMRcR6iccgOhrAca2uCHBdKi44O','Administrator',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,'Jl. Pendidikan No. 1 Kel. Karang Raharja, Kec. Cikarang Utara Kab. Bekasi, Jawa Barat 17550 Indonesia',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,0,1,NULL,0,0,NULL,NULL,NULL,'','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,'2026-07-11 15:07:37',NULL,NULL,NULL,'2026-07-11 15:07:37',0,0,0,0);

--
-- Dumping routines for database 'spmb'
--


