<?php
session_start();
include "config.php";

// Redirect if user is not logged in
if (!isset($_SESSION['user'])) {
    echo '<script>window.location.href = "index.php";</script>';
    exit();
}
// Get user details from session
$user = $_SESSION['user'];
$pid = $user['pid'];

//echo "Welcome " . htmlspecialchars($user['name'] ?? 'User') . "<br>";
//echo "Email: " . htmlspecialchars($user['email'] ?? '') . "<br>";
//echo "Phone: " . htmlspecialchars($user['phone'] ?? '') . "<br>";
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="icon" href="images/favicon.ico" sizes="32x32" type="image/x-icon">

    <title>Report</title>

    <!-- Main CSS -->
    <link rel="stylesheet" type="text/css" href="css/main.css">

    <!-- Bootstrap Icons -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.10.5/font/bootstrap-icons.css">
</head>

<body class="app sidebar-mini">
    <!-- Navbar-->
    <?php include "header.php" ?>
    <!-- Sidebar menu-->
    <div class="app-sidebar__overlay" data-toggle="sidebar"></div>
    <aside class="app-sidebar">

        <?php include "./common/name.php" ?>

        <?php include 'sidebar.php' ?>

    </aside>

    <main class="app-content">
        <div class="app-title">
            <div>
                <h1><i class="bi bi-speedometer"></i> Report</h1>
            </div>
            <ul class="app-breadcrumb breadcrumb">
                <li class="breadcrumb-item"><i class="bi bi-house-door fs-6"></i></li>
                <li class="breadcrumb-item"><a href="report.php">Report</a></li>
            </ul>
        </div>

        <div class="info">
            <h4>BANK</h4>
        </div>
        <div class="row">
            <div class="col-md-6 col-lg-3">
                <div class="widget-small primary coloured-icon" style="cursor: pointer;" onclick="openPage('kyc')">
                    <i class="icon bi bi-file-earmark-person fs-1"></i>
                    <div class="info">
                        <h4>KYC</h4>
                        <p>
                            <?php
                            // Optimized single query
                            $sql = "
                                    SELECT 
                                        COUNT(*) AS total_kyc,
                                        SUM(CASE WHEN k.status = 'Pending' THEN 1 ELSE 0 END) AS total_pending_kyc,
                                        SUM(CASE WHEN k.status = 'KYC Done' THEN 1 ELSE 0 END) AS total_done_kyc
                                    FROM kyc k
                                    INNER JOIN cyber_police c 
                                        ON k.c_id = c.id
                                    WHERE k.type = 'KYC' 
                                    AND c.pid = '$pid'
                                ";

                            $result = mysqli_query($con, $sql);
                            $row = mysqli_fetch_assoc($result);
                            ?>

                            <b>Total : <?= $row['total_kyc'] ?></b><br>
                            <b>Pending : <?= $row['total_pending_kyc'] ?></b><br>
                            <b>KYC Done : <?= $row['total_done_kyc'] ?></b>
                        </p>
                    </div>
                </div>
            </div>

            <div class="col-md-6 col-lg-3">
                <div class="widget-small info coloured-icon" style="cursor: pointer;" onclick="openPage('lien')"><i
                        class="icon bi bi-currency-rupee fs-1"></i>
                    <div class="info">
                        <h4>LIAN AMOUNT</h4>

                        <p>
                            <?php
                            // Optimized single query
                            
                            $sql = "
                                    SELECT 
                                        COUNT(*) AS total_lien,
                                        SUM(CASE WHEN k.status = 'Pending' THEN 1 ELSE 0 END) AS total_pending_lien_amount,
                                        SUM(CASE WHEN k.status = 'KYC Done' THEN 1 ELSE 0 END) AS total_done_line_amount
                                    FROM kyc k
                                    INNER JOIN cyber_police c 
                                        ON k.c_id = c.id
                                    WHERE k.type = 'LIEN AMOUNT' 
                                    AND c.pid = '$pid'
                                ";


                            $result = mysqli_query($con, $sql);
                            $row = mysqli_fetch_assoc($result);
                            ?>

                            <b>Total : <?= $row['total_lien'] ?></b><br>
                            <b>Pending : <?= $row['total_pending_lien_amount'] ?></b><br>
                            <b>KYC Done : <?= $row['total_done_line_amount'] ?></b>
                        </p>
                    </div>
                </div>
            </div>

            <div class="col-md-6 col-lg-3">
                <div class="widget-small warning coloured-icon" style="cursor: pointer;" onclick="openPage('comply')"><i
                        class="icon bi bi-postcard fs-1"></i>
                    <div class="info">
                        <h4>COMPLY ORDER</h4>
                        <p>
                            <?php
                            // Optimized single query
                            
                            $sql = "
                                    SELECT 
                                        COUNT(*) AS total_comply,
                                        SUM(CASE WHEN k.status = 'Pending' THEN 1 ELSE 0 END) AS total_pending_comply,
                                        SUM(CASE WHEN k.status = 'KYC Done' THEN 1 ELSE 0 END) AS total_done_comply
                                    FROM comply_order k
                                    INNER JOIN cyber_police c 
                                        ON k.c_id = c.id
                                    WHERE c.pid = '$pid'
                                ";

                            $result = mysqli_query($con, $sql);
                            $row = mysqli_fetch_assoc($result);
                            ?>

                            <b>Total : <?= $row['total_comply'] ?></b><br>
                            <b>Pending : <?= $row['total_pending_comply'] ?></b><br>
                            <b>Comply Order Done : <?= $row['total_done_comply'] ?></b>
                        </p>
                    </div>
                </div>
            </div>
            <div class="col-md-6 col-lg-3">
                <div class="widget-small danger coloured-icon" style="cursor: pointer;" onclick="openPage('cctv')"><i
                        class="icon bi bi-camera-video fs-1"></i>
                    <div class="info">
                        <h4>CCTV FOOTAGE</h4>
                        <p>
                            <?php
                            // Optimized single query
                            
                            $sql = "
    SELECT 
        COUNT(*) AS total_,
        SUM(CASE WHEN k.status = 'Pending' THEN 1 ELSE 0 END) AS total_pending_,
        SUM(CASE WHEN k.status = 'KYC Done' THEN 1 ELSE 0 END) AS total_done_
    FROM comply_order k
    INNER JOIN cyber_police c 
        ON k.c_id = c.id
    WHERE c.pid = '$pid'
";

                            $result = mysqli_query($con, $sql);
                            $row = mysqli_fetch_assoc($result);
                            ?>

                            <b>Total : <?= $row['total_'] ?></b><br>
                            <b>Pending : <?= $row['total_pending_'] ?></b><br>
                            <b>Comply Order Done : <?= $row['total_done_'] ?></b>

                        </p>
                    </div>
                </div>
            </div>
        </div>

        <div class="info">
            <h4>SOCIAL MEDIA</h4>
        </div>

        <div class="row">
            <div class="col-md-6 col-lg-3">
                <div class="widget-small primary coloured-icon" style="cursor: pointer;" onclick="openPage('facebook')">
                    <i class="icon bi bi-facebook fs-1"></i>
                    <div class="info">
                        <h4>FACEBOOK</h4>
                        <p>
                            <b>
                                <?php
                                // Assuming $conn is your MySQLi connection
                                
                                // $sql = "
                                //     SELECT 
                                //         COUNT(*) AS total_,
                                //         SUM(CASE WHEN k.status = 'Pending' THEN 1 ELSE 0 END) AS total_pending_,
                                //         SUM(CASE WHEN k.status = 'KYC Done' THEN 1 ELSE 0 END) AS total_done_
                                //     FROM  k
                                //     INNER JOIN cyber_police c 
                                //         ON k.c_id = c.id
                                //     WHERE c.pid = '$pid'
                                // ";
                                
                                $sql = "SELECT COUNT(*) AS total_users FROM social_media s INNER JOIN cyber_police c     ON s.c_id = c.id WHERE s.type = 'facebook' AND c.pid = '$pid' ";
                                $result = mysqli_query($con, $sql);
                                $row = mysqli_fetch_assoc($result);
                                echo "Total : " . $row['total_users'];
                                ?>
                            </b>
                        </p>
                    </div>
                </div>
            </div>
            <div class="col-md-6 col-lg-3">
                <div class="widget-small info coloured-icon" style="cursor: pointer;" onclick="openPage('instagram')">
                    <i class="icon bi bi-instagram fs-1"></i>
                    <div class="info">
                        <h4>INSTAGRAM</h4>
                        <p><b>
                                <?php
                                // Assuming $conn is your MySQLi connection
                                $sql = "SELECT COUNT(*) AS total_users FROM social_media s INNER JOIN cyber_police c     ON s.c_id = c.id WHERE s.type = 'instagram' AND c.pid = '$pid' ";
                                $result = mysqli_query($con, $sql);
                                $row = mysqli_fetch_assoc($result);
                                echo "Total : " . $row['total_users'];
                                ?>
                            </b></p>
                    </div>
                </div>
            </div>
            <div class="col-md-6 col-lg-3">
                <div class="widget-small warning coloured-icon" style="cursor: pointer;" onclick="openPage('whatsapp')">
                    <i class="icon bi bi-whatsapp fs-1"></i>
                    <div class="info">
                        <h4>Whatsapp</h4>
                        <p><b>
                                <?php
                                // Assuming $conn is your MySQLi connection
                                $sql = "SELECT COUNT(*) AS total_users FROM social_media s INNER JOIN cyber_police c  ON s.c_id = c.id WHERE s.type = 'whatsapp' AND c.pid = '$pid' ";
                                $result = mysqli_query($con, $sql);
                                $row = mysqli_fetch_assoc($result);
                                echo "Total : " . $row['total_users'];
                                ?>
                            </b></p>
                    </div>
                </div>
            </div>
            <div class="col-md-6 col-lg-3">
                <div class="widget-small danger coloured-icon" style="cursor: pointer;" onclick="openPage('twitter')">
                    <i class="icon bi bi-twitter fs-1"></i>
                    <div class="info">
                        <h4>X(TWitter)</h4>
                        <p><b><?php
                        // Assuming $conn is your MySQLi connection
                        
                        $sql = "SELECT COUNT(*) AS total_users FROM social_media s INNER JOIN cyber_police c  ON s.c_id = c.id WHERE s.type = 'twitter' AND c.pid = '$pid' ";
                        $result = mysqli_query($con, $sql);
                        $row = mysqli_fetch_assoc($result);
                        echo "Total : " . $row['total_users'];
                        ?></b></p>
                    </div>
                </div>
            </div>

            <div class="col-md-6 col-lg-3">
                <div class="widget-small danger coloured-icon" style="cursor: pointer;" onclick="openPage('snapchat')">
                    <i class="icon bi bi-snapchat fs-1"></i>
                    <div class="info">
                        <h4>Snapchat</h4>
                        <p><b><?php
                        // Assuming $conn is your MySQLi connection
                        $sql = "SELECT COUNT(*) AS total_users FROM social_media s INNER JOIN cyber_police c  ON s.c_id = c.id WHERE s.type = 'snapchat' AND c.pid = '$pid'; ";
                        $result = mysqli_query($con, $sql);
                        $row = mysqli_fetch_assoc($result);
                        echo "Total : " . $row['total_users'];
                        ?></b></p>
                    </div>
                </div>
            </div>

            <div class="col-md-6 col-lg-3">
                <div class="widget-small danger coloured-icon" style="cursor: pointer;" onclick="openPage('google')">
                    <i class="icon bi bi-google fs-1"></i>
                    <div class="info">
                        <h4>LERS(Google)</h4>
                        <p><b><?php
                        // Assuming $conn is your MySQLi connection
                        $sql = "SELECT COUNT(*) AS total_users FROM social_media s INNER JOIN cyber_police c  ON s.c_id = c.id WHERE s.type = 'google' AND c.pid = '$pid'; ";
                        $result = mysqli_query($con, $sql);
                        $row = mysqli_fetch_assoc($result);
                        echo "Total : " . $row['total_users'];
                        ?></b></p>
                    </div>
                </div>
            </div>


        </div>

        <div class="info">
            <h4>OTHER</h4>
        </div>

        <div class="row">
            <div class="col-md-6 col-lg-3">
                <div class="widget-small primary coloured-icon" style="cursor: pointer;" onclick="openPage('domain')">
                    <i class="icon bi bi-database fs-1"></i>
                    <div class="info">
                        <h4>DOMAIN</h4>
                        <p>
                            <b>
                                <?php
                                // Assuming $conn is your MySQLi connection
                                $sql = "SELECT COUNT(*) AS total_users FROM domain_data s INNER JOIN cyber_police c  ON s.c_id = c.id  WHERE c.pid = '$pid' ";

                                $result = mysqli_query($con, $sql);
                                $row = mysqli_fetch_assoc($result);
                                echo "Total : " . $row['total_users'];
                                ?>
                            </b>
                        </p>
                    </div>
                </div>
            </div>
            <div class="col-md-6 col-lg-3">
                <div class="widget-small info coloured-icon" style="cursor: pointer;" onclick="openPage('cdr')"><i
                        class="icon bi bi-info-square fs-1"></i>
                    <div class="info">
                        <h4>CDR,SDR & CAF</h4>
                        <p><b>
                                <?php
                                // Assuming $conn is your MySQLi connection
                                $sql = "SELECT COUNT(*) AS total_users 
        FROM cdr s 
        INNER JOIN cyber_police c ON s.c_id = c.id 
        WHERE c.pid = '$pid'";
                                $result = mysqli_query($con, $sql);
                                $row = mysqli_fetch_assoc($result);
                                echo "Total : " . $row['total_users'];
                                ?>
                            </b></p>
                    </div>
                </div>
            </div>

            <a href="download.php" class="btn btn-success">⬇️ Download Database</a>


        </div>


        <!-- Info Modal -->
        <div class="modal fade" id="infoModal" tabindex="-1" aria-labelledby="modalTitle" aria-hidden="true">
            <div class="modal-dialog modal-lg modal-dialog-centered">
                <div class="modal-content">
                    <div class="modal-header bg-primary text-white">
                        <h5 class="modal-title" id="modalTitle">Loading...</h5>
                        <button type="button" class="btn-close btn-close-white" data-bs-dismiss="modal"
                            aria-label="Close"></button>
                    </div>
                    <div class="modal-body" id="modalBody">
                        <form id="reportForm">
                            <div class="row mb-3">
                                <div class="col-md-6">
                                    <label for="startDate" class="form-label fw-bold">Start Date</label>
                                    <input type="date" class="form-control" id="startDate" name="startDate" required>
                                </div>
                                <div class="col-md-6">
                                    <label for="endDate" class="form-label fw-bold">End Date</label>
                                    <input type="date" class="form-control" id="endDate" name="endDate" required>
                                </div>
                            </div>
                            <div class="text-center">
                                <button type="button" class="btn btn-danger me-2" id="pdfBtn">
                                    <i class="bi bi-file-earmark-pdf"></i> Download PDF
                                </button>
                                <button type="button" class="btn btn-success" id="xlsBtn">
                                    <i class="bi bi-file-earmark-excel"></i> Download XLS
                                </button>
                            </div>
                        </form>
                    </div>
                </div>
            </div>
        </div>


        <!-- <div class="row">
            <div class="col-md-6">
                <div class="tile">
                    <h3 class="tile-title">Weekly Sales - Last week</h3>
                    <div class="ratio ratio-16x9">
                        <div id="salesChart"></div>
                    </div>
                </div>
            </div>
            <div class="col-md-6">
                <div class="tile">
                    <h3 class="tile-title">Support Requests</h3>
                    <div class="ratio ratio-16x9">
                        <div id="supportRequestChart"></div>
                    </div>
                </div>
            </div>
        </div> -->
    </main>
    <!-- Essential javascripts for application to work-->
    <script src="js/jquery-3.7.0.min.js"></script>
    <script src="js/bootstrap.min.js"></script>
    <script src="js/main.js"></script>
    <!-- Page specific javascripts-->
    <script type="text/javascript" src="https://cdn.jsdelivr.net/npm/echarts@5.4.3/dist/echarts.min.js"></script>

    <script>
        let currentType = ''; // store which widget was clicked

        function openPage(type) {
            currentType = type.toUpperCase(); // store type (kyc, lien, etc.)

            // Set modal title dynamically
            $('#modalTitle').text(`${currentType} REPORT`);

            // Reset dates
            $('#startDate').val('');
            $('#endDate').val('');

            // Show modal
            $('#infoModal').modal('show');
        }

        // Function to open PHP via POST (hidden form)
        function openReportInNewTab(url, params) {
            const form = document.createElement('form');
            form.method = 'POST';
            form.action = url;
            form.target = '_blank'; // open in new tab

            for (const key in params) {
                if (params.hasOwnProperty(key)) {
                    const input = document.createElement('input');
                    input.type = 'hidden';
                    input.name = key;
                    input.value = params[key];
                    form.appendChild(input);
                }
            }

            document.body.appendChild(form);
            form.submit();
            document.body.removeChild(form);
        }

        // When PDF button is clicked
        $('#pdfBtn').click(function () {
            const startDate = $('#startDate').val();
            const endDate = $('#endDate').val();

            if (!startDate || !endDate) {
                alert('Please select both start and end dates.');
                return;
            }

            const params = {
                type: currentType,
                start: startDate,
                end: endDate,
                pid: "<?= $pid ?>" // securely pass current police ID from session
            };

            if (currentType === 'KYC') {
                openReportInNewTab('reports/kyc_pdf_main.php', params);
            } else if (currentType === 'LIEN') {
                openReportInNewTab('reports/lien_pdf_main.php', params);
            } else if (currentType === 'COMPLY') {
                openReportInNewTab('reports/comply_order_pdf_main.php', params);
            } else if (['FACEBOOK', 'INSTAGRAM', 'WHATSAPP', 'TWITTER', 'SNAPCHAT', 'GOOGLE'].includes(
                currentType)) {
                const platform = currentType.toLowerCase();
                openReportInNewTab(`reports/social_media_pdf_main.php?platform=${platform}`, params);
            } else if (currentType === 'DOMAIN') {
                openReportInNewTab('reports/domain_pdf_main.php', params);
            } else if (currentType === 'CDR') {
                openReportInNewTab('reports/cdr_pdf_main.php', params);
            } else {
                openReportInNewTab('generate_report.php', {
                    ...params,
                    format: 'pdf'
                });
            }
        });

        // When XLS button is clicked
        $('#xlsBtn').click(function () {
            const startDate = $('#startDate').val();
            const endDate = $('#endDate').val();

            if (!startDate || !endDate) {
                alert('Please select both start and end dates.');
                return;
            }

            const params = {
                type: currentType,
                start: startDate,
                end: endDate,
                pid: "<?= $pid ?>",
                format: 'xls'
            };

            if (currentType === 'KYC') {
                openReportInNewTab('reports/kyc_xls_main.php', params);
            } else if (currentType === 'LIEN') {
                openReportInNewTab('reports/lien_xls_main.php', params);
            } else if (currentType === 'COMPLY') {
                openReportInNewTab('reports/comply_order_xls_main.php', params);
            } else if (currentType === 'CCTV') {
                openReportInNewTab('reports/cctv_xls_main.php', params);
            } else if (['FACEBOOK', 'INSTAGRAM', 'WHATSAPP', 'TWITTER', 'SNAPCHAT', 'GOOGLE'].includes(
                currentType)) {
                const platform = currentType.toLowerCase();
                openReportInNewTab(`reports/social_media_xls_main.php?platform=${platform}`, params);
            } else if (currentType === 'DOMAIN') {
                openReportInNewTab('reports/domain_xls_main.php', params);
            } else if (currentType === 'CDR') {
                openReportInNewTab('reports/cdr_xls_main.php', params);
            } else {
                openReportInNewTab('generate_report.php', {
                    ...params,
                    format: 'xls'
                });
            }

            openReportInNewTab('generate_report.php', params);
        });
    </script>




</body>

</html>