<?php
include 'header.php';
$user_id = $_SESSION['user_id'];
$curDate = date('Y-m-d H:i:s');

$querySel = "select l.id, l.exam_id, se.exam_name, l.entity_id, en.name, l.display_text, l.display_text_dates, date from sp_exam_links as l left join sp_exam_entity as en on en.id=l.entity_id LEFT JOIN sp_exam as se on se.exam_id=l.exam_id where l.status='1' and l.date !='' and l.entity_id IN(8, 12, 11) order by l.id desc limit 300";
$query = $db->prepare($querySel);
$query->execute();

$sortedExams = [];
$today = date('Y-m-d');

function parseExamDate($dateString) {
    $dateString = trim($dateString);
    $invalidFormats = ['To be Announced', 'Every Month'];
    
    if (in_array($dateString, $invalidFormats)) {
        return null;
    }
    if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $dateString)) {
        return $dateString;
    }
    if (preg_match('/([A-Za-z]+)\s+(\d{4})/', $dateString, $matches)) {
        return date('Y-m-d', strtotime("1 {$matches[1]} {$matches[2]}"));
    }
    if (strpos($dateString, ',') !== false) {
        $dates = explode(',', $dateString);
        $validDates = array_map('trim', $dates);
        sort($validDates);
        return $validDates[1];
    }

    if (preg_match('/Starts from\s+(\d{1,2})(?:st|nd|rd|th)?\s+([A-Za-z]+)\s+(\d{4})/', $dateString, $matches)) {
        return date('Y-m-d', strtotime("{$matches[1]} {$matches[2]} {$matches[3]}"));
    }

    if (preg_match('/([A-Za-z]+)\s*\/\s*([A-Za-z]+)\s+(\d{4})/', $dateString, $matches)) {
        return date('Y-m-d', strtotime("1 {$matches[1]} {$matches[3]}"));
    }

    if (preg_match('/(\d{1,2}).*([A-Za-z]+)\s+(\d{4})/', $dateString, $matches)) {
        return date('Y-m-d', strtotime("{$matches[1]} {$matches[2]} {$matches[3]}"));
    }

    if (preg_match('/(\d{1,2})(?:st|nd|rd|th)?\s+([A-Za-z]+)\s+(\d{4})\s+Onwards/', $dateString, $matches)) {
        return date('Y-m-d', strtotime("{$matches[1]} {$matches[2]} {$matches[3]}"));
    }

    if (preg_match_all('/(\d{1,2})/', $dateString, $dayMatches) &&
        preg_match('/([A-Za-z]+)\s+(\d{4})/', $dateString, $monthYearMatch)) {
        $earliestDate = null;
        foreach ($dayMatches[1] as $day) {
            $parsedDate = date('Y-m-d', strtotime("$day {$monthYearMatch[1]} {$monthYearMatch[2]}"));
            if (!$earliestDate || $parsedDate < $earliestDate) {
                $earliestDate = $parsedDate;
            }
        }
        return $earliestDate;
    }

    return null;
}

while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
    $parsedDate = parseExamDate($row['date']);
    
    // Exclude past exams
    if ($parsedDate !== null && $parsedDate <= $today) {
        continue;
    }

    $sortedExams[] = [
        'id' => $row['id'],
        'exam_id' => $row['exam_id'],
        'exam_name' => $row['exam_name'],
        'entity_id' => $row['entity_id'],
        'name' => $row['name'],
        'display_text' => $row['display_text'],
        'display_text_dates' => $row['display_text_dates'],
        'date' => $row['date'],
        'sort_date' => $parsedDate
    ];
}

usort($sortedExams, function ($a, $b) {
    return strtotime($a['sort_date']) - strtotime($b['sort_date']);
});

// Output sorted exams
// echo "<pre>";
// print_r($sortedExams);
// echo "</pre>";

// while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
//     $id = $row['id'];
// }
?>

<script type="text/javascript" charset="utf8" src="assets/js/jquery-1.4.1.min.js"></script>
<script type="text/javascript" charset="utf8" src="assets/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" href="assets/css/jquery.dataTables.min.css">

<main id="main-container">
    <div class="content content-narrow">
        <div class="row">
            <div class="col-lg-12">
                <h2 class="content-heading">Exam Upcoming Entities</h2>
                <div class="table-responsive">
                    <table id="notification" class="table table-striped table-bordered dt-responsive nowrap" style="width: 100%; cellspacing: 0;">
                        <thead>
                            <tr class="danger">
                                <th>#N</th>
                                <th>Exam Name</th>
                                <th>Entity</th>
                                <th>Text1</th>
                                <th>Text2</th>
                                <th>date</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php $i=1; 
                            foreach($sortedExams as $exm){ ?>
                            <tr>
                                <td><?php echo $i; ?></td>
                                <td><?php echo $exm['exam_name']; ?></td>
                                <td><?php echo $exm['name']; ?></td>
                                <td><?php echo $exm['display_text']; ?></td>
                                <td><?php echo $exm['display_text_dates']; ?></td>
                                <td><?php echo $exm['date']; ?></td>
                            </tr>
                            <?php $i++; } ?>
                        </tbody>
                    </table>

                    <script type="text/javascript">
                        jQuery.noConflict();
                        jQuery(document).ready(function() {
                            jQuery('#notification').dataTable({
                                "dom": "Bfrtip",
                                "pagingType": "full_numbers",
                                "processing": true,
                                "serverSide": false,
                                "responsive": true,
                                "bPaginate": true,
                                "iDisplayLength": 50,
                                "lengthMenu": [
                                    [50, 100, 200, 500, 1000],
                                    [50, 100, 200, 500, 1000]
                                ]
                            });
                        });
                    </script>
                </div>
            </div>
        </div>
    </div>
</main>

<script>
</script>

<?php include 'footer.php'; ?>