<?php

if (!defined('ABSPATH')) { exit; }

class AIEO_Event_Participants {

    const SCHEMA_OPTION  = 'aieo_events_schema_version';
    const SCHEMA_VERSION = '1';

    private static $registered = false;

    public static function register() {
        if (self::$registered) { return; }
        self::$registered = true;
        add_action('plugins_loaded', array(__CLASS__, 'maybe_install_schema'), 25);
    }

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

    
    public static function maybe_install_schema() {
        if (get_option(self::SCHEMA_OPTION) === self::SCHEMA_VERSION) { return; }
        if (!defined('AIEO_DIR')) { return; }
        $schema = AIEO_DIR . 'includes/dmm/events/sql/events-schema.php';
        if (!file_exists($schema)) { return; }
        require_once $schema;
        if (function_exists('aieo_events_install_schema')) {
            aieo_events_install_schema();
            update_option(self::SCHEMA_OPTION, self::SCHEMA_VERSION, false);
        }
    }

    

    
    public static function generate_token() {
        global $wpdb;
        $tbl = self::table();
        for ($i = 0; $i < 8; $i++) {
            try {
                $token = bin2hex(random_bytes(32)); 
            } catch (Exception $e) {
                
                $token = substr(str_replace('-', '', wp_generate_uuid4()) . wp_generate_password(64, false, false), 0, 64);
                $token = strtolower(preg_replace('/[^0-9a-f]/', '0', $token));
            }
            $exists = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM {$tbl} WHERE rsvp_token = %s",
                $token
            ));
            if ($exists === 0) { return $token; }
        }
        
        
        return $token;
    }

    

    
    public static function upsert($event_id, array $fields, $source, $imported_by = 0, $import_batch = '') {
        global $wpdb;
        $tbl      = self::table();
        $event_id = (int) $event_id;
        if ($event_id <= 0) { return 0; }

        $email = isset($fields['email']) ? strtolower(trim((string) $fields['email'])) : '';
        if ($email === '' || !is_email($email)) { return 0; }

        $now = current_time('mysql', true); 

        
        $resolved = self::resolve_eponymous_for_email($email);

        
        $row = array(
            'event_id'     => $event_id,
            'title'        => isset($fields['title'])        ? sanitize_text_field((string) $fields['title'])        : '',
            'salutation'   => isset($fields['salutation'])   ? sanitize_text_field((string) $fields['salutation'])   : '',
            'first_name'   => isset($fields['first_name'])   ? sanitize_text_field((string) $fields['first_name'])   : '',
            'last_name'    => isset($fields['last_name'])    ? sanitize_text_field((string) $fields['last_name'])    : '',
            'department'   => isset($fields['department'])   ? sanitize_text_field((string) $fields['department'])   : '',
            'organization' => isset($fields['organization']) ? sanitize_text_field((string) $fields['organization']) : '',
            'email'        => $email,
            'phone'        => isset($fields['phone'])        ? sanitize_text_field((string) $fields['phone'])        : '',
            'source'       => ($source === 'self-registered') ? 'self-registered' : 'imported',
            'rsvp_status'  => self::normalize_status($fields['rsvp_status'] ?? 'invited'),
            'rsvp_token'   => self::generate_token(),
            'imported_by'  => (int) $imported_by,
            'import_batch' => substr(sanitize_text_field((string) $import_batch), 0, 32),
            'eponymous_id' => (int) $resolved['eponymous_id'],
            'wp_user_id'   => (int) $resolved['wp_user_id'],
            'created_at'   => $now,
            'updated_at'   => $now,
        );
        if (!empty($fields['invited_at'])) {
            $row['invited_at'] = (string) $fields['invited_at'];
        }

        
        $int_cols = array('event_id', 'imported_by', 'eponymous_id', 'wp_user_id');
        $cols     = array_keys($row);
        $place    = array();
        foreach ($cols as $c) { $place[] = in_array($c, $int_cols, true) ? '%d' : '%s'; }

        
        
        
        
        
        
        
        $skip_on_dup = array('event_id', 'email', 'rsvp_token', 'created_at', 'updated_at', 'rsvp_status', 'source');
        $assigns = array();
        foreach ($cols as $c) {
            if (in_array($c, $skip_on_dup, true)) { continue; }
            $assigns[] = $c . ' = VALUES(' . $c . ')';
        }
        $assigns[] = 'updated_at = UTC_TIMESTAMP()';

        $sql = "INSERT INTO {$tbl} (" . implode(', ', $cols) . ") VALUES (" . implode(', ', $place) . ")
                ON DUPLICATE KEY UPDATE " . implode(', ', $assigns);

        $wpdb->query($wpdb->prepare($sql, ...array_values($row)));

        
        
        $pid = (int) $wpdb->insert_id;
        if ($pid <= 0) {
            $pid = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT participant_id FROM {$tbl} WHERE event_id = %d AND email = %s LIMIT 1",
                $event_id, $email
            ));
        }
        return $pid;
    }

    
    public static function set_status_by_token($token, $status) {
        global $wpdb;
        $token = strtolower(trim((string) $token));
        if (!preg_match('/^[0-9a-f]{64}$/', $token)) { return false; }
        $now = current_time('mysql', true); 
        return $wpdb->update(
            self::table(),
            array('rsvp_status' => self::normalize_status($status), 'responded_at' => $now, 'updated_at' => $now),
            array('rsvp_token' => $token),
            array('%s', '%s', '%s'),
            array('%s')
        );
    }

    
    public static function mark_invited($token) {
        global $wpdb;
        $token = strtolower(trim((string) $token));
        if (!preg_match('/^[0-9a-f]{64}$/', $token)) { return false; }
        $now = current_time('mysql', true); 
        return $wpdb->update(
            self::table(),
            array('invited_at' => $now, 'updated_at' => $now),
            array('rsvp_token' => $token),
            array('%s', '%s'),
            array('%s')
        );
    }

    
    private static function normalize_status($status) {
        $status = strtolower(trim((string) $status));
        return in_array($status, array('invited', 'confirmed', 'declined'), true) ? $status : 'invited';
    }

    

    
    public static function get_by_token($token) {
        global $wpdb;
        $token = trim((string) $token);
        if ($token === '') { return null; }
        $tbl = self::table();
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$tbl} WHERE rsvp_token = %s LIMIT 1",
            $token
        ));
    }

    
    public static function counts($event_id) {
        global $wpdb;
        $tbl = self::table();
        $out = array('invited' => 0, 'confirmed' => 0, 'declined' => 0, 'total' => 0);
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT rsvp_status, COUNT(*) AS n FROM {$tbl} WHERE event_id = %d GROUP BY rsvp_status",
            (int) $event_id
        ));
        if ($rows) {
            foreach ($rows as $r) {
                $n = (int) $r->n;
                $out['total'] += $n;
                $s = (string) $r->rsvp_status;
                if (isset($out[$s])) { $out[$s] += $n; }
            }
        }
        return $out;
    }

    
    public static function stats($event_id) {
        global $wpdb;
        $tbl      = self::table();
        $event_id = (int) $event_id;
        $out = array(
            'total' => 0, 'confirmed' => 0, 'declined' => 0, 'invited' => 0, 'responded' => 0,
            'response_rate' => 0, 'imported' => 0, 'self_registered' => 0, 'invited_sent' => 0,
        );
        if ($event_id <= 0) { return $out; }

        $row = $wpdb->get_row($wpdb->prepare(
            "SELECT
                COUNT(*) AS total,
                SUM(rsvp_status = 'confirmed') AS confirmed,
                SUM(rsvp_status = 'declined') AS declined,
                SUM(rsvp_status = 'invited') AS invited,
                SUM(source = 'imported') AS imported,
                SUM(source = 'self-registered') AS self_registered,
                SUM(invited_at IS NOT NULL AND invited_at <> '' AND invited_at <> '0000-00-00 00:00:00') AS invited_sent
             FROM {$tbl} WHERE event_id = %d",
            $event_id
        ), ARRAY_A);

        if ($row) {
            foreach (array('total', 'confirmed', 'declined', 'invited', 'imported', 'self_registered', 'invited_sent') as $k) {
                $out[$k] = (int) $row[$k];
            }
            $out['responded']     = $out['confirmed'] + $out['declined'];
            $out['response_rate'] = $out['total'] > 0 ? (int) round(100 * $out['responded'] / $out['total']) : 0;
        }
        return $out;
    }

    
    public static function get_for_event($event_id, $args = array()) {
        global $wpdb;
        $tbl      = self::table();
        $event_id = (int) $event_id;
        if ($event_id <= 0) { return array(); }

        $where  = array('event_id = %d');
        $params = array($event_id);

        if (!empty($args['rsvp_status'])) {
            $where[]  = 'rsvp_status = %s';
            $params[] = self::normalize_status($args['rsvp_status']);
        }
        if (!empty($args['search'])) {
            $like     = '%' . $wpdb->esc_like((string) $args['search']) . '%';
            $where[]  = '(email LIKE %s OR first_name LIKE %s OR last_name LIKE %s OR organization LIKE %s)';
            $params[] = $like; $params[] = $like; $params[] = $like; $params[] = $like;
        }

        
        $allowed_orderby = array(
            'participant_id' => 'participant_id', 'last_name' => 'last_name',
            'email' => 'email', 'rsvp_status' => 'rsvp_status',
            'created_at' => 'created_at', 'updated_at' => 'updated_at',
        );
        $orderby = isset($args['orderby'], $allowed_orderby[$args['orderby']])
            ? $allowed_orderby[$args['orderby']] : 'participant_id';
        $order   = (isset($args['order']) && strtoupper((string) $args['order']) === 'ASC') ? 'ASC' : 'DESC';

        $sql = "SELECT * FROM {$tbl} WHERE " . implode(' AND ', $where) . " ORDER BY {$orderby} {$order}";

        $limit  = isset($args['limit'])  ? max(0, (int) $args['limit'])  : 0;
        $offset = isset($args['offset']) ? max(0, (int) $args['offset']) : 0;
        if ($limit > 0) {
            $sql     .= ' LIMIT %d OFFSET %d';
            $params[] = $limit;
            $params[] = $offset;
        }

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

    
    public static function count_for_event($event_id, $args = array()) {
        global $wpdb;
        $tbl      = self::table();
        $event_id = (int) $event_id;
        if ($event_id <= 0) { return 0; }

        $where  = array('event_id = %d');
        $params = array($event_id);
        if (!empty($args['rsvp_status'])) {
            $where[]  = 'rsvp_status = %s';
            $params[] = self::normalize_status($args['rsvp_status']);
        }
        if (!empty($args['search'])) {
            $like     = '%' . $wpdb->esc_like((string) $args['search']) . '%';
            $where[]  = '(email LIKE %s OR first_name LIKE %s OR last_name LIKE %s OR organization LIKE %s)';
            $params[] = $like; $params[] = $like; $params[] = $like; $params[] = $like;
        }
        $sql = "SELECT COUNT(*) FROM {$tbl} WHERE " . implode(' AND ', $where);
        return (int) $wpdb->get_var($wpdb->prepare($sql, ...$params));
    }

    

    
    public static function is_verified_email($email, $eponymous_id = 0) {
        if ((int) $eponymous_id <= 0) {
            $resolved    = self::resolve_eponymous_for_email($email);
            $eponymous_id = (int) $resolved['eponymous_id'];
        }
        return (email_exists($email) !== false) || ((int) $eponymous_id > 0);
    }

    
    public static function resolve_eponymous_for_email($email) {
        global $wpdb;
        $out = array('eponymous_id' => 0, 'wp_user_id' => 0);
        $email = strtolower(trim((string) $email));
        if ($email === '') { return $out; }

        static $has_ci = null;
        $ci = $wpdb->prefix . 'aieo_customer_intelligence';
        if ($has_ci === null) {
            $has_ci = ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $ci)) === $ci);
        }
        if (!$has_ci) { return $out; }

        $row = $wpdb->get_row($wpdb->prepare(
            "SELECT eponymous_id, wp_user_id FROM {$ci}
              WHERE LOWER(TRIM(email)) COLLATE utf8mb4_unicode_ci = %s COLLATE utf8mb4_unicode_ci
              LIMIT 1",
            $email
        ));
        if ($row) {
            $out['eponymous_id'] = (int) $row->eponymous_id;
            $out['wp_user_id']   = (int) $row->wp_user_id;
        }
        return $out;
    }
}
