<?php
require_once 'config/init.php';
require_once 'includes/auth.php';
require_once 'includes/invoice_number.php';
require_once 'includes/billing.php';
requireAuth();

$db = getDB();
$success_msg = null;
$error_msg = null;

// Define consumption periods
$consumption_periods = [
    'monthly' => 'شهري',
    'bimonthly' => 'كل شهرين',
    'quarterly' => 'كل ثلاثة أشهر'
];

// Handle form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $reading_date = $_POST['reading_date'] ?? '';
    $consumption_period = $_POST['consumption_period'] ?? '';
    $readings = [];
    
    // Process readings from form
    foreach ($_POST['meter_number'] ?? [] as $i => $meter_number) {
        if (!empty($meter_number) && isset($_POST['current_reading'][$i])) {
            $readings[] = [
                'meter_number' => $meter_number,
                'current_reading' => floatval($_POST['current_reading'][$i])
            ];
        }
    }
    
    if (!empty($readings)) {
        try {
            $db->exec('BEGIN TRANSACTION');
            
            // Create billing calculator instance
            $billing = new BillingCalculator($db);
            
            foreach ($readings as $reading) {
                // Get subscriber ID
                $subscriber_id = $db->querySingle("SELECT id FROM subscribers WHERE meter_number = '" . SQLite3::escapeString($reading['meter_number']) . "'");
                if (!$subscriber_id) {
                    throw new Exception("رقم العداد غير موجود: " . $reading['meter_number']);
                }

                // Get previous reading
                $previous_reading = $db->querySingle("SELECT current_reading FROM consumption WHERE subscriber_id = $subscriber_id ORDER BY reading_date DESC LIMIT 1") ?? 0;

                // Validate reading
                $consumption = $reading['current_reading'] - $previous_reading;
                if ($consumption < 0) {
                    throw new Exception("القراءة الحالية أقل من القراءة السابقة للعداد: " . $reading['meter_number']);
                }

                // Insert consumption record
                $stmt = $db->prepare('INSERT INTO consumption (subscriber_id, previous_reading, current_reading, reading_date, consumption_period) VALUES (:subscriber_id, :previous_reading, :current_reading, :reading_date, :consumption_period)');
                $stmt->bindValue(':subscriber_id', $subscriber_id);
                $stmt->bindValue(':previous_reading', $previous_reading);
                $stmt->bindValue(':current_reading', $reading['current_reading']);
                $stmt->bindValue(':reading_date', $reading_date);
                $stmt->bindValue(':consumption_period', $consumption_period);
                $stmt->execute();
                
                // Get consumption ID
                $consumption_id = $db->lastInsertRowID();
                
                // Calculate due date (end of next month)
                $due_date = date('Y-m-d', strtotime('last day of next month', strtotime($reading_date)));
                
                // Create invoice using the billing calculator
                $billing->createInvoice($consumption_id, $due_date);
            }
            
            $db->exec('COMMIT');
            logActivity('إدخال قراءات جماعية', 'تم إدخال قراءات جماعية بنجاح');
            $success_msg = 'تم إدخال القراءات بنجاح';
            
        } catch (Exception $e) {
            try {
                $db->exec('ROLLBACK');
            } catch (Exception $rollbackError) {
                // Ignore rollback errors
            }
            $error_msg = $e->getMessage();
        }
    }
}

// Handle Excel file upload
if (isset($_FILES['excel_file']) && $_FILES['excel_file']['error'] === UPLOAD_ERR_OK) {
    require 'vendor/autoload.php';
    
    try {
        $inputFileName = $_FILES['excel_file']['tmp_name'];
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
        $worksheet = $spreadsheet->getActiveSheet();
        $readings = [];
        
        // Start from row 2 (assuming row 1 is header)
        $row = 2;
        while ($worksheet->getCell('A' . $row)->getValue() !== null) {
            $meter_number = $worksheet->getCell('A' . $row)->getValue();
            $current_reading = $worksheet->getCell('B' . $row)->getValue();
            
            if (!empty($meter_number) && !empty($current_reading)) {
                $readings[] = [
                    'meter_number' => trim($meter_number),
                    'current_reading' => floatval($current_reading)
                ];
            }
            $row++;
        }
        
        if (!empty($readings)) {
            // Process readings similar to form submission
            $reading_date = $_POST['reading_date'] ?? '';
            $consumption_period = $_POST['consumption_period'] ?? '';
            
            if (empty($reading_date) || empty($consumption_period)) {
                throw new Exception("يرجى تحديد تاريخ القراءة وفترة الاستهلاك");
            }
            
            $db->exec('BEGIN TRANSACTION');
            $billing = new BillingCalculator($db);
            
            foreach ($readings as $reading) {
                // Get subscriber ID
                $subscriber_id = $db->querySingle("SELECT id FROM subscribers WHERE meter_number = '" . SQLite3::escapeString($reading['meter_number']) . "'");
                if (!$subscriber_id) {
                    throw new Exception("رقم العداد غير موجود: " . $reading['meter_number']);
                }

                // Get previous reading
                $previous_reading = $db->querySingle("SELECT current_reading FROM consumption WHERE subscriber_id = $subscriber_id ORDER BY reading_date DESC LIMIT 1") ?? 0;

                // Validate reading
                $consumption = $reading['current_reading'] - $previous_reading;
                if ($consumption < 0) {
                    throw new Exception("القراءة الحالية أقل من القراءة السابقة للعداد: " . $reading['meter_number']);
                }

                // Insert consumption record
                $stmt = $db->prepare('INSERT INTO consumption (subscriber_id, previous_reading, current_reading, reading_date, consumption_period) VALUES (:subscriber_id, :previous_reading, :current_reading, :reading_date, :consumption_period)');
                $stmt->bindValue(':subscriber_id', $subscriber_id);
                $stmt->bindValue(':previous_reading', $previous_reading);
                $stmt->bindValue(':current_reading', $reading['current_reading']);
                $stmt->bindValue(':reading_date', $reading_date);
                $stmt->bindValue(':consumption_period', $consumption_period);
                $stmt->execute();
                
                // Get consumption ID
                $consumption_id = $db->lastInsertRowID();
                
                // Calculate due date
                $due_date = date('Y-m-d', strtotime('last day of next month', strtotime($reading_date)));
                
                // Create invoice
                $billing->createInvoice($consumption_id, $due_date);
            }
            
            $db->exec('COMMIT');
            logActivity('استيراد قراءات من ملف Excel', 'تم استيراد القراءات بنجاح');
            $success_msg = 'تم استيراد القراءات بنجاح';
        }
    } catch (Exception $e) {
        try {
            $db->exec('ROLLBACK');
        } catch (Exception $rollbackError) {
            // Ignore rollback errors
        }
        $error_msg = $e->getMessage();
    }
}

// Get all subscribers for the form
$subscribers_query = "SELECT id, meter_number, full_name FROM subscribers WHERE status = 1 ORDER BY CAST(meter_number AS INTEGER)";
$subscribers_result = $db->query($subscribers_query);
?>
<!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">
</head>
<body>
    <div class="container-fluid">
        <div class="row">
            <?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>

                <?php if ($success_msg): ?>
                    <div class="alert alert-success alert-dismissible fade show" role="alert">
                        <?php echo htmlspecialchars($success_msg); ?>
                        <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
                    </div>
                <?php endif; ?>

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

                <form method="POST" class="card">
                    <div class="card-body">
                        <div class="row mb-3">
                            <div class="col-md-4">
                                <label class="form-label">تاريخ القراءة</label>
                                <input type="date" class="form-control" name="reading_date" required>
                            </div>
                            
                            <div class="col-md-4">
                                <label class="form-label">فترة الاستهلاك</label>
                                <select class="form-select" name="consumption_period" required>
                                    <?php foreach ($consumption_periods as $period => $label): ?>
                                        <option value="<?php echo $period; ?>"><?php echo $label; ?></option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                        </div>

                        <div class="table-responsive">
                            <table class="table table-striped table-bordered">
                                <thead>
                                    <tr>
                                        <th>رقم العداد</th>
                                        <th>اسم المشترك</th>
                                        <th>القراءة الحالية</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php while ($subscriber = $subscribers_result->fetchArray(SQLITE3_ASSOC)): ?>
                                        <tr>
                                            <td>
                                                <input type="text" class="form-control" name="meter_number[]" 
                                                       value="<?php echo htmlspecialchars($subscriber['meter_number']); ?>" readonly>
                                            </td>
                                            <td><?php echo htmlspecialchars($subscriber['full_name']); ?></td>
                                            <td>
                                                <input type="number" step="1" class="form-control" name="current_reading[]">
                                            </td>
                                        </tr>
                                    <?php endwhile; ?>
                                </tbody>
                            </table>
                        </div>

                        <button type="submit" class="btn btn-primary">حفظ القراءات</button>
                    </div>
                </form>

                <div class="card mb-4">
                    <div class="card-header">
                        <h5 class="mb-0">استيراد القراءات من ملف Excel</h5>
                    </div>
                    <div class="card-body">
                        <form method="post" enctype="multipart/form-data">
                            <div class="row">
                                <div class="col-md-4">
                                    <div class="form-group mb-3">
                                        <label for="excel_file">ملف Excel</label>
                                        <input type="file" class="form-control" id="excel_file" name="excel_file" accept=".xlsx,.xls" required>
                                        <small class="form-text text-muted">يجب أن يحتوي الملف على عمودين: العمود A لرقم العداد والعمود B للقراءة الحالية</small>
                                    </div>
                                </div>
                                <div class="col-md-4">
                                    <div class="form-group mb-3">
                                        <label for="reading_date">تاريخ القراءة</label>
                                        <input type="date" class="form-control" id="reading_date" name="reading_date" required>
                                    </div>
                                </div>
                                <div class="col-md-4">
                                    <div class="form-group mb-3">
                                        <label for="consumption_period">فترة الاستهلاك</label>
                                        <select class="form-control" id="consumption_period" name="consumption_period" required>
                                            <option value="">اختر فترة الاستهلاك</option>
                                            <?php foreach ($consumption_periods as $value => $label): ?>
                                                <option value="<?php echo $value; ?>"><?php echo $label; ?></option>
                                            <?php endforeach; ?>
                                        </select>
                                    </div>
                                </div>
                            </div>
                            <button type="submit" class="btn btn-primary">استيراد القراءات</button>
                        </form>
                    </div>
                </div>
            </main>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
