<?php

declare(strict_types=1);

/**
 * Owner/Administrator reporting helpers for DentalCare.
 * All functions are read-only and defensive: if an optional module/table is not
 * installed yet, the related report returns an empty data set instead of
 * breaking the Reports page.
 */

function owner_report_branch_clause(string $alias, int $branchId): string
{
    if ($branchId > 0) {
        return ' AND ' . $alias . '.branch_id=' . $branchId;
    }
    return superuser_primary_workspace() ? '' : ' AND 1=0';
}

function owner_report_safe_all(PDO $pdo, string $sql): array
{
    try {
        return $pdo->query($sql)->fetchAll();
    } catch (Throwable $exception) {
        error_log('Owner report query skipped: ' . $exception->getMessage());
        return [];
    }
}

function owner_report_safe_scalar(PDO $pdo, string $sql, float $default = 0.0): float
{
    try {
        $value = $pdo->query($sql)->fetchColumn();
        return $value === false || $value === null ? $default : (float) $value;
    } catch (Throwable $exception) {
        error_log('Owner report scalar skipped: ' . $exception->getMessage());
        return $default;
    }
}

function owner_report_monthly_collections(PDO $pdo, int $branchId, int $months = 6): array
{
    if (!schema_table_exists(table('payments')) || !schema_table_exists(table('invoices'))) return [];
    $branch = owner_report_branch_clause('i', $branchId);
    $grossRows = owner_report_safe_all($pdo,
        'SELECT DATE_FORMAT(py.created_at,"%Y-%m") month_key,COUNT(*) payments,COALESCE(SUM(py.amount),0) gross '
        . 'FROM `' . table('payments') . '` py JOIN `' . table('invoices') . '` i ON i.id=py.invoice_id '
        . 'WHERE py.created_at>=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL ' . max(1, $months - 1) . ' MONTH),"%Y-%m-01")' . $branch
        . ' GROUP BY DATE_FORMAT(py.created_at,"%Y-%m") ORDER BY month_key DESC LIMIT ' . max(1, $months)
    );
    $map = [];
    foreach ($grossRows as $row) {
        $key = (string) $row['month_key'];
        $map[$key] = [
            'month_key' => $key,
            'label' => date('M Y', strtotime($key . '-01')),
            'payments' => (int) $row['payments'],
            'gross' => (float) $row['gross'],
            'returns' => 0.0,
            'net' => (float) $row['gross'],
        ];
    }
    if (schema_table_exists(table('payment_adjustments'))) {
        $returnRows = owner_report_safe_all($pdo,
            'SELECT DATE_FORMAT(pa.processed_at,"%Y-%m") month_key,COALESCE(SUM(pa.amount),0) returns '
            . 'FROM `' . table('payment_adjustments') . '` pa JOIN `' . table('invoices') . '` i ON i.id=pa.invoice_id '
            . 'WHERE pa.status="processed" AND pa.processed_at>=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL ' . max(1, $months - 1) . ' MONTH),"%Y-%m-01")' . $branch
            . ' GROUP BY DATE_FORMAT(pa.processed_at,"%Y-%m")'
        );
        foreach ($returnRows as $row) {
            $key = (string) $row['month_key'];
            if (!isset($map[$key])) {
                $map[$key] = ['month_key'=>$key,'label'=>date('M Y', strtotime($key . '-01')),'payments'=>0,'gross'=>0.0,'returns'=>0.0,'net'=>0.0];
            }
            $map[$key]['returns'] = (float) $row['returns'];
            $map[$key]['net'] = round((float)$map[$key]['gross'] - (float)$row['returns'], 2);
        }
    }
    krsort($map);
    return array_slice(array_values($map), 0, max(1, $months));
}

function owner_report_provider_production(PDO $pdo, int $branchId, int $days = 30): array
{
    if (!schema_table_exists(table('invoice_items')) || !schema_table_exists(table('invoices')) || !schema_table_exists(table('clinical_encounters'))) return [];
    $branch = owner_report_branch_clause('i', $branchId);
    $amountColumn = schema_column_exists(table('invoice_items'), 'net_amount') ? 'ii.net_amount' : '(ii.quantity*ii.unit_price)';
    return owner_report_safe_all($pdo,
        'SELECT COALESCE(u.name,ce.dentist_name,"Unassigned provider") provider_name,'
        . 'COUNT(DISTINCT i.id) invoices,COUNT(ii.id) billable_lines,COALESCE(SUM(' . $amountColumn . '),0) production '
        . 'FROM `' . table('invoice_items') . '` ii JOIN `' . table('invoices') . '` i ON i.id=ii.invoice_id '
        . 'LEFT JOIN `' . table('clinical_encounters') . '` ce ON ce.id=i.encounter_id '
        . 'LEFT JOIN `' . table('users') . '` u ON u.id=ce.dentist_user_id '
        . 'WHERE i.created_at>=DATE_SUB(NOW(),INTERVAL ' . max(1, $days) . ' DAY) AND i.status NOT IN ("voided","written_off")' . $branch
        . ' GROUP BY provider_name ORDER BY production DESC LIMIT 12'
    );
}

function owner_report_balance_aging(PDO $pdo, int $branchId): array
{
    if (!schema_table_exists(table('invoices'))) return [];
    $branch = owner_report_branch_clause('i', $branchId);
    $rows = owner_report_safe_all($pdo,
        'SELECT CASE '
        . 'WHEN DATEDIFF(CURDATE(),i.created_at)<=30 THEN "0-30" '
        . 'WHEN DATEDIFF(CURDATE(),i.created_at)<=60 THEN "31-60" '
        . 'WHEN DATEDIFF(CURDATE(),i.created_at)<=90 THEN "61-90" ELSE "90+" END bucket,'
        . 'COUNT(*) invoices,COALESCE(SUM(GREATEST(i.amount-i.paid_amount,0)),0) balance '
        . 'FROM `' . table('invoices') . '` i WHERE i.status IN ("unpaid","partial")' . $branch
        . ' GROUP BY bucket'
    );
    $order = ['0-30'=>0,'31-60'=>1,'61-90'=>2,'90+'=>3];
    usort($rows, static fn(array $a, array $b): int => ($order[$a['bucket']] ?? 9) <=> ($order[$b['bucket']] ?? 9));
    return $rows;
}

function owner_report_top_balances(PDO $pdo, int $branchId, int $limit = 15): array
{
    if (!schema_table_exists(table('invoices')) || !schema_table_exists(table('patients'))) return [];
    $branch = owner_report_branch_clause('i', $branchId);
    return owner_report_safe_all($pdo,
        'SELECT p.id patient_id,p.patient_no,CONCAT(p.first_name," ",p.last_name) patient,'
        . 'COUNT(i.id) open_invoices,COALESCE(SUM(GREATEST(i.amount-i.paid_amount,0)),0) balance,MIN(i.created_at) oldest_invoice '
        . 'FROM `' . table('invoices') . '` i JOIN `' . table('patients') . '` p ON p.id=i.patient_id '
        . 'WHERE i.status IN ("unpaid","partial")' . $branch
        . ' GROUP BY p.id,p.patient_no,p.first_name,p.last_name HAVING balance>0 ORDER BY balance DESC LIMIT ' . max(1, $limit)
    );
}

function owner_report_appointment_conversion(PDO $pdo, int $branchId, int $days = 30): array
{
    if (!schema_table_exists(table('appointments'))) return [];
    $branch = owner_report_branch_clause('a', $branchId);
    return owner_report_safe_all($pdo,
        'SELECT a.status,COUNT(*) total FROM `' . table('appointments') . '` a '
        . 'WHERE a.start_at>=DATE_SUB(NOW(),INTERVAL ' . max(1, $days) . ' DAY)' . $branch
        . ' GROUP BY a.status ORDER BY total DESC'
    );
}

function owner_report_no_show_watchlist(PDO $pdo, int $branchId, int $days = 90): array
{
    if (!schema_table_exists(table('appointments')) || !schema_table_exists(table('patients'))) return [];
    $branch = owner_report_branch_clause('a', $branchId);
    return owner_report_safe_all($pdo,
        'SELECT p.patient_no,CONCAT(p.first_name," ",p.last_name) patient,'
        . 'SUM(a.status="no_show") no_shows,SUM(a.status="cancelled") cancellations,COUNT(*) total_events,MAX(a.start_at) last_event '
        . 'FROM `' . table('appointments') . '` a JOIN `' . table('patients') . '` p ON p.id=a.patient_id '
        . 'WHERE a.start_at>=DATE_SUB(NOW(),INTERVAL ' . max(1, $days) . ' DAY) AND a.status IN ("no_show","cancelled")' . $branch
        . ' GROUP BY p.id,p.patient_no,p.first_name,p.last_name HAVING total_events>=2 ORDER BY total_events DESC,last_event DESC LIMIT 15'
    );
}

function owner_report_cashier_closeout_exceptions(PDO $pdo, int $branchId): array
{
    if (!schema_table_exists(table('cashier_sessions'))) return [];
    $branch = owner_report_branch_clause('cs', $branchId);
    return owner_report_safe_all($pdo,
        'SELECT cs.*,u.name cashier,rv.name reviewer FROM `' . table('cashier_sessions') . '` cs '
        . 'LEFT JOIN `' . table('users') . '` u ON u.id=cs.cashier_user_id '
        . 'LEFT JOIN `' . table('users') . '` rv ON rv.id=cs.reviewed_by '
        . 'WHERE (cs.status="closed_pending_review" OR ABS(COALESCE(cs.variance,0))>0.009)' . $branch
        . ' ORDER BY COALESCE(cs.closed_at,cs.opened_at) DESC LIMIT 20'
    );
}

function owner_report_inventory_usage(PDO $pdo, int $branchId, int $days = 30): array
{
    if (!schema_table_exists(table('clinical_supply_usage_audit')) || !schema_table_exists(table('inventory_items'))) return [];
    $branch = owner_report_branch_clause('u', $branchId);
    $unitCost = schema_column_exists(table('inventory_lots'), 'unit_cost')
        ? 'COALESCE(l.unit_cost,i.usage_cost,i.purchase_cost,0)'
        : 'COALESCE(i.usage_cost,i.purchase_cost,0)';
    $lotJoin = schema_table_exists(table('inventory_lots')) ? 'LEFT JOIN `' . table('inventory_lots') . '` l ON l.id=u.lot_id ' : '';
    return owner_report_safe_all($pdo,
        'SELECT i.sku,i.name,i.category,i.unit,COUNT(u.id) usage_events,COALESCE(SUM(u.quantity_used),0) quantity_used,'
        . 'COALESCE(SUM(u.quantity_used*' . $unitCost . '),0) estimated_cost '
        . 'FROM `' . table('clinical_supply_usage_audit') . '` u JOIN `' . table('inventory_items') . '` i ON i.id=u.item_id ' . $lotJoin
        . 'WHERE u.created_at>=DATE_SUB(NOW(),INTERVAL ' . max(1, $days) . ' DAY)' . $branch
        . ' GROUP BY i.id,i.sku,i.name,i.category,i.unit ORDER BY estimated_cost DESC,quantity_used DESC LIMIT 15'
    );
}

function owner_report_snapshot(PDO $pdo, int $branchId): array
{
    return [
        'monthly_collections' => owner_report_monthly_collections($pdo, $branchId, 6),
        'provider_production' => owner_report_provider_production($pdo, $branchId, 30),
        'balance_aging' => owner_report_balance_aging($pdo, $branchId),
        'top_balances' => owner_report_top_balances($pdo, $branchId, 15),
        'appointment_conversion' => owner_report_appointment_conversion($pdo, $branchId, 30),
        'no_show_watchlist' => owner_report_no_show_watchlist($pdo, $branchId, 90),
        'cashier_closeout_exceptions' => owner_report_cashier_closeout_exceptions($pdo, $branchId),
        'inventory_usage' => owner_report_inventory_usage($pdo, $branchId, 30),
    ];
}
