<?php
require_once 'auth.php';
requireLogin();
if (!canExport()) { header('Location: dashboard.php'); exit; }

$type = $_GET['type'] ?? 'all';
// all | customers | companies | device_names | device_models | devices | maintenance | warranty

// تحميل PhpSpreadsheet إذا كان متوفراً
$useSpreadsheet = file_exists(__DIR__ . '/vendor/autoload.php');

function formatIraqDateTime12($value) {
    if (empty($value)) return '';
    try {
        $dt = new DateTime((string)$value, new DateTimeZone('Asia/Baghdad'));
        $dt->setTimezone(new DateTimeZone('Asia/Baghdad'));
        $period = ((int)$dt->format('H') < 12) ? 'ص' : 'م';
        return $dt->format('Y-m-d h:i:s') . ' ' . $period;
    } catch (Exception $e) {
        return (string)$value;
    }
}

if (!$useSpreadsheet) {
    // تصدير CSV بديل عند عدم وجود PhpSpreadsheet
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="export_' . $type . '_' . date('Y-m-d') . '.csv"');
    $out = fopen('php://output', 'w');
    fprintf($out, chr(0xEF).chr(0xBB).chr(0xBF)); // BOM for UTF-8
    
    if ($type === 'device_names') {
        $r = $conn->query("SELECT dn.id, dn.name, (SELECT COUNT(*) FROM devices d WHERE d.device_name_id = dn.id) as device_count FROM device_names dn ORDER BY dn.name");
        fputcsv($out, ['#', 'اسم الجهاز', 'عدد الأجهزة']);
        while ($row = $r->fetch_assoc()) {
            fputcsv($out, [$row['id'], $row['name'], $row['device_count'] ?? 0]);
        }
    } elseif ($type === 'device_models') {
        $r = $conn->query("SELECT dm.id, dn.name as device_name, c.name as company_name, dm.model_name, (SELECT COUNT(*) FROM devices d WHERE d.device_model_id = dm.id) as device_count FROM device_models dm JOIN device_names dn ON dm.device_name_id = dn.id JOIN companies c ON dm.company_id = c.id ORDER BY dn.name, c.name, dm.model_name");
        fputcsv($out, ['#', 'اسم الجهاز', 'الشركة', 'الموديل', 'عدد الأجهزة']);
        while ($row = $r->fetch_assoc()) {
            fputcsv($out, [$row['id'], $row['device_name'], $row['company_name'], $row['model_name'], $row['device_count'] ?? 0]);
        }
    } elseif ($type === 'companies') {
        $r = $conn->query("SELECT c.id, c.name, (SELECT COUNT(*) FROM devices d WHERE d.company_id = c.id) as device_count FROM companies c ORDER BY c.name");
        fputcsv($out, ['#', 'اسم الشركة', 'عدد الأجهزة']);
        while ($row = $r->fetch_assoc()) {
            fputcsv($out, [$row['id'], $row['name'], $row['device_count'] ?? 0]);
        }
    } elseif ($type === 'customers') {
        $r = $conn->query("SELECT c.id, c.name, c.hospital_name, c.phone, g.name_ar as governorate FROM customers c LEFT JOIN governorates g ON c.governorate_id = g.id ORDER BY c.name");
        fputcsv($out, ['#', 'الاسم', 'المستشفى', 'الهاتف', 'المحافظة']);
        while ($row = $r->fetch_assoc()) {
            fputcsv($out, [$row['id'], $row['name'], $row['hospital_name'], $row['phone'], $row['governorate'] ?? '']);
        }
    } elseif ($type === 'hospitals') {
        $r = $conn->query("SELECT h.*, g.name_ar as governorate FROM hospitals h JOIN governorates g ON h.governorate_id = g.id ORDER BY h.name");
        fputcsv($out, ['#', 'الاسم', 'اسم المسؤول', 'المحافظة', 'الهاتف', 'القطاع']);
        while ($row = $r->fetch_assoc()) {
            fputcsv($out, [$row['id'], $row['name'], $row['responsible_person'] ?? '', $row['governorate'], $row['phone'], $row['sector']]);
        }
    } elseif ($type === 'clinics') {
        $r = $conn->query("SELECT c.id, c.name, g.name_ar as governorate, c.phone, c.sector FROM clinics c JOIN governorates g ON c.governorate_id = g.id ORDER BY c.name");
        fputcsv($out, ['#', 'الاسم', 'المحافظة', 'الهاتف', 'القطاع']);
        while ($row = $r->fetch_assoc()) {
            fputcsv($out, [$row['id'], $row['name'], $row['governorate'], $row['phone'], $row['sector']]);
        }
    } elseif ($type === 'engineers') {
        $r = $conn->query("SELECT e.id, e.name, (SELECT COUNT(*) FROM devices d WHERE d.engineer_id = e.id) as device_count FROM engineers e ORDER BY e.name");
        fputcsv($out, ['#', 'اسم المهندس', 'عدد الأجهزة']);
        while ($row = $r->fetch_assoc()) {
            fputcsv($out, [$row['id'], $row['name'], $row['device_count'] ?? 0]);
        }
    } else {
        $devices_sql = "SELECT d.*, c.name as customer_name, g.name_ar as gov_name, dn.name as device_name, dm.model_name, co.name as company_name, e.name as engineer_name, DATE_ADD(d.installation_date, INTERVAL d.warranty_months MONTH) as warranty_end, (SELECT MAX(created_at) FROM job_sheet_images WHERE device_id = d.id) as last_maintenance FROM devices d JOIN customers c ON d.customer_id = c.id JOIN governorates g ON d.governorate_id = g.id JOIN device_names dn ON d.device_name_id = dn.id JOIN device_models dm ON d.device_model_id = dm.id JOIN companies co ON d.company_id = co.id JOIN engineers e ON d.engineer_id = e.id ORDER BY d.id";
        $r = $conn->query($devices_sql);
        fputcsv($out, ['#', 'رقم الفاتورة', 'الزبون', 'المستشفى', 'الهاتف', 'المحافظة', 'الجهاز', 'الموديل', 'الشركة', 'العدد', 'تاريخ التنصيب', 'الضمان (شهر)', 'نهاية الضمان', 'المهندس', 'تفاصيل الزيارة', 'تاريخ التسجيل', 'الملاحظات']);
        while ($row = $r->fetch_assoc()) {
            if ($type === 'maintenance') {
                $last = $row['last_maintenance'] ? strtotime($row['last_maintenance']) : strtotime($row['installation_date']);
                if ($last > strtotime('-3 months')) continue;
            }
            if ($type === 'warranty') {
                // تصدير الجميع مع حالة الضمان
            }
            $warranty_end = $row['warranty_end'] ?? '';
            $status = ($warranty_end && strtotime($warranty_end) < time()) ? 'منتهي' : 'فعال';
            fputcsv($out, [$row['id'], $row['invoice_number'], $row['customer_name'], $row['hospital_name'], $row['phone'], $row['gov_name'] ?? '', $row['device_name'], $row['model_name'], $row['company_name'], $row['quantity'], $row['installation_date'], $row['warranty_months'], $warranty_end, $row['engineer_name'], $row['visit_type'] ?? '', formatIraqDateTime12($row['created_at'] ?? ''), $row['notes'] ?? '']);
        }
    }
    fclose($out);
    exit;
}

require __DIR__ . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;

$spreadsheet = new Spreadsheet();

if ($type === 'companies') {
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('الشركات');
    $sheet->setRightToLeft(true);
    $headers = ['#', 'اسم الشركة', 'عدد الأجهزة'];
    $sheet->fromArray($headers, null, 'A1');
    $sheet->getStyle('A1:C1')->getFont()->setBold(true)->setSize(12);
    $sheet->getStyle('A1:C1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('1e3a5f');
    $sheet->getStyle('A1:C1')->getFont()->getColor()->setRGB('FFFFFF');
    $r = $conn->query("SELECT c.id, c.name, (SELECT COUNT(*) FROM devices d WHERE d.company_id = c.id) as device_count FROM companies c ORDER BY c.name");
    $row = 2;
    while ($d = $r->fetch_assoc()) {
        $sheet->fromArray([$d['id'], $d['name'], $d['device_count'] ?? 0], null, 'A' . $row);
        $row++;
    }
    foreach (range('A', 'C') as $col) $sheet->getColumnDimension($col)->setAutoSize(true);
} elseif ($type === 'device_names') {
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('أسماء الأجهزة');
    $sheet->setRightToLeft(true);
    $headers = ['#', 'اسم الجهاز', 'عدد الأجهزة'];
    $sheet->fromArray($headers, null, 'A1');
    $sheet->getStyle('A1:C1')->getFont()->setBold(true)->setSize(12);
    $sheet->getStyle('A1:C1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('1e3a5f');
    $sheet->getStyle('A1:C1')->getFont()->getColor()->setRGB('FFFFFF');
    $r = $conn->query("SELECT dn.id, dn.name, (SELECT COUNT(*) FROM devices d WHERE d.device_name_id = dn.id) as device_count FROM device_names dn ORDER BY dn.name");
    $row = 2;
    while ($d = $r->fetch_assoc()) {
        $sheet->fromArray([$d['id'], $d['name'], $d['device_count'] ?? 0], null, 'A' . $row);
        $row++;
    }
    foreach (range('A', 'C') as $col) $sheet->getColumnDimension($col)->setAutoSize(true);
} elseif ($type === 'device_models') {
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('موديلات الأجهزة');
    $sheet->setRightToLeft(true);
    $headers = ['#', 'اسم الجهاز', 'الشركة', 'الموديل', 'عدد الأجهزة'];
    $sheet->fromArray($headers, null, 'A1');
    $sheet->getStyle('A1:E1')->getFont()->setBold(true)->setSize(12);
    $sheet->getStyle('A1:E1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('1e3a5f');
    $sheet->getStyle('A1:E1')->getFont()->getColor()->setRGB('FFFFFF');
    $r = $conn->query("SELECT dm.id, dn.name as device_name, c.name as company_name, dm.model_name, (SELECT COUNT(*) FROM devices d WHERE d.device_model_id = dm.id) as device_count FROM device_models dm JOIN device_names dn ON dm.device_name_id = dn.id JOIN companies c ON dm.company_id = c.id ORDER BY dn.name, c.name, dm.model_name");
    $row = 2;
    while ($d = $r->fetch_assoc()) {
        $sheet->fromArray([$d['id'], $d['device_name'], $d['company_name'], $d['model_name'], $d['device_count'] ?? 0], null, 'A' . $row);
        $row++;
    }
    foreach (range('A', 'E') as $col) $sheet->getColumnDimension($col)->setAutoSize(true);
} elseif ($type === 'devices') {
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('الأجهزة');
    $sheet->setRightToLeft(true);
    $headers = ['#', 'رقم الفاتورة', 'الزبون', 'المستشفى', 'الهاتف', 'المحافظة', 'الجهاز', 'الموديل', 'الشركة', 'العدد', 'تاريخ التنصيب', 'الضمان (شهر)', 'المهندس', 'تفاصيل الزيارة', 'تاريخ التسجيل', 'الملاحظات'];
    $sheet->fromArray($headers, null, 'A1');
    $sheet->getStyle('A1:P1')->getFont()->setBold(true)->setSize(12);
    $sheet->getStyle('A1:P1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('1e3a5f');
    $sheet->getStyle('A1:P1')->getFont()->getColor()->setRGB('FFFFFF');
    $devices_sql = "SELECT d.*, c.name as customer_name, g.name_ar as gov_name, dn.name as device_name, dm.model_name, co.name as company_name, e.name as engineer_name FROM devices d JOIN customers c ON d.customer_id = c.id JOIN governorates g ON d.governorate_id = g.id JOIN device_names dn ON d.device_name_id = dn.id JOIN device_models dm ON d.device_model_id = dm.id JOIN companies co ON d.company_id = co.id JOIN engineers e ON d.engineer_id = e.id ORDER BY d.id";
    $r = $conn->query($devices_sql);
    $row = 2;
    while ($d = $r->fetch_assoc()) {
        $sheet->fromArray([$d['id'], $d['invoice_number'], $d['customer_name'], $d['hospital_name'], $d['phone'], $d['gov_name'] ?? '', $d['device_name'], $d['model_name'], $d['company_name'], $d['quantity'], $d['installation_date'], $d['warranty_months'], $d['engineer_name'], $d['visit_type'] ?? '', formatIraqDateTime12($d['created_at'] ?? ''), $d['notes'] ?? ''], null, 'A' . $row);
        $row++;
    }
    foreach (range('A', 'P') as $col) $sheet->getColumnDimension($col)->setAutoSize(true);
} elseif ($type === 'hospitals') {
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('المستشفيات');
    $sheet->setRightToLeft(true);
    $headers = ['#', 'الاسم', 'اسم المسؤول', 'المحافظة', 'الهاتف', 'القطاع'];
    $sheet->fromArray($headers, null, 'A1');
    $sheet->getStyle('A1:F1')->getFont()->setBold(true)->setSize(12);
    $sheet->getStyle('A1:F1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('1e3a5f');
    $sheet->getStyle('A1:F1')->getFont()->getColor()->setRGB('FFFFFF');
    $r = $conn->query("SELECT h.*, g.name_ar as governorate FROM hospitals h JOIN governorates g ON h.governorate_id = g.id ORDER BY h.name");
    $row = 2;
    while ($d = $r->fetch_assoc()) {
        $sheet->fromArray([$d['id'], $d['name'], $d['responsible_person'] ?? '', $d['governorate'], $d['phone'], $d['sector']], null, 'A' . $row);
        $row++;
    }
    foreach (range('A', 'F') as $col) $sheet->getColumnDimension($col)->setAutoSize(true);
} elseif ($type === 'clinics') {
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('العيادات');
    $sheet->setRightToLeft(true);
    $headers = ['#', 'الاسم', 'المحافظة', 'الهاتف', 'القطاع'];
    $sheet->fromArray($headers, null, 'A1');
    $sheet->getStyle('A1:E1')->getFont()->setBold(true)->setSize(12);
    $sheet->getStyle('A1:E1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('1e3a5f');
    $sheet->getStyle('A1:E1')->getFont()->getColor()->setRGB('FFFFFF');
    $r = $conn->query("SELECT c.id, c.name, g.name_ar as governorate, c.phone, c.sector FROM clinics c JOIN governorates g ON c.governorate_id = g.id ORDER BY c.name");
    $row = 2;
    while ($d = $r->fetch_assoc()) {
        $sheet->fromArray([$d['id'], $d['name'], $d['governorate'], $d['phone'], $d['sector']], null, 'A' . $row);
        $row++;
    }
    foreach (range('A', 'E') as $col) $sheet->getColumnDimension($col)->setAutoSize(true);
} elseif ($type === 'engineers') {
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('المهندسين');
    $sheet->setRightToLeft(true);
    $headers = ['#', 'اسم المهندس', 'عدد الأجهزة'];
    $sheet->fromArray($headers, null, 'A1');
    $sheet->getStyle('A1:C1')->getFont()->setBold(true)->setSize(12);
    $sheet->getStyle('A1:C1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('1e3a5f');
    $sheet->getStyle('A1:C1')->getFont()->getColor()->setRGB('FFFFFF');
    $r = $conn->query("SELECT e.id, e.name, (SELECT COUNT(*) FROM devices d WHERE d.engineer_id = e.id) as device_count FROM engineers e ORDER BY e.name");
    $row = 2;
    while ($d = $r->fetch_assoc()) {
        $sheet->fromArray([$d['id'], $d['name'], $d['device_count'] ?? 0], null, 'A' . $row);
        $row++;
    }
    foreach (range('A', 'C') as $col) $sheet->getColumnDimension($col)->setAutoSize(true);
} elseif ($type === 'customers') {
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('الزبائن');
    $sheet->setRightToLeft(true);
    
    $headers = ['#', 'الاسم', 'المستشفى', 'الهاتف', 'المحافظة'];
    $sheet->fromArray($headers, null, 'A1');
    $sheet->getStyle('A1:E1')->getFont()->setBold(true)->setSize(12);
    $sheet->getStyle('A1:E1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('1e3a5f');
    $sheet->getStyle('A1:E1')->getFont()->getColor()->setRGB('FFFFFF');
    
    $r = $conn->query("SELECT c.id, c.name, c.hospital_name, c.phone, g.name_ar as governorate FROM customers c LEFT JOIN governorates g ON c.governorate_id = g.id ORDER BY c.name");
    $row = 2;
    while ($d = $r->fetch_assoc()) {
        $sheet->fromArray([$d['id'], $d['name'], $d['hospital_name'], $d['phone'], $d['governorate'] ?? ''], null, 'A' . $row);
        $row++;
    }
    foreach (range('A', 'E') as $col) $sheet->getColumnDimension($col)->setAutoSize(true);
} else {
    // شيت الزبائن
    $sheet1 = $spreadsheet->getActiveSheet();
    $sheet1->setTitle('الزبائن');
    $sheet1->setRightToLeft(true);
    $headers = ['#', 'الاسم', 'المستشفى', 'الهاتف', 'المحافظة'];
    $sheet1->fromArray($headers, null, 'A1');
    $sheet1->getStyle('A1:E1')->getFont()->setBold(true);
    $sheet1->getStyle('A1:E1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('1e3a5f');
    $sheet1->getStyle('A1:E1')->getFont()->getColor()->setRGB('FFFFFF');
    $r = $conn->query("SELECT c.id, c.name, c.hospital_name, c.phone, g.name_ar as governorate FROM customers c LEFT JOIN governorates g ON c.governorate_id = g.id ORDER BY c.name");
    $row = 2;
    while ($d = $r->fetch_assoc()) {
        $sheet1->fromArray([$d['id'], $d['name'], $d['hospital_name'], $d['phone'], $d['governorate'] ?? ''], null, 'A' . $row);
        $row++;
    }
    foreach (range('A', 'E') as $col) $sheet1->getColumnDimension($col)->setAutoSize(true);
    
    // شيت الأجهزة
    $sheet2 = $spreadsheet->addSheet(new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'الأجهزة'));
    $sheet2->setRightToLeft(true);
    $headers2 = ['#', 'رقم الفاتورة', 'الزبون', 'المستشفى', 'الهاتف', 'المحافظة', 'الجهاز', 'الموديل', 'الشركة', 'العدد', 'تاريخ التنصيب', 'الضمان (شهر)', 'نهاية الضمان', 'المهندس', 'تفاصيل الزيارة', 'تاريخ التسجيل', 'الملاحظات', 'حالة الضمان'];
    $sheet2->fromArray($headers2, null, 'A1');
    $sheet2->getStyle('A1:R1')->getFont()->setBold(true);
    $sheet2->getStyle('A1:R1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('1e3a5f');
    $sheet2->getStyle('A1:R1')->getFont()->getColor()->setRGB('FFFFFF');
    
    $devices_sql = "SELECT d.*, c.name as customer_name, g.name_ar as gov_name, dn.name as device_name, dm.model_name, co.name as company_name, e.name as engineer_name, DATE_ADD(d.installation_date, INTERVAL d.warranty_months MONTH) as warranty_end, (SELECT MAX(created_at) FROM job_sheet_images WHERE device_id = d.id) as last_maintenance FROM devices d JOIN customers c ON d.customer_id = c.id JOIN governorates g ON d.governorate_id = g.id JOIN device_names dn ON d.device_name_id = dn.id JOIN device_models dm ON d.device_model_id = dm.id JOIN companies co ON d.company_id = co.id JOIN engineers e ON d.engineer_id = e.id ORDER BY d.id";
    $r = $conn->query($devices_sql);
    $row = 2;
    while ($d = $r->fetch_assoc()) {
        if ($type === 'maintenance') {
            $last = $d['last_maintenance'] ? strtotime($d['last_maintenance']) : strtotime($d['installation_date']);
            if ($last > strtotime('-3 months')) continue;
        }
        $warranty_end = $d['warranty_end'] ?? '';
        $expired = $warranty_end && strtotime($warranty_end) < time();
        $status = $expired ? 'منتهي' : 'فعال';
        $sheet2->fromArray([$d['id'], $d['invoice_number'], $d['customer_name'], $d['hospital_name'], $d['phone'], $d['gov_name'] ?? '', $d['device_name'], $d['model_name'], $d['company_name'], $d['quantity'], $d['installation_date'], $d['warranty_months'], $warranty_end, $d['engineer_name'], $d['visit_type'] ?? '', formatIraqDateTime12($d['created_at'] ?? ''), $d['notes'] ?? '', $status], null, 'A' . $row);
        if ($expired) {
            $sheet2->getStyle('A' . $row . ':R' . $row)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('ffebee');
            $sheet2->getStyle('A' . $row . ':R' . $row)->getFont()->getColor()->setRGB('c62828');
        } else {
            $sheet2->getStyle('A' . $row . ':R' . $row)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('e8f5e9');
            $sheet2->getStyle('A' . $row . ':R' . $row)->getFont()->getColor()->setRGB('2e7d32');
        }
        $row++;
    }
    foreach (range('A', 'R') as $col) $sheet2->getColumnDimension($col)->setAutoSize(true);
}

$filename = 'Maintenance_Export_' . date('Y-m-d_His') . '.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . $filename . '"');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;
