<?php
namespace App\Repositories;

use App\Database\Connection;
use PDO;

/**
 * Customer access request ("ticket") storage.
 * Sales experts create requests; support resolves them. See Schema::getCustomerAccessRequestsTable().
 */
class AccessRequestRepository
{
    private $db;

    public function __construct(Connection $db)
    {
        $this->db = $db;
    }

    private function ownerNameField(string $alias): string
    {
        return $this->db->hasColumn('users', 'display_name')
            ? "$alias.display_name"
            : "COALESCE(CONCAT($alias.first_name, ' ', $alias.last_name), '')";
    }

    public function create(array $d): int
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("INSERT INTO customer_access_requests
            (contact_didar_id, request_type, deal_id, deal_didar_id, deal_title, product_type, selected_phone,
             deal_owner_didar_id, deal_owner_name, description, status,
             requested_by_didar_id, requested_by_name, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', ?, ?, NOW())");
        $stmt->execute([
            $d['contact_didar_id'], $d['request_type'] ?? 'grant', $d['deal_id'] ?: null, $d['deal_didar_id'] ?? null,
            $d['deal_title'] ?? null, $d['product_type'] ?? null, $d['selected_phone'] ?? null,
            $d['deal_owner_didar_id'] ?? null, $d['deal_owner_name'] ?? null, $d['description'] ?? '',
            $d['requested_by_didar_id'] ?? null, $d['requested_by_name'] ?? null,
        ]);
        return (int)$pdo->lastInsertId();
    }

    /**
     * Requests for a single lead's card. When $requestedBy is provided, scope to that
     * requester (sales experts only see their own); pass null for support/admin (all).
     */
    public function getByContact(string $contactId, ?string $requestedBy = null): array
    {
        $pdo = $this->db->getPdo();
        $req = $this->ownerNameField('ru');
        $res = $this->ownerNameField('rs');
        $sql = "SELECT car.*, $req AS requested_by_display, $res AS resolved_by_display
                FROM customer_access_requests car
                LEFT JOIN users ru ON car.requested_by_didar_id = ru.didar_user_id
                LEFT JOIN users rs ON car.resolved_by_didar_id = rs.didar_user_id
                WHERE car.contact_didar_id = ?";
        $params = [$contactId];
        if ($requestedBy !== null && $requestedBy !== '') {
            $sql .= " AND car.requested_by_didar_id = ?";
            $params[] = $requestedBy;
        }
        $sql .= " ORDER BY car.created_at DESC, car.id DESC";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /** Support inbox: all requests with customer name + requester name, optional status filter. */
    public function getInbox(string $status = '', int $limit = 200): array
    {
        $pdo = $this->db->getPdo();
        $req = $this->ownerNameField('ru');
        $res = $this->ownerNameField('rs');
        $sql = "SELECT car.*, $req AS requested_by_display, $res AS resolved_by_display,
                       TRIM(CONCAT(COALESCE(p.first_name,''), ' ', COALESCE(p.last_name,''))) AS contact_name
                FROM customer_access_requests car
                LEFT JOIN users ru ON car.requested_by_didar_id = ru.didar_user_id
                LEFT JOIN users rs ON car.resolved_by_didar_id = rs.didar_user_id
                LEFT JOIN persons p ON car.contact_didar_id = p.didar_contact_id";
        $params = [];
        if (in_array($status, ['pending', 'done', 'not_done'], true)) {
            $sql .= " WHERE car.status = ?";
            $params[] = $status;
        }
        $limit = max(1, min(1000, $limit));
        $sql .= " ORDER BY (car.status = 'pending') DESC, car.created_at DESC, car.id DESC LIMIT $limit";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function findById(int $id): ?array
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM customer_access_requests WHERE id = ?");
        $stmt->execute([$id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function updateStatus(int $id, string $status, string $resolvedBy, string $note = ''): bool
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("UPDATE customer_access_requests
            SET status = ?, resolved_by_didar_id = ?, resolution_note = ?, resolved_at = NOW(), updated_at = NOW()
            WHERE id = ?");
        return $stmt->execute([$status, $resolvedBy, $note, $id]);
    }

    /** Best-effort product/service type of a deal: deal.service_name, else deal_products snapshot titles. */
    public function dealProductType(int $dealId): string
    {
        if ($dealId <= 0) {
            return '';
        }
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT service_name FROM deals WHERE id = ?");
        $stmt->execute([$dealId]);
        $name = trim((string)($stmt->fetchColumn() ?: ''));
        if ($name !== '') {
            return $name;
        }
        if ($this->db->hasColumn('deal_products', 'service_title_snapshot')) {
            $stmt = $pdo->prepare("SELECT GROUP_CONCAT(DISTINCT service_title_snapshot SEPARATOR '، ')
                                   FROM deal_products WHERE deal_id = ? AND service_title_snapshot <> ''");
            $stmt->execute([$dealId]);
            return trim((string)($stmt->fetchColumn() ?: ''));
        }
        return '';
    }
}
