<?php

declare(strict_types=1);

namespace FacetCore\WordPress;

final class Repository
{
    private $db;
    private string $facets;
    private string $values;
    private string $productValues;

    public function __construct($db = null)
    {
        global $wpdb;
        $this->db = $db ?: $wpdb;
        $this->facets = $this->db->prefix . 'facetcore_facets';
        $this->values = $this->db->prefix . 'facetcore_values';
        $this->productValues = $this->db->prefix . 'facetcore_product_values';
    }

    public function facetTable(): string
    {
        return $this->facets;
    }

    public function valueTable(): string
    {
        return $this->values;
    }

    public function productValueTable(): string
    {
        return $this->productValues;
    }

    public function facets(bool $enabledOnly = false): array
    {
        $where = $enabledOnly ? ' WHERE enabled=1' : '';
        return $this->db->get_results(
            "SELECT id,name,slug,priority,enabled FROM {$this->facets}{$where} ORDER BY priority DESC,name ASC",
            ARRAY_A
        ) ?: [];
    }

    public function facet(int $id): ?array
    {
        $row = $this->db->get_row(
            $this->db->prepare("SELECT id,name,slug,priority,enabled FROM {$this->facets} WHERE id=%d", $id),
            ARRAY_A
        );
        return $row ?: null;
    }

    public function values(int $facetId): array
    {
        return $this->db->get_results(
            $this->db->prepare(
                "SELECT id,facet_id,label,sort_order FROM {$this->values} WHERE facet_id=%d ORDER BY sort_order ASC,label ASC",
                $facetId
            ),
            ARRAY_A
        ) ?: [];
    }

    public function valuesForFacets(array $facetIds): array
    {
        $facetIds = array_values(array_unique(array_map('intval', $facetIds)));
        if (!$facetIds) {
            return [];
        }
        $in = implode(',', array_fill(0, count($facetIds), '%d'));
        $rows = $this->db->get_results(
            $this->db->prepare(
                "SELECT id,facet_id,label,sort_order FROM {$this->values} WHERE facet_id IN ({$in}) ORDER BY facet_id,sort_order,label",
                ...$facetIds
            ),
            ARRAY_A
        ) ?: [];
        $out = [];
        foreach ($rows as $row) {
            $out[(int) $row['facet_id']][] = $row;
        }
        return $out;
    }

    public function createFacet(string $name, string $slug, int $priority = 0): int
    {
        $this->db->insert($this->facets, [
            'name' => $name,
            'slug' => $slug,
            'priority' => $priority,
            'enabled' => 1,
        ], ['%s','%s','%d','%d']);
        return (int) $this->db->insert_id;
    }

    public function createValue(int $facetId, string $label, int $sortOrder = 0): int
    {
        $this->db->insert($this->values, [
            'facet_id' => $facetId,
            'label' => $label,
            'sort_order' => $sortOrder,
        ], ['%d','%s','%d']);
        return (int) $this->db->insert_id;
    }

    public function deleteFacet(int $facetId): void
    {
        $valueIds = $this->db->get_col(
            $this->db->prepare("SELECT id FROM {$this->values} WHERE facet_id=%d", $facetId)
        ) ?: [];
        if ($valueIds) {
            $in = implode(',', array_map('intval', $valueIds));
            $this->db->query("DELETE FROM {$this->productValues} WHERE value_id IN ({$in})");
        }
        $this->db->delete($this->productValues, ['facet_id' => $facetId], ['%d']);
        $this->db->delete($this->values, ['facet_id' => $facetId], ['%d']);
        $this->db->delete($this->facets, ['id' => $facetId], ['%d']);
    }

    public function deleteValue(int $valueId): void
    {
        $this->db->delete($this->productValues, ['value_id' => $valueId], ['%d']);
        $this->db->delete($this->values, ['id' => $valueId], ['%d']);
    }

    public function productValues(int $productId): array
    {
        $rows = $this->db->get_results(
            $this->db->prepare(
                "SELECT facet_id,value_id FROM {$this->productValues} WHERE product_id=%d ORDER BY facet_id,value_id",
                $productId
            ),
            ARRAY_A
        ) ?: [];
        $out = [];
        foreach ($rows as $row) {
            $out[(int) $row['facet_id']][] = (int) $row['value_id'];
        }
        return $out;
    }

    public function replaceProductValues(int $productId, array $assignments): void
    {
        $this->db->query('START TRANSACTION');
        try {
            $this->db->delete($this->productValues, ['product_id' => $productId], ['%d']);
            foreach ($assignments as $facetId => $valueIds) {
                $facetId = (int) $facetId;
                foreach (array_unique(array_map('intval', (array) $valueIds)) as $valueId) {
                    if ($facetId < 1 || $valueId < 1) {
                        continue;
                    }
                    $valid = (int) $this->db->get_var($this->db->prepare(
                        "SELECT COUNT(*) FROM {$this->values} WHERE id=%d AND facet_id=%d",
                        $valueId,
                        $facetId
                    ));
                    if ($valid !== 1) {
                        continue;
                    }
                    $this->db->query($this->db->prepare(
                        "INSERT IGNORE INTO {$this->productValues}(product_id,facet_id,value_id) VALUES(%d,%d,%d)",
                        $productId,
                        $facetId,
                        $valueId
                    ));
                }
            }
            $this->db->query('COMMIT');
        } catch (\Throwable $e) {
            $this->db->query('ROLLBACK');
            throw $e;
        }
    }

    public function labelsForProducts(array $productIds, array $facetIds): array
    {
        $productIds = array_values(array_unique(array_filter(array_map('intval', $productIds))));
        $facetIds = array_values(array_unique(array_filter(array_map('intval', $facetIds))));
        if (!$productIds || !$facetIds) {
            return [];
        }
        $productIn = implode(',', array_fill(0, count($productIds), '%d'));
        $facetIn = implode(',', array_fill(0, count($facetIds), '%d'));
        $args = array_merge($productIds, $facetIds);
        $rows = $this->db->get_results(
            $this->db->prepare(
                "SELECT pv.product_id,pv.facet_id,v.label
                 FROM {$this->productValues} pv
                 INNER JOIN {$this->values} v ON v.id=pv.value_id
                 WHERE pv.product_id IN ({$productIn}) AND pv.facet_id IN ({$facetIn})
                 ORDER BY pv.product_id,pv.facet_id,v.sort_order,v.label",
                ...$args
            ),
            ARRAY_A
        ) ?: [];
        $out = [];
        foreach ($rows as $row) {
            $productId = (int) $row['product_id'];
            $facetId = (int) $row['facet_id'];
            $out[$productId][$facetId][] = $row['label'];
        }
        return $out;
    }

    public function deleteProduct(int $productId): void
    {
        $this->db->delete($this->productValues, ['product_id' => $productId], ['%d']);
    }
}