?php
// Localhost (පරිගණකයේ) සහ Online (අන්තර්ජාලයේ) යන දෙකටම වැඩ කරන ලෙස සකසා ඇත.
$server_name = $_SERVER['SERVER_NAME'];

if ($server_name == "localhost" || $server_name == "127.0.0.1") {
    // පරිගණකයේ (Local/XAMPP) දත්ත ගබඩාව සඳහා
    $conn = new mysqli("localhost", "root", "", "nursery_db");
} else {
    // අන්තර්ජාලයේ (Online/cPanel) දත්ත ගබඩාව සඳහා
    $conn = new mysqli("localhost", "nisansal_nursery", "Grsp@830390", "nisansal_nursery");
}

if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);
// මින් පහළට ඔබගේ අනෙකුත් කේතයන් සාමාන්‍ය පරිදි යොදන්න...

if(isset($_POST['upload'])) {
    $emp_id = $_POST['emp_id']; $filename = $_FILES["file"]["tmp_name"];
    if($_FILES["file"]["size"] > 0) {
        $file = fopen($filename, "r"); fgetcsv($file, 10000, ",");
        $processed_months = [];
        while (($data = fgetcsv($file, 10000, ",")) !== FALSE) {
            if (empty($data[0])) continue;
            $date_str = str_replace(['.', '/'], '-', $data[0]);
            $date = date('Y-m-d', strtotime($date_str)); $month = date('Y-m', strtotime($date));
            $time_in = !empty($data[1]) ? date('H:i:s', strtotime(str_replace('.', ':', $data[1]))) : '08:30:00';
            $time_out = !empty($data[2]) ? date('H:i:s', strtotime(str_replace('.', ':', $data[2]))) : '17:00:00';
            if (stripos($data[1], 'leave') !== false || stripos($data[1], 'නිවාඩු') !== false) {
                $time_in = '00:00:00'; $time_out = '00:00:00';
            }
            $days_pay = (float)($data[3] ?? 0); $tea_all = (float)($data[4] ?? 0); $ot = (float)($data[5] ?? 0);
            $calculated_pay = $days_pay + $tea_all + $ot;
            $desc = $conn->real_escape_string($data[7] ?? '');
            $s_advance = (float)($data[8] ?? 0);
            $work_category = 'nursery_full';
            if ($days_pay == 0) $work_category = 'leave_full';
            elseif ($days_pay <= 650) $work_category = 'leave_half';
            elseif ($days_pay <= 1300) $work_category = 'nursery_half';
            elseif ($days_pay == 1500) $work_category = 'family_hire';
            elseif ($days_pay >= 2000) $work_category = 'family_hire_12h';
            
            if ($calculated_pay > 0 || $work_category != 'leave_full') {
                $conn->query("INSERT INTO daily_attendance (emp_id, work_date, in_time, out_time, work_category, description, meal_allowance, tea_allowance, calculated_pay) VALUES ('$emp_id', '$date', '$time_in', '$time_out', '$work_category', '$desc', 0, '$tea_all', '$calculated_pay')");
            }
            if ($s_advance > 0) {
                $conn->query("INSERT INTO loans_advances (emp_id, loan_type, amount, granted_date, monthly_deduction) VALUES ('$emp_id', 'advance', '$s_advance', '$date', '$s_advance')");
            }
            
            // ඕනෑම සේවකයෙකුගේ මාසික ණය වාරිකය ස්වයංක්‍‍‍‍රීයව කැපීම
            $loan_info = $conn->query("SELECT amount, monthly_deduction, granted_date, interest_rate FROM loans_advances WHERE emp_id = $emp_id AND loan_type = 'long_term' ORDER BY granted_date DESC LIMIT 1");
            if ($loan_info->num_rows > 0 && !in_array($month, $processed_months)) {
                $ld = $loan_info->fetch_assoc();
                $initial_loan = $ld['amount']; $installment = $ld['monthly_deduction']; $granted_month = date('Y-m', strtotime($ld['granted_date']));
                $int_rate = $ld['interest_rate'] > 0 ? $ld['interest_rate'] : 13; // පොලී අනුපාතය ලබාගැනීම
                
                if ($month >= $granted_month) {
                    $due_date = $month . "-25";
                    $bal_query = $conn->query("SELECT balance_remaining FROM loan_payments WHERE emp_id = $emp_id ORDER BY due_date DESC LIMIT 1");
                    $balance = ($bal_query->num_rows > 0) ? $bal_query->fetch_assoc()['balance_remaining'] : $initial_loan;
                    
                    $interest = $balance * ($int_rate / 100 / 12);
                    $capital = $installment - $interest;
                    $new_balance = $balance - $capital;
                    
                    $conn->query("INSERT INTO loan_payments (emp_id, due_date, paid_date, payment_method, installment_amount, interest_amount, capital_amount, balance_remaining) VALUES ('$emp_id', '$due_date', '$due_date', 'salary', '$installment', '$interest', '$capital', '$new_balance')");
                    $processed_months[] = $month; 
                }
            }
        }
        fclose($file);
        echo "<script>alert('දත්ත යාවත්කාලීන විය!'); window.location='reports.php';</script>";
    }
}
?>
<!DOCTYPE html>
<html lang="si">
<head><meta charset="UTF-8"><title>Upload CSV</title>
<style>
    body { font-family: sans-serif; background: #f4f9f4; padding: 20px; }
    .container { max-width: 600px; margin: auto; background: white; padding: 30px; border-radius: 8px; box-shadow: 0 4px 8px rgba(0,0,0,0.1); text-align: center; }
    input[type="file"], select, button { width: 100%; padding: 10px; margin-top: 15px; font-size: 16px; box-sizing: border-box;}
    button { background: #2e7d32; color: white; border: none; cursor: pointer; font-weight: bold; border-radius: 5px;}
</style>
</head>
<body>
<div class="container">
    <h2 style="color: #2e7d32;">📁 Excel වාර්තා (CSV) ඇතුලත් කිරීම</h2>
    <form action="" method="post" enctype="multipart/form-data">
        <label style="font-weight:bold; display:block; text-align:left;">සේවකයා තෝරන්න:</label>
        <select name="emp_id" required>