<?php
// Include the configuration file that contains the DB connection constants
require_once 'config.php'; // Since it's in the same directory

// PDO Database connection
function db() {
    static $pdo;
    $dbhost = DBHOST;
    $dbuser = DBUSER;
    $dbpass = DBPASS;
    $dbname = DBNAME;

    if ($pdo === NULL) {
        try {
            $pdo = new PDO("mysql:host=$dbhost;dbname=$dbname;charset=utf8", $dbuser, $dbpass);
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            die("Database connection failed: " . $e->getMessage());
        }
    }

    return $pdo;
}

// Secure function to check if a poet username exists using prepared statements
function poetUsernameExists($username) {
    $pdo = db();
    $query = "SELECT COUNT(*) FROM sm_poets WHERE username = :username";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(":username", $username);
    $stmt->execute();
    return $stmt->fetchColumn();
}

// Secure function to check if a poet email exists using prepared statements
function poetEmailExists($email) {
    $pdo = db();
    $query = "SELECT COUNT(*) FROM sm_poets WHERE email = :email";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(":email", $email);
    $stmt->execute();
    return $stmt->fetchColumn();
}

// Get poet by ID securely using prepared statements
function getPoet($poet_id) {
    $pdo = db();
    $query = "SELECT * FROM sm_poets WHERE id = :poet_id";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(":poet_id", $poet_id, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

// Get editor by ID securely using prepared statements
function getEditor($editor_id) {
    $pdo = db();
    $query = "SELECT * FROM sm_editors WHERE id = :editor_id";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(":editor_id", $editor_id, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

// Get poem details securely using prepared statements
function getPoem($poem_id) {
    $pdo = db();
    $query = "SELECT *, a.id AS poem_id, b.id AS author_id
              FROM sm_submissions AS a
              INNER JOIN sm_poets AS b ON a.poetid = b.id
              WHERE a.id = :poem_id";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(":poem_id", $poem_id, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

// Get editor name securely
function getEditorName($editor_id) {
    if ($editor_id == '999999') return 'Super Administrator';

    $pdo = db();
    $query = "SELECT first_name, last_name FROM sm_editors WHERE id = :editor_id";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(":editor_id", $editor_id, PDO::PARAM_INT);
    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Check if a row was found
    if ($row) {
        return $row['first_name'] . ' ' . $row['last_name'];
    } else {
        return 'Unknown Editor'; // Return a default value if no row is found
    }
}

// Get the current edition
function getCurrentEdition() {
    $pdo = db();
    $query = "SELECT id FROM sm_editions WHERE is_open = 1";
    $stmt = $pdo->query($query);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Check if a row was found
    if ($row) {
        return $row['id'];
    } else {
        return null; // Return null if no current edition is found
    }
}

// Update a setting securely
function settingSet($var, $value) {
    $pdo = db();
    $query = "UPDATE sm_settings SET value = :value WHERE var = :var";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(":value", $value);
    $stmt->bindParam(":var", $var);
    $stmt->execute();
}

// Get a setting securely
function settingGet($var) {
    $pdo = db();
    $query = "SELECT value FROM sm_settings WHERE var = :var";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(":var", $var);
    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Check if a row was found
    if ($row) {
        return $row['value'];
    } else {
        return null; // Return null if no setting is found
    }
}

// Get edition name securely
function getEditionName($edition_id) {
    $pdo = db();
    $query = "SELECT edition FROM sm_editions WHERE id = :edition_id";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(":edition_id", $edition_id, PDO::PARAM_INT);
    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Check if a row was found
    if ($row) {
        return $row['edition'];
    } else {
        return 'Unknown Edition'; // Return a default value if no row is found
    }
}

// Generate a new GUID
function NewGuid() {
    $guid = strtoupper(md5(uniqid(rand(), true)));
    $guidText = substr($guid, 0, 8) . '-' .
                substr($guid, 8, 4) . '-' .
                substr($guid, 12, 4) . '-' .
                substr($guid, 16, 4) . '-' .
                substr($guid, 20);
    return $guidText;
}

// Secure function to check if an editor email exists using prepared statements
function editorEmailExists($email) {
    $pdo = db();
    $query = "SELECT COUNT(*) FROM sm_editors WHERE email = :email";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(":email", $email);
    $stmt->execute();
    return $stmt->fetchColumn();
}

// Get the client's IP address securely
function get_client_ip_server() {
    $ipaddress = '';
    if (!empty($_SERVER['HTTP_CLIENT_IP'])) {
        $ipaddress = $_SERVER['HTTP_CLIENT_IP'];
    } elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
        $ipaddress = $_SERVER['HTTP_X_FORWARDED_FOR'];
    } elseif (!empty($_SERVER['REMOTE_ADDR'])) {
        $ipaddress = $_SERVER['REMOTE_ADDR'];
    } else {
        $ipaddress = 'UNKNOWN';
    }
    return $ipaddress;
}

// Count submissions by edition securely
function countSubmissionByEdition($poet_id, $edition_id) {
    $pdo = db();
    $query = "SELECT COUNT(*) AS cnt FROM sm_submissions WHERE editionid = :edition_id AND poetid = :poet_id";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(":edition_id", $edition_id, PDO::PARAM_INT);
    $stmt->bindParam(":poet_id", $poet_id, PDO::PARAM_INT);
    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Check if a row was found
    if ($row) {
        return $row['cnt'];
    } else {
        return 0; // Return 0 if no submissions are found
    }
}

// Count accepted poems by edition securely
function countPoemByEdition($edition_id) {
    $pdo = db();
    $query = "SELECT COUNT(*) AS cnt FROM sm_submissions WHERE editionid = :edition_id AND status = 1";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(":edition_id", $edition_id, PDO::PARAM_INT);
    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Check if a row was found
    if ($row) {
        return $row['cnt'];
    } else {
        return 0; // Return 0 if no accepted poems are found
    }
}

// Calculate the average rating of a submission securely
function getAverageRating($submission_id) {
    $pdo = db();
    $query = "SELECT COUNT(*) AS cnt, SUM(rating) AS ttl FROM sm_submissions_ratings WHERE submission_id = :submission_id";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(":submission_id", $submission_id, PDO::PARAM_INT);
    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    // Check if a row was found and there are ratings
    if ($row && $row['cnt'] > 0) {
        return number_format($row['ttl'] / $row['cnt'], 1);
    } else {
        return '-'; // Return '-' if no ratings are found
    }
}
?>
