<?php

namespace Blazemedia\Blackhole\Tools;

use Blazemedia\Blackhole\Types\PostItem;


class SimpleDB {

    protected static $instance = null;

    protected $db;

    public $table = 'blz_offers_to_remove_table';

    protected function __construct( private string $dbHost, private string $dbName, private string $dbUser, private string $dbPassword, private string $dbPrefix = 'wp_' , private string $offersPostType = 'post' ) {

        try {
            $this->db = new \PDO("mysql:host={$this->dbHost};dbname={$this->dbName};charset=UTF8", $this->dbUser, $this->dbPassword );

            if( !$this->table_exists() ) {

                $this->create_table();
            }

        } catch (\PDOException $e) {
            print "Error!: " . $e->getMessage() . "<br/>";
            die();
        }
    }


    function table_exists() : bool {

        return $this->db->query( "SHOW TABLES like '{$this->table}'" )->rowCount() > 0;
    }


    function create_table() {

        // SQL statement for creating new tables
        $statement =
	        "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,
                `post_status`         VARCHAR(32)  DEFAULT 'publish',

                PRIMARY KEY(`post_id`)
            );";

	    $this->db->exec($statement);
    }


    function setRedirected( int $postId, $redirected = 1 ) {

        $sql = "UPDATE $this->table SET redirected=? WHERE post_id =?";

        return $this->db->prepare($sql)->execute([ $redirected, $postId ]);
    }


    function setStatus( int $postId, $status = 'draft' ) {

        $sql = "UPDATE $this->table SET  post_status=? WHERE post_id =?";

        return $this->db->prepare($sql)->execute([ $status, $postId ]);
    }


    function exists( int $postId ) : bool {

        $exists_sql = "SELECT post_id from {$this->table} WHERE post_id = {$postId}";

        return $this->db->query( $exists_sql )->fetch() !== false;
    }


    function save( PostItem $post ) : bool {

        if( $this->exists( $post->ID ) ) {

            $sql = "UPDATE $this->table SET post_date_timestamp=?, post_query=?, users=? WHERE post_id =?";

        } else{

            $sql = "INSERT INTO $this->table ( `post_date_timestamp`, `post_query`, `users`, `post_id`) VALUES(?,?,?,?)";
        }

        return $this->db->prepare($sql)->execute([ $post->timestamp, $post->query, $post->users, $post->ID ]);
    }


    /**
     * Iterator function that execute action
     * for each iteration
     *
     * @param callable $callback
     * @param int $startId
     * @param int $endId
     * @return void
     */
    public function forEachQueryItem( string $sql, callable $callback, int $beforeTimestamp = 0 ) {

        $sql = str_replace( '{{ prefix }}', $this->dbPrefix, $sql );

        if( $beforeTimestamp > 0 ) {
            $beforeDate = date( 'Y-m-d', $beforeTimestamp );

            $sql .= " AND `post_date` < '{$beforeDate}'";
        }

        $stmt = $this->db->prepare($sql);
        $stmt->execute();

        foreach( $stmt as $post ) {

            $callback( $post  );
        }
    }



    public function forEachOfferPost( callable $callback, $beforeTimestamp ) {

        $this->forEachQueryItem(

            "SELECT * FROM {{ prefix }}posts
             WHERE post_type='{$this->offersPostType}'
             AND post_status='publish'
             AND ID IN (
                SELECT object_id AS ID
                FROM {{ prefix }}term_relationships
                WHERE term_taxonomy_id = (
                    SELECT term_taxonomy_id
                    FROM  {{ prefix }}terms NATURAL JOIN {{ prefix }}term_taxonomy
                    WHERE slug='offerte' AND taxonomy='post_tag'
                )
            )",

            $callback,
            $beforeTimestamp
        );
    }


    public function setWpPostStatus( $postId, $postStatus = 'publish' ) {

        switch( $postStatus ){

            case 'draft':

                $sql = "UPDATE {{ prefix }}posts SET post_status=? WHERE ID =?";
                break;

            case 'publish':

                $sql = "UPDATE {{ prefix }}posts SET post_type='blackhole', post_status=? WHERE ID =?";
                break;

            case 'blackhole':

                $sql = "UPDATE {{ prefix }}posts SET post_type=? WHERE ID =?";
                break;

            default:
                $sql = "SELECT 1";
        }


        $sql = str_replace('{{ prefix }}', $this->dbPrefix, $sql );

        return $this->db->prepare($sql)->execute([ $postStatus, $postId ]);
    }


    public function getPost( $id ) {

        return $this->db->query( "SELECT ID, post_date, post_content FROM {$this->dbPrefix}posts WHERE ID=$id" )->fetch();
    }


    public static function getInstance( string $dbHost, string $dbName, string $dbUser, string $dbPassword, $dbPrefix = 'wp_', $offersPostType = 'post' ){

        if( self::$instance == null ) {

           $c = __CLASS__;
           self::$instance = new $c( $dbHost, $dbName, $dbUser, $dbPassword, $dbPrefix, $offersPostType );
        }

        return self::$instance;
    }

}
