<?php

declare(strict_types=1);

require_once __DIR__ . '/../library/auth.php';

allow_roles(['admin', 'accountant', 'teacher']);

$school_id = (int) $_SESSION['school_id'];

$current_session_id = get_current_session_id($pdo);

/*
|--------------------------------------------------------------------------
| VALID REPORT TYPES
|--------------------------------------------------------------------------
*/

$allowed_types = [

    'class',
    'category',
    'religion',
    'documents',
    'document_missing',
    'missing_documents'

];

$type = trim($_GET['type'] ?? '');

$class_id = (int) ($_GET['class_id'] ?? 0);

/*
|--------------------------------------------------------------------------
| VALIDATION
|--------------------------------------------------------------------------
*/

if (!in_array($type, $allowed_types, true)) {

    exit('Invalid report type.');
}

if ($class_id <= 0) {

    exit('Invalid class.');
}

/*
|--------------------------------------------------------------------------
| FETCH CLASS
|--------------------------------------------------------------------------
*/

$classStmt = $pdo->prepare("

    SELECT

        class_name,
        section

    FROM classes

    WHERE id = ?
    AND school_id = ?

    LIMIT 1

");

$classStmt->execute([

    $class_id,
    $school_id

]);

$class =
    $classStmt->fetch(PDO::FETCH_ASSOC);

if (!$class) {

    exit('Class not found.');
}

/*
|--------------------------------------------------------------------------
| FILE NAME
|--------------------------------------------------------------------------
*/

$file_name =

    $type .
    '_report_' .
    date('Ymd_His') .
    '.csv';

/*
|--------------------------------------------------------------------------
| CSV HEADERS
|--------------------------------------------------------------------------
*/

header('Content-Type: text/csv');

header(
    'Content-Disposition: attachment; filename="' .
    $file_name .
    '"'
);

/*
|--------------------------------------------------------------------------
| OPEN OUTPUT
|--------------------------------------------------------------------------
*/

$output = fopen('php://output', 'w');

if (!$output) {

    exit('Unable to generate report.');
}

/*
|--------------------------------------------------------------------------
| CLASS REPORT
|--------------------------------------------------------------------------
*/

if ($type === 'class') {

    fputcsv($output, [

        'Roll No',
        'Student Name',
        'Gender',
        'Father Name',
        'Phone',
        'Category',
        'Religion',
        'Aadhaar',
        'APAAR',
        'Samagra',
        'Transport Route',
        'Status'

    ]);

    $stmt = $pdo->prepare("

        SELECT

            ss.roll_no,

            s.name,
            s.gender,
            s.phone,

            sp.father_name,

            sci.category,
            sci.religion,

            sd.aadhaar_no,
            sd.apaar_id,
            sd.samagra_id,

            st.transport_route,

            ss.status

        FROM student_sessions ss

        INNER JOIN students s
            ON s.id = ss.student_id

        LEFT JOIN student_parents sp
            ON sp.student_id = s.id

        LEFT JOIN student_documents sd
            ON sd.student_id = s.id

        LEFT JOIN student_category_info sci
            ON sci.student_id = s.id

        LEFT JOIN student_transport st
            ON st.student_id = s.id

        WHERE ss.school_id = ?
        AND ss.session_id = ?
        AND ss.class_id = ?

        ORDER BY
        ss.roll_no ASC,
        s.name ASC

    ");

    $stmt->execute([

        $school_id,
        $current_session_id,
        $class_id

    ]);

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

        fputcsv($output, [

            $row['roll_no'],
            $row['name'],
            $row['gender'],
            $row['father_name'],
            $row['phone'],
            $row['category'],
            $row['religion'],
            $row['aadhaar_no'],
            $row['apaar_id'],
            $row['samagra_id'],
            $row['transport_route'],
            $row['status']

        ]);
    }
}

/*
|--------------------------------------------------------------------------
| CATEGORY REPORT
|--------------------------------------------------------------------------
*/

elseif ($type === 'category') {

    fputcsv($output, [

        'Category',
        'Total Students'

    ]);

    $stmt = $pdo->prepare("

        SELECT

            COALESCE(
                NULLIF(sci.category, ''),
                'Unknown'
            ) AS category,

            COUNT(*) AS total_students

        FROM student_sessions ss

        INNER JOIN students s
            ON s.id = ss.student_id

        LEFT JOIN student_category_info sci
            ON sci.student_id = s.id

        WHERE ss.school_id = ?
        AND ss.session_id = ?
        AND ss.class_id = ?
        AND ss.status = 'Active'

        GROUP BY category

        ORDER BY total_students DESC

    ");

    $stmt->execute([

        $school_id,
        $current_session_id,
        $class_id

    ]);

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

        fputcsv($output, [

            $row['category'],
            $row['total_students']

        ]);
    }
}

/*
|--------------------------------------------------------------------------
| RELIGION REPORT
|--------------------------------------------------------------------------
*/

elseif ($type === 'religion') {

    fputcsv($output, [

        'Religion',
        'Total Students'

    ]);

    $stmt = $pdo->prepare("

        SELECT

            COALESCE(
                NULLIF(sci.religion, ''),
                'Unknown'
            ) AS religion,

            COUNT(*) AS total_students

        FROM student_sessions ss

        INNER JOIN students s
            ON s.id = ss.student_id

        LEFT JOIN student_category_info sci
            ON sci.student_id = s.id

        WHERE ss.school_id = ?
        AND ss.session_id = ?
        AND ss.class_id = ?
        AND ss.status = 'Active'

        GROUP BY religion

        ORDER BY total_students DESC

    ");

    $stmt->execute([

        $school_id,
        $current_session_id,
        $class_id

    ]);

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

        fputcsv($output, [

            $row['religion'],
            $row['total_students']

        ]);
    }
}

/*
|--------------------------------------------------------------------------
| DOCUMENT REPORT
|--------------------------------------------------------------------------
*/

elseif (

    $type === 'documents'
    || $type === 'document_missing'
    || $type === 'missing_documents'

) {

    fputcsv($output, [

        'Roll No',
        'Student Name',
        'Father Name',
        'Phone',
        'Aadhaar',
        'APAAR',
        'Samagra',
        'Missing Documents'

    ]);

    $stmt = $pdo->prepare("

        SELECT

            ss.roll_no,

            s.name,
            s.phone,

            sp.father_name,

            sd.aadhaar_no,
            sd.apaar_id,
            sd.samagra_id

        FROM student_sessions ss

        INNER JOIN students s
            ON s.id = ss.student_id

        LEFT JOIN student_parents sp
            ON sp.student_id = s.id

        LEFT JOIN student_documents sd
            ON sd.student_id = s.id

        WHERE ss.school_id = ?
        AND ss.session_id = ?
        AND ss.class_id = ?
        AND ss.status = 'Active'

        AND (

            sd.aadhaar_no IS NULL
            OR sd.aadhaar_no = ''

            OR sd.apaar_id IS NULL
            OR sd.apaar_id = ''

            OR sd.samagra_id IS NULL
            OR sd.samagra_id = ''

        )

        ORDER BY
        ss.roll_no ASC,
        s.name ASC

    ");

    $stmt->execute([

        $school_id,
        $current_session_id,
        $class_id

    ]);

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

        $missing = [];

        if (empty($row['aadhaar_no'])) {

            $missing[] = 'Aadhaar';
        }

        if (empty($row['apaar_id'])) {

            $missing[] = 'APAAR';
        }

        if (empty($row['samagra_id'])) {

            $missing[] = 'Samagra';
        }

        fputcsv($output, [

            $row['roll_no'],
            $row['name'],
            $row['father_name'],
            $row['phone'],
            $row['aadhaar_no'],
            $row['apaar_id'],
            $row['samagra_id'],
            implode(', ', $missing)

        ]);
    }
}

/*
|--------------------------------------------------------------------------
| CLOSE STREAM
|--------------------------------------------------------------------------
*/

fclose($output);

exit;