CREATE DATABASE IF NOT EXISTS student_id_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE student_id_system;
CREATE TABLE schools(id INT AUTO_INCREMENT PRIMARY KEY,school_name VARCHAR(200) NOT NULL,code VARCHAR(50) UNIQUE NOT NULL,address TEXT,mobile VARCHAR(30),email VARCHAR(150),status TINYINT DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE users(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150),username VARCHAR(100) UNIQUE,password VARCHAR(255),role ENUM('admin','school') DEFAULT 'school',school_id INT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE students(id INT AUTO_INCREMENT PRIMARY KEY,school_id INT NOT NULL,admission_no VARCHAR(100),student_name VARCHAR(200) NOT NULL,class_name VARCHAR(50),section VARCHAR(50),roll_no VARCHAR(50),dob VARCHAR(30),blood_group VARCHAR(20),father_name VARCHAR(200),mother_name VARCHAR(200),mobile VARCHAR(30),address TEXT,aadhar_no VARCHAR(50),photo VARCHAR(255),status ENUM('pending','verified','approved') DEFAULT 'pending',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX(school_id));
INSERT INTO users(name,username,password,role) VALUES('Administrator','admin','$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC5W7RjQ6j2H3v6w1h2','admin');