rktbds/api.php

<?php
header('Content-Type: application/json; charset=utf-8');
header('Cache-Control: no-store');

$dbPath = __DIR__ . '/_private/pl.db';

if (!file_exists($dbPath) || filesize($dbPath) === 0) {
    echo json_encode(['error' => 'Database not initialized. Please upload a CSV file first.']);
    exit;
}

try {
    $db = new PDO('sqlite:' . $dbPath);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->exec('PRAGMA query_only = ON');
} catch (Exception $e) {
    echo json_encode(['error' => 'Database error: ' . $e->getMessage()]);
    exit;
}

$action = $_GET['action'] ?? '';

switch ($action) {

    case 'months':
        $rows = $db->query(
            'SELECT id, label, year, month_num, sort_order, is_total, is_partial
             FROM months ORDER BY sort_order'
        )->fetchAll(PDO::FETCH_ASSOC);

        foreach ($rows as &$r) {
            $r['id']        = (int)$r['id'];
            $r['year']      = $r['year'] !== null ? (int)$r['year'] : null;
            $r['month_num'] = $r['month_num'] !== null ? (int)$r['month_num'] : null;
            $r['sort_order']= (int)$r['sort_order'];
            $r['is_total']  = (int)$r['is_total'];
            $r['is_partial']= (int)$r['is_partial'];
        }
        echo json_encode($rows, JSON_UNESCAPED_UNICODE);
        break;

    case 'data':
        $idsParam = $_GET['ids'] ?? '';
        $idList   = array_unique(array_filter(array_map('intval', explode(',', $idsParam))));

        if (empty($idList)) {
            echo json_encode(['columns' => [], 'rows' => []]);
            break;
        }

        $ph = implode(',', array_fill(0, count($idList), '?'));

        // Columns in chronological order
        $colStmt = $db->prepare(
            "SELECT id, label, year, month_num, sort_order, is_total, is_partial
             FROM months WHERE id IN ($ph) ORDER BY sort_order"
        );
        $colStmt->execute($idList);
        $columns = $colStmt->fetchAll(PDO::FETCH_ASSOC);
        foreach ($columns as &$c) {
            $c['id']        = (int)$c['id'];
            $c['year']      = $c['year'] !== null ? (int)$c['year'] : null;
            $c['month_num'] = $c['month_num'] !== null ? (int)$c['month_num'] : null;
            $c['is_total']  = (int)$c['is_total'];
            $c['is_partial']= (int)$c['is_partial'];
        }

        // All account rows
        $accounts = $db->query(
            'SELECT id, name, row_type, indent_level FROM accounts ORDER BY sort_order'
        )->fetchAll(PDO::FETCH_ASSOC);

        // Values for the requested months
        $valStmt = $db->prepare(
            "SELECT account_id, month_id, value FROM pl_values WHERE month_id IN ($ph)"
        );
        $valStmt->execute($idList);

        $values = [];
        while ($v = $valStmt->fetch(PDO::FETCH_ASSOC)) {
            $values[(int)$v['account_id']][(int)$v['month_id']] = (float)$v['value'];
        }

        $rows = [];
        foreach ($accounts as $a) {
            $aid  = (int)$a['id'];
            $vals = $values[$aid] ?? [];
            // Convert integer keys to string keys for clean JSON object
            $out = new stdClass();
            foreach ($vals as $mid => $val) {
                $out->$mid = $val;
            }
            $rows[] = [
                'id'     => $aid,
                'name'   => $a['name'],
                'type'   => $a['row_type'],
                'indent' => (int)$a['indent_level'],
                'values' => $out,
            ];
        }

        echo json_encode(
            ['columns' => $columns, 'rows' => $rows],
            JSON_UNESCAPED_UNICODE | JSON_PRESERVE_ZERO_FRACTION
        );
        break;

    default:
        http_response_code(400);
        echo json_encode(['error' => 'Unknown action']);
}