-- StepUp Academy MySQL 8 / MariaDB 10.4+ schema and seed data.
-- Create a database in cPanel and select that database in phpMyAdmin before importing.
SET NAMES utf8mb4; SET time_zone = '+06:00';
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS demo_data_students,payment_allocations,payments,invoice_items,invoices,fee_structures,fee_categories,attendance,marks,examination_subjects,examinations,timetables,teacher_subjects,enrollments,student_guardians,students,guardians,teachers,batches,sections,subjects,classes,academic_years,admission_applications,recurring_expenses,transfers,expenses,payroll,account_transactions,financial_accounts,receipts,vouchers,notices,notifications,website_pages,website_settings,conversations,messages,audit_logs,backup_records,login_attempts,system_settings,role_permissions,permissions,users,roles;
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE roles(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) NOT NULL UNIQUE,description VARCHAR(255)) ENGINE=InnoDB;
CREATE TABLE permissions(id INT AUTO_INCREMENT PRIMARY KEY,code VARCHAR(100) NOT NULL UNIQUE,description VARCHAR(255));
CREATE TABLE role_permissions(role_id INT NOT NULL,permission_id INT NOT NULL,PRIMARY KEY(role_id,permission_id),FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE,FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE);
CREATE TABLE users(id INT AUTO_INCREMENT PRIMARY KEY,role_id INT NOT NULL,name VARCHAR(120) NOT NULL,username VARCHAR(80) NOT NULL UNIQUE,email VARCHAR(150),password_hash VARCHAR(255) NOT NULL,must_change_password TINYINT(1) NOT NULL DEFAULT 1,status ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',created_at DATETIME NOT NULL,updated_at DATETIME NULL,FOREIGN KEY(role_id) REFERENCES roles(id),INDEX(role_id,status));
CREATE TABLE academic_years(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30) NOT NULL UNIQUE,is_current TINYINT(1) NOT NULL DEFAULT 0);
CREATE TABLE classes(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(60) NOT NULL UNIQUE,sort_order INT DEFAULT 0);
CREATE TABLE sections(id INT AUTO_INCREMENT PRIMARY KEY,class_id INT NOT NULL,name VARCHAR(30) NOT NULL,UNIQUE(class_id,name),FOREIGN KEY(class_id) REFERENCES classes(id) ON DELETE CASCADE);
CREATE TABLE batches(id INT AUTO_INCREMENT PRIMARY KEY,class_id INT NOT NULL,section_id INT NULL,name VARCHAR(80) NOT NULL,capacity INT DEFAULT 40,status VARCHAR(20) DEFAULT 'Active',FOREIGN KEY(class_id) REFERENCES classes(id),FOREIGN KEY(section_id) REFERENCES sections(id));
CREATE TABLE subjects(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL,class_id INT NULL,code VARCHAR(20),UNIQUE(name,class_id));
CREATE TABLE guardians(id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NULL,name VARCHAR(120) NOT NULL,phone VARCHAR(25) NOT NULL,email VARCHAR(150),address TEXT,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,INDEX(phone));
CREATE TABLE students(id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NULL,student_id VARCHAR(30) NOT NULL UNIQUE,admission_no VARCHAR(30) NOT NULL UNIQUE,name_bn VARCHAR(120) NOT NULL,name_en VARCHAR(120),birth_date DATE NULL,phone VARCHAR(25),address TEXT,admission_date DATE NOT NULL,status ENUM('Active','Inactive','Archived') NOT NULL DEFAULT 'Active',photo VARCHAR(255),FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,INDEX(name_bn),INDEX(status));
CREATE TABLE demo_data_students(student_id INT PRIMARY KEY,FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE CASCADE);
CREATE TABLE student_guardians(student_id INT NOT NULL,guardian_id INT NOT NULL,relationship_name VARCHAR(50),is_primary TINYINT(1) DEFAULT 0,PRIMARY KEY(student_id,guardian_id),FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE CASCADE,FOREIGN KEY(guardian_id) REFERENCES guardians(id) ON DELETE CASCADE);
CREATE TABLE enrollments(id INT AUTO_INCREMENT PRIMARY KEY,student_id INT NOT NULL,academic_year_id INT NOT NULL,class_id INT NOT NULL,section_id INT NULL,batch_id INT NULL,roll_no VARCHAR(20),status VARCHAR(20) DEFAULT 'Active',UNIQUE(student_id,academic_year_id),FOREIGN KEY(student_id) REFERENCES students(id),FOREIGN KEY(academic_year_id) REFERENCES academic_years(id),FOREIGN KEY(class_id) REFERENCES classes(id),FOREIGN KEY(section_id) REFERENCES sections(id),FOREIGN KEY(batch_id) REFERENCES batches(id));
CREATE TABLE teachers(id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NULL,employee_id VARCHAR(30) NOT NULL UNIQUE,name VARCHAR(120) NOT NULL,phone VARCHAR(25),qualification VARCHAR(255),join_date DATE,status ENUM('Active','Inactive') DEFAULT 'Active',salary DECIMAL(12,2) DEFAULT 0,photo VARCHAR(255),FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE teacher_subjects(id INT AUTO_INCREMENT PRIMARY KEY,teacher_id INT NOT NULL,subject_id INT NOT NULL,class_id INT NULL,batch_id INT NULL,FOREIGN KEY(teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,FOREIGN KEY(subject_id) REFERENCES subjects(id),FOREIGN KEY(class_id) REFERENCES classes(id),FOREIGN KEY(batch_id) REFERENCES batches(id));
CREATE TABLE timetables(id INT AUTO_INCREMENT PRIMARY KEY,batch_id INT NOT NULL,subject_id INT NOT NULL,teacher_id INT NOT NULL,weekday TINYINT NOT NULL,start_time TIME NOT NULL,end_time TIME NOT NULL,room VARCHAR(50),is_published TINYINT(1) DEFAULT 1,FOREIGN KEY(batch_id) REFERENCES batches(id),FOREIGN KEY(subject_id) REFERENCES subjects(id),FOREIGN KEY(teacher_id) REFERENCES teachers(id),UNIQUE(batch_id,weekday,start_time));
CREATE TABLE attendance(id INT AUTO_INCREMENT PRIMARY KEY,student_id INT NOT NULL,batch_id INT NULL,attendance_date DATE NOT NULL,status ENUM('Present','Absent','Late','Leave') NOT NULL,marked_by INT NOT NULL,remarks VARCHAR(255),created_at DATETIME NOT NULL,UNIQUE(student_id,batch_id,attendance_date),FOREIGN KEY(student_id) REFERENCES students(id),FOREIGN KEY(batch_id) REFERENCES batches(id),FOREIGN KEY(marked_by) REFERENCES users(id));
CREATE TABLE examinations(id INT AUTO_INCREMENT PRIMARY KEY,academic_year_id INT NOT NULL,name VARCHAR(120) NOT NULL,exam_date DATE,status ENUM('Draft','Published','Closed') DEFAULT 'Draft',FOREIGN KEY(academic_year_id) REFERENCES academic_years(id));
CREATE TABLE examination_subjects(id INT AUTO_INCREMENT PRIMARY KEY,examination_id INT NOT NULL,subject_id INT NOT NULL,class_id INT NOT NULL,total_marks DECIMAL(6,2) NOT NULL,pass_marks DECIMAL(6,2) NOT NULL,FOREIGN KEY(examination_id) REFERENCES examinations(id) ON DELETE CASCADE,FOREIGN KEY(subject_id) REFERENCES subjects(id),FOREIGN KEY(class_id) REFERENCES classes(id));
CREATE TABLE marks(id INT AUTO_INCREMENT PRIMARY KEY,examination_subject_id INT NOT NULL,student_id INT NOT NULL,marks_obtained DECIMAL(6,2) NOT NULL,grade VARCHAR(4),gpa DECIMAL(3,2),UNIQUE(examination_subject_id,student_id),FOREIGN KEY(examination_subject_id) REFERENCES examination_subjects(id) ON DELETE CASCADE,FOREIGN KEY(student_id) REFERENCES students(id));
CREATE TABLE fee_categories(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL UNIQUE,is_recurring TINYINT(1) DEFAULT 0);
CREATE TABLE fee_structures(id INT AUTO_INCREMENT PRIMARY KEY,academic_year_id INT NOT NULL,class_id INT NOT NULL,fee_category_id INT NOT NULL,amount DECIMAL(12,2) NOT NULL,FOREIGN KEY(academic_year_id) REFERENCES academic_years(id),FOREIGN KEY(class_id) REFERENCES classes(id),FOREIGN KEY(fee_category_id) REFERENCES fee_categories(id),UNIQUE(academic_year_id,class_id,fee_category_id));
CREATE TABLE invoices(id INT AUTO_INCREMENT PRIMARY KEY,invoice_no VARCHAR(40) NOT NULL UNIQUE,student_id INT NOT NULL,academic_year_id INT NOT NULL,billing_period VARCHAR(20),total_amount DECIMAL(12,2) NOT NULL,discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0,paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0,due_amount DECIMAL(12,2) NOT NULL,status ENUM('Unpaid','Partial','Paid','Cancelled') NOT NULL DEFAULT 'Unpaid',issue_date DATE NOT NULL,due_date DATE NULL,FOREIGN KEY(student_id) REFERENCES students(id),FOREIGN KEY(academic_year_id) REFERENCES academic_years(id),INDEX(student_id,status));
CREATE TABLE invoice_items(id INT AUTO_INCREMENT PRIMARY KEY,invoice_id INT NOT NULL,fee_category_id INT NULL,description VARCHAR(255) NOT NULL,amount DECIMAL(12,2) NOT NULL,FOREIGN KEY(invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,FOREIGN KEY(fee_category_id) REFERENCES fee_categories(id));
CREATE TABLE financial_accounts(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL,type ENUM('Cash','Bank','Mobile Banking') NOT NULL,opening_balance DECIMAL(12,2) DEFAULT 0,status VARCHAR(20) DEFAULT 'Active');
CREATE TABLE payments(id INT AUTO_INCREMENT PRIMARY KEY,receipt_no VARCHAR(40) NOT NULL UNIQUE,student_id INT NOT NULL,financial_account_id INT NOT NULL,amount DECIMAL(12,2) NOT NULL,payment_date DATE NOT NULL,method VARCHAR(40),received_by INT NOT NULL,notes VARCHAR(255),FOREIGN KEY(student_id) REFERENCES students(id),FOREIGN KEY(financial_account_id) REFERENCES financial_accounts(id),FOREIGN KEY(received_by) REFERENCES users(id));
CREATE TABLE payment_allocations(id INT AUTO_INCREMENT PRIMARY KEY,payment_id INT NOT NULL,invoice_id INT NOT NULL,amount DECIMAL(12,2) NOT NULL,UNIQUE(payment_id,invoice_id),FOREIGN KEY(payment_id) REFERENCES payments(id) ON DELETE CASCADE,FOREIGN KEY(invoice_id) REFERENCES invoices(id));
CREATE TABLE account_transactions(id INT AUTO_INCREMENT PRIMARY KEY,account_id INT NOT NULL,transaction_date DATE NOT NULL,type ENUM('Credit','Debit') NOT NULL,amount DECIMAL(12,2) NOT NULL,reference_type VARCHAR(30),reference_id INT,note VARCHAR(255),FOREIGN KEY(account_id) REFERENCES financial_accounts(id),INDEX(account_id,transaction_date));
CREATE TABLE transfers(id INT AUTO_INCREMENT PRIMARY KEY,from_account_id INT NOT NULL,to_account_id INT NOT NULL,amount DECIMAL(12,2) NOT NULL,transfer_date DATE NOT NULL,notes VARCHAR(255),created_by INT NOT NULL,FOREIGN KEY(from_account_id) REFERENCES financial_accounts(id),FOREIGN KEY(to_account_id) REFERENCES financial_accounts(id),FOREIGN KEY(created_by) REFERENCES users(id));
CREATE TABLE expenses(id INT AUTO_INCREMENT PRIMARY KEY,financial_account_id INT NULL,expense_date DATE NOT NULL,category VARCHAR(100) NOT NULL,amount DECIMAL(12,2) NOT NULL,description VARCHAR(255),created_by INT NOT NULL,FOREIGN KEY(financial_account_id) REFERENCES financial_accounts(id),FOREIGN KEY(created_by) REFERENCES users(id));
CREATE TABLE recurring_expenses(id INT AUTO_INCREMENT PRIMARY KEY,financial_account_id INT NULL,category VARCHAR(100) NOT NULL,amount DECIMAL(12,2) NOT NULL,frequency ENUM('Monthly','Yearly') NOT NULL,next_due_date DATE NOT NULL,is_active TINYINT(1) NOT NULL DEFAULT 1,FOREIGN KEY(financial_account_id) REFERENCES financial_accounts(id));
CREATE TABLE payroll(id INT AUTO_INCREMENT PRIMARY KEY,teacher_id INT NOT NULL,pay_month CHAR(7) NOT NULL,basic_amount DECIMAL(12,2) NOT NULL,deduction_amount DECIMAL(12,2) DEFAULT 0,net_amount DECIMAL(12,2) NOT NULL,status VARCHAR(20) DEFAULT 'Pending',UNIQUE(teacher_id,pay_month),FOREIGN KEY(teacher_id) REFERENCES teachers(id));
CREATE TABLE receipts(id INT AUTO_INCREMENT PRIMARY KEY,payment_id INT NOT NULL UNIQUE,receipt_no VARCHAR(40) NOT NULL UNIQUE,issued_at DATETIME NOT NULL,FOREIGN KEY(payment_id) REFERENCES payments(id) ON DELETE CASCADE);
CREATE TABLE vouchers(id INT AUTO_INCREMENT PRIMARY KEY,voucher_no VARCHAR(40) NOT NULL UNIQUE,expense_id INT NULL,issued_at DATETIME NOT NULL,FOREIGN KEY(expense_id) REFERENCES expenses(id) ON DELETE SET NULL);
CREATE TABLE admission_applications(id INT AUTO_INCREMENT PRIMARY KEY,application_no VARCHAR(40) NOT NULL UNIQUE,student_name VARCHAR(120) NOT NULL,guardian_name VARCHAR(120) NOT NULL,phone VARCHAR(25) NOT NULL,class_requested VARCHAR(50),status ENUM('Pending','Approved','Rejected') NOT NULL DEFAULT 'Pending',submitted_at DATETIME NOT NULL,reviewed_by INT NULL,reviewed_at DATETIME NULL,FOREIGN KEY(reviewed_by) REFERENCES users(id));
CREATE TABLE notices(id INT AUTO_INCREMENT PRIMARY KEY,title VARCHAR(200) NOT NULL,body TEXT NOT NULL,audience VARCHAR(50) DEFAULT 'All',status ENUM('Draft','Published') DEFAULT 'Draft',published_at DATETIME NULL,created_by INT NOT NULL,FOREIGN KEY(created_by) REFERENCES users(id));
CREATE TABLE notifications(id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NOT NULL,title VARCHAR(200),body TEXT,is_read TINYINT(1) DEFAULT 0,created_at DATETIME NOT NULL,FOREIGN KEY(user_id) REFERENCES users(id));
CREATE TABLE website_pages(id INT AUTO_INCREMENT PRIMARY KEY,slug VARCHAR(100) NOT NULL UNIQUE,title VARCHAR(200),body LONGTEXT,status VARCHAR(20) DEFAULT 'Published');
CREATE TABLE website_settings(id INT AUTO_INCREMENT PRIMARY KEY,setting_key VARCHAR(100) NOT NULL UNIQUE,setting_value TEXT);
CREATE TABLE conversations(id INT AUTO_INCREMENT PRIMARY KEY,subject VARCHAR(200),created_at DATETIME NOT NULL);
CREATE TABLE messages(id INT AUTO_INCREMENT PRIMARY KEY,conversation_id INT NOT NULL,sender_id INT NOT NULL,body TEXT NOT NULL,created_at DATETIME NOT NULL,FOREIGN KEY(conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,FOREIGN KEY(sender_id) REFERENCES users(id));
CREATE TABLE audit_logs(id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id INT NOT NULL,action VARCHAR(100) NOT NULL,entity VARCHAR(100),entity_id INT NULL,ip_address VARCHAR(45),created_at DATETIME NOT NULL,FOREIGN KEY(user_id) REFERENCES users(id),INDEX(user_id,created_at));
CREATE TABLE backup_records(id INT AUTO_INCREMENT PRIMARY KEY,file_name VARCHAR(255),created_by INT,created_at DATETIME NOT NULL,FOREIGN KEY(created_by) REFERENCES users(id));
CREATE TABLE login_attempts(id BIGINT AUTO_INCREMENT PRIMARY KEY,ip_address VARCHAR(45) NOT NULL,username VARCHAR(80),attempted_at DATETIME NOT NULL,INDEX(ip_address,attempted_at));
CREATE TABLE system_settings(setting_key VARCHAR(100) PRIMARY KEY,setting_value TEXT NOT NULL);
INSERT INTO roles(name,description) VALUES ('Super Admin','পূর্ণ নিয়ন্ত্রণ'),('Admin','একাডেমিক প্রশাসন'),('Accountant','আর্থিক ব্যবস্থাপনা'),('Teacher','শিক্ষক পোর্টাল'),('Student','শিক্ষার্থী পোর্টাল'),('Parent/Guardian','অভিভাবক পোর্টাল');
INSERT INTO permissions(code,description) VALUES ('all','All modules'),('dashboard.view','Dashboard'),('students.manage','Students'),('admissions.manage','Admission review'),('staff.manage','Teacher and guardian management'),('academics.manage','Academic setup and routine'),('finance.manage','Fees, payments and accounting'),('attendance.manage','Attendance'),('results.manage','Exams and results'),('reports.view','Reports'),('notices.manage','Notice publishing'),('website.manage','Website content'),('imports.manage','Import and export'),('users.manage','User administration'),('settings.manage','System settings and demo cleanup'),('backups.manage','Backup and restore'),('portals.manage','Portal account linking'),('portal.view','Student and parent portal'),('profile.password','Change own password');
INSERT INTO role_permissions SELECT 1,id FROM permissions;
INSERT INTO role_permissions(role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON (r.name='Admin' AND p.code IN ('dashboard.view','students.manage','admissions.manage','staff.manage','academics.manage','attendance.manage','results.manage','finance.manage','reports.view','notices.manage','website.manage','imports.manage','profile.password')) OR (r.name='Accountant' AND p.code IN ('dashboard.view','finance.manage','reports.view','profile.password')) OR (r.name='Teacher' AND p.code IN ('dashboard.view','attendance.manage','results.manage','portal.view','profile.password')) OR (r.name='Student' AND p.code IN ('dashboard.view','portal.view','profile.password')) OR (r.name='Parent/Guardian' AND p.code IN ('dashboard.view','portal.view','profile.password'));
-- Initial credentials: username superadmin / password Admin@12345 (change required on first login)
INSERT INTO users(role_id,name,username,email,password_hash,must_change_password,status,created_at) VALUES (1,'System Super Admin','superadmin','admin@stepup.local','$2y$10$iMuXpiJVQnlYM6r32rgqsODsl4c4YxJOzC8W1RPyzj8M9Ox7A01xK','1','Active',NOW()),(2,'Academic Admin','admin','admin@stepup.local','$2y$10$iMuXpiJVQnlYM6r32rgqsODsl4c4YxJOzC8W1RPyzj8M9Ox7A01xK','1','Inactive',NOW()),(3,'Accounts Officer','accountant','accounts@stepup.local','$2y$10$iMuXpiJVQnlYM6r32rgqsODsl4c4YxJOzC8W1RPyzj8M9Ox7A01xK','1','Inactive',NOW());
-- Disabled portal demo accounts are linked below; activate only after setting a unique password.
INSERT INTO users(role_id,name,username,email,password_hash,must_change_password,status,created_at) VALUES (4,'Demo Teacher','demo_teacher','teacher@stepup.local','$2y$10$iMuXpiJVQnlYM6r32rgqsODsl4c4YxJOzC8W1RPyzj8M9Ox7A01xK','1','Inactive',NOW()),(5,'Demo Student','demo_student','student@stepup.local','$2y$10$iMuXpiJVQnlYM6r32rgqsODsl4c4YxJOzC8W1RPyzj8M9Ox7A01xK','1','Inactive',NOW()),(6,'Demo Parent','demo_parent','parent@stepup.local','$2y$10$iMuXpiJVQnlYM6r32rgqsODsl4c4YxJOzC8W1RPyzj8M9Ox7A01xK','1','Inactive',NOW());
INSERT INTO academic_years(name,is_current) VALUES('2026',1); INSERT INTO classes(name,sort_order) VALUES('ষষ্ঠ',6),('সপ্তম',7),('অষ্টম',8),('নবম',9),('দশম',10); INSERT INTO sections(class_id,name) VALUES(1,'ক'),(2,'ক'),(3,'ক'),(4,'বিজ্ঞান'),(5,'বিজ্ঞান'); INSERT INTO batches(class_id,section_id,name,capacity) VALUES(1,1,'ষষ্ঠ ক - সকাল',35),(2,2,'সপ্তম ক - সকাল',35),(3,3,'অষ্টম ক - বিকাল',35),(4,4,'নবম বিজ্ঞান',30),(5,5,'দশম বিজ্ঞান',30); INSERT INTO subjects(name) VALUES('বাংলা'),('ইংরেজি'),('গণিত'),('বিজ্ঞান'),('আইসিটি');
INSERT INTO teachers(employee_id,name,phone,qualification,join_date,status,salary) VALUES('T-001','মোঃ রফিকুল ইসলাম','01700000001','M.Sc. গণিত','2025-08-01','Active',18000),('T-002','ফারজানা আক্তার','01700000002','M.A. বাংলা','2025-08-01','Active',16000),('T-003','সাইফুল হক','01700000003','B.Sc. বিজ্ঞান','2025-09-01','Active',17000);
INSERT INTO teacher_subjects(teacher_id,subject_id,class_id,batch_id) VALUES(1,3,1,1),(2,2,2,2),(3,4,3,3);
INSERT INTO timetables(batch_id,subject_id,teacher_id,weekday,start_time,end_time,room,is_published) VALUES(1,3,1,1,'08:00','09:00','Room 101',1),(2,2,2,2,'08:00','09:00','Room 102',1),(3,4,3,3,'15:00','16:00','Room 201',1);
UPDATE teachers SET user_id=4 WHERE id=1;
INSERT INTO guardians(name,phone,address) VALUES('মোঃ কামাল হোসেন','01800000001','বটিয়াঘাটা, খুলনা'),('রুবিনা আক্তার','01800000002','বটিয়াঘাটা, খুলনা');
UPDATE guardians SET user_id=6 WHERE id=1;
INSERT INTO students(student_id,admission_no,name_bn,phone,address,admission_date,status) VALUES('ST-2026-001','ADM-001','সুমাইয়া ইসলাম','01900000001','বটিয়াঘাটা, খুলনা','2026-01-05','Active'),('ST-2026-002','ADM-002','তানভীর আহমেদ','01900000002','বটিয়াঘাটা, খুলনা','2026-01-05','Active'),('ST-2026-003','ADM-003','নুসরাত জাহান','01900000003','বটিয়াঘাটা, খুলনা','2026-01-07','Active'),('ST-2026-004','ADM-004','রায়হান কবির','01900000004','বটিয়াঘাটা, খুলনা','2026-01-08','Active'),('ST-2026-005','ADM-005','মারিয়া সুলতানা','01900000005','বটিয়াঘাটা, খুলনা','2026-01-08','Active');
UPDATE students SET user_id=5 WHERE id=1;
INSERT INTO student_guardians(student_id,guardian_id,relationship_name,is_primary) VALUES(1,1,'পিতা',1),(2,1,'পিতা',1),(3,2,'মাতা',1),(4,2,'মাতা',1),(5,2,'মাতা',1); INSERT INTO enrollments(student_id,academic_year_id,class_id,section_id,batch_id,roll_no) VALUES(1,1,1,1,1,'1'),(2,1,1,1,1,'2'),(3,1,2,2,2,'1'),(4,1,3,3,3,'1'),(5,1,4,4,4,'1');
-- Twenty additional connected demo students (total: 25) for realistic reports and filters.
INSERT INTO students(student_id,admission_no,name_bn,phone,address,admission_date,status)
SELECT CONCAT('ST-2026-',LPAD(n,3,'0')),CONCAT('ADM-',LPAD(n,3,'0')),CONCAT('ডেমো শিক্ষার্থী ',n),CONCAT('01900000',LPAD(n,3,'0')),'বটিয়াঘাটা, খুলনা','2026-01-10','Active'
FROM (SELECT 6 n UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25) demo;
INSERT INTO demo_data_students(student_id) SELECT id FROM students WHERE student_id LIKE 'ST-2026-%' AND id>5;
INSERT INTO enrollments(student_id,academic_year_id,class_id,section_id,batch_id,roll_no)
SELECT id,1,1,1,1,CAST(id AS CHAR) FROM students WHERE student_id LIKE 'ST-2026-%' AND id>5;
INSERT INTO fee_categories(name,is_recurring) VALUES('ভর্তি ফি',0),('মাসিক বেতন',1),('পরীক্ষার ফি',0),('সেশন ফি',0); INSERT INTO fee_structures(academic_year_id,class_id,fee_category_id,amount) VALUES(1,1,2,1200),(1,2,2,1300),(1,3,2,1400),(1,4,2,1600),(1,5,2,1700);
INSERT INTO financial_accounts(name,type,opening_balance) VALUES('ক্যাশ ইন হ্যান্ড','Cash',10000),('বিকাশ','Mobile Banking',0); INSERT INTO invoices(invoice_no,student_id,academic_year_id,billing_period,total_amount,discount_amount,paid_amount,due_amount,status,issue_date,due_date) VALUES('INV-2026-001',1,1,'2026-10',1200,0,1200,0,'Paid','2026-10-01','2026-10-10'),('INV-2026-002',2,1,'2026-10',1200,0,600,600,'Partial','2026-10-01','2026-10-10'),('INV-2026-003',3,1,'2026-10',1300,0,0,1300,'Unpaid','2026-10-01','2026-10-10');
INSERT INTO payments(receipt_no,student_id,financial_account_id,amount,payment_date,method,received_by) VALUES('R-2026-001',1,1,1200,'2026-10-02','Cash',1),('R-2026-002',2,2,600,'2026-10-03','bKash',3); INSERT INTO payment_allocations(payment_id,invoice_id,amount) VALUES(1,1,1200),(2,2,600); INSERT INTO receipts(payment_id,receipt_no,issued_at) VALUES(1,'R-2026-001','2026-10-02 10:00:00'),(2,'R-2026-002','2026-10-03 11:00:00');
INSERT INTO attendance(student_id,batch_id,attendance_date,status,marked_by,created_at) VALUES(1,1,CURDATE(),'Present',1,NOW()),(2,1,CURDATE(),'Absent',1,NOW()),(3,2,CURDATE(),'Present',1,NOW()),(4,3,CURDATE(),'Late',1,NOW()),(5,4,CURDATE(),'Present',1,NOW());
INSERT INTO examinations(academic_year_id,name,exam_date,status) VALUES(1,'মাসিক মূল্যায়ন - অক্টোবর','2026-10-20','Published'); INSERT INTO examination_subjects(examination_id,subject_id,class_id,total_marks,pass_marks) VALUES(1,1,1,100,33); INSERT INTO marks(examination_subject_id,student_id,marks_obtained,grade,gpa) VALUES(1,1,85,'A+',5),(1,2,72,'A',4),(1,3,65,'A-',3.5);
INSERT INTO expenses(financial_account_id,expense_date,category,amount,description,created_by) VALUES(1,'2026-10-03','বিদ্যুৎ বিল',2500,'অক্টোবর মাস',3),(1,'2026-10-04','স্টেশনারি',850,'খাতা ও কলম',3); INSERT INTO payroll(teacher_id,pay_month,basic_amount,deduction_amount,net_amount,status) VALUES(1,'2026-09',18000,0,18000,'Paid'),(2,'2026-09',16000,0,16000,'Paid');
INSERT INTO notices(title,body,audience,status,published_at,created_by) VALUES('অক্টোবর মাসিক মূল্যায়ন','২০ অক্টোবর থেকে মাসিক মূল্যায়ন শুরু হবে।','All','Published','2026-10-10 09:00:00',1),('নিয়মিত উপস্থিতি','সব শিক্ষার্থীকে সময়মতো ক্লাসে উপস্থিত থাকার অনুরোধ করা হলো।','All','Published','2026-10-08 09:00:00',1); INSERT INTO system_settings(setting_key,setting_value) VALUES('academy_phone','০১৭০০০০০০০০'),('academy_address','বটিয়াঘাটা, খুলনা, বাংলাদেশ'),('hero_title','উজ্জ্বল ভবিষ্যতের প্রথম স্টেপ'),('hero_text','StepUp Academy-তে নিয়মিত পরিচর্যা ও মানসম্মত শিক্ষার নিশ্চয়তা।');

-- Avoid overlapping teacher and room allocation for a published routine.
DROP TRIGGER IF EXISTS trg_timetable_conflict;
DELIMITER $$
CREATE TRIGGER trg_timetable_conflict BEFORE INSERT ON timetables FOR EACH ROW
BEGIN
  IF EXISTS (SELECT 1 FROM timetables t WHERE t.weekday=NEW.weekday AND t.start_time < NEW.end_time AND t.end_time > NEW.start_time AND (t.teacher_id=NEW.teacher_id OR (NEW.room IS NOT NULL AND NEW.room<>'' AND t.room=NEW.room))) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Teacher or room has a routine time conflict';
  END IF;
END$$
DELIMITER ;