<?php
require_once __DIR__ . '/../init.php';
require_once PROJECT_ROOT . '/backend/controllers/common_function.php';
require_once PROJECT_ROOT . '/backend/controllers/db_select_helper.php';

// include other controller
require_once PROJECT_ROOT . '/backend/controllers/ActivityController.php';
require_once PROJECT_ROOT . '/backend/controllers/UserAksesController.php';

class MembershipController
{
    private $conn;
    private $table = "
        membership M
        LEFT JOIN player P ON M.IDPlayer = P.IDPlayer
    ";
    private $pk = "IDMembership";
    private $sv = "Membership";

    protected $displayedColumn = [
        'M.IDMembership',
        'M.IDPlayer',
        'P.full_name',
        'P.email',
        'M.MembershipNumber',
        'M.status',
        'M.start_date',
        'M.end_date',
        'M.CreateBy',
        'M.CreateAt',
        'M.LastUpdateBy',
        'M.LastUpdateAt'
    ];

    public function __construct($conn)
    {
        $this->conn = $conn;
        $this->activityController = new ActivityController($this->conn);
        $this->userAksesController = new UserAksesController($this->conn);
    }

    public function select($post)
    {
        $listExclude = [];

        $listSearch = [
            'M.MembershipNumber',
            'P.full_name',
            'P.email',
            'M.status'
        ];

        return runSelect(
            $this->conn,
            $this->table,
            $this->pk,
            $this->displayedColumn,
            $post,
            $listExclude,
            $listSearch
        );
    }

    public function select_miniDashboard()
    {
        // === SUMMARY (TOTAL SAJA) ===
        $sql = "
        SELECT
            COUNT(CASE WHEN status = 'active' THEN 1 END) AS total_membership_active,
            COUNT(CASE 
                WHEN status = 'active'
                 AND end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)
                THEN 1
            END) AS total_membership_expiring
        FROM membership
    ";

        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $summary = $stmt->get_result()->fetch_assoc();

        // === GET DETAIL DATA FROM OTHER FUNCTIONS ===
        $expiredList = $this->select_datamauexpired();
        $pendingList = $this->select_paymentpending();

        return [
            "Status" => 1,
            "Keterangan" => "",
            "list_data" => [
                "summary" => [
                    "total_membership_active" => (int) $summary['total_membership_active'],
                    "total_membership_expiring" => (int) $summary['total_membership_expiring'],
                    "total_payment_pending" => count($pendingList)
                ],
                "expired_list" => $expiredList,
                "payment_pending_list" => $pendingList
            ]
        ];
    }


    public function select_datamauexpired()
    {
        $sql = "
        SELECT
            M.IDMembership,
            M.MembershipNumber,
            M.start_date,
            M.end_date,
            DATEDIFF(M.end_date, CURDATE()) AS sisa_hari,

            P.IDPlayer,
            P.full_name,
            P.email,
            P.phone
        FROM membership M
        INNER JOIN player P ON P.IDPlayer = M.IDPlayer
        WHERE M.status = 'active'
          AND M.end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)
        ORDER BY M.end_date ASC
    ";

        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $result = $stmt->get_result();

        $data = [];
        while ($row = $result->fetch_assoc()) {
            $data[] = $row;
        }

        return $data;
    }

    public function select_paymentpending()
    {
        $sql = "
        SELECT
            MP.IDMembershipPayment,
            MP.amount,
            MP.payment_method,
            MP.payment_proof,
            MP.status,
            MP.LastUpdateAt,

            P.IDPlayer,
            P.full_name,
            P.email,
            P.phone
        FROM membership_payment MP
        INNER JOIN player P ON P.IDPlayer = MP.IDPlayer
        WHERE MP.status = 'waiting'
        ORDER BY MP.LastUpdateAt DESC
    ";

        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $result = $stmt->get_result();

        $data = [];
        while ($row = $result->fetch_assoc()) {
            $data[] = $row;
        }

        return $data;
    }

}
?>