<?php
require_once 'config.php';
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

header('Content-Type: application/json');

/* ================= COMMON FUNCTION ================= */

function getExcelFile($assignment_no) {
    $xls = "docs/$assignment_no/$assignment_no.xls";
    $xlsx = "docs/$assignment_no/$assignment_no.xlsx";

    if (file_exists($xls)) {
        return [$xls, "Xls"];
    } elseif (file_exists($xlsx)) {
        return [$xlsx, "Xlsx"];
    }

    return [null, null];
}


/* ================= UPDATE SINGLE ROW ================= */

if (isset($_POST['formData']['rowId'])) {

    $data = $_POST['formData'];

    $assignment_no = trim($data['assignment_no']);
    $docId = trim($data['docId']);
    $rowId = (int)$data['rowId'];
    $title = trim($data['title']);
    $date = trim($data['date']);
    $description = trim($data['description']);

    list($inputFileName, $fileType) = getExcelFile($assignment_no);

    if (!$inputFileName) {
        echo json_encode(['status' => 'error', 'message' => 'File not found']);
        exit;
    }

    $spreadsheet = IOFactory::load($inputFileName);
    $sheet = $spreadsheet->getActiveSheet();

    // Update Excel
    $sheet->setCellValue("B$rowId", $title);
    $sheet->setCellValue("C$rowId", $date);
    $sheet->setCellValue("D$rowId", $description);

    // Get assignment detail ID
    $stmt = $conn->prepare("
        SELECT ad.id 
        FROM assignment_details ad
        INNER JOIN assignments a ON a.id = ad.assignment_id
        WHERE a.assignment_no = ? AND ad.doc_id = ?
    ");
    $stmt->bind_param("ss", $assignment_no, $docId);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();

    if (!$row) {
        echo json_encode(['status' => 'error', 'message' => 'Record not found']);
        exit;
    }

    // Update DB
    $stmt = $conn->prepare("
        UPDATE assignment_details 
        SET doc_title=?, doc_date=?, doc_description=? 
        WHERE id=?
    ");
    $stmt->bind_param("sssi", $title, $date, $description, $row['id']);
    $recordUpdate = $stmt->execute();

    if ($recordUpdate) {
        $writer = IOFactory::createWriter($spreadsheet, $fileType);
        $writer->save($inputFileName);
    }

    // Response logic
    if ($title === "" && $date === "" && $description === "") {
        echo json_encode(['status' => "default"]);
    } elseif ($description !== "") {
        echo json_encode(['status' => "doubt"]);
    } else {
        echo json_encode(['status' => "done"]);
    }

    exit;
}


/* ================= FINAL SUBMIT ================= */

if (isset($_POST['assignment_status_id'])) {

    $assignment_no = $_POST['assignment_no'];
    $status_id = (int)$_POST['assignment_status_id'];

    list($inputFileName, $fileType) = getExcelFile($assignment_no);

    if (!$inputFileName) {
        echo json_encode(['status' => 'fail', 'message' => 'File not found']);
        exit;
    }

    $spreadsheet = IOFactory::load($inputFileName);
    $sheet = $spreadsheet->getActiveSheet();

    $highestRow = $sheet->getHighestRow();
    $highestColumn = $sheet->getHighestColumn();

    // Get assignment ID
    $stmt = $conn->prepare("SELECT id FROM assignments WHERE assignment_no=?");
    $stmt->bind_param("s", $assignment_no);
    $stmt->execute();
    $result = $stmt->get_result();
    $assignment = $result->fetch_assoc();

    if (!$assignment) {
        echo json_encode(['status' => 'fail', 'message' => 'Assignment not found']);
        exit;
    }

    $assignmentId = $assignment['id'];

    // Update all rows
    for ($row = 1; $row <= $highestRow; $row++) {

        $rowData = $sheet->rangeToArray("A$row:$highestColumn$row", NULL, TRUE, FALSE);

        $doc_id = $rowData[0][0];
        $doc_title = trim($rowData[0][1]);
        $doc_date = $rowData[0][2];
        $doc_description = $rowData[0][3];

        $stmt = $conn->prepare("
            UPDATE assignment_details 
            SET doc_title=?, doc_date=?, doc_description=? 
            WHERE assignment_id=? AND doc_id=?
        ");
        $stmt->bind_param("sssis", $doc_title, $doc_date, $doc_description, $assignmentId, $doc_id);
        $stmt->execute();
    }

    // Check empty records
    $result = $conn->query("
        SELECT COUNT(*) as freshCount 
        FROM assignment_details 
        WHERE assignment_id=$assignmentId 
        AND (doc_title='' AND doc_date='' AND doc_description='')
    ");

    $row = $result->fetch_assoc();

    if ($row['freshCount'] == 0) {

        $stmt = $conn->prepare("
            UPDATE assignments 
            SET assignment_status_id=?, submit_date=? 
            WHERE assignment_no=?
        ");
        $today = date("Y-m-d");
        $stmt->bind_param("iss", $status_id, $today, $assignment_no);
        $stmt->execute();

        echo json_encode(['status' => 'success', 'message' => 'done']);

    } else {
        echo json_encode(['status' => 'fail', 'message' => 'Please do not leave fields blank.']);
    }

    exit;
}


/* ================= GET DETAIL ================= */

if (isset($_POST['get_detail'])) {

    $assignment_no = $_POST['assignment_no'];
    $docId = $_POST['docId'];

    $stmt = $conn->prepare("
        SELECT ad.* 
        FROM assignment_details ad
        INNER JOIN assignments a ON a.id = ad.assignment_id
        WHERE a.assignment_no=? AND ad.doc_id=?
    ");

    $stmt->bind_param("ss", $assignment_no, $docId);
    $stmt->execute();

    $result = $stmt->get_result();
    $row = $result->fetch_assoc();

    echo json_encode($row ?: []);

    exit;
}


/* ================= DEFAULT ================= */

echo json_encode(['status' => "error", 'message' => "Invalid request"]);