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

header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: POST, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type');

if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    http_response_code(200);
    exit;
}

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    sendJson(['error' => 'Only POST allowed'], 405);
}

try {
    $db = getDB();
    $results = ['success' => [], 'failed' => [], 'total' => 0];

    // Check if file uploaded
    if (isset($_FILES['file']) && $_FILES['file']['error'] === UPLOAD_ERR_OK) {
        $file = $_FILES['file']['tmp_name'];
        $ext = strtolower(pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION));

        $rows = [];

        if ($ext === 'csv') {
            $handle = fopen($file, 'r');
            $headers = fgetcsv($handle);
            if (!$headers) {
                sendJson(['error' => 'Empty CSV file'], 400);
            }
            // Normalize headers - remove spaces, lowercase
            $headers = array_map(function($h) {
                return strtolower(trim(str_replace([' ', '_'], '', $h)));
            }, $headers);

            while (($data = fgetcsv($handle)) !== false) {
                if (count($data) >= 2) {
                    $row = [];
                    foreach ($headers as $i => $header) {
                        $row[$header] = isset($data[$i]) ? trim($data[$i]) : '';
                    }
                    $rows[] = $row;
                }
            }
            fclose($handle);
        }
        elseif ($ext === 'xlsx' || $ext === 'xls') {
            sendJson(['error' => 'Please save Excel as CSV and upload CSV file instead'], 400);
        }
        else {
            sendJson(['error' => 'Only CSV files supported. Save your Excel as CSV and upload.'], 400);
        }

        // Get current max student number for ID generation
        $stmt = $db->query("SELECT COUNT(*) as cnt FROM students WHERE student_id LIKE 'SK%'");
        $countRow = $stmt->fetch();
        $studentCount = intval($countRow['cnt'] ?? 0);

        foreach ($rows as $index => $row) {
            $results['total']++;

            // ===== FIND COLUMNS (flexible matching) =====

            // Name
            $name = '';
            foreach (['name', 'fullname', 'full_name', 'studentname', 'student_name', 'student', 'studentsname'] as $key) {
                if (!empty($row[$key])) { $name = $row[$key]; break; }
            }

            // Email (login ID)
            $email = '';
            foreach (['email', 'emailid', 'email_id', 'e-mail', 'e_mail', 'emailaddress', 'loginid', 'login_id', 'username', 'mail'] as $key) {
                if (!empty($row[$key])) { $email = $row[$key]; break; }
            }

            // Password
            $password = '';
            foreach (['password', 'pass', 'pwd', 'userpassword', 'loginpassword', 'login_password'] as $key) {
                if (!empty($row[$key])) { $password = $row[$key]; break; }
            }

            // Mobile/Phone
            $phone = '';
            foreach (['mobile', 'mobileno', 'mobile_no', 'phone', 'phoneno', 'phone_no', 'contact', 'contactno', 'mobile_number', 'phone_number'] as $key) {
                if (!empty($row[$key])) { $phone = $row[$key]; break; }
            }

            // Fees (variable - each student can have different amount)
            $fees = 2000; // default
            foreach (['fees', 'fee', 'monthlyfee', 'monthly_fees', 'feeamount', 'fee_amount', 'amount'] as $key) {
                if (!empty($row[$key])) {
                    $fees = floatval($row[$key]);
                    break;
                }
            }

            // Address
            $address = '';
            foreach (['address', 'addr', 'homeaddress', 'home_address', 'residentialaddress'] as $key) {
                if (!empty($row[$key])) { $address = $row[$key]; break; }
            }

            if (empty($name) || empty($email)) {
                $results['failed'][] = ['row' => $index + 2, 'reason' => 'Name or email missing', 'data' => $row];
                continue;
            }

            // Validate email format
            if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
                $results['failed'][] = ['row' => $index + 2, 'reason' => 'Invalid email format: ' . $email, 'name' => $name];
                continue;
            }

            // Generate Student ID (SK001, SK002, etc.)
            $studentCount++;
            $studentId = 'SK' . str_pad($studentCount, 3, '0', STR_PAD_LEFT);

            // Use provided password or generate random
            $userPassword = !empty($password) ? $password : substr(str_shuffle('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'), 0, 8);

            try {
                // ===== INSERT INTO STUDENTS TABLE =====
                $studentStmt = $db->prepare("INSERT INTO students
                    (student_id, full_name, phone, email, monthly_fee, fee_status, joining_date, is_active, batch_id, batch_name, skill_level, address)
                    VALUES (?, ?, ?, ?, ?, 'pending', CURDATE(), 1, 1, 'Morning Batch A', 'beginner', ?)");
                $studentStmt->execute([$studentId, $name, $phone, $email, $fees, $address]);

                // ===== INSERT INTO USERS TABLE (LOGIN) =====
                // Email = username (login ID), Password = bcrypt hashed
                $userStmt = $db->prepare("INSERT INTO users (username, password_hash, name, role, phone, email)
                    VALUES (?, ?, ?, 'student', ?, ?)
                    ON DUPLICATE KEY UPDATE
                    password_hash = VALUES(password_hash),
                    name = VALUES(name),
                    phone = VALUES(phone),
                    is_active = 1");
                $userStmt->execute([
                    $email,
                    password_hash($userPassword, PASSWORD_DEFAULT),
                    $name,
                    $phone,
                    $email
                ]);

                $results['success'][] = [
                    'name' => $name,
                    'email' => $email,
                    'studentId' => $studentId,
                    'password' => $userPassword,
                    'fees' => $fees,
                    'phone' => $phone,
                ];
            } catch (Exception $e) {
                $results['failed'][] = ['row' => $index + 2, 'reason' => $e->getMessage(), 'name' => $name];
            }
        }

        sendJson([
            'success' => true,
            'imported' => count($results['success']),
            'failed' => count($results['failed']),
            'total' => $results['total'],
            'students' => $results['success'],
            'errors' => $results['failed'],
        ]);
    }
    else {
        sendJson(['error' => 'No file uploaded. Please upload a CSV file.'], 400);
    }

} catch (Exception $e) {
    sendJson(['error' => $e->getMessage()], 500);
}
