<?php
namespace Domain\Coupon;

class CouponRepository
{
    private const MAX_PER_PAGE = 100;

    private $db;
    private ?string $defaultLangId = null;

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

    /**
     * @return array{items: array<int, array<string, mixed>>, total: int}
     */
    public function listForAdmin(
        array $filters,
        string $sortColumn = 'name',
        string $sortDir = 'ASC',
        int $page = 1,
        int $perPage = 15
    ): array {
        $allowedSortColumns = [
            'id' => 'sc.id',
            'status' => 'sc.status',
            'used_count' => 'sc.used_count',
            'name' => 'sc.name',
            'type' => 'sc.type',
            'amount' => 'sc.amount',
            'one_time' => 'sc.one_time',
            'send' => 'sc.send',
            'used' => 'sc.used',
            'date_used' => 'sc.date_used',
        ];

        $sortSql = $allowedSortColumns[$sortColumn] ?? 'sc.name';
        $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 = ['1 = 1'];
        $params = [];

        $name = trim((string)($filters['name'] ?? ''));
        if ($name !== '') {
            if (strlen($name) > 255) {
                $name = substr($name, 0, 255);
            }
            $where[] = 'sc.name LIKE :name';
            $params[':name'] = '%' . $name . '%';
        }

        $status = trim((string)($filters['status'] ?? ''));
        if ($status === '0' || $status === '1') {
            $where[] = 'sc.status = :status';
            $params[':status'] = (int)$status;
        }

        $used = trim((string)($filters['used'] ?? ''));
        if ($used === '0' || $used === '1') {
            $where[] = 'sc.used = :used';
            $params[':used'] = (int)$used;
        }

        $send = trim((string)($filters['send'] ?? ''));
        if ($send === '0' || $send === '1') {
            $where[] = 'sc.send = :send';
            $params[':send'] = (int)$send;
        }

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

        $sqlCount = "
            SELECT COUNT(0)
            FROM pp_shop_coupon AS sc
            WHERE {$whereSql}
        ";

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

        $sql = "
            SELECT
                sc.id,
                sc.name,
                sc.status,
                sc.used,
                sc.type,
                sc.amount,
                sc.one_time,
                sc.send,
                sc.include_discounted_product,
                sc.categories,
                sc.date_used,
                sc.used_count
            FROM pp_shop_coupon AS sc
            WHERE {$whereSql}
            ORDER BY {$sortSql} {$sortDir}, sc.id DESC
            LIMIT {$perPage} OFFSET {$offset}
        ";

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

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

        foreach ($items as &$item) {
            $item['id'] = (int)($item['id'] ?? 0);
            $item['status'] = $this->toSwitchValue($item['status'] ?? 0);
            $item['used'] = $this->toSwitchValue($item['used'] ?? 0);
            $item['one_time'] = $this->toSwitchValue($item['one_time'] ?? 0);
            $item['send'] = $this->toSwitchValue($item['send'] ?? 0);
            $item['used_count'] = (int)($item['used_count'] ?? 0);
            $item['type'] = (int)($item['type'] ?? 1);
            $item['categories'] = $this->decodeIdList($item['categories'] ?? null);
        }
        unset($item);

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

    public function find(int $couponId): array
    {
        if ($couponId <= 0) {
            return $this->defaultCoupon();
        }

        $coupon = $this->db->get('pp_shop_coupon', '*', ['id' => $couponId]);
        if (!is_array($coupon)) {
            return $this->defaultCoupon();
        }

        $coupon['id'] = (int)($coupon['id'] ?? 0);
        $coupon['status'] = $this->toSwitchValue($coupon['status'] ?? 0);
        $coupon['send'] = $this->toSwitchValue($coupon['send'] ?? 0);
        $coupon['used'] = $this->toSwitchValue($coupon['used'] ?? 0);
        $coupon['one_time'] = $this->toSwitchValue($coupon['one_time'] ?? 0);
        $coupon['include_discounted_product'] = $this->toSwitchValue($coupon['include_discounted_product'] ?? 0);
        $coupon['type'] = (int)($coupon['type'] ?? 1);
        $coupon['used_count'] = (int)($coupon['used_count'] ?? 0);
        $coupon['categories'] = $this->decodeIdList($coupon['categories'] ?? null);

        return $coupon;
    }

    public function save(array $data): ?int
    {
        $couponId = (int)($data['id'] ?? 0);

        $row = [
            'name' => trim((string)($data['name'] ?? '')),
            'status' => $this->toSwitchValue($data['status'] ?? 0),
            'send' => $this->toSwitchValue($data['send'] ?? 0),
            'used' => $this->toSwitchValue($data['used'] ?? 0),
            'type' => (int)($data['type'] ?? 1),
            'amount' => $this->toNullableNumeric($data['amount'] ?? null),
            'one_time' => $this->toSwitchValue($data['one_time'] ?? 0),
            'include_discounted_product' => $this->toSwitchValue($data['include_discounted_product'] ?? 0),
            'categories' => $this->encodeIdList($data['categories'] ?? null),
        ];

        if ($couponId <= 0) {
            $this->db->insert('pp_shop_coupon', $row);
            $id = (int)$this->db->id();
            return $id > 0 ? $id : null;
        }

        $this->db->update('pp_shop_coupon', $row, ['id' => $couponId]);
        return $couponId;
    }

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

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

    public function findByName(string $name)
    {
        $name = trim($name);
        if ($name === '') {
            return null;
        }

        $coupon = $this->db->get('pp_shop_coupon', '*', ['name' => $name]);
        if (!is_array($coupon)) {
            return null;
        }

        $coupon['id'] = (int)($coupon['id'] ?? 0);
        $coupon['status'] = (int)($coupon['status'] ?? 0);
        $coupon['used'] = (int)($coupon['used'] ?? 0);
        $coupon['one_time'] = (int)($coupon['one_time'] ?? 0);
        $coupon['type'] = (int)($coupon['type'] ?? 0);
        $coupon['include_discounted_product'] = (int)($coupon['include_discounted_product'] ?? 0);
        $coupon['used_count'] = (int)($coupon['used_count'] ?? 0);

        return (object)$coupon;
    }

    public function isAvailable($coupon)
    {
        if (!$coupon) {
            return false;
        }

        $id = is_object($coupon) ? ($coupon->id ?? 0) : ($coupon['id'] ?? 0);
        $status = is_object($coupon) ? ($coupon->status ?? 0) : ($coupon['status'] ?? 0);
        $used = is_object($coupon) ? ($coupon->used ?? 0) : ($coupon['used'] ?? 0);

        if (!(int)$id) {
            return false;
        }

        if (!(int)$status) {
            return false;
        }

        return !(int)$used;
    }

    public function markAsUsed(int $couponId)
    {
        if ($couponId <= 0) {
            return;
        }

        $this->db->update('pp_shop_coupon', [
            'used' => 1,
            'date_used' => date('Y-m-d H:i:s'),
        ], ['id' => $couponId]);
    }

    public function incrementUsedCount(int $couponId)
    {
        if ($couponId <= 0) {
            return;
        }

        $this->db->update('pp_shop_coupon', [
            'used_count[+]' => 1,
        ], ['id' => $couponId]);
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function categoriesTree($parentId = null): array
    {
        $rows = $this->db->select('pp_shop_categories', ['id'], [
            'parent_id' => $parentId,
            'ORDER' => ['o' => 'ASC'],
        ]);

        if (!is_array($rows)) {
            return [];
        }

        $categories = [];
        foreach ($rows as $row) {
            $categoryId = (int)($row['id'] ?? 0);
            if ($categoryId <= 0) {
                continue;
            }

            $category = $this->db->get('pp_shop_categories', '*', ['id' => $categoryId]);
            if (!is_array($category)) {
                continue;
            }

            $translations = $this->db->select('pp_shop_categories_langs', '*', ['category_id' => $categoryId]);
            $category['languages'] = [];
            if (is_array($translations)) {
                foreach ($translations as $translation) {
                    $langId = (string)($translation['lang_id'] ?? '');
                    if ($langId !== '') {
                        $category['languages'][$langId] = $translation;
                    }
                }
            }

            $category['title'] = $this->categoryTitle($category['languages']);
            $category['subcategories'] = $this->categoriesTree($categoryId);
            $categories[] = $category;
        }

        return $categories;
    }

    private function defaultCoupon(): array
    {
        return [
            'id' => 0,
            'name' => '',
            'status' => 1,
            'send' => 0,
            'used' => 0,
            'type' => 1,
            'amount' => null,
            'one_time' => 1,
            'include_discounted_product' => 0,
            'categories' => [],
            'used_count' => 0,
            'date_used' => null,
        ];
    }

    private function toSwitchValue($value): int
    {
        if (is_bool($value)) {
            return $value ? 1 : 0;
        }

        if (is_numeric($value)) {
            return ((int)$value) === 1 ? 1 : 0;
        }

        if (is_string($value)) {
            $normalized = strtolower(trim($value));
            return in_array($normalized, ['1', 'on', 'true', 'yes'], true) ? 1 : 0;
        }

        return 0;
    }

    private function toNullableNumeric($value): ?string
    {
        if ($value === null) {
            return null;
        }

        $stringValue = trim((string)$value);
        if ($stringValue === '') {
            return null;
        }

        return str_replace(',', '.', $stringValue);
    }

    private function encodeIdList($values): ?string
    {
        $ids = $this->normalizeIdList($values);
        if (empty($ids)) {
            return null;
        }

        return json_encode($ids);
    }

    /**
     * @return int[]
     */
    private function decodeIdList($raw): array
    {
        if (is_array($raw)) {
            return $this->normalizeIdList($raw);
        }

        $text = trim((string)$raw);
        if ($text === '') {
            return [];
        }

        $decoded = json_decode($text, true);
        if (!is_array($decoded)) {
            return [];
        }

        return $this->normalizeIdList($decoded);
    }

    /**
     * @return int[]
     */
    private function normalizeIdList($values): array
    {
        if ($values === null) {
            return [];
        }

        if (!is_array($values)) {
            $text = trim((string)$values);
            if ($text === '') {
                return [];
            }

            if (strpos($text, ',') !== false) {
                $values = explode(',', $text);
            } else {
                $values = [$text];
            }
        }

        $ids = [];
        foreach ($values as $value) {
            $id = (int)$value;
            if ($id > 0) {
                $ids[$id] = $id;
            }
        }

        return array_values($ids);
    }

    private function categoryTitle(array $languages): string
    {
        $defaultLang = $this->defaultLanguageId();
        if ($defaultLang !== '' && isset($languages[$defaultLang]['title'])) {
            $title = trim((string)$languages[$defaultLang]['title']);
            if ($title !== '') {
                return $title;
            }
        }

        foreach ($languages as $language) {
            $title = trim((string)($language['title'] ?? ''));
            if ($title !== '') {
                return $title;
            }
        }

        return '';
    }

    private function defaultLanguageId(): string
    {
        if ($this->defaultLangId !== null) {
            return $this->defaultLangId;
        }

        $rows = $this->db->select('pp_langs', ['id', 'start', 'o'], [
            'status' => 1,
            'ORDER' => ['start' => 'DESC', 'o' => 'ASC'],
        ]);

        if (is_array($rows) && !empty($rows)) {
            $this->defaultLangId = (string)($rows[0]['id'] ?? '');
        } else {
            $this->defaultLangId = '';
        }

        return $this->defaultLangId;
    }
}