<?php

namespace App\Builders;

use App\Models\Resource;
use App\Services\DataConnectionService;
use App\Generators\ChartScriptGenerator;
use App\Services\DataConnectionTablesService;
use App\Builders\Helpers\InterpolateDateFilter;
use App\Builders\Helpers\InterpolateTimeSeries;
use Illuminate\Http\Response;

class ChartBuilder
{
    private $chart;
    private $connection;
    private $query;
    private $options;
    private $dataConnectionService;

    public function __construct(Resource $chart)
    {
        $this->chart                 = $chart;
        $this->dataConnectionService = new DataConnectionService();
    }

    private function initializeConnection()
    {
        $this->query = (new DataConnectionService())
            ->getConnection(
                isset($this->chart->data_connection_id)
                ? $this->chart->data_connection_id
                : 'default'
            );

        return $this;

    }

    private function populateOptions()
    {
        $this->options = [
            'name'                => $this->chart->getResourceConfiguration('name'),
            'title'               => $this->chart->getResourceConfiguration('title'),
            'chart_type'          => $this->chart->getResourceConfiguration('chart_type'),
            'x_table'             => $this->chart->getResourceConfiguration('x_table'),
            'y_table'             => $this->chart->getResourceConfiguration('y_table'),
            'x_axis_column'       => $this->chart->getResourceConfiguration('x_axis_column'),
            'y_axis_column'       => $this->chart->getResourceConfiguration('y_axis_column'),
            'data_filter_columns' => $this->chart->getResourceConfiguration('data_filter_columns'),
            'date_range'          => $this->chart->getResourceConfiguration('date_range'),
            'date_time_scale'     => $this->chart->getResourceConfiguration('date_time_scale'),
            'function'            => $this->chart->getResourceConfiguration('function'),
            'drill_down'          => $this->chart->getResourceConfiguration('drill_down'),
            'label'               => $this->chart->getResourceConfiguration('label')

        ];

        return $this;
    }

    private function addTables()
    {

        if ($this->options['x_table'] == $this->options['y_table']) {

            $this->query = $this->query->table(
                $this->options['x_table']
            );

        } else {

            $relation = explode("=", $this->getTablesRelation());

            $this->query = $this->query
                ->table(
                    $this->options['x_table']
                )->join(
                $this->options['y_table'],
                $relation[0],
                "=",
                $relation[1]
            );
        }

        return $this;
    }

    private function addAxis()
    {

        if (!$this->isXAxisDateTime()) {
            $this->query = $this->query->selectRaw(
                "{$this->getXAxis()} as x_axis," .
                $this->getYAxisWithFunction()
            );
        }

        return $this;
    }

    private function addDateFilters()
    {

        if ($this->isXAxisDateTime()) {
            // dump($this->getDatesBasedOnDateRange());
            $this->query = $this->query->whereBetween(
                $this->getXAxis(),
                $this->getDatesBasedOnDateRange()
            );

        }

        return $this;
    }

    private function addSelectData()
    {

        if ($this->isXAxisDateTime()) {
            $this->query = $this->query->selectRaw(
                $this->getSelect()
            );

        }

        return $this;

    }

    private function addXFilters()
    {

        if ($this->isXAxisTextual()) {

            $values = $this->options['data_filter_columns'];

            if (is_array($values) && !empty($values)) {
                $this->query = $this->query->whereIn(
                    $this->getXAxis(),
                    $values
                );
            } else {
                $this->query = $this->query->whereIn(
                    $this->getXAxis(),
                    []
                );
            }

        }

        return $this;
    }

    private function removeNullsFromXAxis()
    {
        $this->query = $this->query->whereNotNull(
            $this->getXAxis()
        );
        return $this;
    }

    private function addGroupBy()
    {
        $this->query = $this->query->orderByRaw($this->getXAxisWithTimeScaleWithoutAlias())->groupByRaw(
            $this->getXAxisWithTimeScaleWithoutAlias()
        );

        return $this;
    }

    private function getTablesRelation()
    {

        $foreignKeys = $this->query->select("
            SELECT
                constraint_name as constraint_name,
                column_name as column_name,
                referenced_table_name as referenced_table_name,
                referenced_column_name as referenced_column_name
            FROM
                information_schema.key_column_usage
            WHERE
                referenced_table_name IS NOT NULL
                AND table_name = '{$this->options['x_table']}'
        ");

        foreach ($foreignKeys as $foreignKey) {

            if ($foreignKey->referenced_table_name == $this->options['y_table']) {
                return "{$this->options['x_table']}.{$foreignKey->column_name}={$this->options['y_table']}.{$foreignKey->referenced_column_name}";
            }

        }

        $foreignKeys = $this->query->select("
            SELECT
                constraint_name as constraint_name,
                column_name as column_name,
                referenced_table_name as referenced_table_name,
                referenced_column_name as referenced_column_name
            FROM
                information_schema.key_column_usage
            WHERE
                referenced_table_name IS NOT NULL
                AND table_name = '{$this->options['y_table']}'
        ");

        foreach ($foreignKeys as $foreignKey) {

            if ($foreignKey->referenced_table_name == $this->options['x_table']) {
                return "{$this->options['y_table']}.{$foreignKey->column_name}={$this->options['x_table']}.{$foreignKey->referenced_column_name}";
            }

        }

    }

    private function isXAxisDateTime()
    {
        return (
            $this->options['chart_type'] == "timeseries"
            || in_array(
                $this->getColumnType($this->options['x_table'], $this->options['x_axis_column']),
                ["timestamp", "date", "datetime"]
            )
        );
    }

    private function isXAxisTextual()
    {

        return (
            $this->options['chart_type'] != "timeseries"
            && !in_array(
                $this->getColumnType($this->options['x_table'], $this->options['x_axis_column']),
                ["timestamp", "date", "datetime", "bigint", "decimal", "double", "float", "int", "integer", "mediumint", "smallint", "tinyint"
                    , "unsignedbiginteger", "unsignedinteger", "unsignedmediuminteger", "unsignedsmallinteger", "unsignedtinyinteger"]
            )

        );

    }

    private function getDatesBasedOnDateRange()
    {

        if (!empty($this->options['date_range'])) {

            switch ($this->options['date_range']) {
                case 'last_5_years':
                    return [now()->firstOfYear()->subYears(5)->toDateTimeString(), now()->endOfYear()->toDateTimeString()];
                    break;
                case 'this_year':
                    return [now()->firstOfYear()->toDateTimeString(), now()->toDateTimeString()];

                    break;
                case 'last_12_months':

                    if ($this->options["date_time_scale"] == "quarters") {
                        $end = now()->subMonth()->endOfQuarter()->toDateTimeString();
                    } else {
                        $end = now()->subMonth()->endOfMonth()->toDateTimeString();

                    }

                    return [now()->subMonths(12)->firstOfMonth()->toDateTimeString(), $end];

                    break;
                case 'this_6_months':
                    return [now()->subMonths(5)->startOfMonth()->toDateTimeString(), now()->endOfMonth()->toDateTimeString()];

                    break;
                case 'last_6_months':
                    return [now()->subMonths(6)->startOfMonth()->toDateTimeString(), now()->subMonth()->endOfMonth()->toDateTimeString()];

                    break;
                case 'this_3_months':
                    return [now()->subMonths(2)->startOfMonth()->toDateTimeString(), now()->endOfMonth()->toDateTimeString()];

                    break;
                case 'last_3_months':
                    return [now()->subMonths(3)->startOfMonth()->toDateTimeString(), now()->subMonth()->endOfMonth()->toDateTimeString()];

                    break;
                case 'this_month':
                    return [now()->firstOfMonth()->toDateTimeString(), now()->endOfMonth()->toDateTimeString()];

                    break;
                case 'last_30_days':
                    return [now()->subDays(30)->startOfDay()->toDateTimeString(), now()->subDay()->endOfDay()->toDateTimeString()];

                    break;
                case 'this_week':
                    return [now()->startOfWeek(getStartOfWeek())->toDateTimeString(), now()->endOfWeek(getEndOfWeek())->toDateTimeString()];

                    break;
                case 'last_7_days':
                    return [now()->subDays(7)->toDateTimeString(), now()->subDay()->toDateTimeString()];

                    break;
                case 'today':
                    return [now()->startOfDay()->toDateTimeString(), now()->endOfDay()->toDateTimeString()];

                    break;
                case 'yesterday':
                    return [now()->subDay()->startOfDay()->toDateTimeString(), now()->subDay()->endOfDay()->toDateTimeString()];
                    break;
                default:
                    break;
            }

        }

    }

    private function getXAxisWithTimeScale()
    {
        return "{$this->getSelectTimeScale()} as x_axis";
    }

    private function getXAxisWithTimeScaleWithoutAlias()
    {

        if (!$this->isXAxisDateTime()) {

            return "{$this->getSelectTimeScale()}({$this->getXAxis()})";
        }

        return "{$this->getSelectTimeScale()}";

    }

    private function getYAxisWithFunction()
    {
        return "{$this->getSelectFunction()}({$this->getYAxis()}) as y_axis";
    }

    private function getSelectFunction()
    {

        switch ($this->options['function']) {
            case 'count':
                return 'COUNT';
                break;
            case 'sum':
                return 'SUM';
                break;
            case 'average':
                return 'AVG';
                break;
            case 'max':
                return 'MAX';
                break;
            case 'min':
                return 'MIN';
                break;
            case 'sample-standard-deviation':
                return 'STDDEV';
                break;
            case 'population-standard-deviation':
                return 'STDDEV_POP';
                break;
            default:
                break;
        }

    }

    private function getSelectTimeScale()
    {

        switch ($this->options['date_time_scale']) {
            case 'hours':
                return "DATE_FORMAT({$this->getXAxis()},'%Y-%m-%d %H:00:00')";
                break;
            case 'days':
                return "DATE_FORMAT({$this->getXAxis()},'%Y-%m-%d')";
                break;
            case 'weeks':
                return "FROM_DAYS(TO_DAYS({$this->getXAxis()}) -MOD(TO_DAYS({$this->getXAxis()}) -1, 7))";
                break;
            case 'months':
                return "DATE_FORMAT({$this->getXAxis()},'%Y-%m-01')";

                break;
            case 'quarters':
                return "DATE(CONCAT(YEAR({$this->getXAxis()}),'-', 1 + 3*(QUARTER({$this->getXAxis()})-1),'-01'))";
                break;
            case 'years':
                return "DATE_FORMAT({$this->getXAxis()},'%Y-01-01')";
                break;

            default:
                break;
        }

    }

    private function getSelect()
    {
        return "{$this->getXAxisWithTimeScale()},{$this->getYAxisWithFunction()}";
    }

    private function getXAxis()
    {
        return "{$this->options['x_table']}.{$this->options['x_axis_column']}";
    }

    private function getYAxis()
    {
        return "{$this->options['y_table']}.{$this->options['y_axis_column']}";
    }

    private function getColumnType($table, $column)
    {
        $connection = isset($this->chart->data_connection_id)
        ? $this->chart->data_connection_id
        : 'default';

        return (new DataConnectionTablesService($this->dataConnectionService))->getColumnType(
            $connection,
            $table,
            $column
        );

    }

    private function getAxisValues()
    {
        $xAxis      = [];
        $yAxis      = [];
        $axisValues = json_decode(json_encode($this->query->get()), true);

// dump($this->query->toRawSql());

        foreach ($axisValues as $axis) {

            $xAxis[] = $axis["x_axis"];
            $yAxis[] = $axis["y_axis"];
        }

        return [$xAxis, $yAxis];
    }

    public function build()
    {

        $this->initializeConnection();

        if(!$this->query && !is_null($this->query)){
            if(request()->expectsJson()){
                abort(Response::HTTP_INTERNAL_SERVER_ERROR,"There is a problem with the chosen data connection.");
            }else{
                return $this->chart->data = [
                        "error"  => "There is a problem with the chosen data connection.",
                    ];
            }
        }

        $this->initializeConnection()
            ->populateOptions()
            ->addTables()
            ->addAxis()
            ->addDateFilters()
            ->addSelectData()
            ->addXFilters()
            ->removeNullsFromXAxis()
            ->addGroupBy();

        [$xAxis, $yAxis] = $this->getAxisValues();

        [$xAxis, $yAxis] = (new InterpolateTimeSeries(
            $xAxis,
            $yAxis,
            $this->getDatesBasedOnDateRange(),
            $this->options['date_time_scale']
        ))->interpolateTimeSeries();

        [$xAxis, $yAxis] = (new InterpolateDateFilter(
            $xAxis,
            $yAxis,
            $this->options['data_filter_columns']
        ))->interploate();

        $this->chart->data = [
            "x_axis" => $xAxis,
            "y_axis" => $yAxis
        ];
        $this->chart->script = (new ChartScriptGenerator($this->chart, [$xAxis, $yAxis]))->getChartScript();
    }

}
