<?php

namespace GriffinForms\Admin\Sql;

class Lists extends \GriffinForms\Admin\Sql\Format
{
    protected $search_columns;

    protected function setSearchColumns()
    {
        $this->search_columns = array(
            'name',
            'heading',
            'description'
        );
    }

    protected function combineDates($filters)
    {
        if (!empty($filters['date_from']) && !empty($filters['date_to'])) {
            $filters['dates'] = array(
                'from' => $filters['date_from'],
                'to' => $filters['date_to']
            );
            unset($filters['date_from'], $filters['date_to']);
        }
        return $filters;
    }

    protected function filtersQuery($filters, &$params = [])
    {
        $query = 'WHERE 1=1';
        if (!empty($filters)) {
            $filters = $this->combineDates($filters);
            foreach ($filters as $name => $value) {
                $query_method = str_replace('_', '', $name) . 'Query';
                if (method_exists($this, $query_method)) {
                    $query .= ' AND ' . $this->$query_method($value, $params);
                }
            }
        }
        return $query;
    }

    private function siteLocalDateToUtc(string $date_str, string $time): string
    {
        $tz = wp_timezone();
        $dt = \DateTimeImmutable::createFromFormat('Y-m-d H:i:s', $date_str . ' ' . $time, $tz);
        if (!$dt) {
            return $date_str . ' ' . $time;
        }
        return $dt->setTimezone(new \DateTimeZone('UTC'))->format('Y-m-d H:i:s');
    }

    protected function dateFromQuery($date_from, &$params)
    {
        $params[] = $this->siteLocalDateToUtc($date_from, '00:00:00');
        return 'created >= %s';
    }

    protected function dateToQuery($date_to, &$params)
    {
        $params[] = $this->siteLocalDateToUtc($date_to, '23:59:59');
        return 'created <= %s';
    }

    protected function datesQuery($dates, &$params)
    {
        $params[] = $this->siteLocalDateToUtc($dates['from'], '00:00:00');
        $params[] = $this->siteLocalDateToUtc($dates['to'], '23:59:59');
        return 'created BETWEEN %s AND %s';
    }

    protected function searchQuery($keyword, &$params)
    {
        $params[] = '%' . $keyword . '%';
        return 'CONCAT(' . implode(',', array_map('esc_sql', $this->search_columns)) . ') LIKE %s';
    }

    public function listData($data)
    {
        global $wpdb;
        $params = [];
        $filters = $this->filtersQuery($data['filters'], $params);

        $order_by = esc_sql($data['order_by']);
        $order = esc_sql($data['order']);

        $params[] = absint($data['list_size']);
        $params[] = absint($data['offset']);

        $table = esc_sql($this->table);
        return $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM `{$table}` {$filters} ORDER BY `{$order_by}` {$order} LIMIT %d OFFSET %d",
            ...$params
        ));
    }

    public function resultsCount($data)
    {
        global $wpdb;
        $params = [];
        $filters = $this->filtersQuery($data['filters'], $params);

        $order_by = esc_sql($data['order_by']);
        $order = esc_sql($data['order']);

        return $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(id) FROM %i {$filters} ORDER BY `{$order_by}` {$order}",
            $this->table,
            ...$params
        ));
    }

    public function submissionCount($form_id)
    {
        global $wpdb;
        $table = esc_sql($this->config->getTable('submission'));

        return $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM %i WHERE form_id = %d",
            $table, absint($form_id)
        ));
    }
}