-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Oct 19, 2025 at 04:47 AM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `grade_calculator`
--

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

--
-- Table structure for table `assessments`
--

CREATE TABLE `assessments` (
  `id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `pg_total` int(11) DEFAULT 0,
  `isian_total` int(11) DEFAULT 0,
  `essay_total` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `assessments`
--

INSERT INTO `assessments` (`id`, `class_id`, `name`, `pg_total`, `isian_total`, `essay_total`, `created_at`, `updated_at`) VALUES
(11, 1, 'Asesmen kelas 1 2025', 25, 10, 5, '2025-10-14 12:36:24', '2025-10-14 12:36:24'),
(12, 2, 'Asesmen kelas 8 2025', 25, 10, 5, '2025-10-14 13:53:45', '2025-10-14 13:53:45');

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

--
-- Table structure for table `chapters`
--

CREATE TABLE `chapters` (
  `id` int(11) NOT NULL,
  `assessment_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `chapters`
--

INSERT INTO `chapters` (`id`, `assessment_id`, `name`, `description`, `created_at`) VALUES
(22, 11, 'Bab 1', 'ini lm 1', '2025-10-14 12:36:24'),
(23, 11, 'Bab 2', 'ini lm 2', '2025-10-14 12:36:24'),
(24, 12, 'Bab 1', 'Deskripsi Bab 1', '2025-10-14 13:53:45'),
(25, 12, 'Bab 2', 'Deskripsi Bab 2', '2025-10-14 13:53:45');

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

--
-- Table structure for table `classes`
--

CREATE TABLE `classes` (
  `id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `kktp` decimal(5,2) DEFAULT 75.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `classes`
--

INSERT INTO `classes` (`id`, `school_id`, `name`, `kktp`, `created_at`) VALUES
(1, 1, 'kelas 1', 75.00, '2025-10-14 11:48:58'),
(2, 2, 'kelas 8', 75.00, '2025-10-14 13:44:12');

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

--
-- Table structure for table `learning_objectives`
--

CREATE TABLE `learning_objectives` (
  `id` int(11) NOT NULL,
  `chapter_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `learning_objectives`
--

INSERT INTO `learning_objectives` (`id`, `chapter_id`, `name`, `description`, `created_at`) VALUES
(46, 22, 'TP 1.1', 'ini 1.1', '2025-10-14 12:36:24'),
(47, 22, 'TP 1.2', 'ini 1.2', '2025-10-14 12:36:24'),
(48, 23, 'TP 2.1', 'ini 2.1', '2025-10-14 12:36:24'),
(49, 23, 'TP 2.2', 'ini 2.2', '2025-10-14 12:36:24'),
(50, 24, 'TP 1.1', 'Deskripsi TP 1.1', '2025-10-14 13:53:45'),
(51, 25, 'TP 2.1', 'Deskripsi TP 2.1', '2025-10-14 13:53:45');

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

--
-- Table structure for table `schools`
--

CREATE TABLE `schools` (
  `id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `schools`
--

INSERT INTO `schools` (`id`, `name`, `created_at`) VALUES
(1, 'SDI AL-AZZAMY', '2025-10-14 11:48:50'),
(2, 'SDM', '2025-10-14 13:44:03');

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

--
-- Table structure for table `settings`
--

CREATE TABLE `settings` (
  `id` int(11) NOT NULL,
  `setting_name` varchar(50) NOT NULL,
  `setting_value` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `settings`
--

INSERT INTO `settings` (`id`, `setting_name`, `setting_value`, `created_at`, `updated_at`) VALUES
(1, 'viewer_key', 'viewer123', '2025-10-14 13:02:59', '2025-10-14 13:02:59'),
(2, 'admin_key', 'Mifwan.id01', '2025-10-14 13:43:37', '2025-10-14 13:43:37');

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

--
-- Table structure for table `students`
--

CREATE TABLE `students` (
  `id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `student_number` varchar(50) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `students`
--

INSERT INTO `students` (`id`, `class_id`, `name`, `student_number`, `created_at`) VALUES
(1, 1, 'Wellyanson Xavier', '1234', '2025-10-14 11:49:07'),
(2, 1, 'ADIB ZAINUL MUSTAKIN', '33', '2025-10-14 12:22:59'),
(3, 2, 'Achmad Nor Adha Prayoga', '11', '2025-10-14 13:44:47');

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

--
-- Table structure for table `student_global_answers`
--

CREATE TABLE `student_global_answers` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `assessment_id` int(11) NOT NULL,
  `pg_correct` int(11) DEFAULT 0,
  `isian_correct` int(11) DEFAULT 0,
  `essay_correct` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `student_global_answers`
--

INSERT INTO `student_global_answers` (`id`, `student_id`, `assessment_id`, `pg_correct`, `isian_correct`, `essay_correct`, `created_at`) VALUES
(13, 1, 11, 20, 7, 4, '2025-10-14 12:36:43'),
(14, 3, 12, 22, 9, 5, '2025-10-14 13:54:05');

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

--
-- Table structure for table `student_scores`
--

CREATE TABLE `student_scores` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `chapter_id` int(11) NOT NULL,
  `slm_score` decimal(5,2) DEFAULT 0.00,
  `final_score` decimal(5,2) DEFAULT 0.00,
  `status` varchar(20) DEFAULT 'BELUM KOMPETEN',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `student_scores`
--

INSERT INTO `student_scores` (`id`, `student_id`, `chapter_id`, `slm_score`, `final_score`, `status`, `created_at`, `updated_at`) VALUES
(25, 1, 22, 90.00, 83.63, 'KOMPETEN', '2025-10-14 12:36:43', '2025-10-14 12:36:43'),
(26, 1, 23, 100.00, 88.89, 'KOMPETEN', '2025-10-14 12:36:43', '2025-10-14 12:36:43'),
(27, 3, 24, 99.00, 94.60, 'KOMPETEN', '2025-10-14 13:54:05', '2025-10-14 13:54:05'),
(28, 3, 25, 88.00, 88.89, 'KOMPETEN', '2025-10-14 13:54:05', '2025-10-14 13:54:05');

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

--
-- Table structure for table `tp_scores`
--

CREATE TABLE `tp_scores` (
  `id` int(11) NOT NULL,
  `student_score_id` int(11) NOT NULL,
  `learning_objective_id` int(11) NOT NULL,
  `score` decimal(5,2) DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tp_scores`
--

INSERT INTO `tp_scores` (`id`, `student_score_id`, `learning_objective_id`, `score`, `created_at`, `updated_at`) VALUES
(51, 25, 46, 77.50, '2025-10-14 12:36:43', '2025-10-14 12:36:43'),
(52, 25, 47, 77.00, '2025-10-14 12:36:43', '2025-10-14 12:36:43'),
(53, 26, 48, 77.78, '2025-10-14 12:36:43', '2025-10-14 12:36:43'),
(54, 26, 49, 77.78, '2025-10-14 12:36:43', '2025-10-14 12:36:43'),
(55, 27, 50, 90.19, '2025-10-14 13:54:05', '2025-10-14 13:54:05'),
(56, 28, 51, 89.79, '2025-10-14 13:54:05', '2025-10-14 13:54:05');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `assessments`
--
ALTER TABLE `assessments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `class_id` (`class_id`);

--
-- Indexes for table `chapters`
--
ALTER TABLE `chapters`
  ADD PRIMARY KEY (`id`),
  ADD KEY `assessment_id` (`assessment_id`);

--
-- Indexes for table `classes`
--
ALTER TABLE `classes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `school_id` (`school_id`);

--
-- Indexes for table `learning_objectives`
--
ALTER TABLE `learning_objectives`
  ADD PRIMARY KEY (`id`),
  ADD KEY `chapter_id` (`chapter_id`);

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

--
-- Indexes for table `settings`
--
ALTER TABLE `settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `setting_name` (`setting_name`);

--
-- Indexes for table `students`
--
ALTER TABLE `students`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `student_number` (`student_number`),
  ADD KEY `class_id` (`class_id`);

--
-- Indexes for table `student_global_answers`
--
ALTER TABLE `student_global_answers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_student_assessment` (`student_id`,`assessment_id`),
  ADD KEY `assessment_id` (`assessment_id`);

--
-- Indexes for table `student_scores`
--
ALTER TABLE `student_scores`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_student_chapter` (`student_id`,`chapter_id`),
  ADD KEY `chapter_id` (`chapter_id`);

--
-- Indexes for table `tp_scores`
--
ALTER TABLE `tp_scores`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_score_tp` (`student_score_id`,`learning_objective_id`),
  ADD KEY `learning_objective_id` (`learning_objective_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `assessments`
--
ALTER TABLE `assessments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- AUTO_INCREMENT for table `chapters`
--
ALTER TABLE `chapters`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=26;

--
-- AUTO_INCREMENT for table `classes`
--
ALTER TABLE `classes`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `learning_objectives`
--
ALTER TABLE `learning_objectives`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=52;

--
-- AUTO_INCREMENT for table `schools`
--
ALTER TABLE `schools`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `settings`
--
ALTER TABLE `settings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `students`
--
ALTER TABLE `students`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `student_global_answers`
--
ALTER TABLE `student_global_answers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

--
-- AUTO_INCREMENT for table `student_scores`
--
ALTER TABLE `student_scores`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=29;

--
-- AUTO_INCREMENT for table `tp_scores`
--
ALTER TABLE `tp_scores`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=57;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `assessments`
--
ALTER TABLE `assessments`
  ADD CONSTRAINT `assessments_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `chapters`
--
ALTER TABLE `chapters`
  ADD CONSTRAINT `chapters_ibfk_1` FOREIGN KEY (`assessment_id`) REFERENCES `assessments` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `classes`
--
ALTER TABLE `classes`
  ADD CONSTRAINT `classes_ibfk_1` FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `learning_objectives`
--
ALTER TABLE `learning_objectives`
  ADD CONSTRAINT `learning_objectives_ibfk_1` FOREIGN KEY (`chapter_id`) REFERENCES `chapters` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `students`
--
ALTER TABLE `students`
  ADD CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `student_global_answers`
--
ALTER TABLE `student_global_answers`
  ADD CONSTRAINT `student_global_answers_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `student_global_answers_ibfk_2` FOREIGN KEY (`assessment_id`) REFERENCES `assessments` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `student_scores`
--
ALTER TABLE `student_scores`
  ADD CONSTRAINT `student_scores_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `student_scores_ibfk_2` FOREIGN KEY (`chapter_id`) REFERENCES `chapters` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `tp_scores`
--
ALTER TABLE `tp_scores`
  ADD CONSTRAINT `tp_scores_ibfk_1` FOREIGN KEY (`student_score_id`) REFERENCES `student_scores` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `tp_scores_ibfk_2` FOREIGN KEY (`learning_objective_id`) REFERENCES `learning_objectives` (`id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
