<?php

/**

 * Action: Send weekly feeding/canteen summary notifications to parents.

 * Pulls from canteen_scans (canteen module) when active, falls back to weekly_feeding_payments.

 */



if (!defined('DB_HOST')) {

    require_once __DIR__ . '/../../config.php';

}

require_once __DIR__ . '/../provider_engine.php';



function action_send_weekly_feeding_summary(array $params): array

{

    $conn        = getDBConnection();

    $week_start  = $params['week_start'] ?? date('Y-m-d', strtotime('monday this week'));

    $week_end    = $params['week_end']   ?? date('Y-m-d', strtotime('friday this week'));

    $school_name = getSettingValue('school_name') ?: 'the school';



    $notifications_sent = 0;

    $data_source        = 'legacy';



    // ── Canteen Module path: use canteen_scans ────────────────────────────

    $canteen_available = false;

    $r = $conn->query("SHOW TABLES LIKE 'canteen_scans'");

    if ($r && $r->num_rows > 0) $canteen_available = true;



    if ($canteen_available) {

        $data_source = 'canteen_scans';



        $stmt = $conn->prepare(

            "SELECT s.id AS student_id,

                    s.first_name, s.last_name, s.class,

                    COALESCE(s.father_phone, s.mother_phone) AS parent_phone,

                    COALESCE(s.father_name, s.mother_name, 'Parent') AS parent_name,

                    COUNT(DISTINCT cscan.id)                 AS meals_confirmed,

                    COUNT(DISTINCT ce.id)                    AS meals_enrolled,

                    SUM(DISTINCT ce.status = 'absent')       AS meals_absent

             FROM students s

             JOIN canteen_entitlements ce ON ce.student_id = s.id

             JOIN canteen_sessions cs     ON cs.id = ce.session_id

             LEFT JOIN canteen_scans cscan

                   ON cscan.student_id = s.id

                   AND cscan.session_id = cs.id

                   AND cscan.scan_result = 'success'

             WHERE cs.session_date BETWEEN ? AND ? AND cs.status != 'cancelled'

               AND (s.father_phone IS NOT NULL OR s.mother_phone IS NOT NULL)

             GROUP BY s.id

             ORDER BY s.class, s.first_name"

        );

        $stmt->bind_param('ss', $week_start, $week_end);

        $stmt->execute();

        $rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

        $stmt->close();



        foreach ($rows as $row) {

            if (empty($row['parent_phone'])) continue;



            $student_name = $row['first_name'] . ' ' . $row['last_name'];

            $confirmed    = (int) $row['meals_confirmed'];

            $enrolled     = (int) $row['meals_enrolled'];

            $absent       = (int) $row['meals_absent'];



            $week_label   = date('d M', strtotime($week_start)) . '–' . date('d M', strtotime($week_end));

            $absent_note  = $absent > 0 ? ", {$absent} absent" : '';

            $msg = "[{$school_name}] Dear {$row['parent_name']},\nWeekly canteen report for {$student_name} ({$row['class']}):\n"

                 . "{$week_label}: {$confirmed} of {$enrolled} meals collected{$absent_note}.\nThank you.";



            if (strlen($msg) > 320) $msg = substr($msg, 0, 317) . '...';



            if (function_exists('sendSMS') && sendSMS($row['parent_phone'], $msg)) {

                $notifications_sent++;

            }



            // Log notification if table exists

            $notif_stmt = $conn->prepare(

                "INSERT INTO parent_notifications

                 (parent_id, title, message, notification_type, is_sent, created_at)

                 SELECT pa.id, 'Weekly Canteen Summary', ?, 'sms', 1, NOW()

                 FROM parent_accounts pa

                 JOIN parent_student_link psl ON psl.parent_id = pa.id

                 WHERE psl.student_id = ? LIMIT 1"

            );

            if ($notif_stmt) {

                $notif_stmt->bind_param('si', $msg, $row['student_id']);

                $notif_stmt->execute();

            }

        }



        return [

            'success'            => true,

            'data_source'        => $data_source,

            'notifications_sent' => $notifications_sent,

            'week_start'         => $week_start,

            'week_end'           => $week_end,

            'students_covered'   => count($rows),

        ];

    }



    // ── Legacy path: weekly_feeding_payments ─────────────────────────────

    $sql = "SELECT wfp.student_id, s.first_name, s.last_name, s.class,

                   pa.phone AS parent_phone, COALESCE(pa.parent_name, pa.full_name) AS parent_name,

                   COALESCE(SUM(wfp.amount), SUM(wfp.amount_paid), 0) AS total_paid,

                   COUNT(wfp.id) AS days_paid

            FROM weekly_feeding_payments wfp

            JOIN students s ON s.id = wfp.student_id

            LEFT JOIN parent_student_link psl ON psl.student_id = wfp.student_id

            LEFT JOIN parent_accounts pa ON pa.id = psl.parent_id

            WHERE DATE(wfp.paid_at) BETWEEN ? AND ?

            GROUP BY wfp.student_id, pa.id";



    $stmt = $conn->prepare($sql);

    if (!$stmt) {

        return [

            'success'            => true,

            'notifications_sent' => 0,

            'week_start'         => $week_start,

            'week_end'           => $week_end,

            'note'               => 'No feeding data source found.',

        ];

    }

    $stmt->bind_param('ss', $week_start, $week_end);

    $stmt->execute();

    $rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);



    foreach ($rows as $row) {

        if (empty($row['parent_phone'])) continue;



        $student_name = $row['first_name'] . ' ' . $row['last_name'];

        $total        = number_format((float) $row['total_paid'], 2);

        $days         = (int) $row['days_paid'];

        $week_label   = date('d M', strtotime($week_start)) . '–' . date('d M', strtotime($week_end));



        $msg = "[{$school_name}] Dear Parent/Guardian,\nWeekly feeding update for {$student_name} ({$row['class']}):\n"

             . "{$week_label}: {$days} day(s) paid — GHS {$total}.\nThank you.";



        if (strlen($msg) > 320) $msg = substr($msg, 0, 317) . '...';



        if (function_exists('sendSMS') && sendSMS($row['parent_phone'], $msg)) {

            $notifications_sent++;

        }



        $notif_stmt = $conn->prepare(

            "INSERT INTO parent_notifications

             (parent_id, title, message, notification_type, is_sent, created_at)

             SELECT pa.id, 'Weekly Feeding Summary', ?, 'sms', 1, NOW()

             FROM parent_accounts pa

             JOIN parent_student_link psl ON psl.parent_id = pa.id

             WHERE psl.student_id = ? LIMIT 1"

        );

        if ($notif_stmt) {

            $notif_stmt->bind_param('si', $msg, $row['student_id']);

            $notif_stmt->execute();

        }

    }



    return [

        'success'            => true,

        'data_source'        => $data_source,

        'notifications_sent' => $notifications_sent,

        'week_start'         => $week_start,

        'week_end'           => $week_end,

        'students_covered'   => count($rows),

    ];

}

