<?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

        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;
    }

    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 id ASC LIMIT 0,2000";
        }

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

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

    public function forEachOfferPost( callable $callback, $beforeTimestamp ) {
        $post_types = implode("','", $this->blackhole_settings_options['post_types']);
        $taxonomy_terms = $this->blackhole_settings_options['taxonomy_terms'];
    
        $taxonomy_term_conditions = [];
        foreach ($taxonomy_terms as $taxonomy_term) {
            $taxonomy = $taxonomy_term['taxonomy'];
            $term = $taxonomy_term['term'];
            $taxonomy_term_conditions[] = "
                term_taxonomy_id = (
                    SELECT term_taxonomy_id
                    FROM  ".$this->db->prefix."terms NATURAL JOIN ".$this->db->prefix."term_taxonomy
                    WHERE slug='$term' AND taxonomy='$taxonomy'
                )
            ";
        }
        
        $taxonomy_term_sql = implode(' OR ', $taxonomy_term_conditions);

        $this->forEachQueryItem(
            "SELECT * FROM ".$this->db->prefix."posts
             WHERE post_type IN ('$post_types')
             AND post_type != 'blackhole'
             AND post_status='publish'
             AND ID IN (
                SELECT object_id AS ID
                FROM ".$this->db->prefix."term_relationships
                WHERE ($taxonomy_term_sql)
            )",
            $callback,
            $beforeTimestamp
        );
        
    }

    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 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;
    }
}