<?php
namespace App\Repositories;

use App\Database\Connection;
use App\Models\Product;
use PDO;

class ProductRepository
{
    private $db;

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

    public function all()
    {
        $pdo = $this->db->getPdo();
        $hasServiceLink = $this->db->hasColumn('products', 'service_lookup_item_id');
        $notDeletedClause = $this->db->hasColumn('products', 'is_deleted')
            ? "WHERE COALESCE(p.is_deleted, 0) = 0"
            : "";
        if ($hasServiceLink) {
            $stmt = $pdo->query("
                SELECT p.*,
                       p.service_lookup_item_id,
                       li.code AS service_lookup_code,
                       li.value AS service_lookup_value,
                       li.title AS service_lookup_title
                FROM products p
                LEFT JOIN lookup_items li ON li.id = p.service_lookup_item_id
                LEFT JOIN lookup_groups lg ON lg.id = li.group_id AND lg.code = 'allowed_services'
                {$notDeletedClause}
                ORDER BY p.name ASC
            ");
        } else {
            $stmt = $pdo->query("SELECT * FROM products p {$notDeletedClause} ORDER BY p.name ASC");
        }
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return array_map(fn($r) => new Product($r), $rows);
    }

    public function allUsedInDeals()
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->query("
            SELECT DISTINCT p.*
            FROM products p
            INNER JOIN deal_products dp ON dp.product_id = p.id
            ORDER BY p.name ASC
        ");
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return array_map(fn($r) => new Product($r), $rows);
    }

    public function allAllowedServices()
    {
        $pdo = $this->db->getPdo();
        $hasServiceLink = $this->db->hasColumn('products', 'service_lookup_item_id');
        if ($hasServiceLink) {
            $stmt = $pdo->query("
            SELECT DISTINCT p.*
            FROM products p
            INNER JOIN lookup_items i ON i.id = p.service_lookup_item_id
            INNER JOIN lookup_groups g ON g.id = i.group_id AND g.code = 'allowed_services'
            WHERE i.is_active = 1" . ($this->db->hasColumn('products', 'is_deleted') ? " AND COALESCE(p.is_deleted, 0) = 0" : "") . "
            ORDER BY p.name ASC
        ");
        } else {
            $stmt = $pdo->query("
                SELECT DISTINCT p.*
                FROM products p
                INNER JOIN lookup_items i ON COALESCE(i.value, i.title) = p.name
                INNER JOIN lookup_groups g ON g.id = i.group_id AND g.code = 'allowed_services'
                WHERE i.is_active = 1" . ($this->db->hasColumn('products', 'is_deleted') ? " AND COALESCE(p.is_deleted, 0) = 0" : "") . "
                ORDER BY p.name ASC
            ");
        }
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return array_map(fn($r) => new Product($r), $rows);
    }

    public function allForKpiReportDimension()
    {
        $historical = $this->allUsedInDeals();
        $current = $this->allActiveVisibleAllowedServices();

        $byId = [];
        foreach (array_merge($historical, $current) as $product) {
            $id = (int)($product->id ?? 0);
            if ($id <= 0 || isset($byId[$id])) {
                continue;
            }
            $byId[$id] = $product;
        }

        $products = array_values($byId);
        usort($products, static function ($a, $b) {
            return strnatcasecmp((string)($a->name ?? ''), (string)($b->name ?? ''));
        });

        return $products;
    }

    private function allActiveVisibleAllowedServices()
    {
        $pdo = $this->db->getPdo();
        $hasServiceLink = $this->db->hasColumn('products', 'service_lookup_item_id');
        $visibleClause = $this->db->hasColumn('lookup_items', 'is_visible')
            ? " AND COALESCE(i.is_visible, 1) = 1"
            : "";
        $notDeletedClause = $this->db->hasColumn('products', 'is_deleted')
            ? " AND COALESCE(p.is_deleted, 0) = 0"
            : "";

        if ($hasServiceLink) {
            $stmt = $pdo->query("
                SELECT DISTINCT p.*
                FROM products p
                INNER JOIN lookup_items i ON i.id = p.service_lookup_item_id
                INNER JOIN lookup_groups g ON g.id = i.group_id AND g.code = 'allowed_services'
                WHERE i.is_active = 1{$visibleClause}{$notDeletedClause}
                ORDER BY p.name ASC
            ");
        } else {
            $stmt = $pdo->query("
                SELECT DISTINCT p.*
                FROM products p
                INNER JOIN lookup_items i ON COALESCE(i.value, i.title) = p.name
                INNER JOIN lookup_groups g ON g.id = i.group_id AND g.code = 'allowed_services'
                WHERE i.is_active = 1{$visibleClause}{$notDeletedClause}
                ORDER BY p.name ASC
            ");
        }
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return array_map(fn($r) => new Product($r), $rows);
    }

    public function getIdsByNames(array $names): array
    {
        $names = array_values(array_filter(array_map(function($name) {
            return trim((string)$name);
        }, $names)));
        if (empty($names)) {
            return [];
        }

        $placeholders = implode(',', array_fill(0, count($names), '?'));
        $pdo = $this->db->getPdo();
        $notDeletedClause = $this->db->hasColumn('products', 'is_deleted')
            ? " AND COALESCE(is_deleted, 0) = 0"
            : "";
        $stmt = $pdo->prepare("SELECT id FROM products WHERE name IN ($placeholders){$notDeletedClause}");
        $stmt->execute($names);
        return array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN));
    }

    public function create(string $name, ?int $serviceLookupItemId = null)
    {
        $pdo = $this->db->getPdo();
        if ($this->db->hasColumn('products', 'service_lookup_item_id')) {
            $stmt = $pdo->prepare("INSERT INTO products (name, service_lookup_item_id) VALUES (?, ?)");
            $stmt->execute([$name, $serviceLookupItemId]);
        } else {
            $stmt = $pdo->prepare("INSERT INTO products (name) VALUES (?)");
            $stmt->execute([$name]);
        }
        return $pdo->lastInsertId();
    }

    public function update(int $id, string $name, ?int $serviceLookupItemId = null): bool
    {
        $pdo = $this->db->getPdo();
        if ($this->db->hasColumn('products', 'service_lookup_item_id')) {
            $stmt = $pdo->prepare("UPDATE products SET name = ?, service_lookup_item_id = ? WHERE id = ?");
            return $stmt->execute([$name, $serviceLookupItemId, $id]);
        }
        $stmt = $pdo->prepare("UPDATE products SET name = ? WHERE id = ?");
        return $stmt->execute([$name, $id]);
    }

    public function delete(int $id)
    {
        $pdo = $this->db->getPdo();
        if ($this->db->hasColumn('products', 'is_deleted')) {
            $usedStmt = $pdo->prepare("SELECT COUNT(*) FROM deal_products WHERE product_id = ?");
            $usedStmt->execute([$id]);
            if ((int)$usedStmt->fetchColumn() > 0) {
                $sql = $this->db->hasColumn('products', 'service_lookup_item_id')
                    ? "UPDATE products SET is_deleted = 1, service_lookup_item_id = NULL WHERE id = ?"
                    : "UPDATE products SET is_deleted = 1 WHERE id = ?";
                $stmt = $pdo->prepare($sql);
                return $stmt->execute([$id]);
            }
        }

        $stmt = $pdo->prepare("DELETE FROM products WHERE id = ?");
        return $stmt->execute([$id]);
    }

    public function getNamesByIds(array $ids): array
    {
        $ids = array_values(array_filter(array_map('intval', $ids)));
        if (empty($ids)) {
            return [];
        }
        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT name FROM products WHERE id IN ($placeholders)");
        $stmt->execute($ids);
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }

    public function matchIdsByText(array $texts): array
    {
        $texts = array_values(array_filter(array_map(function($text) {
            return trim((string)$text);
        }, $texts)));
        if (empty($texts)) {
            return [];
        }

        $pdo = $this->db->getPdo();
        $notDeletedClause = $this->db->hasColumn('products', 'is_deleted')
            ? " AND COALESCE(is_deleted, 0) = 0"
            : "";
        $stmt = $pdo->query("SELECT id, name FROM products WHERE name IS NOT NULL AND name <> ''{$notDeletedClause}");
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $normalize = function($value) {
            $value = trim((string)$value);
            $value = preg_replace('/[|،,]+/', ' ', $value);
            $value = preg_replace('/\s+/', ' ', $value);
            if (function_exists('mb_strtolower')) {
                return mb_strtolower($value, 'UTF-8');
            }
            return strtolower($value);
        };

        $normalizedTexts = array_map($normalize, $texts);
        $matches = [];
        foreach ($rows as $row) {
            $name = trim((string)($row['name'] ?? ''));
            if ($name === '') {
                continue;
            }
            $needle = $normalize($name);
            if ($needle === '') {
                continue;
            }
            foreach ($normalizedTexts as $text) {
                if ($text === '') {
                    continue;
                }
                $found = function_exists('mb_stripos')
                    ? (mb_stripos($text, $needle, 0, 'UTF-8') !== false)
                    : (stripos($text, $needle) !== false);
                if ($found) {
                    $matches[] = (int)($row['id'] ?? 0);
                    break;
                }
            }
        }

        $matches = array_values(array_filter(array_unique($matches)));
        return $matches;
    }

    public function bulkAssignProductsToService(int $serviceLookupItemId, array $productIds): void
    {
        if (!$this->db->hasColumn('products', 'service_lookup_item_id')) {
            return;
        }

        $pdo = $this->db->getPdo();
        $productIds = array_values(array_unique(array_filter(array_map('intval', $productIds))));

        $pdo->beginTransaction();
        try {
            $clearStmt = $pdo->prepare("UPDATE products SET service_lookup_item_id = NULL WHERE service_lookup_item_id = ?");
            $clearStmt->execute([$serviceLookupItemId]);

            if (!empty($productIds)) {
                $placeholders = implode(',', array_fill(0, count($productIds), '?'));
                $params = array_merge([$serviceLookupItemId], $productIds);
                $assignStmt = $pdo->prepare("UPDATE products SET service_lookup_item_id = ? WHERE id IN ($placeholders)");
                $assignStmt->execute($params);
            }

            $pdo->commit();
        } catch (\Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        }
    }

    public function isAllowedServiceLookupItem(int $lookupItemId): bool
    {
        if ($lookupItemId <= 0) {
            return false;
        }
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM lookup_items li
            INNER JOIN lookup_groups lg ON lg.id = li.group_id
            WHERE li.id = ? AND lg.code = 'allowed_services'
        ");
        $stmt->execute([$lookupItemId]);
        return ((int)$stmt->fetchColumn()) > 0;
    }
}
