<?php
// =============================================================
//  include/db_functions.php
//
//  Shared by:
//    weeks_allocation.php          (tabs 1-3)
//    specialty_department.php      (assign specialty → dept)
//    unit_assignment.php           (assign unit)
//    manage_specialty_department.php
//    manage_unit_assignment.php
//    trash.php
// =============================================================


// ─────────────────────────────────────────────────────────────
//  READ — Active lookup lists
// ─────────────────────────────────────────────────────────────

/**
 * Get all active specialties.
 * Table: specialty (uses status VARCHAR: 'Active'/'Inactive')
 */
function getActiveSpecialties($conn) {
    $stmt = $conn->prepare("SELECT id, name FROM specialty WHERE status = 'Active' ORDER BY name");
    $stmt->execute();
    $rows = [];
    $r = $stmt->get_result();
    while ($row = $r->fetch_assoc()) $rows[] = $row;
    $stmt->close();
    return $rows;
}


/**
 * Get all active departments.
 * Table: departments (uses status ENUM: 'Active'/'Inactive')
 */
function getActiveDepartments($conn) {
    $stmt = $conn->prepare("SELECT id, department_name FROM departments WHERE status = 'Active' ORDER BY department_name");
    $stmt->execute();
    $rows = [];
    $r = $stmt->get_result();
    while ($row = $r->fetch_assoc()) $rows[] = $row;
    $stmt->close();
    return $rows;
}


/**
 * Get all active units.
 * Table: units (uses status ENUM: 'Active'/'Inactive')
 */
function getActiveUnits($conn) {
    $stmt = $conn->prepare("SELECT id, unit_name FROM units WHERE status = 'Active' ORDER BY unit_name");
    $stmt->execute();
    $rows = [];
    $r = $stmt->get_result();
    while ($row = $r->fetch_assoc()) $rows[] = $row;
    $stmt->close();
    return $rows;
}



// ─────────────────────────────────────────────────────────────
//  READ — Active allocation data
// ─────────────────────────────────────────────────────────────

/**
 * Departments that have a specific specialty already allocated.
 */
function getDepartmentsBySpecialty($conn, $specialty_id) {
    $stmt = $conn->prepare("
        SELECT d.id, d.department_name, sdw.weeks AS allocated_weeks
        FROM   departments d
        JOIN   specialty_department_weeks sdw ON d.id = sdw.department_id
        WHERE  sdw.specialty_id = ?
          AND  sdw.is_active    = 'Active'
          AND  d.status         = 'Active'
        ORDER  BY d.department_name
    ");
    $stmt->bind_param("i", $specialty_id);
    $stmt->execute();
    $rows = [];
    $r = $stmt->get_result();
    while ($row = $r->fetch_assoc()) $rows[] = $row;
    $stmt->close();
    return $rows;
}


/**
 * Weeks summary for one specialty+department pair.
 */
function getSpecialtyDepartmentSummary($conn, $specialty_id, $department_id) {
    $stmt = $conn->prepare("
        SELECT sdw.weeks                    AS allocated_weeks,
               COALESCE(SUM(duw.weeks), 0)  AS assigned_weeks
        FROM   specialty_department_weeks sdw
        LEFT   JOIN department_unit_weeks duw
               ON  sdw.department_id = duw.department_id
               AND duw.specialty_id   = sdw.specialty_id
               AND duw.is_active      = 'Active'
        WHERE  sdw.specialty_id   = ?
          AND  sdw.department_id = ?
          AND  sdw.is_active     = 'Active'
        GROUP  BY sdw.weeks
    ");
    $stmt->bind_param("ii", $specialty_id, $department_id);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    if ($row) {
        $row['remaining_weeks'] = $row['allocated_weeks'] - $row['assigned_weeks'];
        return $row;
    }
    return null;
}


/**
 * Units NOT yet assigned to a given specialty + department.
 */
function getAvailableUnitsForSpecialtyDepartment($conn, $specialty_id, $department_id) {
    $stmt = $conn->prepare("
        SELECT u.id, u.unit_name
        FROM   units u
        WHERE  u.status = 'Active'
          AND  u.id NOT IN (
               SELECT duw.unit_id
               FROM   department_unit_weeks duw
               WHERE  duw.department_id = ?
                 AND  duw.specialty_id   = ?
                 AND  duw.is_active      = 'Active'
          )
        ORDER  BY u.unit_name
    ");
    $stmt->bind_param("ii", $department_id, $specialty_id);
    $stmt->execute();
    $rows = [];
    $r = $stmt->get_result();
    while ($row = $r->fetch_assoc()) $rows[] = $row;
    $stmt->close();
    return $rows;
}


/**
 * Check whether a unit is already actively assigned.
 */
function checkUnitAssignment($conn, $specialty_id, $department_id, $unit_id) {
    $stmt = $conn->prepare("
        SELECT id FROM department_unit_weeks
        WHERE  specialty_id   = ?
          AND  department_id = ?
          AND  unit_id       = ?
          AND  is_active     = 'Active'
    ");
    $stmt->bind_param("iii", $specialty_id, $department_id, $unit_id);
    $stmt->execute();
    $stmt->store_result();
    $exists = $stmt->num_rows > 0;
    $stmt->close();
    return $exists;
}


/**
 * All active specialty-department allocations with usage counts.
 */
function getSpecialtyDepartmentAllocations($conn) {
    $result = $conn->query("
        SELECT sdw.id,
               s.id   AS specialty_id,   s.name            AS specialty_name,
               d.id   AS department_id, d.department_name,
               sdw.weeks,
               sdw.weeks               AS allocated_weeks,
               COALESCE(SUM(duw.weeks), 0)    AS assigned_weeks,
               COUNT(DISTINCT duw.unit_id)     AS unit_count,
               sdw.created_by, CONCAT(cb.firstname, ' ', cb.surname) AS created_by_name,
               sdw.created_at, sdw.updated_at
        FROM   specialty_department_weeks sdw
        JOIN   specialty    s ON sdw.specialty_id   = s.id
        JOIN   departments d ON sdw.department_id = d.id
        LEFT   JOIN department_unit_weeks duw
               ON  sdw.specialty_id   = duw.specialty_id
               AND sdw.department_id = duw.department_id
               AND duw.is_active     = 'Active'
        LEFT   JOIN users cb ON cb.id = sdw.created_by
        WHERE  sdw.is_active = 'Active'
          AND  s.status = 'Active'
          AND  d.status = 'Active'
        GROUP  BY sdw.id, s.id, s.name, d.id, d.department_name,
                  sdw.weeks, sdw.created_by, cb.firstname, cb.surname, sdw.created_at, sdw.updated_at
        ORDER  BY s.name, d.department_name
    ");
    $rows = [];
    while ($row = $result->fetch_assoc()) {
        $row['remaining_weeks'] = $row['allocated_weeks'] - $row['assigned_weeks'];
        $rows[] = $row;
    }
    return $rows;
}


/**
 * All active unit assignments with full join details.
 */
function getUnitAssignments($conn) {
    $result = $conn->query("
        SELECT duw.id,
               s.id AS specialty_id,   s.name            AS specialty_name,
               d.id AS department_id, d.department_name,
               u.id AS unit_id,       u.unit_name,
               duw.weeks, duw.created_at, duw.created_by,
               CONCAT(cb.firstname, ' ', cb.surname) AS created_by_name,
               duw.updated_at
        FROM   department_unit_weeks duw
        JOIN   specialty    s ON duw.specialty_id   = s.id
        JOIN   departments d ON duw.department_id = d.id
        JOIN   units       u ON duw.unit_id       = u.id
        LEFT   JOIN users cb ON cb.id = duw.created_by
        WHERE  duw.is_active = 'Active'
          AND  s.status = 'Active'
          AND  d.status = 'Active'
          AND  u.status = 'Active'
        ORDER  BY s.name, d.department_name, u.unit_name
    ");
    $rows = [];
    while ($row = $result->fetch_assoc()) $rows[] = $row;
    return $rows;
}


/**
 * Per-specialty totals for summary stat cards.
 */
function getSpecialtySummary($conn) {
    $result = $conn->query("
        SELECT s.id,
               s.name                          AS specialty_name,
               COUNT(DISTINCT sdw.department_id) AS total_departments,
               COALESCE(SUM(sdw.weeks), 0)       AS total_weeks_allocated,
               COALESCE((
                   SELECT SUM(duw.weeks)
                   FROM   department_unit_weeks duw
                   WHERE  duw.specialty_id = s.id AND duw.is_active = 'Active'
               ), 0)                             AS total_weeks_assigned
        FROM   specialty s
        LEFT   JOIN specialty_department_weeks sdw
               ON s.id = sdw.specialty_id AND sdw.is_active = 'Active'
        WHERE  s.status = 'Active'
        GROUP  BY s.id, s.name
        ORDER  BY s.name
    ");
    $rows = [];
    while ($row = $result->fetch_assoc()) {
        $row['remaining_weeks'] = $row['total_weeks_allocated'] - $row['total_weeks_assigned'];
        $rows[] = $row;
    }
    return $rows;
}



// ─────────────────────────────────────────────────────────────
//  READ — Deleted (Inactive) records — used by trash.php
// ─────────────────────────────────────────────────────────────

function getDeletedSpecialtyDepartments($conn) {
    $result = $conn->query("
        SELECT sdw.id,
               s.name            AS specialty_name,
               d.department_name,
               sdw.weeks         AS allocated_weeks,
               sdw.created_by,   sdw.created_at,
               sdw.deleted_by,   sdw.deleted_at
        FROM   specialty_department_weeks sdw
        JOIN   specialty    s ON sdw.specialty_id   = s.id
        JOIN   departments d ON sdw.department_id = d.id
        WHERE  sdw.is_active = 'Inactive'
        ORDER  BY sdw.deleted_at DESC
    ");
    $rows = [];
    while ($row = $result->fetch_assoc()) $rows[] = $row;
    return $rows;
}


function getDeletedUnitAssignments($conn) {
    $result = $conn->query("
        SELECT duw.id,
               s.name            AS specialty_name,
               d.department_name,
               u.unit_name,
               duw.weeks,
               duw.created_by,   duw.created_at,
               duw.deleted_by,   duw.deleted_at
        FROM   department_unit_weeks duw
        JOIN   specialty    s ON duw.specialty_id   = s.id
        JOIN   departments d ON duw.department_id = d.id
        JOIN   units       u ON duw.unit_id       = u.id
        WHERE  duw.is_active = 'Inactive'
        ORDER  BY duw.deleted_at DESC
    ");
    $rows = [];
    while ($row = $result->fetch_assoc()) $rows[] = $row;
    return $rows;
}



// ─────────────────────────────────────────────────────────────
//  CREATE
// ─────────────────────────────────────────────────────────────

function assignSpecialtyToDepartment($conn, $specialty_id, $department_id, $weeks, $user_id) {
    $chk = $conn->prepare("
        SELECT id FROM specialty_department_weeks
        WHERE  specialty_id = ? AND department_id = ? AND is_active = 'Active'
    ");
    $chk->bind_param("ii", $specialty_id, $department_id);
    $chk->execute();
    $chk->store_result();
    if ($chk->num_rows > 0) {
        $chk->close();
        return ['success' => false, 'message' => 'This specialty is already assigned to this department'];
    }
    $chk->close();

    // specialty_department_weeks has a UNIQUE(specialty_id, department_id)
    // index that is NOT scoped to is_active — so a pair that was once
    // assigned and later soft-deleted (is_active='Inactive') still
    // collides with a fresh INSERT even though the check above (which
    // only looks at Active rows) sees it as free. Reactivate that row
    // instead of inserting a duplicate when this happens, rather than
    // letting the resulting mysqli duplicate-key exception surface as an
    // uncaught 500 — this is the same underlying gap on every caller of
    // this function (app/specialty_department.php, app/assign_department_unit.php,
    // and api/v1/admin_allocations/specialty_department_create.php alike).
    $stmt = $conn->prepare("
        INSERT INTO specialty_department_weeks
               (specialty_id, department_id, weeks, created_by, is_active)
        VALUES (?, ?, ?, ?, 'Active')
    ");
    $stmt->bind_param("iiii", $specialty_id, $department_id, $weeks, $user_id);
    try {
        if ($stmt->execute()) {
            $newId = $conn->insert_id;
            $stmt->close();
            return ['success' => true, 'message' => 'Specialty assigned successfully', 'id' => $newId];
        }
        $err = $stmt->error; $stmt->close();
        return ['success' => false, 'message' => 'Database error: ' . $err];
    } catch (mysqli_sql_exception $e) {
        $stmt->close();
        if ($e->getCode() !== 1062) throw $e; // not the duplicate-key case this catch exists for

        $reactivate = $conn->prepare("
            UPDATE specialty_department_weeks
            SET    weeks = ?, created_by = ?, is_active = 'Active',
                   deleted_by = NULL, deletion_reason = NULL, deleted_at = NULL, updated_at = NOW()
            WHERE  specialty_id = ? AND department_id = ? AND is_active = 'Inactive'
        ");
        $reactivate->bind_param("iiii", $weeks, $user_id, $specialty_id, $department_id);
        if ($reactivate->execute() && $reactivate->affected_rows > 0) {
            $reactivate->close();
            $idRow = $conn->prepare("SELECT id FROM specialty_department_weeks WHERE specialty_id = ? AND department_id = ? AND is_active = 'Active'");
            $idRow->bind_param("ii", $specialty_id, $department_id);
            $idRow->execute();
            $reactivatedId = (int)($idRow->get_result()->fetch_assoc()['id'] ?? 0);
            $idRow->close();
            return ['success' => true, 'message' => 'Specialty assigned successfully', 'id' => $reactivatedId];
        }
        $reactivate->close();
        // Neither an Active nor a reactivatable Inactive row explains the
        // collision — surface the original message rather than a raw DB error.
        return ['success' => false, 'message' => 'This specialty is already assigned to this department'];
    }
}


function assignUnitToSpecialtyDepartment($conn, $specialty_id, $department_id, $unit_id, $weeks, $user_id) {
    if (checkUnitAssignment($conn, $specialty_id, $department_id, $unit_id))
        return ['success' => false, 'message' => 'This unit is already assigned to this specialty and department'];

    $summary = getSpecialtyDepartmentSummary($conn, $specialty_id, $department_id);
    if (!$summary)
        return ['success' => false, 'message' => 'This specialty is not allocated to this department'];
    if ($weeks > $summary['remaining_weeks'])
        return ['success' => false, 'message' => "Cannot assign $weeks weeks. Only {$summary['remaining_weeks']} weeks remaining for this specialty in this department."];

    $stmt = $conn->prepare("
        INSERT INTO department_unit_weeks
               (specialty_id, department_id, unit_id, weeks, created_by, is_active)
        VALUES (?, ?, ?, ?, ?, 'Active')
    ");
    $stmt->bind_param("iiiii", $specialty_id, $department_id, $unit_id, $weeks, $user_id);
    if ($stmt->execute()) { $stmt->close(); return ['success' => true, 'message' => 'Unit assigned successfully']; }
    $err = $stmt->error; $stmt->close();
    return ['success' => false, 'message' => 'Database error: ' . $err];
}



// ─────────────────────────────────────────────────────────────
//  UPDATE
// ─────────────────────────────────────────────────────────────

function editSpecialtyDepartment($conn, $id, $weeks, $username) {
    $chk = $conn->prepare("
        SELECT COALESCE(SUM(duw.weeks), 0) AS assigned
        FROM   department_unit_weeks duw
        JOIN   specialty_department_weeks sdw
               ON duw.specialty_id = sdw.specialty_id AND duw.department_id = sdw.department_id
        WHERE  sdw.id = ? AND duw.is_active = 'Active'
    ");
    $chk->bind_param("i", $id);
    $chk->execute();
    $row = $chk->get_result()->fetch_assoc();
    $chk->close();
    if ($row['assigned'] > $weeks)
        return ['success' => false, 'message' => "Cannot reduce below {$row['assigned']} weeks (already assigned to units)"];

    $stmt = $conn->prepare("
        UPDATE specialty_department_weeks
        SET    weeks = ?, updated_at = NOW()
        WHERE  id = ? AND is_active = 'Active'
    ");
    $stmt->bind_param("ii", $weeks, $id);
    if ($stmt->execute()) { $stmt->close(); return ['success' => true, 'message' => 'Updated successfully']; }
    $err = $stmt->error; $stmt->close();
    return ['success' => false, 'message' => 'Database error: ' . $err];
}


/**
 * Returns all active units for a specialty+department, always including the
 * unit currently assigned to $assignment_id (so it appears pre-selected in the edit dropdown).
 */
function getUnitsForAssignment($conn, $assignment_id) {
    $ctx = $conn->prepare("
        SELECT specialty_id, department_id, unit_id
        FROM   department_unit_weeks
        WHERE  id = ? AND is_active = 'Active'
    ");
    $ctx->bind_param("i", $assignment_id);
    $ctx->execute();
    $row = $ctx->get_result()->fetch_assoc();
    $ctx->close();
    if (!$row) return ['success' => false, 'message' => 'Assignment not found'];

    $specialty_id  = $row['specialty_id'];
    $department_id = $row['department_id'];
    $current_unit  = $row['unit_id'];

    $stmt = $conn->prepare("
        SELECT u.id, u.unit_name,
               IF(u.id = ?, 1, 0) AS is_current
        FROM   units u
        WHERE  u.status = 'Active'
          AND (
               u.id = ?
               OR u.id NOT IN (
                   SELECT duw.unit_id
                   FROM   department_unit_weeks duw
                   WHERE  duw.department_id = ?
                     AND  duw.specialty_id  = ?
                     AND  duw.is_active     = 'Active'
               )
          )
        ORDER BY is_current DESC, u.unit_name
    ");
    $stmt->bind_param("iiii", $current_unit, $current_unit, $department_id, $specialty_id);
    $stmt->execute();
    $units = [];
    $r = $stmt->get_result();
    while ($u = $r->fetch_assoc()) $units[] = $u;
    $stmt->close();
    return ['success' => true, 'units' => $units, 'current_unit_id' => $current_unit];
}

function editUnitAssignment($conn, $id, $weeks, $username, $unit_id = null) {
    $get = $conn->prepare("
        SELECT duw.specialty_id, duw.department_id, duw.unit_id,
               sdw.weeks AS allocated_weeks,
               (SELECT COALESCE(SUM(weeks), 0)
                FROM   department_unit_weeks
                WHERE  specialty_id   = duw.specialty_id
                  AND  department_id = duw.department_id
                  AND  is_active     = 'Active'
                  AND  id           != duw.id) AS other_assigned
        FROM   department_unit_weeks duw
        JOIN   specialty_department_weeks sdw
               ON duw.specialty_id = sdw.specialty_id AND duw.department_id = sdw.department_id
        WHERE  duw.id = ? AND duw.is_active = 'Active' AND sdw.is_active = 'Active'
    ");
    $get->bind_param("i", $id);
    $get->execute();
    $row = $get->get_result()->fetch_assoc();
    $get->close();
    if (!$row) return ['success' => false, 'message' => 'Assignment not found'];
    if (($row['other_assigned'] + $weeks) > $row['allocated_weeks'])
        return ['success' => false, 'message' => "Cannot assign $weeks weeks. Only {$row['allocated_weeks']} allocated, with {$row['other_assigned']} already assigned to other units."];

    // If changing unit, verify the new unit isn't already assigned in this specialty+department
    if ($unit_id && $unit_id !== $row['unit_id']) {
        $chk = $conn->prepare("
            SELECT id FROM department_unit_weeks
            WHERE  specialty_id = ? AND department_id = ? AND unit_id = ?
              AND  is_active = 'Active' AND id != ?
        ");
        $chk->bind_param("iiii", $row['specialty_id'], $row['department_id'], $unit_id, $id);
        $chk->execute();
        $chk->store_result();
        if ($chk->num_rows > 0) { $chk->close(); return ['success' => false, 'message' => 'This unit is already assigned in this department.']; }
        $chk->close();
    }

    $effective_unit = $unit_id ?: $row['unit_id'];
    $stmt = $conn->prepare("UPDATE department_unit_weeks SET unit_id = ?, weeks = ?, updated_at = NOW() WHERE id = ? AND is_active = 'Active'");
    $stmt->bind_param("iii", $effective_unit, $weeks, $id);
    if ($stmt->execute()) { $stmt->close(); return ['success' => true, 'message' => 'Updated successfully']; }
    $err = $stmt->error; $stmt->close();
    return ['success' => false, 'message' => 'Database error: ' . $err];
}



// ─────────────────────────────────────────────────────────────
//  SOFT DELETE
// ─────────────────────────────────────────────────────────────

function softDeleteSpecialtyDepartment($conn, $id, $username) {
    $chk = $conn->prepare("
        SELECT COUNT(*) AS cnt
        FROM   department_unit_weeks duw
        JOIN   specialty_department_weeks sdw
               ON duw.specialty_id = sdw.specialty_id AND duw.department_id = sdw.department_id
        WHERE  sdw.id = ? AND duw.is_active = 'Active'
    ");
    $chk->bind_param("i", $id);
    $chk->execute();
    $row = $chk->get_result()->fetch_assoc();
    $chk->close();
    if ($row['cnt'] > 0)
        return ['success' => false, 'message' => 'Cannot delete: There are unit assignments using this allocation'];

    $stmt = $conn->prepare("
        UPDATE specialty_department_weeks
        SET    is_active = 'Inactive', deleted_by = ?, deleted_at = NOW()
        WHERE  id = ?
    ");
    $stmt->bind_param("si", $username, $id);
    if ($stmt->execute()) { $stmt->close(); return ['success' => true, 'message' => 'Deleted successfully']; }
    $err = $stmt->error; $stmt->close();
    return ['success' => false, 'message' => 'Database error: ' . $err];
}


function softDeleteUnitAssignment($conn, $id, $username) {
    $stmt = $conn->prepare("
        UPDATE department_unit_weeks
        SET    is_active = 'Inactive', deleted_by = ?, deleted_at = NOW()
        WHERE  id = ?
    ");
    $stmt->bind_param("si", $username, $id);
    if ($stmt->execute()) { $stmt->close(); return ['success' => true, 'message' => 'Deleted successfully']; }
    $err = $stmt->error; $stmt->close();
    return ['success' => false, 'message' => 'Database error: ' . $err];
}



// ─────────────────────────────────────────────────────────────
//  RESTORE — used by trash.php
// ─────────────────────────────────────────────────────────────

function restoreSpecialtyDepartment($conn, $id, $username) {
    $get = $conn->prepare("SELECT specialty_id, department_id FROM specialty_department_weeks WHERE id = ?");
    $get->bind_param("i", $id);
    $get->execute();
    $row = $get->get_result()->fetch_assoc();
    $get->close();
    if (!$row) return ['success' => false, 'message' => 'Record not found'];

    $chk = $conn->prepare("
        SELECT id FROM specialty_department_weeks
        WHERE  specialty_id = ? AND department_id = ? AND is_active = 'Active' AND id != ?
    ");
    $chk->bind_param("iii", $row['specialty_id'], $row['department_id'], $id);
    $chk->execute(); $chk->store_result();
    if ($chk->num_rows > 0) {
        $chk->close();
        return ['success' => false, 'message' => 'Cannot restore: an active allocation for this specialty-department already exists'];
    }
    $chk->close();

    $stmt = $conn->prepare("
        UPDATE specialty_department_weeks
        SET    is_active = 'Active', deleted_by = NULL, deleted_at = NULL, updated_at = NOW()
        WHERE  id = ?
    ");
    $stmt->bind_param("i", $id);
    if ($stmt->execute()) { $stmt->close(); return ['success' => true, 'message' => 'Restored successfully']; }
    $err = $stmt->error; $stmt->close();
    return ['success' => false, 'message' => 'Database error: ' . $err];
}


function restoreUnitAssignment($conn, $id, $username) {
    $get = $conn->prepare("SELECT specialty_id, department_id, unit_id, weeks FROM department_unit_weeks WHERE id = ?");
    $get->bind_param("i", $id);
    $get->execute();
    $row = $get->get_result()->fetch_assoc();
    $get->close();
    if (!$row) return ['success' => false, 'message' => 'Record not found'];

    $chk = $conn->prepare("
        SELECT id FROM department_unit_weeks
        WHERE  specialty_id = ? AND department_id = ? AND unit_id = ? AND is_active = 'Active' AND id != ?
    ");
    $chk->bind_param("iiii", $row['specialty_id'], $row['department_id'], $row['unit_id'], $id);
    $chk->execute(); $chk->store_result();
    if ($chk->num_rows > 0) {
        $chk->close();
        return ['success' => false, 'message' => 'Cannot restore: this unit is already actively assigned here'];
    }
    $chk->close();

    $summary = getSpecialtyDepartmentSummary($conn, $row['specialty_id'], $row['department_id']);
    if ($summary && $row['weeks'] > $summary['remaining_weeks'])
        return ['success' => false, 'message' => "Cannot restore: only {$summary['remaining_weeks']} weeks remaining in the allocation"];

    $stmt = $conn->prepare("
        UPDATE department_unit_weeks
        SET    is_active = 'Active', deleted_by = NULL, deleted_at = NULL, updated_at = NOW()
        WHERE  id = ?
    ");
    $stmt->bind_param("i", $id);
    if ($stmt->execute()) { $stmt->close(); return ['success' => true, 'message' => 'Restored successfully']; }
    $err = $stmt->error; $stmt->close();
    return ['success' => false, 'message' => 'Database error: ' . $err];
}



// ─────────────────────────────────────────────────────────────
//  PERMANENT DELETE — used by trash.php
// ─────────────────────────────────────────────────────────────

function permanentDeleteSpecialtyDepartment($conn, $id) {
    $stmt = $conn->prepare("DELETE FROM specialty_department_weeks WHERE id = ? AND is_active = 'Inactive'");
    $stmt->bind_param("i", $id);
    if ($stmt->execute()) { $stmt->close(); return ['success' => true, 'message' => 'Permanently deleted']; }
    $err = $stmt->error; $stmt->close();
    return ['success' => false, 'message' => 'Database error: ' . $err];
}


function permanentDeleteUnitAssignment($conn, $id) {
    $stmt = $conn->prepare("DELETE FROM department_unit_weeks WHERE id = ? AND is_active = 'Inactive'");
    $stmt->bind_param("i", $id);
    if ($stmt->execute()) { $stmt->close(); return ['success' => true, 'message' => 'Permanently deleted']; }
    $err = $stmt->error; $stmt->close();
    return ['success' => false, 'message' => 'Database error: ' . $err];
}



// ─────────────────────────────────────────────────────────────
//  ALIAS FUNCTIONS — for specialty_department.php compatibility
// ─────────────────────────────────────────────────────────────

/**
 * Alias: getSpecialtyDepartmentWeeks → getSpecialtyDepartmentAllocations
 * Used by: specialty_department.php
 */
function getSpecialtyDepartmentWeeks($conn) {
    return getSpecialtyDepartmentAllocations($conn);
}


/**
 * Alias: getSpecialtyWeeksSummary → getSpecialtySummary
 * Used by: specialty_department.php
 */
function getSpecialtyWeeksSummary($conn) {
    return getSpecialtySummary($conn);
}

?>