--
-- 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 `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`
--


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


