db.php

<?php
session_start();
require_once('conf.php'); // Include your configuration file

// GET YEAR METADATA
if ($_SERVER['REQUEST_METHOD'] === 'GET' && isset($_GET['action']) && $_GET['action'] === 'get_metadata') {
    $mysqli = new mysqli($prDbhost, $prDbusername, $prDbpassword, $prDbname);
    $result = $mysqli->query("SELECT * FROM progs_metadata");
    $meta = [];
    while ($row = $result->fetch_assoc()) {
        $meta[] = $row;
    }
    echo json_encode($meta);
    if (isset($mysqli)) $mysqli->close();
    exit;
}

// SAVE YEAR METADATA
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'save_metadata') {
    if (!isset($_SESSION['uid']) || ($_SESSION['uid'] !== 'dipeira' && $_SESSION['uid'] !== 'taypeira')) {
        echo json_encode(['success' => false, 'error' => 'Unauthorized action.']);
        exit;
    }
    $mysqli = new mysqli($prDbhost, $prDbusername, $prDbpassword, $prDbname);
    $metadata = json_decode($_POST['metadata'], true);
    
    $mysqli->begin_transaction();
    try {
        foreach ($metadata as $record) {
            $year = $mysqli->real_escape_string($record['year_name']);
            $protocol = $mysqli->real_escape_string($record['protocol']);
            $p_date = $mysqli->real_escape_string($record['protocol_date']);
            
            // Handle date conversion if needed or NULL if empty
            $mysql_date = !empty($p_date) ? "'" . $mysqli->real_escape_string($p_date) . "'" : "NULL";

            $sql = "INSERT INTO progs_metadata (year_name, protocol, protocol_date) 
                    VALUES ('$year', '$protocol', $mysql_date) 
                    ON DUPLICATE KEY UPDATE protocol='$protocol', protocol_date=$mysql_date";
            $mysqli->query($sql);
        }
        $mysqli->commit();
        echo json_encode(['success' => true]);
    } catch (Exception $e) {
        $mysqli->rollback();
        echo json_encode(['success' => false, 'error' => $e->getMessage()]);
    }
    if (isset($mysqli)) $mysqli->close();
    exit;
}

// ARCHIVE TABLE OPERATION
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'archive') {
    if (!isset($_SESSION['uid']) || ($_SESSION['uid'] !== 'dipeira' && $_SESSION['uid'] !== 'taypeira')) {
        echo json_encode(['success' => false, 'error' => 'Unauthorized action. Admin rank required.']);
        exit;
    }
    
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    try {
        $mysqli = new mysqli($prDbhost, $prDbusername, $prDbpassword, $prDbname);
        $suffix = preg_replace('/[^a-zA-Z0-9\-_]/', '', $_POST['archive_year_suffix']);
        if (empty($suffix)) {
            echo json_encode(['success' => false, 'error' => 'Invalid backup suffix format.']);
            exit;
        }
        
        $backupTableName = "progs_" . $suffix;
        
        // Check if table already exists
        $check = $mysqli->query("SHOW TABLES LIKE '$backupTableName'");
        if ($check->num_rows > 0) {
            echo json_encode(['success' => false, 'error' => 'Το αρχείο ' . $backupTableName . ' υπάρχει ήδη!']);
            exit;
        }
        
        // RENAME causes implicit commit
        $mysqli->query("RENAME TABLE `$prTable` TO `$backupTableName`");
        $mysqli->query("CREATE TABLE `$prTable` LIKE `$backupTableName`");
        
        echo json_encode(['success' => true]);
    } catch(Exception $e) {
        echo json_encode(['success' => false, 'error' => 'Archiving failed: ' . $e->getMessage()]);
    }
    if (isset($mysqli)) $mysqli->close();
    exit;
}

// RESTORE TABLE OPERATION
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'restore') {
    if (!isset($_SESSION['uid']) || ($_SESSION['uid'] !== 'dipeira' && $_SESSION['uid'] !== 'taypeira')) {
        echo json_encode(['success' => false, 'error' => 'Unauthorized action. Admin rank required.']);
        exit;
    }
    
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    try {
        $mysqli = new mysqli($prDbhost, $prDbusername, $prDbpassword, $prDbname);
        $suffix = preg_replace('/[^a-zA-Z0-9\-_]/', '', $_POST['restore_year_suffix']);
        if (empty($suffix)) {
            echo json_encode(['success' => false, 'error' => 'Invalid backup suffix format.']);
            exit;
        }
        
        $backupTableName = "progs_" . $suffix;
        
        // Check if backup table exists
        $check = $mysqli->query("SHOW TABLES LIKE '$backupTableName'");
        if ($check->num_rows === 0) {
            echo json_encode(['success' => false, 'error' => 'Το επιλεγμένο αρχείο δεν υπάρχει!']);
            exit;
        }
        
        $mysqli->query("DROP TABLE IF EXISTS `$prTable`");
        $mysqli->query("RENAME TABLE `$backupTableName` TO `$prTable`");
        
        echo json_encode(['success' => true]);
    } catch(Exception $e) {
        echo json_encode(['success' => false, 'error' => 'Restoration failed: ' . $e->getMessage()]);
    }
    if (isset($mysqli)) $mysqli->close();
    exit;
}

// get program record
if ($_SERVER['REQUEST_METHOD'] === 'GET' && isset($_GET['id'])) {
    if (isset($_GET['year']) && !empty($_GET['year']) && preg_match('/^[a-zA-Z0-9_\-]+$/', $_GET['year'])) {
        $prTable = "progs_" . $_GET['year'];
    }
    // Retrieve the record ID from the GET request
    $recordId = (int)$_GET['id']; // Cast to integer for safety

    // Use the $recordId to fetch the record details from your database
    $conn = new mysqli($prDbhost, $prDbusername, $prDbpassword, $prDbname);

    // Use prepared statement to prevent SQL injection
    $stmt = $conn->prepare("SELECT p.*, s.name as sch1name FROM `$prTable` p JOIN $schTable s ON p.sch1 = s.id WHERE p.id = ?");
    $stmt->bind_param('i', $recordId);
    $stmt->execute();
    $result = $stmt->get_result();

    if ($result->num_rows == 1) {
        // Fetch the record data
        $recordData = $result->fetch_assoc();

        // Close the statement and database connection
        $stmt->close();
        $conn->close();

        // Return the record data as JSON (or any other format you prefer)
        header('Content-Type: application/json');
        echo json_encode($recordData);
    } else {
        // Close the statement and database connection
        $stmt->close();
        $conn->close();
        // Handle the case where the record doesn't exist
        echo 'Record not found';
    }

// get school name (by id)
} else if ($_SERVER['REQUEST_METHOD'] === 'GET' && isset($_GET['sch_id'])) {
    $mysqli = new mysqli($prDbhost, $prDbusername, $prDbpassword, $prDbname);
    // Use prepared statement to prevent SQL injection
    $schId = (int)$_GET['sch_id']; // Cast to integer for safety
    $stmt = $mysqli->prepare("SELECT name FROM $schTable WHERE id = ?");
    $stmt->bind_param('i', $schId);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    $stmt->close();
    $mysqli->close();
    echo $row['name'];

// get all schools
} else if ($_SERVER['REQUEST_METHOD'] === 'GET' && isset($_GET['all_schools'])) {
    $mysqli = new mysqli($prDbhost, $prDbusername, $prDbpassword, $prDbname);

    if ($mysqli->connect_error) {
        die("Connection failed: " . $mysqli->connect_error);
    }
    // Query your database to get options from the $schTable table
    if (isset($_GET['term']) ){
        // Use prepared statement to prevent SQL injection
        $searchTerm = "%" . $_GET['term'] . "%";
        $stmt = $mysqli->prepare("SELECT id, name FROM $schTable WHERE name LIKE ?");
        $stmt->bind_param('s', $searchTerm);
        $stmt->execute();
        $result = $stmt->get_result();
    } else {
        $result = $mysqli->query("SELECT id, name FROM $schTable");
    }    

    $options = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $options[] = array(
                'id' => $row['id'],
                'text' => $row['name']
            );
        }
    }

    // Close the statement and database connection
    if (isset($stmt)) {
        $stmt->close();
    }
    $mysqli->close();

    // Return the options as JSON
    echo json_encode($options);

// add record
} else if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['id']) && $_POST['id'] == 0) {
    // INSERT operation
    // Connect to your database (adjust these parameters as needed)
    $mysqli = new mysqli($prDbhost, $prDbusername, $prDbpassword, $prDbname);

    // Check for a successful database connection
    if ($mysqli->connect_error) {
        $response = ['success' => false, 'error' => 'Database connection error'];
        echo json_encode($response);
        exit;
    }

    // Create an empty array to store the SQL insert fields and values
    $fields = array();
    $values = array();

    // Iterate through the posted fields and construct SQL insert fields and values
    foreach ($_POST as $key => $value) {
        // build the SQL insert fields and values
        if ($key == 'praxidate') {
            $dateTime = DateTime::createFromFormat('d/m/Y', $value);
            if ($dateTime != false) {
                $mysql_date = $dateTime->format('Y-m-d');
                $fields[] = "`$key`";
                $values[] = "'" . $mysql_date . "'";
            }
            continue;
        }
        // Sanitize and validate the values as needed
        $fields[] = "`$key`";
        $values[] = "'" . mysqli_real_escape_string($mysqli, $value) . "'";
    }

    if (count($fields) > 0 && count($values) > 0) {
        // Construct the SQL query for INSERT
        $sql = "INSERT INTO `$prTable` (" . implode(', ', $fields) . ") VALUES (" . implode(', ', $values) . ")";

        // Execute the SQL query to insert the new record
        if (mysqli_query($mysqli, $sql)) {
            $response = ['success' => true];
        } else {
            $response = ['success' => false, 'error' => 'Database insert error: ' . mysqli_error($mysqli)];
        }
    } else {
        $response = ['success' => false, 'error' => 'No fields to insert'];
    }

    // Close the database connection
    $mysqli->close();

    // Return a JSON response indicating success or failure
    echo json_encode($response);
// update record
} elseif ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['id']) && $_POST['id'] > 0) {
    // UPDATE operation
    $recordId = $_POST['id'] > 0 ? $_POST['id'] : false;
    // Connect to your database (adjust these parameters as needed)
    $mysqli = new mysqli($prDbhost, $prDbusername, $prDbpassword, $prDbname);

    // Check for a successful database connection
    if ($mysqli->connect_error) {
        $response = ['success' => false, 'error' => 'Database connection error'];
        echo json_encode($response);
        exit;
    }

    // Create an empty array to store the SQL updates
    $updates = array();

    // Iterate through the posted fields and construct SQL updates
    foreach ($_POST as $key => $value) {
        // Exclude the 'record_id' field and build the SET part of the SQL statement
        if ($key !== 'record_id') {
            if ($key == 'praxidate') {
                $dateTime = DateTime::createFromFormat('d/m/Y', $value);
                if ($dateTime != false) {
                    $mysql_date = $dateTime->format('Y-m-d');
                    $updates[] = "`$key` = '" . $mysql_date . "'";
                }
                continue;
            }
            // Sanitize and validate the values as needed
            $updates[] = "`$key` = '" . mysqli_real_escape_string($mysqli, $value) . "'";
        }
    }

    if (count($updates) > 0) {
        // Construct the SQL query for UPDATE
        $sql = "UPDATE `$prTable` SET " . implode(', ', $updates) . " WHERE id = " . (int)$recordId;

        // Execute the SQL query to update the record
        if (mysqli_query($mysqli, $sql)) {
            $response = ['success' => true];
        } else {
            $response = ['success' => false, 'error' => 'Database update error: ' . mysqli_error($mysqli)];
        }
    } else {
        $response = ['success' => false, 'error' => 'No fields to update'];
    }

    // Close the database connection
    $mysqli->close();

    // Return a JSON response indicating success or failure
    echo json_encode($response);
// delete record
} elseif ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['delete_id'])) {
    $deleteId = $_POST['delete_id'];

    // Connect to the database
    $mysqli = new mysqli($prDbhost, $prDbusername, $prDbpassword, $prDbname);

    // Check for a successful database connection
    if ($mysqli->connect_error) {
        $response = ['success' => false, 'error' => 'Database connection error'];
        echo json_encode($response);
        exit;
    }

    // Prepare the SQL query to delete the record
    $sql = "DELETE FROM `$prTable` WHERE id = ?";
    $stmt = $mysqli->prepare($sql);

    if ($stmt) {
        $stmt->bind_param('i', $deleteId);

        // Execute the statement
        if ($stmt->execute()) {
            $response = ['success' => true];
        } else {
            $response = ['success' => false, 'error' => 'Database delete error: ' . $stmt->error];
        }

        $stmt->close();
    } else {
        $response = ['success' => false, 'error' => 'Statement preparation failed: ' . $mysqli->error];
    }

    // Close the database connection
    $mysqli->close();

    // Return a JSON response
    echo json_encode($response);
} else {
    // Handle invalid or missing parameters
    $response = ['success' => false, 'error' => 'Invalid request'];
    echo json_encode($response);
}
//}
?>