<?php
session_start();
require_once '../config.php';

if (!isset($_SESSION['user_id'])) {
    header('Location: ../login.php');
    exit;
}

$user_id = (int)($_SESSION['user_id']);
$role    = $_SESSION['role'] ?? 'user';

if (!function_exists('is_valid_date')) {
    function is_valid_date($d) {
        return is_string($d) && preg_match('/^\d{4}-\d{2}-\d{2}$/', $d);
    }
}

$from = trim($_GET['from'] ?? '');
$to   = trim($_GET['to'] ?? '');

$where  = "b.payment_status = 'unpaid'";
$params = [];
$types  = "";

if ($from !== '' && $to !== '' && is_valid_date($from) && is_valid_date($to)) {
    if ($from > $to) {
        $tmp = $from;
        $from = $to;
        $to   = $tmp;
    }
    $where .= " AND DATE(b.created_at) BETWEEN ? AND ?";
    $params[] = $from;
    $params[] = $to;
    $types .= "ss";
}

if ($role === 'sales_executive') {
    $where .= " AND b.created_by = ?";
    $params[] = $user_id;
    $types .= "i";
}

$sql = "
    SELECT
        b.id,
        b.created_at,
        b.phone,
        b.customer_name,
        b.total_amount,
        b.unpaid_due_date,
        u.name AS created_by_name,
        u.role AS created_by_role,
        COALESCE((
            SELECT COUNT(*)
            FROM bill_items bi
            WHERE bi.bill_id = b.id
        ), 0) AS items_count
    FROM bills b
    LEFT JOIN users u ON b.created_by = u.id
    WHERE $where
    ORDER BY b.id DESC
";

$stmt = $conn->prepare($sql);
if (!$stmt) {
    die("Query prepare failed: " . $conn->error);
}

if (!empty($params)) {
    $stmt->bind_param($types, ...$params);
}

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

$filename = 'unpaid_bills_' . date('Y-m-d_H-i-s') . '.csv';

header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="'.$filename.'"');

$out = fopen('php://output', 'w');
fprintf($out, chr(0xEF).chr(0xBB).chr(0xBF)); // UTF-8 BOM for Excel

fputcsv($out, ['Date', 'Bill No', 'Phone', 'Customer', 'Items', 'Total', 'Due Date', 'Created By', 'Role']);

while ($r = $result->fetch_assoc()) {
    fputcsv($out, [
        !empty($r['created_at']) ? date('d-m-Y', strtotime($r['created_at'])) : '',
        'BILL-' . (int)$r['id'],
        $r['phone'] ?? '',
        $r['customer_name'] ?? '',
        (int)($r['items_count'] ?? 0),
        number_format((float)($r['total_amount'] ?? 0), 2),
        !empty($r['unpaid_due_date']) ? date('d-m-Y', strtotime($r['unpaid_due_date'])) : '',
        $r['created_by_name'] ?? '',
        $r['created_by_role'] ?? '',
    ]);
}

fclose($out);
exit;