<?php

namespace App\Repositories;

use App\Config\Database;

class ProductRepository
{

    /**
     * Allowed fields
     * 
     * @var array
     */
    private const ALLOWED_FIELDS = [
        'id_product'                    => 'p.id_product',
        'reference_product'             => 'p.reference_product',
        'reference_category_product'    => 'p.reference_category_product',
        'name_category'                 => 'c.name_category',
        'reference_brand_product'       => 'p.reference_brand_product',
        'name_brand'                    => 'b.name_brand',
        'name_product'                  => 'p.name_product',
        'name_short_product'            => 'p.name_short_product',
        'description_product'           => 'p.description_product',
        'cost_product'                  => 'p.cost_product',
        'price_sale_product'            => 'p.price_sale_product',
        'quantity_product'              => 'p.quantity_product',
        'tax_product'                   => 'p.tax_product',
        'weight_product'                => 'p.weight_product',
        'width_product'                 => 'p.width_product',
        'high_product'                  => 'p.high_product',
        'cubicaje_product'              => 'p.cubicaje_product',
        'is_active'                     => 'p.is_active',
        'created_at'                    => 'p.created_at',
        'updated_at'                    => 'p.updated_at'


    ];

    /**
     * Get all products by params
     * 
     * @throws Exception
     * 
     * @return array    
     * 
     * @param array $params
     */
    public function getAllProducts(array $params): array
    {
        $fields = $this->resolveSelectFields($params);

        $sql = "
            SELECT $fields, p.reference_product

            FROM products p
            INNER JOIN categories c
                ON c.reference_category = p.reference_category_product
            INNER JOIN brands b
                ON b.reference_brand = p.reference_brand_product
        ";

        [$sql, $types, $values] = $this->applyFilters($sql, $params);
        $sql = $this->applyOrderBy($sql, $params);
        [$sql, $types, $values] = $this->applyPagination($sql, $types, $values, $params);

        $conn = Database::connection();
        $stmt = $conn->prepare($sql);
        $stmt->bind_param($types, ...$values);
        $stmt->execute();

        $products = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

        if (!$products) {
            return [];
        }

        $references = array_column($products, 'reference_product');

        $images = $this->getImagesByReferences($references);

        foreach ($products as &$product) {
            $ref = $product['reference_product'];
            $product['images'] = $images[$ref] ?? [];
        }

        return $products;
    }

    /**
     * Get product by id
     *
     * @throws Exception
     * 
     * @return array|null
     */
    public function getById(int $id): ?array
    {
        $sql = "
            SELECT 
                p.id_product,
                p.reference_product,
                p.reference_category_product,
                c.name_category,
                p.reference_brand_product,
                b.name_brand,
                p.name_product,
                p.name_short_product,
                p.description_product,
                p.cost_product,
                p.price_sale_product,
                p.quantity_product,
                p.tax_product,
                p.weight_product,
                p.width_product,
                p.high_product,
                p.cubicaje_product,
                p.is_active,
                p.created_at,
                p.updated_at,

                pi.id_product_images,
                pi.name_image,
                pi.link_image

            FROM products p
            INNER JOIN categories c
                ON c.reference_category = p.reference_category_product
            INNER JOIN brands b
                ON b.reference_brand = p.reference_brand_product
            LEFT JOIN product_images pi
                ON pi.reference_product = p.reference_product

            WHERE id_product = ?
        ";

        $conn = Database::connection();

        $stmt = $conn->prepare($sql);
        $stmt->bind_param("i", $id);
        $stmt->execute();
        $rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

        if (!$rows) {
            return null;
        }

        $product = [
            'id_product' => $rows[0]['id_product'],
            'reference_product' => $rows[0]['reference_product'],
            'reference_category_product' => $rows[0]['reference_category_product'],
            'name_category' => $rows[0]['name_category'],
            'reference_brand_product' => $rows[0]['reference_brand_product'],
            'name_brand' => $rows[0]['name_brand'],
            'name_product' => $rows[0]['name_product'],
            'name_short_product' => $rows[0]['name_short_product'],
            'description_product' => $rows[0]['description_product'],
            'price_sale_product' => $rows[0]['price_sale_product'],
            'is_active' => $rows[0]['is_active'],
            'created_at' => $rows[0]['created_at'],
            'updated_at' => $rows[0]['updated_at'],
            'images' => []
        ];

        foreach ($rows as $row) {
            if ($row['id_product_images']) {
                $product['images'][] = [
                    'id' => $row['id_product_images'],
                    'name' => $row['name_image'],
                    'url' => $row['link_image']
                ];
            }
        }

        return $product;
    }

    /**
     * Resolve select fields
     * 
     * @return string
     * 
     * @param array $params
     * 
     * @throws Exception
     */
    private function resolveSelectFields(array $params): string
    {
        $mandatoryFields = [
            'id_product',
            'reference_category_product',
            'name_category',
            'reference_brand_product',
            'name_brand',
            'price_sale_product'
        ];

        if (!empty($params['fields'])) {

            $requested = array_flip($params['fields']);

            foreach ($mandatoryFields as $mandatory) {
                if (!isset($requested[$mandatory])) {
                    $params['fields'][] = $mandatory;
                }
            }

            $preferredOrder = [
                'id_product',
                'reference_product',

                'reference_category_product',
                'name_category',

                'reference_brand_product',
                'name_brand',

                'price_sale_product'
            ];

            $orderedFields = [];

            foreach ($preferredOrder as $field) {
                if (in_array($field, $params['fields'], true)) {
                    $orderedFields[] = $field;
                }
            }

            foreach ($params['fields'] as $field) {
                if (!in_array($field, $orderedFields, true)) {
                    $orderedFields[] = $field;
                }
            }

            return implode(', ', array_map([$this, 'sanitizeField'], $orderedFields));
        }

        return implode(', ', array_map([$this, 'sanitizeField'], [
            'id_product',
            'reference_product',
            'reference_category_product',
            'name_category',
            'reference_brand_product',
            'name_brand',
            'name_product',
            'price_sale_product'
        ]));
    }

    /**
     * Apply filters
     * 
     * @return array    
     * 
     * @param array $params
     */
    private function applyFilters(string $sql, array $params): array
    {
        $where = [];
        $types = '';
        $values = [];

        if (!empty($params['filterBy'])) {
            foreach ($params['filterBy'] as $filter) {
                $field = $this->sanitizeField($filter['field']);
                $op    = $this->sanitizeOperator($filter['operator']);

                $where[] = "$field $op ?";
                $types  .= is_int($filter['value']) ? 'i' : 's';
                $values[] = $filter['value'];
            }
        }

        if ($where) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }

        return [$sql, $types, $values];
    }

    /**
     * Apply order by
     * 
     * @return string
     * 
     * @param array $params
     */
    private function applyOrderBy(string $sql, array $params): string
    {
        if (!empty($params['orderBy'])) {
            $orders = [];

            foreach ($params['orderBy'] as $order) {
                $field = $this->sanitizeField($order['field']);
                $dir   = strtoupper($order['direction']) === 'DESC' ? 'DESC' : 'ASC';
                $orders[] = "$field $dir";
            }

            $sql .= ' ORDER BY ' . implode(', ', $orders);
        }

        return $sql;
    }

    /**
     * Apply pagination
     * 
     * @return array
     *  
     * @param array $params
     */
    private function applyPagination(
        string $sql,
        string $types,
        array $values,
        array $params
    ): array {
        $sql .= ' LIMIT ? OFFSET ?';
        $types .= 'ii';
        $values[] = (int)$params['options']['limit'];
        $values[] = (int)$params['options']['offset'];

        return [$sql, $types, $values];
    }

    /**
     * Sanitize field
     * 
     * @throws Exception
     * 
     * @return string
     * 
     * @param string $field
     */
    private function sanitizeField(string $field): string
    {
        if (!isset(self::ALLOWED_FIELDS[$field])) {
            throw new \InvalidArgumentException("INVALID_FIELD: {$field}");
        }

        return self::ALLOWED_FIELDS[$field];
    }

    /**
     * Sanitize operator
     * 
     * @throws Exception
     * 
     * @return string
     * 
     * @param string $op
     */
    private function sanitizeOperator(string $op): string
    {
        $allowed = ['=', '!=', '>', '<', '>=', '<='];
        if (!in_array($op, $allowed, true)) {
            throw new \InvalidArgumentException("INVALID_OPERATOR");
        }
        return $op;
    }

    /**
     * Get images by references
     * 
     * @return array
     * 
     * @param array $references
     */
    private function getImagesByReferences(array $references): array
    {
        if (empty($references)) {
            return [];
        }

        $placeholders = implode(',', array_fill(0, count($references), '?'));
        $types = str_repeat('s', count($references));

        $sql = "
        SELECT
            reference_product,
            name_image,
            link_image
        FROM product_images
        WHERE reference_product IN ($placeholders)
    ";

        $conn = Database::connection();
        $stmt = $conn->prepare($sql);
        $stmt->bind_param($types, ...$references);
        $stmt->execute();

        $rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

        $grouped = [];

        foreach ($rows as $row) {
            $grouped[$row['reference_product']][] = [
                'name' => $row['name_image'],
                'url'  => $row['link_image'],
            ];
        }

        return $grouped;
    }
}
