<?php

namespace App\Controllers\Api;

use App\Libraries\ResponseHelper;

class ReportController extends BaseApiController
{
    public function finance()
    {
        if ($err = $this->requirePermission('view_reports')) return $err;

        $from  = $this->request->getGet('date_from');
        $to    = $this->request->getGet('date_to');
        $db    = \Config\Database::connect();
        $cid   = $this->churchId();

        $base = $db->table('finance_transactions')
                   ->select('DATE_FORMAT(transaction_date, "%Y-%m") AS month, type, SUM(amount) AS total, category_id')
                   ->where('church_id', $cid)
                   ->where('deleted_at IS NULL');

        if ($from) $base->where('transaction_date >=', $from);
        if ($to)   $base->where('transaction_date <=', $to);

        $rows = $base->groupBy('month, type, category_id')
                     ->orderBy('month', 'ASC')
                     ->get()->getResultArray();

        $result = [];
        foreach ($rows as $row) {
            $m  = $row['month'];
            $t  = $row['type'];
            if (!isset($result[$m])) {
                $result[$m] = ['month' => $m, 'income' => 0, 'expense' => 0, 'categories' => []];
            }
            $result[$m][$t] += (float) $row['total'];
            $result[$m]['categories'][] = [
                'category_id' => $row['category_id'],
                'type'        => $t,
                'total'       => (float) $row['total'],
            ];
        }

        return ResponseHelper::success(array_values($result));
    }

    public function members()
    {
        if ($err = $this->requirePermission('view_reports')) return $err;

        $db  = \Config\Database::connect();
        $cid = $this->churchId();

        $byMonth = $db->table('members')
                      ->select('DATE_FORMAT(created_at, "%Y-%m") AS month, membership_type, COUNT(*) AS count')
                      ->where('church_id', $cid)
                      ->where('deleted_at IS NULL')
                      ->groupBy('month, membership_type')
                      ->orderBy('month', 'ASC')
                      ->get()->getResultArray();

        return ResponseHelper::success($byMonth);
    }

    public function attendance()
    {
        if ($err = $this->requirePermission('view_reports')) return $err;

        $db  = \Config\Database::connect();
        $cid = $this->churchId();

        $rows = $db->table('event_attendances ea')
                   ->select('e.title, e.starts_at, COUNT(ea.id) AS attendees')
                   ->join('events e', 'e.id = ea.event_id')
                   ->where('ea.church_id', $cid)
                   ->where('e.deleted_at IS NULL')
                   ->groupBy('ea.event_id')
                   ->orderBy('e.starts_at', 'DESC')
                   ->get()->getResultArray();

        return ResponseHelper::success($rows);
    }

    public function donations()
    {
        if ($err = $this->requirePermission('view_reports')) return $err;

        $db  = \Config\Database::connect();
        $cid = $this->churchId();

        $rows = $db->table('donation_requests dr')
                   ->select('dr.uuid, dr.title, dr.target_amount, COALESCE(SUM(dc.amount),0) AS total_raised')
                   ->join('donation_contributions dc', 'dc.donation_request_id = dr.id', 'left')
                   ->where('dr.church_id', $cid)
                   ->where('dr.deleted_at IS NULL')
                   ->groupBy('dr.id')
                   ->get()->getResultArray();

        return ResponseHelper::success(array_map(function ($r) {
            $target = $r['target_amount'] ? (float) $r['target_amount'] : null;
            return [
                'uuid'          => $r['uuid'],
                'title'         => $r['title'],
                'target_amount' => $target,
                'total_raised'  => (float) $r['total_raised'],
                'completion_pct'=> $target && $target > 0
                    ? round(((float) $r['total_raised'] / $target) * 100, 2)
                    : null,
            ];
        }, $rows));
    }
}
