<?php
/**
 * Выгрузка базы VK-лидгена: CSV (для Excel) и XLSX.
 * ?t=contacts|other_contacts|exclude|blacklist  &fmt=csv|xlsx  &s=поиск  &ids=1,2,3
 */
declare(strict_types=1);
ini_set('session.gc_maxlifetime', '7776000');
ini_set('session.cookie_lifetime', '7776000');
session_set_cookie_params(['lifetime' => 7776000, 'path' => '/', 'secure' => true, 'httponly' => true, 'samesite' => 'Lax']);
session_start();
if (empty($_SESSION['vk_ok'])) { http_response_code(403); exit('нет доступа'); }

$DB_PATH = '/opt/vk-leadgen/data/vk.db';
$LABELS = [
    'contacts' => ['id' => 'ID', 'fio' => 'Имя', 'base' => 'База', 'url' => 'Ссыль', 'processed' => 'Обработан', 'how' => 'Как обработан',
        'friend_status' => 'Friend_status', 'activity' => 'Деятельность', 'last_run' => 'Последнее выполнение',
        'comment' => 'Коммент', 'comment_post' => 'Пост с комментом', 'wrote_dm' => 'Написал в ЛС',
        'msg_history' => 'История сообщений', 'reply' => 'Ответ', 'sale' => 'Продажа', 'city' => 'Город',
        'source' => 'Источник', 'last_start' => 'Старт последнего выполнения',
        'last_workflow' => 'Последний выполненный Workflow', 'note' => 'Заметка',
        'created_at' => 'Создан', 'updated_at' => 'Обновлён'],
    'other_contacts' => ['id' => 'ID', 'fio' => 'Имя', 'base' => 'База', 'url' => 'Ссыль', 'processed' => 'Обработан', 'how' => 'Как обработан',
        'friend_status' => 'Friend_status', 'activity' => 'Деятельность', 'last_run' => 'Последнее выполнение',
        'city' => 'Город', 'source' => 'Источник', 'note' => 'Заметка', 'created_at' => 'Создан', 'updated_at' => 'Обновлён'],
    'exclude' => ['id' => 'ID', 'url' => 'Ссыль', 'reason' => 'Причина исключения', 'created_at' => 'Создан'],
    'blacklist' => ['id' => 'ID', 'url' => 'ВК', 'note' => 'Заметка', 'created_at' => 'Создан'],
];
$t = $_GET['t'] ?? 'contacts';
if (!isset($LABELS[$t])) { http_response_code(400); exit('неизвестная таблица'); }
$fmt = ($_GET['fmt'] ?? 'csv') === 'xlsx' ? 'xlsx' : 'csv';

$pdo = new PDO('sqlite:' . $DB_PATH, null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
$pdo->exec('PRAGMA busy_timeout=30000');

$where = []; $params = [];
$ids = trim((string)($_GET['ids'] ?? ''));
if ($ids !== '') {
    $list = array_filter(array_map('intval', explode(',', $ids)));
    if ($list) { $where[] = 'id IN (' . implode(',', array_fill(0, count($list), '?')) . ')'; $params = $list; }
}
$s = trim((string)($_GET['s'] ?? ''));
if ($s !== '') { $where[] = "(CAST(id AS TEXT) LIKE ? OR COALESCE(fio,'') LIKE ? OR COALESCE(url,'') LIKE ?)"; $params = array_merge($params, ["%$s%", "%$s%", "%$s%"]); }
$w = $where ? ('WHERE ' . implode(' AND ', $where)) : '';
$st = $pdo->prepare("SELECT * FROM $t $w ORDER BY id");
$st->execute($params);
$rows = $st->fetchAll();
$cols = array_keys($LABELS[$t]);
$head = array_values($LABELS[$t]);

function cell_out($v): string {
    return (string)($v ?? '');
}

if ($fmt === 'csv') {
    $fname = "vk-leadgen-$t-" . date('Y-m-d') . '.csv';
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $fname . '"');
    echo "\xEF\xBB\xBF";  // BOM — Excel открывает русский текст корректно
    $out = fopen('php://output', 'w');
    fputcsv($out, $head, ';');
    foreach ($rows as $r) {
        $line = [];
        foreach ($cols as $c) $line[] = cell_out($r[$c] ?? '');
        fputcsv($out, $line, ';');
    }
    fclose($out);
    exit;
}

/* ── XLSX без библиотек (ZipArchive + ручной XML) ── */
function xesc($s): string { return htmlspecialchars((string)$s, ENT_QUOTES | ENT_XML1, 'UTF-8'); }
function xcell(string $ref, $val, int $style = 0): string {
    $t = 'inlineStr';
    return '<c r="' . $ref . '" s="' . $style . '" t="' . $t . '"><is><t xml:space="preserve">' . xesc($val) . '</t></is></c>';
}
function col_name(int $i): string {
    $s = '';
    while ($i > 0) { $i--; $s = chr(65 + ($i % 26)) . $s; $i = intdiv($i, 26); }
    return $s;
}

$sheetRows = '';
$sheetRows .= '<row r="1">';
foreach ($head as $i => $h) $sheetRows .= xcell(col_name($i + 1) . '1', $h, 2);
$sheetRows .= '</row>';
$rn = 1;
foreach ($rows as $r) {
    $rn++;
    $sheetRows .= '<row r="' . $rn . '">';
    foreach ($cols as $i => $c) $sheetRows .= xcell(col_name($i + 1) . $rn, cell_out($r[$c] ?? ''));
    $sheetRows .= '</row>';
}

$colsXml = '';
foreach ($cols as $i => $c) $colsXml .= '<col min="' . ($i + 1) . '" max="' . ($i + 1) . '" width="' . ($c === 'msg_history' || $c === 'comment' ? 60 : 22) . '" customWidth="1"/>';

$sheet = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
    . '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
    . '<cols>' . $colsXml . '</cols><sheetData>' . $sheetRows . '</sheetData></worksheet>';

$contentTypes = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
    . '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'
    . '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'
    . '<Default Extension="xml" ContentType="application/xml"/>'
    . '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>'
    . '<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'
    . '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>'
    . '</Types>';
$rels = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
    . '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
    . '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>'
    . '</Relationships>';
$workbook = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
    . '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" '
    . 'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
    . '<sheets><sheet name="' . xesc(mb_substr($t, 0, 28)) . '" sheetId="1" r:id="rId1"/></sheets></workbook>';
$wbRels = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
    . '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
    . '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>'
    . '<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>'
    . '</Relationships>';
$styles = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
    . '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
    . '<fonts count="2"><font><sz val="11"/><name val="Calibri"/></font>'
    . '<font><b/><sz val="11"/><name val="Calibri"/></font></fonts>'
    . '<fills count="2"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill></fills>'
    . '<borders count="2"><border/><border><left style="thin"/><right style="thin"/><top style="thin"/><bottom style="thin"/></border></borders>'
    . '<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>'
    . '<cellXfs count="3">'
    . '<xf numFmtId="0" fontId="0" fillId="0" borderId="1" xfId="0" applyBorder="1"/>'
    . '<xf numFmtId="0" fontId="0" fillId="0" borderId="1" xfId="0" applyBorder="1"/>'
    . '<xf numFmtId="0" fontId="1" fillId="0" borderId="1" xfId="0" applyBorder="1" applyFont="1"/>'
    . '</cellXfs><cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles></styleSheet>';

$tmp = tempnam(sys_get_temp_dir(), 'xlsx');
$zip = new ZipArchive();
$zip->open($tmp, ZipArchive::OVERWRITE);
$zip->addFromString('[Content_Types].xml', $contentTypes);
$zip->addFromString('_rels/.rels', $rels);
$zip->addFromString('xl/workbook.xml', $workbook);
$zip->addFromString('xl/_rels/workbook.xml.rels', $wbRels);
$zip->addFromString('xl/worksheets/sheet1.xml', $sheet);
$zip->addFromString('xl/styles.xml', $styles);
$zip->close();

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="vk-leadgen-' . $t . '-' . date('Y-m-d') . '.xlsx"');
header('Content-Length: ' . filesize($tmp));
readfile($tmp);
@unlink($tmp);