<?php
/**
 * libAuthorities.php - CRUD-Klasse für Thesaurus-Datenbank
 * 
 * Sicherheitsverbesserungen:
 * - Prepared Statements mit benannten Parametern
 * - Whitelist für authType
 * - Input-Validierung
 * - Fehlerbehandlung
 */

class CRUD
{
    protected $db;
    
    // Whitelist für erlaubte Typen
    private $allowedTypes = ['Subject', 'Person', 'Corporate', 'Publisher', 'Classification'];
    
    // Whitelist für erlaubte Sortierfelder
    private $allowedSortFields = [
        'Anchor.ID', 'Anchor.Text', 'Anchor.Type', 'Anchor.DetailType', 
        'Entry.Text', 'Entry.ID', 'Entry.DateCreated', 'Entry.DateModified'
    ];

    function __construct()
    {
        $this->db = DB();
    }

    function __destruct()
    {
        $this->db = null;
    }
    
    /**
     * Validiert den authType gegen Whitelist
     */
    private function validateAuthType($authType)
    {
        if (!in_array($authType, $this->allowedTypes)) {
            throw new InvalidArgumentException("Ungültiger Typ: $authType");
        }
        return $authType;
    }
    
    /**
     * Validiert und bereinigt Sortierfeld
     */
    private function validateSortField($sort)
    {
        if (empty($sort)) {
            return 'Entry.Text';
        }
        
        $sortField = trim(str_replace(['ASC', 'DESC', 'asc', 'desc'], '', $sort));
        $sortField = trim($sortField);
        
        if (!in_array($sortField, $this->allowedSortFields)) {
            return 'Entry.Text';
        }
        
        return $sort;
    }
    
    /**
     * Validiert Integer-Werte
     */
    private function validateInt($value, $min = 0)
    {
        $intVal = intval($value);
        return ($intVal >= $min) ? $intVal : $min;
    }

    /**
     * Baut ein SQL-LIKE-Pattern aus einem Suchterm.
     * Führendes * → Linkstrunkierung (%...)
     * Abschließendes * → Rechtstrunkierung (...%)
     * Kein * → nur Rechtstrunkierung (Standard-Verhalten)
     */
    private function buildSearchPattern(string $term): string
    {
        $leftTrunc  = str_starts_with($term, '*');
        $rightTrunc = str_ends_with($term, '*');
    
        $clean = trim($term, '*');
        $clean = addcslashes($clean, '%_\\');
    
        return ($leftTrunc ? '%' : '') .
               $clean .
               ($rightTrunc ? '%' : '%');
    }

    /**
     * Dubletten-Prüfung über Anchor.Text (normalisierte Form).
     *
     * Anchor.Text enthält den normalisierten Descriptor (Kleinbuchstaben,
     * Sonderzeichen durch '_' ersetzt, erzeugt durch prepare_desc()).
     * Diese Prüfung ist robuster als ein Vergleich auf Entry.Text, weil
     * sie Schreibvarianten (Groß-/Kleinschreibung, Umlaute, Leerzeichen)
     * zuverlässig als Dublette erkennt.
     *
     * @param  string $authType       Typ-Whitelist-Wert ('Subject', 'Person', ...)
     * @param  string $normalizedText Ergebnis von prepare_desc($term)
     * @return int                    Anzahl gefundener Einträge (0 = keine Dublette)
     */
    public function checkDuplicateByNormalizedText($authType, $normalizedText)
    {
        $authType = $this->validateAuthType($authType);

        $stmt = $this->db->prepare(
            "SELECT COUNT(*) AS count
             FROM Anchor
             WHERE Text = :normalizedText
               AND Type = :authType"
        );
        $stmt->execute([
            ':normalizedText' => $normalizedText,
            ':authType'       => $authType
        ]);

        $rec = $stmt->fetch(PDO::FETCH_ASSOC);
        return $rec ? intval($rec['count']) : 0;
    }
    
    public function readAuthorityNumbersOfRecords($authType, $search, $id, $sharp)
    {
        error_log("DEBUG search='" . $search . "' pattern='" . $this->buildSearchPattern($search) . "'");

        $authType = $this->validateAuthType($authType);
        $id = $this->validateInt($id);
        
        $params = [':authType' => $authType];
        
        $query = "SELECT COUNT(*) as count FROM Anchor 
                  JOIN Entry ON (Anchor.ID = Entry.ID) 
                  WHERE Type = :authType";
        
        if (strlen($search) > 0 && $sharp == false) {
            $query .= " AND LOWER(Entry.CompleteText) LIKE LOWER(:search)";
            $params[':search'] = $this->buildSearchPattern($search);
        }
        if (strlen($search) > 0 && $sharp == true) {
            $query .= " AND LOWER(Entry.Text) = LOWER(:search)";
            $params[':search'] = $search;
        }
        if ($id > 0) {
            $query .= " AND Entry.ID = :id";
            $params[':id'] = $id;
        }
        
        $stmt = $this->db->prepare($query);
        $stmt->execute($params);
        
        $rec = $stmt->fetch(PDO::FETCH_ASSOC);
        return $rec ? intval($rec['count']) : 0;
    }

    public function readAuthorityEntriesByID($authType, $id)
    {
        $authType = $this->validateAuthType($authType);
        $id = $this->validateInt($id, 1);
        
        $query = "SELECT Anchor.ID AS ID, Anchor.DetailType AS DetailType, 
                         Anchor.Type AS Type, Anchor.Classification AS Classification, 
                         Entry.Text as Text, Entry.Comment as Scopenote 
                  FROM Anchor 
                  JOIN Entry ON (Anchor.ID = Entry.ID) 
                  WHERE Anchor.ID = :id AND Type = :authType";
        
        $stmt = $this->db->prepare($query);
        $stmt->execute([':id' => $id, ':authType' => $authType]);

        $count = $stmt->rowCount();
        $ret = [];
        
        while ($rec = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $relations = $this->getRelations($authType, $rec['ID']);
            $ret[] = [
                "ID"             => $rec['ID'], 
                "Descriptor"     => $relations['Descriptor'], 
                "Text"           => $rec['Text'],  
                "DetailType"     => $rec['DetailType'], 
                "Type"           => $rec['Type'], 
                "Scopenote"      => $rec['Scopenote'], 
                "Relations"      => $relations['Data'], 
                "Classification" => $rec['Classification']
            ];
        }

        return ["COUNT" => $count, "RECORDS" => $ret];
    }

    public function readAuthorityEntries($authType, $offset, $search, $id, $sort, $max)
    {
        error_log("DEBUG search='" . $search . "' pattern='" . $this->buildSearchPattern($search) . "'", 3, "error.log");


        $authType = $this->validateAuthType($authType);
        $offset = $this->validateInt($offset);
        $max = $this->validateInt($max, 1);
        $id = $this->validateInt($id);
        $sort = $this->validateSortField($sort);
        
        $params = [':authType' => $authType];
        
        $query = "SELECT Anchor.ID AS ID, Anchor.DetailType AS DetailType, 
                         Anchor.Type AS Type, Anchor.Classification AS Classification, 
                         Entry.Text as Text, Entry.Comment as Scopenote 
                  FROM Anchor 
                  JOIN Entry ON (Anchor.ID = Entry.ID) 
                  WHERE Type = :authType";
        
        if (strlen($search) > 0) {
            $query .= " AND LOWER(Entry.CompleteText) LIKE LOWER(:search)";
            $params[':search'] = $this->buildSearchPattern($search);
        }
        if ($id > 0) {
            $query .= " AND Entry.ID = :id";
            $params[':id'] = $id;
        }
        
        // $query .= " ORDER BY " . $sort . " LIMIT " . intval($offset) . "," . intval($max);

if ($authType === 'Classification') {
    $orderClause = "
        SUBSTRING_INDEX(Anchor.Text, ' ', 1),
        CAST(
            CASE 
                WHEN LOCATE(' ', Anchor.Text) > 0 
                THEN SUBSTRING_INDEX(SUBSTRING_INDEX(Anchor.Text, ' ', -1), '.', 1)
                ELSE NULL 
            END AS UNSIGNED
        ),
        CAST(
            CASE 
                WHEN LOCATE('.', Anchor.Text) > 0 
                THEN SUBSTRING_INDEX(Anchor.Text, '.', -1)
                ELSE NULL 
            END AS UNSIGNED
        )
    ";
} else {
    $orderClause = $sort . " COLLATE utf8mb4_unicode_ci";
}

error_log("DEBUG ORDER BY: " . $orderClause, 3, "/var/www/tools/html/Thesaurus/error.log");

$query .= " ORDER BY " . $orderClause . " LIMIT " . intval($offset) . "," . intval($max);

        
        $stmt = $this->db->prepare($query);
        $stmt->execute($params);

        $count = $stmt->rowCount();
        $ret = [];
        
        while ($rec = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $relations = $this->getRelations($authType, $rec['ID']);
            
            if ($authType === 'Subject') {
                $synonyms = $this->getSynonyms($rec['Text']);
                $ret[] = [
                    "ID"             => $rec['ID'], 
                    "Descriptor"     => $relations['Descriptor'], 
                    "Text"           => $rec['Text'],  
                    "DetailType"     => $rec['DetailType'], 
                    "Type"           => $rec['Type'], 
                    "Scopenote"      => $rec['Scopenote'], 
                    "Relations"      => $relations['Data'], 
                    "Classification" => $rec['Classification'], 
                    "Synonyms"       => $synonyms['Synonyms'], 
                    "Search"         => $synonyms['Search']
                ];
            } else {
                $ret[] = [
                    "ID"             => $rec['ID'], 
                    "Descriptor"     => $relations['Descriptor'], 
                    "Text"           => $rec['Text'],  
                    "DetailType"     => $rec['DetailType'], 
                    "Type"           => $rec['Type'], 
                    "Scopenote"      => $rec['Scopenote'], 
                    "Relations"      => $relations['Data'], 
                    "Classification" => $rec['Classification']
                ];
            }
        }

        return ["COUNT" => $count, "RECORDS" => $ret];
    }

    public function getRelations($authType, $id)
    {
        $authType = $this->validateAuthType($authType);
        $id = $this->validateInt($id, 1);
        
        $query = "SELECT Linking.ID as LinkingID, IDEntry, Entry.Text, Entry.Comment, 
                         Relationtype, DetailType 
                  FROM Linking 
                  JOIN Entry ON (Linking.IDEntry = Entry.ID) 
                  JOIN Anchor ON (IDAnchor = Anchor.ID) 
                  WHERE IDAnchor = :id AND Anchor.Type = :authType AND Relationtype != '' 
                  ORDER BY Relationtype";
        
        $stmt = $this->db->prepare($query);
        $stmt->execute([':id' => $id, ':authType' => $authType]);

        $descriptor = true;
        $ret = [];
        
        while ($rec = $stmt->fetch(PDO::FETCH_ASSOC)) {
            if (strtolower($rec['Relationtype']) === "use") {
                $descriptor = false;
            }
            $ret[] = [
                "IDLinking"      => $rec['LinkingID'], 
                "IDEntry"        => $rec['IDEntry'], 
                "IDRelation"     => $rec['IDEntry'], 
                "Detailtype"     => $rec['DetailType'], 
                "TextRelation"   => $rec['Text'], 
                "CommentRelation"=> $rec['Comment'], 
                "Relationtype"   => $rec['Relationtype']
            ];
        }
        
        return ["Descriptor" => $descriptor, "Data" => $ret];
    }

    public function getSynonyms($input)
    {
        $desc = $this->prepare_desc($input);
        
        $query = "SELECT IDLinking FROM Synonyms WHERE Descriptor = :desc";
        $stmt = $this->db->prepare($query);
        $stmt->execute([':desc' => $desc]);

        $ret = '';
        $retsearch = "topic:'" . addslashes($input) . "' OR ";
        
        while ($rec = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $idSearch = intval($rec['IDLinking']);
            
            $query2 = "SELECT Text FROM Synonyms WHERE IDLinking = :idLinking";
            $stmt2 = $this->db->prepare($query2);
            $stmt2->execute([':idLinking' => $idSearch]);
            
            while ($rec2 = $stmt2->fetch(PDO::FETCH_ASSOC)) {
                if ($rec2['Text'] === $input) continue;
                $ret .= $rec2['Text'] . ", ";
                $retsearch .= "topic:'" . addslashes($rec2['Text']) . "' OR ";
            }
        }
        
        if (strlen($ret) == 0) {
            return ["Synonyms" => '', "Search" => ''];
        } else {
            $synonyms = trim(substr($ret, 0, -2));
            $search   = trim(substr($retsearch, 0, -4));
            return ["Synonyms" => $synonyms, "Search" => $search];
        }
    }

    public function getEntryText($authType, $request)
    {
        $authType = $this->validateAuthType($authType);
        
        $query = "SELECT Anchor.ID AS AnchorID, Entry.Text as Text 
                  FROM Anchor 
                  JOIN Entry ON (Anchor.ID = Entry.ID) 
                  WHERE Anchor.Text LIKE LOWER(:request) AND Anchor.Type = :authType 
                  ORDER BY Entry.Text";
        
        $stmt = $this->db->prepare($query);
        $stmt->execute([':request' => '%' . $request . '%', ':authType' => $authType]);

        $ret = [];
        while ($rec = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $ret[] = ["Text" => $rec['Text'], "ID" => $rec['AnchorID']];
        }

        return $ret;
    }

    public function insertNewTerm($authType, $term, $desc, $detailtype, $classification, $scopenote, $completetext)
    {
        $authType = $this->validateAuthType($authType);
        
        $query = $this->db->prepare(
            "INSERT INTO Anchor (ID, Text, DetailType, Type, Classification) 
             VALUES (:ID, :Text, :DetailType, :Type, :Classification)"
        );
        
        $query->execute([
            ':ID'             => null,
            ':Text'           => $desc,
            ':DetailType'     => $detailtype,
            ':Type'           => $authType,
            ':Classification' => $classification
        ]);

        $IDAnchor = $this->db->lastInsertId();

        $query = $this->db->prepare(
            "INSERT INTO Entry (ID, Text, Comment, Language, CompleteText, DateCreated, DateModified) 
             VALUES (:ID, :Text, :Comment, :Language, :CompleteText, :DateCreated, :DateModified)"
        );

        $query->execute([
            ':ID'           => $IDAnchor,
            ':Text'         => $term,
            ':Comment'      => $scopenote,
            ':Language'     => 'de',
            ':CompleteText' => $completetext,
            ':DateCreated'  => date('Y-m-d H:i:s'),
            ':DateModified' => '0000-00-00 00:00:00'
        ]);

        $IDEntry = $IDAnchor;

        return ["IDAnchor" => $IDAnchor, "IDEntry" => $IDEntry, "IDLinking" => 0];
    }

    public function writeNewRelation($anchorID, $relationType, $relationID)
    {
        $anchorID   = $this->validateInt($anchorID, 1);
        $relationID = $this->validateInt($relationID, 1);
        
        $allowedRelations = ['BT', 'NT', 'RT', 'USE', 'UF'];
        if (!in_array(strtoupper($relationType), $allowedRelations)) {
            throw new InvalidArgumentException("Ungültiger Relationstyp: $relationType");
        }
        
        $query = $this->db->prepare(
            "INSERT INTO Linking (ID, IDAnchor, IDEntry, Relationtype) 
             VALUES (:ID, :IDAnchor, :IDEntry, :Relationtype)"
        );

        $query->execute([
            ':ID'           => null,
            ':IDAnchor'     => $anchorID,
            ':IDEntry'      => $relationID,
            ':Relationtype' => $relationType
        ]);

        return $this->db->lastInsertId();
    }

    public function deleteRelation($anchorID, $linkingID)
    {
        $linkingID = $this->validateInt($linkingID, 1);
        
        $query = $this->db->prepare("DELETE FROM Linking WHERE ID = :id");
        $query->execute([':id' => $linkingID]);

        return $linkingID;
    }

    public function deleteTerm($anchorID)
    {
        $anchorID = $this->validateInt($anchorID, 1);
        $ret = [];

        $query = $this->db->prepare("DELETE FROM Linking WHERE IDAnchor = :id");
        $query->execute([':id' => $anchorID]);
        $ret[] = "Linking gelöscht";

        $query = $this->db->prepare("DELETE FROM Entry WHERE ID = :id");
        $query->execute([':id' => $anchorID]);
        $ret[] = "Entry gelöscht";

        $query = $this->db->prepare("DELETE FROM Anchor WHERE ID = :id");
        $query->execute([':id' => $anchorID]);
        $ret[] = "Anchor gelöscht";

        return $ret;
    }

    public function updateTerm($anchorID, $authType, $desc, $term, $type, $detailtype, $classification, $scopenote, $completetext)
    {
        $anchorID = $this->validateInt($anchorID, 1);
        $authType = $this->validateAuthType($authType);
        
        $query = $this->db->prepare(
            "UPDATE Anchor 
             SET Text = :text, Type = :type, DetailType = :detailtype, Classification = :classification 
             WHERE ID = :id"
        );
        
        $query->execute([
            ':text'           => $desc,
            ':type'           => $authType,
            ':detailtype'     => $detailtype,
            ':classification' => $classification,
            ':id'             => $anchorID
        ]);

        $query = $this->db->prepare(
            "UPDATE Entry 
             SET Text = :text, Comment = :comment, Language = :language, 
                 CompleteText = :completetext, DateModified = :datemodified 
             WHERE ID = :id"
        );

        $query->execute([
            ':text'         => $term,
            ':comment'      => $scopenote,
            ':language'     => 'de',
            ':completetext' => $completetext,
            ':datemodified' => date('Y-m-d H:i:s'),
            ':id'           => $anchorID
        ]);

        return "OK";
    }

    public function getStatistics($dateStart, $dateEnd)
    {
        if (!preg_match('/^\d{4}-\d{2}-\d{2}/', $dateStart) || 
            !preg_match('/^\d{4}-\d{2}-\d{2}/', $dateEnd)) {
            throw new InvalidArgumentException("Ungültiges Datumsformat");
        }
        
        $r = [];
        $dateTypes = ["DateCreated", "DateModified"];
        
        foreach ($this->allowedTypes as $type) {
            foreach ($dateTypes as $dateType) {
                $query = $this->db->prepare(
                    "SELECT COUNT(*) AS COUNT 
                     FROM Anchor 
                     JOIN Entry ON (Anchor.ID = Entry.ID) 
                     WHERE Type = :type AND $dateType BETWEEN :dateStart AND :dateEnd"
                );
                
                $query->execute([
                    ':type'      => $type,
                    ':dateStart' => $dateStart,
                    ':dateEnd'   => $dateEnd
                ]);

                $rec = $query->fetch(PDO::FETCH_ASSOC);
                $r[$type][$dateType] = $rec ? intval($rec['COUNT']) : 0;
            }
        }
        
        return $r;
    }

    public function prepare_desc($text)
    {
        $desc = is_array($text) ? $text[0] : $text;
        $text = strtolower($desc);
        $desc = str_replace(' ', '_', $text);
        
        $search  = ['ü', 'ä', 'ö', 'ß', '.', ',', 'Ö', 'Ü', 'Ä', '[', ']', '<', '>', '""'];
        $replace = ['ue', 'ae', 'oe', 'ss', '', '', 'oe', 'ue', 'ae', '_', '_', '&lt;', '&gt;', '"'];
        $desc = str_replace($search, $replace, $desc);

        return $desc;
    }
}
?>