<?php
namespace Blazemedia\Blackhole\Tools;

use Blazemedia\Blackhole\Types\PostItem;

class SimpleDB {

    protected static $instance = null;

    protected $db;

    private $blackhole_settings_options;

    public $table = 'blz_offers_to_remove_table';

    protected function __construct( private string $dbPrefix = 'wp_'  ) {
        
        Global $wpdb;
        $this->db = $wpdb;  // Utilizzo di wpdb per l'interazione con il database
        $this->table = $this->db->prefix . $this->table;
        
        if( !$this->table_exists() )  $this->create_table();

        $this->blackhole_settings_options = get_option('blackhole_settings_option');
    }

    function table_exists() : bool {
        $result = $this->db->get_results("SHOW TABLES LIKE '{$this->table}'");
        return (count($result) > 0) ? true : false;
    }

    function create_table() {
        $charset_collate = $this->db->get_charset_collate();
        $sql = "CREATE TABLE  $this->table (
            `post_id` BIGINT NOT NULL,
            `post_date_timestamp` BIGINT NOT NULL,
            `post_query` VARCHAR(2048) NOT NULL,
            `users` INT(10) DEFAULT 0,
            `redirected` INT DEFAULT 0,
            `original_post_type` VARCHAR(64) DEFAULT 'post',
            `post_status` VARCHAR(32) DEFAULT 'publish',
            PRIMARY KEY (`post_id`)
        ) $charset_collate;";
        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        $this->db->get_results($sql);
    }

    function setRedirected( int $postId, $redirected = 1 ) {
        return $this->db->update(
            $this->table,
            [ 'redirected' => $redirected ],
            [ 'post_id' => $postId ],
            [ '%d' ],
            [ '%d' ]
        );
    }

    function setStatus( int $postId, $status = 'publish' ) {
        
        if ( $status == 'publish' ) {
            return $this->db->delete(
                $this->table,
                [ 'post_id' => $postId ],
                [ '%d' ]
            );
        } else {
            return $this->db->update(
                $this->table,
                [ 'post_status' => $status ],
                [ 'post_id' => $postId ],
                [ '%s' ],
                [ '%d' ]
            );
        }
    }

    function exists( int $postId ) : bool {
        $result = $this->db->get_results("SELECT post_id FROM ".$this->table." WHERE post_id = " . $postId );
        return (count($result) > 0) ? true : false;
    }

    public function countQueueByStatus( string $status = 'publish', int $limit = 0 ) : int {
        $status = sanitize_text_field( $status );
        if ( $limit > 0 ) {
            $count_sql = "SELECT COUNT(*) FROM (
                SELECT post_id FROM {$this->table}
                WHERE post_status = '{$status}'
                ORDER BY post_date_timestamp ASC
                LIMIT {$limit}
            ) AS limited_rows";
            return (int) $this->db->get_var( $count_sql );
        }

        $count_sql = "SELECT COUNT(*) FROM {$this->table} WHERE post_status = '{$status}'";
        return (int) $this->db->get_var( $count_sql );
    }

    public function restoreFromBlackhole( int $postId ) : bool {
        $postId = absint( $postId );
        if ( $postId <= 0 ) {
            return false;
        }

        $row = $this->db->get_row(
            $this->db->prepare(
                "SELECT post_id, original_post_type FROM {$this->table} WHERE post_id = %d LIMIT 1",
                $postId
            )
        );

        if ( !$row ) {
            return false;
        }

        $restored_wp = $this->setWpPostStatus( (int) $row->post_id, (string) $row->original_post_type, 'publish' );
        $restored_db = $this->setStatus( (int) $row->post_id, 'publish' );

        return $restored_wp !== false && $restored_db !== false;
    }

    function save( PostItem $post ) : bool {
     
        if(! $this->exists($post->ID) ) {
   
            $result = $this->db->insert(
                $this->table,
                [
                    'original_post_type' => $post->post_type,
                    'post_date_timestamp' => $post->timestamp,
                    'post_query' => $post->query,
                    'users' => $post->users,
                    'post_id' => $post->ID
                ],
                [ '%s', '%d', '%s', '%d', '%d' ]
            );
            return $result !== false;
        }

        return false;
    }

    public function forEachQueryItem( string $sql, callable $callback, int $beforeTimestamp = 0 ) {
        if( $beforeTimestamp > 0 ) {
            $beforeDate = date( 'Y-m-d', $beforeTimestamp );
            $sql .= " AND `post_date` < '{$beforeDate}'";
            $sql .= " ORDER BY post_date ASC LIMIT 0,2000";
        }

        $results = $this->db->get_results( $sql );

        foreach( $results as $post ) {
            $callback( $post );
        }
    }

    public function forEachOfferPost( callable $callback, $beforeTimestamp ) : int {
        $where_clauses = $this->buildOfferWhereClauses();
        $beforeDate = date( 'Y-m-d', (int) $beforeTimestamp );
        $where_clauses[] = "post_date < '{$beforeDate}'";

        return $this->runOfferQuery( $callback, $where_clauses );
    }

    public function forEachOfferPostInRange( callable $callback, int $startTimestamp, int $endTimestamp ) : int {
        $where_clauses = $this->buildOfferWhereClauses();
        $startDate = date( 'Y-m-d', $startTimestamp );
        $endDate = date( 'Y-m-d', $endTimestamp );

        $where_clauses[] = "post_date >= '{$startDate}'";
        $where_clauses[] = "post_date < '{$endDate}'";

        return $this->runOfferQuery( $callback, $where_clauses );
    }

    public function getOldestOfferPostTimestampBefore( int $beforeTimestamp ) : int {
        $where_clauses = $this->buildOfferWhereClauses();
        $beforeDate = date( 'Y-m-d', $beforeTimestamp );
        $where_clauses[] = "post_date < '{$beforeDate}'";

        $sql = "SELECT MIN(UNIX_TIMESTAMP(post_date)) AS oldest_timestamp
                FROM {$this->db->prefix}posts
                WHERE " . implode( ' AND ', $where_clauses );

        $oldest_timestamp = (int) $this->db->get_var( $sql );

        return $oldest_timestamp > 0 ? $oldest_timestamp : 0;
    }

    public function getOldestUnprocessedOfferPostTimestampBefore( int $beforeTimestamp ) : int {
        $where_clauses = $this->buildOfferWhereClauses();
        $beforeDate = date( 'Y-m-d', $beforeTimestamp );
        $where_clauses[] = "post_date < '{$beforeDate}'";
        $where_clauses[] = "ID NOT IN (SELECT post_id FROM {$this->table})";

        $sql = "SELECT MIN(UNIX_TIMESTAMP(post_date)) AS oldest_timestamp
                FROM {$this->db->prefix}posts
                WHERE " . implode( ' AND ', $where_clauses );

        $oldest_timestamp = (int) $this->db->get_var( $sql );

        return $oldest_timestamp > 0 ? $oldest_timestamp : 0;
    }

    private function runOfferQuery( callable $callback, array $where_clauses ) : int {
        $sql = "SELECT * FROM {$this->db->prefix}posts
                WHERE " . implode( ' AND ', $where_clauses ) . "
                ORDER BY post_date ASC
                LIMIT 0,2000";

        $results = $this->db->get_results( $sql );
        foreach ( $results as $post ) {
            $callback( $post );
        }

        return is_array( $results ) ? count( $results ) : 0;
    }

    private function buildOfferWhereClauses() {
        $selected_post_types = $this->get_selected_post_types();
        $included_terms = $this->get_selected_taxonomy_terms( 'included_taxonomy_terms', 'taxonomy_terms' );
        $excluded_terms = $this->get_selected_taxonomy_terms( 'excluded_taxonomy_terms' );

        $where_clauses = array(
            "post_type != 'blackhole'",
            "post_status = 'publish'",
            "post_name NOT LIKE '%-gd'",
        );

        if ( !empty( $selected_post_types ) ) {
            $quoted_post_types = array_map(
                function( $post_type ) { return "'" . esc_sql( $post_type ) . "'"; },
                $selected_post_types
            );

            $where_clauses[] = 'post_type IN (' . implode( ',', $quoted_post_types ) . ')';
        }

        $included_subquery = $this->build_object_id_subquery_for_taxonomy_terms( $included_terms );
        if ( !empty( $included_subquery ) ) {
            $where_clauses[] = "ID IN ( {$included_subquery} )";
        }

        $excluded_subquery = $this->build_object_id_subquery_for_taxonomy_terms( $excluded_terms );
        if ( !empty( $excluded_subquery ) ) {
            $where_clauses[] = "ID NOT IN ( {$excluded_subquery} )";
        }

        return $where_clauses;
    }

    private function get_selected_post_types() {
        $selected_post_types = $this->blackhole_settings_options['post_types'] ?? array();
        $selected_post_types = array_values( array_filter( array_map( 'sanitize_key', (array) $selected_post_types ) ) );

        if ( !empty( $selected_post_types ) ) {
            return $selected_post_types;
        }

        $public_post_types = get_post_types( array( 'public' => true ), 'names' );
        $public_post_types = array_values( array_filter( $public_post_types, function ( $post_type ) {
            return $post_type !== 'blackhole';
        }));

        return array_values( array_filter( array_map( 'sanitize_key', $public_post_types ) ) );
    }

    private function get_selected_taxonomy_terms( string $option_key, string $legacy_key = '' ) {
        $taxonomy_terms = $this->blackhole_settings_options[ $option_key ] ?? null;

        if ( $taxonomy_terms === null && !empty( $legacy_key ) ) {
            $taxonomy_terms = $this->blackhole_settings_options[ $legacy_key ] ?? array();
        }

        $sanitized_terms = array();
        foreach ( (array) $taxonomy_terms as $item ) {
            $taxonomy = isset( $item['taxonomy'] ) ? sanitize_key( $item['taxonomy'] ) : '';
            $term = isset( $item['term'] ) ? sanitize_title( $item['term'] ) : '';

            if ( empty( $taxonomy ) || empty( $term ) ) {
                continue;
            }

            $sanitized_terms[] = array(
                'taxonomy' => $taxonomy,
                'term' => $term,
            );
        }

        return $sanitized_terms;
    }

    private function build_object_id_subquery_for_taxonomy_terms( array $taxonomy_terms ) {
        if ( empty( $taxonomy_terms ) ) {
            return '';
        }

        $conditions = array();
        foreach ( $taxonomy_terms as $taxonomy_term ) {
            $taxonomy = esc_sql( $taxonomy_term['taxonomy'] );
            $term = esc_sql( $taxonomy_term['term'] );
            $conditions[] = "(tax.taxonomy = '{$taxonomy}' AND terms.slug = '{$term}')";
        }

        if ( empty( $conditions ) ) {
            return '';
        }

        return "SELECT DISTINCT rel.object_id
                FROM {$this->db->prefix}term_relationships rel
                INNER JOIN {$this->db->prefix}term_taxonomy tax ON tax.term_taxonomy_id = rel.term_taxonomy_id
                INNER JOIN {$this->db->prefix}terms terms ON terms.term_id = tax.term_id
                WHERE " . implode( ' OR ', $conditions );
    }

    public function setWpPostStatus( $postId, $post_type = '', $postStatus = 'publish' ) {
        if ( $postStatus == 'blackhole' ) {
            return $this->db->update(
                $this->db->prefix . "posts",
                [ 'post_type' => 'blackhole' ],
                [ 'ID' => $postId ],
                [ '%s' ],
                [ '%d' ]
            );
        } else {
            return $this->db->update(
                $this->db->prefix . "posts",
                [ 'post_type' => $post_type ],
                [ 'ID' => $postId ],
                [ '%s' ],
                [ '%d' ]
            );
        }
    }

    public function isBlackholedSlug( string $slug ) : bool {
        $slug = sanitize_title( $slug );
        if ( empty( $slug ) ) {
            return false;
        }

        $sql = $this->db->prepare(
            "SELECT ID
             FROM {$this->db->prefix}posts
             WHERE post_name = %s
               AND post_type = 'blackhole'
               AND post_status = 'publish'
             LIMIT 1",
            $slug
        );

        return (int) $this->db->get_var( $sql ) > 0;
    }

    public function getPost( $id ) {
        return $this->db->get_row( $this->db->prepare( "SELECT ID, post_date, post_content FROM {$this->db->prefix}posts WHERE ID = %d", $id ) );
    }

    public static function getInstance( string $dbPrefix = 'wp_' ){
  
        if( self::$instance == null ) {
            self::$instance = new self( $dbPrefix );
        }

        return self::$instance;
    }
}