-- Database Schema for Saran DEO Portal

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+05:30";

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

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `full_name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('DEO','BEO','HM','Teacher','Parents') NOT NULL,
  `school_id` int(11) DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `grievances`
--

DROP TABLE IF EXISTS `grievances`;
CREATE TABLE `grievances` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `status` enum('Pending','In Progress','Resolved','Rejected') DEFAULT 'Pending',
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `inspections`
--

DROP TABLE IF EXISTS `inspections`;
CREATE TABLE `inspections` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `inspector_id` int(11) NOT NULL,
  `school_name` varchar(255) NOT NULL,
-- ... (rest of cols)

DROP TABLE IF EXISTS `leaves`;
CREATE TABLE `leaves` (
-- ...

DROP TABLE IF EXISTS `funds`;
CREATE TABLE `funds` (
-- ...

DROP TABLE IF EXISTS `video_meetings`;
CREATE TABLE `video_meetings` (
-- ...

DROP TABLE IF EXISTS `schools`;
CREATE TABLE `schools` (
-- ...

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
-- ...
  `inspector_id` int(11) NOT NULL,
  `school_name` varchar(255) NOT NULL,
  `visit_date` date NOT NULL,
  `report_file` varchar(255) DEFAULT NULL,
  `comments` text,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `leaves`
--

CREATE TABLE `leaves` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `leave_type` varchar(50) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `reason` text NOT NULL,
  `status` enum('Pending','Approved','Rejected') DEFAULT 'Pending',
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `funds`
--

CREATE TABLE `funds` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_name` varchar(255) NOT NULL,
  `allocated_amount` decimal(15,2) NOT NULL,
  `utilized_amount` decimal(15,2) DEFAULT 0.00,
  `uc_status` enum('Pending','Submitted','Approved') DEFAULT 'Pending',
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `video_meetings`
--

CREATE TABLE `video_meetings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `meeting_link` varchar(255) NOT NULL,
  `created_by` varchar(50) NOT NULL,
  `scheduled_time` datetime NOT NULL,
  `status` enum('Active','Ended') DEFAULT 'Active',
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

CREATE TABLE `schools` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `udise_code` varchar(50) NOT NULL UNIQUE,
  `name` varchar(255) NOT NULL,
  `block` varchar(100) NOT NULL,
  `cluster` varchar(100) DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `class` varchar(20) NOT NULL,
  `roll_no` varchar(50) NOT NULL,
  `guardian_name` varchar(100) DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`school_id`) REFERENCES `schools`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

INSERT INTO `users` (`full_name`, `email`, `password`, `role`) VALUES
('District Officer', 'deo@saran.gov.in', 'password123', 'DEO'),
('Block Officer', 'beo@saran.gov.in', 'password123', 'BEO'),
('Head Master', 'hm@school.edu', 'password123', 'HM'),
('Amit Teacher', 'teacher@school.edu', 'password123', 'Teacher'),
('Ravi Parent', 'parent@gmail.com', 'password123', 'Parents');

COMMIT;