แดชบอร์ด

-- ================================================================ -- ระบบช่วยเหลือนักเรียน โรงเรียนสุมณฑาศึกษา -- Database: thungya1_care | Host: localhost | User: thungya1_care -- ================================================================ CREATE DATABASE IF NOT EXISTS thungya1_care CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE thungya1_care; CREATE TABLE IF NOT EXISTS students ( student_pk INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, student_id VARCHAR(20) NOT NULL UNIQUE, -- เลขประจำตัวนักเรียน title_th VARCHAR(20), first_name_th VARCHAR(100) NOT NULL, last_name_th VARCHAR(100) NOT NULL, title_en VARCHAR(20), first_name_en VARCHAR(100), last_name_en VARCHAR(100), nickname VARCHAR(50), gender ENUM('ชาย','หญิง') NOT NULL, birthdate DATE NOT NULL, nationality VARCHAR(50) DEFAULT 'ไทย', ethnicity VARCHAR(50) DEFAULT 'ไทย', religion VARCHAR(50) DEFAULT 'พุทธ', photo_data LONGTEXT, -- Base64 image data grade VARCHAR(10) NOT NULL, room VARCHAR(10) NOT NULL, seat_no TINYINT UNSIGNED, weight_kg DECIMAL(5,2), height_cm DECIMAL(5,2), blood_type ENUM('A','B','AB','O'), has_chronic_disease TINYINT(1) DEFAULT 0, chronic_disease_detail TEXT, reg_house_no VARCHAR(20), reg_village VARCHAR(200), reg_moo VARCHAR(10), reg_subdistrict VARCHAR(100), reg_district VARCHAR(100), reg_province VARCHAR(100), reg_zipcode CHAR(5), disadvantage_types JSON, disability_types JSON, housing_status VARCHAR(100), lives_with VARCHAR(100), distance_km VARCHAR(30), transport_method VARCHAR(100), teacher_name VARCHAR(200) NOT NULL, teacher_position VARCHAR(100), teacher_phone VARCHAR(20) NOT NULL, guardian_name VARCHAR(200), guardian_relation VARCHAR(50), guardian_phone VARCHAR(20), guardian_income DECIMAL(10,2), parent_status VARCHAR(100), father_name VARCHAR(200), father_occupation VARCHAR(100), father_income DECIMAL(10,2), mother_name VARCHAR(200), mother_occupation VARCHAR(100), mother_income DECIMAL(10,2), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS screenings ( screening_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, student_pk INT UNSIGNED NOT NULL, semester TINYINT(1) NOT NULL, academic_year SMALLINT UNSIGNED NOT NULL, -- ปีการศึกษา เช่น 2569 fav_subject VARCHAR(200), unfav_subject VARCHAR(200), dream_career VARCHAR(100), gpa DECIMAL(3,2), academic_risk_flags JSON, academic_prob_flags JSON, opinion_academic TEXT, trend_academic ENUM('ปกติ','เสี่ยง','มีปัญหา'), attend_risk_flags JSON, attend_prob_flags JSON, opinion_attendance TEXT, trend_attendance ENUM('ปกติ','เสี่ยง','มีปัญหา'), health_risk_flags JSON, health_prob_flags JSON, opinion_health TEXT, trend_health ENUM('ปกติ','เสี่ยง','มีปัญหา'), econ_risk_flags JSON, econ_prob_flags JSON, opinion_economy TEXT, trend_economy ENUM('ปกติ','เสี่ยง','มีปัญหา'), welfare_risk_flags JSON, welfare_prob_flags JSON, opinion_welfare TEXT, trend_welfare ENUM('ปกติ','เสี่ยง','มีปัญหา'), adhd_flags JSON, depression_flags JSON, bully_flags JSON, redflag_flags JSON, opinion_behavior TEXT, trend_behavior ENUM('ปกติ','เสี่ยง','มีปัญหา'), screening_result ENUM('ปกติ','เสี่ยง','มีปัญหา'), remark TEXT, screened_by VARCHAR(200), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_screen (student_pk, semester, academic_year), FOREIGN KEY (student_pk) REFERENCES students(student_pk) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE INDEX IF NOT EXISTS idx_grade ON students(grade, room); CREATE INDEX IF NOT EXISTS idx_result ON screenings(screening_result); CREATE OR REPLACE VIEW v_student_summary AS SELECT s.student_pk, s.student_id, CONCAT(s.title_th, s.first_name_th, ' ', s.last_name_th) AS full_name, s.gender, s.grade, s.room, s.teacher_name, sc.gpa, sc.screening_result, sc.trend_academic, sc.trend_attendance, sc.trend_health, sc.trend_economy, sc.trend_welfare, sc.trend_behavior FROM students s LEFT JOIN screenings sc ON s.student_pk = sc.student_pk ORDER BY s.grade, s.room, s.seat_no;