<?php
/**
 * Logs management class
 *
 * Handles submission logging and retrieval for the Form Connector plugin.
 *
 * @package FormConnector
 * @subpackage Logs
 */

namespace FormConnector\Features\Logs;

defined('ABSPATH') || exit;

/**
 * Logs class
 */
class Logs {

    /**
     * Singleton instance
     *
     * @var Logs|null
     */
    private static ?Logs $instance = null;

    /**
     * Track if we've already checked schema this request
     *
     * @var bool
     */
    private static bool $schema_checked = false;

    /**
     * Get singleton instance
     *
     * @return Logs
     */
    public static function get_instance(): Logs {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    /**
     * Get the log table name
     *
     * @return string
     */
    public function get_table_name(): string {
        global $wpdb;
        return $wpdb->prefix . 'fc_submissions_log';
    }

    /**
     * Ensure all required columns exist in the table
     * This auto-migrates on first use if needed
     *
     * @return void
     */
    private function ensure_schema(): void {
        if (self::$schema_checked) {
            return;
        }
        self::$schema_checked = true;

        global $wpdb;
        $table_name = $this->get_table_name();

        // Check if table exists
        if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) !== $table_name) {
            $this->create_table();
            return;
        }

        // Get existing columns
        $existing_columns = $wpdb->get_col("SHOW COLUMNS FROM {$table_name}");
        
        // Required columns and their definitions
        $required_columns = [
            'utm_term'       => "VARCHAR(255) DEFAULT '' NOT NULL",
            'utm_content'    => "VARCHAR(255) DEFAULT '' NOT NULL",
            'utm_id'         => "VARCHAR(255) DEFAULT '' NOT NULL",
            'landing_page'   => "VARCHAR(500) DEFAULT '' NOT NULL",
            'referrer_other' => "VARCHAR(500) DEFAULT '' NOT NULL",
        ];

        foreach ($required_columns as $column => $definition) {
            if (!in_array($column, $existing_columns)) {
                $wpdb->query("ALTER TABLE {$table_name} ADD COLUMN {$column} {$definition}");
                if (defined('WP_DEBUG') && WP_DEBUG) {
                    error_log("[FormConnector Logs] Added missing column: {$column}");
                }
            }
        }
    }

    /**
     * Create the log table
     *
     * @return void
     */
    public function create_table(): void {
        global $wpdb;
        
        $table_name = $this->get_table_name();
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            timestamp datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
            form_id varchar(100) NOT NULL,
            form_name varchar(255) DEFAULT '' NOT NULL,
            form_plugin varchar(50) DEFAULT '' NOT NULL,
            submitted_data longtext NOT NULL,
            response_message longtext NOT NULL,
            response_status varchar(20) DEFAULT '' NOT NULL,
            location_id varchar(100) DEFAULT '' NOT NULL,
            location_name varchar(255) DEFAULT '' NOT NULL,
            utm_source varchar(255) DEFAULT '' NOT NULL,
            utm_medium varchar(255) DEFAULT '' NOT NULL,
            utm_campaign varchar(255) DEFAULT '' NOT NULL,
            utm_term varchar(255) DEFAULT '' NOT NULL,
            utm_content varchar(255) DEFAULT '' NOT NULL,
            utm_id varchar(255) DEFAULT '' NOT NULL,
            landing_page varchar(500) DEFAULT '' NOT NULL,
            referrer_other varchar(500) DEFAULT '' NOT NULL,
            PRIMARY KEY (id),
            KEY timestamp (timestamp),
            KEY form_plugin (form_plugin),
            KEY response_status (response_status),
            KEY location_id (location_id)
        ) $charset_collate;";

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

    /**
     * Log a form submission
     *
     * @param array $data Log data
     * @return int|false Insert ID on success, false on failure
     */
    public function log_submission(array $data): int|false {
        global $wpdb;
        
        // Ensure schema is up to date before inserting
        $this->ensure_schema();
        
        $table_name = $this->get_table_name();

        // Prepare submitted data
        $submitted_data = isset($data['submitted_data']) ? $data['submitted_data'] : [];
        $submitted_data_json = wp_json_encode($submitted_data, JSON_UNESCAPED_SLASHES);
        if ($submitted_data_json === false) {
            $submitted_data_json = '{}';
        }

        // Prepare response message
        $response = isset($data['response']) ? $data['response'] : '';
        if (is_wp_error($response)) {
            $response_json = wp_json_encode([
                'error_code' => $response->get_error_code(),
                'error_message' => $response->get_error_message(),
            ], JSON_UNESCAPED_SLASHES);
        } elseif (is_array($response) || is_object($response)) {
            $response_json = wp_json_encode($response, JSON_UNESCAPED_SLASHES);
        } else {
            $response_json = wp_json_encode(['message' => (string) $response], JSON_UNESCAPED_SLASHES);
        }

        $result = $wpdb->insert(
            $table_name,
            [
                'timestamp'        => current_time('mysql', true),
                'form_id'          => sanitize_text_field($data['form_id'] ?? ''),
                'form_name'        => sanitize_text_field($data['form_name'] ?? ''),
                'form_plugin'      => sanitize_text_field($data['form_plugin'] ?? ''),
                'submitted_data'   => $submitted_data_json,
                'response_status'  => sanitize_key($data['status'] ?? 'unknown'),
                'response_message' => $response_json,
                'location_id'      => sanitize_text_field($data['location_id'] ?? ''),
                'location_name'    => sanitize_text_field($data['location_name'] ?? ''),
                'utm_source'       => sanitize_text_field($data['utm_source'] ?? ''),
                'utm_medium'       => sanitize_text_field($data['utm_medium'] ?? ''),
                'utm_campaign'     => sanitize_text_field($data['utm_campaign'] ?? ''),
                'utm_term'         => sanitize_text_field($data['utm_term'] ?? ''),
                'utm_content'      => sanitize_text_field($data['utm_content'] ?? ''),
                'utm_id'           => sanitize_text_field($data['utm_id'] ?? ''),
                'landing_page'     => sanitize_text_field($data['landing_page'] ?? ''),
                'referrer_other'   => sanitize_text_field($data['referrer_other'] ?? ''),
            ],
            ['%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s']
        );

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

    /**
     * Get logs with filtering and pagination
     *
     * @param array $args Query arguments
     * @return array
     */
    public function get_logs(array $args = []): array {
        global $wpdb;
        
        $table_name = $this->get_table_name();

        // Check if table exists
        if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) !== $table_name) {
            return ['logs' => [], 'total' => 0, 'pages' => 0];
        }

        $defaults = [
            'page'        => 1,
            'per_page'    => 20,
            'status'      => '',
            'plugin'      => '',
            'search'      => '',
            'date_from'   => '',
            'date_to'     => '',
            'orderby'     => 'timestamp',
            'order'       => 'DESC',
        ];

        $args = wp_parse_args($args, $defaults);

        // Build WHERE clause
        $where = ['1=1'];
        $values = [];

        if (!empty($args['status'])) {
            $where[] = 'response_status = %s';
            $values[] = $args['status'];
        }

        if (!empty($args['plugin'])) {
            $where[] = 'form_plugin = %s';
            $values[] = $args['plugin'];
        }

        if (!empty($args['search'])) {
            $where[] = '(form_name LIKE %s OR submitted_data LIKE %s)';
            $search = '%' . $wpdb->esc_like($args['search']) . '%';
            $values[] = $search;
            $values[] = $search;
        }

        if (!empty($args['date_from'])) {
            $where[] = 'timestamp >= %s';
            $values[] = $args['date_from'] . ' 00:00:00';
        }

        if (!empty($args['date_to'])) {
            $where[] = 'timestamp <= %s';
            $values[] = $args['date_to'] . ' 23:59:59';
        }

        $where_sql = implode(' AND ', $where);

        // Validate orderby
        $allowed_orderby = ['id', 'timestamp', 'form_name', 'form_plugin', 'response_status'];
        $orderby = in_array($args['orderby'], $allowed_orderby) ? $args['orderby'] : 'timestamp';
        $order = strtoupper($args['order']) === 'ASC' ? 'ASC' : 'DESC';

        // Get total count
        $count_sql = "SELECT COUNT(*) FROM $table_name WHERE $where_sql";
        if (!empty($values)) {
            $count_sql = $wpdb->prepare($count_sql, $values);
        }
        $total = (int) $wpdb->get_var($count_sql);

        // Calculate pagination
        $per_page = max(1, min(100, (int) $args['per_page']));
        $page = max(1, (int) $args['page']);
        $pages = ceil($total / $per_page);
        $offset = ($page - 1) * $per_page;

        // Get logs
        $sql = "SELECT * FROM $table_name WHERE $where_sql ORDER BY $orderby $order LIMIT %d OFFSET %d";
        $values[] = $per_page;
        $values[] = $offset;
        
        $results = $wpdb->get_results($wpdb->prepare($sql, $values), ARRAY_A);

        // Parse JSON fields
        $logs = [];
        foreach ($results as $row) {
            $row['submitted_data'] = json_decode($row['submitted_data'], true) ?? [];
            $row['response_message'] = json_decode($row['response_message'], true) ?? [];
            $logs[] = $row;
        }

        return [
            'logs'  => $logs,
            'total' => $total,
            'pages' => (int) $pages,
            'page'  => $page,
        ];
    }

    /**
     * Get a single log entry
     *
     * @param int $id Log ID
     * @return array|null
     */
    public function get_log(int $id): ?array {
        global $wpdb;
        
        $table_name = $this->get_table_name();
        
        $row = $wpdb->get_row(
            $wpdb->prepare("SELECT * FROM $table_name WHERE id = %d", $id),
            ARRAY_A
        );

        if (!$row) {
            return null;
        }

        $row['submitted_data'] = json_decode($row['submitted_data'], true) ?? [];
        $row['response_message'] = json_decode($row['response_message'], true) ?? [];

        return $row;
    }

    /**
     * Delete a log entry
     *
     * @param int $id Log ID
     * @return bool
     */
    public function delete_log(int $id): bool {
        global $wpdb;
        
        $table_name = $this->get_table_name();
        
        return $wpdb->delete($table_name, ['id' => $id], ['%d']) !== false;
    }

    /**
     * Delete logs older than a certain number of days
     *
     * @param int $days Number of days
     * @return int Number of deleted rows
     */
    public function delete_old_logs(int $days): int {
        global $wpdb;
        
        $table_name = $this->get_table_name();
        $date = gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));
        
        return (int) $wpdb->query(
            $wpdb->prepare("DELETE FROM $table_name WHERE timestamp < %s", $date)
        );
    }

    /**
     * Redact PII (email/phone/first_name/last_name) in log rows older than $days.
     *
     * Replaces values with the literal "[redacted]" in the stored JSON and
     * tags the row with `__redacted: true` so subsequent runs skip it.
     * Processes up to $batch rows per invocation.
     *
     * @param int $days Age threshold in days. <=0 disables the operation.
     * @param int $batch Maximum rows to process this call.
     * @return int Number of rows redacted.
     */
    public function redact_pii_older_than(int $days, int $batch = 200): int {
        global $wpdb;

        if ($days <= 0) {
            return 0;
        }

        $table_name = $this->get_table_name();
        $cutoff = gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));
        $batch = max(1, min(1000, $batch));

        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, submitted_data FROM $table_name
                    WHERE timestamp < %s
                      AND submitted_data NOT LIKE %s
                    LIMIT %d",
                $cutoff,
                '%"__redacted":true%',
                $batch
            ),
            ARRAY_A
        );

        if (empty($rows)) {
            return 0;
        }

        $pii_keys = ['email', 'phone', 'first_name', 'last_name', 'mobile'];
        $redacted_count = 0;

        foreach ($rows as $row) {
            $payload = json_decode((string) $row['submitted_data'], true);
            if (!is_array($payload)) {
                $payload = [];
            }

            if (isset($payload['mapped_to_carehq']) && is_array($payload['mapped_to_carehq'])) {
                foreach ($pii_keys as $k) {
                    if (array_key_exists($k, $payload['mapped_to_carehq']) && $payload['mapped_to_carehq'][$k] !== '') {
                        $payload['mapped_to_carehq'][$k] = '[redacted]';
                    }
                }
            }

            if (isset($payload['original_fields']) && is_array($payload['original_fields'])) {
                foreach ($payload['original_fields'] as $fk => $fv) {
                    if (is_string($fv) && (preg_match('/@/', $fv) || preg_match('/\\+?\\d[\\d\\s\\-]{6,}/', $fv))) {
                        $payload['original_fields'][$fk] = '[redacted]';
                    }
                }
            }

            $payload['__redacted'] = true;

            $json = wp_json_encode($payload, JSON_UNESCAPED_SLASHES);
            if ($json === false) {
                continue;
            }

            $updated = $wpdb->update(
                $table_name,
                ['submitted_data' => $json],
                ['id' => (int) $row['id']],
                ['%s'],
                ['%d']
            );

            if ($updated !== false) {
                $redacted_count++;
            }
        }

        return $redacted_count;
    }

    /**
     * Get log statistics
     *
     * @return array
     */
    public function get_stats(): array {
        global $wpdb;
        
        $table_name = $this->get_table_name();

        // Check if table exists
        if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) !== $table_name) {
            return [
                'total'     => 0,
                'success'   => 0,
                'error'     => 0,
                'skipped'   => 0,
                'today'     => 0,
                'this_week' => 0,
            ];
        }

        $today = gmdate('Y-m-d');
        $week_start = gmdate('Y-m-d', strtotime('monday this week'));

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table_name is safe
        $stats = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT 
                    COUNT(*) as total,
                    SUM(CASE WHEN response_status = 'success' THEN 1 ELSE 0 END) as success,
                    SUM(CASE WHEN response_status = 'error' THEN 1 ELSE 0 END) as error,
                    SUM(CASE WHEN response_status = 'skipped' THEN 1 ELSE 0 END) as skipped,
                    SUM(CASE WHEN DATE(timestamp) = %s THEN 1 ELSE 0 END) as today,
                    SUM(CASE WHEN DATE(timestamp) >= %s THEN 1 ELSE 0 END) as this_week
                FROM {$table_name}",
                $today,
                $week_start
            ),
            ARRAY_A
        );

        return [
            'total'     => (int) ($stats['total'] ?? 0),
            'success'   => (int) ($stats['success'] ?? 0),
            'error'     => (int) ($stats['error'] ?? 0),
            'skipped'   => (int) ($stats['skipped'] ?? 0),
            'today'     => (int) ($stats['today'] ?? 0),
            'this_week' => (int) ($stats['this_week'] ?? 0),
        ];
    }

    /**
     * Check whether an email was successfully submitted within the last N hours.
     *
     * Used for duplicate-submission detection. Searches the `submitted_data`
     * JSON column for the mapped email address.
     *
     * @param string $email Email address to look for.
     * @param int    $hours Look-back window in hours. 0 = feature disabled.
     * @return bool True if a duplicate is found.
     */
    public function has_recent_submission_by_email(string $email, int $hours): bool {
        global $wpdb;

        if (empty($email) || $hours <= 0) {
            return false;
        }

        $table_name = $this->get_table_name();

        if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) !== $table_name) {
            return false;
        }

        $cutoff = gmdate('Y-m-d H:i:s', strtotime("-{$hours} hours"));

        // The email lives inside mapped_to_carehq.email in the submitted_data JSON.
        // Use a LIKE search as a fast first-pass (safe because $email is never stored as regex).
        $email_pattern = '%' . $wpdb->esc_like('"email":"' . $email . '"') . '%';

        $count = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$table_name}
                    WHERE response_status = 'success'
                      AND timestamp >= %s
                      AND submitted_data LIKE %s",
                $cutoff,
                $email_pattern
            )
        );

        return $count > 0;
    }

    /**
     * Clear all logs
     *
     * @return bool
     */
    public function clear_all(): bool {
        global $wpdb;
        
        $table_name = $this->get_table_name();
        
        return $wpdb->query("TRUNCATE TABLE $table_name") !== false;
    }
}
