<?php

/*
 * This file is part of the VITICORE2 package.
 *
 * (c) CP Creation <web@cpcreation.fr>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

namespace CpCreation\VitiCore\Cart\LegacyOrderImport;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Platforms\AbstractMySQLPlatform;

final class LegacyOrderSourceReader
{
    /**
     * @var array<string, list<string>>
     */
    private const array REQUIRED_SCHEMA = [
        'cart_cart' => [
            'id', 'token', 'shipping', 'shipping_name', 'group_code', 'delivery_method_json', 'promo', 'promo_id',
            'promo_value_amount', 'promo_value_percent', 'promo_with_shipping_fees', 'degressive_value', 'view_ht',
            'tax', 'comment', 'delivery_method_id', 'invoice_address_id', 'delivery_address_id', 'created_at', 'updated_at',
        ],
        'cart_cart_checkout' => [
            'id', 'cart_cart_id', 'payment_method_id', 'payed_at', 'payed_token', 'cgv', 'delivery', 'locale', 'response',
            'colissimo_carrier', 'colissimo_code', 'private_carrier', 'ip_client', 'disabled', 'deleted_at', 'created_at', 'updated_at',
        ],
        'cart_cart_item' => ['id', 'cart_cart_id', 'product_variant_id', 'quantity', 'price', 'created_at', 'updated_at'],
        'cart_invoice_address' => [
            'id', 'company', 'tva_number', 'lastname', 'firstname', 'street', 'postal_code', 'city', 'country', 'email', 'phone',
        ],
        'cart_delivery_address' => [
            'id', 'lastname', 'firstname', 'street', 'postal_code', 'city', 'country', 'email', 'phone', 'different_address',
        ],
        'cart_payment_method' => ['id', 'method'],
        'product_variant' => ['id', 'product_id', 'product_type_id'],
        'product_type' => ['id', 'volume', 'unity'],
        'product_product_translation' => ['translatable_id', 'locale', 'name', 'quality'],
        'product_type_translation' => ['translatable_id', 'locale', 'name'],
    ];

    /**
     * @return list<string>
     */
    public function validate(Connection $source): array
    {
        $errors = [];

        if (!$source->getDatabasePlatform() instanceof AbstractMySQLPlatform) {
            $errors[] = 'La base source doit utiliser MySQL ou MariaDB.';

            return $errors;
        }

        foreach (self::REQUIRED_SCHEMA as $table => $requiredColumns) {
            $columnNames = $source->fetchFirstColumn(<<<'SQL'
                SELECT LOWER(column_name)
                FROM information_schema.columns
                WHERE table_schema = DATABASE() AND table_name = :table_name
                SQL, ['table_name' => $table]);

            if ([] === $columnNames) {
                $errors[] = \sprintf('Table source requise absente : %s.', $table);

                continue;
            }

            $columns = array_fill_keys(array_map('strval', $columnNames), true);

            foreach ($requiredColumns as $column) {
                if (!isset($columns[$column])) {
                    $errors[] = \sprintf('Colonne source requise absente : %s.%s.', $table, $column);
                }
            }
        }

        return $errors;
    }

    /**
     * @return list<array<string, mixed>>
     */
    public function orders(Connection $source, string $scope): array
    {
        $paidFilter = 'paid' === $scope ? ' AND checkout.payed_at IS NOT NULL' : '';

        return $source->fetchAllAssociative(<<<SQL
            SELECT
                cart.id AS source_cart_id,
                cart.token,
                cart.shipping,
                cart.shipping_name,
                cart.group_code,
                cart.delivery_method_json,
                cart.promo,
                cart.promo_id,
                cart.promo_value_amount,
                cart.promo_value_percent,
                cart.promo_with_shipping_fees,
                cart.degressive_value,
                cart.view_ht,
                cart.tax,
                cart.comment,
                cart.delivery_method_id AS source_delivery_method_id,
                cart.created_at AS cart_created_at,
                cart.updated_at AS cart_updated_at,
                checkout.id AS source_checkout_id,
                checkout.payed_at,
                checkout.payed_token,
                checkout.cgv,
                checkout.delivery,
                checkout.locale,
                checkout.response AS checkout_response,
                checkout.colissimo_carrier,
                checkout.colissimo_code,
                checkout.private_carrier,
                checkout.ip_client,
                checkout.disabled,
                checkout.created_at AS checkout_created_at,
                checkout.updated_at AS checkout_updated_at,
                payment_method.method AS source_payment_method,
                invoice.id AS source_invoice_address_id,
                invoice.company AS invoice_company,
                invoice.tva_number AS invoice_vat_id,
                invoice.lastname AS invoice_lastname,
                invoice.firstname AS invoice_firstname,
                invoice.street AS invoice_street,
                invoice.postal_code AS invoice_postal,
                invoice.city AS invoice_city,
                invoice.country AS invoice_country,
                invoice.email AS invoice_email,
                invoice.phone AS invoice_phone,
                delivery_address.id AS source_delivery_address_id,
                delivery_address.lastname AS delivery_lastname,
                delivery_address.firstname AS delivery_firstname,
                delivery_address.street AS delivery_street,
                delivery_address.postal_code AS delivery_postal,
                delivery_address.city AS delivery_city,
                delivery_address.country AS delivery_country,
                delivery_address.email AS delivery_email,
                delivery_address.phone AS delivery_phone,
                delivery_address.different_address
            FROM cart_cart_checkout checkout
            INNER JOIN cart_cart cart ON cart.id = checkout.cart_cart_id
            LEFT JOIN cart_payment_method payment_method ON payment_method.id = checkout.payment_method_id
            LEFT JOIN cart_invoice_address invoice ON invoice.id = cart.invoice_address_id
            LEFT JOIN cart_delivery_address delivery_address ON delivery_address.id = cart.delivery_address_id
            WHERE checkout.deleted_at IS NULL{$paidFilter}
            ORDER BY checkout.created_at ASC, checkout.id ASC
            SQL);
    }

    /**
     * @return array<string, list<array<string, mixed>>>
     */
    public function linesByCart(Connection $source, string $scope): array
    {
        $paidFilter = 'paid' === $scope ? ' AND checkout.payed_at IS NOT NULL' : '';
        $rows = $source->fetchAllAssociative(<<<SQL
            SELECT
                item.id AS source_item_id,
                item.cart_cart_id AS source_cart_id,
                item.product_variant_id AS source_variant_id,
                item.quantity,
                item.price,
                item.created_at AS item_created_at,
                item.updated_at AS item_updated_at,
                variant.product_id AS source_product_id,
                variant.product_type_id AS source_type_id,
                product_type.volume,
                product_type.unity,
                COALESCE(
                    (SELECT translation.name FROM product_product_translation translation
                        WHERE translation.translatable_id = variant.product_id AND translation.locale = checkout.locale LIMIT 1),
                    (SELECT translation.name FROM product_product_translation translation
                        WHERE translation.translatable_id = variant.product_id AND translation.locale = 'fr' LIMIT 1),
                    (SELECT translation.name FROM product_product_translation translation
                        WHERE translation.translatable_id = variant.product_id ORDER BY translation.locale LIMIT 1)
                ) AS product_name,
                COALESCE(
                    (SELECT translation.quality FROM product_product_translation translation
                        WHERE translation.translatable_id = variant.product_id AND translation.locale = checkout.locale LIMIT 1),
                    (SELECT translation.quality FROM product_product_translation translation
                        WHERE translation.translatable_id = variant.product_id AND translation.locale = 'fr' LIMIT 1),
                    (SELECT translation.quality FROM product_product_translation translation
                        WHERE translation.translatable_id = variant.product_id ORDER BY translation.locale LIMIT 1)
                ) AS product_quality,
                COALESCE(
                    (SELECT translation.name FROM product_type_translation translation
                        WHERE translation.translatable_id = variant.product_type_id AND translation.locale = checkout.locale LIMIT 1),
                    (SELECT translation.name FROM product_type_translation translation
                        WHERE translation.translatable_id = variant.product_type_id AND translation.locale = 'fr' LIMIT 1),
                    (SELECT translation.name FROM product_type_translation translation
                        WHERE translation.translatable_id = variant.product_type_id ORDER BY translation.locale LIMIT 1)
                ) AS type_name
            FROM cart_cart_item item
            INNER JOIN cart_cart_checkout checkout ON checkout.cart_cart_id = item.cart_cart_id
            LEFT JOIN product_variant variant ON variant.id = item.product_variant_id
            LEFT JOIN product_type product_type ON product_type.id = variant.product_type_id
            WHERE checkout.deleted_at IS NULL{$paidFilter}
            ORDER BY item.cart_cart_id ASC, item.created_at ASC, item.id ASC
            SQL);

        $linesByCart = [];

        foreach ($rows as $row) {
            $cartId = (string) $row['source_cart_id'];
            $linesByCart[$cartId] ??= [];
            $linesByCart[$cartId][] = $row;
        }

        return $linesByCart;
    }

    public function giftCount(Connection $source, string $scope): int
    {
        $tableExists = (int) $source->fetchOne(<<<'SQL'
            SELECT COUNT(*)
            FROM information_schema.tables
            WHERE table_schema = DATABASE() AND table_name = 'product_gift'
            SQL) > 0;

        if (!$tableExists) {
            return 0;
        }

        $paidFilter = 'paid' === $scope ? ' AND checkout.payed_at IS NOT NULL' : '';

        return (int) $source->fetchOne(<<<SQL
            SELECT COUNT(*)
            FROM product_gift gift
            INNER JOIN cart_cart_item item ON item.id = gift.cart_item_id
            INNER JOIN cart_cart_checkout checkout ON checkout.cart_cart_id = item.cart_cart_id
            WHERE checkout.deleted_at IS NULL{$paidFilter}
            SQL);
    }
}
