<?php
$DB_HOST = 'localhost';
$DB_NAME = 'school_checkin';
$DB_USER = 'root';
$DB_PASS = '';
$API_TOKEN = 'sdaesi';

date_default_timezone_set('Asia/Manila');

header('Content-Type: application/json');

function respond($success, $payload) {
    echo json_encode($success ? ['success' => true, 'data' => $payload] : ['success' => false, 'error' => $payload]);
    exit;
}

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    http_response_code(405);
    respond(false, 'Method Not Allowed');
}

$input = json_decode(file_get_contents('php://input'), true);

if (!isset($input['method'], $input['table'], $input['token'])) {
    http_response_code(400);
    respond(false, 'Missing required parameters');
}

if ($input['token'] !== $API_TOKEN) {
    http_response_code(401);
    respond(false, 'Invalid token');
}

$method = strtoupper($input['method']);
$table = preg_replace('/[^a-zA-Z0-9_]/', '', $input['table']);
$target = $input['target'] ?? '*';
$order = strtoupper($input['order'] ?? '');
$limit = isset($input['limit']) ? intval($input['limit']) : null;
$date = $input['date'] ?? date('Y-m-d');
$grade = $input['grade'] ?? null;
$session = $input['session'] ?? 'All';

try {
    // API log
    $string = serialize($input);
    $fp = fopen('api_logs.txt', 'a');
    fwrite($fp, "api: " . $string . "\r\n");
    fclose($fp);

    $pdo = new PDO("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=utf8mb4", $DB_USER, $DB_PASS);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // --- Holiday/weekend logic ---
    $today = $date;
    $suspension_dates = [];
    $suspension_reasons = [];
    
    $sql_holidays = "SELECT date, description FROM holidays";
    $res_holidays = $pdo->query($sql_holidays);
    if ($res_holidays) {
        while ($h = $res_holidays->fetch(PDO::FETCH_ASSOC)) {
            $suspension_dates[] = $h['date'];
            $suspension_reasons[$h['date']] = $h['description'];
        }
    }
    
    $is_holiday = in_array($today, $suspension_dates);
    $is_weekend = in_array(date('l', strtotime($today)), ['Saturday', 'Sunday']);
    $suspended = $is_holiday || $is_weekend;
    
    $suspension_reason = "";
    if ($is_holiday) {
        $suspension_reason = $suspension_reasons[$today] ?? 'Holiday';
    } else if ($is_weekend) {
        $suspension_reason = "Weekend";
    }

    $where = '';
    if (isset($input['where']) && is_array($input['where'])) {
        $w = [];
        foreach ($input['where'] as $col => $val) {
            $col = preg_replace('/[^a-zA-Z0-9_]/', '', $col);
            $w[] = "$col = " . (is_numeric($val) ? $val : $pdo->quote($val));
        }
        $where = implode(' AND ', $w);
    } elseif (isset($input['where'])) {
        $where = trim((string)$input['where']);
    }

    switch ($method) {
        case 'CHECK_LICENSE':
            $schoolCode = $input['school_code'] ?? '';
            $licenseKey = $input['license_key'] ?? '';
            if (!$schoolCode || !$licenseKey) respond(false, 'Missing school code or license key');
            $stmt = $pdo->prepare("SELECT * FROM license_keys WHERE school_code = ? AND license_key = ? AND is_active = 1 AND valid_until >= CURDATE()");
            $stmt->execute([$schoolCode, $licenseKey]);
            respond($stmt->rowCount() > 0, $stmt->fetch(PDO::FETCH_ASSOC) ?: 'Invalid or expired license key');
            break;

        case 'REGISTER':
            $username = $input['username'] ?? '';
            $password = $input['password'] ?? '';
            if (!$username || !$password) respond(false, 'Missing username or password');
            $stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
            $stmt->execute([$username]);
            if ($stmt->rowCount() > 0) respond(false, 'Username already exists');
            $stmt = $pdo->prepare("INSERT INTO users (username, password, role) VALUES (?, ?, 'teacher')");
            $stmt->execute([$username, password_hash($password, PASSWORD_DEFAULT)]);
            respond(true, 'Account created');
            break;

        case 'GET_ATTENDANCE_BY_SESSION':
            if ($suspended) {
                $students = [];
                $grade_sql = "";
                $params = [];
                
                if ($grade) {
                    $grade_sql = " WHERE grade = ?";
                    $params = [$grade];
                }
                
                $stmt = $pdo->prepare("SELECT lrn, CONCAT(first_name, ' ', IFNULL(middle_name, ''), ' ', last_name) AS name, grade FROM students" . $grade_sql);
                $stmt->execute($params);
                
                while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                    $students[] = [
                        'lrn' => $row['lrn'],
                        'name' => $row['name'],
                        'grade' => $row['grade'],
                        'am_in' => null,
                        'am_out' => null,
                        'pm_in' => null,
                        'pm_out' => null,
                        'am_status' => 'N/A',
                        'pm_status' => 'N/A',
                        'date' => $date
                    ];
                }
                
                respond(true, [
                    'suspended' => true,
                    'reason' => $suspension_reason,
                    'students' => $students,
                    'stats' => [
                        'total' => count($students),
                        'present' => 0,
                        'absent' => 0,
                        'pending' => 0,
                        'na' => count($students)
                    ]
                ]);
                break;
            }

            $session_column = $session === 'Morning' ? 'am_in, am_out' : 'pm_in, pm_out';
            $sql = "SELECT 
                        s.lrn, 
                        CONCAT(s.first_name, ' ', IFNULL(s.middle_name, ''), ' ', s.last_name) AS name, 
                        s.grade,
                        c.$session_column
                    FROM students s
                    LEFT JOIN (
                        SELECT 
                            lrn,
                            scan_date,
                            MIN(am_in) AS am_in,
                            MAX(am_out) AS am_out,
                            MIN(pm_in) AS pm_in,
                            MAX(pm_out) AS pm_out
                        FROM checkins
                        WHERE scan_date = ? AND session = ?
                        GROUP BY lrn, scan_date
                    ) c ON s.lrn = c.lrn AND c.scan_date = ?
            ";
            $params = [$date, $session, $date];

            if ($grade) {
                $sql .= " WHERE s.grade = ?";
                $params[] = $grade;
            }

            // Debugging: Log the query and parameters
            error_log("SQL: $sql");
            error_log("Params: " . implode(', ', $params));

            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);

            $students = [];
            $total = 0;
            $present = 0;
            $absent = 0;
            $pending = 0;

            $today_date = date('Y-m-d');
            $is_today = ($date === $today_date);
            $is_future = (strtotime($date) > strtotime($today_date));

            $cutoff_time = $session === 'Morning' ? strtotime($date . ' 12:00:00') : strtotime($date . ' 17:00:00');
            $current_time = time();

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

                if ($is_future) {
                    $status = 'Pending';
                    $pending++;
                } else {
                    $in_time = $session === 'Morning' ? $row['am_in'] : $row['pm_in'];
                    if (!empty($in_time)) {
                        $status = 'Present';
                        $present++;
                    } else {
                        if ($is_today && $current_time < $cutoff_time) {
                            $status = 'Pending';
                            $pending++;
                        } else {
                            $status = 'Absent';
                            $absent++;
                        }
                    }
                }

                $students[] = [
                    'lrn' => $row['lrn'],
                    'name' => $row['name'],
                    'grade' => $row['grade'],
                    'status' => $status,
                    'date' => $date
                ];
            }

            respond(true, [
                'suspended' => false,
                'students' => $students,
                'stats' => [
                    'total' => $total,
                    'present' => $present,
                    'absent' => $absent,
                    'pending' => $pending,
                    'na' => 0
                ]
            ]);
            break;

        case 'UPDATE':         
            if (empty($input['set']) || !$where) respond(false, 'SET and WHERE clause required for UPDATE');

            if (isset($input['set']['pm_out'])) {
                $row = $pdo->query("SELECT am_out, pm_in FROM $table WHERE $where LIMIT 1")->fetch(PDO::FETCH_ASSOC);
                if (empty($row['am_out'])) $input['set']['am_out'] = '11:30:00';
                if (empty($row['pm_in'])) $input['set']['pm_in'] = '12:00:00';
            }

            $setParts = [];
            foreach ($input['set'] as $col => $val) {
                $col = preg_replace('/[^a-zA-Z0-9_]/', '', $col);
                $setParts[] = "$col = " . (is_numeric($val) ? $val : $pdo->quote($val));
            }
            $stmt = $pdo->prepare("UPDATE $table SET " . implode(', ', $setParts) . " WHERE $where" . ($limit ? " LIMIT $limit" : ''));
            $stmt->execute();
            respond(true, ['affectedRows' => $stmt->rowCount()]);
            break;

        case 'INSERT':
            if (empty($input['columns']) || empty($input['values'])) respond(false, 'Columns and values required for INSERT');
            
            // Add session to columns and values if not already included
            if (!in_array('session', $input['columns'])) {
                $input['columns'][] = 'session';
                $input['values'][] = $session;
            }
            
            $columns = implode(',', array_map(fn($c) => preg_replace('/[^a-zA-Z0-9_]/', '', $c), $input['columns']));
            $placeholders = implode(',', array_fill(0, count($input['values']), '?'));
            $stmt = $pdo->prepare("INSERT INTO $table ($columns) VALUES ($placeholders)");
            $stmt->execute($input['values']);
            respond(true, ['lastInsertId' => $pdo->lastInsertId()]);
            break;

        case 'SELECT':
            $sql = "SELECT $target FROM $table";
            if ($where) $sql .= " WHERE $where";
            if ($order) $sql .= " ORDER BY id $order";
            if ($limit) $sql .= " LIMIT $limit";
            $stmt = $pdo->query($sql);
            respond(true, $stmt->fetchAll(PDO::FETCH_ASSOC));
            break;

        case 'DELETE':
            if (!$where) respond(false, 'WHERE clause required for DELETE');
            $sql = "DELETE FROM $table WHERE $where";
            if ($limit) $sql .= " LIMIT $limit";
            respond(true, ['affectedRows' => $pdo->exec($sql)]);
            break;

        default:
            respond(false, 'Invalid method');
    }
} catch (PDOException $e) {
    respond(false, 'Query error: ' . $e->getMessage());
}
?>