pomysloweprezenty.pl/autoload/Domain/Client/ClientRepository.php

<?php
namespace Domain\Client;

class ClientRepository
{
    private const MAX_PER_PAGE = 100;

    private $db;

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

    /**
     * @return array{items: array<int, array<string, mixed>>, total: int}
     */
    public function listForAdmin(
        array $filters,
        string $sortColumn = 'client_surname',
        string $sortDir = 'ASC',
        int $page = 1,
        int $perPage = 15
    ): array {
        $allowedSortColumns = [
            'client_name' => 'c.client_name',
            'client_surname' => 'c.client_surname',
            'client_email' => 'c.client_email',
            'client_phone' => 'c.client_phone',
            'client_city' => 'c.client_city',
            'total_orders' => 'c.total_orders',
            'total_spent' => 'c.total_spent',
            'client_type' => 'c.is_registered',
        ];

        $sortSql = $allowedSortColumns[$sortColumn] ?? 'c.client_surname';
        $sortDir = strtoupper(trim($sortDir)) === 'DESC' ? 'DESC' : 'ASC';
        $page = max(1, $page);
        $perPage = min(self::MAX_PER_PAGE, max(1, $perPage));
        $offset = ($page - 1) * $perPage;

        $where = [
            'o.client_name IS NOT NULL',
            'o.client_surname IS NOT NULL',
            'o.client_email IS NOT NULL',
        ];
        $params = [];

        $name = $this->normalizeTextFilter($filters['name'] ?? '');
        if ($name !== '') {
            $where[] = 'o.client_name LIKE :name';
            $params[':name'] = '%' . $name . '%';
        }

        $surname = $this->normalizeTextFilter($filters['surname'] ?? '');
        if ($surname !== '') {
            $where[] = 'o.client_surname LIKE :surname';
            $params[':surname'] = '%' . $surname . '%';
        }

        $email = $this->normalizeTextFilter($filters['email'] ?? '');
        if ($email !== '') {
            $where[] = 'o.client_email LIKE :email';
            $params[':email'] = '%' . $email . '%';
        }

        $clientType = trim((string)($filters['client_type'] ?? ''));
        if ($clientType === 'registered') {
            $where[] = 'o.client_id IS NOT NULL';
        } elseif ($clientType === 'guest') {
            $where[] = 'o.client_id IS NULL';
        }

        $whereSql = implode(' AND ', $where);

        $aggregatedSql = "
            SELECT
                MAX(o.client_id) AS client_id,
                o.client_name,
                o.client_surname,
                o.client_email,
                MAX(o.client_phone) AS client_phone,
                MAX(o.client_city) AS client_city,
                COUNT(*) AS total_orders,
                COALESCE(SUM(o.summary), 0) AS total_spent,
                CASE
                    WHEN MAX(o.client_id) IS NOT NULL THEN 1
                    ELSE 0
                END AS is_registered
            FROM pp_shop_orders AS o
            WHERE {$whereSql}
            GROUP BY o.client_name, o.client_surname, o.client_email
        ";

        $sqlCount = "
            SELECT COUNT(0)
            FROM ({$aggregatedSql}) AS c
        ";

        $stmtCount = $this->db->query($sqlCount, $params);
        $countRows = $stmtCount ? $stmtCount->fetchAll() : [];
        $total = isset($countRows[0][0]) ? (int)$countRows[0][0] : 0;

        $sql = "
            SELECT
                c.client_id,
                c.client_name,
                c.client_surname,
                c.client_email,
                c.client_phone,
                c.client_city,
                c.total_orders,
                c.total_spent,
                c.is_registered
            FROM ({$aggregatedSql}) AS c
            ORDER BY {$sortSql} {$sortDir}, c.client_surname ASC, c.client_name ASC
            LIMIT {$perPage} OFFSET {$offset}
        ";

        $stmt = $this->db->query($sql, $params);
        $items = $stmt ? $stmt->fetchAll() : [];

        if (!is_array($items)) {
            $items = [];
        }

        foreach ($items as &$item) {
            $item['client_id'] = !isset($item['client_id']) ? null : (int)$item['client_id'];
            $item['client_name'] = (string)($item['client_name'] ?? '');
            $item['client_surname'] = (string)($item['client_surname'] ?? '');
            $item['client_email'] = (string)($item['client_email'] ?? '');
            $item['client_phone'] = (string)($item['client_phone'] ?? '');
            $item['client_city'] = (string)($item['client_city'] ?? '');
            $item['total_orders'] = (int)($item['total_orders'] ?? 0);
            $item['total_spent'] = (float)($item['total_spent'] ?? 0);
            $item['is_registered'] = ((int)($item['is_registered'] ?? 0)) === 1 ? 1 : 0;
        }
        unset($item);

        return [
            'items' => $items,
            'total' => $total,
        ];
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function ordersForClient(string $name, string $surname, string $email): array
    {
        $name = trim($name);
        $surname = trim($surname);
        $email = trim($email);

        if ($name === '' || $surname === '' || $email === '') {
            return [];
        }

        $sql = "
            SELECT
                o.id,
                o.date_order,
                o.summary,
                o.payment_method,
                o.transport,
                o.message
            FROM pp_shop_orders AS o
            WHERE o.client_name = :name
                AND o.client_surname = :surname
                AND o.client_email = :email
            ORDER BY o.date_order DESC, o.id DESC
        ";

        $stmt = $this->db->query($sql, [
            ':name' => $name,
            ':surname' => $surname,
            ':email' => $email,
        ]);

        $rows = $stmt ? $stmt->fetchAll() : [];
        if (!is_array($rows)) {
            return [];
        }

        foreach ($rows as &$row) {
            $row['id'] = (int)($row['id'] ?? 0);
            $row['date_order'] = (string)($row['date_order'] ?? '');
            $row['summary'] = (float)($row['summary'] ?? 0);
            $row['payment_method'] = (string)($row['payment_method'] ?? '');
            $row['transport'] = (string)($row['transport'] ?? '');
            $row['message'] = (string)($row['message'] ?? '');
        }
        unset($row);

        return $rows;
    }

    /**
     * @return array{total_orders: int, total_spent: float}
     */
    public function totalsForClient(string $name, string $surname, string $email): array
    {
        $name = trim($name);
        $surname = trim($surname);
        $email = trim($email);

        if ($name === '' || $surname === '' || $email === '') {
            return [
                'total_orders' => 0,
                'total_spent' => 0.0,
            ];
        }

        $sql = "
            SELECT
                COUNT(*) AS total_orders,
                COALESCE(SUM(o.summary), 0) AS total_spent
            FROM pp_shop_orders AS o
            WHERE o.client_name = :name
                AND o.client_surname = :surname
                AND o.client_email = :email
        ";

        $stmt = $this->db->query($sql, [
            ':name' => $name,
            ':surname' => $surname,
            ':email' => $email,
        ]);
        $rows = $stmt ? $stmt->fetchAll() : [];

        return [
            'total_orders' => isset($rows[0]['total_orders']) ? (int)$rows[0]['total_orders'] : 0,
            'total_spent' => isset($rows[0]['total_spent']) ? (float)$rows[0]['total_spent'] : 0.0,
        ];
    }

    // ===== Frontend methods =====

    /**
     * @return array<string, mixed>|null
     */
    public function clientDetails(int $clientId): ?array
    {
        if ($clientId <= 0) {
            return null;
        }

        return $this->db->get('pp_shop_clients', '*', ['id' => $clientId]) ?: null;
    }

    public function clientEmail(int $clientId): ?string
    {
        if ($clientId <= 0) {
            return null;
        }

        $email = $this->db->get('pp_shop_clients', 'email', ['id' => $clientId]);

        return $email ? (string)$email : null;
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function clientAddresses(int $clientId): array
    {
        if ($clientId <= 0) {
            return [];
        }

        $rows = $this->db->select('pp_shop_clients_addresses', '*', ['client_id' => $clientId]);

        return is_array($rows) ? $rows : [];
    }

    /**
     * @return array<string, mixed>|null
     */
    public function addressDetails(int $addressId): ?array
    {
        if ($addressId <= 0) {
            return null;
        }

        return $this->db->get('pp_shop_clients_addresses', '*', ['id' => $addressId]) ?: null;
    }

    public function addressDelete(int $addressId): bool
    {
        if ($addressId <= 0) {
            return false;
        }

        return (bool)$this->db->delete('pp_shop_clients_addresses', ['id' => $addressId]);
    }

    /**
     * @param array<string, string> $data Keys: name, surname, street, postal_code, city, phone
     */
    public function addressSave(int $clientId, ?int $addressId, array $data): bool
    {
        if ($clientId <= 0) {
            return false;
        }

        $row = [
            'name' => (string)($data['name'] ?? ''),
            'surname' => (string)($data['surname'] ?? ''),
            'street' => (string)($data['street'] ?? ''),
            'postal_code' => (string)($data['postal_code'] ?? ''),
            'city' => (string)($data['city'] ?? ''),
            'phone' => (string)($data['phone'] ?? ''),
        ];

        if (!$addressId || $addressId <= 0) {
            $row['client_id'] = $clientId;
            return (bool)$this->db->insert('pp_shop_clients_addresses', $row);
        }

        return (bool)$this->db->update('pp_shop_clients_addresses', $row, [
            'AND' => [
                'client_id' => $clientId,
                'id' => $addressId,
            ],
        ]);
    }

    public function markAddressAsCurrent(int $clientId, int $addressId): bool
    {
        if ($clientId <= 0 || $addressId <= 0) {
            return false;
        }

        $this->db->update('pp_shop_clients_addresses', ['current' => 0], ['client_id' => $clientId]);
        $this->db->update('pp_shop_clients_addresses', ['current' => 1], [
            'AND' => [
                'client_id' => $clientId,
                'id' => $addressId,
            ],
        ]);

        return true;
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function clientOrders(int $clientId): array
    {
        if ($clientId <= 0) {
            return [];
        }

        $rows = $this->db->select('pp_shop_orders', 'id', [
            'client_id' => $clientId,
            'ORDER' => ['date_order' => 'DESC'],
        ]);

        $orders = [];
        if (is_array($rows)) {
            $orderRepo = new \Domain\Order\OrderRepository($this->db);
            foreach ($rows as $row) {
                $orders[] = $orderRepo->orderDetailsFrontend($row);
            }
        }

        return $orders;
    }

    /**
     * @return array{status: string, client?: array, hash?: string, code?: string}
     */
    public function authenticate(string $email, string $password): array
    {
        $email = trim($email);
        $password = trim($password);

        if ($email === '' || $password === '') {
            return ['status' => 'error', 'code' => 'logowanie-nieudane'];
        }

        $client = $this->db->get('pp_shop_clients', [
            'id', 'password', 'register_date', 'hash', 'status',
        ], ['email' => $email]);

        if (!$client) {
            return ['status' => 'error', 'code' => 'logowanie-nieudane'];
        }

        if (!(int)$client['status']) {
            return ['status' => 'inactive', 'hash' => $client['hash']];
        }

        if ($client['password'] !== md5($client['register_date'] . $password)) {
            return ['status' => 'error', 'code' => 'logowanie-blad-nieprawidlowe-haslo'];
        }

        $fullClient = $this->clientDetails((int)$client['id']);

        return ['status' => 'ok', 'client' => $fullClient];
    }

    /**
     * @return array{id: int, hash: string}|null Null when email already taken
     */
    public function createClient(string $email, string $password, bool $agreementMarketing): ?array
    {
        $email = trim($email);
        if ($email === '' || $password === '') {
            return null;
        }

        if ($this->db->count('pp_shop_clients', ['email' => $email])) {
            return null;
        }

        $hash = md5(time() . $email);
        $registerDate = date('Y-m-d H:i:s');

        $inserted = $this->db->insert('pp_shop_clients', [
            'email' => $email,
            'password' => md5($registerDate . $password),
            'hash' => $hash,
            'agremment_marketing' => $agreementMarketing ? 1 : 0,
            'register_date' => $registerDate,
        ]);

        if (!$inserted) {
            return null;
        }

        return [
            'id' => (int)$this->db->id(),
            'hash' => $hash,
        ];
    }

    /**
     * Confirms registration. Returns client email on success, null on failure.
     */
    public function confirmRegistration(string $hash): ?string
    {
        $hash = trim($hash);
        if ($hash === '') {
            return null;
        }

        $id = $this->db->get('pp_shop_clients', 'id', [
            'AND' => ['hash' => $hash, 'status' => 0],
        ]);

        if (!$id) {
            return null;
        }

        $this->db->update('pp_shop_clients', ['status' => 1], ['id' => $id]);

        $email = $this->db->get('pp_shop_clients', 'email', ['id' => $id]);

        return $email ? (string)$email : null;
    }

    /**
     * Generates new password. Returns [email, password] on success, null on failure.
     *
     * @return array{email: string, password: string}|null
     */
    public function generateNewPassword(string $hash): ?array
    {
        $hash = trim($hash);
        if ($hash === '') {
            return null;
        }

        $data = $this->db->get('pp_shop_clients', ['id', 'email', 'register_date'], [
            'AND' => ['hash' => $hash, 'status' => 1, 'password_recovery' => 1],
        ]);

        if (!$data) {
            return null;
        }

        $newPassword = substr(md5(time()), 0, 10);

        $this->db->update('pp_shop_clients', [
            'password_recovery' => 0,
            'password' => md5($data['register_date'] . $newPassword),
        ], ['id' => $data['id']]);

        return [
            'email' => (string)$data['email'],
            'password' => $newPassword,
        ];
    }

    /**
     * Initiates password recovery. Returns hash on success, null on failure.
     */
    public function initiatePasswordRecovery(string $email): ?string
    {
        $email = trim($email);
        if ($email === '') {
            return null;
        }

        $hash = $this->db->get('pp_shop_clients', 'hash', [
            'AND' => ['email' => $email, 'status' => 1],
        ]);

        if (!$hash) {
            return null;
        }

        $this->db->update('pp_shop_clients', ['password_recovery' => 1], ['email' => $email]);

        return (string)$hash;
    }

    private function normalizeTextFilter($value): string
    {
        $value = trim((string)$value);
        if ($value === '') {
            return '';
        }

        if (strlen($value) > 255) {
            return substr($value, 0, 255);
        }

        return $value;
    }
}