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

class Interview extends BaseModel {
    protected $table = 'interview_requests';

    public function searchAndPaginateWithDetails($page = 1, $perPage = 10, $searchTerm = '', $filters = []) {
        $offset = ($page - 1) * $perPage;
        
        $whereParts = [];
        $params = [];
        
        if (!empty($searchTerm)) {
            $whereParts[] = "(cust.full_name LIKE ? OR v.agency_name LIKE ? OR m.full_name LIKE ?)";
            $params[] = "%$searchTerm%";
            $params[] = "%$searchTerm%";
            $params[] = "%$searchTerm%";
        }
        
        if (!empty($filters['status'])) {
            $whereParts[] = "i.status = ?";
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['date'])) {
            $whereParts[] = "i.interview_date = ?";
            $params[] = $filters['date'];
        }
        
        $whereSql = !empty($whereParts) ? "WHERE " . implode(" AND ", $whereParts) : "";
        
        $sql = "
            SELECT i.*, cust.full_name as customer_name, v.agency_name as vendor_name, m.full_name as maid_name, p.name as platform_name
            FROM {$this->table} i
            LEFT JOIN customers cust ON i.customer_id = cust.id
            LEFT JOIN vendors v ON i.vendor_id = v.id
            LEFT JOIN maids m ON i.maid_id = m.id
            LEFT JOIN interview_platforms p ON i.platform_id = p.id
            {$whereSql}
            ORDER BY i.interview_date DESC, i.time_from DESC LIMIT ? OFFSET ?
        ";
        
        $stmt = $this->db->prepare($sql);
        $paramIndex = 1;
        foreach ($params as $param) {
            $stmt->bindValue($paramIndex++, $param);
        }
        $stmt->bindValue($paramIndex++, (int)$perPage, PDO::PARAM_INT);
        $stmt->bindValue($paramIndex++, (int)$offset, PDO::PARAM_INT);
        $stmt->execute();
        $data = $stmt->fetchAll();
        
        $countSql = "
            SELECT COUNT(*) 
            FROM {$this->table} i 
            LEFT JOIN customers cust ON i.customer_id = cust.id
            LEFT JOIN vendors v ON i.vendor_id = v.id
            LEFT JOIN maids m ON i.maid_id = m.id
            {$whereSql}
        ";
        $countStmt = $this->db->prepare($countSql);
        // Remove limit/offset params for count
        $countParams = array_slice($params, 0, count($params));
        $countStmt->execute($countParams);
        $total = $countStmt->fetchColumn();
        
        return [
            'data' => $data,
            'total' => $total
        ];
    }
}
