<?php
// api/analysis-controller.php
defined('ROOT') or die("Direct script access denied");

header('Content-Type: application/json; charset=utf-8');
header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
header('Cache-Control: post-check=0, pre-check=0', false);
header('Pragma: no-cache');

// Accept JSON or form-encoded POST
$raw = file_get_contents('php://input');
$post = json_decode($raw, true);
if (!is_array($post)) {
    $post = $_POST;
}

// —– (1) Filters —–
$txType    = $post['transaction_type']?? '';
$category  = $post['category']        ?? '';
$startDate = $post['start_date']      ?? '';
$endDate   = $post['end_date']        ?? '';
$user      = $post['user']            ?? '';

if ($startDate === '' && $endDate === '') {
    try {
        $now = new DateTime('now');
        $startDate = $now->format('Y-m-d');
        $endDate = $now->format('Y-m-d');
    } catch (\Throwable $e) {
        // ignore
    }
}

// —– (1a) Activities filter —–
$activities = $post['activity'] ?? [];
if (!is_array($activities)) {
    $activities = $activities === '' ? [] : [$activities];
}

$session = get_session();

if(!empty($session->id)){
    $username = $session->first_name . ' '. $session->last_name;
    $user_id = $session->id;
}

// Build WHERE clauses
$clauses   = ["1=1"];
$t_clauses = ["1=1"];
$like_clauses = ["1=1"];
$params    = [];

if ($startDate && $endDate) {
    $clauses[]        = "date_created BETWEEN :start AND :end";
    $t_clauses[]      = "t.date_created BETWEEN :start AND :end";
    $like_clauses[]      = "date_created BETWEEN :start AND :end";
    $params[':start'] = "$startDate 00:00:00";
    $params[':end']   = "$endDate   23:59:59";
}
if ($user !== '') {
    $clauses[]        = "created_by = :user";
    $t_clauses[]      = "t.user_id = :user";
    $like_clauses[]      = "created_by = :user";
    $params[':user']  = $user;
}
if ($txType !== '') {
    $clauses[]        = "transaction_type = :txType";
    $like_clauses[]        = "transaction_type LIKE :txType";
    $t_clauses[]      = "t.transaction_type = :txType";
    $params[':txType']= $txType;
}
if ($category !== '') {
    $clauses[]           = "category = :category";
    $t_clauses[]         = "t.category = :category";
    $params[':category'] = $category;
}

$where   = 'WHERE ' . implode(' AND ', $clauses);
$t_where = 'WHERE ' . implode(' AND ', $t_clauses);
$like_where = 'WHERE ' . implode(' AND ', $like_clauses);

// Instantiate DB helper
$db = new \Core\Database;

// —– (2) agencybankings —–
$agencyOverview  = $db->runOne("
    SELECT SUM(amount) AS total_amount, COUNT(*) AS total_transactions
    FROM agencybankings $where
", $params);

$agencyByNetwork = $db->run("
    SELECT network, SUM(amount) AS amount, COUNT(*) AS customers
    FROM agencybankings $where
    GROUP BY network
", $params);

$agencyByNetworkType = $db->run("
    SELECT network, transaction_type AS type, SUM(amount) AS amount, COUNT(*) AS customers
    FROM agencybankings $where
    GROUP BY network, transaction_type
", $params);

$agencyByType    = $db->run("
    SELECT transaction_type, SUM(amount) AS amount, COUNT(*) AS customers
    FROM agencybankings $where
    GROUP BY transaction_type
", $params);

// —– (2b) agencybankings distribution by user —–
$agencyByCreatedBy = [];
$agencyByCreatedByBreakdown = [];
try {
	$abWhere = $where;
	$abWhere = preg_replace('/\\bdate_created\\b/', 'ab.date_created', $abWhere);
	$abWhere = preg_replace('/\\bcreated_by\\b/', 'ab.created_by', $abWhere);
	$abWhere = preg_replace('/\\btransaction_type\\b/', 'ab.transaction_type', $abWhere);
	$abWhere = preg_replace('/\\bcategory\\b/', 'ab.category', $abWhere);

	$agencyByCreatedBy = $db->run("
		SELECT
			ab.created_by,
			COALESCE(NULLIF(CONCAT(TRIM(u.first_name), ' ', TRIM(u.last_name)), ' '), CONCAT('User ', ab.created_by)) AS full_name,
			COUNT(*) AS total_count,
			SUM(ab.amount) AS total_amount
		FROM agencybankings ab
		LEFT JOIN users u ON u.id = ab.created_by
		$abWhere
		GROUP BY ab.created_by, full_name
		ORDER BY total_count DESC
	", $params);

	$agencyByCreatedByBreakdown = $db->run("
		SELECT
			ab.created_by,
			ab.network,
			ab.transaction_type,
			SUM(ab.amount) AS total_amount,
			COUNT(*) AS total_count
		FROM agencybankings ab
		$abWhere
		GROUP BY ab.created_by, ab.network, ab.transaction_type
		ORDER BY total_amount DESC
	", $params);
} catch (\Throwable $e) {
	$agencyByCreatedBy = [];
	$agencyByCreatedByBreakdown = [];
}

// —– (3) airtimebundles —–
$airOverview  = $db->runOne("
    SELECT
      SUM(CAST(amount AS DECIMAL(10,2)))     AS total_amount,
      SUM(CAST(commission AS DECIMAL(10,2))) AS total_commission,
      COUNT(*)                               AS total_transactions
    FROM airtimebundles $like_where
", $params);

$airByNetwork      = $db->run("SELECT network, SUM(CAST(amount AS DECIMAL(10,2))) AS amount, COUNT(*) AS customers FROM airtimebundles $like_where GROUP BY network", $params);

$airByNetworkType  = $db->run("SELECT network, transaction_type AS type, SUM(CAST(amount AS DECIMAL(10,2))) AS amount, COUNT(*) AS customers FROM airtimebundles $like_where GROUP BY network, transaction_type", $params);

$airByTransType    = $db->run("SELECT transaction_type, SUM(CAST(amount AS DECIMAL(10,2))) AS amount, COUNT(*) AS customers FROM airtimebundles $like_where GROUP BY transaction_type", $params);

$airByStatus       = $db->run("SELECT transaction_status AS status, SUM(CAST(amount AS DECIMAL(10,2))) AS amount, COUNT(*) AS customers FROM airtimebundles $like_where GROUP BY transaction_status", $params);

$airByNetTypeStat  = $db->run("SELECT network, transaction_type, transaction_status AS status, SUM(CAST(amount AS DECIMAL(10,2))) AS amount, COUNT(*) AS customers FROM airtimebundles $where GROUP BY network, transaction_type, transaction_status", $params);

try {
	$airWhere = $like_where;
	$airWhere = preg_replace('/\bnetwork\b/', 'a.network', $airWhere);
	$airWhere = preg_replace('/\btransaction_type\b/', 'a.transaction_type', $airWhere);
	$airWhere = preg_replace('/\bcreated_by\b/', 'a.created_by', $airWhere);
	$airWhere = preg_replace('/\bdate_created\b/', 'a.date_created', $airWhere);
	$airWhere = preg_replace('/\bamount\b/', 'a.amount', $airWhere);

	$airByCreatedBy = $db->run("
		SELECT
			a.created_by,
			COALESCE(NULLIF(CONCAT(TRIM(u.first_name), ' ', TRIM(u.last_name)), ' '), CONCAT('User ', a.created_by)) AS full_name,
			COUNT(*) AS total_count,
			SUM(CAST(a.amount AS DECIMAL(10,2))) AS total_amount
		FROM airtimebundles a
		LEFT JOIN users u ON u.id = a.created_by
		$airWhere
		GROUP BY a.created_by, full_name
		ORDER BY total_count DESC
	", $params);

	$airByCreatedByBreakdown = $db->run("
		SELECT
			a.created_by,
			a.network,
			a.transaction_type,
			SUM(CAST(a.amount AS DECIMAL(10,2))) AS total_amount,
			COUNT(*) AS total_count
		FROM airtimebundles a
		$airWhere
		GROUP BY a.created_by, a.network, a.transaction_type
		ORDER BY total_amount DESC
	", $params);
} catch (\Throwable $e) {
	$airByCreatedBy = [];
	$airByCreatedByBreakdown = [];
}

// —– (4) attendance —–
$attendanceDays   = $db->run("
    SELECT u.first_name, u.last_name, t.remarks, COUNT(DISTINCT t.date) AS days_worked
    FROM attendance t
    LEFT JOIN users u ON t.user_id = u.id
    $t_where
    GROUP BY t.remarks
", $params);
$attendanceByGender = $db->run("
    SELECT u.gender, COUNT(*) AS count
    FROM attendance t
    JOIN users u ON t.user_id = u.id
    $t_where
    GROUP BY u.gender
", $params);

$attendanceSummary = $db->run("
    SELECT 
      u.id,
      u.first_name,
      u.last_name,
      SUM(CASE WHEN UPPER(t.remarks) = 'PRESENT' THEN 1 ELSE 0 END) AS days_present,
      SUM(CASE WHEN UPPER(t.remarks) = 'LATE' THEN 1 ELSE 0 END) AS days_late,
      SUM(CASE WHEN UPPER(t.remarks) = 'ABSENT'  THEN 1 ELSE 0 END) AS days_absent
    FROM attendance t
    JOIN users u ON u.id = t.user_id
    $t_where
    GROUP BY t.user_id
", $params);


// —– (5) cfas —–
$cfaSummary = $db->runOne("
    SELECT SUM(amount_cfa) AS total_cfa, SUM(amount_ghs) AS total_ghs, COUNT(*) AS total_transactions
    FROM cfas $where
", $params);
$cfaByType  = $db->run("
    SELECT transaction_type, SUM(amount_cfa) AS amount_cfa, SUM(amount_ghs) AS amount_ghs, COUNT(*) AS customers
    FROM cfas $where
    GROUP BY transaction_type
", $params);

// —– (6) commissions —–
$commSummary   = $db->runOne("SELECT SUM(amount) AS total_commission, COUNT(*) AS total_transactions FROM commissions $where", $params);
$commByNetwork = $db->run("SELECT network, SUM(amount) AS amount, COUNT(*) AS transactions FROM commissions $where GROUP BY network", $params);

// —– (7) deposits —–
$depSummary   = $db->runOne("SELECT SUM(CAST(amount AS DECIMAL(10,2))) AS total_amount, COUNT(*) AS total_transactions FROM deposits $where", $params);
$depByNetwork = $db->run("SELECT network, SUM(CAST(amount AS DECIMAL(10,2))) AS amount, COUNT(*) AS customers FROM deposits $where GROUP BY network", $params);

// —– (8) ezwichs —–

$ezwSummary    = $db->runOne("SELECT SUM(amount) AS total_amount, COUNT(*) AS total_customers FROM ezwichs $where", $params);
$ezwByType     = $db->run("SELECT transaction_type, transaction_status, SUM(amount) AS amount, SUM(charge) AS charges, COUNT(*) AS customers FROM ezwichs $where GROUP BY transaction_type, transaction_status", $params);
$ezwByStatus   = $db->run("SELECT transaction_status AS status, SUM(amount) AS amount, SUM(charge) AS charges, COUNT(*) AS customers FROM ezwichs $where GROUP BY transaction_status", $params);

// —– (9) momoremits —–
$momoSummary         = $db->runOne("SELECT SUM(CAST(amount AS DECIMAL(10,2))) AS total_amount, SUM(amount_payable) AS total_payable, COUNT(*) AS customers_served FROM momoremits $like_where", $params);
$momoByNetwork       = $db->run("SELECT network, SUM(CAST(amount AS DECIMAL(10,2))) AS amount, COUNT(*) AS customers FROM momoremits $like_where GROUP BY network", $params);
$momoByNetworkType   = $db->run("SELECT network, transaction_type AS type, SUM(CAST(amount AS DECIMAL(10,2))) AS amount, COUNT(*) AS customers FROM momoremits $like_where GROUP BY network, transaction_type", $params);
$momoByTransType     = $db->run("SELECT transaction_type AS type, SUM(CAST(amount AS DECIMAL(10,2))) AS amount, COUNT(*) AS customers FROM momoremits $like_where GROUP BY transaction_type", $params);

// —– (9b) momoremits distribution by user —–
$momoByCreatedBy = [];
$momoByCreatedByBreakdown = [];
try {
	$mmWhere = $like_where;
	$mmWhere = preg_replace('/\bdate_created\b/', 'mm.date_created', $mmWhere);
	$mmWhere = preg_replace('/\bcreated_by\b/', 'mm.created_by', $mmWhere);
	$mmWhere = preg_replace('/\btransaction_type\b/', 'mm.transaction_type', $mmWhere);
	$mmWhere = preg_replace('/\bcategory\b/', 'mm.category', $mmWhere);

	$momoByCreatedBy = $db->run("
		SELECT
			mm.created_by,
			COALESCE(NULLIF(CONCAT(TRIM(u.first_name), ' ', TRIM(u.last_name)), ' '), CONCAT('User ', mm.created_by)) AS full_name,
			COUNT(*) AS total_count,
			SUM(CAST(mm.amount AS DECIMAL(10,2))) AS total_amount
		FROM momoremits mm
		LEFT JOIN users u ON u.id = mm.created_by
		$mmWhere
		GROUP BY mm.created_by, full_name
		ORDER BY total_amount DESC
	", $params);

	$momoByCreatedByBreakdown = $db->run("
		SELECT
			mm.created_by,
			mm.network,
			mm.transaction_type,
			SUM(CAST(mm.amount AS DECIMAL(10,2))) AS total_amount,
			COUNT(*) AS total_count
		FROM momoremits mm
		$mmWhere
		GROUP BY mm.created_by, mm.network, mm.transaction_type
		ORDER BY total_amount DESC
	", $params);
} catch (\Throwable $e) {
	$momoByCreatedBy = [];
	$momoByCreatedByBreakdown = [];
}

// —– (10) myaccounts —–
$accSummary = $db->runOne("SELECT SUM(debit) AS total_debit, SUM(credit) AS total_credit, COUNT(*) AS total_customers FROM myaccounts $where", $params);

// —– (11) payments & backoffice —–
$paySummary   = $db->runOne("SELECT SUM(amount) AS total_amount, COUNT(*) AS total_transactions FROM payments $where", $params);
$payBOsummary = $db->runOne("SELECT SUM(amount) AS total_amount, COUNT(*) AS total_transactions FROM payments_backoffice $where", $params);

// —– (12) payment_services —–
$svcSummary    = $db->runOne("SELECT SUM(amount) AS total_amount, SUM(charge) AS total_charge, COUNT(*) AS total_customers FROM payment_services $where", $params);
$svcByCategory = $db->run("SELECT category, SUM(amount) AS total_amount, SUM(charge) AS total_charge, COUNT(*) AS total_customers FROM payment_services $where GROUP BY category", $params);

// —– (13) requests & backoffice —–
$reqSummary        = $db->run("SELECT network AS request_option, SUM(amount) AS total_amount, COUNT(*) AS total_count, transaction_status AS status FROM requests $where GROUP BY network, transaction_status", $params);
$paymentBOSummary  = $db->run("SELECT network AS payment_type, SUM(amount) AS total_payment, COUNT(*) AS total_transactions, transaction_status AS status FROM payments_backoffice $where GROUP BY network, transaction_status", $params);

// —– (14) reversals —–
$revSummary = $db->runOne("SELECT SUM(amount) AS total_amount, COUNT(*) AS total_transactions FROM reversals $where", $params);

// —– (15) sendings —–
$sendSummary    = $db->runOne("SELECT SUM(CAST(amount AS DECIMAL(10,2))) AS total_amount, COUNT(*) AS total_customers FROM sendings $where", $params);
$sendByNetwork  = $db->run("SELECT network, SUM(CAST(amount AS DECIMAL(10,2))) AS amount, COUNT(*) AS customers FROM sendings $where GROUP BY network", $params);

// —– (16) sims —–
$simsSummary   = $db->runOne("SELECT COUNT(*) AS total_quantity, SUM(amount) AS total_amount, SUM(commission) AS total_commission FROM sims $where", $params);
$simsByNetwork = $db->run("SELECT network, COUNT(*) AS qty, SUM(amount) AS total_amount, SUM(commission) AS total_commission FROM sims $where GROUP BY network", $params);
$simsByStatus  = $db->run("SELECT transaction_status AS sim_status, COUNT(*) AS quantity, SUM(amount) AS total_amount, SUM(commission) AS total_commission FROM sims $where GROUP BY transaction_status", $params);

// —– (17) tickets —–
$ticketsSummary = $db->run("SELECT status, COUNT(*) AS count FROM tickets $where GROUP BY status", $params);

// —– (17b) cashpickups —–
$cashpickupScopeParams = [];
$cashpickupScopeClauses = ["(deleted IS NULL OR deleted = 0)"];

 $cashpickupCanViewAll = false;
 try {
     $cashpickupCanViewAll = (user_can('is_manager') || user_can('is_admin'));
 } catch (\Throwable $e) {
     $cashpickupCanViewAll = false;
 }

 try {
     $roles = (array)($session->roles ?? []);
     $roles = array_map(function($r){ return strtolower(trim((string)$r)); }, $roles);
     $role0 = strtolower(trim((string)($roles[0] ?? '')));
     $isPrivileged = in_array($role0, ['manager','admin','is_manager','is_admin'], true);
     if (!$isPrivileged && !empty($roles)) {
         foreach ($roles as $r) {
             if (in_array($r, ['manager','admin','is_manager','is_admin'], true)) {
                 $isPrivileged = true;
                 break;
             }
         }
     }
     if ($isPrivileged) {
         $cashpickupCanViewAll = true;
     }
 } catch (\Throwable $e) {
     // ignore
 }

 if (!empty($session->id) && !$cashpickupCanViewAll) {
     $cashpickupScopeClauses[] = "(created_by = :cashpickup_uid OR assigned_user_id = :cashpickup_uid)";
     $cashpickupScopeParams[':cashpickup_uid'] = $session->id;
 }

if ($startDate && $endDate) {
    $cashpickupScopeClauses[] = "date_created BETWEEN :start AND :end";
    $cashpickupScopeParams[':start'] = "$startDate 00:00:00";
    $cashpickupScopeParams[':end'] = "$endDate   23:59:59";
}

$cashpickupWhere = 'WHERE ' . implode(' AND ', $cashpickupScopeClauses);

$cashpickupsError = '';

$cashpickupsDebug = [];
try {
    $cashpickupsDebug['can_view_all'] = !empty($cashpickupCanViewAll) ? 1 : 0;
    $cashpickupsDebug['scope_uid'] = (int)($session->id ?? 0);
    $cashpickupsDebug['scope_where'] = $cashpickupWhere;
} catch (\Throwable $e) {
    $cashpickupsDebug = [];
}

$cashpickupOverview = $db->runOne("
    SELECT
      SUM(CAST(amount_involved AS DECIMAL(10,2))) AS total_amount,
      COUNT(*) AS total_transactions
    FROM cashpickups
    $cashpickupWhere
", $cashpickupScopeParams);

if ($cashpickupOverview === false) {
    try {
        $cashpickupsError = (string)($db->error ?? '');
    } catch (\Throwable $e) {
        $cashpickupsError = '';
    }
    $cashpickupOverview = ['total_amount' => 0, 'total_transactions' => 0];
}

// extra debug counts to understand why totals might be 0
try {
    $rowAll = $db->runOne("SELECT COUNT(*) AS c FROM cashpickups");
    $rowDel0Num = $db->runOne("SELECT COUNT(*) AS c FROM cashpickups WHERE deleted = 0");
    $rowDel0Str = $db->runOne("SELECT COUNT(*) AS c FROM cashpickups WHERE deleted = '0'");
    $rowDelNullOr0 = $db->runOne("SELECT COUNT(*) AS c FROM cashpickups WHERE (deleted IS NULL OR deleted = 0)");
    $cashpickupsDebug['count_all'] = (int)($rowAll['c'] ?? 0);
    $cashpickupsDebug['count_deleted_eq_0'] = (int)($rowDel0Num['c'] ?? 0);
    $cashpickupsDebug['count_deleted_eq_str0'] = (int)($rowDel0Str['c'] ?? 0);
    $cashpickupsDebug['count_deleted_null_or_0'] = (int)($rowDelNullOr0['c'] ?? 0);
} catch (\Throwable $e) {
    // ignore
}

$cashpickupHasApprovalCol = false;
try {
    $hasApprovalColRow = $db->runOne(
        "SELECT 1 AS ok
         FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_SCHEMA = :db
           AND TABLE_NAME = 'cashpickups'
           AND COLUMN_NAME = 'approval_status'
         LIMIT 1",
        ['db' => DB_NAME]
    );
    $cashpickupHasApprovalCol = !empty($hasApprovalColRow);
} catch (\Throwable $e) {
    $cashpickupHasApprovalCol = false;
}

$cashpickupByApproval = [];
if ($cashpickupHasApprovalCol) {
    $cashpickupByApproval = $db->run("
        SELECT
          LOWER(COALESCE(approval_status,'pending')) AS status,
          COUNT(*) AS total_count
        FROM cashpickups
        $cashpickupWhere
        GROUP BY LOWER(COALESCE(approval_status,'pending'))
    ", $cashpickupScopeParams);
    if ($cashpickupByApproval === false) {
        if ($cashpickupsError === '') {
            try {
                $cashpickupsError = (string)($db->error ?? '');
            } catch (\Throwable $e) {
                $cashpickupsError = '';
            }
        }
        $cashpickupByApproval = [];
    }
} else {
    // If the column doesn't exist, align with legacy behavior: treat as pending
    $cashpickupByApproval = [(object)['status' => 'pending', 'total_count' => (int)($cashpickupOverview['total_transactions'] ?? 0)]];
}

$cashpickupByStatus = $db->run("
    SELECT
      transaction_status AS status,
      COUNT(*) AS total_count,
      SUM(CAST(amount_involved AS DECIMAL(10,2))) AS total_amount
    FROM cashpickups
    $cashpickupWhere
    GROUP BY transaction_status
", $cashpickupScopeParams);

if ($cashpickupByStatus === false) {
    if ($cashpickupsError === '') {
        try {
            $cashpickupsError = (string)($db->error ?? '');
        } catch (\Throwable $e) {
            $cashpickupsError = '';
        }
    }
    $cashpickupByStatus = [];
}

$cashpickupByDate = $db->run("
    SELECT
      DATE(date_created) AS date,
      COUNT(*) AS count,
      SUM(CAST(amount_involved AS DECIMAL(10,2))) AS amount
    FROM cashpickups
    $cashpickupWhere
    GROUP BY DATE(date_created)
    ORDER BY date ASC
", $cashpickupScopeParams);

if ($cashpickupByDate === false) {
    if ($cashpickupsError === '') {
        try {
            $cashpickupsError = (string)($db->error ?? '');
        } catch (\Throwable $e) {
            $cashpickupsError = '';
        }
    }
    $cashpickupByDate = [];
}

$cashpickupBySide = $db->run("
    SELECT
      LOWER(COALESCE(transaction_side,'')) AS side,
      COUNT(*) AS total_count
    FROM cashpickups
    $cashpickupWhere
    GROUP BY LOWER(COALESCE(transaction_side,''))
", $cashpickupScopeParams);

if ($cashpickupBySide === false) {
    if ($cashpickupsError === '') {
        try {
            $cashpickupsError = (string)($db->error ?? '');
        } catch (\Throwable $e) {
            $cashpickupsError = '';
        }
    }
    $cashpickupBySide = [];
}

$cashpickupBySideApproval = [];
try {
    if (!empty($cashpickupHasApprovalCol)) {
        $cashpickupBySideApproval = $db->run("
            SELECT
              LOWER(COALESCE(transaction_side,'')) AS side,
              LOWER(COALESCE(approval_status,'pending')) AS status,
              COUNT(*) AS total_count,
              SUM(CAST(amount_involved AS DECIMAL(10,2))) AS total_amount
            FROM cashpickups
            $cashpickupWhere
            GROUP BY LOWER(COALESCE(transaction_side,'')), LOWER(COALESCE(approval_status,'pending'))
        ", $cashpickupScopeParams);
        if ($cashpickupBySideApproval === false) {
            if ($cashpickupsError === '') {
                try {
                    $cashpickupsError = (string)($db->error ?? '');
                } catch (\Throwable $e) {
                    $cashpickupsError = '';
                }
            }
            $cashpickupBySideApproval = [];
        }
    }
} catch (\Throwable $e) {
    $cashpickupBySideApproval = [];
}

$cashpickupApprovalCounts = [
    'approved' => 0,
    'pending' => 0,
    'draft' => 0,
    'denied' => 0,
];
foreach (($cashpickupByApproval ?: []) as $row) {
    $r = (array)$row;
    $st = strtolower(trim((string)($r['status'] ?? '')));
    if ($st === '') continue;
    if (isset($cashpickupApprovalCounts[$st])) {
        $cashpickupApprovalCounts[$st] = (int)($r['total_count'] ?? 0);
    }
}

$vars = get_value();
$rolesMapTable = $vars['optional_tables']['roles_map_table'] ?? 'user_roles_map';
$rolesTable = $vars['optional_tables']['roles_table'] ?? 'user_roles';
$usersTableForRoles = $vars['optional_tables']['users_table'] ?? 'users';

$topRolesError = '';
$topRoles = [];

// schema-aware user filters (some installs don't have users.disabled/users.deleted)
$hasUserDisabledCol = false;
$hasUserDeletedCol = false;
try {
    $chkDisabled = $db->runOne("SELECT 1 AS ok FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = :db AND TABLE_NAME = :t AND COLUMN_NAME = 'disabled' LIMIT 1", [
        'db' => DB_NAME,
        't' => $usersTableForRoles,
    ]);
    $hasUserDisabledCol = (bool)$chkDisabled;

    $chkDeleted = $db->runOne("SELECT 1 AS ok FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = :db AND TABLE_NAME = :t AND COLUMN_NAME = 'deleted' LIMIT 1", [
        'db' => DB_NAME,
        't' => $usersTableForRoles,
    ]);
    $hasUserDeletedCol = (bool)$chkDeleted;
} catch (\Throwable $e) {
    $hasUserDisabledCol = false;
    $hasUserDeletedCol = false;
}

$userRoleWhereParts = [
    'urm.disabled = 0',
    '(ur.disabled IS NULL OR ur.disabled = 0)',
];
if ($hasUserDisabledCol) {
    $userRoleWhereParts[] = '(u.disabled IS NULL OR u.disabled = 0)';
}
if ($hasUserDeletedCol) {
    $userRoleWhereParts[] = '(u.deleted IS NULL OR u.deleted = 0)';
}
$userRoleWhere = 'WHERE ' . implode("\n          AND ", $userRoleWhereParts);

$unassignedWhereParts = [];
if ($hasUserDisabledCol) {
    $unassignedWhereParts[] = '(u.disabled IS NULL OR u.disabled = 0)';
}
if ($hasUserDeletedCol) {
    $unassignedWhereParts[] = '(u.deleted IS NULL OR u.deleted = 0)';
}
$unassignedWhere = $unassignedWhereParts ? ('WHERE ' . implode(' AND ', $unassignedWhereParts)) : '';

try {
    // Count enabled role assignments
    $assignedRoleCounts = $db->run("
        SELECT
          COALESCE(NULLIF(TRIM(ur.role),''), 'Unassigned') AS role,
          COUNT(DISTINCT urm.user_id) AS cnt
        FROM {$rolesMapTable} urm
        JOIN {$usersTableForRoles} u
          ON u.id = urm.user_id
        JOIN {$rolesTable} ur
          ON ur.id = urm.role_id
        {$userRoleWhere}
        GROUP BY COALESCE(NULLIF(TRIM(ur.role),''), 'Unassigned')
    ");

    if (!empty($db->has_error)) {
        $topRolesError = (string)($db->error ?? '');
        throw new \Exception($topRolesError);
    }

    foreach (($assignedRoleCounts ?: []) as $row) {
        $r = (array)$row;
        $roleName = trim((string)($r['role'] ?? ''));
        $cnt = (int)($r['cnt'] ?? 0);
        if ($roleName === '' || $cnt <= 0) continue;
        $topRoles[$roleName] = ($topRoles[$roleName] ?? 0) + $cnt;
    }

    // Count unassigned users (no active role mapping)
    $unassignedRow = $db->runOne("
        SELECT COUNT(*) AS cnt
        FROM {$usersTableForRoles} u
        {$unassignedWhere}
          AND NOT EXISTS (
            SELECT 1
            FROM {$rolesMapTable} urm
            WHERE urm.user_id = u.id
              AND urm.disabled = 0
          )
    ");
    if (!empty($db->has_error)) {
        $topRolesError = (string)($db->error ?? '');
        throw new \Exception($topRolesError);
    }
    $unassignedCnt = (int)(is_array($unassignedRow) ? ($unassignedRow['cnt'] ?? 0) : 0);
    if ($unassignedCnt > 0) {
        $topRoles['Unassigned'] = ($topRoles['Unassigned'] ?? 0) + $unassignedCnt;
    }
} catch (\Throwable $e) {
    $topRoles = [];
    if ($topRolesError === '') {
        $topRolesError = trim((string)$e->getMessage());
    }
}

// normalize to list, sort desc by cnt, take top 10
$topRolesList = [];
foreach (($topRoles ?: []) as $roleName => $cnt) {
    $roleName = trim((string)$roleName);
    $cnt = (int)$cnt;
    if ($roleName === '' || $cnt <= 0) continue;
    $topRolesList[] = ['role' => $roleName, 'cnt' => $cnt];
}
usort($topRolesList, function($a, $b) {
    return ($b['cnt'] ?? 0) <=> ($a['cnt'] ?? 0);
});
$topRoles = array_slice($topRolesList, 0, 10);

// —– (18) transactions —–
// 1) Overview: counts, sums...
$transOverview = $db->runOne("
    SELECT
      COUNT(*)       AS transaction_count,
      SUM(amount)    AS total_amount,
      SUM(debit)     AS total_debit,
      SUM(credit)    AS total_credit
    FROM transactions
    $where
", $params);

// 2) Breakdown by transaction_type
$transByType = $db->run("
    SELECT
      transaction_type,
      COUNT(*)    AS count,
      SUM(amount) AS total_amount
    FROM transactions
    $where
    GROUP BY transaction_type
", $params);

// 3) By date (last 30 days)
$transByDate = $db->run("
    SELECT
      DATE(date_created) AS date,
      COUNT(*)            AS count,
      SUM(amount)         AS amount,
      SUM(debit)          AS total_debit,
      SUM(credit)         AS total_credit
    FROM transactions
    $where
    GROUP BY DATE(date_created)
    ORDER BY date ASC
", $params);

// 4) Recent 40, with full name & image, formatted date, no commission
$transRecentRaw = $db->run("
    SELECT
      t.date_created,
      u.first_name,
      u.last_name,
      u.image,
      t.transaction_type,
      t.description,
      t.amount,
      t.debit,
      t.credit,
      t.balance
    FROM transactions t
    JOIN users u ON u.id = t.created_by
    WHERE t.created_by = $user_id
    ORDER BY t.date_created DESC
    LIMIT 40
");

// Apply PHP-side formatting to date and full_name
$transRecent = [];
foreach ($transRecentRaw as $row) {
    $r = (array)$row;
    $transRecent[] = [
        'date'             => get_date($r['date_created']),
        'full_name'        => trim($r['first_name'] . ' ' . $r['last_name']),
        'image'            => get_image($r['image']),
        'transaction_type' => $r['transaction_type'],
        'amount'           => $r['amount'],
        'debit'            => $r['debit'],
        'credit'           => $r['credit'],
        'balance'          => $r['balance'],
        'description'          => $r['description'],
    ];
}

// —– (19) withdrawals —–
$withdOverview  = $db->runOne("SELECT SUM(CAST(amount AS DECIMAL(10,2))) AS total_amount, COUNT(*) AS total_transactions FROM withdrawals $where", $params);
$withdByNetwork = $db->run("SELECT network, SUM(CAST(amount AS DECIMAL(10,2))) AS amount, COUNT(*) AS transactions FROM withdrawals $where GROUP BY network", $params);

// —– (21) NEW KPI CALCULATIONS —–

// 21a) Average Transaction Value
$avgValue = 0;
if (!empty($transOverview['transaction_count'])) {
    $avgValue = $transOverview['total_amount'] / $transOverview['transaction_count'];
}

// 21b) Customer Reach (distinct users in transactions)
$reachRow = $db->runOne("
    SELECT COUNT(DISTINCT created_by) AS customer_reach
    FROM transactions
    $where
", $params);
$customerReach = $reachRow['customer_reach'] ?? 0;

// 21c) Failure Rate (%) in requests
$reqStats = $db->runOne("
    SELECT 
      SUM(CASE WHEN transaction_status != 'SUCCESS' THEN 1 ELSE 0 END) AS failures,
      COUNT(*) AS total
    FROM requests
    $where
", $params);
$failureRate = 0;
if (!empty($reqStats['total'])) {
    $failureRate = round($reqStats['failures'] / $reqStats['total'] * 100, 1);
}

// 21d) Average Ticket Resolution Time (hours)
$ticketStats = $db->runOne("
    SELECT 
      AVG(TIMESTAMPDIFF(HOUR, date_created, date_updated)) AS avg_resolution_hours
    FROM tickets
    $where
    AND date_updated IS NOT NULL
", $params);
$avgTicketTime = round($ticketStats['avg_resolution_hours'] ?? 0, 1);

// 21e) Deposit ↔ Withdrawal Ratio
$depSum = $depSummary['total_amount'] ?? 0;
$withdSum = $withdOverview['total_amount'] ?? 0;
$depWithRatio = $withdSum > 0 ? round($depSum / $withdSum, 2) : 0;

// 21f) Commission Yield (%) — total commission ÷ transaction volume
$totalComm = $commSummary['total_commission'] ?? 0;
$totalVol = $transOverview['total_amount']/2 ?? 0;
$commissionYield = $totalVol > 0 ? round($totalComm / $totalVol, 4) : 0;

// 21g) Network Coverage (distinct networks across key tables)
$netCovRow = $db->runOne("
    SELECT COUNT(DISTINCT network) AS coverage FROM (
      SELECT network FROM agencybankings $where
      UNION
      SELECT network FROM sendings    $where
      UNION
      SELECT network FROM momoremits  $where
    ) AS combined
", $params);
$networkCoverage = $netCovRow['coverage'] ?? 0;

// 21h) Growth Rate (%) vs. prior period (transaction count)
$rangeDays = (new DateTime($endDate))->diff(new DateTime($startDate))->days + 1;
$priorStart = (new DateTime($startDate))->modify("-{$rangeDays} days")->format('Y-m-d') . ' 00:00:00';
$priorEnd   = (new DateTime($startDate))->modify('-1 day')->format('Y-m-d') . ' 23:59:59';
$priorParams = $params;
$priorParams[':start'] = $priorStart;
$priorParams[':end']   = $priorEnd;

$priorStats = $db->runOne("
    SELECT COUNT(*) AS prior_count
    FROM transactions
    WHERE date_created BETWEEN :start AND :end
", $priorParams);
$priorCount = $priorStats['prior_count'] ?? 0;
$currentCount = $transOverview['transaction_count'] ?? 0;
$growthRate = $priorCount > 0 
    ? round(($currentCount - $priorCount) / $priorCount * 100, 1)
    : 0;

// 21i) Attendance Penetration (%) — % of users who marked today
$today = date('Y-m-d');
$markedToday = $db->runOne("
    SELECT COUNT(DISTINCT user_id) AS marked
    FROM attendance
    WHERE date = :today
", ['today' => $today])['marked'] ?? 0;
$totalUsers = $db->runOne("SELECT COUNT(*) AS cnt FROM users WHERE deleted = 0")['cnt'] ?? 0;
$presentOrLateToday = $db->runOne("
    SELECT COUNT(DISTINCT user_id) AS cnt
    FROM attendance
    WHERE date = :today
      AND (UPPER(remarks) = 'PRESENT' OR UPPER(remarks) = 'LATE')
", ['today' => $today])['cnt'] ?? 0;
$attendancePenetr = $totalUsers > 0 
    ? round($markedToday / $totalUsers * 100, 1) 
    : 0;

// 21j) Average Commission per Transaction (₵)
$avgCommPerTx = $transOverview['transaction_count'] > 0 
    ? round($totalComm / $transOverview['transaction_count'], 2)
    : 0;

// —– (20) Combine & output —–

$roleNames = [];
try {
  $roleNames = $session->roles ?? [];
  if(!is_array($roleNames)){
    $roleNames = [$roleNames];
  }
} catch (\Throwable $e) {
  $roleNames = [];
}
$primaryRole = '';
try {
  $primaryRole = strtolower(trim((string)($roleNames[0] ?? '')));
} catch (\Throwable $e) {
  $primaryRole = '';
}

$requestsBacklogRow = $db->runOne("SELECT COUNT(*) AS backlog FROM requests $where AND (transaction_status = 'Pending' OR transaction_status = 'pending' OR transaction_status = 'Draft' OR transaction_status = 'draft')", $params);
$requestsBacklog = (int)($requestsBacklogRow['backlog'] ?? 0);

$reqFailureTrend = [];
try {
  $reqFailureTrend = $db->run("SELECT DATE(date_created) AS day, COUNT(*) AS total, SUM(CASE WHEN transaction_status = 'Failed' OR transaction_status = 'failed' OR transaction_status = 'Rejected' OR transaction_status = 'rejected' THEN 1 ELSE 0 END) AS failures FROM requests $where GROUP BY DATE(date_created) ORDER BY day DESC LIMIT 7", $params);
  if(!is_array($reqFailureTrend)){
    $reqFailureTrend = [];
  }
  $reqFailureTrend = array_reverse($reqFailureTrend);
} catch (\Throwable $e) {
  $reqFailureTrend = [];
}

$ezwTrend = [];
try {
  $ezwTrend = $db->run("SELECT DATE(date_created) AS day, SUM(amount) AS total_amount, COUNT(*) AS total_customers FROM ezwichs $where GROUP BY DATE(date_created) ORDER BY day DESC LIMIT 7", $params);
  if(!is_array($ezwTrend)){
    $ezwTrend = [];
  }
  $ezwTrend = array_reverse($ezwTrend);
} catch (\Throwable $e) {
  $ezwTrend = [];
}

$output = [
  'meta' => [
    'start_date' => $startDate,
    'end_date'   => $endDate,
    'username'   => $username,
    'user_id'    => $user_id,
    'roles'      => $roleNames,
    'primary_role' => $primaryRole,
    'build_id' => 'dash_roles_v2',
    'total_users' => $totalUsers,
    'present_or_late_today' => $presentOrLateToday,
    'cashpickups_total' => (int)($cashpickupOverview['total_transactions'] ?? 0),
    'cashpickups_error' => $cashpickupsError,
    'cashpickups_can_view_all' => (!empty($cashpickupCanViewAll) ? 1 : 0),
    'cashpickups_debug' => $cashpickupsDebug,
    'cashpickups_approved' => (int)($cashpickupApprovalCounts['approved'] ?? 0),
    'cashpickups_pending' => (int)($cashpickupApprovalCounts['pending'] ?? 0),
    'cashpickups_draft' => (int)($cashpickupApprovalCounts['draft'] ?? 0),
    'cashpickups_denied' => (int)($cashpickupApprovalCounts['denied'] ?? 0),
    'top_roles' => $topRoles,
    'top_roles_error' => $topRolesError,
  ],
  'agencybankings'   => [
	'Overview'=>$agencyOverview,
	'By_Network'=>$agencyByNetwork,
	'By_Network_Type'=>$agencyByNetworkType,
	'By_Type'=>$agencyByType,
	'By_Created_By'=>$agencyByCreatedBy,
	'By_Created_By_Breakdown'=>$agencyByCreatedByBreakdown
	],
  'airtimebundles'   => [
	'Overview'=>$airOverview,
	'By_Network'=>$airByNetwork,
	'By_Network_Type'=>$airByNetworkType,
	'By_Type'=>$airByTransType,
	'By_Status'=>$airByStatus,
	'By_Network_Type_Status'=>$airByNetTypeStat,
	'By_Created_By'=>$airByCreatedBy,
	'By_Created_By_Breakdown'=>$airByCreatedByBreakdown
	],
  'attendance'       => ['Summary' => $attendanceSummary, 'Days_Worked' => $attendanceDays, 'By_Gender' => $attendanceByGender ],
  'cfas'             => ['Summary'=>$cfaSummary, 'By_Type'=>$cfaByType],
  'commissions'      => ['Summary'=>$commSummary, 'By_Network'=>$commByNetwork],
  'cashpickups'      => [
    'Overview' => $cashpickupOverview,
    'By_Status' => $cashpickupByStatus,
    'By_Approval' => $cashpickupByApproval,
    'By_Date' => $cashpickupByDate,
    'By_Side' => $cashpickupBySide,
    'By_Side_Approval' => $cashpickupBySideApproval,
  ],
  'deposits'         => ['Overview'=>$depSummary, 'By_Network'=>$depByNetwork],
  'ezwichs'          => ['Overview'=>$ezwSummary, 'By_Type'=>$ezwByType, 'By_Status'=>$ezwByStatus],
  'momoremits'       => [
	'Overview'=>$momoSummary,
	'By_Network'=>$momoByNetwork,
	'By_Type'=>$momoByTransType,
	'By_Network_Type'=>$momoByNetworkType,
	'By_Created_By'=>$momoByCreatedBy,
	'By_Created_By_Breakdown'=>$momoByCreatedByBreakdown
	],
  'myaccounts'       => ['Summary'=>$accSummary],
  'payments'         => ['Summary'=>$paySummary],
  'payment_services' => ['Summary'=>$svcSummary, 'By_Category'=>$svcByCategory],
  'requests'         => [
    'Requests'=>$reqSummary,
    'Backoffice_Payments'=>$paymentBOSummary,
    'FailureRate' => $failureRate,
    'Backlog' => $requestsBacklog,
    'By_Date' => $reqFailureTrend,
  ],
  'reversals'        => ['Summary'=>$revSummary],
  'sendings'         => ['Overview'=>$sendSummary, 'By_Network'=>$sendByNetwork],
  'sims'             => ['Summary'=>$simsSummary, 'By_Network'=>$simsByNetwork, 'By_Status'=>$simsByStatus],
  'tickets'          => ['By_Status'=>$ticketsSummary, 'AvgResolutionTime' => $avgTicketTime],
  'transactions'     => [
    'Overview'=>$transOverview,
    'By_Type'=>$transByType,
    'By_Date'=>$transByDate,
    'Recent'=>$transRecent,
    'AverageValue' => $avgValue,
    'GrowthRate' => $growthRate,
    'AvgCommission' => $avgCommPerTx,
  ],
  'withdrawals'      => ['Overview'=>$withdOverview, 'By_Network'=>$withdByNetwork],
  'customerReach'                   => $customerReach,
  'depositWithdrawalRatio'          => $depWithRatio,
  'commissionYield'                 => $commissionYield,
  'networkCoverage'                 => $networkCoverage,
];

$output['attendance']['Penetration'] = $attendancePenetr;
$output['ezwichs']['By_Date'] = $ezwTrend;


echo json_encode($output, JSON_UNESCAPED_SLASHES);
exit;
