// ── AMBIL DAFTAR FILE (tanpa file_data agar ringan) ──────────────────
public function file()
{
    $tahun = preg_replace('/[^0-9]/', '', $this->input->get('tahun') ?: date('Y'));
    $pokja = preg_replace('/[^0-9]/', '', $this->input->get('pokja') ?: '1');

    $this->load->database();

    // Jangan SELECT file_data di sini — berat!
    $query = $this->db
        ->select('id, file_name, file_size, upload_date')
        ->from('dokumen_files')
        ->where('tahun', (int)$tahun)
        ->where('pokja', (int)$pokja)
        ->order_by('file_name', 'ASC')
        ->get();

    $files = [];
    foreach ($query->result_array() as $row) {
        $baseUrl = base_url('dashboard/stream_pdf/' . $row['id']);
        $files[] = [
            'file_name'   => $row['file_name'],
            // URL view & download arahkan ke endpoint stream
            'file_path'   => $baseUrl,
            'file_size'   => $row['file_size'],
            'upload_date' => date('d M Y', strtotime($row['upload_date'])),
        ];
    }

    header('Content-Type: application/json');
    echo json_encode($files);
}


// ── STREAM / SERVE FILE PDF ──────────────────────────────────────────
public function stream_pdf($id)
{
    $id = (int)$id;
    if (!$id) show_404();

    $this->load->database();

    $row = $this->db
        ->select('file_name, file_mime, file_data')
        ->where('id', $id)
        ->get('dokumen_files')
        ->row_array();

    if (!$row) show_404();

    // PostgreSQL mengembalikan BYTEA sebagai hex string dengan prefix \x
    $rawData = $row['file_data'];
    if (strpos($rawData, '\\x') === 0) {
        $rawData = hex2bin(substr($rawData, 2));
    }

    $mime     = $row['file_mime'] ?: 'application/pdf';
    $fileName = $row['file_name'];
    $inline   = ($this->input->get('download') ? 'attachment' : 'inline');

    header('Content-Type: ' . $mime);
    header('Content-Disposition: ' . $inline . '; filename="' . $fileName . '"');
    header('Content-Length: ' . strlen($rawData));
    header('Cache-Control: private, max-age=3600');
    echo $rawData;
    exit;
}


// ── UPLOAD ───────────────────────────────────────────────────────────
public function upload_pdf()
{
    $tahun = preg_replace('/[^0-9]/', '', $this->input->post('tahun'));
    $pokja = preg_replace('/[^0-9]/', '', $this->input->post('pokja'));

    if (!$tahun || !$pokja) {
        echo json_encode(['status' => 'error', 'message' => 'Parameter tahun/pokja tidak valid.']);
        return;
    }

    // Validasi file
    if (empty($_FILES['file']['tmp_name'])) {
        echo json_encode(['status' => 'error', 'message' => 'Tidak ada file yang diunggah.']);
        return;
    }

    $file     = $_FILES['file'];
    $tmpPath  = $file['tmp_name'];
    $origName = basename($file['name']);
    $fileSize = $file['size'];
    $mimeType = mime_content_type($tmpPath);

    // Validasi tipe
    if ($mimeType !== 'application/pdf') {
        echo json_encode(['status' => 'error', 'message' => 'Hanya file PDF yang diizinkan.']);
        return;
    }

    // Validasi ukuran (2 MB)
    if ($fileSize > 2 * 1024 * 1024) {
        echo json_encode(['status' => 'error', 'message' => 'Ukuran file melebihi batas 2 MB.']);
        return;
    }

    $sizeFmt  = $fileSize < 1048576
        ? round($fileSize / 1024) . ' KB'
        : round($fileSize / 1048576, 1) . ' MB';

    // Baca isi file sebagai binary
    $fileData = file_get_contents($tmpPath);
    if ($fileData === false) {
        echo json_encode(['status' => 'error', 'message' => 'Gagal membaca file.']);
        return;
    }

    $this->load->database();

    // Cek duplikat
    $exists = $this->db
        ->where('tahun', (int)$tahun)
        ->where('pokja', (int)$pokja)
        ->where('file_name', $origName)
        ->count_all_results('dokumen_files');

    if ($exists > 0) {
        echo json_encode(['status' => 'error', 'message' => 'File dengan nama yang sama sudah ada.']);
        return;
    }

    // Encode binary ke hex untuk PostgreSQL BYTEA
    // Gunakan query langsung agar BYTEA di-handle dengan benar
    $hexData = bin2hex($fileData);

    $sql = "INSERT INTO dokumen_files
                (tahun, pokja, file_name, file_mime, file_size, file_data, uploaded_by)
            VALUES (?, ?, ?, ?, ?, decode(?, 'hex'), ?)";

    $this->db->query($sql, [
        (int)$tahun,
        (int)$pokja,
        $origName,
        $mimeType,
        $sizeFmt,
        $hexData,
        $this->session->userdata('nm_user'),
    ]);

    if ($this->db->affected_rows() > 0) {
        echo json_encode(['status' => 'success']);
    } else {
        echo json_encode(['status' => 'error', 'message' => 'Gagal menyimpan ke database.']);
    }
}


// ── HITUNG FILE PER POKJA ────────────────────────────────────────────
public function file_counts()
{
    $tahun = preg_replace('/[^0-9]/', '', $this->input->get('tahun') ?: date('Y'));

    $this->load->database();

    $query = $this->db
        ->select('pokja, COUNT(*) as jumlah')
        ->from('dokumen_files')
        ->where('tahun', (int)$tahun)
        ->group_by('pokja')
        ->get();

    $counts = [];
    foreach ($query->result_array() as $row) {
        $counts[(int)$row['pokja']] = (int)$row['jumlah'];
    }

    for ($i = 1; $i <= 20; $i++) {
        if (!isset($counts[$i])) $counts[$i] = 0;
    }

    header('Content-Type: application/json');
    echo json_encode($counts);
}


// ── HAPUS ─────────────────────────────────────────────────────────────
public function hapus1()
{
    $tahun    = preg_replace('/[^0-9]/', '', $this->input->post('tahun'));
    $pokja    = preg_replace('/[^0-9]/', '', $this->input->post('pokja'));
    $fileName = basename($this->input->post('file'));

    if (!$tahun || !$pokja || !$fileName) {
        echo json_encode(['status' => 'error', 'message' => 'Parameter tidak lengkap.']);
        return;
    }

    $this->load->database();

    $deleted = $this->db
        ->where('tahun', (int)$tahun)
        ->where('pokja', (int)$pokja)
        ->where('file_name', $fileName)
        ->delete('dokumen_files');

    if ($deleted) {
        echo json_encode(['status' => 'success']);
    } else {
        echo json_encode(['status' => 'error', 'message' => 'File tidak ditemukan.']);
    }
}



/// VIEW DITAMBAHKAN 

// Ganti bagian render file row, tambahkan ?download=1 pada link download
html += `<div class="file-row">
    <div class="file-thumb"><i class="fas fa-file-pdf"></i></div>
    <div class="file-info">
        <div class="file-name">${fileName}</div>
        <div class="file-meta">${f.file_size} ${f.upload_date ? '&bull; ' + f.upload_date : ''}</div>
    </div>
    <div class="file-actions">
        <a href="${f.file_path}" target="_blank" class="action-btn view" title="Lihat">
            <i class="fas fa-eye"></i>
        </a>
        <a href="${f.file_path}?download=1" class="action-btn download" title="Unduh">
            <i class="fas fa-download"></i>
        </a>
        ${delBtn}
    </div>
</div>`;


<!-- CREATE TABLE  -->
CREATE TABLE dokumen_files (
    id          SERIAL PRIMARY KEY,
    tahun       SMALLINT      NOT NULL,
    pokja       SMALLINT      NOT NULL,
    file_name   VARCHAR(255)  NOT NULL,
    file_mime   VARCHAR(100)  DEFAULT 'application/pdf',
    file_size   VARCHAR(20),
    file_data   BYTEA         NOT NULL,        -- ← file PDF disimpan di sini
    upload_date TIMESTAMP     NOT NULL DEFAULT NOW(),
    uploaded_by VARCHAR(100),

    CONSTRAINT uq_dokumen UNIQUE (tahun, pokja, file_name)
);

CREATE INDEX idx_dokumen_tahun_pokja ON dokumen_files (tahun, pokja);