71 lines
3.8 KiB
SQL
71 lines
3.8 KiB
SQL
CREATE TABLE IF NOT EXISTS school_students (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
center_application_id INT UNSIGNED NOT NULL,
|
|
student_code VARCHAR(60) NOT NULL,
|
|
full_name VARCHAR(190) NOT NULL,
|
|
gender VARCHAR(20) NOT NULL,
|
|
grade_level VARCHAR(80) NOT NULL,
|
|
guardian_name VARCHAR(150) NOT NULL,
|
|
guardian_phone VARCHAR(60) NOT NULL,
|
|
birth_date DATE NULL,
|
|
enrollment_status VARCHAR(30) NOT NULL DEFAULT 'active',
|
|
notes TEXT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uniq_school_student_code (center_application_id, student_code),
|
|
INDEX idx_school_students_center (center_application_id),
|
|
INDEX idx_school_students_status (enrollment_status),
|
|
CONSTRAINT fk_school_students_center_application FOREIGN KEY (center_application_id) REFERENCES center_applications(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS school_teachers (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
center_application_id INT UNSIGNED NOT NULL,
|
|
full_name VARCHAR(190) NOT NULL,
|
|
role_title VARCHAR(120) NOT NULL,
|
|
specialization VARCHAR(150) NULL,
|
|
phone VARCHAR(60) NULL,
|
|
email VARCHAR(190) NULL,
|
|
employment_status VARCHAR(30) NOT NULL DEFAULT 'active',
|
|
notes TEXT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_school_teachers_center (center_application_id),
|
|
INDEX idx_school_teachers_status (employment_status),
|
|
CONSTRAINT fk_school_teachers_center_application FOREIGN KEY (center_application_id) REFERENCES center_applications(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS school_assessment_types (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
center_application_id INT UNSIGNED NOT NULL,
|
|
title VARCHAR(150) NOT NULL,
|
|
category VARCHAR(80) NOT NULL,
|
|
scale_type VARCHAR(40) NOT NULL DEFAULT 'percentage',
|
|
max_score DECIMAL(6,2) NOT NULL DEFAULT 100.00,
|
|
weight_percentage DECIMAL(5,2) NOT NULL DEFAULT 0.00,
|
|
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
|
notes TEXT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_school_assessments_center (center_application_id),
|
|
INDEX idx_school_assessments_active (is_active),
|
|
CONSTRAINT fk_school_assessments_center_application FOREIGN KEY (center_application_id) REFERENCES center_applications(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS school_attendance_records (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
center_application_id INT UNSIGNED NOT NULL,
|
|
student_id INT UNSIGNED NOT NULL,
|
|
attendance_date DATE NOT NULL,
|
|
attendance_status VARCHAR(30) NOT NULL DEFAULT 'absent',
|
|
absence_reason VARCHAR(190) NULL,
|
|
notes TEXT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uniq_school_attendance_student_date (student_id, attendance_date),
|
|
INDEX idx_school_attendance_center (center_application_id),
|
|
INDEX idx_school_attendance_date (attendance_date),
|
|
CONSTRAINT fk_school_attendance_center_application FOREIGN KEY (center_application_id) REFERENCES center_applications(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_school_attendance_student FOREIGN KEY (student_id) REFERENCES school_students(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|