<?php
// db connection
$host = 'localhost';
$db = 'deanslister_db';
$user = 'root';
$pass = '';

$conn = new mysqli($host, $user, $pass, $db);

if ($conn->connect_error) {
    die("Database connection failed: " . $conn->connect_error);
}

// to store uploaded files
$uploadDir = 'uploads/';
if (!is_dir($uploadDir)) {
    mkdir($uploadDir, 0777, true);
}

// fields & db column mapping
$fileFields = [
    'COR' => 'cor_path',
    'COG' => 'cog_path',
    'FORM' => 'applicationform_path',
    'QPA' => 'qpa_path',
];

// file size limit (5MB)
$fileSizeLimit = 5 * 1024 * 1024;

$uploadedPaths = [];

try {
    foreach ($fileFields as $field => $column) {
        if (isset($_FILES[$field]) && $_FILES[$field]['error'] === UPLOAD_ERR_OK) {
            $fileTmpPath = $_FILES[$field]['tmp_name'];
            $fileName = $_FILES[$field]['name'];
            $fileSize = $_FILES[$field]['size'];
            $fileExt = pathinfo($fileName, PATHINFO_EXTENSION);

            // check file size
            if ($fileSize > $fileSizeLimit) {
                throw new Exception("File size for $field exceeds the limit of 5MB.");
            }

            // valid file types
            $allowedExtensions = ['jpg', 'jpeg', 'png', 'pdf', 'xlsx'];
            if (!in_array(strtolower($fileExt), $allowedExtensions)) {
                throw new Exception("Invalid file type for $field. Allowed types: " . implode(', ', $allowedExtensions));
            }

            // generate unique file name
            $newFileName = $field . '_' . time() . '.' . $fileExt;
            $destPath = $uploadDir . $newFileName;

            if (move_uploaded_file($fileTmpPath, $destPath)) {
                $uploadedPaths[$column] = $destPath;
            } else {
                throw new Exception("Error uploading $field.");
            }
        } else {
            throw new Exception("File $field is missing or failed to upload.");
        }
    }

    // sql prepare & execute
    $stmt = $conn->prepare("
        INSERT INTO deanslister_tbl (cor_path, cog_path, applicationform_path, qpa_path)
        VALUES (?, ?, ?, ?)
    ");
    $stmt->bind_param(
        "ssss",
        $uploadedPaths['cor_path'],
        $uploadedPaths['cog_path'],
        $uploadedPaths['applicationform_path'],
        $uploadedPaths['qpa_path']
    );

    if ($stmt->execute()) {
        echo "Files uploaded and saved successfully!";
    } else {
        throw new Exception("Database error: " . $stmt->error);
    }

    $stmt->close();
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}

$conn->close();
?>