<?php
// ini_set('display_errors', 1);
// ini_set('display_startup_errors', 1);
// error_reporting(E_ALL);

require("../config.php");

// Check database connection
if (!$db) {
    die("Database connection failed: " . mysqli_connect_error());
}

// Fetch all unique attributes safely
$attrQuery = "SELECT DISTINCT attri_name FROM contact_attributes WHERE client_id='$_SESSION[user_id]'";

$attrResult = mysqli_query($db, $attrQuery);

$attributes = [];
while ($row = mysqli_fetch_assoc($attrResult)) {
    $attributes[] = mysqli_real_escape_string($db, $row['attri_name']);
}

// Dynamically build attribute columns safely
$attributeColumns = "";
foreach ($attributes as $attribute) {
    $attributeColumns .= "MAX(CASE WHEN contact_attributes.attri_name = '$attribute' 
                        THEN contact_attributes.attri_value ELSE NULL END) AS `$attribute`,";
}
$attributeColumns = rtrim($attributeColumns, ',');

// Final SQL Query
$sql = "SELECT * FROM (
    SELECT contacts.name, contacts.country_code, contacts.phone, $attributeColumns
        FROM contacts
        LEFT JOIN contact_attributes ON contacts.id = contact_attributes.contact_id
        WHERE contacts.client_id = '$_SESSION[user_id]'
        GROUP BY contacts.id
) as sub";

################################################################
if (isset($_GET['filter']) && $_GET['filter'] != '') {
    try {
        $filters = json_decode($_GET['filter'], true);
        $conditions = [];
        foreach ($filters as $filter) {
            $conditions[] = "{$filter['attribute']}{$filter['operation']}'{$filter['value']}'";
        }
    } catch (\Throwable $th) {
        //throw $th;
    }
}
if (!empty($conditions)) {
    $sql = $sql . " WHERE " . implode(" AND ", $conditions);
}
################################################################

$result = mysqli_query($db, $sql);

if (!$result) {
    die("Query Error: " . mysqli_error($db));
}

// Set headers for CSV download
header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="contacts.csv"');
header('Pragma: no-cache');
header('Expires: 0');

// Open output stream
$output = fopen('php://output', 'w');

// Add UTF-8 BOM for Excel compatibility
//fwrite($output, "\xEF\xBB\xBF"); writes the UTF-8 BOM at the beginning of the file.
// This makes sure that Excel correctly interprets characters without corrupting them.
fwrite($output, "\xEF\xBB\xBF");  //putting content to csv file

// Define column headers
$header = array_merge(["Name", "Country Code", "Phone"], $attributes);
fputcsv($output, $header);

// Fetch and write data rows
while ($row = mysqli_fetch_assoc($result)) {
    $csvRow = [
        $row['name'] ?? '',
        $row['country_code'] ?? '',
        "\t" . $row['phone'] // ✅ Adding a tab before the phone number
    ];

    // Add attribute values dynamically with proper formatting
    foreach ($attributes as $attribute) {
        $csvRow[] = isset($row[$attribute]) && $row[$attribute] !== '' ? trim($row[$attribute]) : '-';
    }

    fputcsv($output, $csvRow);
}

// Close output stream
fclose($output);
mysqli_close($db);
exit;
