-- phpMyAdmin SQL Dump
-- version 5.2.3
-- https://www.phpmyadmin.net/
--
-- Servidor: localhost:8889
-- Tiempo de generación: 15-02-2026 a las 21:21:38
-- Versión del servidor: 8.0.44
-- Versión de PHP: 8.3.28

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 */;

--
-- Base de datos: `school_management`
--

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

--
-- Estructura de tabla para la tabla `parents`
--

CREATE TABLE `parents` (
  `parent_id` varchar(17) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name_paternal` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name_maternal` varchar(100) NOT NULL,
  `telegram_chat_id` bigint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Volcado de datos para la tabla `parents`
--

INSERT INTO `parents` (`parent_id`, `first_name`, `last_name_paternal`, `last_name_maternal`, `telegram_chat_id`) VALUES
('PAR-8U6A', 'd', 'd', 'd', NULL),
('PAR-AKL98THY65NMQ', 'Ricardo', 'Mendoza', 'Flores', 0),
('PAR-BVC22XDR44PLK', 'Lucía', 'García', 'Paredes', 0),
('PAR-HJK66LZX88CVB', 'Sofía', 'Espinoza', 'Villalba', 0),
('PAR-JJMHYYT6544E3', 'Michel', 'Loverd', 'Loving', 0),
('PAR-JJMHYYT65LKJH', 'Gsus Manuel', 'Salazar', 'Bazan', 0),
('PAR-LKJ99HGF11DSA', 'Roberto', 'Sánchez', 'Vargas', 0),
('PAR-LLPPO9IUY6TR4', 'Maicol', 'Sazalar', 'Bazan', 0),
('PAR-MNB88VCX00POI', 'Gabriela', 'Rojas', 'Díaz', 0),
('PAR-POU00YTR22REW', 'Valeria', 'Campos', 'Arias', 0),
('PAR-QWE33RTY55MNB', 'Carlos', 'Torres', 'Huamán', 0),
('PAR-UIO55PAS77DFG', 'Mauricio', 'Castillo', 'López', 0),
('PAR-YTR77REW99QAZ', 'Andrés', 'Quispe', 'Mamani', 0),
('PAR-ZXC44VBN66LKJ', 'Elena', 'Vásquez', 'Ramos', 0);

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

--
-- Estructura de tabla para la tabla `students`
--

CREATE TABLE `students` (
  `student_id` varchar(17) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name_paternal` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name_maternal` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Volcado de datos para la tabla `students`
--

INSERT INTO `students` (`student_id`, `first_name`, `last_name_paternal`, `last_name_maternal`) VALUES
('STU-BV334455ZXCV', 'Luciana', 'Herrera', 'Paredes'),
('STU-ER998877HJKL', 'Daniela', 'Flores', 'Reyes'),
('STU-LK213001ABCD', 'Mateo', 'Rojas', 'Quispe'),
('STU-MN778899GHJK', 'Diego', 'Ramirez', 'Soto'),
('STU-OP667788LKJH', 'Antonella', 'Silva', 'Campos'),
('STU-PL112244ASDF', 'Camila', 'Vargas', 'Diaz'),
('STU-QW556677UIOP', 'Adrian', 'Morales', 'Salas'),
('STU-RT567893TYUI', 'Sebastian', 'Castro', 'Lopez'),
('STU-TY443322BNML', 'Thiago', 'Gutierrez', 'Navarro'),
('STU-ZX984502QWER', 'Valeria', 'Torres', 'Mendoza');

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

--
-- Estructura de tabla para la tabla `student_attendance_log`
--

CREATE TABLE `student_attendance_log` (
  `id` int UNSIGNED NOT NULL,
  `fk_student_id` varchar(17) NOT NULL,
  `check_type` tinyint NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Volcado de datos para la tabla `student_attendance_log`
--

INSERT INTO `student_attendance_log` (`id`, `fk_student_id`, `check_type`, `created_at`) VALUES
(24, 'STU-BV334455ZXCV', 1, '2026-02-14 17:46:37'),
(25, 'STU-BV334455ZXCV', 1, '2026-02-15 11:33:23'),
(26, 'STU-BV334455ZXCV', 1, '2026-02-15 11:33:50'),
(27, 'STU-BV334455ZXCV', 1, '2026-02-15 11:37:16'),
(28, 'STU-BV334455ZXCV', 2, '2026-02-15 11:37:50'),
(29, 'STU-BV334455ZXCV', 1, '2026-02-15 11:49:52'),
(30, 'STU-BV334455ZXCV', 1, '2026-02-15 11:50:43'),
(31, 'STU-BV334455ZXCV', 1, '2026-02-15 11:55:14'),
(32, 'STU-BV334455ZXCV', 1, '2026-02-15 14:34:46'),
(33, 'STU-BV334455ZXCV', 1, '2026-02-15 14:35:49'),
(34, 'STU-ER998877HJKL', 1, '2026-02-15 14:37:11'),
(35, 'STU-BV334455ZXCV', 1, '2026-02-15 14:43:06'),
(36, 'STU-BV334455ZXCV', 1, '2026-02-15 14:43:55'),
(37, 'STU-ER998877HJKL', 1, '2026-02-15 14:52:41'),
(38, 'STU-BV334455ZXCV', 1, '2026-02-15 14:53:42'),
(39, 'STU-ER998877HJKL', 1, '2026-02-15 14:59:30'),
(40, 'STU-ER998877HJKL', 1, '2026-02-15 15:03:50'),
(41, 'STU-ER998877HJKL', 1, '2026-02-15 15:04:12'),
(42, 'STU-ER998877HJKL', 1, '2026-02-15 15:05:05'),
(43, 'STU-ER998877HJKL', 1, '2026-02-15 15:06:19'),
(44, 'STU-ER998877HJKL', 1, '2026-02-15 15:07:30'),
(45, 'STU-ER998877HJKL', 1, '2026-02-15 15:09:00'),
(46, 'STU-ER998877HJKL', 1, '2026-02-15 15:13:15'),
(47, 'STU-BV334455ZXCV', 1, '2026-02-15 15:18:21'),
(48, 'STU-RT567893TYUI', 1, '2026-02-15 15:23:28'),
(49, 'STU-ER998877HJKL', 1, '2026-02-15 15:23:34'),
(50, 'STU-BV334455ZXCV', 1, '2026-02-15 15:23:40'),
(51, 'STU-LK213001ABCD', 1, '2026-02-15 15:29:53'),
(52, 'STU-RT567893TYUI', 1, '2026-02-15 15:30:00'),
(53, 'STU-ER998877HJKL', 1, '2026-02-15 15:30:06'),
(54, 'STU-BV334455ZXCV', 1, '2026-02-15 15:30:25'),
(55, 'STU-BV334455ZXCV', 1, '2026-02-15 15:33:29'),
(56, 'STU-ER998877HJKL', 1, '2026-02-15 15:33:38'),
(57, 'STU-RT567893TYUI', 1, '2026-02-15 15:33:51'),
(58, 'STU-LK213001ABCD', 1, '2026-02-15 15:34:13'),
(59, 'STU-ER998877HJKL', 1, '2026-02-15 15:36:22'),
(60, 'STU-BV334455ZXCV', 1, '2026-02-15 15:43:22'),
(61, 'STU-LK213001ABCD', 1, '2026-02-15 15:47:07'),
(62, 'STU-ER998877HJKL', 1, '2026-02-15 15:47:17'),
(63, 'STU-ER998877HJKL', 1, '2026-02-15 15:53:12'),
(64, 'STU-BV334455ZXCV', 1, '2026-02-15 15:54:01'),
(65, 'STU-BV334455ZXCV', 1, '2026-02-15 15:57:07'),
(66, 'STU-BV334455ZXCV', 1, '2026-02-15 15:58:20'),
(67, 'STU-ER998877HJKL', 1, '2026-02-15 15:58:25'),
(68, 'STU-RT567893TYUI', 1, '2026-02-15 15:58:45'),
(69, 'STU-BV334455ZXCV', 1, '2026-02-15 16:00:11'),
(70, 'STU-BV334455ZXCV', 1, '2026-02-15 16:01:56'),
(71, 'STU-BV334455ZXCV', 1, '2026-02-15 16:03:47'),
(72, 'STU-ER998877HJKL', 1, '2026-02-15 16:03:52'),
(73, 'STU-BV334455ZXCV', 1, '2026-02-15 16:07:48'),
(74, 'STU-ER998877HJKL', 1, '2026-02-15 16:07:55'),
(75, 'STU-ER998877HJKL', 1, '2026-02-15 16:09:02'),
(76, 'STU-BV334455ZXCV', 1, '2026-02-15 16:10:30'),
(77, 'STU-ER998877HJKL', 1, '2026-02-15 16:10:35'),
(78, 'STU-RT567893TYUI', 1, '2026-02-15 16:11:30'),
(79, 'STU-ER998877HJKL', 1, '2026-02-15 16:11:34'),
(80, 'STU-ER998877HJKL', 1, '2026-02-15 16:12:12'),
(81, 'STU-ER998877HJKL', 1, '2026-02-15 16:13:14'),
(82, 'STU-ER998877HJKL', 1, '2026-02-15 16:14:14'),
(83, 'STU-BV334455ZXCV', 1, '2026-02-15 16:14:45'),
(84, 'STU-RT567893TYUI', 1, '2026-02-15 16:14:52'),
(85, 'STU-BV334455ZXCV', 1, '2026-02-15 16:18:41'),
(86, 'STU-BV334455ZXCV', 1, '2026-02-15 16:19:56'),
(87, 'STU-ER998877HJKL', 1, '2026-02-15 16:20:05'),
(88, 'STU-BV334455ZXCV', 1, '2026-02-15 16:20:22');

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

--
-- Estructura de tabla para la tabla `student_attendance_notification_log`
--

CREATE TABLE `student_attendance_notification_log` (
  `id` int UNSIGNED NOT NULL,
  `fk_student_attendance_log_id` int UNSIGNED NOT NULL,
  `notification_channel` tinyint NOT NULL,
  `api_response_code` varchar(4) DEFAULT NULL,
  `api_response_message` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `notification_status` tinyint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Volcado de datos para la tabla `student_attendance_notification_log`
--

INSERT INTO `student_attendance_notification_log` (`id`, `fk_student_attendance_log_id`, `notification_channel`, `api_response_code`, `api_response_message`, `created_at`, `notification_status`) VALUES
(5, 24, 1, '200', 'OK', '2026-02-14 17:47:17', 1),
(6, 24, 2, '200', 'DONE', '2026-02-14 17:48:20', 1);

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

--
-- Estructura de tabla para la tabla `student_parent`
--

CREATE TABLE `student_parent` (
  `student_id` varchar(17) NOT NULL,
  `parent_id` varchar(17) NOT NULL,
  `relationship_type` enum('Padre','Madre','Padrastro','Madrastra','Abuelo','Abuela','Tío','Tía','Hermano','Hermana') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Volcado de datos para la tabla `student_parent`
--

INSERT INTO `student_parent` (`student_id`, `parent_id`, `relationship_type`) VALUES
('STU-BV334455ZXCV', 'PAR-JJMHYYT65LKJH', 'Padrastro'),
('STU-BV334455ZXCV', 'PAR-YTR77REW99QAZ', 'Padre'),
('STU-ER998877HJKL', 'PAR-BVC22XDR44PLK', 'Hermano'),
('STU-ER998877HJKL', 'PAR-JJMHYYT6544E3', 'Tío'),
('STU-ER998877HJKL', 'PAR-JJMHYYT65LKJH', 'Tío'),
('STU-ER998877HJKL', 'PAR-MNB88VCX00POI', 'Padrastro'),
('STU-ER998877HJKL', 'PAR-POU00YTR22REW', 'Hermano'),
('STU-ER998877HJKL', 'PAR-UIO55PAS77DFG', 'Hermana'),
('STU-ER998877HJKL', 'PAR-YTR77REW99QAZ', 'Padre'),
('STU-LK213001ABCD', 'PAR-BVC22XDR44PLK', 'Tío'),
('STU-LK213001ABCD', 'PAR-JJMHYYT6544E3', 'Hermano'),
('STU-LK213001ABCD', 'PAR-QWE33RTY55MNB', 'Hermana'),
('STU-OP667788LKJH', 'PAR-AKL98THY65NMQ', 'Tía'),
('STU-PL112244ASDF', 'PAR-MNB88VCX00POI', 'Hermana'),
('STU-PL112244ASDF', 'PAR-QWE33RTY55MNB', 'Tío'),
('STU-QW556677UIOP', 'PAR-HJK66LZX88CVB', 'Padre'),
('STU-RT567893TYUI', 'PAR-AKL98THY65NMQ', 'Tía'),
('STU-TY443322BNML', 'PAR-BVC22XDR44PLK', 'Tía'),
('STU-ZX984502QWER', 'PAR-JJMHYYT65LKJH', 'Tía');

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

--
-- Estructura Stand-in para la vista `vw_students_without_parent`
-- (Véase abajo para la vista actual)
--
CREATE TABLE `vw_students_without_parent` (
`first_name` varchar(100)
,`last_name_maternal` varchar(100)
,`last_name_paternal` varchar(100)
,`student_id` varchar(17)
);

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

--
-- Estructura Stand-in para la vista `vw_student_parent_relationships`
-- (Véase abajo para la vista actual)
--
CREATE TABLE `vw_student_parent_relationships` (
`parent_first_name` varchar(100)
,`parent_id` varchar(17)
,`parent_last_name_maternal` varchar(100)
,`parent_last_name_paternal` varchar(100)
,`relationship_type` enum('Padre','Madre','Padrastro','Madrastra','Abuelo','Abuela','Tío','Tía','Hermano','Hermana')
,`student_first_name` varchar(100)
,`student_id` varchar(17)
,`student_last_name_maternal` varchar(100)
,`student_last_name_paternal` varchar(100)
);

--
-- Índices para tablas volcadas
--

--
-- Indices de la tabla `parents`
--
ALTER TABLE `parents`
  ADD PRIMARY KEY (`parent_id`);

--
-- Indices de la tabla `students`
--
ALTER TABLE `students`
  ADD PRIMARY KEY (`student_id`);

--
-- Indices de la tabla `student_attendance_log`
--
ALTER TABLE `student_attendance_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_student_id` (`fk_student_id`);

--
-- Indices de la tabla `student_attendance_notification_log`
--
ALTER TABLE `student_attendance_notification_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_student_attendance_log_id` (`fk_student_attendance_log_id`);

--
-- Indices de la tabla `student_parent`
--
ALTER TABLE `student_parent`
  ADD PRIMARY KEY (`student_id`,`parent_id`),
  ADD KEY `student_parent_ibfk_2` (`parent_id`);

--
-- AUTO_INCREMENT de las tablas volcadas
--

--
-- AUTO_INCREMENT de la tabla `student_attendance_log`
--
ALTER TABLE `student_attendance_log`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=89;

--
-- AUTO_INCREMENT de la tabla `student_attendance_notification_log`
--
ALTER TABLE `student_attendance_notification_log`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

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

--
-- Estructura para la vista `vw_students_without_parent`
--
DROP TABLE IF EXISTS `vw_students_without_parent`;

CREATE VIEW `vw_students_without_parent`  AS SELECT `s`.`student_id` AS `student_id`, `s`.`first_name` AS `first_name`, `s`.`last_name_paternal` AS `last_name_paternal`, `s`.`last_name_maternal` AS `last_name_maternal` FROM (`students` `s` left join `student_parent` `sp` on((`sp`.`student_id` = `s`.`student_id`))) WHERE (`sp`.`student_id` is null) ;

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

--
-- Estructura para la vista `vw_student_parent_relationships`
--
DROP TABLE IF EXISTS `vw_student_parent_relationships`;

CREATE VIEW `vw_student_parent_relationships`  AS SELECT `s`.`student_id` AS `student_id`, `s`.`first_name` AS `student_first_name`, `s`.`last_name_paternal` AS `student_last_name_paternal`, `s`.`last_name_maternal` AS `student_last_name_maternal`, `p`.`parent_id` AS `parent_id`, `p`.`first_name` AS `parent_first_name`, `p`.`last_name_paternal` AS `parent_last_name_paternal`, `p`.`last_name_maternal` AS `parent_last_name_maternal`, `sp`.`relationship_type` AS `relationship_type` FROM ((`student_parent` `sp` join `students` `s` on((`s`.`student_id` = `sp`.`student_id`))) join `parents` `p` on((`p`.`parent_id` = `sp`.`parent_id`))) ;

--
-- Restricciones para tablas volcadas
--

--
-- Filtros para la tabla `student_attendance_log`
--
ALTER TABLE `student_attendance_log`
  ADD CONSTRAINT `student_attendance_log_ibfk_1` FOREIGN KEY (`fk_student_id`) REFERENCES `students` (`student_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

--
-- Filtros para la tabla `student_attendance_notification_log`
--
ALTER TABLE `student_attendance_notification_log`
  ADD CONSTRAINT `student_attendance_notification_log_ibfk_1` FOREIGN KEY (`fk_student_attendance_log_id`) REFERENCES `student_attendance_log` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

--
-- Filtros para la tabla `student_parent`
--
ALTER TABLE `student_parent`
  ADD CONSTRAINT `student_parent_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  ADD CONSTRAINT `student_parent_ibfk_2` FOREIGN KEY (`parent_id`) REFERENCES `parents` (`parent_id`) ON DELETE RESTRICT ON UPDATE 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 */;
