<?php

declare(strict_types=1);

/**
 * Ulandy / UWA CRM SaaS — Comprehensive Utility Functions
 * -------------------------------------------------------
 * - Dev mode: injects fake session user/tenant (no-login DX)
 * - PDO-safe database helpers (single source of truth: getDbConnection)
 * - Tenant-aware helpers (IDs & lookups)
 * - Invoices / Tasks / Customers / KPIs / Trends
 * - Email helpers (PHPMailer if available)
 * - Flash & CSRF utilities
 * - Small misc helpers
 *
 * This file assumes config/config.php has already defined:
 *   - APP_ROOT, BASE_URL, ENVIRONMENT, $pdo, etc.
 */

// Bootstrap guard (load config if functions.php is called directly)
if (!defined('BASE_URL')) {
    require_once __DIR__ . '/../config/config.php'; // from includes/ to config/
}



//
// Composer (optional: PHPMailer, etc.)
//
$__autoload = APP_ROOT . '/vendor/autoload.php';
if (is_readable($__autoload)) {
    require_once $__autoload;
    unset($__autoload);
}

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

/* ============================================================
 * CONSTANTS
 * ============================================================ */

if (!defined('CSRF_TOKEN_NAME')) {
    define('CSRF_TOKEN_NAME', 'csrf_token');
}

/* ============================================================
 * SESSION HELPERS
 * ============================================================ */

/**
 * Start session once; seed a fake user/tenant in development.
 */
if (!function_exists('session_start_safe')) {
    function session_start_safe(): void
    {
        if (session_status() === PHP_SESSION_NONE) {
            session_start();
        }

        // Dev-only DX: inject a fake tenant/user so pages work without login
        if ((defined('ENVIRONMENT') && ENVIRONMENT === 'development')) {
            if (empty($_SESSION['user'])) {
                $_SESSION['user'] = [
                    'user_id'    => 1,
                    'first_name' => 'Dev',
                    'last_name'  => 'User',
                    'email'      => 'dev@example.com',
                    'tenant_id'  => 1,
                    'role'       => 'admin',
                    'roles'      => ['admin', 'manager'],
                    'is_active'  => true,
                ];
            }
            if (empty($_SESSION['tenant'])) {
                $_SESSION['tenant'] = [
                    'tenant_id' => 1,
                    'plan'      => 'pro',
                    'name'      => 'Dev Tenant',
                ];
            }
        }
    }
}

/* ============================================================
 * TENANT & USER HELPERS
 * ============================================================ */

/** Get current tenant id or null */
if (!function_exists('getTenantId')) {
    function getTenantId(): ?int
    {
        return isset($_SESSION['user']['tenant_id'])
            ? (int)$_SESSION['user']['tenant_id']
            : null;
    }
}

/** Get current user array (or null) */
if (!function_exists('current_user')) {
    function current_user(): ?array
    {
        session_start_safe();
        return $_SESSION['user'] ?? null;
    }
}

/** Get current tenant record (fallback to sensible defaults) */
if (!function_exists('current_tenant')) {
    function current_tenant(PDO $db): array
    {
        $tid = (int)(getTenantId() ?? 0);
        if ($tid <= 0) {
            return ['tenant_id' => 0, 'name' => 'Unknown Workspace', 'plan' => ''];
        }
        $stmt = $db->prepare("SELECT tenant_id, name, plan FROM tenants WHERE tenant_id = :tid");
        $stmt->execute(['tid' => $tid]);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: ['tenant_id' => $tid, 'name' => 'Unknown Workspace', 'plan' => ''];
    }
}

/* ============================================================
 * RBAC HELPERS
 * ============================================================ */

if (!function_exists('hasRole')) {
    function hasRole(string $role): bool
    {
        return in_array($role, $_SESSION['user']['roles'] ?? [], true);
    }
}

if (!function_exists('hasAnyRole')) {
    function hasAnyRole(array $roles): bool
    {
        return !empty(array_intersect($_SESSION['user']['roles'] ?? [], $roles));
    }
}

if (!function_exists('requireRoles')) {
    function requireRoles(array $allowedRoles): void
    {
        session_start_safe();
        if (!isset($_SESSION['user']) || !hasAnyRole($allowedRoles)) {
            show_access_denied();
        }
    }
}

/* ============================================================
 * REDIRECTION & ERRORS
 * ============================================================ */

if (!function_exists('redirect')) {
    function redirect(string $url): void
    {
        header("Location: $url");
        exit;
    }
}

if (!function_exists('show_access_denied')) {
    function show_access_denied(): void
    {
        http_response_code(403);
        echo "<h2 style='color:red; font-family:system-ui, sans-serif'>Access Denied</h2>";
        exit;
    }
}

/* ============================================================
 * INPUT SANITIZATION
 * ============================================================ */

if (!function_exists('sanitizeInput')) {
    function sanitizeInput(string $value): string
    {
        return htmlspecialchars(strip_tags(trim($value)), ENT_QUOTES, 'UTF-8');
    }
}

if (!function_exists('post')) {
    function post(string $key): ?string
    {
        return isset($_POST[$key]) ? sanitizeInput((string)$_POST[$key]) : null;
    }
}

if (!function_exists('get')) {
    function get(string $key): ?string
    {
        return isset($_GET[$key]) ? sanitizeInput((string)$_GET[$key]) : null;
    }
}

/* ============================================================
 * MISC HELPERS
 * ============================================================ */

/** Quick curl JSON fetch (used sparingly) */
if (!function_exists('fetchDataFromApi')) {
    function fetchDataFromApi(string $url): array
    {
        $ch = curl_init();
        curl_setopt_array($ch, [
            CURLOPT_URL            => $url,
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_FOLLOWLOCATION => true,
            CURLOPT_TIMEOUT        => 15,
        ]);
        $response = curl_exec($ch);
        if (curl_errno($ch)) {
            $err = curl_error($ch);
            curl_close($ch);
            return ['success' => false, 'message' => $err];
        }
        curl_close($ch);

        if (strpos((string)$response, '<html') !== false) {
            return ['success' => false, 'message' => 'Unexpected HTML response.'];
        }
        $data = json_decode((string)$response, true);
        if (!is_array($data)) {
            return ['success' => false, 'message' => 'Failed to parse API response.'];
        }
        return $data;
    }
}

/* ============================================================
 * DATABASE HELPERS (PDO)
 * ============================================================ */

if (!function_exists('getDbConnection')) {
    function getDbConnection(): PDO
    {
        /** @var PDO $pdo */
        global $pdo;
        return $pdo;
    }
}

/** Internal: associative param array check */
if (!function_exists('__is_assoc')) {
    function __is_assoc(array $arr): bool
    {
        return array_keys($arr) !== range(0, count($arr) - 1);
    }
}

/** Execute a write statement */
if (!function_exists('executeQuery')) {
    function executeQuery(string $sql, array $params = []): bool
    {
        $stmt = getDbConnection()->prepare($sql);
        return $stmt->execute($params);
    }
}

/** Fetch all rows */
if (!function_exists('fetchAll')) {
    function fetchAll(string $sql, array $params = []): array
    {
        $pdo = getDbConnection();
        try {
            $stmt = $pdo->prepare($sql);
            if (__is_assoc($params)) {
                foreach ($params as $k => $v) {
                    $stmt->bindValue($k, $v);
                }
                $stmt->execute();
            } else {
                $stmt->execute($params);
            }
            return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (PDOException $e) {
            if (defined('ENVIRONMENT') && ENVIRONMENT === 'development') {
                error_log("[fetchAll] SQL Error: " . $e->getMessage() . " | Query: $sql");
            }
            return [];
        }
    }
}

/** Fetch single row */
if (!function_exists('fetchOne')) {
    function fetchOne(string $sql, array $params = []): ?array
    {
        $pdo = getDbConnection();
        try {
            $stmt = $pdo->prepare($sql);
            if (__is_assoc($params)) {
                foreach ($params as $k => $v) {
                    $stmt->bindValue($k, $v);
                }
                $stmt->execute();
            } else {
                $stmt->execute($params);
            }
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            return $row ?: null;
        } catch (PDOException $e) {
            if (defined('ENVIRONMENT') && ENVIRONMENT === 'development') {
                error_log("[fetchOne] SQL Error: " . $e->getMessage() . " | Query: $sql");
            }
            return null;
        }
    }
}

/** Fetch first column */
if (!function_exists('fetchValue')) {
    function fetchValue(string $sql, array $params = [])
    {
        $stmt = getDbConnection()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchColumn();
    }
}
/* ============================================================
 * WEEKLY REPORTS — ISO week helper
 * ============================================================ */

/**
 * Returns ['iso_year'=>int,'iso_week'=>int,'week_start'=>Y-m-d,'week_end'=>Y-m-d]
 * $date can be 'Y-m-d' or anything strtotime() accepts; null = today.
 */
if (!function_exists('isoWeekInfo')) {
    function isoWeekInfo(?string $date = null): array
    {
        $dt = $date ? new DateTimeImmutable($date) : new DateTimeImmutable('now');
        $isoYear = (int)$dt->format('o');
        $isoWeek = (int)$dt->format('W');
        $weekStart = $dt->modify('monday this week')->format('Y-m-d');
        $weekEnd   = $dt->modify('sunday this week')->format('Y-m-d');
        return ['iso_year' => $isoYear, 'iso_week' => $isoWeek, 'week_start' => $weekStart, 'week_end' => $weekEnd];
    }
}

/* ============================================================
 * WEEKLY REPORTS — Core CRUD
 * ============================================================ */

/**
 * Upsert the weekly report for (tenant, employee, year, week).
 * Requires UNIQUE(tenant_id, employee_id, iso_year, iso_week) on weekly_reports.
 * Returns the full report row.
 */
if (!function_exists('upsertWeeklyReport')) {
    function upsertWeeklyReport(
        int $tenantId,
        int $employeeId,
        int $isoYear,
        int $isoWeek,
        string $weekStart,
        string $weekEnd
    ): ?array {
        $pdo = getDbConnection();
        try {
            $pdo->beginTransaction();

            $sql = "INSERT INTO weekly_reports
                      (tenant_id, employee_id, iso_year, iso_week, week_start_date, week_end_date)
                    VALUES
                      (:tenant_id, :employee_id, :iso_year, :iso_week, :week_start, :week_end)
                    ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)";
            $stmt = $pdo->prepare($sql);
            $stmt->execute([
                ':tenant_id' => $tenantId,
                ':employee_id' => $employeeId,
                ':iso_year' => $isoYear,
                ':iso_week' => $isoWeek,
                ':week_start' => $weekStart,
                ':week_end' => $weekEnd
            ]);

            $id = (int)$pdo->lastInsertId();
            $row = fetchOne(
                "SELECT * FROM weekly_reports WHERE id=:id AND tenant_id=:tid",
                [':id' => $id, ':tid' => $tenantId]
            );

            $pdo->commit();
            return $row;
        } catch (Throwable $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            error_log("upsertWeeklyReport error: " . $e->getMessage());
            return null;
        }
    }
}

/** Get a report with its items (for rendering/email) */
if (!function_exists('getWeeklyReportWithItems')) {
    function getWeeklyReportWithItems(int $tenantId, int $reportId): ?array
    {
        $report = fetchOne(
            "SELECT * FROM weekly_reports WHERE id=:id AND tenant_id=:tid",
            [':id' => $reportId, ':tid' => $tenantId]
        );
        if (!$report) return null;

        $items = fetchAll(
            "SELECT wri.*, COALESCE(wri.title_override, t.title) AS item_title
             FROM weekly_report_items wri
             LEFT JOIN tasks t
               ON t.task_id = wri.task_id AND t.tenant_id = :tid
             WHERE wri.report_id = :rid
             ORDER BY wri.id",
            [':rid' => $reportId, ':tid' => $tenantId]
        );

        return ['report' => $report, 'items' => $items];
    }
}

/** Candidate tasks touched in week for employee */
if (!function_exists('getWeeklyReportCandidates')) {
    /**
     * Candidate tasks that changed or were due/completed within the week window.
     */
    function getWeeklyReportCandidates(int $tenantId, int $employeeId, string $weekStart, string $weekEnd): array
    {
        $sql = "
            SELECT
                t.task_id,
                t.title,
                t.description,
                t.progress,
                t.updated_at,
                t.due_date,
                t.completed_at,
                ta.assigned_at
            FROM tasks t
            JOIN task_assignments ta
              ON ta.task_id = t.task_id
             AND ta.tenant_id = t.tenant_id
            WHERE t.tenant_id = :tid
              AND ta.assignee_id = :eid
              AND (
                    (t.updated_at   BETWEEN :ws1 AND :we1)
                 OR (t.due_date     BETWEEN :ws2 AND :we2)
                 OR (t.completed_at BETWEEN :ws3 AND :we3)
              )
            ORDER BY COALESCE(t.updated_at, t.due_date, t.completed_at) DESC
        ";

        // Each placeholder must be unique when ATTR_EMULATE_PREPARES = false
        $params = [
            ':tid' => $tenantId,
            ':eid' => $employeeId,
            ':ws1' => $weekStart,
            ':we1' => $weekEnd,
            ':ws2' => $weekStart,
            ':we2' => $weekEnd,
            ':ws3' => $weekStart,
            ':we3' => $weekEnd,
        ];

        return fetchAll($sql, $params);
    }
}

/** List items for a report */
if (!function_exists('listWeeklyReportItems')) {
    function listWeeklyReportItems(int $tenantId, int $reportId): array
    {
        return fetchAll(
            "SELECT wri.*, COALESCE(wri.title_override, t.title) AS item_title
             FROM weekly_report_items wri
             LEFT JOIN tasks t ON t.task_id = wri.task_id AND t.tenant_id = :tid
             WHERE wri.report_id = :rid
             ORDER BY wri.id",
            [':rid' => $reportId, ':tid' => $tenantId]
        );
    }
}

/** Create a single item; returns the new row */
if (!function_exists('createWeeklyReportItem')) {
    function createWeeklyReportItem(array $payload): ?array
    {
        $pdo = getDbConnection();
        $sql = "INSERT INTO weekly_report_items
                (report_id, task_id, title_override, description, hours_spent, progress_before, progress_after, blocked, blockers_note, links)
                VALUES
                (:report_id, :task_id, :title_override, :description, :hours_spent, :progress_before, :progress_after, :blocked, :blockers_note, :links)";
        $ok = executeQuery($sql, [
            ':report_id'       => (int)($payload['report_id'] ?? 0),
            ':task_id'         => $payload['task_id']         ?? null,
            ':title_override'  => $payload['title_override']  ?? null,
            ':description'     => $payload['description']     ?? '',
            ':hours_spent'     => $payload['hours_spent']     ?? null,
            ':progress_before' => $payload['progress_before'] ?? null,
            ':progress_after'  => $payload['progress_after']  ?? null,
            ':blocked'         => !empty($payload['blocked']) ? 1 : 0,
            ':blockers_note'   => $payload['blockers_note']   ?? null,
            ':links'           => $payload['links']           ?? null,
        ]);
        if (!$ok) return null;

        $id = (int)getDbConnection()->lastInsertId();
        return fetchOne("SELECT * FROM weekly_report_items WHERE id=:id", [':id' => $id]);
    }
}

/** Update a single item; returns the updated row */
if (!function_exists('updateWeeklyReportItem')) {
    function updateWeeklyReportItem(int $id, array $fields): ?array
    {
        $allowed = ['task_id', 'title_override', 'description', 'hours_spent', 'progress_before', 'progress_after', 'blocked', 'blockers_note', 'links'];
        $sets = [];
        $params = [':id' => $id];

        foreach ($allowed as $k) {
            if (array_key_exists($k, $fields)) {
                $sets[] = "$k = :$k";
                $params[":$k"] = ($k === 'blocked') ? (!empty($fields[$k]) ? 1 : 0) : $fields[$k];
            }
        }
        if (!$sets) return fetchOne("SELECT * FROM weekly_report_items WHERE id=:id", [':id' => $id]);

        $sql = "UPDATE weekly_report_items SET " . implode(', ', $sets) . " WHERE id=:id";
        executeQuery($sql, $params);
        return fetchOne("SELECT * FROM weekly_report_items WHERE id=:id", [':id' => $id]);
    }
}

/** Delete a single item */
if (!function_exists('deleteWeeklyReportItem')) {
    function deleteWeeklyReportItem(int $id): bool
    {
        return executeQuery("DELETE FROM weekly_report_items WHERE id=:id", [':id' => $id]);
    }
}

/* ============================================================
 * WEEKLY REPORTS — Email helper + Submit
 * ============================================================ */

/** Build a simple HTML summary table for the weekly report email */
if (!function_exists('generateWeeklyReportEmailHtml')) {
    function generateWeeklyReportEmailHtml(array $report, array $items): string
    {
        $rows = '';
        foreach ($items as $i) {
            $title = $i['item_title'] ?? $i['title_override'] ?? '(No Title)';
            $rows .= '<tr>'
                . '<td>' . htmlspecialchars((string)$title) . '</td>'
                . '<td>' . htmlspecialchars((string)($i['description'] ?? '')) . '</td>'
                . '<td>' . htmlspecialchars((string)($i['hours_spent'] ?? '')) . '</td>'
                . '<td>' . htmlspecialchars((string)($i['progress_before'] ?? '')) . ' → ' . htmlspecialchars((string)($i['progress_after'] ?? '')) . '</td>'
                . '<td>' . (!empty($i['blocked']) ? 'Yes' : 'No') . '</td>'
                . '</tr>';
        }
        return '<h3>Weekly Accomplishment Report</h3>'
            . '<p>Employee: ' . htmlspecialchars((string)$report['employee_id']) . ' | Week: '
            . htmlspecialchars((string)$report['iso_year']) . '-W' . htmlspecialchars((string)$report['iso_week']) . '</p>'
            . '<table border="1" cellpadding="6" cellspacing="0">'
            . '<thead><tr><th>Item</th><th>Description</th><th>Hours</th><th>Progress</th><th>Blocked</th></tr></thead>'
            . '<tbody>' . $rows . '</tbody></table>';
    }
}

/**
 * Submit (transition draft → submitted), then email manager.
 * Returns ['ok'=>true] or ['ok'=>false,'error'=>...]
 * NOTE: Expects `mail_send()` to be available from config/config.php
 */
if (!function_exists('submitWeeklyReport')) {
    function submitWeeklyReport(
        int $tenantId,
        int $reportId,
        int $employeeId,
        ?int $managerId = null,
        ?string $managerEmailOverride = null
    ): array {
        $pdo = getDbConnection();
        try {
            $pdo->beginTransaction();

            $aff = executeQuery(
                "UPDATE weekly_reports
                 SET status='submitted', submitted_at=NOW(), submitted_by=:eid, manager_id=:mid
                 WHERE id=:rid AND tenant_id=:tid AND status='draft'",
                [':eid' => $employeeId, ':mid' => $managerId, ':rid' => $reportId, ':tid' => $tenantId]
            );
            if (!$aff) {
                $pdo->rollBack();
                return ['ok' => false, 'error' => 'Report not in draft or not found'];
            }

            $report = fetchOne(
                "SELECT wr.*, e.email AS employee_email, m.email AS manager_email
                 FROM weekly_reports wr
                 LEFT JOIN employees e ON e.employee_id = wr.employee_id AND e.tenant_id = wr.tenant_id
                 LEFT JOIN employees m ON m.employee_id = wr.manager_id  AND m.tenant_id = wr.tenant_id
                 WHERE wr.id=:rid AND wr.tenant_id=:tid",
                [':rid' => $reportId, ':tid' => $tenantId]
            );
            $items = fetchAll(
                "SELECT wri.*, COALESCE(wri.title_override, t.title) AS item_title
                 FROM weekly_report_items wri
                 LEFT JOIN tasks t ON t.task_id = wri.task_id AND t.tenant_id = :tid
                 WHERE wri.report_id = :rid
                 ORDER BY wri.id",
                [':rid' => $reportId, ':tid' => $tenantId]
            );

            $pdo->commit();

            // Email
            $to = $managerEmailOverride ?: ($report['manager_email'] ?? '');
            if ($to) {
                $subject = "Weekly Report: {$report['iso_year']}-W{$report['iso_week']} (Emp #{$report['employee_id']})";
                $html    = generateWeeklyReportEmailHtml($report, $items);
                if (function_exists('mail_send')) {
                    $resp = mail_send(['to' => $to, 'subject' => $subject, 'html' => $html]);
                    if (!$resp['ok']) {
                        error_log("submitWeeklyReport: email send failed: " . $resp['error']);
                    }
                } else {
                    error_log("submitWeeklyReport: mail_send() not available; email skipped.");
                }
            }

            return ['ok' => true];
        } catch (Throwable $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            error_log("submitWeeklyReport error: " . $e->getMessage());
            return ['ok' => false, 'error' => 'Server error'];
        }
    }
}
/* ============================================================
 * DEMO REQUEST — Form handling, database insert, and email notification
 * ============================================================ */

/** Function to process demo requests */
# if (!function_exists('handleDemoRequest')) {
#     function handleDemoRequest($pdo, $data)
#     {
#         // Prepare the SQL to insert the demo request into the database
#         $sql = "INSERT INTO demo_requests 
#                     (full_name, email, phone, company_name, city, country, message, ip_address, is_new, created_at)
#                 VALUES 
#                     (:full_name, :email, :phone, :company_name, :city, :country, :message, :ip_address, 1, NOW())";
# 
#         // Prepare the statement
#         $stmt = $pdo->prepare($sql);
# 
#         // Bind the parameters
#         $stmt->bindParam(':full_name', $data['name'], PDO::PARAM_STR);
#         $stmt->bindParam(':email', $data['email'], PDO::PARAM_STR);
#         $stmt->bindParam(':phone', $data['phone'], PDO::PARAM_STR);
#         $stmt->bindParam(':company_name', $data['company_name'], PDO::PARAM_STR);
#         $stmt->bindParam(':city', $data['city'], PDO::PARAM_STR);
#         $stmt->bindParam(':country', $data['country'], PDO::PARAM_STR);
#         $stmt->bindParam(':message', $data['message'], PDO::PARAM_STR);
#         $stmt->bindParam(':ip_address', $_SERVER['REMOTE_ADDR'], PDO::PARAM_STR);
# 
#         // Execute the query and check for success
#         if ($stmt->execute()) {
#             // Send email to sales team
#             $to = 'sales@ulandycrm.com'; // Sales email address
#             $subject = 'New Demo Request from ' . htmlspecialchars($data['name']);
#             $message = "You have received a new demo request from:\n\n"
#                 . "Name: " . htmlspecialchars($data['name']) . "\n"
#                 . "Email: " . htmlspecialchars($data['email']) . "\n"
#                 . "Phone: " . htmlspecialchars($data['phone']) . "\n"
#                 . "Company: " . htmlspecialchars($data['company_name']) . "\n"
#                 . "City: " . htmlspecialchars($data['city']) . "\n"
#                 . "Country: " . htmlspecialchars($data['country']) . "\n"
#                 . "Message: " . htmlspecialchars($data['message']) . "\n"
#                 . "IP Address: " . htmlspecialchars($_SERVER['REMOTE_ADDR']) . "\n";
# 
#             $headers = "From: no-reply@ulandycrm.com" . "\r\n";
#             $headers .= "Reply-To: " . htmlspecialchars($data['email']) . "\r\n";
#             $headers .= "Content-Type: text/plain; charset=UTF-8\r\n";
# 
#             mail($to, $subject, $message, $headers);
# 
#             return true;  // Successful insertion
#         } else {
#             return false; // Error occurred
#         }
#     }
# }
if (!function_exists('handleDemoRequest')) {
    function handleDemoRequest($pdo, $data)
    {
        // SQL query to insert demo request into database
        $sql = "INSERT INTO demo_requests 
                    (full_name, email, phone, company_name, city, country, message, ip_address, is_new, created_at)
                VALUES 
                    (:full_name, :email, :phone, :company_name, :city, :country, :message, :ip_address, 1, NOW())";

        $stmt = $pdo->prepare($sql);

        // Bind form input data to the SQL statement
        $stmt->bindParam(':full_name', $data['name'], PDO::PARAM_STR);
        $stmt->bindParam(':email', $data['email'], PDO::PARAM_STR);
        $stmt->bindParam(':phone', $data['phone'], PDO::PARAM_STR);
        $stmt->bindParam(':company_name', $data['company_name'], PDO::PARAM_STR);
        $stmt->bindParam(':city', $data['city'], PDO::PARAM_STR);
        $stmt->bindParam(':country', $data['country'], PDO::PARAM_STR);
        $stmt->bindParam(':message', $data['message'], PDO::PARAM_STR);
        $stmt->bindParam(':ip_address', $_SERVER['REMOTE_ADDR'], PDO::PARAM_STR);

        // Execute the SQL query
        if ($stmt->execute()) {
            // Send email to sales team
            $to = 'sales@ulandycrm.com';  // Sales team email
            $subject = 'New Demo Request from ' . htmlspecialchars($data['name']);
            $message = "You have received a new demo request from:\n\n"
                . "Name: " . htmlspecialchars($data['name']) . "\n"
                . "Email: " . htmlspecialchars($data['email']) . "\n"
                . "Phone: " . htmlspecialchars($data['phone']) . "\n"
                . "Company: " . htmlspecialchars($data['company_name']) . "\n"
                . "City: " . htmlspecialchars($data['city']) . "\n"
                . "Country: " . htmlspecialchars($data['country']) . "\n"
                . "Message: " . htmlspecialchars($data['message']) . "\n"
                . "IP Address: " . htmlspecialchars($_SERVER['REMOTE_ADDR']) . "\n";

            $headers = "From: no-reply@ulandycrm.com" . "\r\n";
            $headers .= "Reply-To: " . htmlspecialchars($data['email']) . "\r\n";
            $headers .= "Content-Type: text/plain; charset=UTF-8\r\n";

            // Send the email
            mail($to, $subject, $message, $headers);

            return true;  // Successfully inserted data and sent email
        } else {
            return false;  // Error occurred during database insertion
        }
    }
}
// Simple error log function
if (!function_exists('logError')) {
    function logError($message)
    {
        $logFile = __DIR__ . '/../logs/error_log.txt';
        file_put_contents($logFile, date('Y-m-d H:i:s') . " - " . $message . "\n", FILE_APPEND);
    }
}

/* ============================================================
 * INVOICE HELPERS
 * ============================================================ */
/* ============================================================
 * INVOICE HELPERS  (production-ready)
 * - Adds customer_name via LEFT JOIN
 * - Keeps backward compatibility
 * - Optional pagination helper included
 * ============================================================ */

/** Back-compat alias: some views still call getInvoicesByTenant() */
if (!function_exists('getInvoicesByTenant')) {
    function getInvoicesByTenant(int $tenantId): array
    {
        return getInvoicesForTenant($tenantId);
    }
}

/** List invoices for a tenant (now includes customer_name) */
if (!function_exists('getInvoicesForTenant')) {
    function getInvoicesForTenant(int $tenantId): array
    {
        $sql = "
            SELECT  i.invoice_id,
                    i.title,
                    i.description,
                    i.total_amount,
                    i.status,
                    i.due_date,
                    i.customer_id,
                    i.currency_id,
                    i.created_by,
                    i.created_at,
                    /* Canonical display name for UI */
                    COALESCE(c.company_name, CONCAT('Customer #', i.customer_id)) AS customer_name
            FROM invoices i
            LEFT JOIN customers c
              ON c.customer_id = i.customer_id
             AND c.tenant_id   = i.tenant_id
            WHERE i.tenant_id = ?
            ORDER BY i.created_at DESC
        ";
        return fetchAll($sql, [$tenantId]);
    }
}

/** Single invoice by id for a tenant (now includes customer_name) */
if (!function_exists('getInvoiceById')) {
    function getInvoiceById(int $invoiceId, int $tenantId): ?array
    {
        $sql = "
            SELECT  i.invoice_id,
                    i.title,
                    i.description,
                    i.total_amount,
                    i.status,
                    i.due_date,
                    i.customer_id,
                    i.currency_id,
                    i.created_by,
                    i.created_at,
                    COALESCE(c.company_name, CONCAT('Customer #', i.customer_id)) AS customer_name
            FROM invoices i
            LEFT JOIN customers c
              ON c.customer_id = i.customer_id
             AND c.tenant_id   = i.tenant_id
            WHERE i.invoice_id = ?
              AND i.tenant_id  = ?
            LIMIT 1
        ";
        return fetchOne($sql, [$invoiceId, $tenantId]);
    }
}

/** Invoice KPIs (counts & amount totals) */
if (!function_exists('getInvoiceKPIs')) {
    function getInvoiceKPIs(int $tenantId): array
    {
        try {
            $sql = "
                SELECT
                    COUNT(*) AS total_count,
                    COALESCE(SUM(total_amount),0) AS total_amount,
                    SUM(CASE WHEN status='draft'   THEN 1 ELSE 0 END) AS draft_count,
                    SUM(CASE WHEN status='sent'    THEN 1 ELSE 0 END) AS sent_count,
                    SUM(CASE WHEN status='paid'    THEN 1 ELSE 0 END) AS paid_count,
                    SUM(CASE WHEN status='overdue' THEN 1 ELSE 0 END) AS overdue_count
                FROM invoices
                WHERE tenant_id = :tid
            ";
            return fetchOne($sql, [':tid' => $tenantId]) ?? [
                'total_count'    => 0,
                'total_amount'   => 0,
                'draft_count'    => 0,
                'sent_count'     => 0,
                'paid_count'     => 0,
                'overdue_count'  => 0,
            ];
        } catch (PDOException $e) {
            error_log('Error fetching invoice KPIs: ' . $e->getMessage());
            return [
                'total_count'    => 0,
                'total_amount'   => 0,
                'draft_count'    => 0,
                'sent_count'     => 0,
                'paid_count'     => 0,
                'overdue_count'  => 0,
            ];
        }
    }
}

/** Invoice trends (monthly totals) */
if (!function_exists('getInvoiceTrends')) {
    function getInvoiceTrends(int $tenantId): array
    {
        $sql = "
            SELECT DATE_FORMAT(due_date, '%Y-%m') AS month,
                   COALESCE(SUM(total_amount),0) AS total_amount
            FROM invoices
            WHERE tenant_id = :tid
            GROUP BY month
            ORDER BY month
        ";
        return fetchAll($sql, [':tid' => $tenantId]);
    }
}

/** Dashboard KPIs (reuse invoice KPIs for now) */
if (!function_exists('loadDashboardKPIs')) {
    function loadDashboardKPIs(int $tenantId): array
    {
        return getInvoiceKPIs($tenantId);
    }
}

/**
 * OPTIONAL: Paginated/filtered invoice list for API usage
 * Mirrors the params your JS sends (status, search, dates, sort, page, perPage).
 * Returns ['rows'=>[], 'totalRows'=>int]
 */
if (!function_exists('getInvoicesPaginated')) {
    function getInvoicesPaginated(
        int $tenantId,
        ?string $status = null,
        ?string $search = null,
        ?string $startDate = null,
        ?string $endDate = null,
        string $sortField = 'due_date',
        string $sortDirection = 'asc',
        int $page = 1,
        int $perPage = 10
    ): array {
        // Whitelist sorting to prevent SQL injection
        $allowedSort = [
            'invoice_id'   => 'i.invoice_id',
            'title'        => 'i.title',
            'customer_name' => 'customer_name',
            'total_amount' => 'i.total_amount',
            'status'       => 'i.status',
            'due_date'     => 'i.due_date',
            'created_at'   => 'i.created_at',
        ];
        $sf = $allowedSort[$sortField] ?? 'i.due_date';
        $sd = (strtolower($sortDirection) === 'desc') ? 'DESC' : 'ASC';

        $where  = ['i.tenant_id = :tid'];
        $params = [':tid' => $tenantId];

        if (!empty($status)) {
            $where[] = 'i.status = :status';
            $params[':status'] = $status;
        }
        if (!empty($search)) {
            $where[] = '(i.title LIKE :q OR COALESCE(c.company_name, "") LIKE :q)';
            $params[':q'] = "%{$search}%";
        }
        if (!empty($startDate)) {
            $where[] = 'i.due_date >= :start';
            $params[':start'] = $startDate;
        }
        if (!empty($endDate)) {
            $where[] = 'i.due_date <= :end';
            $params[':end'] = $endDate;
        }

        $whereSql = implode(' AND ', $where);
        $offset   = max(0, ($page - 1) * $perPage);
        $limit    = max(1, $perPage);

        // Count first
        $countSql = "
            SELECT COUNT(*) AS cnt
            FROM invoices i
            LEFT JOIN customers c
              ON c.customer_id = i.customer_id
             AND c.tenant_id   = i.tenant_id
            WHERE {$whereSql}
        ";
        $row = fetchOne($countSql, $params);
        $totalRows = (int)($row['cnt'] ?? 0);

        // Page rows
        $listSql = "
            SELECT  i.invoice_id,
                    i.title,
                    i.description,
                    i.total_amount,
                    i.status,
                    i.due_date,
                    i.customer_id,
                    i.currency_id,
                    i.created_by,
                    i.created_at,
                    COALESCE(c.name, CONCAT('Customer #', i.customer_id)) AS customer_name
            FROM invoices i
            LEFT JOIN customers c
              ON c.customer_id = i.customer_id
             AND c.tenant_id   = i.tenant_id
            WHERE {$whereSql}
            ORDER BY {$sf} {$sd}
            LIMIT {$limit} OFFSET {$offset}
        ";
        $rows = fetchAll($listSql, $params);

        return ['rows' => $rows, 'totalRows' => $totalRows];
    }
}

/* ============================================================
 * TASK HELPERS
 * ============================================================ */

if (!function_exists('getTaskKPIs')) {
    function getTaskKPIs(int $tenantId): array
    {
        $sql = "
            SELECT
                COUNT(*) AS total,
                SUM(status='not_started')                         AS not_started,
                SUM(status IN ('started','in_progress'))          AS in_progress,
                SUM(status='completed')                           AS completed,
                SUM(status='cancelled')                           AS cancelled
            FROM tasks
            WHERE tenant_id = :tid
        ";
        return fetchOne($sql, [':tid' => $tenantId]) ?? [
            'total' => 0,
            'not_started' => 0,
            'in_progress' => 0,
            'completed' => 0,
            'cancelled' => 0
        ];
    }
}

/* ============================================================
 * CUSTOMER HELPERS
 * ============================================================ */

if (!function_exists('customerEmailExists')) {
    function customerEmailExists(PDO $pdo, string $email, int $tenant_id, ?int $exclude_customer_id = null): bool
    {
        $sql    = "SELECT COUNT(*) FROM customers WHERE email = ? AND tenant_id = ?";
        $params = [$email, $tenant_id];
        if ($exclude_customer_id) {
            $sql .= " AND customer_id != ?";
            $params[] = $exclude_customer_id;
        }
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->fetchColumn() > 0;
    }
}

if (!function_exists('customerPhoneExists')) {
    function customerPhoneExists(PDO $pdo, string $phone, int $tenant_id, ?int $exclude_customer_id = null): bool
    {
        $sql    = "SELECT COUNT(*) FROM customers WHERE phone_number = ? AND tenant_id = ?";
        $params = [$phone, $tenant_id];
        if ($exclude_customer_id) {
            $sql .= " AND customer_id != ?";
            $params[] = $exclude_customer_id;
        }
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->fetchColumn() > 0;
    }
}

/** Customer list (id + display name) for selects */
if (!function_exists('getCustomers')) {
    function getCustomers(): array
    {
        $tenantId = getTenantId();
        if (!$tenantId) return [];
        $sql = "
            SELECT customer_id,
                   TRIM(CONCAT(first_name, ' ', last_name,
                       CASE WHEN COALESCE(company_name,'') <> '' THEN CONCAT(' - ', company_name) ELSE '' END)
                   ) AS full_name
            FROM customers
            WHERE tenant_id = :tid
              AND (is_deleted = 0 OR is_deleted IS NULL)
            ORDER BY company_name, first_name, last_name
        ";
        return fetchAll($sql, [':tid' => $tenantId]);
    }
}

/** Currency list for selects */
if (!function_exists('getCurrencies')) {
    function getCurrencies(): array
    {
        $stmt = getDbConnection()->query("
            SELECT currency_id, code, symbol
            FROM currencies
            ORDER BY code
        ");
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
}

/* ============================================================
 * EMAIL (PHPMailer) — Example: Proposal email
 * ============================================================ */

if (!function_exists('sendProposalEmail')) {
    function sendProposalEmail(
        PDO $pdo,
        int $tenantId,
        int $proposalId,
        string $toEmail,
        string $customerName,
        string $proposalTitle,
        float $amount,
        string $currency,
        string $validUntil,
        string $template = 'default',
        int $sentBy = 0
    ): void {
        // Build body from template
        $tpl = APP_ROOT . "/includes/email_templates/{$template}_proposal.php";
        if (!is_file($tpl)) {
            $tpl = APP_ROOT . "/includes/email_templates/default_proposal.php";
        }
        ob_start();
        // expects variables in scope
        include $tpl;
        $body = (string)ob_get_clean();
        $subject = "New Proposal: {$proposalTitle}";

        // If SMTP not configured, just log and return
        if (empty(SMTP_HOST) || empty(SMTP_USER) || empty(SMTP_FROM_EMAIL)) {
            error_log('sendProposalEmail: SMTP not configured; skipping send.');
        } else {
            $mail = new PHPMailer(true);
            try {
                $mail->isSMTP();
                $mail->Host       = (string)SMTP_HOST;
                $mail->SMTPAuth   = true;
                $mail->Username   = (string)SMTP_USER;
                $mail->Password   = (string)SMTP_PASS;
                $mail->SMTPSecure = (SMTP_ENCRYPTION === 'ssl')
                    ? PHPMailer::ENCRYPTION_SMTPS
                    : PHPMailer::ENCRYPTION_STARTTLS;
                $mail->Port       = (int)SMTP_PORT;

                $mail->setFrom((string)SMTP_FROM_EMAIL, (string)(SMTP_FROM_NAME ?? APP_NAME ?? 'Ulandy'));
                $mail->addAddress($toEmail, $customerName);
                $mail->isHTML(true);
                $mail->Subject = $subject;
                $mail->Body    = $body;
                $mail->send();
            } catch (Exception $e) {
                error_log("Email Error: " . $mail->ErrorInfo);
            }
        }

        // Log attempt
        $logStmt = $pdo->prepare("
            INSERT INTO email_logs (tenant_id, proposal_id, to_email, subject, template_used, sent_by)
            VALUES (?, ?, ?, ?, ?, ?)
        ");
        $logStmt->execute([$tenantId, $proposalId, $toEmail, $subject, $template, $sentBy]);
    }
}

/* ============================================================
 * FLASH & CSRF
 * ============================================================ */

if (!function_exists('setFlash')) {
    function setFlash(string $type, string $message): void
    {
        $_SESSION['flash'] = ['type' => $type, 'message' => $message];
    }
}

if (!function_exists('getFlash')) {
    function getFlash(): ?array
    {
        if (!empty($_SESSION['flash'])) {
            $f = $_SESSION['flash'];
            unset($_SESSION['flash']);
            return $f;
        }
        return null;
    }
}

if (!function_exists('generateCsrfToken')) {
    function generateCsrfToken(): string
    {
        if (empty($_SESSION[CSRF_TOKEN_NAME])) {
            $_SESSION[CSRF_TOKEN_NAME] = bin2hex(random_bytes(32));
        }
        return $_SESSION[CSRF_TOKEN_NAME];
    }
}

if (!function_exists('validateCsrfToken')) {
    function validateCsrfToken(?string $token): bool
    {
        return isset($_SESSION[CSRF_TOKEN_NAME])
            && hash_equals($_SESSION[CSRF_TOKEN_NAME], (string)($token ?? ''));
    }
}

/* ============================================================
 * TENANT INITIALIZATION (optional helper)
 * ============================================================ */

/**
 * Ensure the session has a tenant_id; if not, create a personal workspace.
 * Useful for onboarding flows; safe no-op if tenant already present.
 */
if (!function_exists('ensureTenantContext')) {
    function ensureTenantContext(PDO $pdo): int
    {
        session_start_safe();

        if (!empty($_SESSION['user']['tenant_id'])) {
            return (int)$_SESSION['user']['tenant_id'];
        }

        $userEmail = (string)($_SESSION['user']['email'] ?? 'unknown@example.com');
        $userName  = (string)(($_SESSION['user']['first_name'] ?? '') . ' ' . ($_SESSION['user']['last_name'] ?? 'User'));
        $userName  = trim($userName) ?: 'Personal Account';

        $stmt = $pdo->prepare("
            INSERT INTO tenants (name, email, plan, created_at)
            VALUES (?, ?, 'free', NOW())
        ");
        $stmt->execute([$userName . ' (Personal Account)', $userEmail]);

        $newId = (int)$pdo->lastInsertId();
        $_SESSION['user']['tenant_id'] = $newId;

        return $newId;
    }
}
function sanitize_input($data)
{
    return htmlspecialchars(trim($data));
}
// /includes/functions.php (or a helper file like db_helper.php)

// This function prepares and executes an SQL query
function execute_query($sql, $params = [])
{
    try {
        // Establish database connection using PDO
        $pdo = getDbConnection();

        // Prepare the SQL query
        $stmt = $pdo->prepare($sql);

        // Execute the query with parameters
        $stmt->execute($params);

        // If the query is a SELECT query, fetch the result
        if (strpos(strtolower($sql), 'select') === 0) {
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        }

        // For INSERT, UPDATE, DELETE queries, return the number of affected rows
        return $stmt->rowCount();
    } catch (PDOException $e) {
        // Log the error and rethrow it
        error_log('Database Query Error: ' . $e->getMessage());
        throw new Exception("Database query failed: " . $e->getMessage());
    }
}
function get_last_insert_id()
{
    global $pdo; // Assuming you are using PDO for database connections
    return $pdo->lastInsertId();
}

// Include essential utility functions for the application

/**
 * Fetches the current user's subscription plan
 */
function getUserSubscriptionPlan($tenant_id)
{
    global $pdo;
    $stmt = $pdo->prepare("SELECT subscription_plan FROM tenants WHERE tenant_id = :tenant_id");
    $stmt->bindParam(':tenant_id', $tenant_id);
    $stmt->execute();
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    return $result['subscription_plan'];
}

/**
 * Returns available modules based on the user's subscription plan
 */
function getAvailableModules($subscription_plan)
{
    $modules = [];

    // Plan-based access to modules
    if ($subscription_plan == 'pro') {
        $modules = ['Analytics', 'Customers', 'Orders', 'Products', 'Invoices', 'Settings', 'Support'];
    } elseif ($subscription_plan == 'mid') {
        $modules = ['Analytics', 'Customers', 'Orders', 'Products', 'Invoices'];
    } else {
        $modules = ['Analytics', 'Customers', 'Orders'];
    }

    return $modules;
}
