<?php
/**
 * Databank – shared query layer for dashboard and API.
 * All functions accept mysqli $conn and optional date range; return arrays (no PII).
 * Used by databank_dashboard.php and databank/api/index.php.
 */

if (!defined('DATABANK_QUERIES_LOADED')) {
    define('DATABANK_QUERIES_LOADED', 1);
}

/**
 * Validate and normalize date range. Returns [start_date, end_date] or null on invalid.
 * Max range 366 days.
 */
function databank_validate_date_range($start_date, $end_date, $default_start = null, $default_end = null) {
    if ($default_start === null) $default_start = date('Y-m-01');
    if ($default_end === null)   $default_end   = date('Y-m-t');
    if (empty($start_date) || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $start_date)) $start_date = $default_start;
    if (empty($end_date)   || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $end_date))   $end_date   = $default_end;
    if ($end_date < $start_date) return null;
    $days = (strtotime($end_date) - strtotime($start_date)) / 86400;
    if ($days > 366) return null;
    return [$start_date, $end_date];
}

/**
 * Total active patient count. Optional: count of new registrations in date range.
 */
function databank_patients_count($conn, $start_date = null, $end_date = null) {
    $total_sql = "SELECT COUNT(*) AS total FROM patients WHERE is_active = 1";
    $total = (int) $conn->query($total_sql)->fetch_assoc()['total'];

    $registrations_in_range = null;
    if ($start_date && $end_date) {
        $range = databank_validate_date_range($start_date, $end_date);
        if ($range) {
            list($s, $e) = $range;
            $stmt = $conn->prepare("SELECT COUNT(*) AS cnt FROM patients WHERE is_active = 1 AND DATE(created_at) BETWEEN ? AND ?");
            $stmt->bind_param("ss", $s, $e);
            $stmt->execute();
            $registrations_in_range = (int) $stmt->get_result()->fetch_assoc()['cnt'];
            $stmt->close();
        }
    }

    return [
        'total_active' => $total,
        'registrations_in_range' => $registrations_in_range,
    ];
}

/**
 * Patient counts by registration_type (CASH, INSURANCE, WALK_IN, etc.).
 */
function databank_patients_by_registration_type($conn, $start_date = null, $end_date = null) {
    $sql = "SELECT registration_type, COUNT(*) AS patient_count FROM patients WHERE is_active = 1";
    $params = [];
    $types = '';
    if ($start_date && $end_date) {
        $range = databank_validate_date_range($start_date, $end_date);
        if ($range) {
            list($s, $e) = $range;
            $sql .= " AND DATE(created_at) BETWEEN ? AND ?";
            $params = [$s, $e];
            $types = 'ss';
        }
    }
    $sql .= " GROUP BY registration_type ORDER BY patient_count DESC";

    $out = [];
    if ($params) {
        $stmt = $conn->prepare($sql);
        $stmt->bind_param($types, ...$params);
        $stmt->execute();
        $res = $stmt->get_result();
    } else {
        $res = $conn->query($sql);
    }
    if ($res) {
        while ($row = $res->fetch_assoc()) {
            $out[] = [
                'registration_type' => $row['registration_type'],
                'label' => ucfirst(str_replace('_', ' ', strtolower($row['registration_type'] ?? ''))),
                'patient_count' => (int) $row['patient_count'],
            ];
        }
        if ($params) $stmt->close();
    }
    return $out;
}

/**
 * Aggregated demographics: age bands and gender (no PII).
 */
function databank_patients_demographics($conn) {
    $sql = "SELECT 
                u.gender,
                CASE 
                    WHEN TIMESTAMPDIFF(YEAR, u.date_of_birth, CURDATE()) < 18 THEN '0-17'
                    WHEN TIMESTAMPDIFF(YEAR, u.date_of_birth, CURDATE()) BETWEEN 18 AND 34 THEN '18-34'
                    WHEN TIMESTAMPDIFF(YEAR, u.date_of_birth, CURDATE()) BETWEEN 35 AND 49 THEN '35-49'
                    WHEN TIMESTAMPDIFF(YEAR, u.date_of_birth, CURDATE()) BETWEEN 50 AND 64 THEN '50-64'
                    ELSE '65+'
                END AS age_band,
                COUNT(*) AS patient_count
            FROM patients p
            INNER JOIN users u ON p.user_id = u.user_id
            WHERE p.is_active = 1 AND u.date_of_birth IS NOT NULL
            GROUP BY u.gender, age_band
            ORDER BY u.gender, age_band";
    $res = $conn->query($sql);
    $out = ['by_gender' => [], 'by_age_band' => [], 'rows' => []];
    $gender_totals = [];
    $age_totals = [];
    if ($res) {
        while ($row = $res->fetch_assoc()) {
            $g = $row['gender'] ?? 'Unknown';
            $a = $row['age_band'] ?? 'Unknown';
            $c = (int) $row['patient_count'];
            $gender_totals[$g] = ($gender_totals[$g] ?? 0) + $c;
            $age_totals[$a] = ($age_totals[$a] ?? 0) + $c;
            $out['rows'][] = ['gender' => $g, 'age_band' => $a, 'patient_count' => $c];
        }
    }
    foreach ($gender_totals as $g => $c) $out['by_gender'][] = ['gender' => $g, 'patient_count' => $c];
    foreach ($age_totals as $a => $c) $out['by_age_band'][] = ['age_band' => $a, 'patient_count' => $c];
    return $out;
}

/**
 * Payment methods: totals and patient count per method (completed payments only).
 */
function databank_payments_methods($conn, $start_date, $end_date) {
    $range = databank_validate_date_range($start_date, $end_date);
    if (!$range) return [];
    list($s, $e) = $range;

    $has_status = false;
    $chk = @$conn->query("SHOW COLUMNS FROM payments LIKE 'status'");
    if ($chk && $chk->num_rows > 0) $has_status = true;

    $sql = "SELECT payment_method, COUNT(*) AS transaction_count, COUNT(DISTINCT patient_id) AS patient_count, COALESCE(SUM(amount), 0) AS total
            FROM payments WHERE DATE(payment_date) BETWEEN ? AND ? ";
    if ($has_status) $sql .= " AND (status = 'COMPLETED' OR status IS NULL) ";
    $sql .= " GROUP BY payment_method ORDER BY total DESC";

    $stmt = $conn->prepare($sql);
    if (!$stmt) return [];
    $stmt->bind_param("ss", $s, $e);
    $stmt->execute();
    $res = $stmt->get_result();
    $out = [];
    while ($row = $res->fetch_assoc()) {
        $out[] = [
            'payment_method' => $row['payment_method'],
            'label' => ucfirst(str_replace('_', ' ', $row['payment_method'] ?? '')),
            'transaction_count' => (int) $row['transaction_count'],
            'patient_count' => (int) $row['patient_count'],
            'total' => (float) $row['total'],
        ];
    }
    $stmt->close();
    return $out;
}

/**
 * Total revenue in date range; optional breakdown by payment method.
 */
function databank_payments_revenue($conn, $start_date, $end_date) {
    $methods = databank_payments_methods($conn, $start_date, $end_date);
    $total = 0;
    foreach ($methods as $m) $total += $m['total'];
    return [
        'total_revenue' => $total,
        'by_method' => $methods,
    ];
}

/**
 * Insurance provider breakdown: patient count (and visit count in range) per provider.
 */
function databank_insurance_by_provider($conn, $start_date = null, $end_date = null) {
    $sql = "SELECT ip.provider_id, ip.provider_name, ip.provider_code,
                   COUNT(DISTINCT p.patient_id) AS patient_count
            FROM insurance_providers ip
            LEFT JOIN patients p ON p.insurance_provider_id = ip.provider_id AND p.is_active = 1
            GROUP BY ip.provider_id, ip.provider_name, ip.provider_code
            ORDER BY patient_count DESC";
    $res = $conn->query($sql);
    $out = [];
    if ($res) {
        while ($row = $res->fetch_assoc()) {
            $out[] = [
                'provider_id' => (int) $row['provider_id'],
                'provider_name' => $row['provider_name'],
                'provider_code' => $row['provider_code'] ?? '',
                'patient_count' => (int) $row['patient_count'],
            ];
        }
    }
    if ($start_date && $end_date) {
        $range = databank_validate_date_range($start_date, $end_date);
        if ($range) {
            list($s, $e) = $range;
            $visit_sql = "SELECT p.insurance_provider_id, COUNT(*) AS visit_count
                          FROM patient_visits pv
                          INNER JOIN patients p ON pv.patient_id = p.patient_id
                          WHERE DATE(pv.registration_date) BETWEEN ? AND ? AND p.insurance_provider_id IS NOT NULL
                          GROUP BY p.insurance_provider_id";
            $stmt = $conn->prepare($visit_sql);
            $stmt->bind_param("ss", $s, $e);
            $stmt->execute();
            $vres = $stmt->get_result();
            $visit_map = [];
            while ($v = $vres->fetch_assoc()) $visit_map[(int)$v['insurance_provider_id']] = (int)$v['visit_count'];
            $stmt->close();
            foreach ($out as &$item) {
                $item['visit_count_in_range'] = $visit_map[$item['provider_id']] ?? 0;
            }
            unset($item);
        }
    }
    return $out;
}

/**
 * Visit counts in date range; optional group by registration_type.
 */
function databank_visits_count($conn, $start_date, $end_date) {
    $range = databank_validate_date_range($start_date, $end_date);
    if (!$range) return ['total' => 0, 'by_type' => []];
    list($s, $e) = $range;

    $stmt = $conn->prepare("SELECT COUNT(*) AS total FROM patient_visits WHERE DATE(registration_date) BETWEEN ? AND ?");
    $stmt->bind_param("ss", $s, $e);
    $stmt->execute();
    $total = (int) $stmt->get_result()->fetch_assoc()['total'];
    $stmt->close();

    $stmt = $conn->prepare("SELECT registration_type, COUNT(*) AS visit_count, COUNT(DISTINCT patient_id) AS patient_count
                            FROM patient_visits WHERE DATE(registration_date) BETWEEN ? AND ?
                            GROUP BY registration_type ORDER BY visit_count DESC");
    $stmt->bind_param("ss", $s, $e);
    $stmt->execute();
    $res = $stmt->get_result();
    $by_type = [];
    while ($row = $res->fetch_assoc()) {
        $by_type[] = [
            'registration_type' => $row['registration_type'],
            'label' => ucfirst(str_replace('_', ' ', strtolower($row['registration_type'] ?? ''))),
            'visit_count' => (int) $row['visit_count'],
            'patient_count' => (int) $row['patient_count'],
        ];
    }
    $stmt->close();

    return ['total' => $total, 'by_type' => $by_type];
}
