<?php

declare(strict_types=1);

namespace PortalPeople\dataexchange\services;

use Yii;
use yii\db\Expression;
use yii\web\UploadedFile;

/**
 * DataExchangeService
 *
 * Handles all data import/export operations for ERP integration.
 * Extracted from api/modules/v1/controllers/DataController.
 */
class DataExchangeService
{
    /**
     * Return rows from a model filtered by recency.
     *
     * @param string $model   Fully-qualified model class name
     * @param int    $days    Number of past days to include
     * @param int    $qty     Result limit
     * @param string $order   Column to order by (DESC)
     * @return array<int, array<string, mixed>>
     */
    public function index(string $model, int $days, int $qty, string $order): array
    {
        return $model::find()
            ->where(['>=', 'created_at', strtotime("-{$days} days")])
            ->limit($qty)
            ->orderBy([$order => SORT_DESC])
            ->asArray()
            ->all();
    }

    /**
     * Return field labels for a model.
     *
     * @param string $model  Fully-qualified model class name
     * @return array<int, array<string, string>>
     */
    public function getModelFields(string $model): array
    {
        $fields = [];
        $result = $model::find()->one();

        foreach ($result->fields() as $Field) {
            $fields[] = [$Field => $result->getAttributeLabel($Field)];
        }

        return $fields;
    }

    /**
     * Accept a compressed data file upload and queue an import job.
     */
    public function upload(string $modelname, string $connection, string $db, string $company): mixed
    {
        $model    = '\\api\\modules\\v1\\models\\' . str_replace('DF', '', $modelname);
        $base     = Yii::getAlias('@console');
        $upload   = UploadedFile::getInstanceByName('compresseddata');
        $filepath = $base . '/temp/data/' . date('d-Hi') . '-' . $upload->name;

        file_put_contents($filepath, file_get_contents($upload->tempName));

        return Yii::$app->dfqueue
            ->jobname('Import ' . date('d-Hi') . '-' . $model)
            ->ttr(7200)
            ->push(new \console\jobs\system\Apiuploaddata([
                'filepath' => date('d-Hi') . '-' . $upload->name,
                'model'    => $model,
            ]));
    }

    /**
     * Return un-downloaded orders (Paid/Ordered) formatted for ERP.
     *
     * @return array<string, mixed>|array<int, string>
     */
    public function downloadOrders(string $type): array
    {
        $down   = new Expression("JSON_EXTRACT(basket.extended, '$.downloaded')");
        $params = $this->frontendParams();

        $carts = \frontend\models\Cart::find()
            ->where(['basket.status' => ['Paid', 'Ordered']])
            ->andWhere(['or', ['is', $down, null], ['=', $down, false]])
            ->andWhere(['not', ['in', 'basketlines.productid', [
                $params['site']['account']['pay_invoices']['invoice_code'],
                $params['site']['account']['pay_invoices']['credit_code'],
            ]]])
            ->leftJoin('basketlines', 'basketlines.basketid = basket.id and basketlines.status = "Active"')
            ->all();

        if ($type === 'csv') {
            return array_column($carts, 'orderref');
        }

        $output = [];
        foreach ($carts as $cart) {
            foreach ($cart->cartlines as $line) {
                $stockCode = empty($line->stock_code) === false
                    ? trim($line->stock_code)
                    : ($line->product ? $line->product->stock_code : null);

                $output[$cart->orderref][] = [
                    'order_number'    => $cart->orderref,
                    'order_date'      => date('d/m/y', strtotime(str_replace('/', '-', $cart->dateordered))),
                    'time'            => date('Hi', strtotime(str_replace('/', '-', $cart->dateordered))),
                    'product'         => $stockCode,
                    'product_desc'    => trim($line->product_name),
                    'product_qty'     => $line->qty,
                    'prod_price_ea'   => $line->price,
                    'prod_price_to'   => $line->linetotal,
                    'goods_total'     => $cart->subtotal,
                    'ship_total'      => $cart->shipping,
                    'tax_total'       => $cart->tax,
                    'disc_total'      => $cart->discount,
                    'order_total'     => $cart->total,
                    'cust_name'       => trim($cart->contact),
                    'cust_company'    => trim($cart->companyname),
                    'cust_add1'       => trim($cart->fulladdresses['billing']->address1),
                    'cust_add2'       => trim($cart->fulladdresses['billing']->address2),
                    'cust_town'       => trim($cart->fulladdresses['billing']->town),
                    'cust_county'     => trim($cart->fulladdresses['billing']->county),
                    'cust_country'    => trim($cart->fulladdresses['billing']->country),
                    'cust_postcode'   => trim($cart->fulladdresses['billing']->postcode),
                    'cust_tel'        => trim($cart->fulladdresses['billing']->telephone),
                    'cust_fax'        => trim($cart->fulladdresses['billing']->fax),
                    'cust_email'      => trim($cart->fulladdresses['billing']->email),
                    'del_name'        => null,
                    'del_company'     => trim($cart->fulladdresses['shipping']->company_name),
                    'del_add1'        => trim($cart->fulladdresses['shipping']->address1),
                    'del_add2'        => trim($cart->fulladdresses['shipping']->address2),
                    'del_town'        => trim($cart->fulladdresses['shipping']->town),
                    'del_county'      => trim($cart->fulladdresses['shipping']->county),
                    'del_country'     => trim($cart->fulladdresses['shipping']->country),
                    'del_postcode'    => trim($cart->fulladdresses['shipping']->postcode),
                    'del_tel'         => trim($cart->fulladdresses['shipping']->telephone),
                    'paid'            => $cart->paid == 1 ? 'Y' : 'N',
                    'pay_type'        => null,
                    'pay_c_status'    => null,
                    'pay_processed'   => null,
                    'pay_gateway'     => null,
                    'pay_trans_ref'   => null,
                    'pay_status'      => null,
                    'pay_agr_terms'   => null,
                    'ipaddress'       => $cart->ipaddress,
                    'ref_site'        => null,
                    'location'        => null,
                    'account_code'    => trim($cart->account_code),
                    'order_created'   => null,
                    'ord_error'       => null,
                    'size'            => null,
                    'code_size'       => $stockCode,
                    'stock_cde_fnd'   => null,
                    'orderID'         => $cart->id,
                    'branchcode'      => null,
                    'main_code'       => null,
                    'hy_code'         => null,
                    'cust_ref'        => trim($cart->porderref),
                    'addit_info'      => null,
                    'branch_out'      => null,
                    'create_user'     => null,
                    'create_date'     => null,
                    'create_time'     => null,
                ];
            }
        }

        return $output;
    }

    /**
     * Return un-downloaded invoice/credit payment orders.
     *
     * @return array<int, string>
     */
    public function downloadPaidOrders(string $type): array
    {
        $down   = new Expression("JSON_EXTRACT(basket.extended, '$.downloaded')");
        $params = $this->frontendParams();

        $carts = \frontend\models\Cart::find()
            ->where(['basket.status' => 'Paid'])
            ->andWhere(['or', ['is', $down, null], ['=', $down, false]])
            ->andWhere(['in', 'basketlines.productid', [
                $params['site']['account']['pay_invoices']['invoice_code'],
                $params['site']['account']['pay_invoices']['credit_code'],
            ]])
            ->leftJoin('basketlines', 'basketlines.basketid = basket.id and basketlines.status = "Active"')
            ->andWhere(['or', ['is', $down, null], ['=', $down, false]])
            ->all();

        if ($type === 'csv') {
            return array_column($carts, 'orderref');
        }

        return [];
    }

    /**
     * Stream a CSV download for a single order reference.
     *
     * @return mixed
     */
    public function downloadCsv(string $orderref): mixed
    {
        $down = new Expression("JSON_EXTRACT(basket.extended, '$.downloaded')");

        $cart = \frontend\models\Cart::find()
            ->select(['basket.*', "{$down} as downloaded"])
            ->where(['basket.orderref' => $orderref])
            ->andWhere(['or', ['is', $down, null], ['=', $down, false]])
            ->one();

        if ($cart) {
            Yii::$app->response->statusCode = 200;
            return $cart->exporttoCsv('cartlines', 'download');
        }

        Yii::$app->response->statusCode = 100;
        return ['status' => 100, 'message' => 'No Cart at this address'];
    }

    /**
     * Stream a CSV download for a single paid/invoice order.
     *
     * @return mixed
     */
    public function downloadPaidCsv(string $orderref): mixed
    {
        $params = $this->frontendParams();
        $down   = new Expression("JSON_EXTRACT(basket.extended, '$.downloaded')");

        $cart = \frontend\models\Cart::find()
            ->select(['basket.*', "{$down} as downloaded"])
            ->where(['basket.orderref' => $orderref])
            ->andWhere(['or', ['is', $down, null], ['=', $down, false]])
            ->one();

        if ($cart) {
            Yii::$app->response->statusCode = 200;

            if (in_array(
                $params['site']['account']['pay_invoices']['invoice_code'],
                array_column($cart->cartlines, 'productid')
            )) {
                return $cart->ExportPaidInvoice('cartlines', 'download');
            }

            return $cart->exporttoCsv('cartlines', 'download');
        }

        Yii::$app->response->statusCode = 100;
        return ['status' => 100, 'message' => 'No Cart at this address'];
    }

    /**
     * Stream a grouped CSV download for a month.
     *
     * @return mixed
     */
    public function downloadCsvGroup(?string $month): mixed
    {
        $month = $month ?? date('M', strtotime('last month'));
        $data  = \modules\custom\models\Cart::exporttoCsvmonth('cartlines', 'download', $month);

        if ($data) {
            Yii::$app->response->statusCode = 200;
            return $data;
        }

        Yii::$app->response->statusCode = 100;
        return ['status' => 100, 'message' => 'No Carts at this address'];
    }

    /**
     * Mark order(s) as downloaded in the ERP.
     *
     * @param array<string, mixed> $params  Request post params
     * @return mixed
     */
    public function markDownloaded(array $params): mixed
    {
        $down = new Expression("JSON_EXTRACT(basket.extended, '$.downloaded')");

        if (($params['type'] ?? null) === 'csv') {
            $orderref = $params['orderref'];

            $model = \frontend\models\Cart::find()
                ->where(['basket.orderref' => $orderref])
                ->andWhere(['in', 'basket.status', ['Ordered', 'Paid']])
                ->andWhere(['or', ['is', $down, null], ['=', $down, false]])
                ->one();

            if ($model) {
                $extended               = $model->extended;
                $extended->downloaded   = 1;
                $extended->downloadeddate = date('d/m/y H:i');

                return $model->updateAttributes([
                    'billingstatus' => 'Sent to Erp',
                    'extended'      => json_encode($extended),
                ]);
            }

            return null;
        }

        $carts   = \frontend\models\Cart::find()
            ->where(['basket.status' => 'Ordered'])
            ->andWhere(['or', ['is', $down, null], ['=', $down, false]])
            ->all();

        $results = [];
        $saved   = $params['saved'] ?? [];

        foreach ($params['rows'] ?? [] as $orderno => $drows) {
            if (isset($saved[$orderno]) && count($saved[$orderno]) === $drows) {
                $results[$orderno] = $drows;
            }
        }

        foreach ($carts as $model) {
            if (isset($results[$model->orderref])
                && (count($model->cartlines) === $results[$model->orderref]
                    || $results[$model->orderref] === '*')
            ) {
                $extended               = $model->extended;
                $extended->downloaded   = 1;
                $extended->downloadeddate = date('d/m/y H:i');

                return $model->updateAttributes([
                    'billingstatus' => 'Sent to Erp',
                    'extended'      => json_encode($extended),
                ]);
            }
        }

        return null;
    }

    /**
     * Download all key model data as a base64+gzip blob.
     *
     * @param string|null $date  Optional ISO date filter
     * @return string
     */
    public function download(?string $date): string
    {
        $classes = [
            '\app\models\Products'       => '\backend\models\Productsbe',
            '\app\models\Lookuptablebe'  => '\backend\models\Lookuptablebe',
            '\app\models\Categories'     => '\backend\models\Categoriesbe',
            '\app\models\Settings'       => '\backend\models\Settings',
            '\app\models\Seobe'          => '\backend\models\Seobe',
        ];

        $output = [];

        foreach ($classes as $destination => $source) {
            if ($date === null) {
                $output[$destination] = $source::find()->asArray()->all();
            } else {
                $output[$destination] = $source::find()
                    ->where(['or',
                        ['>=', 'created_at', strtotime($date)],
                        ['>=', 'updated_at', strtotime($date)],
                    ])
                    ->asArray()
                    ->all();
            }
        }

        return base64_encode(gzcompress(json_encode($output)));
    }

    /**
     * Health check — returns true.
     */
    public function check(): bool
    {
        return true;
    }

    /**
     * Check remote IDs against local records and delete orphans.
     *
     * @return array<string, mixed>
     */
    public function checkIds(string $modelname): array
    {
        $model  = '\\api\\modules\\v1\\models\\' . str_replace('DF', '', $modelname);
        $base   = Yii::getAlias('@console');
        $upload = UploadedFile::getInstanceByName('compresseddata');

        $filepath = $base . '/temp/data/' . date('d-Hi') . '-' . $upload->name;
        file_put_contents($filepath, file_get_contents($upload->tempName));

        $data    = json_decode(gzuncompress(base64_decode(file_get_contents($filepath))), true);
        $lmodel  = $model::instance();
        $pk      = $lmodel->tableSchema->primaryKey;
        $results = $model::find()->select($pk)->asArray()->all();
        $pkex    = $pk[0];
        $deleted = 0;
        $local   = [];

        foreach ($results as $array) {
            if (!in_array($array[$pkex], $data, true)) {
                $local[] = $array[$pkex];
                $delmodel = $model::find()->where([$pkex => $array[$pkex]])->one();

                if ($delmodel->delete() !== null) {
                    $deleted++;
                }

                Yii::$app->queue
                    ->jobname('Cleanup')
                    ->ttr(7200)
                    ->push(new \frontend\jobs\Cleanup(['model' => $model, 'id' => $array[$pkex]]));
            }
        }

        return ['status' => 200, 'message' => $deleted . '/' . count($local)];
    }

    /**
     * Ingest Sage product group discount data.
     *
     * @param array<int, array<string, mixed>> $data
     */
    public function importSageData(array $data): void
    {
        $existing = \backend\models\SageDiscounts::find()->all();

        if ($existing) {
            foreach ($existing as $model) {
                $status = 'Bad';

                foreach ($data as $array) {
                    if ($model->PGQDB_ProductGroupDiscountID === $array['PGQDB_ProductGroupDiscountID']) {
                        $status = 'Good';
                        break;
                    } else {
                        \backend\models\SageDiscounts::check($array);
                    }
                }

                if ($status === 'Bad') {
                    $model->updateAttributes(['status' => 'Deleted']);
                }
            }
        }

        foreach ($data as $array) {
            \backend\models\SageDiscounts::check($array);
        }
    }

    // -------------------------------------------------------------------------
    // Private helpers
    // -------------------------------------------------------------------------

    /**
     * Merge frontend params whether running in API or frontend context.
     *
     * @return array<string, mixed>
     */
    private function frontendParams(): array
    {
        if (str_contains(Yii::getAlias('@app'), 'frontend')) {
            return Yii::$app->params;
        }

        return array_merge(
            Yii::$app->params,
            include(Yii::getAlias('@frontend') . '/config/params.php'),
            include(Yii::getAlias('@frontend') . '/config/params-local.php')
        );
    }
}
