<?php
// admin/export-payment-excel.php - خروجی Excel حرفه‌ای گزارش پرداخت

error_reporting(0);
ini_set('display_errors', 0);

session_start();
require_once '../config/database.php';
require_once '../includes/db.php';
require_once '../includes/auth.php';
require_once '../includes/functions.php';

if (!isLoggedIn()) {
    die('ابتدا وارد شوید.');
}

$user = getCurrentUser();
if (!in_array($user['role_level'], [1, 2, 3, 5])) {
    die('دسترسی غیرمجاز.');
}

// ===== دریافت فیلترها =====
$teacher_id = isset($_GET['teacher_id']) ? (int)$_GET['teacher_id'] : 0;
$term_id = isset($_GET['term_id']) ? (int)$_GET['term_id'] : 0;
$degree = isset($_GET['degree']) ? (int)$_GET['degree'] : 0;
$search = isset($_GET['search']) ? trim($_GET['search']) : '';

// ===== دریافت تنظیمات مالیات و بیمه =====
$taxSettings = getTaxSettings();
$taxPercent = $taxSettings['tax_percent'] ?? 0;
$insurancePercent = $taxSettings['insurance_percent'] ?? 0;

// ===== شرط‌های جستجو =====
$whereConditions = ["u.role_id = (SELECT id FROM roles WHERE level = 6)", "u.is_active = 1"];
$params = [];

// ===== جستجو بر اساس نام/کد ملی =====
if (!empty($search) && $teacher_id == 0) {
    $whereConditions[] = "(u.name LIKE ? OR u.family LIKE ? OR u.national_code LIKE ?)";
    $searchTerm = "%$search%";
    $params[] = $searchTerm;
    $params[] = $searchTerm;
    $params[] = $searchTerm;
}

if ($teacher_id > 0) {
    $whereConditions[] = "u.id = ?";
    $params[] = $teacher_id;
}

if ($term_id > 0) {
    $whereConditions[] = "c.term_id = ?";
    $params[] = $term_id;
}

if ($degree > 0) {
    $whereConditions[] = "u.degree = ?";
    $params[] = $degree;
}

$whereSQL = implode(' AND ', $whereConditions);

// ===== کوئری اصلی =====
$sql = "
    SELECT 
        u.id as teacher_id,
        u.name,
        u.family,
        u.national_code,
        u.sheba_number,
        u.degree,
        c.id as course_id,
        c.name as course_name,
        c.course_type,
        c.units,
        c.hours,
        COALESCE(cs.sessions_count, 0) as sessions_count,
        COALESCE(cs.student_count, 0) as student_count,
        t.id as term_id,
        t.name as term_name,
        t.code as term_code,
        s.amount_per_hour,
        s.amount_per_student
    FROM users u
    LEFT JOIN courses c ON c.teacher_id = u.id
    LEFT JOIN terms t ON c.term_id = t.id
    LEFT JOIN course_sessions cs ON cs.course_id = c.id AND cs.term_id = t.id
    LEFT JOIN salary_settings s ON s.degree = u.degree AND s.course_type = c.course_type
    WHERE $whereSQL
    ORDER BY u.family ASC, u.name ASC, t.id DESC, c.name ASC
";

$results = fetchAll($sql, $params);

// ============================================================
// تنظیم هدر CSV (Excel با فرمت CSV)
// ============================================================
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="گزارش_پرداخت_' . date('Ymd_His') . '.csv"');

$output = fopen('php://output', 'w');
fwrite($output, "\xEF\xBB\xBF"); // BOM برای UTF-8

// ============================================================
// هدر ستون‌ها (راست به چپ)
// ============================================================
fputcsv($output, [
    'ردیف',
    'نام استاد',
    'کد ملی',
    'نام درس',
    'ترم',
    'نوع درس',
    'تعداد جلسات',
    'تعداد دانشجویان',
    'مبلغ واحد (ریال)',
    'مبلغ کل (ریال)',
    'مالیات (ریال)',
    'بیمه (ریال)',
    'مبلغ نهایی (ریال)'
]);

// ============================================================
// نوشتن داده‌ها
// ============================================================
$i = 1;
$totalAmount = 0;
$totalTax = 0;
$totalInsurance = 0;
$totalNet = 0;

foreach ($results as $row) {
    if ($row['course_type'] == 'theoretical') {
        $courseAmount = ($row['sessions_count'] ?? 0) * ($row['hours'] ?? 1) * ($row['amount_per_hour'] ?? 0);
        $unitPrice = $row['amount_per_hour'] ?? 0;
    } else {
        $courseAmount = ($row['student_count'] ?? 0) * ($row['amount_per_student'] ?? 0);
        $unitPrice = $row['amount_per_student'] ?? 0;
    }
    
    $courseTax = $courseAmount * ($taxPercent / 100);
    $courseInsurance = $courseAmount * ($insurancePercent / 100);
    $courseNet = $courseAmount - $courseTax - $courseInsurance;
    
    $totalAmount += $courseAmount;
    $totalTax += $courseTax;
    $totalInsurance += $courseInsurance;
    $totalNet += $courseNet;
    
    fputcsv($output, [
        $i++,
        $row['name'] . ' ' . $row['family'],
        $row['national_code'],
        $row['course_name'],
        $row['term_name'] ?? 'بدون ترم',
        getCourseTypeName($row['course_type']),
        $row['sessions_count'],
        $row['student_count'],
        number_format($unitPrice),
        number_format($courseAmount),
        number_format($courseTax),
        number_format($courseInsurance),
        number_format($courseNet)
    ]);
}

// ============================================================
// ردیف جمع کل
// ============================================================
fputcsv($output, []);
fputcsv($output, [
    'جمع کل',
    '',
    '',
    '',
    '',
    '',
    '',
    '',
    '',
    number_format($totalAmount),
    number_format($totalTax),
    number_format($totalInsurance),
    number_format($totalNet)
]);

fclose($output);
exit;
?>