<?php
require_once 'config/init.php';
require_once 'vendor/autoload.php';
require_once 'includes/auth.php';
requireAuth();

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;

$db = getDB();

// Handle form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $action = $_POST['action'] ?? '';
    
    if ($action === 'add' || $action === 'edit') {
        $meter_number = $_POST['meter_number'] ?? '';
        $full_name = $_POST['full_name'] ?? '';
        $national_id = $_POST['national_id'] ?? '';
        $address = $_POST['address'] ?? '';
        $family_members = intval($_POST['family_members'] ?? 1);
        
        if ($action === 'add') {
            $stmt = $db->prepare('INSERT INTO subscribers (meter_number, full_name, national_id, address, family_members) VALUES (:meter_number, :full_name, :national_id, :address, :family_members)');
        } else {
            $id = $_POST['id'] ?? 0;
            $stmt = $db->prepare('UPDATE subscribers SET meter_number = :meter_number, full_name = :full_name, national_id = :national_id, address = :address, family_members = :family_members WHERE id = :id');
            $stmt->bindValue(':id', $id, SQLITE3_INTEGER);
        }
        
        $stmt->bindValue(':meter_number', $meter_number);
        $stmt->bindValue(':full_name', $full_name);
        $stmt->bindValue(':national_id', $national_id);
        $stmt->bindValue(':address', $address);
        $stmt->bindValue(':family_members', $family_members, SQLITE3_INTEGER);
        
        try {
            $stmt->execute();
            logActivity($action === 'add' ? 'إضافة مشترك' : 'تعديل مشترك', "رقم العداد: $meter_number");
            header('Location: subscribers.php?success=1');
            exit;
        } catch (Exception $e) {
            $error = 'حدث خطأ أثناء حفظ البيانات';
        }
    } elseif ($action === 'delete') {
        $id = $_POST['id'] ?? 0;
        $stmt = $db->prepare('UPDATE subscribers SET status = 0 WHERE id = :id');
        $stmt->bindValue(':id', $id, SQLITE3_INTEGER);
        try {
            $stmt->execute();
            logActivity('حذف مشترك', "رقم المشترك: $id");
            header('Location: subscribers.php?success=1');
            exit;
        } catch (Exception $e) {
            $error = 'حدث خطأ أثناء حذف المشترك';
        }
    } elseif ($action === 'export') {
        // Create new Spreadsheet object
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        
        // Set headers
        $sheet->setCellValue('A1', 'رقم العداد');
        $sheet->setCellValue('B1', 'الاسم الكامل');
        $sheet->setCellValue('C1', 'رقم الهوية');
        $sheet->setCellValue('D1', 'العنوان');
        $sheet->setCellValue('E1', 'عدد أفراد الأسرة');
        
        // Get subscribers data
        $stmt = $db->query('SELECT meter_number, full_name, national_id, address, family_members FROM subscribers WHERE status = 1');
        $subscribers = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Add data to sheet
        $row = 2;
        foreach ($subscribers as $subscriber) {
            $sheet->setCellValue('A' . $row, $subscriber['meter_number']);
            $sheet->setCellValue('B' . $row, $subscriber['full_name']);
            $sheet->setCellValue('C' . $row, $subscriber['national_id']);
            $sheet->setCellValue('D' . $row, $subscriber['address']);
            $sheet->setCellValue('E' . $row, $subscriber['family_members']);
            $row++;
        }
        
        // Create writer and output file
        $writer = new Xlsx($spreadsheet);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="subscribers.xlsx"');
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        exit;
    } elseif ($action === 'import') {
        if (isset($_FILES['excel_file']) && $_FILES['excel_file']['error'] === UPLOAD_ERR_OK) {
            $reader = new XlsxReader();
            $spreadsheet = $reader->load($_FILES['excel_file']['tmp_name']);
            $worksheet = $spreadsheet->getActiveSheet();
            $rows = $worksheet->toArray();
            
            // Skip header row
            array_shift($rows);
            
            $stmt = $db->prepare('INSERT INTO subscribers (meter_number, full_name, national_id, address, family_members) VALUES (:meter_number, :full_name, :national_id, :address, :family_members)');
            
            foreach ($rows as $row) {
                if (!empty($row[0])) { // Check if meter number exists
                    $stmt->bindValue(':meter_number', $row[0]);
                    $stmt->bindValue(':full_name', $row[1] ?? '');
                    $stmt->bindValue(':national_id', $row[2] ?? '');
                    $stmt->bindValue(':address', $row[3] ?? '');
                    $stmt->bindValue(':family_members', intval($row[4] ?? 1), SQLITE3_INTEGER);
                    try {
                        $stmt->execute();
                    } catch (Exception $e) {
                        // Skip duplicate entries
                        continue;
                    }
                }
            }
            
            logActivity('استيراد المشتركين', 'تم استيراد المشتركين من ملف Excel');
            header('Location: subscribers.php?success=1');
            exit;
        } else {
            $error = 'حدث خطأ أثناء رفع الملف';
        }
    }
}

// Get subscribers list
$query = 'SELECT * FROM subscribers WHERE status = 1 ORDER BY id DESC';
$result = $db->query($query);
$subscribers = [];
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    $subscribers[] = $row;
}
?>
<!DOCTYPE html>
<html dir="rtl" lang="ar">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>إدارة المشتركين - نظام إدارة جمعيات توزيع الماء</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap-icons/font/bootstrap-icons.css" rel="stylesheet">
    <link href="https://cdn.datatables.net/1.10.24/css/dataTables.bootstrap5.min.css" rel="stylesheet">
</head>
<body>
    <div class="container-fluid">
        <div class="row">
            <!-- Include sidebar here -->
            <?php include 'includes/sidebar.php'; ?>

            <main class="col-md-9 ms-sm-auto col-lg-10 px-md-4">
                <div class="d-flex justify-content-between flex-wrap flex-md-nowrap align-items-center pt-3 pb-2 mb-3 border-bottom">
                    <h1 class="h2">إدارة المشتركين</h1>
                    <div class="btn-toolbar mb-2 mb-md-0">
                        <div class="btn-group me-2">
                            <button type="button" class="btn btn-sm btn-outline-primary" data-bs-toggle="modal" data-bs-target="#importModal">
                                استيراد من Excel
                            </button>
                            <form method="post" style="display: inline;">
                                <input type="hidden" name="action" value="export">
                                <button type="submit" class="btn btn-sm btn-outline-secondary">تصدير إلى Excel</button>
                            </form>
                            <button type="button" class="btn btn-sm btn-outline-success" data-bs-toggle="modal" data-bs-target="#addSubscriberModal">
                                إضافة مشترك
                            </button>
                        </div>
                    </div>
                </div>

                <?php if (isset($_GET['success'])): ?>
                    <div class="alert alert-success alert-dismissible fade show" role="alert">
                        تم حفظ البيانات بنجاح
                        <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
                    </div>
                <?php endif; ?>

                <?php if (isset($error)): ?>
                    <div class="alert alert-danger alert-dismissible fade show" role="alert">
                        <?php echo htmlspecialchars($error); ?>
                        <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
                    </div>
                <?php endif; ?>

                <!-- Subscribers Table -->
                <div class="table-responsive">
                    <table class="table table-striped table-hover" id="subscribersTable">
                        <thead>
                            <tr>
                                <th>رقم العداد</th>
                                <th>الاسم الكامل</th>
                                <th>رقم البطاقة الوطنية</th>
                                <th>العنوان</th>
                                <th>عدد أفراد الأسرة</th>
                                <th>تاريخ التسجيل</th>
                                <th>الإجراءات</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($subscribers as $subscriber): ?>
                                <tr>
                                    <td><?php echo htmlspecialchars($subscriber['meter_number']); ?></td>
                                    <td><?php echo htmlspecialchars($subscriber['full_name']); ?></td>
                                    <td><?php echo htmlspecialchars($subscriber['national_id']); ?></td>
                                    <td><?php echo htmlspecialchars($subscriber['address']); ?></td>
                                    <td><?php echo htmlspecialchars($subscriber['family_members']); ?></td>
                                    <td><?php echo date('Y-m-d', strtotime($subscriber['created_at'])); ?></td>
                                    <td>
                                        <button class="btn btn-sm btn-primary edit-subscriber" 
                                                data-id="<?php echo $subscriber['id']; ?>"
                                                data-meter="<?php echo htmlspecialchars($subscriber['meter_number']); ?>"
                                                data-name="<?php echo htmlspecialchars($subscriber['full_name']); ?>"
                                                data-national-id="<?php echo htmlspecialchars($subscriber['national_id']); ?>"
                                                data-address="<?php echo htmlspecialchars($subscriber['address']); ?>"
                                                data-family-members="<?php echo htmlspecialchars($subscriber['family_members']); ?>">
                                            <i class="bi bi-pencil"></i>
                                        </button>
                                        <button class="btn btn-sm btn-danger delete-subscriber" 
                                                data-id="<?php echo $subscriber['id']; ?>"
                                                data-name="<?php echo htmlspecialchars($subscriber['full_name']); ?>">
                                            <i class="bi bi-trash"></i>
                                        </button>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </main>
        </div>
    </div>

    <!-- Add Subscriber Modal -->
    <div class="modal fade" id="addSubscriberModal" tabindex="-1">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title">إضافة مشترك جديد</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
                </div>
                <form method="POST">
                    <div class="modal-body">
                        <input type="hidden" name="action" value="add">
                        <div class="mb-3">
                            <label class="form-label">رقم العداد</label>
                            <input type="text" class="form-control" name="meter_number" required>
                        </div>
                        <div class="mb-3">
                            <label class="form-label">الاسم الكامل</label>
                            <input type="text" class="form-control" name="full_name" required>
                        </div>
                        <div class="mb-3">
                            <label class="form-label">رقم البطاقة الوطنية</label>
                            <input type="text" class="form-control" name="national_id" required>
                        </div>
                        <div class="mb-3">
                            <label class="form-label">عدد أفراد الأسرة</label>
                            <input type="number" class="form-control" name="family_members" min="0" value="1" required>
                        </div>
                        <div class="mb-3">
                            <label class="form-label">العنوان</label>
                            <input type="text" class="form-control" name="address" required>
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">إلغاء</button>
                        <button type="submit" class="btn btn-primary">حفظ</button>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <!-- Edit Subscriber Modal -->
    <div class="modal fade" id="editSubscriberModal" tabindex="-1">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title">تعديل بيانات المشترك</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
                </div>
                <form method="POST">
                    <div class="modal-body">
                        <input type="hidden" name="action" value="edit">
                        <input type="hidden" name="id" id="edit_id">
                        <div class="mb-3">
                            <label class="form-label">رقم العداد</label>
                            <input type="text" class="form-control" name="meter_number" id="edit_meter_number" required>
                        </div>
                        <div class="mb-3">
                            <label class="form-label">الاسم الكامل</label>
                            <input type="text" class="form-control" name="full_name" id="edit_full_name" required>
                        </div>
                        <div class="mb-3">
                            <label class="form-label">رقم البطاقة الوطنية</label>
                            <input type="text" class="form-control" name="national_id" id="edit_national_id" required>
                        </div>
                        <div class="mb-3">
                            <label class="form-label">عدد أفراد الأسرة</label>
                            <input type="number" class="form-control" name="family_members" id="edit_family_members" min="0" value="0" required>
                        </div>
                        <div class="mb-3">
                            <label class="form-label">العنوان</label>
                            <input type="text" class="form-control" name="address" id="edit_address" required>
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">إلغاء</button>
                        <button type="submit" class="btn btn-primary">حفظ التغييرات</button>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <!-- Delete Confirmation Modal -->
    <div class="modal fade" id="deleteSubscriberModal" tabindex="-1">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title">تأكيد الحذف</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
                </div>
                <div class="modal-body">
                    هل أنت متأكد من حذف المشترك <span id="delete_subscriber_name"></span>؟
                </div>
                <div class="modal-footer">
                    <form method="POST">
                        <input type="hidden" name="action" value="delete">
                        <input type="hidden" name="id" id="delete_id">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">إلغاء</button>
                        <button type="submit" class="btn btn-danger">تأكيد الحذف</button>
                    </form>
                </div>
            </div>
        </div>
    </div>

    <!-- Import Modal -->
    <div class="modal fade" id="importModal" tabindex="-1">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title">استيراد المشتركين من Excel</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
                </div>
                <form method="post" enctype="multipart/form-data">
                    <div class="modal-body">
                        <input type="hidden" name="action" value="import">
                        <div class="mb-3">
                            <label for="excel_file" class="form-label">اختر ملف Excel</label>
                            <input type="file" class="form-control" id="excel_file" name="excel_file" accept=".xlsx" required>
                        </div>
                        <div class="alert alert-info">
                            يجب أن يحتوي الملف على الأعمدة التالية: رقم العداد، الاسم الكامل، رقم الهوية، العنوان، عدد أفراد الأسرة
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">إلغاء</button>
                        <button type="submit" class="btn btn-primary">استيراد</button>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.24/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.24/js/dataTables.bootstrap5.min.js"></script>
    <script>
        $(document).ready(function() {
            // Initialize DataTable
            $('#subscribersTable').DataTable({
                language: {
                    url: '//cdn.datatables.net/plug-ins/1.10.24/i18n/ar.json'
                }
            });

            // Handle edit button click
            $('.edit-subscriber').click(function() {
                $('#edit_id').val($(this).data('id'));
                $('#edit_meter_number').val($(this).data('meter'));
                $('#edit_full_name').val($(this).data('name'));
                $('#edit_national_id').val($(this).data('national-id'));
                $('#edit_family_members').val($(this).data('family-members'));
                $('#edit_address').val($(this).data('address'));
                $('#editSubscriberModal').modal('show');
            });

            // Handle delete button click
            $('.delete-subscriber').click(function() {
                $('#delete_id').val($(this).data('id'));
                $('#delete_subscriber_name').text($(this).data('name'));
                $('#deleteSubscriberModal').modal('show');
            });
        });
    </script>
</body>
</html>
