<?php

/**

 * OneRoster Import Handler

 * Handles IMS Global OneRoster format imports

 */



define('MIGRATION_ACCESS', true);

require_once '../config.php';

require_once __DIR__ . '/../../lib/academic_period.php';

require_once '../validation/validator.php';



class OneRosterImporter {

    private $conn;

    private $manifest;

    private $files = [];

    

    public function __construct($conn) {

        $this->conn = $conn;

    }

    

    /**

     * Import OneRoster zip package

     */

    public function importPackage($zipPath) {

        $extractPath = sys_get_temp_dir() . '/oneroster_' . uniqid();

        $this->extractZip($zipPath, $extractPath);

        

        // Parse manifest

        $this->loadManifest($extractPath);

        

        // Process files in correct order

        $results = [

            'orgs' => $this->importOrgs($extractPath),

            'academicSessions' => $this->importAcademicSessions($extractPath),

            'courses' => $this->importCourses($extractPath),

            'classes' => $this->importClasses($extractPath),

            'users' => $this->importUsers($extractPath),

            'enrollments' => $this->importEnrollments($extractPath),

            'results' => $this->importResults($extractPath)

        ];

        

        // Cleanup

        $this->cleanup($extractPath);

        

        return $results;

    }

    

    /**

     * Import organizations

     */

    private function importOrgs($path) {

        $file = $path . '/orgs.csv';

        if (!file_exists($file)) return ['imported' => 0, 'failed' => 0];

        

        $handle = fopen($file, 'r');

        $headers = fgetcsv($handle);

        $imported = 0;

        $failed = 0;

        

        while ($row = fgetcsv($handle)) {

            $data = array_combine($headers, $row);

            

            // Map OneRoster fields to your system

            $org = [

                'sourcedId' => $data['sourcedId'],

                'name' => $data['name'],

                'type' => $data['type'],

                'identifier' => $data['identifier'],

                'parentSourcedId' => $data['parentSourcedId'] ?? null

            ];

            

            // Save to settings or orgs table (create if needed)

            if ($this->saveOrganization($org)) {

                $imported++;

            } else {

                $failed++;

            }

        }

        

        fclose($handle);

        return ['imported' => $imported, 'failed' => $failed];

    }

    

    /**

     * Import academic sessions (terms, school years)

     */

    private function importAcademicSessions($path) {

        $file = $path . '/academicSessions.csv';

        if (!file_exists($file)) return ['imported' => 0, 'failed' => 0];

        

        $handle = fopen($file, 'r');

        $headers = fgetcsv($handle);

        $imported = 0;

        $failed = 0;

        

        while ($row = fgetcsv($handle)) {

            $data = array_combine($headers, $row);

            

            $session = [

                'sourcedId' => $data['sourcedId'],

                'title' => $data['title'],

                'type' => $data['type'],

                'startDate' => $data['startDate'],

                'endDate' => $data['endDate'],

                'parentSourcedId' => $data['parentSourcedId'] ?? null

            ];

            

            // Save to terms table

            if ($this->saveAcademicSession($session)) {

                $imported++;

            } else {

                $failed++;

            }

        }

        

        fclose($handle);

        return ['imported' => $imported, 'failed' => $failed];

    }

    

    /**

     * Import courses

     */

    private function importCourses($path) {

        $file = $path . '/courses.csv';

        if (!file_exists($file)) return ['imported' => 0, 'failed' => 0];

        

        $handle = fopen($file, 'r');

        $headers = fgetcsv($handle);

        $imported = 0;

        $failed = 0;

        

        while ($row = fgetcsv($handle)) {

            $data = array_combine($headers, $row);

            

            $course = [

                'sourcedId' => $data['sourcedId'],

                'title' => $data['title'],

                'courseCode' => $data['courseCode'],

                'grades' => $data['grades'] ?? '',

                'subject' => $data['subject'] ?? '',

                'orgSourcedId' => $data['orgSourcedId'] ?? null

            ];

            

            // Map to your subjects table

            if ($this->saveCourse($course)) {

                $imported++;

            } else {

                $failed++;

            }

        }

        

        fclose($handle);

        return ['imported' => $imported, 'failed' => $failed];

    }

    

    /**

     * Import classes

     */

    private function importClasses($path) {

        $file = $path . '/classes.csv';

        if (!file_exists($file)) return ['imported' => 0, 'failed' => 0];

        

        $handle = fopen($file, 'r');

        $headers = fgetcsv($handle);

        $imported = 0;

        $failed = 0;

        

        while ($row = fgetcsv($handle)) {

            $data = array_combine($headers, $row);

            

            $class = [

                'sourcedId' => $data['sourcedId'],

                'title' => $data['title'],

                'classCode' => $data['classCode'],

                'classType' => $data['classType'],

                'location' => $data['location'] ?? '',

                'grades' => $data['grades'] ?? '',

                'subjects' => $data['subjects'] ?? '',

                'courseSourcedId' => $data['courseSourcedId'],

                'schoolSourcedId' => $data['schoolSourcedId'],

                'termSourcedIds' => $data['termSourcedIds'] ?? ''

            ];

            

            // Map to your classes/class_subjects

            if ($this->saveClass($class)) {

                $imported++;

            } else {

                $failed++;

            }

        }

        

        fclose($handle);

        return ['imported' => $imported, 'failed' => $failed];

    }

    

    /**

     * Import users (students, teachers, staff)

     */

    private function importUsers($path) {

        $file = $path . '/users.csv';

        if (!file_exists($file)) return ['imported' => 0, 'failed' => 0];

        

        $handle = fopen($file, 'r');

        $headers = fgetcsv($handle);

        $imported = 0;

        $failed = 0;

        

        while ($row = fgetcsv($handle)) {

            $data = array_combine($headers, $row);

            

            $user = [

                'sourcedId' => $data['sourcedId'],

                'username' => $data['username'] ?? '',

                'givenName' => $data['givenName'] ?? '',

                'familyName' => $data['familyName'] ?? '',

                'middleName' => $data['middleName'] ?? '',

                'role' => $data['role'],

                'identifier' => $data['identifier'] ?? '',

                'email' => $data['email'] ?? '',

                'sms' => $data['sms'] ?? '',

                'phone' => $data['phone'] ?? '',

                'agentSourcedIds' => $data['agentSourcedIds'] ?? '',

                'grades' => $data['grades'] ?? '',

                'password' => $data['password'] ?? ''

            ];

            

            // Map to students or staff based on role

            if ($user['role'] == 'student') {

                if ($this->saveStudent($user)) {

                    $imported++;

                } else {

                    $failed++;

                }

            } elseif (in_array($user['role'], ['teacher', 'staff'])) {

                if ($this->saveStaff($user)) {

                    $imported++;

                } else {

                    $failed++;

                }

            } else {

                $failed++;

            }

        }

        

        fclose($handle);

        return ['imported' => $imported, 'failed' => $failed];

    }

    

    /**

     * Import enrollments

     */

    private function importEnrollments($path) {

        $file = $path . '/enrollments.csv';

        if (!file_exists($file)) return ['imported' => 0, 'failed' => 0];

        

        $handle = fopen($file, 'r');

        $headers = fgetcsv($handle);

        $imported = 0;

        $failed = 0;

        

        while ($row = fgetcsv($handle)) {

            $data = array_combine($headers, $row);

            

            $enrollment = [

                'sourcedId' => $data['sourcedId'],

                'classSourcedId' => $data['classSourcedId'],

                'schoolSourcedId' => $data['schoolSourcedId'],

                'userSourcedId' => $data['userSourcedId'],

                'role' => $data['role'],

                'primary' => $data['primary'] ?? 'false',

                'beginDate' => $data['beginDate'] ?? null,

                'endDate' => $data['endDate'] ?? null

            ];

            

            // Map to student_class assignments

            if ($this->saveEnrollment($enrollment)) {

                $imported++;

            } else {

                $failed++;

            }

        }

        

        fclose($handle);

        return ['imported' => $imported, 'failed' => $failed];

    }

    

    /**

     * Import results (scores/grades)

     */

    private function importResults($path) {

        $file = $path . '/results.csv';

        if (!file_exists($file)) return ['imported' => 0, 'failed' => 0];

        

        $handle = fopen($file, 'r');

        $headers = fgetcsv($handle);

        $imported = 0;

        $failed = 0;

        

        while ($row = fgetcsv($handle)) {

            $data = array_combine($headers, $row);

            

            $result = [

                'sourcedId' => $data['sourcedId'],

                'lineItemSourcedId' => $data['lineItemSourcedId'],

                'studentSourcedId' => $data['studentSourcedId'],

                'scoreStatus' => $data['scoreStatus'],

                'score' => $data['score'] ?? null,

                'scoreDate' => $data['scoreDate'] ?? null,

                'comment' => $data['comment'] ?? ''

            ];

            

            // Map to scores table

            if ($this->saveResult($result)) {

                $imported++;

            } else {

                $failed++;

            }

        }

        

        fclose($handle);

        return ['imported' => $imported, 'failed' => $failed];

    }

    

    /**

     * Extract OneRoster zip file

     */

    private function extractZip($zipPath, $extractPath) {

        $zip = new ZipArchive;

        if ($zip->open($zipPath) === TRUE) {

            $zip->extractTo($extractPath);

            $zip->close();

        }

    }

    

    /**

     * Load and parse manifest file

     */

    private function loadManifest($path) {

        $manifestFile = $path . '/manifest.csv';

        if (file_exists($manifestFile)) {

            $handle = fopen($manifestFile, 'r');

            $headers = fgetcsv($handle);

            

            while ($row = fgetcsv($handle)) {

                $file = array_combine($headers, $row);

                $this->files[$file['file']] = $file;

            }

            

            fclose($handle);

        }

    }

    

    /**

     * Clean up extracted files

     */

    private function cleanup($path) {

        $files = glob($path . '/*');

        foreach ($files as $file) {

            if (is_file($file)) unlink($file);

        }

        rmdir($path);

    }

    

    /**

     * Save organization to settings

     */

    private function saveOrganization($org) {

        // You might want to create an orgs table or store in settings

        // For now, store in settings as JSON

        $query = "INSERT INTO settings (setting_key, setting_value) 

                  VALUES (?, ?) ON DUPLICATE KEY UPDATE setting_value = ?";

        $stmt = $this->conn->prepare($query);

        $key = 'oneroster_org_' . $org['sourcedId'];

        $value = json_encode($org);

        $stmt->bind_param("sss", $key, $value, $value);

        return $stmt->execute();

    }

    

    /**

     * Save academic session

     */

    private function saveAcademicSession($session) {

        // Map to your terms/academic years

        if ($session['type'] == 'schoolYear') {

            // Update academic year setting

            $query = "UPDATE settings SET setting_value = ? WHERE setting_key = 'academic_year'";

            $stmt = $this->conn->prepare($query);

            $year = $session['title'];

            $stmt->bind_param("s", $year);

            $stmt->execute();

        }

        return true;

    }

    

    /**

     * Save course to subjects

     */

    private function saveCourse($course) {

        // Check if subject already exists

        $check = $this->conn->query("SELECT id FROM subjects WHERE subject_code = '{$course['courseCode']}'");

        

        if ($check->num_rows > 0) {

            // Update existing

            $query = "UPDATE subjects SET subject_name = ?, active = 'yes' WHERE subject_code = ?";

            $stmt = $this->conn->prepare($query);

            $stmt->bind_param("ss", $course['title'], $course['courseCode']);

        } else {

            // Insert new

            $query = "INSERT INTO subjects (subject_name, subject_code, active) VALUES (?, ?, 'yes')";

            $stmt = $this->conn->prepare($query);

            $stmt->bind_param("ss", $course['title'], $course['courseCode']);

        }

        

        return $stmt->execute();

    }

    

    /**

     * Save class

     */

    private function saveClass($class) {

        // Extract class name and section

        $className = $class['title'];

        $section = $class['classCode'] ?? '';

        

        // Check if class exists in fee_structures

        $check = $this->conn->query("SELECT DISTINCT class_name FROM fee_structures WHERE class_name = '$className'");

        if ($check->num_rows == 0) {

            // Create default fee structure for this class

            // You might want to prompt user to set up fees later

        }

        

        return true;

    }

    

    /**

     * Save student

     */

    private function saveStudent($user) {

        // Check if student already exists

        $check = $this->conn->query("SELECT id FROM students WHERE student_id = '{$user['identifier']}'");

        

        $firstName = $user['givenName'] ?? '';

        $lastName = $user['familyName'] ?? '';

        $fullName = trim("$firstName $lastName");

        

        if ($check->num_rows > 0) {

            // Update existing

            $query = "UPDATE students SET 

                first_name = ?, last_name = ?, email = ?, phone = ?

                WHERE student_id = ?";

            $stmt = $this->conn->prepare($query);

            $stmt->bind_param("sssss", $firstName, $lastName, $user['email'], $user['phone'], $user['identifier']);

        } else {

            // Insert new

            $query = "INSERT INTO students (student_id, first_name, last_name, email, phone, status) 

                      VALUES (?, ?, ?, ?, ?, 'active')";

            $stmt = $this->conn->prepare($query);

            $stmt->bind_param("sssss", $user['identifier'], $firstName, $lastName, $user['email'], $user['phone']);

        }

        

        return $stmt->execute();

    }

    

    /**

     * Save staff/teacher

     */

    private function saveStaff($user) {

        // Similar to saveStudent but for staff table

        $query = "INSERT INTO staff (staff_id, full_name, email, phone, status) 

                  VALUES (?, ?, ?, ?, 'active')

                  ON DUPLICATE KEY UPDATE full_name = ?, email = ?, phone = ?";

        $stmt = $this->conn->prepare($query);

        $fullName = $user['givenName'] . ' ' . $user['familyName'];

        $stmt->bind_param("ssssss", $user['identifier'], $fullName, $user['email'], $user['phone'], $fullName, $user['email'], $user['phone']);

        return $stmt->execute();

    }

    

    /**

     * Save enrollment

     */

    private function saveEnrollment($enrollment) {

        // Map user to class - update student's class field

        if ($enrollment['role'] == 'student') {

            // Find student by sourcedId (stored in student_id)

            $userCheck = $this->conn->query("SELECT id FROM students WHERE student_id = '{$enrollment['userSourcedId']}'");

            if ($userRow = $userCheck->fetch_assoc()) {

                // Find class name from class sourcedId (simplified - you'd have a mapping table)

                $classQuery = "UPDATE students SET class = ? WHERE id = ?";

                $className = $enrollment['classSourcedId']; // You'd map this to actual class name

                $stmt = $this->conn->prepare($classQuery);

                $stmt->bind_param("si", $className, $userRow['id']);

                $stmt->execute();

            }

        }

        return true;

    }

    

    /**

     * Save result

     */

    private function saveResult($result) {

        // Map to scores table

        // Find student

        $studentCheck = $this->conn->query("SELECT id FROM students WHERE student_id = '{$result['studentSourcedId']}'");

        if ($studentRow = $studentCheck->fetch_assoc()) {

            // Find subject

            $subjectCheck = $this->conn->query("SELECT id FROM subjects LIMIT 1");

            if ($subjectRow = $subjectCheck->fetch_assoc()) {

                // Insert score (simplified)

                // ON DUPLICATE KEY UPDATE: scores carries a unique key on

                // (student_id, subject_id, term, academic_year), so a repeated

                // import updates in place instead of failing.

                $query = "INSERT INTO scores (student_id, subject_id, class_name, total_score, term, academic_year)

                          VALUES (?, ?, ?, ?, ?, ?)

                          ON DUPLICATE KEY UPDATE

                            class_name  = VALUES(class_name),

                            total_score = VALUES(total_score)";

                $stmt = $this->conn->prepare($query);

                $term = '1'; // You'd determine from session

                $year = date('Y') . '-' . (date('Y') + 1);

                // bind_param binds by reference — array offsets cannot be passed directly.

                $studentPk = (int) $studentRow['id'];

                $subjectPk = (int) $subjectRow['id'];

                $className = academicStudentClass($this->conn, $studentPk);

                $scoreVal  = $result['score'];

                $stmt->bind_param("iisdss", $studentPk, $subjectPk, $className, $scoreVal, $term, $year);

                $stmt->execute();

            }

        }

        return true;

    }

}

?>