<?php
// --- Database and Data Fetching Logic ---

$db_path = __DIR__ . '/doe_data.sqlite';
$results = [];
$file_no_from_url = '';

// Check if a file_no is provided in the URL
if (isset($_GET['file_no']) && !empty($_GET['file_no'])) {
    $file_no_from_url = filter_input(INPUT_GET, 'file_no', FILTER_SANITIZE_NUMBER_INT);

    try {
        // Connect to the SQLite database
        $pdo = new PDO('sqlite:' . $db_path);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // Prepare and execute the query to prevent SQL injection
        $stmt = $pdo->prepare("SELECT * FROM certificates WHERE file_no = :file_no");
        $stmt->bindValue(':file_no', $file_no_from_url, PDO::PARAM_INT);
        $stmt->execute();

        // Fetch all matching results
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    } catch (PDOException $e) {
        // For a real application, you would log this error instead of displaying it
        die("Database error: " . $e->getMessage());
    }
}
?><!DOCTYPE html>
<html lang="en" data-bs-theme="light"><!--begin::Head-->

<head>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
    <!-- base href="https://ecc-beta.doe.gov.bd/" -->
    <title>DoE ECC System</title>
    <meta charset="utf-8">
    <meta name="description"
        content="Department of Environment (DoE) is a government department responsible for the protection of the environment in Bangladesh">
    <meta name="keywords" content="DoE,Department,Environment,Clearance,Certificate,Bangladesh">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="shortcut icon" href="https://ecc-beta.doe.gov.bd/assets/media/logos/favicon.ico">
    <link rel="stylesheet" href="DoE%20ECC%20System_files/css_CHsI.css">
    <!-- Add Font Awesome CDN to ensure icons load correctly -->
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.1/css/all.min.css"
        integrity="sha512-DTOQO9RWCH3ppGqcWaEA1BIZOC6xxalwEsw9c2QQeAIftl+Vegovlnee1c9QX4TctnWMn13TZye+giMm8e2LwA=="
        crossorigin="anonymous" referrerpolicy="no-referrer" />
    <link href="DoE%20ECC%20System_files/plugins.bundle_CHsI.css" rel="stylesheet" type="text/css">
    <link href="DoE%20ECC%20System_files/style.bundle_CHsI.css" rel="stylesheet" type="text/css">
    <link href="DoE%20ECC%20System_files/custom_CHsI.css" rel="stylesheet" type="text/css">
    <script>
        // Frame-busting to prevent site from being loaded within a frame without permission (click-jacking) 
        if (window.top != window.self) {
            window.top.location.replace(window.self.location.href);
        }
    </script>

    <style>
        @media (min-width: 768px) {
            .w-md-600px {
                width: 720px !important;
            }
        }

        .flex-center {
            justify-content: center;
            align-items: normal;
        }

        .container {
            padding: 10px;
            margin: 0px;
            border: none;
        }

        .instruction i,
        #instruction i {
            color: black;
        }

        .manual {
            font-size: 20px;
        }
    </style>
</head>

<body id="kt_body" class="app-blank bgi-size-cover bgi-attachment-fixed bgi-position-center bgi-no-repeat">
    <script>
        var defaultThemeMode = "light";
        var themeMode;
        if (document.documentElement) {
            if (document.documentElement.hasAttribute("data-bs-theme-mode")) {
                themeMode = document.documentElement.getAttribute("data-bs-theme-mode");
            } else {
                if (localStorage.getItem("data-bs-theme") !== null) {
                    themeMode = localStorage.getItem("data-bs-theme");
                } else {
                    themeMode = defaultThemeMode;
                }
            }
            if (themeMode === "system") {
                themeMode = window.matchMedia("(prefers-color-scheme: dark)").matches ? "dark" : "light";
            }
            document.documentElement.setAttribute("data-bs-theme", themeMode);
        }
    </script>
    <div class="d-flex flex-column flex-root" id="kt_app_root">
        <!--begin::Page bg image-->
        <style>
            body {
                background-image: url('DoE ECC System_files/bg.jpg');
            }

            [data-bs-theme="dark"] body {
                background-image: url('DoE ECC System_files/bg.jpg');
            }
        </style>
        <!--end::Page bg image-->
        <!--begin::Authentication - Sign-in -->
        <div class="d-flex flex-column flex-column-fluid flex-lg-row">
            <!--begin::Aside-->
            <div class="d-flex flex-center w-lg-50 pt-15 pt-lg-0 px-10" style="margin-top: 30px;">
                <!--begin::Aside-->
                <div class="d-flex flex-lg-start flex-column">
                    <div class="pt-30">
                        <!--begin::Logo-->
                        <a href="https://ecc-beta.doe.gov.bd/assets/"> <img alt="Logo"
                                src="DoE%20ECC%20System_files/logo_CHsI.png" width="60px"> </a>
                        <span class="text-dark fw-bold m-5" style="font-size:30px;"><a
                                href="https://ecc-beta.doe.gov.bd/assets/" style="color: black;">পরিবেশ অধিদপ্তর</a> </span>
                        <!--end::Logo-->
                    </div>
                    <p>&nbsp;</p>
                    <div class="instruction">
                        <h2>পরিবেশগত ছাড়পত্র এবং গবেষণাগার আবেদন নির্দেশনা: </h2>
                        <p><i class="fa-sharp fa-solid fa-circle-check fs-3"></i>
                            উদ্যোক্তাগণ অনলাইনে আবেদন করার সময় নিজস্ব
                            ই-মেইল, মোবাইল নম্বর/ফোন নম্বর ও জাতীয় পরিচয়পত্রের নম্বর ব্যবহার
                            করবেন। </p>
                        <p><i class="fa-sharp fa-solid fa-circle-check fs-3"></i>
                            উদ্যোক্তাগণ কোন অবস্থাতেই অন্যের ই-মেইল,
                            মোবাইল নম্বর/ফোন নম্বর ও জাতীয় পরিচয়পত্রের নম্বর ব্যবহার করবেন না।</p>
                        <p><i class="fa-sharp fa-solid fa-circle-check fs-3"></i>
                            ই-মেইল আইডি অথবা পাসওয়ার্ড ভুলে গেলে ফরগেট পাসওয়ার্ড অপশনটি ব্যবহার করবেন।</p>
                        <!-- <p><i class="fa-sharp fa-solid fa-circle-check fs-3"></i>
                            পরিবেশগত ছাড়পত্র ফি এবং ছাড়পত্র নবায়ন ফি এর অর্থনৈতিক কোড: ১৪২২৩১৯</p>
                        <p><i class="fa-sharp fa-solid fa-circle-check fs-3"></i>
                            গবেষণাগারের পরীক্ষণ ফি এর অর্থনৈতিক কোড : ১৪২২৩২৯</p> -->
                        <p><i class="fa-sharp fa-solid fa-circle-check fs-3"></i>
                            সকল প্রকার ফি অনলাইনে অথবা সিস্টেম হতে
                            স্বয়ংক্রিয়ভাবে তৈরিকৃত চালান কপির মাধ্যমে ব্যাংকে পরিশোধ করতে হবে।
                        </p>
                    </div>
                    <div class="instruction manual mt-10 d-none">

                        <a href="https://ecc-beta.doe.gov.bd/assets/storage/documents/doe_user_manual_for_entrepreneur.pdf"
                            target="_blank"><i class="fa-solid fa-file-pdf fs-3"></i><span
                                class="text-danger fw-semibold light-up"> User Manual </span></a> &nbsp;&nbsp;&nbsp;
                        <a href="https://www.youtube.com/watch?v=dIPDV7WVL8M" target="_blank"><i
                                class="fa-brands fa-youtube fs-3"></i> <span
                                class="text-danger fw-semibold light-up">Video Tutorial </span></a>
                    </div>
                    <div class="help-options mt-7">
                        <div class="button-group gap-5 text-center">
                            <a href="https://ecc-beta.doe.gov.bd/assets/how-to-apply" class="btn btn-sm btn-primary mb-3">How
                                to Apply </a>
                            <a href="https://ecc-beta.doe.gov.bd/assets/certificate-verification"
                                class="btn btn-sm btn-primary mb-3">Verify Certificate </a>
                            <a href="https://ecc-beta.doe.gov.bd/assets/search-project"
                                class="btn btn-sm btn-primary mb-3">Search Project</a>
                            <a href="https://ecc-beta.doe.gov.bd/assets/help" class="btn btn-sm btn-primary mb-3">Help</a>
                            <a href="https://ecc-beta.doe.gov.bd/assets/faq" class="btn btn-sm btn-primary mb-3">FAQ</a>
                            <a href="https://ecc-beta.doe.gov.bd/assets/" class="btn btn-sm btn-primary mb-3">Home Page</a>
                        </div>
                    </div>
                </div>
                <!--begin::Aside-->
            </div>
            <!--begin::Aside-->
            <!--begin::Body-->
            <div
                class="d-flex flex-column-fluid flex-lg-row-auto justify-content-center justify-content-lg-end p-12 p-lg-10">
                <!--begin::Card-->
                <div class="bg-body d-flex flex-column align-items-stretch flex-center rounded-4 w-md-1000px">
                    <!--begin::Wrapper-->
                    <div class="d-flex flex-center flex-column-fluid rounded-4"
                        style="height:600px; overflow-y:scroll;">
                        <!--begin::Form-->
                        <div class="container" style="max-width: 800px;">
                            <div class="page-header text-center">
                                <h3>Certificate Verification</h3>
                                <hr>
                            </div>
                            <div class="panel-group mt-3">
                                <form action="" method="get">
                                    <div class="input-group mb-3" style="margin: 0 auto;">
                                        <label for="file_no_input" class="visually-hidden">File Number</label>
                                        <span class="input-group-text"><b>File No</b></span>
                                        <input type="number" id="file_no_input" name="file_no" class="form-control" value="<?php echo htmlspecialchars($file_no_from_url); ?>" placeholder="Enter File Number">
                                        <label for="sarok_no_input" class="visually-hidden">স্মারক নম্বর</label>
                                        <span class="input-group-text"><b>অথবা স্মারক নম্বর</b></span>
                                        <input type="text" id="sarok_no_input" name="sarok_no" maxlength="40"
                                            minlength="5" class="form-control" placeholder="স্মারক নম্বর (বাংলায়)">
                                        <div class="input-group-append">
                                            &nbsp;&nbsp;<button class="btn btn-primary" type="submit">Verify</button>
                                        </div>
                                    </div>
                                </form>
                                <table class="table table-responsive align-middle table-bordered table-hover">
                                    <thead class="table-primary text-center sticky-top">
                                        <tr>
                                            <th>No</th>
                                            <th>Project/ Industry</th>
                                            <th>Address</th>
                                            <th>Category</th>
                                            <th>Certificate Type</th>
                                            <th>Issue Date</th>
                                            <th>Expiry Date</th>
                                            <th>Detail</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php if (!empty($results)): ?>
                                            <?php foreach ($results as $index => $row): ?>
                                                <tr>
                                                    <td><?php echo $index + 1; ?></td>
                                                    <td><?php echo htmlspecialchars($row['project_name']); ?></td>
                                                    <td><?php echo htmlspecialchars($row['address']); ?></td>
                                                    <td><?php echo htmlspecialchars($row['category']); ?></td>
                                                    <td><?php echo htmlspecialchars($row['certificate_type']); ?></td>
                                                    <td><?php echo htmlspecialchars($row['issue_date']); ?></td>
                                                    <td><?php echo htmlspecialchars($row['expiry_date']); ?></td>
                                                    <td class="text-center">
                                                        <a href="<?php echo htmlspecialchars($row['detail_link']); ?>" target="_blank">Detail</a>
                                                    </td>
                                                </tr>
                                            <?php endforeach; ?>
                                        <?php elseif (!empty($file_no_from_url)): ?>
                                            <tr>
                                                <td colspan="8" class="text-center text-danger">No certificate found for File No: <?php echo htmlspecialchars($file_no_from_url); ?></td>
                                            </tr>
                                        <?php endif; ?>
                                    </tbody>
                                </table>
                            </div>
                            <div class="text-gray-500 text-center fw-semibold fs-6 mt-10">
                                <a class="btn btn-success btn-sm px-4 me-2" data-bs-toggle="popover"
                                    data-bs-placement="top" data-bs-trigger="hover" data-bs-content="Edit"
                                    href="https://ecc-beta.doe.gov.bd/assets/login">
                                    <i class="fa-solid fa-backward"></i>Go Back
                                </a>
                                <div>&nbsp;</div>
                            </div>
                        </div>
                        <!--end::Form-->
                    </div>
                    <!--end::Wrapper-->
                </div>
                <!--end::Card-->
            </div>
            <!--end::Body-->
        </div>
        <!--end::Authentication - Sign-in-->
    </div>
    <script>
        var hostUrl = "/assets/";
    </script>
    <script src="DoE%20ECC%20System_files/plugins.bundle_CHsI.js"></script>
    <script src="DoE%20ECC%20System_files/scripts.bundle_CHsI.js"></script>
    <script src="DoE%20ECC%20System_files/general_CHsI.js"></script>


    <svg id="SvgjsSvg1001" width="2" height="0" xmlns="http://www.w3.org/2000/svg" version="1.1"
        xlink="http://www.w3.org/1999/xlink" svgjs="http://svgjs.dev"
        style="overflow: hidden; top: -100%; left: -100%; position: absolute; opacity: 0;">
        <defs id="SvgjsDefs1002"></defs>
        <polyline id="SvgjsPolyline1003" points="0,0"></polyline>
        <path id="SvgjsPath1004" d="M0 0 "></path>
    </svg>
</body>

</html>