<?php
// ============================================================
//  MediCore — Dynamic Reporting Engine API
//  File: api/reporting.php
// ============================================================
require_once '../includes/db.php';
require_once '../includes/auth.php';
require_once '../includes/audit.php';
require_once '../includes/reporting.php';
require_login();
session_write_close();

ensure_reporting_schema();

header('Content-Type: application/json');

// ── GET-based CSV export (needs a real navigation/download, not AJAX) ──
if (($_GET['action'] ?? '') === 'export_csv') {
    if (!can('reporting_engine')) { http_response_code(403); exit('Access denied'); }
    $id   = (int) ($_GET['id'] ?? 0);
    $from = $_GET['from'] ?? '';
    $to   = $_GET['to']   ?? '';
    $report = db_row("SELECT * FROM custom_reports WHERE id = ? AND is_active = 1", [$id]);
    if (!$report) { http_response_code(404); exit('Report not found'); }

    $def = json_decode($report['definition'], true) ?: [];
    try {
        $result = run_custom_report($report['data_source'], $def, $from ?: null, $to ?: null);
    } catch (Throwable $e) {
        http_response_code(400); exit('Report error: ' . $e->getMessage());
    }

    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . preg_replace('/[^a-z0-9]+/i', '_', $report['name']) . '.csv"');
    $out = fopen('php://output', 'w');
    fputcsv($out, array_column($result['columns'], 'label'));
    foreach ($result['rows'] as $row) {
        $line = [];
        foreach ($result['columns'] as $col) $line[] = $row[$col['key']] ?? '';
        fputcsv($out, $line);
    }
    fclose($out);
    exit;
}

$body   = json_decode(file_get_contents('php://input'), true) ?? [];
$action = trim($body['action'] ?? '');

try {
    match ($action) {
        'list'    => api_list($body),
        'get'     => api_get($body),
        'save'    => api_save($body),
        'delete'  => api_delete($body),
        'reorder' => api_reorder($body),
        'run'     => api_run($body),
        'meta'    => api_meta(),
        default   => fail('Unknown action'),
    };
} catch (Throwable $e) {
    fail($e->getMessage());
}

// ── List saved reports (viewable by anyone with reporting_engine perm) ──
function api_list(array $b): void {
    if (!can('reporting_engine')) fail('Permission denied');
    $activeOnly = $b['active_only'] ?? true;
    $where = $activeOnly ? 'WHERE is_active = 1' : '';
    $rows  = db_rows("SELECT id, name, icon, description, data_source, is_active, sort_order
                         FROM custom_reports $where
                        ORDER BY sort_order ASC, name ASC");
    ok(['data' => $rows]);
}

// ── Get one report's full definition (for the builder or the view page) ──
function api_get(array $b): void {
    if (!can('reporting_engine')) fail('Permission denied');
    $id = (int) ($b['id'] ?? 0);
    if (!$id) fail('Missing id');
    $row = db_row("SELECT * FROM custom_reports WHERE id = ?", [$id]);
    if (!$row) fail('Report not found');
    $row['definition'] = json_decode($row['definition'], true) ?: [];
    ok(['data' => $row]);
}

// ── Create / update a report definition — admin only ──────────
function api_save(array $b): void {
    require_admin();

    $id          = (int) ($b['id'] ?? 0);
    $name        = trim($b['name'] ?? '');
    $icon        = trim($b['icon'] ?? '📊') ?: '📊';
    $description = trim($b['description'] ?? '');
    $dataSource  = trim($b['data_source'] ?? '');
    $definition  = $b['definition'] ?? [];

    if (!$name) fail('Report name is required');
    $registry = reporting_registry();
    if (!isset($registry[$dataSource])) fail('Invalid data source');

    // Validate the definition against the registry before saving, so a
    // broken report can never be created — same validation path as
    // run_custom_report() itself, just executed against LIMIT 1 to
    // confirm the generated SQL is actually runnable.
    try {
        run_custom_report($dataSource, $definition, date('Y-m-01'), date('Y-m-d'));
    } catch (Throwable $e) {
        fail('Report definition is invalid: ' . $e->getMessage());
    }

    $defJson = json_encode($definition);

    if ($id) {
        $exists = db_val("SELECT COUNT(*) FROM custom_reports WHERE id = ?", [$id]);
        if (!$exists) fail('Report not found');
        db_run(
            "UPDATE custom_reports
                SET name = ?, icon = ?, description = ?, data_source = ?, definition = ?
              WHERE id = ?",
            [$name, $icon, $description, $dataSource, $defJson, $id]
        );
        log_action('update', 'reporting_engine', $id, "Updated custom report \"{$name}\"");
        ok(['id' => $id, 'msg' => 'Report updated']);
    } else {
        $maxOrder = (int) db_val("SELECT COALESCE(MAX(sort_order),0) FROM custom_reports");
        $newId = db_insert(
            "INSERT INTO custom_reports (name, icon, description, data_source, definition, sort_order, created_by)
             VALUES (?,?,?,?,?,?,?)",
            [$name, $icon, $description, $dataSource, $defJson, $maxOrder + 1, current_user_id()]
        );
        log_action('create', 'reporting_engine', $newId, "Created custom report \"{$name}\"");
        ok(['id' => $newId, 'msg' => 'Report created']);
    }
}

// ── Delete — admin only ────────────────────────────────────────
function api_delete(array $b): void {
    require_admin();
    $id = (int) ($b['id'] ?? 0);
    if (!$id) fail('Missing id');
    $row = db_row("SELECT name FROM custom_reports WHERE id = ?", [$id]);
    if (!$row) fail('Report not found');
    db_run("DELETE FROM custom_reports WHERE id = ?", [$id]);
    log_action('delete', 'reporting_engine', $id, "Deleted custom report \"{$row['name']}\"");
    ok(['msg' => 'Report deleted']);
}

// ── Reorder sidebar position — admin only ──────────────────────
function api_reorder(array $b): void {
    require_admin();
    $order = $b['order'] ?? []; // array of report ids, in desired order
    foreach ($order as $i => $id) {
        db_run("UPDATE custom_reports SET sort_order = ? WHERE id = ?", [$i, (int) $id]);
    }
    ok(['msg' => 'Order saved']);
}

// ── Run a saved report and return its rows ─────────────────────
function api_run(array $b): void {
    if (!can('reporting_engine')) fail('Permission denied');
    $id   = (int) ($b['id'] ?? 0);
    $from = $b['from'] ?? null;
    $to   = $b['to']   ?? null;
    if (!$id) fail('Missing id');

    $report = db_row("SELECT * FROM custom_reports WHERE id = ? AND is_active = 1", [$id]);
    if (!$report) fail('Report not found or inactive');

    $def = json_decode($report['definition'], true) ?: [];
    $result = run_custom_report($report['data_source'], $def, $from, $to);

    $registry = reporting_registry();
    $src = $registry[$report['data_source']];

    ok([
        'report'     => ['id' => $report['id'], 'name' => $report['name'], 'icon' => $report['icon']],
        'columns'    => $result['columns'],
        'rows'       => $result['rows'],
        'has_date'   => (bool) $src['date_field'],
        'chart_type' => $def['chart_type'] ?? 'none',
        'grouped'    => !empty($def['group_by']),
    ]);
}

// ── Registry metadata for the builder UI ───────────────────────
function api_meta(): void {
    require_admin();
    $registry = reporting_registry();
    $out = [];
    foreach ($registry as $key => $src) {
        $out[$key] = [
            'label'      => $src['label'],
            'has_date'   => (bool) $src['date_field'],
            'fields'     => $src['fields'],
        ];
    }
    ok(['data' => $out, 'agg_fns' => REPORT_AGG_FNS, 'filter_ops' => REPORT_FILT_OPS]);
}

function ok(array $extra = []): void {
    echo json_encode(array_merge(['success' => true], $extra)); exit;
}
function fail(string $msg): never {
    echo json_encode(['success' => false, 'error' => $msg]); exit;
}
