<?php
require_once '../config.php';

ini_set('display_errors', 0); // keep OFF in production

/* ================= HELPERS ================= */

function formatDate($date)
{
    return (!empty($date) && $date !== '0000-00-00')
        ? date('m/d/Y', strtotime($date))
        : '';
}

function getOrder($order)
{
    return ($order === 'desc') ? 'asc' : 'desc';
}

function getValidSortColumn($column)
{
    $allowed = [
        'assignment_no',
        'no_of_record',
        'name',
        'assignment_date',
        'submit_date',
        'id'
    ];

    return in_array($column, $allowed) ? $column : 'assignments.id';
}

function buildSearchQuery(&$params, &$types, $search)
{
    if (!empty($search)) {
        $params[] = "%" . $search . "%";
        $types .= "s";
        return " AND assignments.assignment_no LIKE ? ";
    }
    return "";
}

/* ================= INPUT ================= */

$formData = $_POST['formData'] ?? [];

$action    = $formData['action'] ?? '';
$order     = getOrder($formData['sortorder'] ?? 'desc');
$sort_by   = getValidSortColumn($formData['sortby'] ?? '');
$search    = $formData['searchText'] ?? '';

$params = [];
$types  = "";

/* ================= BASE QUERY ================= */

$whereSearch = buildSearchQuery($params, $types, $search);

/* ================= ACTION HANDLER ================= */

switch ($action) {

    case 'assignment_complete_table_sort':

        $sql = "SELECT assignments.*, users.name 
                FROM assignments
                INNER JOIN users ON users.id = assignments.user_id
                WHERE assignments.assignment_status_id = 2 
                AND assignments.deleted = 0
                $whereSearch
                ORDER BY $sort_by $order";

        echo renderTable($conn, $sql, $params, $types, 'complete', $order);
        break;

    case 'assignment_delete_table_sort':

        $params = [];
        $types  = "";

        // ✅ FIRST add date (matches first ?)
        $params[] = date('Y-m-d', strtotime('-3 days'));
        $types .= "s";

        // ✅ THEN build search
        $whereSearch = buildSearchQuery($params, $types, $search);

        $sql = "SELECT assignments.*, users.name 
            FROM assignments
            INNER JOIN users ON users.id = assignments.user_id
            WHERE assignments.assignment_status_id = 2 
            AND assignments.deleted = 0
            AND assignments.submit_date <= ?
            $whereSearch
            ORDER BY $sort_by $order";

        echo renderTable($conn, $sql, $params, $types, 'delete', $order);
        break;

    case 'assignment_to_table_sort':

        $sql = "SELECT assignments.*, 
                SUM(IF(assignment_details.doc_title != '' 
                OR assignment_details.doc_date != '' 
                OR assignment_details.doc_description != '', 1, 0)) AS completed_count,
                users.name AS user_name
                FROM assignments
                INNER JOIN users ON users.id = assignments.user_id
                INNER JOIN assignment_details ON assignment_details.assignment_id = assignments.id
                WHERE assignments.assignment_status_id = 1
                $whereSearch
                GROUP BY assignments.id
                ORDER BY $sort_by $order";

        echo renderTable($conn, $sql, $params, $types, 'assign', $order);
        break;

    case 'new_assignment_table_sort':

        $sql = "SELECT * FROM assignments
                WHERE assignments.assignment_status_id = 1
                $whereSearch
                GROUP BY assignments.id
                ORDER BY $sort_by $order";

        echo renderTable($conn, $sql, $params, $types, 'new', $order);
        break;
}

/* ================= TABLE RENDER ================= */

function renderTable($conn, $sql, $params, $types, $type, $order)
{

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

    if (!empty($params)) {
        $stmt->bind_param($types, ...$params);
    }

    $stmt->execute();
    $result = $stmt->get_result();

    $output = '<table class="table table-striped table-sm table-bordered">';
    $output .= '<tr>';

    // Header
    $output .= '<th>Action</th>';
    $output .= '<th><a class="column_sort_' . $type . '" id="assignment_no" data-order="' . $order . '">Folder Index</a></th>';
    $output .= '<th><a class="column_sort_' . $type . '" id="no_of_record" data-order="' . $order . '">Data Count</a></th>';

    if ($type === 'assign') {
        $output .= '<th>Completed Count</th>';
        $output .= '<th><a class="column_sort_' . $type . '" id="name" data-order="' . $order . '">Assigned To</a></th>';
    } elseif ($type !== 'new') {
        $output .= '<th><a class="column_sort_' . $type . '" id="name" data-order="' . $order . '">User</a></th>';
    }

    $output .= '<th><a class="column_sort_' . $type . '" id="assignment_date" data-order="' . $order . '">Assigned Date</a></th>';

    if ($type !== 'new') {
        $output .= '<th><a class="column_sort_' . $type . '" id="submit_date" data-order="' . $order . '">Completed Date</a></th>';
    }

    $output .= '</tr>';

    // Rows
    while ($row = $result->fetch_assoc()) {

        $output .= '<tr>';

        $output .= '<td><input type="checkbox" value="' . $row['assignment_no'] . '"></td>';
        $output .= '<td>' . $row['assignment_no'] . '</td>';
        $output .= '<td>' . $row['no_of_record'] . '</td>';

        if ($type === 'assign') {
            $output .= '<td>' . ($row['completed_count'] ?? 0) . '</td>';
            $output .= '<td>' . $row['user_name'] . '</td>';
        } elseif ($type !== 'new') {
            $output .= '<td>' . $row['name'] . '</td>';
        }

        $output .= '<td>' . formatDate($row['assignment_date']) . '</td>';

        if ($type !== 'new') {
            $output .= '<td>' . formatDate($row['submit_date']) . '</td>';
        }

        $output .= '</tr>';
    }

    $output .= '</table>';

    return $output;
}