<?php
// api/business_department.php
// PostgreSQL API for Business Department enrollment and promotion performance.
declare(strict_types=1);
ini_set('display_errors', '0');
header('Content-Type: application/json; charset=UTF-8');
header('Cache-Control: no-store, no-cache, must-revalidate');

function businessRespond(int $status, bool $ok, string $message, array $data = []): void
{
    http_response_code($status);
    echo json_encode(
        ['ok' => $ok, 'message' => $message, 'data' => $data],
        JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE
    );
    exit;
}

function businessInput(): array
{
    $input = json_decode(file_get_contents('php://input') ?: '{}', true);
    return is_array($input) ? $input : [];
}

function businessId($value): int
{
    return filter_var($value, FILTER_VALIDATE_INT, ['options' => ['min_range' => 1]]) ?: 0;
}

function businessYear($value): int
{
    return filter_var($value, FILTER_VALIDATE_INT, ['options' => ['min_range' => 2000, 'max_range' => 2200]]) ?: 0;
}

try {
    // These are the same shared PostgreSQL connection and bearer-session files
    // used by the other APIs in this project.
    require_once __DIR__ . '/../config/database.php';
    require_once __DIR__ . '/session_auth.php';

    if (!isset($pdo) || !($pdo instanceof PDO)) {
        throw new RuntimeException('PostgreSQL database connection is not available.');
    }

    $user = rvu_current_user($pdo);
    if (!$user) {
        businessRespond(401, false, 'Your session is missing or expired. Please sign in again.');
    }

    // Any authenticated account may use this department workspace.

    $method = strtoupper((string)($_SERVER['REQUEST_METHOD'] ?? 'GET'));
    $action = trim((string)($_GET['action'] ?? ''));

    if ($method === 'GET' && $action === 'overview') {
        $schoolId = businessId($_GET['school_id'] ?? 0);
        $programId = businessId($_GET['program_id'] ?? 0);
        $year = ($_GET['year'] ?? '') === '' ? 0 : businessYear($_GET['year']);
        if (($_GET['year'] ?? '') !== '' && $year === 0) {
            businessRespond(422, false, 'Enter a valid intake year.');
        }

        $schools = $pdo->query(
            'SELECT id, school_name, school_code FROM biz_schools WHERE is_active = TRUE ORDER BY school_name'
        )->fetchAll(PDO::FETCH_ASSOC);
        $programs = $pdo->query(
            'SELECT p.id, p.school_id, p.program_name, p.program_code, s.school_name
             FROM biz_programs p
             JOIN biz_schools s ON s.id = p.school_id
             WHERE p.is_active = TRUE AND s.is_active = TRUE
             ORDER BY s.school_name, p.program_name'
        )->fetchAll(PDO::FETCH_ASSOC);

        // Build bound filters so the query never inserts user input into SQL.
        $conditions = [];
        $params = [];
        if ($schoolId > 0) { $conditions[] = 's.id = ?'; $params[] = $schoolId; }
        if ($programId > 0) { $conditions[] = 'p.id = ?'; $params[] = $programId; }
        if ($year > 0) { $conditions[] = 'e.intake_year = ?'; $params[] = $year; }
        $where = $conditions ? ' WHERE ' . implode(' AND ', $conditions) : '';
        $joins = ' FROM biz_enrollments e
                   JOIN biz_programs p ON p.id = e.program_id
                   JOIN biz_schools s ON s.id = p.school_id';

        $stmt = $pdo->prepare(
            'SELECT e.id, p.id AS program_id, s.id AS school_id, s.school_name,
                    p.program_name, p.program_code, e.intake_name, e.intake_year,
                    e.male_count, e.female_count, (e.male_count + e.female_count) AS total_count' .
            $joins . $where . ' ORDER BY e.intake_year DESC, e.id DESC'
        );
        $stmt->execute($params);
        $enrollments = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Every active program intake is automatically available for results.
        // Programs and cohorts are loaded from the database, never hard-coded.
        $performanceIntakes = $pdo->query(
            'SELECT e.id AS enrollment_id, p.id AS program_id, s.id AS school_id,
                    s.school_name, p.program_name, p.program_code,
                    e.intake_name, e.intake_year,
                    (e.intake_year + 1) AS promotion_year,
                    e.male_count, e.female_count,
                    (e.male_count + e.female_count) AS total_count,
                    (perf.id IS NOT NULL) AS has_performance,
                    COALESCE(perf.students_sat, 0) AS students_sat,
                    COALESCE(perf.students_passed, 0) AS students_passed,
                    COALESCE(perf.students_failed, 0) AS students_failed,
                    perf.updated_at AS performance_updated_at
             FROM biz_enrollments e
             JOIN biz_programs p ON p.id = e.program_id AND p.is_active = TRUE
             JOIN biz_schools s ON s.id = p.school_id AND s.is_active = TRUE
             LEFT JOIN biz_student_performance perf
               ON perf.program_id = e.program_id
              AND perf.intake_year = e.intake_year
              AND perf.intake_name = e.intake_name
             ORDER BY e.intake_year DESC, s.school_name, p.program_name, e.intake_name'
        )->fetchAll(PDO::FETCH_ASSOC);
        foreach ($performanceIntakes as &$row) {
            $row['has_performance'] = in_array($row['has_performance'], [true, 't', 1, '1'], true);
        }
        unset($row);

        $stmt = $pdo->prepare(
            'SELECT e.intake_year, e.intake_name,
                    SUM(e.male_count) AS male_count,
                    SUM(e.female_count) AS female_count,
                    SUM(e.male_count + e.female_count) AS total_count' .
            $joins . $where . '
             GROUP BY e.intake_year, e.intake_name
             ORDER BY e.intake_year, MIN(e.id)'
        );
        $stmt->execute($params);
        $trend = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach ($trend as &$row) {
            $row['label'] = $row['intake_name'] . ' ' . $row['intake_year'];
        }
        unset($row);

        $currentYear = $year;
        if ($currentYear === 0) {
            $latest = $pdo->query('SELECT MAX(intake_year) FROM biz_enrollments')->fetchColumn();
            $currentYear = $latest === false || $latest === null ? (int)date('Y') : (int)$latest;
        }
        $previousYear = $currentYear - 1;
        $yearTotals = [];
        foreach ([$currentYear, $previousYear] as $checkYear) {
            $yearConditions = ['e.intake_year = ?'];
            $yearParams = [$checkYear];
            if ($schoolId > 0) { $yearConditions[] = 's.id = ?'; $yearParams[] = $schoolId; }
            if ($programId > 0) { $yearConditions[] = 'p.id = ?'; $yearParams[] = $programId; }
            $stmt = $pdo->prepare(
                'SELECT COALESCE(SUM(e.male_count), 0) AS male,
                        COALESCE(SUM(e.female_count), 0) AS female' . $joins .
                ' WHERE ' . implode(' AND ', $yearConditions)
            );
            $stmt->execute($yearParams);
            $yearTotals[$checkYear] = $stmt->fetch(PDO::FETCH_ASSOC) ?: ['male' => 0, 'female' => 0];
        }

        $male = (int)$yearTotals[$currentYear]['male'];
        $female = (int)$yearTotals[$currentYear]['female'];
        $currentTotal = $male + $female;
        $previousTotal = (int)$yearTotals[$previousYear]['male'] + (int)$yearTotals[$previousYear]['female'];
        $growth = $previousTotal === 0 ? null : (($currentTotal - $previousTotal) / $previousTotal) * 100;
        $direction = $previousTotal === 0
            ? ($currentTotal > 0 ? 'new' : 'stable')
            : ($currentTotal > $previousTotal ? 'increasing' : ($currentTotal < $previousTotal ? 'decreasing' : 'stable'));

        $years = $pdo->query(
            'SELECT DISTINCT intake_year FROM biz_enrollments ORDER BY intake_year DESC'
        )->fetchAll(PDO::FETCH_COLUMN);
        $years = array_map('intval', $years);

        businessRespond(200, true, 'Business Department data loaded.', [
            'schools' => $schools,
            'programs' => $programs,
            'enrollments' => $enrollments,
            'performance_intakes' => $performanceIntakes,
            'trend' => $trend,
            'years' => $years,
            'summary' => [
                'total_students' => $currentTotal,
                'male_students' => $male,
                'female_students' => $female,
                'current_year' => $currentYear,
                'previous_year' => $previousYear,
                'growth_percent' => $growth === null ? null : round($growth, 1),
                'growth_direction' => $direction,
                'school_count' => count($schools),
                'program_count' => count($programs),
            ],
        ]);
    }

    if ($method !== 'POST') {
        businessRespond(405, false, 'Unsupported request method.');
    }

    $input = businessInput();
    $createdBy = businessId($user['id'] ?? 0) ?: null;

    if ($action === 'create_school') {
        $name = trim((string)($input['school_name'] ?? ''));
        $code = trim((string)($input['school_code'] ?? ''));
        if ($name === '' || mb_strlen($name) > 150 || mb_strlen($code) > 30) {
            businessRespond(422, false, 'Enter a school name (up to 150 characters).');
        }
        $stmt = $pdo->prepare(
            'INSERT INTO biz_schools (school_name, school_code, created_by)
             VALUES (?, NULLIF(?, \'\'), ?) RETURNING id'
        );
        $stmt->execute([$name, $code, $createdBy]);
        businessRespond(201, true, 'School created.', ['id' => (int)$stmt->fetchColumn()]);
    }

    if ($action === 'update_school') {
        $schoolId = businessId($input['school_id'] ?? 0);
        $name = trim((string)($input['school_name'] ?? ''));
        $code = trim((string)($input['school_code'] ?? ''));
        if ($schoolId === 0 || $name === '' || mb_strlen($name) > 150 || mb_strlen($code) > 30) {
            businessRespond(422, false, 'Enter a department name (up to 150 characters).');
        }
        $exists = $pdo->prepare('SELECT id FROM bus_schools WHERE id = ?');
        $exists->execute([$schoolId]);
        if (!$exists->fetchColumn()) {
            businessRespond(404, false, 'Department not found. Refresh the directory and try again.');
        }
        $stmt = $pdo->prepare("UPDATE bus_schools SET school_name = ?, school_code = NULLIF(?, '') WHERE id = ?");
        $stmt->execute([$name, $code, $schoolId]);
        businessRespond(200, true, 'Department updated.', ['id' => $schoolId]);
    }

    if ($action === 'create_program') {
        $schoolId = businessId($input['school_id'] ?? 0);
        $name = trim((string)($input['program_name'] ?? ''));
        $code = trim((string)($input['program_code'] ?? ''));
        if ($schoolId === 0 || $name === '' || mb_strlen($name) > 150 || mb_strlen($code) > 30) {
            businessRespond(422, false, 'Select a school and enter a program name.');
        }
        $stmt = $pdo->prepare(
            'INSERT INTO biz_programs (school_id, program_name, program_code, created_by)
             VALUES (?, ?, NULLIF(?, \'\'), ?) RETURNING id'
        );
        $stmt->execute([$schoolId, $name, $code, $createdBy]);
        businessRespond(201, true, 'Program created.', ['id' => (int)$stmt->fetchColumn()]);
    }

    if ($action === 'delete_program') {
        $programId = businessId($input['program_id'] ?? 0);
        if ($programId === 0) businessRespond(422, false, 'Choose a valid program to delete.');

        $pdo->beginTransaction();
        $lock = $pdo->prepare('SELECT id FROM biz_programs WHERE id = ? FOR UPDATE');
        $lock->execute([$programId]);
        if (!$lock->fetchColumn()) {
            $pdo->rollBack();
            businessRespond(404, false, 'Program not found. Refresh the directory and try again.');
        }
        $pdo->prepare('DELETE FROM biz_student_performance WHERE program_id = ?')->execute([$programId]);
        $pdo->prepare('DELETE FROM biz_enrollments WHERE program_id = ?')->execute([$programId]);
        $pdo->prepare('DELETE FROM biz_programs WHERE id = ?')->execute([$programId]);
        $pdo->commit();
        businessRespond(200, true, 'Program and its enrollment and performance records deleted.', ['id' => $programId]);
    }

    if ($action === 'delete_school') {
        $schoolId = businessId($input['school_id'] ?? 0);
        if ($schoolId === 0) businessRespond(422, false, 'Choose a valid department to delete.');

        $pdo->beginTransaction();
        $lock = $pdo->prepare('SELECT id FROM biz_schools WHERE id = ? FOR UPDATE');
        $lock->execute([$schoolId]);
        if (!$lock->fetchColumn()) {
            $pdo->rollBack();
            businessRespond(404, false, 'Department not found. Refresh the directory and try again.');
        }
        $pdo->prepare(
            'DELETE FROM biz_student_performance
             WHERE program_id IN (SELECT id FROM biz_programs WHERE school_id = ?)'
        )->execute([$schoolId]);
        $pdo->prepare(
            'DELETE FROM biz_enrollments
             WHERE program_id IN (SELECT id FROM biz_programs WHERE school_id = ?)'
        )->execute([$schoolId]);
        $pdo->prepare('DELETE FROM biz_programs WHERE school_id = ?')->execute([$schoolId]);
        $pdo->prepare('DELETE FROM biz_schools WHERE id = ?')->execute([$schoolId]);
        $pdo->commit();
        businessRespond(200, true, 'Department, programs, enrollment and performance records deleted.', ['id' => $schoolId]);
    }

    if ($action === 'save_enrollment') {
        $programId = businessId($input['program_id'] ?? 0);
        $year = businessYear($input['intake_year'] ?? 0);
        $intake = trim((string)($input['intake_name'] ?? ''));
        $male = filter_var($input['male_count'] ?? null, FILTER_VALIDATE_INT);
        $female = filter_var($input['female_count'] ?? null, FILTER_VALIDATE_INT);
        if ($programId === 0 || $year === 0 || $intake === '' || mb_strlen($intake) > 50 ||
            $male === false || $female === false || $male < 0 || $female < 0) {
            businessRespond(422, false, 'Check the program, intake, year and non-negative student counts.');
        }

        $stmt = $pdo->prepare(
            'INSERT INTO biz_enrollments
                (program_id, intake_year, intake_name, male_count, female_count, created_by, updated_by)
             VALUES (?, ?, ?, ?, ?, ?, ?)
             ON CONFLICT (program_id, intake_year, intake_name)
             DO UPDATE SET male_count = EXCLUDED.male_count,
                           female_count = EXCLUDED.female_count,
                           updated_by = EXCLUDED.updated_by,
                           updated_at = CURRENT_TIMESTAMP
             WHERE NOT EXISTS (
                 SELECT 1 FROM biz_student_performance perf
                 WHERE perf.program_id = biz_enrollments.program_id
                   AND perf.intake_year = biz_enrollments.intake_year
                   AND perf.intake_name = biz_enrollments.intake_name
                   AND perf.students_sat > EXCLUDED.male_count + EXCLUDED.female_count
             )
             RETURNING id'
        );
        $stmt->execute([$programId, $year, $intake, $male, $female, $createdBy, $createdBy]);
        $enrollmentId = $stmt->fetchColumn();
        if ($enrollmentId === false) {
            businessRespond(422, false, 'Enrollment total cannot be lower than the number of students who already sat the examination.');
        }
        businessRespond(200, true, 'Intake figures saved.', ['id' => (int)$enrollmentId]);
    }

    if ($action === 'save_student_performance') {
        $programId = businessId($input['program_id'] ?? 0);
        $intakeYear = businessYear($input['intake_year'] ?? 0);
        $intake = trim((string)($input['intake_name'] ?? ''));
        $promotionYear = filter_var($input['promotion_year'] ?? null, FILTER_VALIDATE_INT, ['options' => ['min_range' => 2001, 'max_range' => 2201]]) ?: 0;
        $sat = filter_var($input['students_sat'] ?? null, FILTER_VALIDATE_INT);
        $passed = filter_var($input['students_passed'] ?? null, FILTER_VALIDATE_INT);
        $failed = filter_var($input['students_failed'] ?? null, FILTER_VALIDATE_INT);

        if ($programId === 0 || $intakeYear === 0 || $intake === '' || mb_strlen($intake) > 50 ||
            $promotionYear !== $intakeYear + 1 || $sat === false || $passed === false || $failed === false ||
            $sat < 0 || $passed < 0 || $failed < 0 || $passed + $failed !== $sat) {
            businessRespond(422, false, 'Check the program, intake, one-year promotion year, and examination totals. Passed plus failed must equal students who sat.');
        }

        $pdo->beginTransaction();
        $stmt = $pdo->prepare(
            'SELECT e.id, (e.male_count + e.female_count) AS enrolled
             FROM biz_enrollments e
             JOIN biz_programs p ON p.id = e.program_id AND p.is_active = TRUE
             JOIN biz_schools s ON s.id = p.school_id AND s.is_active = TRUE
             WHERE e.program_id = ? AND e.intake_year = ? AND e.intake_name = ?
             FOR UPDATE OF e'
        );
        $stmt->execute([$programId, $intakeYear, $intake]);
        $cohort = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$cohort) {
            $pdo->rollBack();
            businessRespond(422, false, 'Choose an existing program intake before entering results.');
        }
        if ($sat > (int)$cohort['enrolled']) {
            $pdo->rollBack();
            businessRespond(422, false, 'Students who sat cannot exceed this intake enrollment total.');
        }

        $stmt = $pdo->prepare(
            'INSERT INTO biz_student_performance
                (program_id, intake_year, intake_name, promotion_year,
                 students_sat, students_passed, students_failed, created_by, updated_by)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
             ON CONFLICT (program_id, intake_year, intake_name)
             DO UPDATE SET promotion_year = EXCLUDED.promotion_year,
                           students_sat = EXCLUDED.students_sat,
                           students_passed = EXCLUDED.students_passed,
                           students_failed = EXCLUDED.students_failed,
                           updated_by = EXCLUDED.updated_by,
                           updated_at = CURRENT_TIMESTAMP
             RETURNING id'
        );
        $stmt->execute([$programId, $intakeYear, $intake, $promotionYear, $sat, $passed, $failed, $createdBy, $createdBy]);
        $resultId = (int)$stmt->fetchColumn();
        $pdo->commit();
        businessRespond(200, true, 'Promotion examination results saved.', ['id' => $resultId]);
    }

    businessRespond(404, false, 'Unknown Business Department action.');
} catch (PDOException $error) {
    $state = (string)$error->getCode();
    error_log('Business Department API database error (' . $state . '): ' . $error->getMessage());
    if ($state === '23505') {
        businessRespond(409, false, 'A school or program with these details already exists.');
    }
    if ($state === '23503') {
        businessRespond(422, false, 'Choose an existing school or program and try again.');
    }
    businessRespond(500, false, 'Database error. Check the server error log.');
} catch (Throwable $error) {
    error_log('Business Department API error: ' . $error->getMessage());
    businessRespond(500, false, 'Business Department request failed. Check the server error log.');
}
