<!DOCTYPE html>
<html>

<head>
    <title>Backup DataBase</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
</head>
<style type="text/css">
    body {
        background-color: whitesmoke;
    }

    .card {
        margin-top: 30px;
    }
</style>

<body>
    <div class="container">
        <div class="card card-body">
            <div class="row">
                <div class="col-lg-2">
                    <h4>
                        <center>
                            <a class="btn btn-success btn-lg" href="SekolahView/1"><i class="fas fa-arrow-left"></i> Kembali</a>
                        </center>
                    </h4>
                </div>
                <div class="col">

                    <?php
                    if (isset($_GET['n'])) {
                        $n = explode("|", $_GET['n']);
                        $npsn = $n['0'];
                        $nama_sekolah = $n['1'];
                        $tahun_pelajaran = $n['2'];
                    }
                    ?>
                    <?php if (isset($_GET['c'])) : ?>
                        <?php if ($_GET['c'] == 'restore') : ?>
                            <div class="card card-body">
                                <h4>
                                    <center>Restore DataBase <?= $nama_sekolah ?> Tahun Pelajaran <?= $tahun_pelajaran ?></center>
                                </h4>
                                <hr>
                                <form method="post" enctype="multipart/form-data" class="form-inline">
                                    <div class="form-group">
                                        <label for="exampleFormControlFile1">Pilih Database</label>
                                        <input type="file" class="form-control" name="file_image" style="width:75%">
                                    </div>
                                    <hr>
                                    <div class="form-group">
                                        <button class="btn btn-primary mb-3" type="submit" name="upload">Restore DataBase</button>
                                    </div>
                                </form>

                                <?php
                                error_reporting(0);
                                if (isset($_POST["upload"])) {
                                    if ($_FILES["file_image"]["name"] != '') {
                                        $filename = '../db/rapor.db';

                                        if (unlink($filename)) {
                                            //echo 'The file ' . $filename . ' was deleted successfully!';
                                            echo 'Restore Database Berhasil, <a href="SekolahView/1"> Kembali Ke Aplikasi</a>';
                                        } else {
                                            echo 'There was a error deleting the file ' . $filename;
                                        }

                                        $allowed_ext = array("db", "rdigital", "ambatura");
                                        $ext = end(explode('.', $_FILES["file_image"]["name"]));
                                        if (in_array($ext, $allowed_ext)) {

                                            $name = '../db/rapor.db';
                                            $path = $name;
                                            move_uploaded_file($_FILES["file_image"]["tmp_name"], $path);
                                        } else {
                                            echo '<script>alert("Tidak Sesuai File")</script>';
                                        }
                                        echo '<script>alert("Restore DataBase Berhasil")</script>';
                                    } else {
                                        echo '<script>alert("Silahkan pilih file ")</script>';
                                    }

                                    // $db = new PDO('sqlite:../db/rapor.db');
                                    // $newFields = range(16, 30);

                                    // try {
                                    //     foreach ($newFields as $fieldNumber) {
                                    //         $fieldName = 'f' . $fieldNumber;
                                    //         $sql = "ALTER TABLE nilai_formatif ADD COLUMN $fieldName TEXT (255)";
                                    //         $db->exec($sql);
                                    //         echo "Added column $fieldName successfully.\n";
                                    //     }
                                    //     echo "All columns added successfully.";
                                    //     header("location:SekolahView/1");
                                    // } catch (PDOException $e) {
                                    //     echo "Error: " . $e->getMessage();
                                    // }

                                    // try {
                                    //     // SQL statement to add 'materi_id' column to the 'tp' table
                                    //     $sql = "ALTER TABLE tujuan_pembelajaran ADD COLUMN materi_id INTEGER";
                                    //     $db->exec($sql);
                                    //     echo "Column 'materi_id' added successfully.";
                                    // } catch (PDOException $e) {
                                    //     echo "Error: " . $e->getMessage();
                                    // }
                                }
                                ?>
                            <?php endif ?>

                            <?php if ($_GET['c'] == 'backup') : ?>
                                <h4>
                                    <center><small>Backup DataBase</small><br><?= $nama_sekolah ?><br><small><small>Tahun Pelajaran <?= $tahun_pelajaran ?></small></small></center>
                                </h4>
                                <table class="table table-bordered table-sm">
                                    <tr>
                                        <th>Siswa</th>
                                        <th>Guru</th>
                                        <th>WaliKelas</th>
                                        <th>Ektrakurikuler</th>
                                        <th>Ektra Absensi</th>
                                        <th>Penilaian</th>
                                        <th>Nilai Formatif</th>
                                        <th>Nilai Sumatif</th>
                                        <th>Nilai P5</th>
                                    </tr>
                                    <tr>
                                        <?php
                                        $database_file = '../db/rapor.db';
                                        $db = new PDO("sqlite:$database_file");
                                        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                                        $kode_tapel = "SELECT(SELECT kode_tapel FROM tahun_pelajaran WHERE status='Aktif' AND tahun_pelajaran='$tahun_pelajaran') AS kode_tapel";
                                        $result = $db->query($kode_tapel)->fetch(PDO::FETCH_ASSOC);
                                        $kode_tapel = $result['kode_tapel'];
                                        // Query to count rows in peserta_didik and guru tables
                                        $query_peserta_didik =
                                            "SELECT 
                                        (SELECT count(*) FROM peserta_didik) AS siswa_count, 
                                        (SELECT count(*) FROM guru) AS guru_count, 
                                        (SELECT count(*) FROM wali_kelas) AS wali_kelas_count,
                                        (SELECT count(*) FROM ektrakurikuler) AS ektrakurikuler_count,
                                        (SELECT count(*) FROM extra_absensi) AS extra_absensi_count,
                                        (SELECT count(*) FROM penilaian) AS penilaian_count,
                                        (SELECT count(*) FROM nilai_formatif) AS nilai_formatif_count,
                                        (SELECT count(*) FROM nilai_sumatif) AS nilai_sumatif_count,
                                        (SELECT count(*) FROM nilai_p5) AS nilai_p5_count
                                        ";
                                        $result = $db->query($query_peserta_didik)->fetch(PDO::FETCH_ASSOC);

                                        // Display the counts directly in the table cells
                                        echo "<td>" . htmlspecialchars(number_format($result['siswa_count'])) . "</td>";
                                        echo "<td>" . htmlspecialchars(number_format($result['guru_count'])) . "</td>";
                                        echo "<td>" . htmlspecialchars(number_format($result['wali_kelas_count'])) . "</td>";
                                        echo "<td>" . htmlspecialchars(number_format($result['ektrakurikuler_count'])) . "</td>";
                                        echo "<td>" . htmlspecialchars(number_format($result['extra_absensi_count'])) . "</td>";
                                        echo "<td>" . htmlspecialchars(number_format($result['penilaian_count'])) . "</td>";
                                        echo "<td>" . htmlspecialchars(number_format($result['nilai_formatif_count'])) . "</td>";
                                        echo "<td>" . htmlspecialchars(number_format($result['nilai_sumatif_count'])) . "</td>";
                                        echo "<td>" . htmlspecialchars(number_format($result['nilai_p5_count'])) . "</td>";
                                        ?>
                                    </tr>
                                </table>
                                <table class="table table-bordered table-sm">
                                    <tr>
                                        <th>Proyek</th>
                                        <th>Tema</th>
                                        <th>Dimensi</th>
                                        <th>Materi</th>
                                        <th>Tujuan Pembelajaran</th>
                                        <th>KKTP Sumatif</th>
                                        <th>Tahun Pelajaran</th>
                                        <th>Mata Pelajaran</th>
                                        <th>Sekolah</th>
                                    </tr>
                                    <tr>
                                        <?php
                                        $database_file = '../db/rapor.db';
                                        $db = new PDO("sqlite:$database_file");
                                        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                                        $kode_tapel = "SELECT(SELECT kode_tapel FROM tahun_pelajaran WHERE status='Aktif' AND tahun_pelajaran='$tahun_pelajaran') AS kode_tapel";
                                        $result = $db->query($kode_tapel)->fetch(PDO::FETCH_ASSOC);
                                        $kode_tapel = $result['kode_tapel'];
                                        // Query to count rows in peserta_didik and guru tables
                                        $query_peserta_didik =
                                            "SELECT 
                                        (SELECT count(*) FROM proyek) AS proyek, 
                                        (SELECT count(*) FROM tema) AS tema, 
                                        (SELECT count(*) FROM dimensi) AS dimensi,
                                        (SELECT count(*) FROM materi) AS materi,
                                        (SELECT count(*) FROM tujuan_pembelajaran) AS tujuan_pembelajaran,
                                        (SELECT count(*) FROM kktp_sumatif) AS kktp_sumatif,
                                        (SELECT count(*) FROM tahun_pelajaran) AS tahun_pelajaran,
                                        (SELECT count(*) FROM mata_pelajaran) AS mata_pelajaran,
                                        (SELECT count(*) FROM sekolah) AS sekolah
                                        ";
                                        $result = $db->query($query_peserta_didik)->fetch(PDO::FETCH_ASSOC);

                                        // Display the counts directly in the table cells
                                        echo "<td>" . htmlspecialchars(number_format($result['proyek'])) . "</td>";
                                        echo "<td>" . htmlspecialchars(number_format($result['tema'])) . "</td>";
                                        echo "<td>" . htmlspecialchars(number_format($result['dimensi'])) . "</td>";
                                        echo "<td>" . htmlspecialchars(number_format($result['materi'])) . "</td>";
                                        echo "<td>" . htmlspecialchars(number_format($result['tujuan_pembelajaran'])) . "</td>";
                                        echo "<td>" . htmlspecialchars(number_format($result['kktp_sumatif'])) . "</td>";
                                        echo "<td>" . htmlspecialchars(number_format($result['tahun_pelajaran'])) . "</td>";
                                        echo "<td>" . htmlspecialchars(number_format($result['mata_pelajaran'])) . "</td>";
                                        echo "<td>" . htmlspecialchars(number_format($result['sekolah'])) . "</td>";
                                        ?>
                                    </tr>
                                </table>
                                <?php
                                $tgl = date("d-m-Y");
                                $thn = str_replace("/", "_", $tahun_pelajaran);
                                $namadb = $npsn . "-" . $thn . "-" . $tgl;
                                $dbnama = "files/" . $namadb . ".rdigital";
                                copy("../db/rapor.db", $dbnama);
                                ?>
                                <meta http-equiv="refresh" content="0;url=<?= $dbnama ?>" />
                            <?php endif ?>


                            <?php if ($_GET['c'] == 'kktp') : ?>
                                <h4>
                                    <center><small>Generate KKTP Sumatif</small><br><?= $nama_sekolah ?><br><small><small>Tahun Pelajaran <?= $tahun_pelajaran ?></small></small></center>
                                </h4>
                                <hr>
                                <?php
                                $database_file = '../db/rapor.db';
                                $db = new PDO("sqlite:$database_file");
                                if (!$db) {
                                    die("Koneksi ke database gagal");
                                }

                                $query = "CREATE TABLE IF NOT EXISTS kktp_sumatif (
                        desk_id INTEGER PRIMARY KEY AUTOINCREMENT,
                        tahun_pelajaran TEXT NOT NULL,
                        abjad TEXT NOT NULL,
                        min TEXT NOT NULL,
                        max TEXT NOT NULL,
                        ket TEXT NOT NULL
                    )";

                                $result = $db->exec($query);



                                echo "Tabel berhasil dibuat.";

                                $data = [
                                    [$tahun_pelajaran, 'A', '91', '100', 'Sangat baik dalam hal'],
                                    [$tahun_pelajaran, 'B', '71', '90', 'Baik dalam hal'],
                                    [$tahun_pelajaran, 'C', '51', '70', 'Perlu penguatan dalam hal'],
                                    [$tahun_pelajaran, 'D', '0', '50', 'Perlu pendampingan dalam hal']
                                ];

                                foreach ($data as $row) {
                                    $insertQuery = "INSERT INTO kktp_sumatif (`tahun_pelajaran`, `abjad`, `min`, `max`, `ket`) VALUES ('" . $row[0] . "', '" . $row[1] . "', '" . $row[2] . "', '" . $row[3] . "', '" . $row[4] . "')";

                                    $insertResult = $db->exec($insertQuery);

                                    if (!$insertResult) {
                                        die("Penyisipan data gagal: " . $db->lastErrorMsg());
                                    }
                                }

                                echo "Generate Berhasil.";

                                $db = null;
                                ?>

                                <meta http-equiv="refresh" content="0;url=SekolahView/1" />
                            <?php endif ?>

                        <?php endif ?>
                            </div>
                </div>
            </div>

        </div>
    </div>
</body>

</html>