database.sql

-- Advanced Student Management System | Core PHP + MySQL
-- Import in phpMyAdmin. Creates DB, tables, demo users, demo students, sample dynamic fields.

CREATE DATABASE IF NOT EXISTS `student_management_system` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `student_management_system`;

DROP TABLE IF EXISTS `student_field_values`;
DROP TABLE IF EXISTS `custom_fields`;
DROP TABLE IF EXISTS `students`;
DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(150) NOT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('admin','staff') NOT NULL DEFAULT 'staff',
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), UNIQUE KEY `email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Admin: admin@example.com / Admin@123 | Staff: staff@example.com / Staff@123
INSERT INTO `users` (`name`,`email`,`phone`,`password`,`role`) VALUES
('Super Admin','admin@example.com','9876543210','$2y$10$ji4RkbCdpzFTxF4dRhWk9uPt79KrjDv9/h4IWDfsSe6FY5BrIjtF.','admin'),
('Demo Staff','staff@example.com','9876543211','$2y$10$7GRn262L3zxBdzlOh1jz1.aINiMvueTABwBs3QIu/fGhENEgSteMq','staff');

CREATE TABLE `students` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `admission_no` VARCHAR(30) NOT NULL,
  `first_name` VARCHAR(60) NOT NULL,
  `last_name` VARCHAR(60) DEFAULT NULL,
  `email` VARCHAR(150) NOT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `gender` ENUM('Male','Female','Other') NOT NULL DEFAULT 'Male',
  `dob` DATE DEFAULT NULL,
  `course` VARCHAR(50) NOT NULL,
  `section` VARCHAR(10) DEFAULT NULL,
  `roll_no` VARCHAR(20) DEFAULT NULL,
  `admission_date` DATE DEFAULT NULL,
  `guardian_name` VARCHAR(100) DEFAULT NULL,
  `guardian_phone` VARCHAR(20) DEFAULT NULL,
  `address` VARCHAR(255) DEFAULT NULL,
  `city` VARCHAR(60) DEFAULT NULL,
  `state` VARCHAR(60) DEFAULT NULL,
  `pincode` VARCHAR(10) DEFAULT NULL,
  `photo` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('active','inactive','alumni') NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `admission_unique` (`admission_no`),
  UNIQUE KEY `student_email_unique` (`email`),
  KEY `course_idx` (`course`), KEY `status_idx` (`status`), KEY `gender_idx` (`gender`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `custom_fields` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `field_label` VARCHAR(100) NOT NULL,
  `field_name` VARCHAR(100) NOT NULL,
  `field_type` ENUM('text','number','email','date','select','textarea','radio','checkbox') NOT NULL DEFAULT 'text',
  `field_options` TEXT DEFAULT NULL COMMENT 'One option per line (for select/radio/checkbox)',
  `placeholder` VARCHAR(150) DEFAULT NULL,
  `is_required` TINYINT(1) NOT NULL DEFAULT 0,
  `show_in_list` TINYINT(1) NOT NULL DEFAULT 0,
  `sort_order` INT NOT NULL DEFAULT 0,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), UNIQUE KEY `field_name_unique` (`field_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `student_field_values` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `student_id` INT UNSIGNED NOT NULL,
  `field_id` INT UNSIGNED NOT NULL,
  `field_value` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `student_field_unique` (`student_id`,`field_id`),
  CONSTRAINT `sfv_student_fk` FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  CONSTRAINT `sfv_field_fk` FOREIGN KEY (`field_id`) REFERENCES `custom_fields`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sample dynamic fields (Admin can add/edit/delete more from Dynamic Fields module)
INSERT INTO `custom_fields` (`field_label`,`field_name`,`field_type`,`field_options`,`placeholder`,`is_required`,`show_in_list`,`sort_order`) VALUES
('Blood Group','blood_group','select','A+\nA-\nB+\nB-\nO+\nO-\nAB+\nAB-',NULL,0,1,1),
('Transport Required','transport_required','radio','Yes\nNo',NULL,0,0,2),
('Previous School / College','previous_school','text',NULL,'Enter previous school name',0,0,3),
('Scholarship (%)','scholarship_percent','number',NULL,'e.g. 25',0,0,4);

INSERT INTO `students` (`admission_no`,`first_name`,`last_name`,`email`,`phone`,`gender`,`dob`,`course`,`section`,`roll_no`,`admission_date`,`guardian_name`,`guardian_phone`,`address`,`city`,`state`,`pincode`,`status`) VALUES
('STU2026001','Aarav','Sharma','aarav.sharma@example.com','9811111111','Male','2007-05-14','Class 10','A','01','2026-04-02','Rajesh Sharma','9811111110','12 MG Road','Delhi','Delhi','110001','active'),
('STU2026002','Priya','Singh','priya.singh@example.com','9822222222','Female','2008-09-21','Class 9','B','07','2026-04-03','Sunita Singh','9822222220','44 Lake View','Lucknow','Uttar Pradesh','226001','active'),
('STU2026003','Rohan','Verma','rohan.verma@example.com','9833333333','Male','2004-02-11','BCA','A','15','2025-07-15','Manoj Verma','9833333330','8 Station Road','Kanpur','Uttar Pradesh','208001','active'),
('STU2026004','Sneha','Patel','sneha.patel@example.com','9844444444','Female','2003-12-01','MCA','A','03','2025-07-16','Kiran Patel','9844444440','21 River Side','Ahmedabad','Gujarat','380001','active'),
('STU2026005','Arjun','Kumar','arjun.kumar@example.com','9855555555','Male','2006-07-30','Class 12','C','22','2024-04-05','Sanjay Kumar','9855555550','5 Mall Road','Patna','Bihar','800001','alumni');

INSERT INTO `student_field_values` (`student_id`,`field_id`,`field_value`) VALUES
(1,1,'B+'),(1,2,'Yes'),(2,1,'A+'),(2,2,'No'),(3,1,'O+'),(3,4,'25'),(4,1,'AB+'),(5,1,'B-');