<?php

namespace Vendtics\App\Repositories;

defined( "ABSPATH" ) || exit;

use Exception;
use Vendtics\App\Models\EntityList;
use Vendtics\App\Models\EntityTag;
use Vendtics\WpMVC\Database\Query\Builder;

trait AdvancedFilter {
    protected $advanced_filter_joins = [];

    protected function advanced_filter_query( Builder $query, array $queries ) {
        $this->advanced_filter( $query, $queries );
        
        foreach ( $this->advanced_filter_joins as $key => $value ) {
            $join_method = "{$key}_join";

            if ( method_exists( $this, $join_method ) ) {    
                $this->$join_method( $query );
            }
        }

        return $query;
    }

    protected function lists_join( Builder $query ) {
        return $query->left_join(
            EntityList::get_table_name() . ' as entity_list', function( $join ) {
                $join->on_column( "contact.id", "entity_list.entity_id" )->on( "entity_list.entity_type", "contacts" );
            }
        );
    }

    protected function tags_join( Builder $query ) {
        return $query->left_join(
            EntityTag::get_table_name() . ' as entity_tag', function( $join ) {
                $join->on_column( "contact.id", "entity_tag.entity_id" )->on( "entity_tag.entity_type", "contacts" );
            }
        );
    }

    protected function advanced_filter( Builder $query, array $group, array $parent_group = ['combinator' => 'and', 'not' => false] ) {
        if ( empty( $group['rules'] ) ) {
            return $query;
        }

        $group_queries = function( Builder $query ) use( $group ) {
            foreach ( $group['rules'] as $rule ) {
                if ( isset( $rule['rules'] ) ) {
                    $this->advanced_filter( $query, $rule, $group );
                } else {
                    $this->bind_query_value( $query, $rule, $group );
                }
            }
        };

        if ( 'or' === $parent_group['combinator'] ) {
            return $query->or_where( $group_queries );
        }

        return $query->where( $group_queries );
    }
    
    protected function bind_query_value( Builder $query, array $rule, array $group ) {
        $combinator = $group['combinator'];
        $value      = $rule['value'];
        $field      = $rule['field'];

        switch ( $rule['operator'] ) {
            case 'contains':
                return ! empty( $value ) ? $this->contains( $query, $field, $combinator, $value ) : $query;
            case 'doesNotContain':
                return ! empty( $value ) ? $this->does_not_contain( $query, $field, $combinator, $value ) : $query;  
            case 'beginsWith':
                return ! empty( $value ) && is_string( $value ) ? $this->begins_with( $query, $field, $combinator, $value ) : $query;
            case 'doesNotBeginWith':
                return ! empty( $value ) && is_string( $value ) ? $this->does_not_begin_with( $query, $field, $combinator, $value ) : $query;
            case 'endsWith':
                return ! empty( $value ) && is_string( $value ) ? $this->ends_with( $query, $field, $combinator, $value ) : $query;
            case 'doesNotEndWith':
                return ! empty( $value ) && is_string( $value ) ? $this->does_not_end_with( $query, $field, $combinator, $value ) : $query;  
            case 'null':
                return ! empty( $value ) && is_string( $value ) ? $this->null( $query, $field, $combinator ) : $query;
            case 'notNull':
                return ! empty( $value ) && is_string( $value ) ? $this->not_null( $query, $field, $combinator ) : $query;
            case '=':
                return ! empty( $value ) && is_string( $value ) ? $this->equal( $query, $field, $combinator, $value ) : $query;
            case '!=':
                return ! empty( $value ) && is_string( $value ) ? $this->not_equal( $query, $field, $combinator, $value ) : $query;
            case '<':
                return ! empty( $value ) && is_string( $value ) ? $this->less_than( $query, $field, $combinator, $value ) : $query;
            case '>':
                return ! empty( $value ) && is_string( $value ) ? $this->greater_than( $query, $field, $combinator, $value ) : $query;
            case 'between':
                return ! empty( $value[0] ) && ! empty( $value[1] ) ? $this->between( $query, $field, $combinator, $value ) : $query;
            case 'notBetween':
                return ! empty( $value[0] ) && ! empty( $value[1] ) ? $this->not_between( $query, $field, $combinator, $value ) : $query;              
        }

        return $query;
    }

    protected function contains( Builder $query, string $field, string $combinator, $value ) {
        if ( is_string( $value ) ) {
            if ( 'contact.all_fields' === $field ) {
                return $this->contact_search_query( $query, $value );
            }

            $field = $this->resolve_field_name( $field );
            
            return 'and' === $combinator ? $query->where_like( $field, "%{$value}%" ) : $query->or_where_like( $field, "%{$value}%" );  
        }

        $field = $this->resolve_field_name( $field );

        return 'and' === $combinator ? $query->where_in( $field, $value ) : $query->or_where_in( $field, $value );
    }

    protected function does_not_contain( Builder $query, string $field, string $combinator, $value ) {
        $field = $this->resolve_field_name( $field );

        if ( is_string( $value ) ) {
            return 'and' === $combinator ? $query->where_not_like( $field, "{$value}%" ) : $query->or_where_not_like( $field, "{$value}%" );
        }

        $not_query = function( Builder $query ) use( $field, $value ) {
            $query->where_is_null( $field )->or_where_not_in( $field, $value );
        };

        return 'and' === $combinator ? $query->where( $not_query ) : $query->or_where( $field, $value );
    }

    protected function begins_with( Builder $query, string $field, string $combinator, $value ) {
        $field = $this->resolve_field_name( $field );

        return 'and' === $combinator ? $query->where_like( $field, "{$value}%" ) : $query->or_where_like( $field, "{$value}%" );  
    }

    protected function does_not_begin_with( Builder $query, string $field, string $combinator, $value ) {
        $field = $this->resolve_field_name( $field );

        return 'and' === $combinator ? $query->where_not_like( $field, "{$value}%" ) : $query->or_where_not_like( $field, "{$value}%" );  
    }

    protected function ends_with( Builder $query, string $field, string $combinator, $value ) {
        $field = $this->resolve_field_name( $field );

        return 'and' === $combinator ? $query->where_like( $field, "%{$value}" ) : $query->or_where_like( $field, "%{$value}" );  
    }

    protected function does_not_end_with( Builder $query, string $field, string $combinator, $value ) {
        $field = $this->resolve_field_name( $field );

        return 'and' === $combinator ? $query->where_not_like( $field, "%{$value}" ) : $query->or_where_not_like( $field, "%{$value}" );  
    }

    protected function null( Builder $query, string $field, string $combinator ) {
        $field = $this->resolve_field_name( $field );

        $not_query = function( Builder $query ) use( $field ) {
            $query->where( $field, '' )->or_where_null( $field );
        };

        if ( 'and' === $combinator ) {
            return $query->where( $not_query );
        }

        return $query->or_where( $not_query );
    }

    protected function not_null( Builder $query, string $field, string $combinator ) {
        $field = $this->resolve_field_name( $field );

        $not_query = function( Builder $query ) use( $field ) {
            $query->where( $field, '!=', '' )->or_where_not_null( $field );
        };

        if ( 'and' === $combinator ) {
            return $query->where( $not_query );
        }

        return $query->or_where( $not_query );
    }

    public function equal( Builder $query, string $field, string $combinator, $value ) {
        $field = $this->resolve_field_name( $field );

        return 'and' === $combinator ? $query->where( $field, $value ) : $query->or_where( $field, $value );
    }

    public function not_equal( Builder $query, string $field, string $combinator, $value ) {
        $field = $this->resolve_field_name( $field );

        return 'and' === $combinator ? $query->where( $field, '!=', $value ) : $query->or_where( $field, '!=', $value );
    }

    public function less_than( Builder $query, string $field, string $combinator, $value ) {
        $field = $this->resolve_field_name( $field );

        return 'and' === $combinator ? $query->where( $field, '<', $value ) : $query->or_where( $field, '<', $value );
    }

    public function greater_than( Builder $query, string $field, string $combinator, $value ) {
        $field = $this->resolve_field_name( $field );

        return 'and' === $combinator ? $query->where( $field, '>', $value ) : $query->or_where( $field, '>', $value );
    }

    public function between( Builder $query, string $field, string $combinator, $value ) {
        $field = $this->resolve_field_name( $field );

        return 'and' === $combinator ? $query->where_between( $field, $value ) : $query->or_where_between( $field, $value );
    }

    public function not_between( Builder $query, string $field, string $combinator, $value ) {
        $field = $this->resolve_field_name( $field );

        return 'and' === $combinator ? $query->where_not_between( $field, $value ) : $query->or_where_not_between( $field, $value );
    }

    protected function resolve_field_name( string $key ) {
        $keys = explode( '.', $key );

        if ( empty( $keys[1] ) ) {
            $aliases = [
                'tags'   => 'entity_tag.tag_id',
                'lists'  => 'entity_list.list_id',
                'status' => 'contact.status'
            ];

            if ( isset( $aliases[$key] ) ) {
                if ( in_array( $key, ['tags', 'lists'] ) ) {
                    $this->advanced_filter_joins[$key] = true;
                }
                return $aliases[$key];
            }
        } else if ( 'contact' === $keys[0] && in_array( $key, $this->get_contact_columns(), true ) ) {
            return $key;
        }

        // Translators: %s is the key that was not found.
        throw new Exception( sprintf( esc_html__( '%s not found', 'vendtics' ), esc_html( $key ) ), 404 );
    }

    protected function contact_search_query( Builder $query, string $search ) {
        return $query->where(
            function( $like_query ) use( $search ) {
                $columns = [
                    'contact.first_name',
                    'contact.last_name',
                    'contact.email',
                    'contact.phone_number',
                    'contact.company',
                    'contact.designation',
                    'contact.timezone',
                    'contact.address',
                    'contact.city',
                    'contact.state',
                    'contact.postal_code',
                    'contact.country',
                    'contact.status'
                ];

                foreach ( $columns as $column ) {
                    $like_query->or_where_like( $column, "%{$search}%" );
                }
            }
        );
    }

    protected function get_contact_columns() {
        return [
            'contact.first_name',
            'contact.last_name',
            'contact.email',
            'contact.phone_number',
            'contact.company',
            'contact.designation',
            'contact.date_of_birth',
            'contact.type',
            'contact.timezone',
            'contact.address',
            'contact.city',
            'contact.state',
            'contact.postal_code',
            'contact.country',
            'contact.status',
            'contact.created_at'
        ];
    }
}