<?php
session_start();
include('includes/config.php');
require '../vendor/autoload.php'; // PhpSpreadsheet
date_default_timezone_set('Asia/Kolkata');
$updatetime = date('Y-m-d H:i:s');

use PhpOffice\PhpSpreadsheet\IOFactory;

if (isset($_POST['import'])) {
    $file = $_FILES['excel_file']['tmp_name'];

    try {
        $spreadsheet = IOFactory::load($file);
        $sheet = $spreadsheet->getActiveSheet();
        $rows = $sheet->toArray();

        foreach ($rows as $index => $row) {
            if ($index == 0)
                continue; // skip header

            $studentId = trim($row[1]);
            $fullName = trim($row[2]);
            $mobile = trim($row[3]);
            $email = trim($row[4]);
            $address = trim($row[5]);
            $academicYear = trim($row[6]) . '-' . trim($row[7]);
            $departmentName = trim($row[8]);
            $degreeName = trim($row[9]);
            $shift = 2;

            // Department
            $stmtDept = $dbh->prepare("
    SELECT department_id 
    FROM tbldepartment 
    WHERE LOWER(department_name) = LOWER(:dept) 
    LIMIT 1
");

            $stmtDept->bindParam(':dept', $departmentName);
            $stmtDept->execute();
            $dept = $stmtDept->fetch(PDO::FETCH_OBJ);
            $department_id = $dept ? $dept->department_id : null;

            if (!$department_id) {
                $stmtInsertDept = $dbh->prepare("INSERT INTO tbldepartment (department_name, is_deleted) VALUES (:dept, 0)");
                $stmtInsertDept->bindParam(':dept', $departmentName);
                $stmtInsertDept->execute();
                $department_id = $dbh->lastInsertId();
            }

            // Degree
            $stmtDegree = $dbh->prepare("
    SELECT degree_id 
    FROM tbldegree 
    WHERE LOWER(degree_name) = LOWER(:degree) 
    LIMIT 1
");

            $stmtDegree->bindParam(':degree', $degreeName);
            $stmtDegree->execute();
            $deg = $stmtDegree->fetch(PDO::FETCH_OBJ);
            $degree_id = $deg ? $deg->degree_id : null;

            if (!$degree_id) {
                $stmtInsertDegree = $dbh->prepare("INSERT INTO tbldegree (degree_name, is_deleted) VALUES (:degree, 0)");
                $stmtInsertDegree->bindParam(':degree', $degreeName);
                $stmtInsertDegree->execute();
                $degree_id = $dbh->lastInsertId();
            }

            // Default password
            if (empty($mobile)) {
                // If mobile is not available → password = student ID
                $password = password_hash($studentId, PASSWORD_DEFAULT);
            } else {
                // If mobile is available → password = mobile number
                $password = password_hash($mobile, PASSWORD_DEFAULT);
            }


            // Insert student
            $stmtInsertStudent = $dbh->prepare("
                INSERT INTO tblstudents
                (StudentId, FullName, EmailId, MobileNumber, Password, Status, RegDate, UpdationDate, Address, passed_out_year, gender_id, department_id, degree_id, shift)
                VALUES
                (:studentId, :fullName, :email, :mobile, :password, 1, :updatetime, NULL, :address, :passedOut, 2, :deptId, :degreeId, :shift)
            ");
            $stmtInsertStudent->bindParam(':studentId', $studentId);
            $stmtInsertStudent->bindParam(':fullName', $fullName);
            $stmtInsertStudent->bindParam(':email', $email);
            $stmtInsertStudent->bindParam(':mobile', $mobile);
            $stmtInsertStudent->bindParam(':password', $password);
            $stmtInsertStudent->bindParam(':address', $address);
            $stmtInsertStudent->bindParam(':passedOut', $academicYear);
            $stmtInsertStudent->bindParam(':deptId', $department_id);
            $stmtInsertStudent->bindParam(':degreeId', $degree_id);
            $stmtInsertStudent->bindParam(':updatetime', $updatetime);
            $stmtInsertStudent->bindParam(':shift', $shift);
            $stmtInsertStudent->execute();
        }

        $_SESSION['msg'] = "Students imported successfully!";
        header('location:reg-students.php');
        exit;
    } catch (Exception $e) {
        $_SESSION['error'] = "Error importing students: " . $e->getMessage();
        header('location:import_students.php');
        exit;
    }
}