-- MySQL dump 10.13  Distrib 5.7.42, for Linux (x86_64)
--
-- Host: localhost    Database: school_alerts
-- ------------------------------------------------------
-- Server version	5.7.42-0ubuntu0.18.04.1

/*!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 utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `acknowledgments`
--

DROP TABLE IF EXISTS `acknowledgments`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `acknowledgments` (
  `ack_id` int(11) NOT NULL AUTO_INCREMENT,
  `alert_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `acknowledged_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ack_id`),
  KEY `alert_id` (`alert_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `acknowledgments_ibfk_1` FOREIGN KEY (`alert_id`) REFERENCES `alerts` (`alert_id`),
  CONSTRAINT `acknowledgments_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `acknowledgments`
--

LOCK TABLES `acknowledgments` WRITE;
/*!40000 ALTER TABLE `acknowledgments` DISABLE KEYS */;
/*!40000 ALTER TABLE `acknowledgments` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `alerts`
--

DROP TABLE IF EXISTS `alerts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `alerts` (
  `alert_id` int(11) NOT NULL AUTO_INCREMENT,
  `school_id` int(11) NOT NULL,
  `type` varchar(50) DEFAULT NULL,
  `message` text NOT NULL,
  `status` enum('active','resolved') DEFAULT 'active',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `location` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`alert_id`),
  KEY `fk_school_id` (`school_id`),
  CONSTRAINT `alerts_ibfk_1` FOREIGN KEY (`school_id`) REFERENCES `schools` (`school_id`),
  CONSTRAINT `fk_school_id` FOREIGN KEY (`school_id`) REFERENCES `schools` (`school_id`)
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `alerts`
--

LOCK TABLES `alerts` WRITE;
/*!40000 ALTER TABLE `alerts` DISABLE KEYS */;
INSERT INTO `alerts` VALUES (22,1,'lockdown','Test','resolved','2024-11-12 17:04:55','All rooms 11-12'),(23,1,'earthquake','shake rattle and roll\r\n','resolved','2024-11-12 17:15:40','shar '),(24,1,'fire_drill','drill','resolved','2024-11-12 17:17:53','Drill'),(25,1,'shelter_in_place','all','resolved','2024-11-12 17:28:26','all'),(26,1,'modified_lockdown','123','resolved','2024-11-12 17:35:10','123'),(27,1,'other','No message','resolved','2024-11-12 17:40:13','Unknown location'),(28,1,'lockdown','No message','resolved','2024-11-12 18:49:11','Unknown location'),(29,1,'lockdown','No message','resolved','2024-11-12 19:01:47','Unknown location'),(30,1,'lockdown','No message','resolved','2024-11-12 19:09:35','Unknown location'),(31,1,'fire_drill','No message','resolved','2024-11-12 19:09:46','Unknown location'),(32,1,'other','No message','resolved','2024-11-12 19:09:50','Unknown location'),(33,1,'fire_drill','No message','resolved','2024-11-12 19:23:43','Unknown location'),(34,1,'evacuation','No message','resolved','2024-11-12 19:25:46','Unknown location'),(35,1,'evacuation','No message','resolved','2024-11-12 20:07:16','Unknown location'),(36,1,'other','No message','resolved','2024-11-12 20:11:43','Unknown location'),(37,1,'other','test','resolved','2024-11-12 21:06:26','alert test'),(38,1,'evacuation','get in the chopper','resolved','2024-11-12 21:10:59','Unknown location'),(39,1,'shelter_in_place','get in the chopper2','resolved','2024-11-12 21:16:18','Unknown location'),(40,1,'modified_lockdown','Get in the chopper 3','resolved','2024-11-12 21:18:29','Unknown location'),(41,1,'earthquake','chopper 4','resolved','2024-11-12 21:20:25','Unknown location'),(42,1,'fire_drill','chopper5','resolved','2024-11-12 21:25:25','Unknown location'),(43,1,'evacuation','chopper6','resolved','2024-11-12 21:44:14','Unknown location'),(44,1,'evacuation','chopper 7','resolved','2024-11-12 21:47:32','Unknown location'),(45,1,'evacuation','I love HaLee ','resolved','2024-11-13 01:38:08','Unknown location'),(46,1,'lockdown','I Love you Nicholas','resolved','2024-11-13 01:38:40','Unknown location'),(47,1,'fight','No message','resolved','2024-11-13 15:32:12','Fight in the halls'),(48,1,'Fight','No message','resolved','2024-11-13 15:33:27','In the halls'),(49,1,'Lockdown','<?php\r\nsession_start();\r\nrequire \'config/db.php\';\r\n\r\nini_set(\'display_errors\', 1);\r\nini_set(\'display_startup_errors\', 1);\r\nerror_reporting(E_ALL);\r\n\r\n// Function to send email notifications\r\nfunction sendAlertEmail($type, $location, $message, $recipients) {\r\n    $subject = \"New Alert: $type\";\r\n    $body = \"An alert has been created.\\n\\n\";\r\n    $body .= \"Type: $type\\n\";\r\n    $body .= \"Location: $location\\n\";\r\n    $body .= \"Message: $message\\n\";\r\n    $body .= \"Please check the alert system for more details.\";\r\n\r\n    foreach ($recipients as $recipient) {\r\n        mail($recipient, $subject, $body, \"From: alerts@yourdomain.com\");\r\n    }\r\n}\r\n\r\n// Check if the user is logged in and has admin privileges\r\nif (!isset($_SESSION[\'user_id\']) || $_SESSION[\'role\'] !== \'admin\') {\r\n    header(\'Location: dashboard.php\');\r\n    exit();\r\n}\r\n\r\n// Fetch all schools\r\n$stmt = $pdo->prepare(\"\r\n    SELECT schools.school_id, schools.name AS school_name \r\n    FROM schools\r\n    ORDER BY schools.name\r\n\");\r\n$stmt->execute();\r\n$schools = $stmt->fetchAll(PDO::FETCH_ASSOC);\r\n\r\n// Process form submission\r\nif ($_SERVER[\'REQUEST_METHOD\'] == \'POST\') {\r\n    $school_id = $_POST[\'school_id\'];\r\n    $type = $_POST[\'type\'];\r\n    $location = !empty($_POST[\'location\']) ? $_POST[\'location\'] : \"Unknown location\"; // Default to \"Unknown location\"\r\n    $message = !empty($_POST[\'message\']) ? $_POST[\'message\'] : \"No message\"; // Default to \"No message\"\r\n\r\n    // Fetch notification emails for the selected school\r\n    $stmt = $pdo->prepare(\"SELECT notification_emails FROM schools WHERE school_id = ?\");\r\n    $stmt->execute([$school_id]);\r\n    $school = $stmt->fetch(PDO::FETCH_ASSOC);\r\n    $recipients = array_map(\'trim\', explode(\',\', $school[\'notification_emails\']));\r\n\r\n    try {\r\n        // Insert alert into the database\r\n        $stmt = $pdo->prepare(\"INSERT INTO alerts (school_id, type, location, message, status) VALUES (?, ?, ?, ?, \'active\')\");\r\n        $stmt->execute([$school_id, $type, $location, $message]);\r\n\r\n        // Send email notifications\r\n        sendAlertEmail($type, $location, $message, $recipients);\r\n\r\n        // Redirect to dashboard after creation\r\n        header(\'Location: dashboard.php\');\r\n        exit();\r\n    } catch (PDOException $e) {\r\n        echo \"Error: Could not create alert. \" . $e->getMessage();\r\n    }\r\n}\r\n?>\r\n\r\n<?php include __DIR__ . \'/views/header.php\'; ?>\r\n\r\n<h2>Create Alert</h2>\r\n<div class=\"form-container\">\r\n    <form method=\"POST\" action=\"create_alert.php\">\r\n        <label for=\"school_id\">Select School:</label>\r\n\r\n        <!-- Custom Sortable Dropdown -->\r\n        <div id=\"school-dropdown\" class=\"dropdown\">\r\n            <button type=\"button\" class=\"dropdown-btn\">Select School</button>\r\n            <ul id=\"school-list\" class=\"dropdown-list\">\r\n                <?php \r\n                // Initialize default school to null\r\n                $default_school_id = null;\r\n\r\n                // Loop through the schools\r\n                foreach ($schools as $school): \r\n                    // Set the default school to the first school\r\n                    if ($default_school_id === null) {\r\n                        $default_school_id = $school[\'school_id\'];\r\n                    }\r\n                ?>\r\n                    <li data-id=\"<?= htmlspecialchars($school[\'school_id\']) ?>\" class=\"school-item\">\r\n                        <?= htmlspecialchars($school[\'school_name\']) ?>\r\n                    </li>\r\n                <?php endforeach; ?>\r\n            </ul>\r\n        </div>\r\n\r\n        <input type=\"hidden\" name=\"school_id\" id=\"selected-school-id\" value=\"<?= $default_school_id ?>\">\r\n\r\n        <!-- Type Selection -->\r\n        <label for=\"type\">Type:</label>\r\n        <select name=\"type\" id=\"type\" required>\r\n            <option value=\"Other\">Other</option>\r\n            <option value=\"Lockdown\">Lockdown</option>\r\n            <option value=\"Evacuation\">Evacuation</option>\r\n            <option value=\"Shelter in Place\">Shelter in Place</option>\r\n            <option value=\"Earthquake\">Earthquake</option>\r\n            <option value=\"Fire Drill\">Fire Drill</option>\r\n            <option value=\"Modified Lockdown\">Modified Lockdown</option>\r\n            <option value=\"Help Needed\">Help Needed</option>\r\n            <option value=\"Intruder\">Intruder</option>\r\n            <option value=\"Fight\">Fight</option>\r\n        </select>\r\n\r\n        <label for=\"location\">Location or Room:</label>\r\n        <input type=\"text\" id=\"location\" name=\"location\">\r\n\r\n        <label for=\"message\">Message:</label>\r\n        <textarea name=\"message\" id=\"message\" rows=\"5\"></textarea>\r\n\r\n        <button type=\"submit\" class=\"submit-button\">Create Alert</button>\r\n    </form>\r\n</div>\r\n\r\n<!-- Include Sortable.js -->\r\n<script src=\"https://cdnjs.cloudflare.com/ajax/libs/Sortable/1.14.0/Sortable.min.js\"></script>\r\n\r\n<script>\r\n// Initialize the Sortable list for the custom dropdown\r\nvar el = document.getElementById(\'school-list\');\r\nvar sortable = new Sortable(el, {\r\n    handle: \'.school-item\',  // Allow drag-and-drop for list items\r\n    animation: 150,\r\n    onEnd: function(evt) {\r\n        // After sorting, update the hidden input field with the selected school id\r\n        var selectedSchool = evt.item;\r\n        document.getElementById(\'selected-school-id\').value = selectedSchool.getAttribute(\'data-id\');\r\n    }\r\n});\r\n\r\n// Toggle dropdown visibility on button click\r\ndocument.querySelector(\'.dropdown-btn\').addEventListener(\'click\', function() {\r\n    var dropdownList = document.querySelector(\'.dropdown-list\');\r\n    dropdownList.classList.toggle(\'show\');\r\n});\r\n\r\n// Update dropdown button text based on the selected item\r\ndocument.querySelectorAll(\'.school-item\').forEach(function(item) {\r\n    item.addEventListener(\'click\', function() {\r\n        document.querySelector(\'.dropdown-btn\').textContent = item.textContent;\r\n        document.getElementById(\'selected-school-id\').value = item.getAttribute(\'data-id\');\r\n    });\r\n});\r\n</script>\r\n\r\n<?php include __DIR__ . \'/views/footer.php\'; ?>\r\n','resolved','2024-11-13 17:24:18','All rooms 11-12'),(50,1,'Evacuation','sdfsdf','resolved','2024-11-13 17:24:37','Drillfsd'),(51,6,'Other','No message','resolved','2024-11-13 17:49:14','Unknown location'),(52,6,'Lockdown','No message','resolved','2024-11-13 17:49:27','All rooms 11-12'),(53,1,'Other','No message','resolved','2024-11-13 17:57:53','Unknown location'),(54,1,'Other','No message','active','2024-11-13 18:38:18','Unknown location'),(55,6,'Lockdown','No message','resolved','2024-11-13 18:38:24','Unknown location'),(56,6,'Lockdown','No message','resolved','2024-11-13 18:38:36','Unknown location'),(57,6,'Other','fsdfsdfasd','resolved','2024-11-13 18:41:07','Unknown location'),(58,6,'Other','54165','active','2024-11-13 18:41:57','Unknown location'),(59,6,'Evacuation','gfdsfds','active','2024-11-13 18:49:31','Unknown location'),(60,6,'Evacuation','No message','active','2024-11-13 18:50:29','12316165156'),(61,6,'Lockdown','No message','resolved','2024-11-13 18:52:06','34523423'),(62,6,'Lockdown','2342343','active','2024-11-13 18:56:03','2314234'),(63,7,'Lockdown','No message','resolved','2024-11-13 18:56:40','All rooms 11-12'),(64,1,'Other','No message','active','2024-11-13 19:14:22','vbfdfg'),(65,1,'Other','1111','active','2024-11-13 19:37:02','Unknown location'),(66,7,'Other','22222','resolved','2024-11-13 19:37:10','Unknown location'),(67,1,'Other','No message','active','2024-11-13 19:37:18','Unknown location'),(69,8,'Earthquake','jghj','active','2024-11-13 21:11:49','Unknown location'),(70,7,'Earthquake','oooppp','active','2024-11-13 22:59:23','Unknown location'),(71,7,'Evacuation','tttttttt','resolved','2024-11-13 22:59:44','Unknown location'),(72,7,'Other','asdfgasdfgasdfgf','active','2024-11-13 23:08:34','Unknown location'),(73,7,'Other','yyyyyyyyyyyyyyyyyyyyyyyy','active','2024-11-13 23:12:01','Unknown location'),(74,8,'Other','nao','active','2024-11-13 23:14:36','Unknown location');
/*!40000 ALTER TABLE `alerts` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `districts`
--

DROP TABLE IF EXISTS `districts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `districts` (
  `district_id` int(11) NOT NULL AUTO_INCREMENT,
  `district_name` varchar(100) NOT NULL,
  `contact_email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`district_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `districts`
--

LOCK TABLES `districts` WRITE;
/*!40000 ALTER TABLE `districts` DISABLE KEYS */;
INSERT INTO `districts` VALUES (1,'Wilson Creek School District','nodor@nykoinc.com');
/*!40000 ALTER TABLE `districts` ENABLE KEYS */;
UNLOCK TABLES;

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

DROP TABLE IF EXISTS `schools`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `schools` (
  `school_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `location` varchar(255) DEFAULT NULL,
  `contact_info` varchar(100) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `notification_emails` text,
  `district_id` int(11) DEFAULT NULL,
  `display_order` int(11) DEFAULT '0',
  PRIMARY KEY (`school_id`),
  KEY `fk_district` (`district_id`),
  CONSTRAINT `fk_district` FOREIGN KEY (`district_id`) REFERENCES `districts` (`district_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

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

LOCK TABLES `schools` WRITE;
/*!40000 ALTER TABLE `schools` DISABLE KEYS */;
INSERT INTO `schools` VALUES (1,'Wilson Creek School District','','509-345-2541','400 Navar St','509-855-6396','nodor@nykoinc.com',1,0),(6,'Test School45',NULL,NULL,'123 Test Street','509-855-6396','nodor@nykoinc.com',1,1),(7,'Test School4',NULL,NULL,'123 Test Street','509-855-6396','nodor@nykoinc.com',1,0),(8,'new school',NULL,NULL,'new address','565-34534-234234','email@address.com',1,0);
/*!40000 ALTER TABLE `schools` ENABLE KEYS */;
UNLOCK TABLES;

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

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `school_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('admin','staff') NOT NULL,
  `district_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

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

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES (1,1,'Admin User','admin@example.com','*D4CECBA84F0A507325CD8AA82FCDE04EA8BE56B0','admin',NULL),(3,1,'Admin User','nodor@nykoinc.com','$2y$10$JNUen6057Uay.Xs5o7F.hOglSPe8LnYOH9b.H/5mud9HYWx2btRES','admin',NULL);
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping events for database 'school_alerts'
--

--
-- Dumping routines for database 'school_alerts'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!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 */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-11-15 12:48:05