<?php

namespace App\Services;

use Exception;
use Illuminate\Support\Str;
use App\Models\DataConnection;
use App\Services\ReportService;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class DataConnectionTablesService
{
    public $dataConnectionService;
    public function __construct(DataConnectionService $dataConnectionService)
    {
        $this->dataConnectionService =  $dataConnectionService;
    }


    public function getTables($connection)
    {
        if(trim($connection) === "default") {
            return $this->getDefaultConnectionTables();
        }
        return $this->getConnectionTables($connection);

    }

    public function getColumns($connection, $table, $types = [])
    {
        if(trim($connection) === "default") {
            return $this->getDefaultConnectionColumns($table, $types);
        }
        return $this->getConnectionTableColumns($connection, $table, $types);
    }



    public function getColumnType($connection, $table, $column)
    {
        if(trim($connection) === "default") {
            return $this->getDefaultConnectionColumnType($table, $column);
        }
        return $this->getConnectionTableColumnType($connection, $table, $column);

    }

    public function getValues($connection, $table, $column)
    {
        if(trim($connection) === "default") {
            return $this->getDefaultConnectionValues($table, $column);
        }
        return $this->getConnectionTableValues($connection, $table, $column);

    }

    public function getDefaultConnectionTables()
    {
        $connection = $this->dataConnectionService->getDefaultConnection();
        $prefix = config('srm_config.installer.table_prefix', 'srm_');

        $tables = [];
        foreach($connection->getSchemaBuilder()->getAllTables() as $table) {
            $tables[] = $table->{"Tables_in_" . $connection->getDatabaseName()};
        }

        $tables = array_filter($tables, function ($element) use ($prefix) {
            return strpos($element, $prefix) !== 0;
        });

        return $tables;
    }

    public function getDefaultConnectionColumns($table, $types = [])
    {
        $connection = $this->dataConnectionService->getDefaultConnection();

        $columns = [];

        if(empty($types)) {
            foreach($connection->getSchemaBuilder()->getColumnListing($table) as $column) {
                $columns[] = $column;
            }

        } else {
            if(empty(array_diff($types, ["date", "datetime", "char", "varchar", "string", "timestamp"]))) {

                $columns = $this->getDateTimeColumns('default', $table, $types);
            } else {
                $columns = $this->getColumnsType('default', $table, $types);
            }
        }

        return $columns;
    }

    public function getDefaultConnectionColumnType($table, $column)
    {
        $connection = $this->dataConnectionService->getDefaultConnection();

        try {
            return $connection->getSchemaBuilder()->getColumnType($table, strtolower($column));

        } catch(\Exception $e) {
            return $connection->getSchemaBuilder()->getColumnType($table, $column);

        }
    }

    public function getDefaultConnectionValues($table, $column)
    {
        $connection = $this->dataConnectionService->getDefaultConnection();

        return array_column($connection->table($table)->select($column)->distinct()->get()->toArray(), $column);
    }


    public function getConnectionTables($connection)
    {
        $connection = $this->dataConnectionService->getConnection($connection);
        $prefix = config('srm_config.installer.table_prefix', 'srm_');

        $tables = [];
        foreach($connection->getSchemaBuilder()->getAllTables() as $table) {
            $tables[] = $table->{"Tables_in_" . $connection->getDatabaseName()};
        }

        $tables = array_filter($tables, function ($element) use ($prefix) {
            return strpos($element, 'srm_') !== 0;
        });

        return $tables;
    }

    public function getConnectionTableColumns($connection, $table, $types = [])
    {
        $connectionID = $connection;
        $connection = $this->dataConnectionService->getConnection($connection);
        $columns = [];

        if(empty($types)) {
            foreach($connection->getSchemaBuilder()->getColumnListing($table) as $column) {
                $columns[] = $column;
            }

        } else {
            if(empty(array_diff($types, ["date", "datetime", "char", "varchar", "string", "timestamp"]))) {

                $columns = $this->getDateTimeColumns($connectionID, $table, $types);
            } else {
                $columns = $this->getColumnsType($connectionID, $table, $types);
            }
        }
        return $columns;
    }

    public function getDateTimeColumns($connection, $table, $types)
    {
        $connectionID = $connection;
        $connection = $this->dataConnectionService->getConnection($connection);
        $columns = [];

        foreach($connection->getSchemaBuilder()->getColumnListing($table) as $column) {
            if(
                in_array($this->getColumnType($connectionID, $table, $column), ["date", "datetime", "timestamp"])

            ) {
                $columns[] = $column;

            } elseif($this->checkIsDate($connection, $table, $column)) {

                $columns[] = $column;
            }
        }
        return $columns;
    }


    public function checkIsDate($connection, $table, $column)
    {
        if(!$connection->table($table)->select($column)->get()->isEmpty()) {
            $firstRowValue = $connection->table($table)->select($column)->get()[0]->$column;
        } else {
            return false;
        }
        // Try to parse the date using strtotime
        $timestamp = strtotime($firstRowValue);

        // Check if the timestamp is valid
        if ($timestamp === false) {
            return false;
        }

        // Check if the resulting date is a valid Gregorian date
        $date = date('Y-m-d', $timestamp);
        $parts = explode('-', $date);
        if (!checkdate($parts[1], $parts[2], $parts[0])) {
            return false;
        }

        return true;

    }


    public function getColumnsType($connection, $table, $types)
    {
        $connectionID = $connection;
        $connection = $this->dataConnectionService->getConnection($connection);

        foreach($connection->getSchemaBuilder()->getColumnListing($table) as $column) {
            if(
                in_array($this->getColumnType($connectionID, $table, $column), $types)
            ) {
                $columns[] = $column;
            }
        }
        return $columns;

    }


    public function getConnectionTableColumnType($connection, $table, $column)
    {
        $connection = $this->dataConnectionService->getConnection($connection);

        try {

            return $connection->getSchemaBuilder()->getColumnType($table, strtolower($column));
        } catch(\Exception $e) {

            return $connection->getSchemaBuilder()->getColumnType($table, $column);

        }
    }


    public function getConnectionTableValues($connection, $table, $column)
    {
        $connection = $this->dataConnectionService->getConnection($connection);

        return array_column($connection->table($table)->select($column)->distinct()->get()->toArray(), $column);
    }



}
