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

class Novajyin_Repository
{
    public static function defaults()
    {
        return array(
            'managed_forms' => array(),
            'field_mapping' => array(),
            'save_attachments' => 0,
            'retention_days' => 0,
            'keep_data_on_delete' => 1,
            'notify_on_new' => 1,
            'notify_admin' => 1,
            'notify_assignee' => 1,
            'notify_group' => 1,
            'notify_emails' => '',
        );
    }

    public static function get_settings()
    {
        $settings = get_option(NOVAJYIN_OPTION_SETTINGS, array());
        if (!is_array($settings)) {
            $settings = array();
        }

        return wp_parse_args($settings, self::defaults());
    }

    public static function update_settings($settings)
    {
        update_option(NOVAJYIN_OPTION_SETTINGS, wp_parse_args($settings, self::defaults()), false);
    }

    public static function current_mysql()
    {
        return current_time('mysql');
    }

    public static function decode_json($value)
    {
        if (empty($value)) {
            return array();
        }

        $decoded = json_decode($value, true);
        return is_array($decoded) ? $decoded : array();
    }

    public static function get_default_status_id()
    {
        global $wpdb;

        $table = Novajyin_DB::table('statuses');
        $status_id = (int) $wpdb->get_var("SELECT id FROM {$table} WHERE is_default = 1 ORDER BY sort_order ASC, id ASC LIMIT 1");

        if ($status_id <= 0) {
            $status_id = (int) $wpdb->get_var("SELECT id FROM {$table} ORDER BY sort_order ASC, id ASC LIMIT 1");
        }

        return $status_id;
    }

    public static function get_default_priority_id()
    {
        global $wpdb;

        $table = Novajyin_DB::table('priorities');
        $priority_id = (int) $wpdb->get_var($wpdb->prepare("SELECT id FROM {$table} WHERE priority_name = %s ORDER BY sort_order ASC, id ASC LIMIT 1", '中'));

        if ($priority_id <= 0) {
            $priority_id = (int) $wpdb->get_var("SELECT id FROM {$table} ORDER BY sort_order ASC, id ASC LIMIT 1");
        }

        return $priority_id;
    }

    public static function insert_inquiry($data)
    {
        global $wpdb;

        $table = Novajyin_DB::table('inquiries');
        $now = self::current_mysql();

        $defaults = array(
            'form_id' => 0,
            'form_name' => '',
            'submitted_at' => $now,
            'name' => '',
            'email' => '',
            'tel' => '',
            'subject' => '',
            'inquiry_type' => '',
            'shop_name' => '',
            'message' => '',
            'raw_data_json' => '',
            'attachments_json' => '',
            'status_id' => self::get_default_status_id(),
            'assignee_user_id' => null,
            'assignee_group_id' => null,
            'priority_id' => self::get_default_priority_id(),
            'due_date' => null,
            'created_at' => $now,
            'updated_at' => $now,
        );

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

        $wpdb->insert(
            $table,
            $data,
            array(
                '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s',
                '%s', '%s', '%d', '%d', '%d', '%d', '%s', '%s', '%s',
            )
        );

        return (int) $wpdb->insert_id;
    }

    public static function update_inquiry($inquiry_id, $changes, $user_id = 0, $record_history = true)
    {
        global $wpdb;

        $inquiry_id = absint($inquiry_id);
        if ($inquiry_id <= 0 || empty($changes)) {
            return false;
        }

        $old = self::get_inquiry($inquiry_id);
        if (!$old) {
            return false;
        }

        $allowed = array(
            'status_id' => '%d',
            'assignee_user_id' => '%d',
            'assignee_group_id' => '%d',
            'priority_id' => '%d',
            'due_date' => '%s',
            'attachments_json' => '%s',
            'updated_at' => '%s',
        );

        $data = array();
        $format = array();

        foreach ($changes as $key => $value) {
            if (!isset($allowed[$key])) {
                continue;
            }

            if (in_array($key, array('status_id', 'assignee_user_id', 'assignee_group_id', 'priority_id'), true)) {
                $value = $value === null || $value === '' ? null : absint($value);
            }

            if ($key === 'due_date') {
                $value = $value ? sanitize_text_field($value) : null;
            }

            $data[$key] = $value;
            $format[] = $allowed[$key];
        }

        if (empty($data)) {
            return false;
        }

        $data['updated_at'] = self::current_mysql();
        $format[] = '%s';

        $updated = $wpdb->update(
            Novajyin_DB::table('inquiries'),
            $data,
            array('id' => $inquiry_id),
            $format,
            array('%d')
        );

        if ($updated !== false && $record_history) {
            self::record_change_histories($old, $data, $user_id);
        }

        return $updated !== false;
    }

    private static function record_change_histories($old, $data, $user_id)
    {
        $labels = array(
            'status_id' => array('ステータス変更', 'ステータス', 'status'),
            'assignee_user_id' => array('担当者変更', '担当者', 'assignee_user'),
            'assignee_group_id' => array('担当グループ変更', '担当グループ', 'assignee_group'),
            'priority_id' => array('優先度変更', '優先度', 'priority'),
            'due_date' => array('期限変更', '対応期限', 'due_date'),
        );

        foreach ($labels as $field => $meta) {
            if (!array_key_exists($field, $data)) {
                continue;
            }

            $old_value = isset($old->$field) ? $old->$field : null;
            $new_value = $data[$field];
            if ((string) $old_value === (string) $new_value) {
                continue;
            }

            $content = sprintf(
                '%sが「%s」から「%s」に変更されました。',
                $meta[1],
                self::label_for_value($field, $old_value),
                self::label_for_value($field, $new_value)
            );

            self::add_history((int) $old->id, $meta[2], $content, $user_id);
        }
    }

    public static function label_for_value($field, $value)
    {
        if ($value === null || $value === '') {
            return '未設定';
        }

        $value = is_numeric($value) ? (int) $value : $value;

        if ($field === 'status_id') {
            $status = self::get_status($value);
            return $status ? $status->status_name : '未設定';
        }

        if ($field === 'priority_id') {
            $priority = self::get_priority($value);
            return $priority ? $priority->priority_name : '未設定';
        }

        if ($field === 'assignee_user_id') {
            $user = get_user_by('id', $value);
            return $user ? $user->display_name : '未設定';
        }

        if ($field === 'assignee_group_id') {
            $group = self::get_group($value);
            return $group ? $group->group_name : '未設定';
        }

        if ($field === 'due_date') {
            return $value ? mysql2date('Y/m/d H:i', $value) : '未設定';
        }

        return (string) $value;
    }

    public static function get_inquiry($inquiry_id)
    {
        global $wpdb;

        $inquiries = Novajyin_DB::table('inquiries');
        $statuses = Novajyin_DB::table('statuses');
        $priorities = Novajyin_DB::table('priorities');
        $groups = Novajyin_DB::table('groups');
        $users = $wpdb->users;

        return $wpdb->get_row(
            $wpdb->prepare(
                "SELECT i.*, s.status_name, s.is_closed, p.priority_name, g.group_name, u.display_name AS assignee_name
                FROM {$inquiries} i
                LEFT JOIN {$statuses} s ON i.status_id = s.id
                LEFT JOIN {$priorities} p ON i.priority_id = p.id
                LEFT JOIN {$groups} g ON i.assignee_group_id = g.id
                LEFT JOIN {$users} u ON i.assignee_user_id = u.ID
                WHERE i.id = %d",
                $inquiry_id
            )
        );
    }

    public static function query_inquiries($args = array())
    {
        global $wpdb;

        $defaults = array(
            'paged' => 1,
            'per_page' => 20,
            'search' => '',
            'form_id' => '',
            'status_id' => '',
            'assignee_user_id' => '',
            'assignee_group_id' => '',
            'priority_id' => '',
            'shop_name' => '',
            'inquiry_type' => '',
            'due_filter' => '',
            'unhandled_only' => '',
            'date_from' => '',
            'date_to' => '',
            'current_user_id' => get_current_user_id(),
        );

        $args = wp_parse_args($args, $defaults);
        $query = self::build_inquiry_query($args, false);
        $per_page = (int) $args['per_page'];
        $paged = max(1, (int) $args['paged']);

        $sql = $query['sql'] . ' ORDER BY i.submitted_at DESC, i.id DESC';
        if ($per_page > 0) {
            $offset = ($paged - 1) * $per_page;
            $sql .= $wpdb->prepare(' LIMIT %d OFFSET %d', $per_page, $offset);
        }

        return self::prepared_results($sql, $query['params']);
    }

    public static function count_inquiries($args = array())
    {
        $args = wp_parse_args(
            $args,
            array(
                'paged' => 1,
                'per_page' => 20,
                'search' => '',
                'form_id' => '',
                'status_id' => '',
                'assignee_user_id' => '',
                'assignee_group_id' => '',
                'priority_id' => '',
                'shop_name' => '',
                'inquiry_type' => '',
                'due_filter' => '',
                'unhandled_only' => '',
                'date_from' => '',
                'date_to' => '',
                'current_user_id' => get_current_user_id(),
            )
        );
        $query = self::build_inquiry_query($args, true);
        return (int) self::prepared_var($query['sql'], $query['params']);
    }

    private static function build_inquiry_query($args, $count_only)
    {
        global $wpdb;

        $inquiries = Novajyin_DB::table('inquiries');
        $statuses = Novajyin_DB::table('statuses');
        $priorities = Novajyin_DB::table('priorities');
        $groups = Novajyin_DB::table('groups');
        $users = $wpdb->users;

        $where = array('1=1');
        $params = array();

        if ($args['form_id'] !== '') {
            $where[] = 'i.form_id = %d';
            $params[] = absint($args['form_id']);
        }

        if ($args['status_id'] !== '') {
            $where[] = 'i.status_id = %d';
            $params[] = absint($args['status_id']);
        }

        if ($args['assignee_user_id'] !== '') {
            $where[] = 'i.assignee_user_id = %d';
            $params[] = absint($args['assignee_user_id']);
        }

        if ($args['assignee_group_id'] !== '') {
            $where[] = 'i.assignee_group_id = %d';
            $params[] = absint($args['assignee_group_id']);
        }

        if ($args['priority_id'] !== '') {
            $where[] = 'i.priority_id = %d';
            $params[] = absint($args['priority_id']);
        }

        if ($args['shop_name'] !== '') {
            $where[] = 'i.shop_name LIKE %s';
            $params[] = '%' . $wpdb->esc_like(sanitize_text_field($args['shop_name'])) . '%';
        }

        if ($args['inquiry_type'] !== '') {
            $where[] = 'i.inquiry_type LIKE %s';
            $params[] = '%' . $wpdb->esc_like(sanitize_text_field($args['inquiry_type'])) . '%';
        }

        if ($args['date_from'] !== '') {
            $where[] = 'i.submitted_at >= %s';
            $params[] = sanitize_text_field($args['date_from']) . ' 00:00:00';
        }

        if ($args['date_to'] !== '') {
            $where[] = 'i.submitted_at <= %s';
            $params[] = sanitize_text_field($args['date_to']) . ' 23:59:59';
        }

        $now = current_time('mysql');
        $today_start = current_time('Y-m-d') . ' 00:00:00';
        $today_end = current_time('Y-m-d') . ' 23:59:59';

        if ($args['due_filter'] === 'overdue') {
            $where[] = 'i.due_date IS NOT NULL AND i.due_date < %s AND (s.is_closed IS NULL OR s.is_closed = 0)';
            $params[] = $now;
        } elseif ($args['due_filter'] === 'today') {
            $where[] = 'i.due_date BETWEEN %s AND %s';
            $params[] = $today_start;
            $params[] = $today_end;
        } elseif ($args['due_filter'] === 'none') {
            $where[] = 'i.due_date IS NULL';
        }

        if (!empty($args['unhandled_only'])) {
            $where[] = '(s.is_closed IS NULL OR s.is_closed = 0)';
        }

        if ($args['search'] !== '') {
            $like = '%' . $wpdb->esc_like(sanitize_text_field($args['search'])) . '%';
            $where[] = '(i.name LIKE %s OR i.email LIKE %s OR i.tel LIKE %s OR i.message LIKE %s OR i.raw_data_json LIKE %s)';
            array_push($params, $like, $like, $like, $like, $like);
        }

        $scope = self::scope_clause((int) $args['current_user_id']);
        if ($scope['sql']) {
            $where[] = $scope['sql'];
            $params = array_merge($params, $scope['params']);
        }

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

        if ($count_only) {
            $sql = "SELECT COUNT(*)
                FROM {$inquiries} i
                LEFT JOIN {$statuses} s ON i.status_id = s.id
                WHERE {$where_sql}";
        } else {
            $sql = "SELECT i.*, s.status_name, s.is_closed, p.priority_name, g.group_name, u.display_name AS assignee_name
                FROM {$inquiries} i
                LEFT JOIN {$statuses} s ON i.status_id = s.id
                LEFT JOIN {$priorities} p ON i.priority_id = p.id
                LEFT JOIN {$groups} g ON i.assignee_group_id = g.id
                LEFT JOIN {$users} u ON i.assignee_user_id = u.ID
                WHERE {$where_sql}";
        }

        return array('sql' => $sql, 'params' => $params);
    }

    private static function scope_clause($user_id)
    {
        if ($user_id <= 0) {
            return array('sql' => '1=0', 'params' => array());
        }

        if (current_user_can('manage_options') || current_user_can('novajyin_manage_inquiries') || current_user_can('novajyin_view_all_inquiries')) {
            return array('sql' => '', 'params' => array());
        }

        if (current_user_can('novajyin_edit_assigned_inquiries')) {
            $group_ids = self::get_group_ids_for_user($user_id);
            if (!empty($group_ids)) {
                $placeholders = implode(',', array_fill(0, count($group_ids), '%d'));
                return array(
                    'sql' => "(i.assignee_user_id = %d OR i.assignee_group_id IN ({$placeholders}))",
                    'params' => array_merge(array($user_id), array_map('absint', $group_ids)),
                );
            }

            return array('sql' => 'i.assignee_user_id = %d', 'params' => array($user_id));
        }

        return array('sql' => '1=0', 'params' => array());
    }

    private static function prepared_results($sql, $params)
    {
        global $wpdb;
        if (!empty($params)) {
            return $wpdb->get_results($wpdb->prepare($sql, $params));
        }

        return $wpdb->get_results($sql);
    }

    private static function prepared_var($sql, $params)
    {
        global $wpdb;
        if (!empty($params)) {
            return $wpdb->get_var($wpdb->prepare($sql, $params));
        }

        return $wpdb->get_var($sql);
    }

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

        $ids = array_filter(array_map('absint', (array) $ids));
        if (empty($ids)) {
            return 0;
        }

        self::delete_attachment_files($ids);

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

        return (int) $wpdb->query($wpdb->prepare("DELETE FROM " . Novajyin_DB::table('inquiries') . " WHERE id IN ({$placeholders})", $ids));
    }

    private static function delete_attachment_files($ids)
    {
        global $wpdb;

        $ids = array_filter(array_map('absint', (array) $ids));
        if (empty($ids)) {
            return;
        }

        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT attachments_json FROM " . Novajyin_DB::table('inquiries') . " WHERE id IN ({$placeholders})",
                $ids
            )
        );
        $upload_dir = wp_upload_dir();
        $base_dir = wp_normalize_path(trailingslashit($upload_dir['basedir']) . 'nova-jyo-inquiries');

        foreach ($rows as $row) {
            $attachments = self::decode_json($row->attachments_json);
            foreach ($attachments as $attachment) {
                if (empty($attachment['path'])) {
                    continue;
                }
                $path = wp_normalize_path($attachment['path']);
                if (strpos($path, $base_dir) === 0 && is_file($path)) {
                    wp_delete_file($path);
                }
            }
        }
    }

    public static function cleanup_old_inquiries($days)
    {
        global $wpdb;

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

        $threshold = wp_date('Y-m-d H:i:s', current_time('timestamp') - ($days * DAY_IN_SECONDS));
        $ids = $wpdb->get_col(
            $wpdb->prepare(
                "SELECT id FROM " . Novajyin_DB::table('inquiries') . " WHERE submitted_at < %s",
                $threshold
            )
        );

        return self::delete_inquiries($ids);
    }

    public static function add_history($inquiry_id, $history_type, $content, $user_id = 0)
    {
        global $wpdb;

        $content = wp_kses_post($content);
        if (trim(wp_strip_all_tags($content)) === '') {
            return 0;
        }

        $wpdb->insert(
            Novajyin_DB::table('histories'),
            array(
                'inquiry_id' => absint($inquiry_id),
                'user_id' => absint($user_id),
                'history_type' => sanitize_key($history_type),
                'content' => $content,
                'created_at' => self::current_mysql(),
            ),
            array('%d', '%d', '%s', '%s', '%s')
        );

        return (int) $wpdb->insert_id;
    }

    public static function get_histories($inquiry_id)
    {
        global $wpdb;

        $table = Novajyin_DB::table('histories');
        $users = $wpdb->users;

        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT h.*, u.display_name
                FROM {$table} h
                LEFT JOIN {$users} u ON h.user_id = u.ID
                WHERE h.inquiry_id = %d
                ORDER BY h.created_at DESC, h.id DESC",
                $inquiry_id
            )
        );
    }

    public static function add_memo($inquiry_id, $memo_content, $user_id = 0)
    {
        global $wpdb;

        $memo_content = wp_kses_post($memo_content);
        if (trim(wp_strip_all_tags($memo_content)) === '') {
            return 0;
        }

        $wpdb->insert(
            Novajyin_DB::table('memos'),
            array(
                'inquiry_id' => absint($inquiry_id),
                'user_id' => absint($user_id),
                'memo_content' => $memo_content,
                'created_at' => self::current_mysql(),
            ),
            array('%d', '%d', '%s', '%s')
        );

        return (int) $wpdb->insert_id;
    }

    public static function get_memos($inquiry_id)
    {
        global $wpdb;

        $table = Novajyin_DB::table('memos');
        $users = $wpdb->users;

        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT m.*, u.display_name
                FROM {$table} m
                LEFT JOIN {$users} u ON m.user_id = u.ID
                WHERE m.inquiry_id = %d
                ORDER BY m.created_at DESC, m.id DESC",
                $inquiry_id
            )
        );
    }

    public static function get_statuses()
    {
        global $wpdb;
        $table = Novajyin_DB::table('statuses');

        return $wpdb->get_results("SELECT * FROM {$table} ORDER BY sort_order ASC, id ASC");
    }

    public static function get_status($status_id)
    {
        global $wpdb;
        $table = Novajyin_DB::table('statuses');

        return $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table} WHERE id = %d", $status_id));
    }

    public static function save_status($data)
    {
        global $wpdb;

        $table = Novajyin_DB::table('statuses');
        $now = self::current_mysql();
        $id = isset($data['id']) ? absint($data['id']) : 0;
        $is_default = !empty($data['is_default']) ? 1 : 0;

        $row = array(
            'status_name' => sanitize_text_field($data['status_name'] ?? ''),
            'sort_order' => isset($data['sort_order']) ? (int) $data['sort_order'] : 0,
            'is_default' => $is_default,
            'is_closed' => !empty($data['is_closed']) ? 1 : 0,
            'updated_at' => $now,
        );

        if ($row['status_name'] === '') {
            return 0;
        }

        if ($is_default) {
            $wpdb->update($table, array('is_default' => 0), array('is_default' => 1), array('%d'), array('%d'));
        }

        if ($id > 0) {
            $wpdb->update($table, $row, array('id' => $id), array('%s', '%d', '%d', '%d', '%s'), array('%d'));
            return $id;
        }

        $row['created_at'] = $now;
        $wpdb->insert($table, $row, array('%s', '%d', '%d', '%d', '%s', '%s'));
        return (int) $wpdb->insert_id;
    }

    public static function delete_status($id)
    {
        global $wpdb;

        $status = self::get_status(absint($id));
        if (!$status || (int) $status->is_default === 1) {
            return false;
        }

        return (bool) $wpdb->delete(Novajyin_DB::table('statuses'), array('id' => absint($id)), array('%d'));
    }

    public static function get_priorities()
    {
        global $wpdb;
        $table = Novajyin_DB::table('priorities');

        return $wpdb->get_results("SELECT * FROM {$table} ORDER BY sort_order ASC, id ASC");
    }

    public static function get_priority($priority_id)
    {
        global $wpdb;
        $table = Novajyin_DB::table('priorities');

        return $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table} WHERE id = %d", $priority_id));
    }

    public static function save_priority($data)
    {
        global $wpdb;

        $table = Novajyin_DB::table('priorities');
        $now = self::current_mysql();
        $id = isset($data['id']) ? absint($data['id']) : 0;

        $row = array(
            'priority_name' => sanitize_text_field($data['priority_name'] ?? ''),
            'sort_order' => isset($data['sort_order']) ? (int) $data['sort_order'] : 0,
            'updated_at' => $now,
        );

        if ($row['priority_name'] === '') {
            return 0;
        }

        if ($id > 0) {
            $wpdb->update($table, $row, array('id' => $id), array('%s', '%d', '%s'), array('%d'));
            return $id;
        }

        $row['created_at'] = $now;
        $wpdb->insert($table, $row, array('%s', '%d', '%s', '%s'));
        return (int) $wpdb->insert_id;
    }

    public static function delete_priority($id)
    {
        global $wpdb;
        return (bool) $wpdb->delete(Novajyin_DB::table('priorities'), array('id' => absint($id)), array('%d'));
    }

    public static function get_groups($active_only = false)
    {
        global $wpdb;
        $table = Novajyin_DB::table('groups');
        $where = $active_only ? 'WHERE is_active = 1' : '';

        return $wpdb->get_results("SELECT * FROM {$table} {$where} ORDER BY sort_order ASC, id ASC");
    }

    public static function get_group($group_id)
    {
        global $wpdb;
        $table = Novajyin_DB::table('groups');

        return $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table} WHERE id = %d", $group_id));
    }

    public static function save_group($data)
    {
        global $wpdb;

        $table = Novajyin_DB::table('groups');
        $now = self::current_mysql();
        $id = isset($data['id']) ? absint($data['id']) : 0;
        $user_ids = isset($data['user_ids']) ? array_filter(array_map('absint', (array) $data['user_ids'])) : array();

        $row = array(
            'group_name' => sanitize_text_field($data['group_name'] ?? ''),
            'description' => sanitize_textarea_field($data['description'] ?? ''),
            'notification_email' => sanitize_email($data['notification_email'] ?? ''),
            'sort_order' => isset($data['sort_order']) ? (int) $data['sort_order'] : 0,
            'is_active' => !empty($data['is_active']) ? 1 : 0,
            'updated_at' => $now,
        );

        if ($row['group_name'] === '') {
            return 0;
        }

        if ($id > 0) {
            $wpdb->update($table, $row, array('id' => $id), array('%s', '%s', '%s', '%d', '%d', '%s'), array('%d'));
        } else {
            $row['created_at'] = $now;
            $wpdb->insert($table, $row, array('%s', '%s', '%s', '%d', '%d', '%s', '%s'));
            $id = (int) $wpdb->insert_id;
        }

        self::set_group_users($id, $user_ids);
        return $id;
    }

    public static function set_group_users($group_id, $user_ids)
    {
        global $wpdb;

        $group_id = absint($group_id);
        $user_ids = array_unique(array_filter(array_map('absint', (array) $user_ids)));
        $table = Novajyin_DB::table('group_users');

        $wpdb->delete($table, array('group_id' => $group_id), array('%d'));

        foreach ($user_ids as $user_id) {
            $wpdb->insert(
                $table,
                array(
                    'group_id' => $group_id,
                    'user_id' => $user_id,
                    'created_at' => self::current_mysql(),
                ),
                array('%d', '%d', '%s')
            );
        }
    }

    public static function get_group_user_ids($group_id)
    {
        global $wpdb;
        $table = Novajyin_DB::table('group_users');

        return array_map('absint', $wpdb->get_col($wpdb->prepare("SELECT user_id FROM {$table} WHERE group_id = %d", $group_id)));
    }

    public static function get_group_ids_for_user($user_id)
    {
        global $wpdb;
        $table = Novajyin_DB::table('group_users');

        return array_map('absint', $wpdb->get_col($wpdb->prepare("SELECT group_id FROM {$table} WHERE user_id = %d", $user_id)));
    }

    public static function delete_group($id)
    {
        global $wpdb;

        $id = absint($id);
        $wpdb->delete(Novajyin_DB::table('group_users'), array('group_id' => $id), array('%d'));
        return (bool) $wpdb->delete(Novajyin_DB::table('groups'), array('id' => $id), array('%d'));
    }

    public static function get_rules($active_only = false)
    {
        global $wpdb;
        $table = Novajyin_DB::table('rules');
        $where = $active_only ? 'WHERE is_active = 1' : '';

        return $wpdb->get_results("SELECT * FROM {$table} {$where} ORDER BY sort_order ASC, id ASC");
    }

    public static function get_rule($id)
    {
        global $wpdb;
        $table = Novajyin_DB::table('rules');

        return $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table} WHERE id = %d", $id));
    }

    public static function save_rule($data)
    {
        global $wpdb;

        $table = Novajyin_DB::table('rules');
        $now = self::current_mysql();
        $id = isset($data['id']) ? absint($data['id']) : 0;

        $row = array(
            'rule_name' => sanitize_text_field($data['rule_name'] ?? ''),
            'conditions_json' => wp_json_encode($data['conditions'] ?? array(), JSON_UNESCAPED_UNICODE),
            'actions_json' => wp_json_encode($data['actions'] ?? array(), JSON_UNESCAPED_UNICODE),
            'sort_order' => isset($data['sort_order']) ? (int) $data['sort_order'] : 0,
            'is_active' => !empty($data['is_active']) ? 1 : 0,
            'updated_at' => $now,
        );

        if ($row['rule_name'] === '') {
            return 0;
        }

        if ($id > 0) {
            $wpdb->update($table, $row, array('id' => $id), array('%s', '%s', '%s', '%d', '%d', '%s'), array('%d'));
            return $id;
        }

        $row['created_at'] = $now;
        $wpdb->insert($table, $row, array('%s', '%s', '%s', '%d', '%d', '%s', '%s'));
        return (int) $wpdb->insert_id;
    }

    public static function delete_rule($id)
    {
        global $wpdb;
        return (bool) $wpdb->delete(Novajyin_DB::table('rules'), array('id' => absint($id)), array('%d'));
    }

    public static function get_contact_forms()
    {
        $forms = array();

        if (class_exists('WPCF7_ContactForm')) {
            $contact_forms = WPCF7_ContactForm::find(array('posts_per_page' => -1));
            foreach ($contact_forms as $form) {
                $forms[(int) $form->id()] = $form->title();
            }
            return $forms;
        }

        $posts = get_posts(
            array(
                'post_type' => 'wpcf7_contact_form',
                'post_status' => 'any',
                'numberposts' => -1,
                'orderby' => 'title',
                'order' => 'ASC',
            )
        );

        foreach ($posts as $post) {
            $forms[(int) $post->ID] = $post->post_title;
        }

        return $forms;
    }
}





