<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class TrendsController extends Controller
{
    public function currentMembersByGender()
    {
        $genderCounts = DB::select("
        SELECT 
            gender,
            COUNT(*) AS total_members
        FROM 
            members
        WHERE 
            status = 1
            AND national_id NOT LIKE 'old-%'
        GROUP BY 
            gender

        UNION ALL

        SELECT 
            'Total' AS gender,
            COUNT(*) AS total_members
        FROM 
            members
        WHERE 
          
            national_id NOT LIKE 'old-%'
    ");

        return view('trends.current_members_by_gender', compact('genderCounts'));
    }



    public function membersByAgeGroup()
    {
        $ageGroups = DB::select("
            SELECT 
              CASE
                WHEN FLOOR(DATEDIFF(CURDATE(), date_of_birth)/365) BETWEEN 0 AND 20 THEN '0-20'
                WHEN FLOOR(DATEDIFF(CURDATE(), date_of_birth)/365) BETWEEN 21 AND 30 THEN '21-30'
                WHEN FLOOR(DATEDIFF(CURDATE(), date_of_birth)/365) BETWEEN 31 AND 40 THEN '31-40'
                WHEN FLOOR(DATEDIFF(CURDATE(), date_of_birth)/365) BETWEEN 41 AND 50 THEN '41-50'
                WHEN FLOOR(DATEDIFF(CURDATE(), date_of_birth)/365) BETWEEN 51 AND 60 THEN '51-60'
                WHEN FLOOR(DATEDIFF(CURDATE(), date_of_birth)/365) > 60 THEN '61+'
                ELSE 'Unknown'
              END AS age_group,
              COUNT(*) AS total_members
            FROM 
              members
            WHERE 
              national_id NOT LIKE 'old-%'
              AND date_of_birth IS NOT NULL
            GROUP BY 
              age_group

            UNION ALL

            SELECT 
              'Total' AS age_group,
              COUNT(*) AS total_members
            FROM 
              members
            WHERE 
              national_id NOT LIKE 'old-%'
              AND date_of_birth IS NOT NULL
        ");

       // return response()->json($ageGroups);
        return view('trends.members_by_age_group', compact('ageGroups'));
    }




    public function membershipGrowthTrends()
    {
        $growthTrends = DB::select("
            SELECT 
              YEAR(registration_date) AS year,
              COUNT(*) AS new_members
            FROM 
              members
            WHERE 
              registration_date >= DATE_SUB(CURDATE(), INTERVAL 5 YEAR)
              AND national_id NOT LIKE 'old-%'
            GROUP BY 
              YEAR(registration_date)

            UNION ALL

            SELECT 
              'Total' AS year,
              COUNT(*) AS new_members
            FROM 
              members
            WHERE 
              registration_date >= DATE_SUB(CURDATE(), INTERVAL 5 YEAR)
              AND national_id NOT LIKE 'old-%'

            ORDER BY 
              year ASC
        ");

          return view('trends.membership_growth_trends', compact('growthTrends'));
    }

    public function contributionRates(Request $request)
{
    $startDate = $request->input('start_date');
    $endDate = $request->input('end_date');

    $query = "
        SELECT 
          m.id AS member_id,
          CONCAT(m.surname, ' ', m.name) AS member_name,
          SUM(p.amount_paid) AS total_contribution,
          SUM(p.amount_paid) / SUM(p.months) AS average_per_month
        FROM 
          members m
        JOIN 
          premium_payments p ON p.member_id = m.id
        WHERE 
          m.national_id NOT LIKE 'old-%'
    ";

    if ($startDate && $endDate) {
        $query .= " AND p.payment_date BETWEEN ? AND ? ";
        $params = [$startDate, $endDate];
    } else {
        $params = [];
    }

    $query .= "
        GROUP BY 
          m.id, member_name
        ORDER BY 
          total_contribution DESC
    ";

    $contributionRates = DB::select($query, $params);

    return view('trends.contribution_rates', compact('contributionRates', 'startDate', 'endDate'));
}



   public function paymentPatterns(Request $request)
{
    $startDate = $request->input('start_date');
    $endDate = $request->input('end_date');

    $query = "
        SELECT 
          m.id AS member_id,
          CONCAT(m.surname, ' ', m.name) AS member_name,
          COUNT(p.id) AS total_payments,
          MIN(p.payment_date) AS first_payment_date,
          MAX(p.payment_date) AS last_payment_date,
          ROUND(COUNT(p.id) / (TIMESTAMPDIFF(MONTH, MIN(p.payment_date), MAX(p.payment_date)) + 1), 2) AS payments_per_month
        FROM 
          members m
        JOIN 
          premium_payments p ON p.member_id = m.id
        WHERE 
          m.national_id NOT LIKE 'old-%'
    ";

    $params = [];

    if ($startDate && $endDate) {
        $query .= " AND p.payment_date BETWEEN ? AND ? ";
        $params = [$startDate, $endDate];
    }

    $query .= "
        GROUP BY 
          m.id, member_name
        ORDER BY 
          total_payments DESC
    ";

    $paymentPatterns = DB::select($query, $params);

    return view('trends.payment_patterns', compact('paymentPatterns', 'startDate', 'endDate'));
}

}
