<?php
include('../connection.php'); // Ensure this file contains your database connection

// Check the action type and process accordingly
$action = isset($_POST['action']) ? $_POST['action'] : (isset($_GET['action']) ? $_GET['action'] : '');

// Handle fetching records
if ($action == 'fetch') {
    $query = "SELECT `ID`, `record_subject`, `record_category`, `record_date`, `file_name` FROM `record_keeper`";
    $result = $conn->query($query);

    $records = [];
    while ($row = $result->fetch_assoc()) {
        $records[] = $row;
    }

    echo json_encode(['records' => $records]);
    exit;
}

// Handle file upload and insertion
if ($action == 'upload') {
    $subject = $_POST['record_subject'];
    $category = $_POST['record_category'];
    $file = $_FILES['file'];

    // Check file type
    $allowedTypes = ['application/pdf', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'application/vnd.ms-excel'];
    if (!in_array($file['type'], $allowedTypes)) {
        echo json_encode(['status' => 'error', 'message' => 'Invalid file type. Only PDF and Excel files are allowed.']);
        exit;
    }

    // Check for duplicate file
    $fileName = basename($file['name']);
    $checkQuery = "SELECT * FROM `record_keeper` WHERE `file_name` = '$fileName'";
    $checkResult = $conn->query($checkQuery);

    if ($checkResult->num_rows > 0) {
        echo json_encode(['status' => 'error', 'message' => 'File already exists.']);
        exit;
    }

    // Move the uploaded file to the desired directory
    $uploadDir = '../uploads/Downloads/';
    $targetFile = $uploadDir . $fileName;

    if (move_uploaded_file($file['tmp_name'], $targetFile)) {
        // Insert record into database
        $date = date('Y-m-d');
        $insertQuery = "INSERT INTO `record_keeper` (`record_subject`, `record_category`, `record_date`, `file_name`) VALUES ('$subject', '$category', '$date', '$fileName')";
        if ($conn->query($insertQuery)) {
            echo json_encode(['status' => 'success']);
        } else {
            echo json_encode(['status' => 'error', 'message' => 'Failed to insert record into database.']);
        }
    } else {
        echo json_encode(['status' => 'error', 'message' => 'Failed to upload file.']);
    }

    exit;
}

// Handle deletion of records
if ($action == 'delete') {
    $id = $_POST['id'];

    // Fetch the file name to delete it from the server
    $selectQuery = "SELECT file_name FROM `record_keeper` WHERE ID = '$id'";
    $result = $conn->query($selectQuery);
    $file = $result->fetch_assoc();

    if ($file) {
        $filePath = '../uploads/Downloads/'. $file['file_name'];
        if (unlink($filePath)) {
            // Delete the record from the database
            $deleteQuery = "DELETE FROM `record_keeper` WHERE ID = '$id'";
            if ($conn->query($deleteQuery)) {
                echo json_encode(['status' => 'success']);
            } else {
                echo json_encode(['status' => 'error', 'message' => 'Failed to delete record from database.']);
            }
        } else {
            echo json_encode(['status' => 'error', 'message' => 'Failed to delete file from server.']);
        }
    } else {
        echo json_encode(['status' => 'error', 'message' => 'Record not found.']);
    }

    exit;
}
?>
