<?php
/**
 * ============================================================
 * CAPAPP IT SOLUTION
 * COMPLETE DOMAIN DATA EXCEL EXPORT
 * ============================================================
 */

include 'db.php';

if (!$conn) {
    die("Database connection failed.");
}

/* ============================================================
   EXCEL HEADERS
============================================================ */

header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
header("Content-Disposition: attachment; filename=domain-data-" . date('Y-m-d') . ".xls");
header("Pragma: no-cache");
header("Expires: 0");

/* UTF-8 BOM */
echo "\xEF\xBB\xBF";

/* ============================================================
   FETCH ALL DOMAIN DATA
============================================================ */

$query = mysqli_query(
    $conn,
    "SELECT * FROM domains ORDER BY expiry_date ASC, id DESC"
);

if (!$query) {
    die("Database Error: " . mysqli_error($conn));
}

/* ============================================================
   GET ALL COLUMN NAMES
============================================================ */

$fields = mysqli_num_fields($query);

$columns = [];

for ($i = 0; $i < $fields; $i++) {
    $fieldInfo = mysqli_fetch_field_direct($query, $i);
    $columns[] = $fieldInfo->name;
}

/* ============================================================
   HELPER FUNCTION
============================================================ */

function excelValue($value)
{
    if ($value === null || $value === '') {
        return '-';
    }

    return htmlspecialchars(
        (string)$value,
        ENT_QUOTES | ENT_SUBSTITUTE,
        'UTF-8'
    );
}

/* ============================================================
   HTML EXCEL DOCUMENT
============================================================ */

echo '<html>';
echo '<head>';

echo '<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">';

echo '
<style>

body{
    font-family: Arial, Helvetica, sans-serif;
    background:#f4f7fb;
    color:#172033;
}

table{
    border-collapse:collapse;
    width:100%;
    font-family:Arial, Helvetica, sans-serif;
    font-size:11px;
}

.title{
    background:#0f172a;
    color:#ffffff;
    font-size:20px;
    font-weight:bold;
    padding:14px;
    text-align:center;
}

.subtitle{
    background:#2563eb;
    color:#ffffff;
    font-size:12px;
    padding:9px;
    text-align:center;
}

th{
    background:#2563eb;
    color:#ffffff;
    font-weight:bold;
    border:1px solid #1d4ed8;
    padding:9px 7px;
    text-align:center;
    white-space:nowrap;
}

td{
    border:1px solid #dbe3ef;
    padding:7px;
    vertical-align:middle;
}

tr:nth-child(even) td{
    background:#f8fbff;
}

tr:nth-child(odd) td{
    background:#ffffff;
}

.number{
    mso-number-format:"0.00";
}

.date{
    mso-number-format:"yyyy-mm-dd";
}

.text{
    mso-number-format:"@";
}

.center{
    text-align:center;
}

.money{
    mso-number-format:"#,##0.00";
}

</style>
';

echo '</head>';
echo '<body>';

/* ============================================================
   TITLE
============================================================ */

echo '
<table>
<tr>
    <td colspan="' . $fields . '" class="title">
        CAPAPP IT SOLUTION PRIVATE LIMITED
    </td>
</tr>

<tr>
    <td colspan="' . $fields . '" class="subtitle">
        COMPLETE DOMAIN DATABASE EXPORT
    </td>
</tr>

<tr>
    <td colspan="' . $fields . '">
        <b>Export Date:</b> ' . date('d-m-Y H:i:s') . '
    </td>
</tr>

<tr>
    <td colspan="' . $fields . '">
        <b>Total Records:</b> ' . mysqli_num_rows($query) . '
    </td>
</tr>
</table>

<br>
';

/* ============================================================
   MAIN DATA TABLE
============================================================ */

echo '<table border="1">';

/* ============================================================
   HEADER
============================================================ */

echo '<tr>';

foreach ($columns as $column) {

    /*
     * Convert database field name into readable heading
     *
     * client_name -> Client Name
     * expiry_date -> Expiry Date
     * server_password -> Server Password
     */

    $heading = ucwords(
        str_replace(
            ['_', '-'],
            ' ',
            $column
        )
    );

    echo '<th>' . excelValue($heading) . '</th>';
}

echo '</tr>';

/* ============================================================
   DATA
============================================================ */

$rowNumber = 0;

while ($row = mysqli_fetch_assoc($query)) {

    $rowNumber++;

    echo '<tr>';

    foreach ($columns as $column) {

        $value = $row[$column] ?? '';

        /*
         * Password / ID / phone etc. ko Excel me text
         * rakhne ke liye leading apostrophe use nahi kar rahe,
         * CSS mso-number-format se text format set karenge.
         */

        $columnLower = strtolower($column);

        /* -----------------------------------------
           DATE FIELDS
        ----------------------------------------- */

        if (
            strpos($columnLower, 'date') !== false &&
            !empty($value) &&
            $value !== '0000-00-00'
        ) {

            echo '<td class="date">'
                . excelValue($value)
                . '</td>';

        }

        /* -----------------------------------------
           AMOUNT / PRICE FIELDS
        ----------------------------------------- */

        elseif (
            strpos($columnLower, 'amount') !== false ||
            strpos($columnLower, 'price') !== false ||
            strpos($columnLower, 'charge') !== false
        ) {

            if (is_numeric($value)) {

                echo '<td class="money">'
                    . number_format((float)$value, 2, '.', '')
                    . '</td>';

            } else {

                echo '<td>'
                    . excelValue($value)
                    . '</td>';
            }

        }

        /* -----------------------------------------
           PHONE / MOBILE / USER ID / PASSWORD
        ----------------------------------------- */

        elseif (
            strpos($columnLower, 'mobile') !== false ||
            strpos($columnLower, 'phone') !== false ||
            strpos($columnLower, 'password') !== false ||
            $columnLower === 'user_id'
        ) {

            echo '<td class="text">'
                . excelValue($value)
                . '</td>';

        }

        /* -----------------------------------------
           NORMAL DATA
        ----------------------------------------- */

        else {

            echo '<td>'
                . excelValue($value)
                . '</td>';
        }
    }

    echo '</tr>';
}

echo '</table>';

/* ============================================================
   FOOTER
============================================================ */

echo '
<br>

<table>

<tr>
    <td colspan="' . $fields . '">
        <b>Generated By:</b> CAPAPP IT SOLUTION PRIVATE LIMITED
    </td>
</tr>

<tr>
    <td colspan="' . $fields . '">
        <b>Website:</b> www.capapp.in
    </td>
</tr>

</table>
';

echo '</body>';
echo '</html>';

exit;
?>