<?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>Dashboard</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> Dashboard</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="main.php">Dashboard</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</h4>
                        <p>
                            <?php
                            // Optimized single query
                            
                            $sql = "
                                    SELECT 
                                        COUNT(*) AS total_cctv,
                                        SUM(CASE WHEN k.status = 'Pending' THEN 1 ELSE 0 END) AS total_pending_cctv,
                                        SUM(CASE WHEN k.status = 'KYC Done' THEN 1 ELSE 0 END) AS total_done_cctv
                                    FROM cctv 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_cctv'] ?></b><br>
                            <b>Pending : <?= $row['total_pending_cctv'] ?></b><br>
                            <b>Comply Order Done : <?= $row['total_done_cctv'] ?></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="openSocial('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_cctv,
                                //         SUM(CASE WHEN k.status = 'Pending' THEN 1 ELSE 0 END) AS total_pending_cctv,
                                //         SUM(CASE WHEN k.status = 'KYC Done' THEN 1 ELSE 0 END) AS total_done_cctv
                                //     FROM cctv 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="openSocial('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="openSocial('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="openSocial('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="openSocial('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="openSocial('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>


        </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>
        function openPage(type) {

            if (type === 'kyc') {
                window.location.href = 'kyc_form.php'; // Change to your actual KYC page
            } else if (type === 'lien') {
                window.location.href = 'lien_amount.php'; // Change to your actual Lien page
            } else if (type === 'comply') {
                window.location.href = 'comply_order.php'; // Change to your actual Lien page
            } else if (type === 'cctv') {
                window.location.href = 'cctv.php'; // Change to your actual Lien page
            } else if (type === 'domain') {
                window.location.href = 'domain.php'; // Change to your actual Lien page
            } else if (type === 'cdr') {
                window.location.href = 'cdr.php'; // Change to your actual Lien page
            }
        }

        function openSocial(type) {
            window.location.href = 'socialmedia.php?platform=' + encodeURIComponent(
                type); // Redirect with platform as a query parameter
        }
    </script>

</body>

</html>