<?php
if ( ! defined( 'ABSPATH' ) ) exit;

class Net360_360CH_Leads {
    public static function insert( $data ) {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Writes to the plugin's own custom leads table.
        return $wpdb->insert(
            Net360_360CH_Database::table_name(),
            array(
                'name'    => $data['name'],
                'email'   => $data['email'],
                'agent'   => $data['agent'],
                'message' => $data['message'],
                'page'    => $data['page'],
                'country' => $data['country'],
            ),
            array( '%s', '%s', '%s', '%s', '%s', '%s' )
        );
    }

    public static function count() {
        global $wpdb;
        $table = self::table_identifier();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads the plugin's own custom leads table; the count must reflect current data.
        return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) );
    }

    public static function count_today() {
        global $wpdb;
        $table = self::table_identifier();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads the plugin's own custom leads table; the count must reflect current data.
        return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE date >= %s', $table, current_time( 'Y-m-d 00:00:00' ) ) );
    }

    public static function top_agent() {
        global $wpdb;
        $table = self::table_identifier();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads the plugin's own custom leads table; statistics must reflect current data.
        return (string) $wpdb->get_var( $wpdb->prepare( 'SELECT agent FROM %i WHERE agent <> %s GROUP BY agent ORDER BY COUNT(*) DESC LIMIT 1', $table, '' ) );
    }

    public static function recent( $limit = 10, $offset = 0 ) {
        global $wpdb;
        $table = self::table_identifier();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads the plugin's own custom leads table; pagination is intentionally live.
        return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i ORDER BY id DESC LIMIT %d OFFSET %d', $table, absint( $limit ), absint( $offset ) ) );
    }


    public static function count_between( $from = '', $to = '' ) {
        global $wpdb;
        $table = self::table_identifier();
        if ( $from && $to ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads the plugin's own custom leads table.
            return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE date >= %s AND date <= %s', $table, $from . ' 00:00:00', $to . ' 23:59:59' ) );
        }
        if ( $from ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads the plugin's own custom leads table.
            return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE date >= %s', $table, $from . ' 00:00:00' ) );
        }
        if ( $to ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads the plugin's own custom leads table.
            return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE date <= %s', $table, $to . ' 23:59:59' ) );
        }
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads the plugin's own custom leads table.
        return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) );
    }

    public static function recent_between( $limit = 10, $offset = 0, $from = '', $to = '' ) {
        global $wpdb;
        $table  = self::table_identifier();
        $limit  = absint( $limit );
        $offset = absint( $offset );
        if ( $from && $to ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads the plugin's own custom leads table; pagination is intentionally live.
            return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE date >= %s AND date <= %s ORDER BY id DESC LIMIT %d OFFSET %d', $table, $from . ' 00:00:00', $to . ' 23:59:59', $limit, $offset ) );
        }
        if ( $from ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads the plugin's own custom leads table; pagination is intentionally live.
            return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE date >= %s ORDER BY id DESC LIMIT %d OFFSET %d', $table, $from . ' 00:00:00', $limit, $offset ) );
        }
        if ( $to ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads the plugin's own custom leads table; pagination is intentionally live.
            return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE date <= %s ORDER BY id DESC LIMIT %d OFFSET %d', $table, $to . ' 23:59:59', $limit, $offset ) );
        }
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads the plugin's own custom leads table; pagination is intentionally live.
        return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i ORDER BY id DESC LIMIT %d OFFSET %d', $table, $limit, $offset ) );
    }

    public static function export_between( $from = '', $to = '' ) {
        global $wpdb;
        $table = self::table_identifier();
        if ( $from && $to ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Exports the plugin's own custom leads table.
            return $wpdb->get_results( $wpdb->prepare( 'SELECT id, name, email, agent, message, page, country, date FROM %i WHERE date >= %s AND date <= %s ORDER BY id DESC', $table, $from . ' 00:00:00', $to . ' 23:59:59' ) );
        }
        if ( $from ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Exports the plugin's own custom leads table.
            return $wpdb->get_results( $wpdb->prepare( 'SELECT id, name, email, agent, message, page, country, date FROM %i WHERE date >= %s ORDER BY id DESC', $table, $from . ' 00:00:00' ) );
        }
        if ( $to ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Exports the plugin's own custom leads table.
            return $wpdb->get_results( $wpdb->prepare( 'SELECT id, name, email, agent, message, page, country, date FROM %i WHERE date <= %s ORDER BY id DESC', $table, $to . ' 23:59:59' ) );
        }
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Exports the plugin's own custom leads table.
        return $wpdb->get_results( $wpdb->prepare( 'SELECT id, name, email, agent, message, page, country, date FROM %i ORDER BY id DESC', $table ) );
    }

    private static function table_identifier() {
        return Net360_360CH_Database::table_name();
    }
}