<?php
session_start();
include 'includes/dbconn.php'; // Ensure this is the path to your DB connection script

$user_id = $_SESSION['user_id']; // Ensure that user_id is stored in session upon login
$company_id = $_SESSION['company_id'];

// Fetch user, company, and subscription details
$stmt = $conn->prepare("SELECT u.full_name, c.name AS company_name, s.end_date 
                        FROM users u 
                        JOIN companies c ON u.company_id = c.company_id 
                        LEFT JOIN subscriptions s ON u.user_id = s.user_id 
                        WHERE u.company_id = ? 
                        AND u.user_id = ?");
$stmt->bind_param("ii", $company_id, $user_id);
$stmt->execute();
$result = $stmt->get_result();
$data = $result->fetch_assoc();

// Calculate the number of days left for the subscription
$subscriptionDaysLeft = "N/A";
if (!empty($data['end_date'])) {
    $endDate = new DateTime($data['end_date']);
    $today = new DateTime();
    $subscriptionDaysLeft = $endDate->diff($today)->format("%a Days");
    if ($endDate < $today) {
        $subscriptionDaysLeft = "Expired";
    }
}

// Close the statement
$stmt->close();

// Fetch user and company details based on company_id from session
$stmt = $conn->prepare("SELECT u.full_name, c.name AS company_name FROM users u JOIN companies c ON u.company_id = c.company_id WHERE u.company_id = ?");
$stmt->bind_param("i", $company_id);
$stmt->execute();
$result = $stmt->get_result();
$data = $result->fetch_assoc();

// Close the statement
$stmt->close();

// Fetch client count based on company_id
$stmt = $conn->prepare("SELECT COUNT(*) as client_count FROM clients WHERE company_id = ?");
$stmt->bind_param("i", $company_id);
$stmt->execute();
$result = $stmt->get_result();
$clientCount = ($row = $result->fetch_assoc()) ? $row['client_count'] : 0;
$stmt->close();

// Fetch total expenses based on company_id
$stmt = $conn->prepare("SELECT SUM(amount) as total_expenses FROM expenses WHERE company_id = ?");
$stmt->bind_param("i", $company_id);
$stmt->execute();
$result = $stmt->get_result();
$totalExpenses = ($row = $result->fetch_assoc()) ? $row['total_expenses'] : 0;
$stmt->close();

// Fetch total invoices based on company_id
$stmt = $conn->prepare("SELECT COUNT(*) as total_invoices FROM invoices WHERE company_id = ?");
$stmt->bind_param("i", $company_id);
$stmt->execute();
$result = $stmt->get_result();
$totalInvoices = ($row = $result->fetch_assoc()) ? $row['total_invoices'] : 0;
$stmt->close();
?>

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

<head>

    <?php $title = "Profile";
    include 'partials/title-meta.php'; ?>

    <?php include 'partials/head-css.php'; ?>

</head>

<?php include 'partials/body.php'; ?>

<!-- Begin page -->
<div id="wrapper">

    <?php $pagetitle = "Profile";
    include 'partials/menu.php'; ?>
            
            <!-- ============================================================== -->
            <!-- Start Page Content here -->
            <!-- ============================================================== -->

            <div class="content-page">
                <div class="content">

                    <!-- Start Content-->
                    <div class="container-fluid">

                        <div class="row">
                            <div class="col-sm-8">
                                <div class="card">
                                <div class="bg-picture card-body">
                                    <div class="d-flex align-items-top">
                                        <img src="assets/images/users/user-1.jpg"
                                                class="flex-shrink-0 rounded-circle avatar-xl img-thumbnail float-start me-3" alt="profile-image">

                                        <div class="flex-grow-1 overflow-hidden">
                                            <h4 class="m-0"><?php echo htmlspecialchars($data['full_name']); ?></h4>
                                            <p class="text-muted"><i><?php echo htmlspecialchars($data['company_name']); ?></i></p>
                                            <p class="font-13">Hi I'm <?php echo htmlspecialchars($data['full_name']); ?>,  I run the company <?php echo htmlspecialchars($data['company_name']); ?> .</p>
                                            <p class="font-13">Subscription Status: <strong><?php echo $subscriptionDaysLeft; ?></strong> Days.</p>

                                            <ul class="social-list list-inline mt-3 mb-0">
                                                <li class="list-inline-item">
                                                    <a href="javascript: void(0);" class="social-list-item border-purple text-purple"><i
                                                            class="mdi mdi-facebook"></i></a>
                                                </li>
                                                <li class="list-inline-item">
                                                    <a href="javascript: void(0);" class="social-list-item border-danger text-danger"><i
                                                            class="mdi mdi-google"></i></a>
                                                </li>
                                                <li class="list-inline-item">
                                                    <a href="javascript: void(0);" class="social-list-item border-info text-info"><i
                                                            class="mdi mdi-twitter"></i></a>
                                                </li>
                                                <li class="list-inline-item">
                                                    <a href="javascript: void(0);" class="social-list-item border-secondary text-secondary"><i
                                                            class="mdi mdi-github"></i></a>
                                                </li>
                                            </ul>
        
                                        </div>

                                        <div class="clearfix"></div>
                                    </div>
                                </div>
                            </div>
                                <!--/ meta -->

                            <div class="row">
                                <!-- Display Total Expenses -->
                                <div class="col-md-4">
                                    <a href="exp_report.php" style="text-decoration: none; color: inherit;">
                                        <div class="card border border-secondary" data-section="expenses">
                                            <div class="card-body text-secondary">
                                                <h4 class="header-title mt-0 mb-3">Total Expenses</h4>
                                                <div class="widget-chart-1">
                                                    <div class="widget-detail-1 text-end">
                                                        <h2 class="fw-normal pt-2 mb-1"><?php echo $totalExpenses; ?></h2>
                                                        <p class="text-muted mb-1"><?php echo ($totalExpenses == 1) ? 'Expense' : 'Expenses'; ?></p>
                                                    </div>
                                                </div>
                                            </div>
                                        </div>
                                    </a>
                                </div><!-- end col -->

                                
                                <!-- Display Total Invoices -->
                                <div class="col-md-4">
                                    <a href="list_invoices.php" style="text-decoration: none; color: inherit;">
                                    <div class="card border border-secondary" data-section="invoices">
                                        <div class="card-body text-secondary">
                                            <h4 class="header-title mt-0 mb-3">Total Invoices</h4>
                                            <div class="widget-chart-1">
                                                <div class="widget-detail-1 text-end">
                                                    <h2 class="fw-normal pt-2 mb-1"><?php echo $totalInvoices; ?></h2>
                                                    <p class="text-muted mb-1"><?php echo ($totalInvoices == 1) ? 'Invoice' : 'Invoices'; ?></p>
                                                </div>
                                            </div>
                                        </div>
                                    </div>
                                    </a>
                                </div><!-- end col -->
                                
                                <div class="col-md-4">
                                    <div class="card border border-secondary" data-section="expenses">
                                        <div class="card-body text-secondary">
                                            <h4 class="header-title mt-0 mb-3">Total Clients</h4>
                                            <div class="widget-chart-1">
                                                <div class="widget-detail-1 text-end">
                                                    <h2 class="fw-normal pt-2 mb-1"><?php echo $clientCount; ?></h2>
                                                    <p class="text-muted mb-1"><?php echo ($clientCount == 1) ? 'Client' : 'Clients'; ?></p>
                                                </div>
                                            </div>
                                        </div>
                                    </div>
                                </div><!-- end col -->
                            </div>
                                
                            </div>
                            <?php
                            // Your PHP code from display_coa.php
                            include('includes/dbconn.php'); // Database connection script
                            
                            $companyId = $_SESSION['company_id']; // From session
                            
                            $sql = "SELECT a.account_id, a.account_name, IFNULL(SUM(CASE WHEN t.type = 'Debit' THEN t.amount ELSE -t.amount END), 0) as balance 
                                    FROM chart_of_accounts a 
                                    LEFT JOIN transactions t ON a.account_id = t.account_id AND a.company_id = t.company_id 
                                    WHERE a.company_id = ? 
                                    GROUP BY a.account_id, a.account_name";
                            $stmt = $conn->prepare($sql);
                            $stmt->bind_param("i", $companyId);
                            $stmt->execute();
                            $result = $stmt->get_result();
                            ?>
                            <div class="col-sm-4">
                                <div class="card">
                                <div class="card-body">
                                    <h4 class="header-title mt-0 mb-3">My Chart of Accounts</h4>

                                    <ul class="list-group mb-0 user-list" style="max-height: 300px; overflow-y: auto;">
                                        <?php
                                        while ($row = $result->fetch_assoc()) {
                                            echo '<li class="list-group-item">';
                                            echo '<a href="#" class="user-list-item">';
                                            echo '<div class="user avatar-sm float-start me-2">';
                                            echo '<img src="assets/images/faida-logo.png" alt="" class="img-fluid rounded-circle">';
                                            echo '</div>';
                                            echo '<div class="user-desc">';
                                            echo '<h5 class="name mt-0 mb-1">' . $row['account_name'] . '</h5>';
                                            echo '<p class="desc text-muted mb-0 font-12">Balance: $' . number_format($row['balance'], 2) . '</p>';
                                            echo '</div>';
                                            echo '</a>';
                                            echo '</li>';
                                        }
                                        ?>
                                    </ul>
                                </div>
                                </div>
                                

                            </div>
                        </div>    
                    </div> <!-- container -->

                </div> <!-- content -->

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

            </div>

            <!-- ============================================================== -->
            <!-- End Page content -->
            <!-- ============================================================== -->


        </div>
        <!-- END wrapper -->

<?php include 'partials/right-sidebar.php'; ?>

<?php include 'partials/footer-scripts.php'; ?>

<!-- App js -->
<script src="assets/js/app.min.js"></script>

</body>

</html>