<?php
require_once "config.php";
require_once "auth.php";
requireLogin();

$selected_year = isset($_GET['year']) ? (int)$_GET['year'] : date('Y');
$selected_month = isset($_GET['month']) ? (int)$_GET['month'] : (int)date('m');
$selected_komitent = isset($_GET['komitent_id']) ? (int)$_GET['komitent_id'] : null;

$start_date = date('Y-m-01', mktime(0, 0, 0, $selected_month, 1, $selected_year));
$end_date = date('Y-m-t', mktime(0, 0, 0, $selected_month, 1, $selected_year));

$where_conditions = ["v.datum BETWEEN ? AND ?"];
$params = [$start_date, $end_date];
$types = "ss";

if (!isAdmin() && isset($_SESSION['user']['komitent_id'])) {
    $where_conditions[] = "v.komitent_id = ?";
    $params[] = $_SESSION['user']['komitent_id'];
    $types .= "i";
    $where_conditions[] = "v.status != 'Obrisano'";
} elseif (isAdmin() && $selected_komitent) {
    $where_conditions[] = "v.komitent_id = ?";
    $params[] = $selected_komitent;
    $types .= "i";
}

$where_clause = "WHERE " . implode(" AND ", $where_conditions);

if (isset($_GET['export']) && $_GET['export'] === 'excel') {
    $stmt = $conn->prepare("
        SELECT v.id, v.datum, v.vreme, v.broj_leta, v.polazna_adresa, v.dolazna_adresa,
               v.ime_klijenta, v.vrsta_vozila, v.dodaci_vozilo, v.napomena,
               k.naziv as komitent_naziv
        FROM voznje v
        LEFT JOIN komitenti k ON v.komitent_id = k.id
        $where_clause
        ORDER BY v.datum ASC, v.vreme ASC
    ");
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $result = $stmt->get_result();

    $filename = 'statistika_komitenti_' . $selected_year . '_' . str_pad($selected_month, 2, '0', STR_PAD_LEFT) . '.xls';
    header('Content-Type: application/vnd.ms-excel; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $xe = function($v) { return htmlspecialchars((string)$v, ENT_XML1, 'UTF-8'); };

    echo '<?xml version="1.0" encoding="UTF-8"?>' . "\n";
    echo '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">' . "\n";
    echo '<Worksheet ss:Name="Statistika"><Table>' . "\n";

    echo '<Row>';
    foreach (['ID','Datum','Vreme','Broj leta','Polazna adresa','Dolazna adresa','Ime klijenta','Vrsta vozila','Dodaci za vozilo','Napomena','Komitent'] as $h)
        echo '<Cell><Data ss:Type="String">' . $xe($h) . '</Data></Cell>';
    echo '</Row>' . "\n";

    while ($row = $result->fetch_assoc()) {
        echo '<Row>';
        foreach ([
            $row['id'], $row['datum'] ?? '', $row['vreme'] ?? '', $row['broj_leta'] ?? '',
            $row['polazna_adresa'] ?? '', $row['dolazna_adresa'] ?? '', $row['ime_klijenta'] ?? '',
            $row['vrsta_vozila'] ?? '', $row['dodaci_vozilo'] ?? '', $row['napomena'] ?? '',
            $row['komitent_naziv'] ?? ''
        ] as $v)
            echo '<Cell><Data ss:Type="String">' . $xe($v) . '</Data></Cell>';
        echo '</Row>' . "\n";
    }

    echo '</Table></Worksheet></Workbook>';
    exit;
}

if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    $stmt = $conn->prepare("
        SELECT v.id, v.datum, v.vreme, v.broj_leta, v.polazna_adresa, v.dolazna_adresa,
               v.ime_klijenta, v.vrsta_vozila, v.dodaci_vozilo, v.napomena,
               k.naziv as komitent_naziv
        FROM voznje v
        LEFT JOIN komitenti k ON v.komitent_id = k.id
        $where_clause
        ORDER BY v.datum ASC, v.vreme ASC
    ");
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $result = $stmt->get_result();

    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=statistika_komitenti_' . $selected_year . '_' . str_pad($selected_month, 2, '0', STR_PAD_LEFT) . '.csv');

    $output = fopen('php://output', 'w');
    fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
    fputcsv($output, ['ID','Datum','Vreme','Broj leta','Polazna adresa','Dolazna adresa','Ime klijenta','Vrsta vozila','Dodaci za vozilo','Napomena','Komitent'], ',', '"', '\\');

    while ($row = $result->fetch_assoc()) {
        fputcsv($output, [
            $row['id'], $row['datum'], $row['vreme'], $row['broj_leta'] ?? '',
            $row['polazna_adresa'], $row['dolazna_adresa'], $row['ime_klijenta'],
            $row['vrsta_vozila'], $row['dodaci_vozilo'] ?? '', $row['napomena'] ?? '',
            $row['komitent_naziv'] ?? ''
        ], ',', '"', '\\');
    }

    fclose($output);
    exit;
}

$stmt = $conn->prepare("
    SELECT 
        v.*,
        k.naziv as komitent_naziv,
        u.name as vozac_ime,
        u.telefon as vozac_telefon
    FROM voznje v
    LEFT JOIN komitenti k ON v.komitent_id = k.id
    LEFT JOIN users u ON v.vozac_id = u.id
    $where_clause
    ORDER BY v.datum ASC, v.vreme ASC
");
$stmt->bind_param($types, ...$params);
$stmt->execute();
$result = $stmt->get_result();

$months = [
    1 => 'Januar',
    2 => 'Februar',
    3 => 'Mart',
    4 => 'April',
    5 => 'Maj',
    6 => 'Jun',
    7 => 'Jul',
    8 => 'Avgust',
    9 => 'Septembar',
    10 => 'Oktobar',
    11 => 'Novembar',
    12 => 'Decembar'
];

$current_year = date('Y');
$years = range($current_year - 5, $current_year + 1);

$komitenti = null;
$selected_komitent_name = '';
if (isAdmin()) {
    $stmt_komitenti = $conn->prepare("SELECT id, naziv FROM komitenti ORDER BY naziv");
    $stmt_komitenti->execute();
    $komitenti = $stmt_komitenti->get_result();
    
    if ($selected_komitent) {
        $komitenti->data_seek(0);
        while ($komitent = $komitenti->fetch_assoc()) {
            if ($komitent['id'] == $selected_komitent) {
                $selected_komitent_name = $komitent['naziv'];
                break;
            }
        }
        $komitenti->data_seek(0);
    }
}
?>

<?php include "header.php"; ?>

<div class="container-fluid mt-4">
    <div class="card">
        <div class="card-header">
            <h3 class="card-title mb-0">
                <i class="bi bi-bar-chart me-2"></i>
                Statistika za komitente - po mesecima
            </h3>
        </div>
        <div class="card-body">
            <form method="get" class="row g-3 mb-4">
                <div class="col-md-3">
                    <label for="year" class="form-label">Godina</label>
                    <select class="form-select" id="year" name="year" required>
                        <?php foreach ($years as $year): ?>
                            <option value="<?php echo $year; ?>" <?php echo $year == $selected_year ? 'selected' : ''; ?>>
                                <?php echo $year; ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="col-md-3">
                    <label for="month" class="form-label">Mesec</label>
                    <select class="form-select" id="month" name="month" required>
                        <?php foreach ($months as $num => $name): ?>
                            <option value="<?php echo $num; ?>" <?php echo $num == $selected_month ? 'selected' : ''; ?>>
                                <?php echo $name; ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <?php if (isAdmin()): ?>
                <div class="col-md-3">
                    <label for="komitent_id" class="form-label">Komitent</label>
                    <select class="form-select" id="komitent_id" name="komitent_id">
                        <option value="">Svi komitenti</option>
                        <?php if ($komitenti): ?>
                            <?php while ($komitent = $komitenti->fetch_assoc()): ?>
                                <option value="<?php echo $komitent['id']; ?>" <?php echo $komitent['id'] == $selected_komitent ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($komitent['naziv']); ?>
                                </option>
                            <?php endwhile; ?>
                        <?php endif; ?>
                    </select>
                </div>
                <?php endif; ?>
                <div class="col-md-<?php echo isAdmin() ? '3' : '6'; ?> d-flex align-items-end gap-2">
                    <button type="submit" class="btn btn-primary">
                        <i class="bi bi-search me-2"></i>Prikaži
                    </button>
                    <a href="?year=<?php echo $selected_year; ?>&month=<?php echo $selected_month; ?><?php echo isAdmin() && $selected_komitent ? '&komitent_id=' . $selected_komitent : ''; ?>&export=csv" class="btn btn-success">
                        <i class="bi bi-download me-2"></i>Export CSV
                    </a>
                    <a href="?year=<?php echo $selected_year; ?>&month=<?php echo $selected_month; ?><?php echo isAdmin() && $selected_komitent ? '&komitent_id=' . $selected_komitent : ''; ?>&export=excel" class="btn btn-outline-success">
                        <i class="bi bi-file-earmark-excel me-2"></i>Export Excel
                    </a>
                </div>
            </form>

            <div class="alert alert-info">
                <i class="bi bi-info-circle me-2"></i>
                Prikazuju se vožnje za <strong><?php echo $months[$selected_month]; ?> <?php echo $selected_year; ?></strong>
                <?php if (isAdmin() && $selected_komitent && $selected_komitent_name): ?>
                    - <strong><?php echo htmlspecialchars($selected_komitent_name); ?></strong>
                <?php endif; ?>
            </div>

            <?php 
            $rows = [];
            $total_price = 0;
            while ($row = $result->fetch_assoc()) {
                $rows[] = $row;
                $total_price += floatval($row['cena'] ?? 0);
            }
            
            if (empty($rows)): ?>
                <div class="alert alert-warning">
                    <i class="bi bi-exclamation-triangle me-2"></i>
                    Nema vožnji za izabrani mesec.
                </div>
            <?php else: ?>
                <div class="table-responsive">
                    <table class="table table-bordered table-hover">
                        <thead>
                            <tr>
                                <th>ID</th>
                                <th>Datum</th>
                                <th>Vreme</th>
                                <th>Broj leta</th>
                                <th>Polazna adresa</th>
                                <th>Dolazna adresa</th>
                                <th>Ime klijenta</th>
                                <th>Vrsta vozila</th>
                                <th>Dodaci za vozilo</th>
                                <th>Napomena</th>
                                <th>Komitent</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($rows as $row): ?>
                                <tr>
                                    <td><?php echo $row['id']; ?></td>
                                    <td><?php echo date('d.m.Y', strtotime($row['datum'])); ?></td>
                                    <td><?php echo date('H:i', strtotime($row['vreme'])); ?></td>
                                    <td><?php echo !empty($row['broj_leta']) ? htmlspecialchars($row['broj_leta']) : '-'; ?></td>
                                    <td><?php echo htmlspecialchars($row['polazna_adresa'] ?? ''); ?></td>
                                    <td><?php echo htmlspecialchars($row['dolazna_adresa'] ?? ''); ?></td>
                                    <td><?php echo htmlspecialchars($row['ime_klijenta'] ?? ''); ?></td>
                                    <td><?php echo htmlspecialchars($row['vrsta_vozila'] ?? ''); ?></td>
                                    <td><?php echo htmlspecialchars($row['dodaci_vozilo'] ?? '-'); ?></td>
                                    <td><?php echo htmlspecialchars($row['napomena'] ?? '-'); ?></td>
                                    <td><?php echo htmlspecialchars($row['komitent_naziv'] ?? '-'); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                        <tfoot>
                            <tr class="table-info">
                                <td colspan="10" class="text-end"><strong>Ukupno:</strong></td>
                                <td><strong><?php echo count($rows); ?> vožnji</strong></td>
                            </tr>
                        </tfoot>
                    </table>
                </div>
            <?php endif; ?>
        </div>
    </div>
</div>

<?php include "footer.php"; ?>

