tls-web-push/setup-tables.php at main · newsuk/tls-web-push · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
<?php
/**
 * Manual Database Setup
 * Visit: https://cod.local/wp-content/plugins/tls-web-push/setup-tables.php
 */

// Load WordPress
require_once('../../../wp-load.php');

// Check if user is admin
if (!current_user_can('manage_options')) {
    die('Access denied');
}

echo '<h1>TLS Web Push - Manual Database Setup</h1>';
echo '<style>body { font-family: sans-serif; padding: 20px; } pre { background: #f5f5f5; padding: 10px; } .success { color: green; } .error { color: red; }</style>';

global $wpdb;
$charset_collate = $wpdb->get_charset_collate();

// Subscriptions table
$subscriptions_table = $wpdb->prefix . 'tls_push_subscriptions';
$sql_subscriptions = "CREATE TABLE IF NOT EXISTS $subscriptions_table (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    endpoint text NOT NULL,
    auth_key varchar(255) NOT NULL,
    p256dh_key varchar(255) NOT NULL,
    user_agent text,
    ip_address varchar(45),
    created_at datetime DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY endpoint_index (endpoint(191))
) $charset_collate;";

// Notifications table
$notifications_table = $wpdb->prefix . 'tls_push_notifications';
$sql_notifications = "CREATE TABLE IF NOT EXISTS $notifications_table (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    title varchar(255) NOT NULL,
    message text NOT NULL,
    url text,
    icon text,
    sent_count int(11) DEFAULT 0,
    failed_count int(11) DEFAULT 0,
    click_count int(11) DEFAULT 0,
    created_at datetime DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) $charset_collate;";

// Analytics table
$analytics_table = $wpdb->prefix . 'tls_push_analytics';
$sql_analytics = "CREATE TABLE IF NOT EXISTS $analytics_table (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    event_type varchar(50) NOT NULL,
    notification_id bigint(20) DEFAULT NULL,
    subscription_id bigint(20) DEFAULT NULL,
    event_data text,
    created_at datetime DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY event_type_index (event_type),
    KEY notification_id_index (notification_id)
) $charset_collate;";

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

echo '<h2>Creating Tables...</h2>';

echo '<h3>1. Subscriptions Table</h3>';
$result1 = dbDelta($sql_subscriptions);
echo '<pre>' . print_r($result1, true) . '</pre>';

echo '<h3>2. Notifications Table</h3>';
$result2 = dbDelta($sql_notifications);
echo '<pre>' . print_r($result2, true) . '</pre>';

echo '<h3>3. Analytics Table</h3>';
$result3 = dbDelta($sql_analytics);
echo '<pre>' . print_r($result3, true) . '</pre>';

// Verify tables exist
echo '<h2>Verification</h2>';

$tables = array(
    $subscriptions_table,
    $notifications_table,
    $analytics_table
);

foreach ($tables as $table) {
    $exists = $wpdb->get_var("SHOW TABLES LIKE '$table'");
    if ($exists) {
        $count = $wpdb->get_var("SELECT COUNT(*) FROM $table");
        echo '<p class="success">✓ Table <strong>' . $table . '</strong> exists (Rows: ' . $count . ')</p>';
    } else {
        echo '<p class="error">✗ Table <strong>' . $table . '</strong> NOT found</p>';
    }
}

echo '<hr>';
echo '<p><a href="' . admin_url('admin.php?page=tls-web-push') . '">Go to Web Push Dashboard</a></p>';
?>