<style>

    
</style>
<?php


include_once("includes/config.php");
require_once 'endpoint/helpers.php';


// get voucher list
function getVouchers() {

    session_start();

    if (!isset($_SESSION['company_id'])) {
        	echo "
        <script>
            alert('Company context missing. Please re-login.');
            window.location.href = 'sign-in.php';
        </script>
        ";
        exit;
    }

    $company_id = (int) $_SESSION['company_id'];

    // Connect to the database
    $mysqli = new mysqli(DATABASE_HOST, DATABASE_USER, DATABASE_PASS, DATABASE_NAME);

    if ($mysqli->connect_error) {
        die('Error : ('.$mysqli->connect_errno .') '. $mysqli->connect_error);
    }

    $date_pdf_exp = "";

    /* =========================
       BUILD QUERY
    ========================== */
    if (isset($_POST['search'])) {

        $date1 = date("Y-m-d", strtotime($_POST['date1']));
        $date2 = date("Y-m-d", strtotime($_POST['date2']));

        $query = "
            SELECT *
            FROM vouchers
            WHERE company_id = ?
              AND DATE(voucher_date) BETWEEN ? AND ?
            ORDER BY voucher_date DESC
        ";

        $stmt = $mysqli->prepare($query);
        $stmt->bind_param("iss", $company_id, $date1, $date2);
        $stmt->execute();
        $results = $stmt->get_result();

        $date_pdf_exp = "?init_date=$date1&final_date=$date2";

    } else {

        $query = "
            SELECT *
            FROM vouchers
            WHERE company_id = ?
            ORDER BY voucher_date DESC
        ";

        $stmt = $mysqli->prepare($query);
        $stmt->bind_param("i", $company_id);
        $stmt->execute();
        $results = $stmt->get_result();
    }

    /* =========================
       LOAD COMPANY SETTINGS
    ========================== */
    $stmtc = $mysqli->prepare("
        SELECT currency, vat_rate, invoice_prefix, company_logo
        FROM company
        WHERE id = ?
        LIMIT 1
    ");
    $stmtc->bind_param("i", $company_id);
    $stmtc->execute();
    $company = $stmtc->get_result()->fetch_assoc();

    $currency       = $company['currency'] ?? '';
    $vat_rate       = $company['vat_rate'] ?? 0;
    $invoice_prefix = $company['invoice_prefix'] ?? '';
    $company_logo   = $company['company_logo'] ?? '';

    /* =========================
       OUTPUT ACTION BUTTONS
    ========================== */
    echo '
    <div style="margin-bottom: 15px" class="d-flex justify-content-end mb-3">

        <a style="margin-right: 3px;" href="voucher-create.php" class="btn btn-secondary btn-sm me-2">
            <i class="fa fa-plus-circle"></i> New Voucher
        </a>

        <a href="voucherpdf-export.php'.$date_pdf_exp.'" 
           class="btn btn-primary btn-sm">
            <i class="fa fa-file-pdf-o"></i> Export PDF
        </a>

    </div>
    ';

    /* =========================
       TABLE
    ========================== */
    echo '
    <table class="table table-striped table-hover no-border" id="data-table">
        <thead>
            <tr>
                <th width="9%">Ref No.</th>
                <th width="20%">Title</th>
                <th>Date</th>
                <th>Mode</th>
                <th width="12%">Amount ('.$currency.')</th>
                <th width="12%">Requested By</th>
                <th>Department</th>
                <th width="13%">Created By</th>
                <th width="12%">Actions</th>
            </tr>
        </thead>
        <tbody>
    ';

    if ($results && $results->num_rows > 0) {

        while ($row = $results->fetch_assoc()) {

            echo '
            <tr>
                <td>'.$row["voucher_id"].'</td>
                <td>'.$row["short_title"].'</td>
                <td>'.date('d M Y', strtotime($row["voucher_date"])).'</td>
                <td>'.$row["payment_mode"].'</td>
                <td>'.number_format($row["total"], 2).'</td>
                <td>'.$row["requested_by"].'</td>
                <td>'.$row["voucher_dept"].'</td>
                <td>'.$row["created_by"].'</td>
                <td>

                    <a  href="voucher-view.php?id='.$row["voucher_id"].'" 
                       class="btn btn-success btn-xs">
                        <i class="glyphicon glyphicon-eye-open"></i>
                    </a>

                    <a href="voucher-edit.php?id='.$row["voucher_id"].'" 
                       class="btn btn-primary btn-xs">
                        <i class="glyphicon glyphicon-edit"></i>
                    </a>

                    <a href="voucher-download.php?id='.$row["voucher_id"].'" 
                       class="btn btn-info btn-xs" target="_blank">
                        <i class="glyphicon glyphicon-download-alt"></i>
                    </a>

                    <button class="btn btn-danger btn-xs delete-voucher"
                            data-voucher-id="'.$row["voucher_id"].'">
                        <i class="glyphicon glyphicon-trash"></i>
                    </button>

                </td>
            </tr>
            ';
        }

        echo '</tbody></table>';

    } 

    $stmt->close();
    $stmtc->close();
    $mysqli->close();


}


// get invoice list 
function getInvoices() {

    session_start();

    if (!isset($_SESSION['company_id'])) {
        	echo "
        <script>
            alert('Company context missing. Please re-login.');
            window.location.href = 'sign-in.php';
        </script>
        ";
        exit;
    }

    $company_id = (int) $_SESSION['company_id'];

    // Connect to the database
    $mysqli = new mysqli(DATABASE_HOST, DATABASE_USER, DATABASE_PASS, DATABASE_NAME);

    if ($mysqli->connect_error) {
        die('Error : (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
    }

    $date_pdf_exp = "";

    /* ======================
       BUILD QUERY
    ========================== */
    if (isset($_POST['search'])) {

        $date1 = date("Y-m-d", strtotime($_POST['date1']));
        $date2 = date("Y-m-d", strtotime($_POST['date2']));

        $stmt = $mysqli->prepare("
            SELECT i.*, c.name
            FROM invoices i
            JOIN customers c 
                ON c.invoice = i.invoice
               AND c.company_id = i.company_id
            WHERE i.company_id = ?
              AND DATE(i.invoice_date) BETWEEN ? AND ?
            ORDER BY i.invoice DESC
        ");

        $stmt->bind_param("iss", $company_id, $date1, $date2);
        $stmt->execute();
        $results = $stmt->get_result();

        $date_pdf_exp = "?init_date=$date1&final_date=$date2";

    } else {

        $stmt = $mysqli->prepare("
            SELECT i.*, c.name
            FROM invoices i
            JOIN customers c 
                ON c.invoice = i.invoice
               AND c.company_id = i.company_id
            WHERE i.company_id = ?
            ORDER BY i.invoice DESC
        ");

        $stmt->bind_param("i", $company_id);
        $stmt->execute();
        $results = $stmt->get_result();
    }

    /* =========================
       LOAD COMPANY SETTINGS
    ========================== */
    $stmtC = $mysqli->prepare("
        SELECT currency, vat_rate, invoice_prefix, company_logo
        FROM company
        WHERE id = ?
        LIMIT 1
    ");
    $stmtC->bind_param("i", $company_id);
    $stmtC->execute();
    $company = $stmtC->get_result()->fetch_assoc();

    $currency       = $company['currency'] ?? '';
    $vat_rate       = $company['vat_rate'] ?? 0;
    $invoice_prefix = $company['invoice_prefix'] ?? '';
    $company_logo   = $company['company_logo'] ?? '';

    /* =========================
       ACTION BUTTONS
    ========================== */
    echo '
    <div style="margin-bottom: 15px" class="d-flex justify-content-end mb-3 pt-3">

        <a style="margin-right: 3px;" href="invoice-create.php" class="btn btn-secondary btn-sm me-2">
            <i class="fa fa-plus-circle"></i> New Invoice
        </a>

        <a href="invoicepdf-export.php' . $date_pdf_exp . '" class="btn btn-primary btn-sm">
            <i class="fa fa-file-pdf-o"></i> Export PDF
        </a>

    </div>
    ';

    /* =========================
       TABLE OUTPUT
    ========================== */
    if ($results && $results->num_rows > 0) {

        echo '
        <table class="table table-striped table-hover no-border" id="data-table">
            <thead>
                <tr>
                    <th width="10%">Invoice</th>
                    <th width="20%">Customer</th>
                    <th width="10%">Issue Date</th>
                    <th width="10%">Due Date</th>
                    <th>Type</th>
                    <th>Amount (' . htmlspecialchars($currency) . ')</th>
                    <th width="9%">Status</th>
                    <th width="12%">Created By</th>
                    <th width="12%">Actions</th>
                </tr>
            </thead>
            <tbody>
        ';

        while ($row = $results->fetch_assoc()) {

            echo '
            <tr>
                <td>' . htmlspecialchars($row["invoice"]) . '</td>
                <td>' . htmlspecialchars($row["name"]) . '</td>
                <td>' . date('d M Y', strtotime($row["invoice_date"])) . '</td>
                <td>' . date('d M Y', strtotime($row["invoice_due_date"])) . '</td>
                <td>' . htmlspecialchars($row["invoice_type"]) . '</td>
                <td>' . number_format($row["total"], 2) . '</td>
            ';

            // Status badge
            if ($row['status'] === "Open") {
                echo '<td><span class="label label-primary">Open</span></td>';
            } elseif ($row['status'] === "Pending") {
                echo '<td><span class="label label-warning">Pending</span></td>';
            } elseif ($row['status'] === "Paid") {
                echo '<td><span class="label label-success">Paid</span></td>';
            } else {
                echo '<td><span class="label label-default">' . htmlspecialchars($row['status']) . '</span></td>';
            }

            echo '
                <td>' . htmlspecialchars($row["created_by"]) . '</td>
                <td>
                    <a href="invoice-view.php?id=' . urlencode($row["invoice"]) . '" class="btn btn-success btn-xs">
                        <span class="glyphicon glyphicon-eye-open"></span>
                    </a>

                    <a href="invoice-edit.php?id=' . urlencode($row["invoice"]) . '" class="btn btn-primary btn-xs">
                        <span class="glyphicon glyphicon-edit"></span>
                    </a>

                    <a href="invoice-download.php?id=' . urlencode($row["invoice"]) . '" 
                       class="btn btn-info btn-xs" target="_blank">
                        <span class="glyphicon glyphicon-download-alt"></span>
                    </a>

                    <a data-invoice-id="' . htmlspecialchars($row['invoice']) . '" 
                       class="btn btn-danger btn-xs delete-invoice">
                        <span class="glyphicon glyphicon-trash"></span>
                    </a>
                </td>
            </tr>
            ';
        }

        echo '</tbody></table>';

    } else {
        echo "<p>No invoices found.</p>";
    }

    /* =========================
       CLEANUP
    ========================== */
    $stmt->close();
    $stmtC->close();
    $mysqli->close();
}



// Preview next Voucher number (NO increment)
function previewNextVoucherNumber(mysqli $mysqli, int $company_id): int
{
    $stmt = $mysqli->prepare("
        SELECT voucher_counter
        FROM company
        WHERE id = ?
        LIMIT 1
    ");
    $stmt->bind_param("i", $company_id);
    $stmt->execute();

    $row = $stmt->get_result()->fetch_assoc();

    // If counter is NULL or missing, start from 1
    $current = (int) ($row['voucher_counter'] ?? 0);

    return $current;
}



//reserve Voucher Number
function reserveVoucherNumber(mysqli $mysqli, int $company_id): int
{
    $stmt = $mysqli->prepare("
        UPDATE company
        SET voucher_counter = LAST_INSERT_ID(voucher_counter + 1)
        WHERE id = ?
    ");
    $stmt->bind_param("i", $company_id);
    $stmt->execute();

    $result = $mysqli->query("SELECT LAST_INSERT_ID() AS reserved");
    return (int) $result->fetch_assoc()['reserved'];
}



// Preview next invoice number (NO increment)
function previewNextInvoiceNumber(mysqli $mysqli, int $company_id): int
{
    $stmt = $mysqli->prepare("
        SELECT invoice_counter
        FROM company
        WHERE id = ?
        LIMIT 1
    ");
    $stmt->bind_param("i", $company_id);
    $stmt->execute();

    $row = $stmt->get_result()->fetch_assoc();

    // If counter is NULL or missing, start from 1
    $current = (int) ($row['invoice_counter'] ?? 0);

    return $current;
}



//reserve
function reserveInvoiceNumber(mysqli $mysqli, int $company_id): int
{
    $stmt = $mysqli->prepare("
        UPDATE company
        SET invoice_counter = LAST_INSERT_ID(invoice_counter + 1)
        WHERE id = ?
    ");
    $stmt->bind_param("i", $company_id);
    $stmt->execute();

    $result = $mysqli->query("SELECT LAST_INSERT_ID() AS reserved");
    return (int) $result->fetch_assoc()['reserved'];
}


// Preview next Transaction number (NO increment)
function previewNextTransactionNumber(mysqli $mysqli, int $company_id): int
{
    $stmt = $mysqli->prepare("
        SELECT transaction_counter
        FROM company
        WHERE id = ?
        LIMIT 1
    ");
    $stmt->bind_param("i", $company_id);
    $stmt->execute();

    $row = $stmt->get_result()->fetch_assoc();

    // If counter is NULL or missing, start from 1
    $current = (int) ($row['transaction_counter'] ?? 0);

    return $current;
}



//reserve Transaction Number
function reserveTransactionNumber(mysqli $mysqli, int $company_id): int
{
    $stmt = $mysqli->prepare("
        UPDATE company
        SET transaction_counter = LAST_INSERT_ID(transaction_counter + 1)
        WHERE id = ?
    ");
    $stmt->bind_param("i", $company_id);
    $stmt->execute();

    $result = $mysqli->query("SELECT LAST_INSERT_ID() AS reserved");
    return (int) $result->fetch_assoc()['reserved'];
}


// populate product dropdown for invoice creation
function popProductsList() {

    session_start();

    // Ensure company context
    if (!isset($_SESSION['company_id'])) {
        echo "<p class='text-danger'>Company context missing. Please re-login.</p>";
        return;
    }

    $company_id = (int) $_SESSION['company_id'];

    // Connect to the database
    $mysqli = new mysqli(DATABASE_HOST, DATABASE_USER, DATABASE_PASS, DATABASE_NAME);

    // output any connection error
    if ($mysqli->connect_error) {
        die('Error : (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
    }

    // company-safe query
    $query = "
        SELECT product_name, product_desc, product_price
        FROM products
        WHERE company_id = ?
        ORDER BY product_name ASC
    ";

    $stmt = $mysqli->prepare($query);
    if ($stmt === false) {
        echo "<p class='text-danger'>Database error.</p>";
        return;
    }

    $stmt->bind_param("i", $company_id);
    $stmt->execute();
    $results = $stmt->get_result();

    if ($results && $results->num_rows > 0) {

        echo '<select class="form-control item-select">';
        echo '<option value="">-- Select Product --</option>';

        while ($row = $results->fetch_assoc()) {

            echo '
                <option
                    value="' . htmlspecialchars($row['product_price']) . '"
                    data-desc="' . htmlspecialchars($row['product_desc']) . '">
                    ' . htmlspecialchars($row['product_name']) . '
                </option>
            ';
        }

        echo '</select>';

    } else {

        echo "<p>No products found. Please add a product.</p>";

    }

    $stmt->close();
    $mysqli->close();
}


// populate customer dropdown for invoice creation
function popCustomersList() {

    session_start();

    // Ensure company context
    if (!isset($_SESSION['company_id'])) {
        echo "<p class='text-danger'>Company context missing. Please re-login.</p>";
        return;
    }

    $company_id = (int) $_SESSION['company_id'];

    // Connect to the database
    $mysqli = new mysqli(DATABASE_HOST, DATABASE_USER, DATABASE_PASS, DATABASE_NAME);

    // output any connection error
    if ($mysqli->connect_error) {
        die('Error : (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
    }

    // company-safe query
    $query = "
        SELECT *
        FROM store_customers
        WHERE company_id = ?
        ORDER BY name ASC
    ";

    $stmt = $mysqli->prepare($query);
    if ($stmt === false) {
        echo "<p class='text-danger'>Database error.</p>";
        return;
    }

    $stmt->bind_param("i", $company_id);
    $stmt->execute();
    $results = $stmt->get_result();

    if ($results && $results->num_rows > 0) {

        echo '
        <table class="table table-striped table-hover no-border margin-top" id="data-table">
        <thead>
            <tr>
                <th style="width:45%; font-size:12px;">NAME</th>
                <th style="width:25%; font-size:12px;">EMAIL</th>
                <th style="width:15%; font-size:12px;">PHONE</th>
                <th style="width:15%; font-size:12px;">ACTION</th>
            </tr>
        </thead>
        <tbody>';

        while ($row = $results->fetch_assoc()) {

            echo '
            <tr>
                <td>' . htmlspecialchars($row["name"]) . '</td>
                <td>' . htmlspecialchars($row["email"]) . '</td>
                <td>' . htmlspecialchars($row["phone"]) . '</td>
                <td>
                    <a href="#"
                       class="btn btn-primary btn-xs customer-select"

                       data-customer-name="' . htmlspecialchars($row['name']) . '"
                       data-customer-email="' . htmlspecialchars($row['email']) . '"
                       data-customer-phone="' . htmlspecialchars($row['phone']) . '"

                       data-customer-address-1="' . htmlspecialchars($row['address_1']) . '"
                       data-customer-address-2="' . htmlspecialchars($row['address_2']) . '"
                       data-customer-town="' . htmlspecialchars($row['town']) . '"
                       data-customer-county="' . htmlspecialchars($row['county']) . '"
                       data-customer-postcode="' . htmlspecialchars($row['postcode']) . '"

                       data-customer-name-ship="' . htmlspecialchars($row['name_ship']) . '"
                       data-customer-address-1-ship="' . htmlspecialchars($row['address_1_ship']) . '"
                       data-customer-address-2-ship="' . htmlspecialchars($row['address_2_ship']) . '"
                       data-customer-town-ship="' . htmlspecialchars($row['town_ship']) . '"
                       data-customer-county-ship="' . htmlspecialchars($row['county_ship']) . '"
                       data-customer-postcode-ship="' . htmlspecialchars($row['postcode_ship']) . '"
                    >
                        Select
                    </a>
                </td>
            </tr>';
        }

        echo '</tbody></table>';

    } else {
        echo "<p>No customers found.</p>";
    }

    $stmt->close();
    $mysqli->close();
}



// get products list
function getProducts() {

    session_start();

    if (!isset($_SESSION['company_id'])) {
        echo "<p class='text-danger'>Company context missing. Please re-login.</p>";
        return;
    }

    $company_id = (int) $_SESSION['company_id'];

    // Connect to the database
    $mysqli = new mysqli(DATABASE_HOST, DATABASE_USER, DATABASE_PASS, DATABASE_NAME);

    // output any connection error
    if ($mysqli->connect_error) {
        die('Error : (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
    }

    // company-scoped query
    $stmt = $mysqli->prepare("
        SELECT product_id, product_name, product_desc, product_price
        FROM products
        WHERE company_id = ?
        ORDER BY product_name ASC
    ");
    /* =========================
       ACTION BUTTONS
    ========================== */
    echo '
    <div style="margin-bottom: 15px" class="d-flex justify-content-end mb-3 pt-3">

        <a href="product-add.php" class="btn btn-secondary btn-sm me-2">
            <i class="fa fa-plus-circle"></i> New Product
        </a>


    </div>
    ';
    $stmt->bind_param("i", $company_id);
    $stmt->execute();
    $results = $stmt->get_result();

    if ($results && $results->num_rows > 0) {

   

        echo '
        <table class="table table-striped table-hover no-border" id="data-table">
            <thead>
                <tr>
                    <th style="width:35%;"><strong>PRODUCT</strong></th>
                    <th style="width:40%;"><strong>DESCRIPTION</strong></th>
                    <th style="width:15%;"><strong>PRICE</strong></th>
                    <th style="width:10%;"><strong>ACTION</strong></th>
                </tr>
            </thead>
            <tbody>
        ';

        while ($row = $results->fetch_assoc()) {

            echo '
                <tr>
                    <td>' . htmlspecialchars($row["product_name"]) . '</td>
                    <td>' . htmlspecialchars($row["product_desc"]) . '</td>
                    <td>' . number_format($row["product_price"], 2) . '</td>
                    <td>
                        <a href="product-edit.php?id=' . $row["product_id"] . '" 
                           class="btn btn-primary btn-xs">
                           <span class="glyphicon glyphicon-edit"></span>
                        </a>

                        <a data-product-id="' . $row["product_id"] . '" 
                           class="btn btn-danger btn-xs delete-product">
                           <span class="glyphicon glyphicon-trash"></span>
                        </a>
                    </td>
                </tr>
            ';
        }

        echo '</tbody></table>';

    } else {
        echo "<p>There are no products to display.</p>";
    }

    $stmt->close();
    $mysqli->close();
}


// get customer list
function getCustomers() {

    session_start();

    /* =========================
       COMPANY CONTEXT CHECK
    ========================== */
    if (!isset($_SESSION['company_id'])) {
        echo "<p class='text-danger'>Company context missing. Please re-login.</p>";
        return;
    }

    $company_id = (int) $_SESSION['company_id'];

    // Connect to the database
    $mysqli = new mysqli(DATABASE_HOST, DATABASE_USER, DATABASE_PASS, DATABASE_NAME);

    if ($mysqli->connect_error) {
        die('Error : (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
    }

    /* =========================
       COMPANY-SCOPED QUERY
    ========================== */
    $stmt = $mysqli->prepare("
        SELECT id, name, email, phone
        FROM store_customers
        WHERE company_id = ?
        ORDER BY name ASC
    ");

     echo '
    <div style="margin-bottom: 15px" class="d-flex justify-content-end mb-3 pt-3">

        <a href="customer-add.php" class="btn btn-secondary btn-sm me-2">
            <i class="fa fa-plus-circle"></i> New Customer
        </a>


    </div>
    ';

    $stmt->bind_param("i", $company_id);
    $stmt->execute();
    $results = $stmt->get_result();

    if ($results && $results->num_rows > 0) {

        echo '
        <table class="table table-striped table-hover no-border" id="data-table">
            <thead>
                <tr>
                    <th>Name</th>
                    <th>Email</th>
                    <th>Phone</th>
                    <th width="120">Action</th>
                </tr>
            </thead>
            <tbody>
        ';

        while ($row = $results->fetch_assoc()) {

            echo '
                <tr>
                    <td>' . htmlspecialchars($row["name"]) . '</td>
                    <td>' . htmlspecialchars($row["email"]) . '</td>
                    <td>' . htmlspecialchars($row["phone"]) . '</td>
                    <td>
                        <a href="customer-edit.php?id=' . (int)$row["id"] . '" 
                           class="btn btn-primary btn-xs">
                            <span class="glyphicon glyphicon-edit"></span>
                        </a>

                        <a href="#"
                           data-customer-id="' . (int)$row["id"] . '" 
                           class="btn btn-danger btn-xs delete-customer">
                            <span class="glyphicon glyphicon-trash"></span>
                        </a>
                    </td>
                </tr>
            ';
        }

        echo '</tbody></table>';

    } else {
        echo "<p class='text-muted'>There are no customers to display.</p>";
    }

    $stmt->close();
    $mysqli->close();
}


// get user list
function getUsers() {

	// Connect to the database
	$mysqli = new mysqli(DATABASE_HOST, DATABASE_USER, DATABASE_PASS, DATABASE_NAME);

	// output any connection error
	if ($mysqli->connect_error) {
	    die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
	}

	// the query
	$query = "SELECT * FROM users ORDER BY username ASC";

	// mysqli select query
	$results = $mysqli->query($query);

	if($results) {

		print '<table class="table table-striped table-hover no border" id="data-table"><thead><tr>

				<th>Name</th>
				<th>Username</th>
				<th>Email</th>
				<th style="width:13%;">Phone</th>
				<th>Role</th>
				<th>Action</th>

			  </tr></thead><tbody>';

              

		while($row = $results->fetch_assoc()) {

		    print '
			    <tr>
			    	<td>'.$row['name'].'</td>
					<td>'.$row["username"].'</td>
				    <td>'.$row["email"].'</td>
				    <td>'.$row["phone"].'</td>
					<td>'.$row["role"].'</td>
				    <td><a href="user-edit.php?id='.$row["id"].'" class="btn btn-primary btn-xs"><span class="glyphicon glyphicon-edit" aria-hidden="true"></span></a> <a data-user-id="'.$row['id'].'" class="btn btn-danger btn-xs delete-user"><span class="glyphicon glyphicon-trash" aria-hidden="true"></span></a></td>
			    </tr>
		    ';
		}

		print '</tr></tbody></table>';

	} else {

		echo "<p>There are no users to display.</p>";

	}

	// Frees the memory associated with a result
	$results->free();

	// close connection 
	$mysqli->close();
}


// get expenses list
function getExpenses() {

    session_start();

    if (!isset($_SESSION['company_id'])) {
        echo "<p class='text-danger'>Company context missing. Please re-login.</p>";
        return;
    }

    $company_id = (int) $_SESSION['company_id'];

    // Connect to the database
    $mysqli = new mysqli(DATABASE_HOST, DATABASE_USER, DATABASE_PASS, DATABASE_NAME);

    if ($mysqli->connect_error) {
        die('Error : (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
    }

    $date_pdf_exp = "";

    /* =========================
       BUILD QUERY
    ========================== */
    if (isset($_POST['search'])) {

        $date1 = date("Y-m-d", strtotime($_POST['date1']));
        $date2 = date("Y-m-d", strtotime($_POST['date2']));

        $sql = "
            SELECT *
            FROM expenses
            WHERE company_id = ?
              AND DATE(expense_date) BETWEEN ? AND ?
            ORDER BY expense_id DESC
        ";

        $stmt = $mysqli->prepare($sql);
        $stmt->bind_param("iss", $company_id, $date1, $date2);

        $date_pdf_exp = "?init_date=$date1&final_date=$date2";

    } else {

        $sql = "
            SELECT *
            FROM expenses
            WHERE company_id = ?
            ORDER BY expense_id DESC
        ";

        $stmt = $mysqli->prepare($sql);
        $stmt->bind_param("i", $company_id);
    }

    $stmt->execute();
    $results = $stmt->get_result();

    if ($results && $results->num_rows > 0) {

        /* =========================
           LOAD COMPANY SETTINGS
        ========================== */
        $stmtc = $mysqli->prepare("
            SELECT currency, vat_rate, invoice_prefix, company_logo
            FROM company
            WHERE id = ?
            LIMIT 1
        ");
        $stmtc->bind_param("i", $company_id);
        $stmtc->execute();
        $company = $stmtc->get_result()->fetch_assoc();

        $currency = $company['currency'] ?? '₦';

        /* =========================
           ACTION BUTTONS
        ========================== */
   

        echo '
        <div style="margin-bottom: 15px" class="d-flex mb-3 justify-content-end">

            <a style="margin-right: 3px" href="expense-add.php"
               class="btn btn-secondary btn-sm me-2">
                <i class="fa fa-plus-circle"></i> New Transaction
            </a>

            <a href="transactionpdf-export.php' . $date_pdf_exp . '"
               class="btn btn-primary btn-sm">
                <i class="fa fa-file-pdf-o"></i> Export PDF
            </a>

        </div>';

        /* =========================
           TABLE
        ========================== */
        echo '
        <table class="table table-striped table-hover  no-border" id="data-table">
        <thead>
        <tr>
            <th width="4%">ID</th>
            <th width="10%">REF. NO</th>
            <th width="6%">DATE</th>
            <th width="6%">TYPE</th>
            <th>DESCRIPTION</th>
            <th width="12%">CREATED BY</th>
           
            <th width="10%">AMOUNT (' . htmlspecialchars($currency) . ')</th>
           
            <th width="7%">STATUS</th>
            <th width="3%">DOC</th>
            <th width="12%">ACTION</th>
        </tr>
        </thead>
        <tbody>';

        while ($row = $results->fetch_assoc()) {

            echo '
            <tr>
                <td>' . htmlspecialchars($row['expense_id']) . '</td>
                <td>' . htmlspecialchars($row['reference_no']) . '</td>
                <td>' . htmlspecialchars($row['expense_date']) . '</td>
                <td>' . htmlspecialchars($row['transaction_type']) . '</td>
                <td>' . htmlspecialchars($row['expense_desc']) . '</td>
                <td>' . htmlspecialchars($row['created_by']) . '</td>
                
                <td>' . number_format($row['expense_amount'], 2) . '</td>';

            if ($row['transaction_status'] === 'completed') {
                echo '<td><span class="label label-success">Completed</span></td>';
            } else {
                echo '<td><span class="label label-warning">Pending</span></td>';
            }

            if (!empty($row['expense_doc'])) {
                echo '
                <td class="text-center">
                    <a href="images/doc/' . htmlspecialchars($row['expense_doc']) . '" target="_blank">
                        <span class="glyphicon glyphicon-eye-open"></span>
                    </a>
                </td>';
            } else {
                echo '<td></td>';
            }

            echo '
                <td>
                    <a href="expense-edit.php?id=' . (int)$row['id'] . '" class="btn btn-primary btn-xs">
                        <span class="glyphicon glyphicon-edit"></span>
                    </a>

                    <a data-expense-id="' . (int)$row['id'] . '" class="btn btn-danger btn-xs delete-expense">
                        <span class="glyphicon glyphicon-trash"></span>
                    </a>

                    <a href="expense-view.php?id='.$row["id"].'" 
                       class="btn btn-success btn-xs">
                        <i class="glyphicon glyphicon-eye-open"></i>
                    </a>

                    <a href="expense-download.php?id=' . (int)$row['id'] . '" class="btn btn-info btn-xs" target="_blank">
                        <span class="glyphicon glyphicon-download-alt"></span>
                    </a>
                </td>
            </tr>';
        }

        echo '</tbody></table>';

    } else {
        echo "<p>No transactions found.</p>";
    }

    $stmt->close();
    $mysqli->close();
}

// get schedule list
function getSchedule() {

    session_start();

    if (!isset($_SESSION['company_id'])) {
        echo "<p class='text-danger'>Company context missing. Please re-login.</p>";
        return;
    }

    $company_id = (int) $_SESSION['company_id'];

    // Connect to the database
    $mysqli = new mysqli(DATABASE_HOST, DATABASE_USER, DATABASE_PASS, DATABASE_NAME);

    if ($mysqli->connect_error) {
        die('Error : (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
    }

    // Always define to prevent PHP 8.4 warnings
    $date_pdf_exp = "";

    /* ============================
       BUILD QUERY (company-safe)
    ============================= */
    if (isset($_POST['search'])) {

        $date1 = date("Y-m-d", strtotime($_POST['date1']));
        $date2 = date("Y-m-d", strtotime($_POST['date2']));

        $query = "
            SELECT *
            FROM event_calendar
            WHERE company_id = ?
              AND DATE(start) BETWEEN ? AND ?
            ORDER BY start ASC
        ";

        $stmt = $mysqli->prepare($query);
        $stmt->bind_param("iss", $company_id, $date1, $date2);

        $date_pdf_exp = "?init_date=$date1&final_date=$date2";

    } else {

        $query = "
            SELECT *
            FROM event_calendar
            WHERE company_id = ?
            ORDER BY start ASC
        ";

        $stmt = $mysqli->prepare($query);
        $stmt->bind_param("i", $company_id);
    }

    $stmt->execute();
    $results = $stmt->get_result();

    if ($results && $results->num_rows > 0) {

        /* ============================
           LOAD COMPANY SETTINGS
        ============================= */

        $companyStmt = $mysqli->prepare("
            SELECT currency
            FROM company
            WHERE id = ?
            LIMIT 1
        ");

                echo '
        <div style="margin-bottom: 15px" class="d-flex mb-3 justify-content-end">

            <a style="margin-right: 3px" href="schedule-add.php"
               class="btn btn-secondary btn-sm me-2">
                <i class="fa fa-plus-circle"></i> New Schedule
            </a>

        </div>';
        $companyStmt->bind_param("i", $company_id);
        $companyStmt->execute();
        $company = $companyStmt->get_result()->fetch_assoc();

        $currency = $company['currency'] ?? '';

        echo '
        <table class="table table-striped table-hover no-border" id="data-table">
            <thead>
                <tr>
                    <th style="width:10%;" class="text-uppercase small">Date</th>
                    <th style="width:10%;" class="text-uppercase small">Type</th>
                    <th style="width:40%;" class="text-uppercase small">Narration</th>
                    <th style="width:25%;" class="text-uppercase small">Client / Location</th>
                    <th style="width:9%;"  class="text-uppercase small">Amount (' . htmlspecialchars($currency) . ')</th>
                    <th style="width:6%;"  class="text-uppercase small">Action</th>
                </tr>
            </thead>
            <tbody>
        ';

        while ($row = $results->fetch_assoc()) {

            echo '
            <tr>
                <td>' . date('d M Y', strtotime($row["start"])) . '</td>
                <td>' . htmlspecialchars($row["schedule_type"]) . '</td>
                <td>' . htmlspecialchars($row["title"]) . '</td>
                <td>' . htmlspecialchars($row["client_location"]) . '</td>
                <td>' . number_format((float)$row["schedule_amount"], 2) . '</td>
                <td>
                    <a href="schedule-edit.php?id=' . (int)$row["id"] . '" 
                       class="btn btn-primary btn-xs">
                        <span class="glyphicon glyphicon-edit"></span>
                    </a>

                    <a data-schedule-id="' . (int)$row['id'] . '" 
                       class="btn btn-danger btn-xs delete-schedule">
                        <span class="glyphicon glyphicon-trash"></span>
                    </a>
                </td>
            </tr>';
        }

        echo '</tbody></table>';

        $companyStmt->close();

    } else {
        echo "<p>No schedules found.</p>";
    }

    $stmt->close();
    $mysqli->close();
}


function getCompanyDetails(mysqli $db, int $company_id): array {
    $stmt = $db->prepare("
        SELECT *
        FROM company
        WHERE id = ?
        LIMIT 1
    ");
    $stmt->bind_param("i", $company_id);
    $stmt->execute();
    return $stmt->get_result()->fetch_assoc() ?? [];
}

//payments functions
//to block app usage entirely
function checkCompanySubscription(mysqli $mysqli, int $company_id): array
{
    $stmt = $mysqli->prepare("
        SELECT *
        FROM subscriptions
        WHERE company_id = ?
        LIMIT 1
    ");
    $stmt->bind_param("i", $company_id);
    $stmt->execute();
    $sub = $stmt->get_result()->fetch_assoc();

    // No subscription found
    if (!$sub) {
            jsAlert(
        'Subscription Not Found',
        'Please purchase a plan',
        'warning',
        'billing.php'
    );
        exit;
    }

    // CANCELLED SUBSCRIPTION
    if ($sub['status'] === 'cancelled') {
            jsAlert(
        'Your account has been suspended',
        'Please contact ADMIN',
        'warning',
        'sign-in.php'
    );
        exit;
    }

    $now = new DateTime();

    // Trial expired
    if ($sub['plan'] === 'trial' && $now > new DateTime($sub['trial_ends_at'])) {
        $mysqli->query("
            UPDATE subscriptions 
            SET status='expired' 
            WHERE company_id=$company_id
        ");
            jsAlert(
        'Trial Expired',
        'Please purchase a plan',
        'warning',
        'billing.php'
    );
        exit;
    }

    // Active OR expired
if (
    in_array($sub['status'], ['active', 'expired'], true)
    && $now > new DateTime($sub['ends_at'])
) {

    // Ensure status is expired in DB
    if ($sub['status'] !== 'expired') {
        $stmt = $mysqli->prepare("
            UPDATE subscriptions 
            SET status = 'expired'
            WHERE company_id = ?
        ");
        $stmt->bind_param('i', $company_id);
        $stmt->execute();
    }

    jsAlert(
        'Subscription Expired',
        'Please purchase a plan',
        'warning',
        'billing.php'
    );

    exit;
}


    return $sub;
}

// place below function on affected pages
//session_start();
//$sub = checkCompanySubscription($mysqli, $_SESSION['company_id']);



//to restrict usage- like create invoice, edit, etc

function canCreate(array $sub): bool
{
    $now = new DateTime();

    if ($sub['status'] === 'trial' && $now <= new DateTime($sub['trial_ends_at'])) {
        return true;
    }

    if ($sub['status'] === 'active' && $now <= new DateTime($sub['ends_at'])) {
        return true;
    }

    return false;
}

// place below function on affected pages
//if (!canCreate($sub)) {
//die("Upgrade required to create new records.");
//}


?>

