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;