 <?php

$jsonData = file_get_contents('php://input');

$data = json_decode($jsonData, true);

$db = new SQLite3('./.db.db');
if (!$db) {
    die("Connection failed: " . $db->lastErrorMsg());
}

$seenquery = "CREATE TABLE IF NOT EXISTS rtxseen (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    deviceid TEXT,
    announcement_id TEXT,
    rtxseens INTEGER
)";

$db->exec($seenquery);

if (isset($data['action']) && $data['action'] === "get-announcements") {
    
        $note = $db->query('SELECT * FROM note');
        
        
        function checkid($deviceid, $announcement_id) {
            $db = new SQLite3('./.db.db');
            $checkQuery = "SELECT COUNT(*) AS count FROM rtxseen WHERE deviceid = :deviceid AND announcement_id = :announcement_id";
        
            $stmt = $db->prepare($checkQuery);
            $stmt->bindValue(':deviceid', $deviceid, SQLITE3_TEXT);
            $stmt->bindValue(':announcement_id', $announcement_id, SQLITE3_TEXT);
        
            $result = $stmt->execute();
            $row = $result->fetchArray(SQLITE3_ASSOC);
            $db->close();
            
            
            if ($row['count'] > 0) {
               return 1;
            } else {
               return 0;
            }
        }
        

        while ($notes = $note->fetchArray(SQLITE3_ASSOC)) {
        	$cdata[] = ['id' => $notes['Title'], 'title' => $notes['Title'], 'message' => $notes['Description'], 'created_on' => $notes['CreateDate'],'seen' => checkid($data['deviceid'], $notes['Title'])];
        }
        
        $jdata = json_encode($cdata);
        $annousement = '{"result":"success","sc":"' . $data['sc'] . '","message":"No Announcements Available","totalrecords":0,"data":'.$jdata.'}';
    
        echo $annousement;
    
}
?>

