<?php

if( !class_exists( 'Post_SMTP_RAT_DB' ) ):
class Post_SMTP_RAT_DB {

    private $db = '';
    private $table_name = '';
    
    const OPTION = 'postman_rat';
    const TRANSIENT_EXPIRY_TIME = 'ps_rat_has_sent';

    /**
     * PostSMTP_DB constructor.
     * 
     * @since 1.0.0
     * @version 1.0.0
     */
    public function __construct() {

        global $wpdb;

        $this->db = $wpdb;
        $this->table_name = $this->db->prefix . 'post_smtp_tracking';

    }

    
    /**
     * Installs Table
     * 
     * @since 1.0.0
     * @version 1.0.0
     */
    public function install_table() {

        $charset_collate = $this->db->get_charset_collate();

        $sql = "CREATE TABLE IF NOT EXISTS $this->table_name (
            `id` bigint(20) NOT NULL AUTO_INCREMENT,
            `email_id` bigint(20) NOT NULL,
            `event_type` VARCHAR(255) NOT NULL,
            `time` VARCHAR(255) NOT NULL,
            PRIMARY KEY  (id)
        ) $charset_collate;";

        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        dbDelta( $sql );

    }


    /**
     * Insert data into the table
     * 
     * @param array $data
     * @since 1.0.0
     * @version 1.0.0
     */
    public function insert( $data ) {

        if( !class_exists( 'PostmanEmailQueryLog' ) ) {

            include_once POST_SMTP_PATH . '/Postman/Postman-Email-Log/PostmanEmailQueryLog.php';

        }

        $ps_query = new PostmanEmailQueryLog();

        $response = $this->db->get_var(
            $this->db->prepare(
                "SELECT id FROM $ps_query->table WHERE id = %d",
                $data['email_id']
            )
        );

        if( $response ) {

            $response = $this->db->update(
                $this->table_name,
                $data,
                array( 'email_id' => $data['email_id'] )
            );
            
            if( !$response ) {
    
                $response = $this->db->insert( 
                    $this->table_name, 
                    $data 
                );
    
    
            }

        }

        return $response;

    }

    public function get_table_name() {

        return $this->table_name;

    } 


    /**
     * Get option
     * 
     * @param string $key
     * @since 1.0.0
     * @version 1.0.0
     */
    public function get_option( $key = '' ) {

        $data = get_option( self::OPTION );

        if( $data ) {

            if( $key == '' ) {

                return $data;

            }
            if( array_key_exists( $key, $data ) ) {

                return $data[$key];

            }

        }

        return false;

    }


    /**
     * Get total sent emails count
     * 
     * @param int $id
     * @since 1.0.0
     * @version 1.0.0
     */
    public function get_total_sent_emails_count( $id ) {

        if( !class_exists( 'PostmanEmailQueryLog' ) ) {

            include_once POST_SMTP_PATH . '/Postman/Postman-Email-Log/PostmanEmailQueryLog.php';

        }

        $ps_query = new PostmanEmailQueryLog();

        $response = $this->db->get_var(
            $this->db->prepare(
                "SELECT COUNT(*) AS email_count
                FROM {$ps_query->table}
                WHERE original_to = (
                    SELECT original_to
                    FROM {$ps_query->table}
                    WHERE id = %d
                )",
                $id
            )
        );

        return $response ? $response : false;
        
    }



    /**
     * Get total email open count by id
     * 
     * @param int $id
     * @since 1.0.0
     * @version 1.0.0
     */
    public function get_total_open_count_by_id( $id ) {

        if( !class_exists( 'PostmanEmailQueryLog' ) ) {

            include_once POST_SMTP_PATH . '/Postman/Postman-Email-Log/PostmanEmailQueryLog.php';

        }

        $ps_query = new PostmanEmailQueryLog();

        $response = $this->db->get_row(
            $this->db->prepare(
                "SELECT ps.original_to, COUNT(*) AS opened_count
                FROM {$this->table_name} AS rat
                JOIN {$ps_query->table} AS ps ON rat.email_id = ps.id
                WHERE ps.original_to = ( 
                        SELECT original_to 
                        FROM {$ps_query->table} 
                        WHERE id = %d 
                    )
                AND rat.event_type = 'open-email'
                GROUP BY ps.original_to;",
                $id
            )
        );

        if( !$response ) {

            $response = $this->db->get_row(
                $this->db->prepare(
                    "SELECT ps.original_to
                    FROM {$ps_query->table} AS ps
                    WHERE ps.id = %d",
                    $id
                )
            );


        }

        return $response ? $response : false;

    }


    /**
     * Get total email open count by email
     * 
     * @param string $email
     * @since 1.0.0
     * @version 1.0.0
     */
    public function get_reporting_logs( $from = '', $to = '', $limit = '' ) {

        if( !class_exists( 'PostmanEmailQueryLog' ) ) {

            include_once POST_SMTP_PATH . '/Postman/Postman-Email-Log/PostmanEmailQueryLog.php';

        }

        $ps_query = new PostmanEmailQueryLog();

        $where = ( !empty( $from ) && !empty( $to ) ) ? "WHERE pl.`time` >= {$from} && pl.`time` <= {$to}" : '';

        $query = "
        SELECT 
        pl.original_subject AS subject,
        COUNT( pl.original_subject ) AS total, 
        SUM(
            CASE 
                WHEN pl.success = 1 
                     OR pl.success = '1' 
                     OR pl.success LIKE 'Sent ( ** Fallback ** )%%' 
                THEN 1 
                ELSE 0 
            END
        ) AS sent,
        SUM(
            CASE 
                WHEN pl.success = 1 
                     OR pl.success = '1' 
                     OR pl.success LIKE 'Sent ( ** Fallback ** )%%' 
                THEN 0 
                ELSE 1 
            END
        ) AS failed,
        COUNT( rat.id ) AS opened,
        concat(
            round(
                ( 
                    COUNT( rat.id ) / NULLIF(
                        SUM(
                            CASE 
                                WHEN pl.success = 1 
                                     OR pl.success = '1' 
                                     OR pl.success LIKE 'Sent ( ** Fallback ** )%%' 
                                THEN 1 
                                ELSE 0 
                            END
                        ), 
                        0
                    ) * 100 
                ),
                2
            ),
            '%'
        ) AS opened_ratio
        FROM {$ps_query->table} AS pl 
        LEFT JOIN {$this->table_name} AS rat ON rat.email_id = pl.id
        {$where}
        GROUP BY pl.original_subject";

        $query .= !empty( $limit ) ? " LIMIT {$limit}" : '';

        $response = $this->db->get_results( $query );

        return $response ? $response : false;


    }


    /**
     * Get reporting graph data
     * 
     * @since 1.0.0
     * @version 1.0.0
     */
    public function get_reporting_graph_data( $from = '', $to = '' ) {

        if( !class_exists( 'PostmanEmailQueryLog' ) ) {

            include_once POST_SMTP_PATH . '/Postman/Postman-Email-Log/PostmanEmailQueryLog.php';

        }

        $ps_query = new PostmanEmailQueryLog();

        $where = ( !empty( $from ) && !empty( $to ) ) ? "WHERE pl.`time` >= {$from} && pl.`time` <= {$to}" : '';

        $response = $this->db->get_results(
            "SELECT 
            DATE_FORMAT( FROM_UNIXTIME( pl.`time` ), '%d-%m-%Y' ) as date,
            COUNT( pl.original_subject ) AS total, 
            SUM(
                CASE 
                    WHEN pl.success = 1 
                         OR pl.success = '1' 
                         OR pl.success LIKE 'Sent ( ** Fallback ** )%%' 
                    THEN 1 
                    ELSE 0 
                END
            ) AS sent,
            SUM(
                CASE 
                    WHEN pl.success = 1 
                         OR pl.success = '1' 
                         OR pl.success LIKE 'Sent ( ** Fallback ** )%%' 
                    THEN 0 
                    ELSE 1 
                END
            ) AS failed,
            COUNT( rat.id ) AS opened
            FROM {$ps_query->table} AS pl 
            LEFT JOIN {$this->table_name} AS rat ON rat.email_id = pl.id
            {$where}
            GROUP BY DATE_FORMAT( FROM_UNIXTIME( pl.`time` ), '%d-%m-%Y' )"
        );

        return $response ? $response : false;

    }


    /**
     * Delete logs
     * 
     * @param array $ids
     * @since 1.0.0
     * @version 1.0.0
     */
    public function delete_logs( $ids ) {

        $ids = implode( ',', $ids );
        $ids = $ids == -1 ? '' : "WHERE email_id IN ({$ids});";

        return $this->db->query(
            "DELETE FROM `{$this->table_name}` {$ids}"
        );

    }

}
endif;