<?php
// Exit if accessed directly
if (!defined('ABSPATH')) {
    exit;
}

class B2BKing_Group_Rules_Log {

    private static $instance = null;
    private $table_name;

    public static function get_instance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    private function __construct() {
        global $wpdb;
        $this->table_name = $wpdb->prefix . 'b2bking_group_rules_log';

        // Only run CREATE TABLE once per install. Avoids calling dbDelta on every
        // request, which conflicts with DB caching plugins (e.g. W3 Total Cache).
        if (!get_option('b2bking_grlog_table_created')) {
            // Give up after 3 failed attempts to avoid retrying forever.
            $attempts = intval(get_option('b2bking_grlog_create_attempts', 0));
            if ($attempts < 3) {
                $charset_collate = $wpdb->get_charset_collate();
                $wpdb->query("CREATE TABLE IF NOT EXISTS {$this->table_name} (
                    id bigint(20) NOT NULL AUTO_INCREMENT,
                    user_id bigint(20) NOT NULL,
                    rule_id bigint(20) NOT NULL,
                    old_group_id bigint(20) DEFAULT NULL,
                    new_group_id bigint(20) NOT NULL,
                    date_created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (id),
                    KEY user_id (user_id),
                    KEY rule_id (rule_id),
                    KEY old_group_id (old_group_id),
                    KEY new_group_id (new_group_id),
                    KEY date_created (date_created)
                ) $charset_collate");

                if (empty($wpdb->last_error)) {
                    update_option('b2bking_grlog_table_created', true, false);
                    delete_option('b2bking_grlog_create_attempts');
                } else {
                    // Log the error so it can be diagnosed, then increment attempt count.
                    $error_message = 'B2BKing group rules log table creation failed (attempt ' . ($attempts + 1) . '/3): ' . $wpdb->last_error;
                    error_log($error_message);
                    update_option('b2bking_grlog_create_error', $error_message, false);
                    update_option('b2bking_grlog_create_attempts', $attempts + 1, false);
                }
            }
        }
    }

    /**
     * Add log entry
     */
    public function add_log($user_id, $rule_id, $old_group_id, $new_group_id) {
        global $wpdb;

        $result = $wpdb->insert(
            $this->table_name,
            array(
                'user_id'      => absint($user_id),
                'rule_id'      => absint($rule_id),
                'old_group_id' => $old_group_id ? absint($old_group_id) : null,
                'new_group_id' => absint($new_group_id),
                'date_created' => current_time('mysql'),
            ),
            array('%d', '%d', '%d', '%d', '%s')
        );

        return $result !== false ? $wpdb->insert_id : false;
    }

    /**
     * Get log entries with pagination
     */
    public function get_logs($page = 1, $per_page = 20, $user_id = null, $rule_id = null) {
        global $wpdb;

        if ($page < 1) $page = 1;
        if ($per_page < 1) $per_page = 1;
        $offset = ($page - 1) * $per_page;

        $where_conditions = array();
        $where_values     = array();

        if ($user_id) {
            $where_conditions[] = 'l.user_id = %d';
            $where_values[]     = absint($user_id);
        }

        if ($rule_id) {
            $where_conditions[] = 'l.rule_id = %d';
            $where_values[]     = absint($rule_id);
        }

        $where_clause = !empty($where_conditions) ? 'WHERE ' . implode(' AND ', $where_conditions) : '';

        $count_sql = "SELECT COUNT(*) FROM {$this->table_name} l $where_clause";
        if (!empty($where_values)) {
            $count_sql = $wpdb->prepare($count_sql, $where_values);
        }
        $total = $wpdb->get_var($count_sql);

        $sql = "SELECT l.*, u.display_name, u.user_email, r.post_title as rule_name,
                       og.post_title as old_group_name, ng.post_title as new_group_name
                FROM {$this->table_name} l
                LEFT JOIN {$wpdb->users} u ON l.user_id = u.ID
                LEFT JOIN {$wpdb->posts} r ON l.rule_id = r.ID
                LEFT JOIN {$wpdb->posts} og ON l.old_group_id = og.ID
                LEFT JOIN {$wpdb->posts} ng ON l.new_group_id = ng.ID
                $where_clause
                ORDER BY l.date_created DESC
                LIMIT %d OFFSET %d";

        $query_values = array_merge($where_values, array($per_page, $offset));
        $logs = $wpdb->get_results($wpdb->prepare($sql, $query_values));

        return array(
            'logs'  => $logs ?: array(),
            'total' => intval($total),
        );
    }

    /**
     * Search logs
     */
    public function search_logs($search_term, $page = 1, $per_page = 20) {
        if (empty($search_term)) {
            return array('logs' => array(), 'total' => 0);
        }

        global $wpdb;

        if ($page < 1) $page = 1;
        if ($per_page < 1) $per_page = 1;
        $offset = ($page - 1) * $per_page;
        $like   = '%' . $wpdb->esc_like($search_term) . '%';

        $count_sql = "SELECT COUNT(*) FROM {$this->table_name} l
                      LEFT JOIN {$wpdb->users} u ON l.user_id = u.ID
                      LEFT JOIN {$wpdb->posts} r ON l.rule_id = r.ID
                      LEFT JOIN {$wpdb->posts} og ON l.old_group_id = og.ID
                      LEFT JOIN {$wpdb->posts} ng ON l.new_group_id = ng.ID
                      WHERE u.display_name LIKE %s OR u.user_email LIKE %s OR r.post_title LIKE %s
                         OR og.post_title LIKE %s OR ng.post_title LIKE %s";
        $total = $wpdb->get_var($wpdb->prepare($count_sql, $like, $like, $like, $like, $like));

        $sql = "SELECT l.*, u.display_name, u.user_email, r.post_title as rule_name,
                       og.post_title as old_group_name, ng.post_title as new_group_name
                FROM {$this->table_name} l
                LEFT JOIN {$wpdb->users} u ON l.user_id = u.ID
                LEFT JOIN {$wpdb->posts} r ON l.rule_id = r.ID
                LEFT JOIN {$wpdb->posts} og ON l.old_group_id = og.ID
                LEFT JOIN {$wpdb->posts} ng ON l.new_group_id = ng.ID
                WHERE u.display_name LIKE %s OR u.user_email LIKE %s OR r.post_title LIKE %s
                   OR og.post_title LIKE %s OR ng.post_title LIKE %s
                ORDER BY l.date_created DESC
                LIMIT %d OFFSET %d";

        $logs = $wpdb->get_results($wpdb->prepare($sql, $like, $like, $like, $like, $like, $per_page, $offset));

        return array(
            'logs'  => $logs ?: array(),
            'total' => intval($total),
        );
    }

    /**
     * Get user's log history
     */
    public function get_user_logs($user_id, $limit = 10) {
        global $wpdb;

        if ($limit < 1) $limit = 1;

        $sql = "SELECT l.*, r.post_title as rule_name,
                       og.post_title as old_group_name, ng.post_title as new_group_name
                FROM {$this->table_name} l
                LEFT JOIN {$wpdb->posts} r ON l.rule_id = r.ID
                LEFT JOIN {$wpdb->posts} og ON l.old_group_id = og.ID
                LEFT JOIN {$wpdb->posts} ng ON l.new_group_id = ng.ID
                WHERE l.user_id = %d
                ORDER BY l.date_created DESC
                LIMIT %d";

        return $wpdb->get_results($wpdb->prepare($sql, absint($user_id), $limit)) ?: array();
    }

    /**
     * Get rule's log history
     */
    public function get_rule_logs($rule_id, $limit = 10) {
        global $wpdb;

        if ($limit < 1) $limit = 1;

        $sql = "SELECT l.*, u.display_name, u.user_email,
                       og.post_title as old_group_name, ng.post_title as new_group_name
                FROM {$this->table_name} l
                LEFT JOIN {$wpdb->users} u ON l.user_id = u.ID
                LEFT JOIN {$wpdb->posts} og ON l.old_group_id = og.ID
                LEFT JOIN {$wpdb->posts} ng ON l.new_group_id = ng.ID
                WHERE l.rule_id = %d
                ORDER BY l.date_created DESC
                LIMIT %d";

        return $wpdb->get_results($wpdb->prepare($sql, absint($rule_id), $limit)) ?: array();
    }

    /**
     * Delete old logs (cleanup)
     */
    public function cleanup_old_logs($days = 365) {
        global $wpdb;

        if ($days < 1) $days = 1;
        // Use current_time('timestamp') so the cutoff respects the WordPress
        // timezone, consistent with how add_log() writes via current_time('mysql').
        $cutoff_date = date('Y-m-d H:i:s', current_time('timestamp') - $days * DAY_IN_SECONDS);

        return $wpdb->query($wpdb->prepare(
            "DELETE FROM {$this->table_name} WHERE date_created < %s",
            $cutoff_date
        ));
    }

    /**
     * Get table stats
     */
    public function get_stats() {
        global $wpdb;

        // Single query instead of 5 separate ones.
        $row = $wpdb->get_row("
            SELECT COUNT(*) as total_entries,
                   COUNT(DISTINCT user_id) as unique_users,
                   COUNT(DISTINCT rule_id) as unique_rules,
                   MIN(date_created) as oldest_entry,
                   MAX(date_created) as newest_entry
            FROM {$this->table_name}
        ", ARRAY_A);

        return $row ?: array();
    }

    /**
     * Drop table (for uninstall)
     */
    public function drop_table() {
        global $wpdb;
        delete_option('b2bking_grlog_table_created');
        delete_option('b2bking_grlog_create_attempts');
        delete_option('b2bking_grlog_create_error');
        return $wpdb->query("DROP TABLE IF EXISTS {$this->table_name}");
    }
}

// Initialize the log system
B2BKing_Group_Rules_Log::get_instance();
