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

class BotBlocker_Storage {
    public static function init() {
        add_action('bot_blocker_daily_cleanup', [__CLASS__, 'cleanup']);
        if (!wp_next_scheduled('bot_blocker_daily_cleanup')) {
            wp_schedule_event(time() + HOUR_IN_SECONDS, 'daily', 'bot_blocker_daily_cleanup');
        }
    }

    public static function table() {
        global $wpdb;
        return $wpdb->prefix . 'bot_blocker_events';
    }

    public static function lock_table() {
        global $wpdb;
        return $wpdb->prefix . 'bot_blocker_locks';
    }

    public static function activate() {
        global $wpdb;

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $charset = $wpdb->get_charset_collate();
        $events = self::table();
        $locks = self::lock_table();

        dbDelta("CREATE TABLE $events (
            id bigint unsigned NOT NULL AUTO_INCREMENT,
            occurred_at datetime NOT NULL,
            ip varchar(64) NOT NULL,
            ip_hash varchar(64) NOT NULL,
            masked_ip varchar(64) NOT NULL,
            user_agent text NOT NULL,
            bot_name varchar(191) NOT NULL,
            category varchar(64) NOT NULL,
            claimed_identity varchar(191) NOT NULL,
            verification_state varchar(32) NOT NULL,
            path text NOT NULL,
            query_string text NOT NULL,
            referrer text NOT NULL,
            action_taken varchar(32) NOT NULL,
            matched_rule varchar(191) NOT NULL,
            country varchar(191) NOT NULL,
            region varchar(191) NOT NULL,
            city varchar(191) NOT NULL,
            latitude decimal(10,6) NULL,
            longitude decimal(10,6) NULL,
            geo_source varchar(64) NOT NULL,
            burst_count int unsigned NOT NULL DEFAULT 1,
            PRIMARY KEY (id),
            KEY occurred_at (occurred_at),
            KEY ip_hash (ip_hash),
            KEY bot_name (bot_name),
            KEY category (category),
            KEY verification_state (verification_state),
            KEY action_taken (action_taken)
        ) $charset;");

        dbDelta("CREATE TABLE $locks (
            id bigint unsigned NOT NULL AUTO_INCREMENT,
            lock_key varchar(191) NOT NULL,
            reason varchar(191) NOT NULL,
            expires_at datetime NOT NULL,
            created_at datetime NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY lock_key (lock_key),
            KEY expires_at (expires_at)
        ) $charset;");

        $defaults = BotBlocker_Settings::defaults();
        $existing = get_option(BotBlocker_Settings::OPTION, []);
        update_option(BotBlocker_Settings::OPTION, BotBlocker_Settings::sanitize_settings(wp_parse_args(is_array($existing) ? $existing : [], $defaults)));
    }

    public static function deactivate() {
        wp_clear_scheduled_hook('bot_blocker_daily_cleanup');
        wp_clear_scheduled_hook('bot_blocker_geo_resolve');
        wp_clear_scheduled_hook('bot_blocker_verify_identity');
    }

    public static function uninstall() {
        global $wpdb;

        self::deactivate();

        $wpdb->query('DROP TABLE IF EXISTS ' . self::table());
        $wpdb->query('DROP TABLE IF EXISTS ' . self::lock_table());
        delete_option(BotBlocker_Settings::OPTION);
        // License and trial options
        delete_site_option('sera_bot_blocker_license_key');
        delete_site_option('sera_bot_blocker_license_data');
        delete_site_option('sera_bot_blocker_trial_start');
        delete_site_option('sera_bot_blocker_trial_remote');
        delete_site_option('bot_blocker_network_license_key');
        // Pro cron hooks
        wp_clear_scheduled_hook('bot_blocker_send_digest');
        wp_clear_scheduled_hook('sera_bot_blocker_license_recheck');

        $like_patterns = [
            $wpdb->esc_like('_transient_bot_blocker_') . '%',
            $wpdb->esc_like('_transient_timeout_bot_blocker_') . '%',
            $wpdb->esc_like('_site_transient_bot_blocker_') . '%',
            $wpdb->esc_like('_site_transient_timeout_bot_blocker_') . '%',
        ];

        foreach ($like_patterns as $pattern) {
            $wpdb->query($wpdb->prepare("DELETE FROM {$wpdb->options} WHERE option_name LIKE %s", $pattern));
            if (is_multisite()) {
                $wpdb->query($wpdb->prepare("DELETE FROM {$wpdb->sitemeta} WHERE meta_key LIKE %s", $pattern));
            }
        }
    }

    public static function mask_ip($ip) {
        if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4)) {
            $parts = explode('.', $ip);
            $parts[3] = '0';
            return implode('.', $parts);
        }

        if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV6)) {
            $parts = explode(':', $ip);
            return implode(':', array_slice($parts, 0, 4)) . '::';
        }

        return $ip;
    }

    public static function hash_ip($ip) {
        return hash('sha256', (string) $ip . '|' . wp_salt('auth'));
    }

    public static function insert_event($event) {
        global $wpdb;

        $defaults = [
            'occurred_at'        => gmdate('Y-m-d H:i:s'),
            'ip'                 => '',
            'ip_hash'            => '',
            'masked_ip'          => '',
            'user_agent'         => '',
            'bot_name'           => '',
            'category'           => '',
            'claimed_identity'   => '',
            'verification_state' => '',
            'path'               => '',
            'query_string'       => '',
            'referrer'           => '',
            'action_taken'       => '',
            'matched_rule'       => '',
            'country'            => '',
            'region'             => '',
            'city'               => '',
            'latitude'           => null,
            'longitude'          => null,
            'geo_source'         => '',
            'burst_count'        => 1,
        ];

        $event = wp_parse_args($event, $defaults);
        $fingerprint = md5(implode('|', [
            $event['ip_hash'],
            substr((string) $event['user_agent'], 0, 255),
            $event['bot_name'],
            $event['category'],
            $event['action_taken'],
            $event['path'],
        ]));
        $transient_key = 'bot_blocker_agg_' . $fingerprint;
        $existing_id = (int) get_transient($transient_key);

        if ($existing_id > 0) {
            $updated = $wpdb->query($wpdb->prepare(
                'UPDATE ' . self::table() . ' SET occurred_at = %s, burst_count = burst_count + 1 WHERE id = %d',
                gmdate('Y-m-d H:i:s'),
                $existing_id
            ));

            if ($updated !== false) {
                set_transient($transient_key, $existing_id, 30);
                return $existing_id;
            }
        }

        $wpdb->insert(self::table(), $event);
        $insert_id = (int) $wpdb->insert_id;
        if ($insert_id > 0) {
            set_transient($transient_key, $insert_id, 30);
        }

        return $insert_id;
    }

    public static function cleanup() {
        global $wpdb;

        $settings = BotBlocker_Settings::get();
        $days = max(1, (int) $settings['log_retention_days']);
        $cutoff = gmdate('Y-m-d H:i:s', time() - (DAY_IN_SECONDS * $days));

        $wpdb->query($wpdb->prepare('DELETE FROM ' . self::table() . ' WHERE occurred_at < %s', $cutoff));
        $wpdb->query($wpdb->prepare('DELETE FROM ' . self::lock_table() . ' WHERE expires_at < %s', gmdate('Y-m-d H:i:s')));
    }

    public static function get_recent_events($minutes = 5, $limit = 250, $filters = []) {
        global $wpdb;

        $minutes = max(1, (int) $minutes);
        $limit = max(1, min(1000, (int) $limit));

        $where = ['occurred_at >= %s'];
        $params = [gmdate('Y-m-d H:i:s', time() - (MINUTE_IN_SECONDS * $minutes))];

        if (!empty($filters['category'])) {
            $where[] = 'category = %s';
            $params[] = $filters['category'];
        }

        if (!empty($filters['action'])) {
            $where[] = 'action_taken = %s';
            $params[] = $filters['action'];
        }

        $sql = 'SELECT * FROM ' . self::table() . ' WHERE ' . implode(' AND ', $where) . ' ORDER BY occurred_at DESC LIMIT %d';
        $params[] = $limit;

        return $wpdb->get_results($wpdb->prepare($sql, ...$params), ARRAY_A);
    }

    public static function counts($minutes = 5) {
        $events = self::get_recent_events($minutes, 1000);
        $counts = ['live' => 0, 'blocked' => 0, 'ai' => 0, 'verified' => 0, 'fake' => 0, 'unmapped' => 0];
        $counts['live'] = count($events);

        foreach ($events as $event) {
            if (in_array($event['action_taken'], ['block', 'temporary_block'], true)) {
                $counts['blocked']++;
            }
            if (in_array($event['category'], ['ai_crawlers', 'ai_assistants'], true)) {
                $counts['ai']++;
            }
            if ($event['verification_state'] === 'verified') {
                $counts['verified']++;
            }
            if ($event['verification_state'] === 'failed') {
                $counts['fake']++;
            }
            if (empty($event['latitude']) || empty($event['longitude'])) {
                $counts['unmapped']++;
            }
        }

        return $counts;
    }

    public static function search_logs($args = []) {
        global $wpdb;

        $paged = max(1, (int) ($args['paged'] ?? 1));
        $per_page = max(10, min(200, (int) ($args['per_page'] ?? 30)));
        $offset = ($paged - 1) * $per_page;
        $orderby = in_array(($args['orderby'] ?? ''), ['occurred_at', 'bot_name', 'category', 'action_taken', 'country'], true) ? $args['orderby'] : 'occurred_at';
        $order = strtoupper((string) ($args['order'] ?? 'DESC')) === 'ASC' ? 'ASC' : 'DESC';

        $where = ['1=1'];
        $params = [];

        if (!empty($args['s'])) {
            $like = '%' . $wpdb->esc_like($args['s']) . '%';
            $where[] = '(bot_name LIKE %s OR user_agent LIKE %s OR path LIKE %s OR country LIKE %s OR category LIKE %s)';
            array_push($params, $like, $like, $like, $like, $like);
        }

        if (!empty($args['category'])) {
            $where[] = 'category = %s';
            $params[] = $args['category'];
        }

        if (!empty($args['action'])) {
            $where[] = 'action_taken = %s';
            $params[] = $args['action'];
        }

        $base = ' FROM ' . self::table() . ' WHERE ' . implode(' AND ', $where);
        $total_sql = 'SELECT COUNT(*)' . $base;
        $items_sql = 'SELECT *' . $base . " ORDER BY $orderby $order LIMIT %d OFFSET %d";

        $total = $params ? (int) $wpdb->get_var($wpdb->prepare($total_sql, ...$params)) : (int) $wpdb->get_var($total_sql);
        $items_params = array_merge($params, [$per_page, $offset]);
        $items = $wpdb->get_results($wpdb->prepare($items_sql, ...$items_params), ARRAY_A);

        return ['items' => $items, 'total' => $total];
    }

    public static function delete_logs($ids) {
        global $wpdb;

        $ids = array_filter(array_map('intval', (array) $ids));
        if (!$ids) {
            return;
        }

        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        $wpdb->query($wpdb->prepare("DELETE FROM " . self::table() . " WHERE id IN ($placeholders)", ...$ids));
    }

    public static function delete_filtered($args = []) {
        global $wpdb;

        $where = ['1=1'];
        $params = [];
        $has_filter = false;

        if (!empty($args['category'])) {
            $where[] = 'category = %s';
            $params[] = $args['category'];
            $has_filter = true;
        }

        if (!empty($args['action'])) {
            $where[] = 'action_taken = %s';
            $params[] = $args['action'];
            $has_filter = true;
        }

        if (!empty($args['s'])) {
            $like = '%' . $wpdb->esc_like($args['s']) . '%';
            $where[] = '(bot_name LIKE %s OR user_agent LIKE %s OR path LIKE %s OR country LIKE %s OR category LIKE %s)';
            array_push($params, $like, $like, $like, $like, $like);
            $has_filter = true;
        }

        if (!$has_filter) {
            return;
        }

        $sql = 'DELETE FROM ' . self::table() . ' WHERE ' . implode(' AND ', $where);
        $wpdb->query($wpdb->prepare($sql, ...$params));
    }

    public static function delete_all() {
        global $wpdb;
        $wpdb->query('TRUNCATE TABLE ' . self::table());
    }


    public static function privacy_export($email_address, $page = 1) {
        global $wpdb;

        $page = max(1, (int) $page);
        $per_page = 100;
        $offset = ($page - 1) * $per_page;
        $email = sanitize_email($email_address);

        if ($email === '') {
            return ['data' => [], 'done' => true];
        }

        $like = '%' . $wpdb->esc_like($email) . '%';
        $sql = 'SELECT * FROM ' . self::table() . ' WHERE query_string LIKE %s OR referrer LIKE %s ORDER BY id ASC LIMIT %d OFFSET %d';
        $rows = $wpdb->get_results($wpdb->prepare($sql, $like, $like, $per_page, $offset), ARRAY_A);

        $data = [];
        foreach ($rows as $row) {
            $data[] = [
                'group_id'    => 'bot-blocker',
                'group_label' => __('Bot Blocker logs', 'sera-bot-blocker'),
                'item_id'     => 'bot-blocker-log-' . (int) $row['id'],
                'data'        => [
                    ['name' => __('Time', 'sera-bot-blocker'), 'value' => $row['occurred_at']],
                    ['name' => __('Action', 'sera-bot-blocker'), 'value' => $row['action_taken']],
                    ['name' => __('Category', 'sera-bot-blocker'), 'value' => $row['category']],
                    ['name' => __('Path', 'sera-bot-blocker'), 'value' => $row['path']],
                    ['name' => __('Query string', 'sera-bot-blocker'), 'value' => $row['query_string']],
                    ['name' => __('Referrer', 'sera-bot-blocker'), 'value' => $row['referrer']],
                ],
            ];
        }

        return [
            'data' => $data,
            'done' => count($rows) < $per_page,
        ];
    }

    public static function privacy_erase($email_address, $page = 1) {
        global $wpdb;

        $page = max(1, (int) $page);
        $per_page = 100;
        $offset = ($page - 1) * $per_page;
        $email = sanitize_email($email_address);

        if ($email === '') {
            return ['items_removed' => false, 'messages' => [], 'done' => true];
        }

        $like = '%' . $wpdb->esc_like($email) . '%';
        $id_sql = 'SELECT id FROM ' . self::table() . ' WHERE query_string LIKE %s OR referrer LIKE %s ORDER BY id ASC LIMIT %d OFFSET %d';
        $ids = $wpdb->get_col($wpdb->prepare($id_sql, $like, $like, $per_page, $offset));

        if (!$ids) {
            return ['items_removed' => false, 'messages' => [], 'done' => true];
        }

        $ids = array_map('intval', $ids);
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        $removed = (int) $wpdb->query($wpdb->prepare('DELETE FROM ' . self::table() . ' WHERE id IN (' . $placeholders . ')', ...$ids));

        return [
            'items_removed' => $removed > 0,
            'messages'      => $removed > 0 ? [sprintf(__('Removed %d Bot Blocker log entries linked to the supplied email address.', 'sera-bot-blocker'), $removed)] : [],
            'done'          => count($ids) < $per_page,
        ];
    }

    public static function set_lock($key, $reason, $minutes = 30) {
        global $wpdb;

        $wpdb->replace(self::lock_table(), [
            'lock_key'   => $key,
            'reason'     => sanitize_text_field((string) $reason),
            'created_at' => gmdate('Y-m-d H:i:s'),
            'expires_at' => gmdate('Y-m-d H:i:s', time() + (MINUTE_IN_SECONDS * max(1, (int) $minutes))),
        ]);
    }

    public static function is_locked($key) {
        global $wpdb;

        return (bool) $wpdb->get_var($wpdb->prepare(
            'SELECT id FROM ' . self::lock_table() . ' WHERE lock_key = %s AND expires_at >= %s',
            $key,
            gmdate('Y-m-d H:i:s')
        ));
    }

    /**
     * Returns a 7-day threat summary for the dashboard widget.
     *
     * @return array{
     *   blocks_7d: int,
     *   top_category: string,
     *   top_country: string,
     *   fake_bot_attempts: int,
     *   total_events_7d: int
     * }
     */
    public static function get_threat_summary(): array {
        global $wpdb;

        $table   = self::table();
        $cutoff  = gmdate('Y-m-d H:i:s', strtotime('-7 days'));

        // Total events and blocked count in the last 7 days.
        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT
                    COUNT(*) AS total,
                    SUM(CASE WHEN action_taken IN ('block','temporary_block') THEN 1 ELSE 0 END) AS blocks,
                    SUM(CASE WHEN category = 'fake_trusted_bots' THEN 1 ELSE 0 END) AS fake_bots
                FROM {$table}
                WHERE occurred_at >= %s",
                $cutoff
            ),
            ARRAY_A
        );

        $total_events     = (int) ($row['total']   ?? 0);
        $blocks_7d        = (int) ($row['blocks']  ?? 0);
        $fake_bot_attempts = (int) ($row['fake_bots'] ?? 0);

        // Top blocked category.
        $top_category = (string) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT category
                FROM {$table}
                WHERE occurred_at >= %s
                  AND action_taken IN ('block','temporary_block')
                  AND category != ''
                GROUP BY category
                ORDER BY COUNT(*) DESC
                LIMIT 1",
                $cutoff
            )
        );

        // Top blocked country.
        $top_country = (string) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT country
                FROM {$table}
                WHERE occurred_at >= %s
                  AND action_taken IN ('block','temporary_block')
                  AND country != ''
                GROUP BY country
                ORDER BY COUNT(*) DESC
                LIMIT 1",
                $cutoff
            )
        );

        return [
            'blocks_7d'         => $blocks_7d,
            'top_category'      => $top_category ?: '',
            'top_country'       => $top_country  ?: '',
            'fake_bot_attempts' => $fake_bot_attempts,
            'total_events_7d'   => $total_events,
        ];
    }
}
