<?php

date_default_timezone_set('Africa/Lagos');

include "../includes/admin_auth.php";
include "../config/database.php";


/*
|--------------------------------------------------------------------------
| Search
|--------------------------------------------------------------------------
*/

$search = trim($_GET['q'] ?? '');


/*
|--------------------------------------------------------------------------
| Get All Scheme Subscriptions
|--------------------------------------------------------------------------
*/

$stmt = $pdo->query("
    SELECT

        s.id AS subscription_id,
        s.user_id,
        s.plan_id,
        s.hands,
        s.status AS subscription_status,
        s.created_at AS subscription_created_at,

        u.fullname,
        u.phone,

        p.plan_name,
        p.frequency,
        p.duration,
        p.start_month,
        p.start_year,

        sc.id AS clearance_id,
        sc.clearance_code,
        sc.total_defaults_settled,
        sc.clearance_date,
        sc.status AS clearance_status

    FROM subscriptions s

    INNER JOIN users u
        ON s.user_id = u.id

    INNER JOIN plans p
        ON s.plan_id = p.id

    LEFT JOIN scheme_clearances sc
        ON sc.subscription_id = s.id

    ORDER BY s.created_at DESC
");

$subscriptions =
    $stmt->fetchAll(PDO::FETCH_ASSOC);


$today = new DateTime('today');

$clearanceItems = [];


/*
|--------------------------------------------------------------------------
| Summary Counters
|--------------------------------------------------------------------------
*/

$completedCount = 0;

$awaitingCount = 0;

$readyCount = 0;

$ongoingCount = 0;

$totalDefaultsSettled = 0;


/*
|--------------------------------------------------------------------------
| Build Clearance Data
|--------------------------------------------------------------------------
*/

foreach ($subscriptions as $subscription) {

    $subscriptionId =
        (int)$subscription['subscription_id'];


    /*
    |--------------------------------------------------------------------------
    | Scheme Period
    |--------------------------------------------------------------------------
    */

    $startMonth =
        (int)($subscription['start_month'] ?? 0);

    $startYear =
        (int)($subscription['start_year'] ?? 0);

    $durationMonths =
        (int)($subscription['duration'] ?? 0);


    if ($durationMonths < 1) {

        $durationMonths = 1;

    }


    if (
        $startMonth >= 1 &&
        $startMonth <= 12 &&
        $startYear >= 2000
    ) {

        $schemeStart =
            new DateTime(
                sprintf(
                    '%04d-%02d-01',
                    $startYear,
                    $startMonth
                )
            );

    } else {

        $schemeStart =
            new DateTime(
                date(
                    'Y-m-01',
                    strtotime(
                        $subscription[
                            'subscription_created_at'
                        ]
                    )
                )
            );

    }


    $schemeEndExclusive =
        clone $schemeStart;

    $schemeEndExclusive->modify(
        '+' . $durationMonths . ' months'
    );


    $schemeLastDay =
        clone $schemeEndExclusive;

    $schemeLastDay->modify('-1 day');


    $schemeEnded =
        ($today >= $schemeEndExclusive);


    /*
    |--------------------------------------------------------------------------
    | Contribution Summary
    |--------------------------------------------------------------------------
    */

    $stmt = $pdo->prepare("
        SELECT

            COUNT(*) AS total_contributions,

            COALESCE(
                SUM(
                    CASE
                        WHEN status != 'paid'
                        THEN 1
                        ELSE 0
                    END
                ),
                0
            ) AS outstanding_count,

            COALESCE(
                SUM(
                    CASE
                        WHEN status != 'paid'
                        THEN amount
                        ELSE 0
                    END
                ),
                0
            ) AS outstanding_amount

        FROM contributions

        WHERE subscription_id = ?
    ");

    $stmt->execute([
        $subscriptionId
    ]);

    $contributionData =
        $stmt->fetch(PDO::FETCH_ASSOC);


    /*
    |--------------------------------------------------------------------------
    | Default Summary
    |--------------------------------------------------------------------------
    */

    $stmt = $pdo->prepare("
        SELECT

            COUNT(*) AS total_defaults,

            COALESCE(
                SUM(
                    CASE
                        WHEN status = 'unpaid'
                        THEN 1
                        ELSE 0
                    END
                ),
                0
            ) AS unpaid_count,

            COALESCE(
                SUM(
                    CASE
                        WHEN status = 'unpaid'
                        THEN amount
                        ELSE 0
                    END
                ),
                0
            ) AS unpaid_amount

        FROM subscription_defaults

        WHERE subscription_id = ?
    ");

    $stmt->execute([
        $subscriptionId
    ]);

    $defaultData =
        $stmt->fetch(PDO::FETCH_ASSOC);


    $outstandingContributions =
        (int)($contributionData['outstanding_count'] ?? 0);

    $outstandingContributionAmount =
        (float)($contributionData['outstanding_amount'] ?? 0);

    $unpaidDefaults =
        (int)($defaultData['unpaid_count'] ?? 0);

    $unpaidDefaultAmount =
        (float)($defaultData['unpaid_amount'] ?? 0);


    /*
    |--------------------------------------------------------------------------
    | Clearance State
    |--------------------------------------------------------------------------
    */

    if (!empty($subscription['clearance_id'])) {

        $state = 'completed';

        $completedCount++;

        $totalDefaultsSettled +=
            (float)$subscription['total_defaults_settled'];

    }

    elseif (!$schemeEnded) {

        $state = 'ongoing';

        $ongoingCount++;

    }

    elseif ($outstandingContributions > 0) {

        $state = 'contributions_outstanding';

        $awaitingCount++;

    }

    elseif ($unpaidDefaultAmount > 0) {

        $state = 'defaults_outstanding';

        $awaitingCount++;

    }

    else {

        $state = 'ready';

        $readyCount++;

        $awaitingCount++;

    }


    /*
    |--------------------------------------------------------------------------
    | Store
    |--------------------------------------------------------------------------
    */

    $clearanceItems[] = [

        'subscription' =>
            $subscription,

        'scheme_start' =>
            $schemeStart,

        'scheme_last_day' =>
            $schemeLastDay,

        'scheme_ended' =>
            $schemeEnded,

        'outstanding_contributions' =>
            $outstandingContributions,

        'outstanding_contribution_amount' =>
            $outstandingContributionAmount,

        'unpaid_defaults' =>
            $unpaidDefaults,

        'unpaid_default_amount' =>
            $unpaidDefaultAmount,

        'state' =>
            $state

    ];

}


/*
|--------------------------------------------------------------------------
| Search Filter
|--------------------------------------------------------------------------
*/

if ($search !== '') {

    $clearanceItems =
        array_filter(
            $clearanceItems,
            function ($item) use ($search) {

                $subscription =
                    $item['subscription'];

                $searchText =
                    $subscription['fullname']
                    . ' '
                    . $subscription['plan_name']
                    . ' '
                    . ($subscription['clearance_code'] ?? '')
                    . ' '
                    . ($subscription['phone'] ?? '');


                return stripos(
                    $searchText,
                    $search
                ) !== false;

            }
        );

}

?>

<!DOCTYPE html>

<html lang="en">

<head>

<meta charset="UTF-8">

<meta
    name="viewport"
    content="width=device-width, initial-scale=1.0"
>

<title>Clearance Management - Complete Bliss</title>


<link
    rel="stylesheet"
    href="../assets/css/admin.css"
>

<link
    rel="stylesheet"
    href="../assets/css/responsive.css"
>

<link
    rel="stylesheet"
    href="../assets/css/admin-clearance.css"
>

<link
    rel="stylesheet"
    href="../assets/css/admin-clearance-responsive.css"
>

<link
    rel="stylesheet"
    href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.7.2/css/all.min.css"
>

</head>

<body>


<?php include "../includes/admin_sidebar.php"; ?>


<div class="sidebar-overlay"></div>


<?php include "../includes/admin_topbar.php"; ?>


<div class="main-content">


<main class="admin-clearance-page">


    <!-- ======================================
         HEADER
    ======================================= -->

<div class="admin-clearance-header">

    <div>

        <h1>

            <i class="fas fa-clipboard-check"></i>

            Scheme Clearance

        </h1>

        <p>
            Monitor member clearance progress,
            completed clearances and verification codes.
        </p>

    </div>


    <a
        href="verify_clearance.php"
        class="clearance-verify-btn"
    >

        <i class="fas fa-shield-halved"></i>

        Verify Clearance Code

    </a>

</div>



    <!-- ======================================
         SUMMARY
    ======================================= -->

    <div class="admin-clearance-summary-grid">


        <!-- COMPLETED -->

        <div class="admin-clearance-summary-card">

            <div class="admin-clearance-summary-icon completed">

                <i class="fas fa-circle-check"></i>

            </div>

            <div>

                <span>
                    Completed Clearances
                </span>

                <h2>
                    <?= $completedCount; ?>
                </h2>

            </div>

        </div>



        <!-- AWAITING -->

        <div class="admin-clearance-summary-card">

            <div class="admin-clearance-summary-icon awaiting">

                <i class="fas fa-hourglass-half"></i>

            </div>

            <div>

                <span>
                    Awaiting Clearance
                </span>

                <h2>
                    <?= $awaitingCount; ?>
                </h2>

            </div>

        </div>



        <!-- READY -->

        <div class="admin-clearance-summary-card">

            <div class="admin-clearance-summary-icon ready">

                <i class="fas fa-user-check"></i>

            </div>

            <div>

                <span>
                    Ready to Clear
                </span>

                <h2>
                    <?= $readyCount; ?>
                </h2>

            </div>

        </div>



        <!-- DEFAULTS SETTLED -->

        <div class="admin-clearance-summary-card">

            <div class="admin-clearance-summary-icon money">

                <i class="fas fa-naira-sign"></i>

            </div>

            <div>

                <span>
                    Defaults Settled
                </span>

                <h2>

                    ₦<?= number_format(
                        $totalDefaultsSettled,
                        2
                    ); ?>

                </h2>

            </div>

        </div>


    </div>



    <!-- ======================================
         SEARCH
    ======================================= -->

    <section class="admin-clearance-search-card">

        <form
            method="GET"
            action=""
            class="admin-clearance-search-form"
        >

            <div class="admin-clearance-search-box">

                <i class="fas fa-magnifying-glass"></i>

                <input
                    type="text"
                    name="q"
                    placeholder="Search member, scheme, phone or clearance code..."
                    value="<?= htmlspecialchars($search); ?>"
                >

            </div>


            <button type="submit">

                Search

            </button>


            <?php if ($search !== ''): ?>

                <a
                    href="clearance.php"
                    class="clear-search-btn"
                >

                    Clear

                </a>

            <?php endif; ?>


        </form>

    </section>



    <!-- ======================================
         CLEARANCE RECORDS
    ======================================= -->

    <section class="admin-clearance-record-card">


        <div class="admin-clearance-record-header">

            <div>

                <h2>
                    Member Clearance Records
                </h2>

                <p>
                    <?= count($clearanceItems); ?>
                    record<?= count($clearanceItems) === 1 ? '' : 's'; ?>
                    displayed.
                </p>

            </div>

        </div>



        <?php if (count($clearanceItems) > 0): ?>


        <div class="admin-clearance-table-wrap">


            <table class="admin-clearance-table">


                <thead>

                    <tr>

                        <th>Member</th>

                        <th>Scheme</th>

                        <th>Scheme Period</th>

                        <th>Contributions</th>

                        <th>Defaults</th>

                        <th>Clearance Status</th>

                        <th>Clearance Code</th>

                        <th>Clearance Date</th>

                    </tr>

                </thead>


                <tbody>


                <?php foreach ($clearanceItems as $item): ?>


                <?php

                $scheme =
                    $item['subscription'];

                $state =
                    $item['state'];

                ?>


                <tr>


                    <!-- MEMBER -->

                    <td>

                        <div class="admin-clearance-member">

                            <div class="admin-clearance-member-icon">

                                <i class="fas fa-user"></i>

                            </div>

                            <div>

                                <strong>

                                    <?= htmlspecialchars(
                                        $scheme['fullname']
                                    ); ?>

                                </strong>

                                <small>

                                    <?= htmlspecialchars(
                                        $scheme['phone'] ?? ''
                                    ); ?>

                                </small>

                            </div>

                        </div>

                    </td>



                    <!-- SCHEME -->

                    <td>

                        <strong>

                            <?= htmlspecialchars(
                                $scheme['plan_name']
                            ); ?>

                        </strong>

                        <small class="admin-clearance-subtext">

                            <?= (int)$scheme['hands']; ?>

                            <?= ((int)$scheme['hands'] === 1)
                                ? 'Hand'
                                : 'Hands'; ?>

                            ·

                            <?= htmlspecialchars(
                                ucfirst(
                                    $scheme['frequency']
                                )
                            ); ?>

                        </small>

                    </td>



                    <!-- PERIOD -->

                    <td>

                        <strong>

                            <?= $item[
                                'scheme_start'
                            ]->format('M Y'); ?>

                            –

                            <?= $item[
                                'scheme_last_day'
                            ]->format('M Y'); ?>

                        </strong>

                        <small class="admin-clearance-subtext">

                            Ends:

                            <?= $item[
                                'scheme_last_day'
                            ]->format('d M Y'); ?>

                        </small>

                    </td>



                    <!-- CONTRIBUTIONS -->

                    <td>

                        <?php if (
                            $item['outstanding_contributions'] === 0
                        ): ?>

                            <span class="admin-clearance-check good">

                                <i class="fas fa-check"></i>

                                Settled

                            </span>

                        <?php else: ?>

                            <span class="admin-clearance-check bad">

                                <?= $item['outstanding_contributions']; ?>

                                Outstanding

                            </span>

                            <small class="admin-clearance-subtext danger-text">

                                ₦<?= number_format(
                                    $item[
                                        'outstanding_contribution_amount'
                                    ],
                                    2
                                ); ?>

                            </small>

                        <?php endif; ?>

                    </td>



                    <!-- DEFAULTS -->

                    <td>

                        <?php if (
                            $item['unpaid_default_amount'] <= 0
                        ): ?>

                            <span class="admin-clearance-check good">

                                <i class="fas fa-check"></i>

                                Settled

                            </span>

                        <?php else: ?>

                            <span class="admin-clearance-check bad">

                                <?= $item['unpaid_defaults']; ?>

                                Unpaid

                            </span>

                            <small class="admin-clearance-subtext danger-text">

                                ₦<?= number_format(
                                    $item['unpaid_default_amount'],
                                    2
                                ); ?>

                            </small>

                        <?php endif; ?>

                    </td>



                    <!-- STATUS -->

                    <td>

                        <?php if ($state === 'completed'): ?>

                            <span class="admin-clearance-status completed">

                                <i class="fas fa-circle-check"></i>

                                Completed

                            </span>

                        <?php elseif ($state === 'ready'): ?>

                            <span class="admin-clearance-status ready">

                                <i class="fas fa-check"></i>

                                Ready

                            </span>

                        <?php elseif ($state === 'ongoing'): ?>

                            <span class="admin-clearance-status ongoing">

                                <i class="fas fa-clock"></i>

                                Ongoing

                            </span>

                        <?php elseif (
                            $state === 'defaults_outstanding'
                        ): ?>

                            <span class="admin-clearance-status action">

                                Defaults Owed

                            </span>

                        <?php else: ?>

                            <span class="admin-clearance-status action">

                                Contributions Owed

                            </span>

                        <?php endif; ?>

                    </td>



                    <!-- CODE -->

                    <td>

                        <?php if (
                            !empty($scheme['clearance_code'])
                        ): ?>

                            <span class="admin-clearance-code">

                                <?= htmlspecialchars(
                                    $scheme['clearance_code']
                                ); ?>

                            </span>

                        <?php else: ?>

                            <span class="admin-clearance-empty-value">

                                —

                            </span>

                        <?php endif; ?>

                    </td>



                    <!-- DATE -->

                    <td>

                        <?php if (
                            !empty($scheme['clearance_date'])
                        ): ?>

                            <?= date(
                                'd M Y',
                                strtotime(
                                    $scheme['clearance_date']
                                )
                            ); ?>

                        <?php else: ?>

                            <span class="admin-clearance-empty-value">

                                —

                            </span>

                        <?php endif; ?>

                    </td>


                </tr>


                <?php endforeach; ?>


                </tbody>


            </table>


        </div>


        <?php else: ?>


        <div class="admin-clearance-empty">

            <div class="admin-clearance-empty-icon">

                <i class="fas fa-magnifying-glass"></i>

            </div>

            <h3>
                No Clearance Records Found
            </h3>

            <p>

                <?php if ($search !== ''): ?>

                    No record matches your search.

                <?php else: ?>

                    There are currently no scheme subscriptions.

                <?php endif; ?>

            </p>

        </div>


        <?php endif; ?>


    </section>


</main>


</div>


<script src="../assets/js/admin.js"></script>


</body>

</html>