<?php
require_once __DIR__ . '/BaseModel.php';

class QuotationRequest extends BaseModel {
    protected $table = 'quotation_requests';

    public function paginateWithDetails($page = 1, $perPage = 10) {
        $offset = ($page - 1) * $perPage;
        
        $stmt = $this->db->prepare("
            SELECT qr.*, c.full_name as customer_name, v.agency_name, m.full_name as maid_name,
                   sm.name_en as service_model_name,
                   at.name_en as accommodation_type_name,
                   n.name_en as preferred_nationality_name
            FROM {$this->table} qr
            LEFT JOIN customers c ON qr.customer_id = c.id
            LEFT JOIN vendors v ON qr.vendor_id = v.id
            LEFT JOIN maids m ON qr.maid_id = m.id
            LEFT JOIN service_models sm ON qr.service_model_id = sm.id
            LEFT JOIN accommodation_types at ON qr.accommodation_type_id = at.id
            LEFT JOIN nationalities n ON qr.preferred_nationality_id = n.id
            ORDER BY qr.created_at DESC LIMIT ? OFFSET ?
        ");
        $stmt->bindValue(1, $perPage, PDO::PARAM_INT);
        $stmt->bindValue(2, $offset, PDO::PARAM_INT);
        $stmt->execute();
        $data = $stmt->fetchAll();

        foreach ($data as &$row) {
            // Fetch specialties
            $specStmt = $this->db->prepare("
                SELECT s.name_en 
                FROM quotation_request_specialties qrs
                JOIN specialties s ON qrs.specialty_id = s.id
                WHERE qrs.quotation_request_id = ?
            ");
            $specStmt->execute([$row['id']]);
            $row['specialties'] = $specStmt->fetchAll(PDO::FETCH_COLUMN);

            // Fetch skills
            $skillStmt = $this->db->prepare("
                SELECT sk.name_en 
                FROM quotation_request_skills qrs
                JOIN skills sk ON qrs.skill_id = sk.id
                WHERE qrs.quotation_request_id = ?
            ");
            $skillStmt->execute([$row['id']]);
            $row['skills'] = $skillStmt->fetchAll(PDO::FETCH_COLUMN);
        }
        
        $countStmt = $this->db->query("SELECT COUNT(*) FROM {$this->table}");
        $total = $countStmt->fetchColumn();
        
        return [
            'data' => $data,
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage
        ];
    }
}
