<?php
declare(strict_types=1);

namespace Gatecraft;

use WP_User, WP_Error, WP_Session_Tokens, WP_CLI, RuntimeException, Throwable, Exception, DateTime, DateTimeImmutable, ZipArchive;

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

/** Table definitions. {name} becomes {$wpdb->prefix}gatecraft_name. InnoDB is required for row locks. */
function gatecraft_schema(): array
{
    global $wpdb;
    $cs = 'ENGINE=InnoDB ' . $wpdb->get_charset_collate();
    return [
        "CREATE TABLE IF NOT EXISTS {accounts} (
          user_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
          google_sub VARCHAR(64) NULL UNIQUE,
          avatar_url VARCHAR(500) NULL,
          status ENUM('active','disabled') NOT NULL DEFAULT 'active',
          totp_secret_enc VARBINARY(255) NULL,
          totp_enabled TINYINT(1) NOT NULL DEFAULT 0,
          totp_last_step BIGINT NULL,
          last_login_at DATETIME NULL,
          language VARCHAR(8) NULL,
          created_at DATETIME NOT NULL
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {sessions} (
          id CHAR(64) NOT NULL PRIMARY KEY,
          data MEDIUMTEXT NOT NULL,
          expires INT UNSIGNED NOT NULL,
          KEY idx_expires (expires)
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {settings} (
          k VARCHAR(64) PRIMARY KEY,
          v MEDIUMTEXT NOT NULL
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {venues} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          name VARCHAR(190) NOT NULL,
          address VARCHAR(255) NULL, city VARCHAR(120) NULL, region VARCHAR(120) NULL, postal_code VARCHAR(20) NULL, country VARCHAR(80) NULL,
          map_url VARCHAR(500) NULL,
          capacity INT UNSIGNED NOT NULL DEFAULT 0,
          description TEXT NULL, accessibility TEXT NULL, transport TEXT NULL,
          contact_name VARCHAR(120) NULL, contact_email VARCHAR(190) NULL, contact_phone VARCHAR(40) NULL,
          checkin_opens_minutes INT UNSIGNED NOT NULL DEFAULT 60,
          is_active TINYINT(1) NOT NULL DEFAULT 1,
          created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {venue_sections} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          venue_id INT UNSIGNED NOT NULL,
          name VARCHAR(120) NOT NULL,
          capacity INT UNSIGNED NOT NULL DEFAULT 0,
          sort_order INT NOT NULL DEFAULT 0,
          FOREIGN KEY (venue_id) REFERENCES {venues}(id) ON DELETE CASCADE
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {events} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          slug VARCHAR(120) NOT NULL UNIQUE,
          title VARCHAR(190) NOT NULL,
          summary VARCHAR(300) NULL, category VARCHAR(60) NULL, description MEDIUMTEXT NULL,
          venue_id INT UNSIGNED NULL,
          capacity INT UNSIGNED NULL,
          starts_at DATETIME NOT NULL, ends_at DATETIME NULL, doors_at DATETIME NULL,
          cover_image VARCHAR(120) NULL,
          status ENUM('draft','published','cancelled','archived') NOT NULL DEFAULT 'draft',
          visibility ENUM('public','unlisted') NOT NULL DEFAULT 'public',
          max_per_order INT UNSIGNED NULL DEFAULT NULL,
          hero_overlay TINYINT(1) NOT NULL DEFAULT 1,
          bot_check TINYINT(1) NOT NULL DEFAULT 0,
          design TEXT NULL,
          language VARCHAR(8) NULL, series_id INT UNSIGNED NULL,
          terms TEXT NULL, payment_methods VARCHAR(500) NULL, payment_details TEXT NULL,
          reminder_sent_at DATETIME NULL, capacity_alerted_at DATETIME NULL, email_note VARCHAR(1000) NULL,
          created_by BIGINT UNSIGNED NULL,
          created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
          KEY idx_status_start (status, starts_at), KEY idx_series (series_id, starts_at),
          FOREIGN KEY (venue_id) REFERENCES {venues}(id) ON DELETE SET NULL
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {event_announcements} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          event_id INT UNSIGNED NOT NULL,
          title VARCHAR(190) NOT NULL,
          body TEXT NULL,
          level ENUM('info','important','urgent') NOT NULL DEFAULT 'info',
          is_active TINYINT(1) NOT NULL DEFAULT 1,
          show_from DATETIME NULL, show_until DATETIME NULL,
          created_by BIGINT UNSIGNED NULL,
          created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
          KEY idx_event_live (event_id, is_active),
          FOREIGN KEY (event_id) REFERENCES {events}(id) ON DELETE CASCADE
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {event_sponsors} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          event_id INT UNSIGNED NOT NULL,
          name VARCHAR(160) NOT NULL,
          tier VARCHAR(60) NULL,
          info VARCHAR(1500) NULL,
          website VARCHAR(500) NULL,
          logo VARCHAR(64) NULL,
          image VARCHAR(64) NULL,
          sort_order INT NOT NULL DEFAULT 0,
          is_active TINYINT(1) NOT NULL DEFAULT 1,
          created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
          KEY idx_event_order (event_id, is_active, sort_order),
          FOREIGN KEY (event_id) REFERENCES {events}(id) ON DELETE CASCADE
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {ticket_categories} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          event_id INT UNSIGNED NOT NULL,
          venue_section_id INT UNSIGNED NULL,
          name VARCHAR(120) NOT NULL, description VARCHAR(500) NULL, color CHAR(7) NULL,
          capacity INT UNSIGNED NOT NULL,
          requires_approval TINYINT(1) NOT NULL DEFAULT 0,
          min_per_order INT UNSIGNED NOT NULL DEFAULT 1, max_per_order INT UNSIGNED NULL DEFAULT NULL,
          is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0,
          FOREIGN KEY (event_id) REFERENCES {events}(id) ON DELETE CASCADE,
          FOREIGN KEY (venue_section_id) REFERENCES {venue_sections}(id) ON DELETE SET NULL
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {ticket_tiers} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          category_id INT UNSIGNED NOT NULL,
          name VARCHAR(120) NOT NULL,
          price INT UNSIGNED NOT NULL DEFAULT 0, quantity INT UNSIGNED NULL,
          sale_start DATETIME NULL, sale_end DATETIME NULL,
          is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0,
          FOREIGN KEY (category_id) REFERENCES {ticket_categories}(id) ON DELETE CASCADE
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {event_questions} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          event_id INT UNSIGNED NOT NULL, category_id INT UNSIGNED NULL,
          label VARCHAR(190) NOT NULL,
          type ENUM('text','textarea','select','checkbox') NOT NULL DEFAULT 'text',
          options TEXT NULL, required TINYINT(1) NOT NULL DEFAULT 0, sort_order INT NOT NULL DEFAULT 0,
          FOREIGN KEY (event_id) REFERENCES {events}(id) ON DELETE CASCADE,
          FOREIGN KEY (category_id) REFERENCES {ticket_categories}(id) ON DELETE CASCADE
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {discounts} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          event_id INT UNSIGNED NULL, category_id INT UNSIGNED NULL,
          name VARCHAR(120) NOT NULL, code VARCHAR(40) NULL UNIQUE,
          auto_apply TINYINT(1) NOT NULL DEFAULT 0,
          type ENUM('percent','fixed') NOT NULL DEFAULT 'percent', value INT UNSIGNED NOT NULL,
          applies_per ENUM('order','ticket') NOT NULL DEFAULT 'order',
          min_qty INT UNSIGNED NOT NULL DEFAULT 1, max_uses INT UNSIGNED NULL, max_per_email INT UNSIGNED NULL,
          starts_at DATETIME NULL, ends_at DATETIME NULL,
          is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL,
          FOREIGN KEY (event_id) REFERENCES {events}(id) ON DELETE CASCADE,
          FOREIGN KEY (category_id) REFERENCES {ticket_categories}(id) ON DELETE CASCADE
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {orders} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          reference VARCHAR(12) NOT NULL UNIQUE,
          public_token CHAR(64) NOT NULL UNIQUE,
          event_id INT UNSIGNED NOT NULL,
          user_id BIGINT UNSIGNED NULL,
          buyer_name VARCHAR(120) NOT NULL, buyer_email VARCHAR(190) NOT NULL, buyer_phone VARCHAR(40) NULL,
          subtotal INT UNSIGNED NOT NULL, discount_amount INT UNSIGNED NOT NULL DEFAULT 0, total INT UNSIGNED NOT NULL,
          currency CHAR(3) NOT NULL,
          discount_id INT UNSIGNED NULL,
          status ENUM('awaiting_approval','pending_payment','paid','rejected','cancelled','expired','refunded') NOT NULL,
          answers TEXT NULL, decision_note VARCHAR(500) NULL, decided_by BIGINT UNSIGNED NULL, decided_at DATETIME NULL,
          expires_at DATETIME NULL,
          payment_method ENUM('free','offline','stripe','comp','cash','door_card','imported') NULL, stripe_session_id VARCHAR(255) NULL, created_by BIGINT UNSIGNED NULL,
          import_source VARCHAR(40) NULL, external_ref VARCHAR(100) NULL,
          pay_option VARCHAR(80) NULL, pay_instructions TEXT NULL, ip_hash CHAR(16) NULL,
          tax_mode VARCHAR(10) NULL, tax_rate DECIMAL(5,2) NULL, tax_label VARCHAR(60) NULL, tax_amount INT UNSIGNED NOT NULL DEFAULT 0,
          buyer_company VARCHAR(190) NULL, buyer_tax_id VARCHAR(40) NULL, buyer_address VARCHAR(500) NULL,
          invoice_number VARCHAR(40) NULL UNIQUE, invoiced_at DATETIME NULL, invoice_meta TEXT NULL, language VARCHAR(8) NULL,
          paid_at DATETIME NULL, closed_at DATETIME NULL, created_at DATETIME NOT NULL,
          KEY idx_event_status (event_id, status), KEY idx_email (buyer_email), KEY idx_user (user_id), KEY idx_status_exp (status, expires_at), KEY idx_event_ip (event_id, ip_hash), UNIQUE KEY uniq_import (import_source, external_ref),
          FOREIGN KEY (event_id) REFERENCES {events}(id),
          FOREIGN KEY (discount_id) REFERENCES {discounts}(id) ON DELETE SET NULL
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {order_items} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          order_id INT UNSIGNED NOT NULL, category_id INT UNSIGNED NOT NULL, tier_id INT UNSIGNED NOT NULL,
          qty INT UNSIGNED NOT NULL, unit_price INT UNSIGNED NOT NULL,
          KEY idx_cat (category_id), KEY idx_tier (tier_id),
          FOREIGN KEY (order_id) REFERENCES {orders}(id) ON DELETE CASCADE,
          FOREIGN KEY (category_id) REFERENCES {ticket_categories}(id),
          FOREIGN KEY (tier_id) REFERENCES {ticket_tiers}(id)
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {tickets} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          order_id INT UNSIGNED NOT NULL, category_id INT UNSIGNED NOT NULL, tier_id INT UNSIGNED NOT NULL,
          code CHAR(16) NOT NULL UNIQUE,
          status ENUM('valid','void') NOT NULL DEFAULT 'valid',
          checked_in_at DATETIME NULL, checked_in_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL,
          FOREIGN KEY (order_id) REFERENCES {orders}(id) ON DELETE CASCADE,
          FOREIGN KEY (category_id) REFERENCES {ticket_categories}(id),
          FOREIGN KEY (tier_id) REFERENCES {ticket_tiers}(id)
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {checkin_scans} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          event_id INT UNSIGNED NULL, ticket_id INT UNSIGNED NULL, user_id BIGINT UNSIGNED NULL,
          result ENUM('ok','duplicate','invalid','wrong_event','void') NOT NULL, created_at DATETIME NOT NULL,
          KEY idx_event (event_id, created_at)
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {email_queue} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          to_email VARCHAR(190) NOT NULL, to_name VARCHAR(120) NOT NULL DEFAULT '', subject VARCHAR(255) NOT NULL,
          html MEDIUMTEXT NOT NULL, text_body MEDIUMTEXT NOT NULL, meta TEXT NULL,
          status ENUM('queued','sending','sent','failed','cancelled') NOT NULL DEFAULT 'queued',
          attempts INT UNSIGNED NOT NULL DEFAULT 0, last_error VARCHAR(500) NULL,
          send_after DATETIME NOT NULL, sent_at DATETIME NULL, created_at DATETIME NOT NULL, campaign_id INT UNSIGNED NULL,
          KEY idx_status (status, send_after), KEY idx_campaign (campaign_id)
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {notifications} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          user_id BIGINT UNSIGNED NOT NULL, title VARCHAR(190) NOT NULL, body VARCHAR(500) NULL, link VARCHAR(255) NULL,
          read_at DATETIME NULL, created_at DATETIME NOT NULL,
          KEY idx_user_read (user_id, read_at)
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {audit_log} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          user_id BIGINT UNSIGNED NULL, action VARCHAR(80) NOT NULL, entity VARCHAR(40) NULL, entity_id INT UNSIGNED NULL,
          details TEXT NULL, ip VARCHAR(45) NULL, created_at DATETIME NOT NULL,
          KEY idx_created (created_at), KEY idx_entity (entity, entity_id)
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {contacts} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          email VARCHAR(190) NOT NULL UNIQUE,
          name VARCHAR(120) NOT NULL DEFAULT '',
          language VARCHAR(8) NULL,
          status ENUM('subscribed','unsubscribed') NOT NULL DEFAULT 'subscribed',
          source VARCHAR(20) NOT NULL DEFAULT 'checkout',
          consent_at DATETIME NULL, consent_text VARCHAR(500) NULL, unsubscribed_at DATETIME NULL,
          tags VARCHAR(500) NOT NULL DEFAULT '', notes TEXT NULL,
          orders_count INT UNSIGNED NOT NULL DEFAULT 0, tickets_count INT UNSIGNED NOT NULL DEFAULT 0, total_spent INT UNSIGNED NOT NULL DEFAULT 0,
          first_order_at DATETIME NULL, last_order_at DATETIME NULL,
          created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
          KEY idx_status (status), KEY idx_last (last_order_at)
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {campaigns} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          subject VARCHAR(255) NOT NULL, body MEDIUMTEXT NOT NULL,
          purpose ENUM('news','event') NOT NULL, filters TEXT NULL, event_id INT UNSIGNED NULL,
          recipients INT UNSIGNED NOT NULL DEFAULT 0,
          status ENUM('sent','scheduled','cancelled') NOT NULL DEFAULT 'sent', send_at DATETIME NULL,
          created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL, sent_at DATETIME NULL
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {segments} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          name VARCHAR(120) NOT NULL, filters TEXT NOT NULL, created_at DATETIME NOT NULL
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {event_series} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          name VARCHAR(190) NULL, rule TEXT NULL,
          created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {waitlist} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          event_id INT UNSIGNED NOT NULL, category_id INT UNSIGNED NULL,
          name VARCHAR(120) NOT NULL, email VARCHAR(190) NOT NULL, qty INT UNSIGNED NOT NULL DEFAULT 1,
          status ENUM('waiting','notified','booked','lapsed','removed') NOT NULL DEFAULT 'waiting',
          token CHAR(32) NOT NULL UNIQUE, language VARCHAR(8) NULL,
          notified_at DATETIME NULL, created_at DATETIME NOT NULL,
          UNIQUE KEY uniq_event_email (event_id, email), KEY idx_event_status (event_id, status, id),
          FOREIGN KEY (event_id) REFERENCES {events}(id) ON DELETE CASCADE
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {trusted_browsers} (
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          user_id BIGINT UNSIGNED NOT NULL,
          token_hash CHAR(64) NOT NULL UNIQUE,
          user_agent VARCHAR(255) NULL,
          created_at DATETIME NOT NULL, expires_at DATETIME NOT NULL, last_used_at DATETIME NULL,
          KEY idx_user (user_id)
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {counters} (
          name VARCHAR(40) NOT NULL PRIMARY KEY,
          value INT UNSIGNED NOT NULL
        ) $cs",
        "CREATE TABLE IF NOT EXISTS {rate_limits} (
          k CHAR(64) PRIMARY KEY,
          hits INT UNSIGNED NOT NULL,
          window_start INT UNSIGNED NOT NULL
        ) $cs",
    ];
}

const GATECRAFT_TABLES = ['waitlist', 'event_series', 'segments', 'campaigns', 'contacts', 'trusted_browsers', 'counters', 'rate_limits', 'audit_log', 'notifications', 'email_queue', 'checkin_scans', 'tickets', 'order_items', 'orders', 'discounts',
    'event_questions', 'event_announcements', 'event_sponsors', 'ticket_tiers', 'ticket_categories', 'events', 'venue_sections', 'venues', 'settings', 'sessions', 'accounts'];

/**
 * One-time import from the earlier private plugin ("Event Ticketing", table prefix evtix_): if its tables exist
 * and Gatecraft has none yet, rename them and carry over options, roles and uploaded images. Nothing is lost
 * and nothing is copied twice.
 */
function gatecraft_import_previous(): void
{
    global $wpdb;
    $old = $wpdb->prefix . 'evtix_';
    if (get_option('gatecraft_db_version') || !qv('SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?', [$old . 'orders'])) return;
    if (qv('SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?', [tbl('orders')])) return;
    $pairs = [];
    foreach (GATECRAFT_TABLES as $t) {
        if (qv('SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?', [$old . $t])) $pairs[] = '`' . $old . $t . '` TO `' . tbl($t) . '`';
    }
    if ($pairs) q('RENAME TABLE ' . implode(', ', $pairs));
    update_option('gatecraft_db_version', (int)get_option('evtix_db_version', 1), false);
    foreach (['sheets_status', 'update_status'] as $k) if (($v = get_option('evtix_' . $k)) !== false) update_option('gatecraft_' . $k, $v, false);
    // Staff roles and capabilities.
    $roleMap = ['evtix_manager' => 'gatecraft_manager', 'evtix_checkin' => 'gatecraft_checkin'];
    add_role('gatecraft_manager', 'Ticketing manager', ['read' => true, 'gatecraft_checkin' => true, 'gatecraft_manage' => true]);
    add_role('gatecraft_checkin', 'Ticketing check-in', ['read' => true, 'gatecraft_checkin' => true]);
    foreach ($roleMap as $from => $to) {
        foreach (get_users(['role' => $from, 'fields' => 'all']) as $u) { $u->add_role($to); $u->remove_role($from); }
        remove_role($from);
    }
    foreach (wp_roles()->role_objects as $role) foreach (['checkin', 'manage', 'admin'] as $c) if ($role->has_cap('evtix_' . $c)) { $role->add_cap('gatecraft_' . $c); $role->remove_cap('evtix_' . $c); }
    // Uploaded logos and covers.
    $up = wp_upload_dir(null, false);
    if (is_dir($up['basedir'] . '/evtix') && !is_dir($up['basedir'] . '/gatecraft')) {
        global $wp_filesystem;
        if (!function_exists('WP_Filesystem')) require_once ABSPATH . 'wp-admin/includes/file.php';
        if (WP_Filesystem()) $wp_filesystem->move($up['basedir'] . '/evtix', $up['basedir'] . '/gatecraft');
    }
    wp_clear_scheduled_hook('evtix_worker');
}

function gatecraft_install(): void
{
    gatecraft_import_previous();
    $from = (int)get_option('gatecraft_db_version', 0);
    foreach (gatecraft_schema() as $sql) q($sql);
    gatecraft_migrate($from);
    update_option('gatecraft_db_version', GATECRAFT_DB_VERSION, false);

    // Roles. WordPress administrators get every ticketing capability.
    add_role('gatecraft_manager', 'Ticketing manager', ['read' => true, 'gatecraft_checkin' => true, 'gatecraft_manage' => true]);
    add_role('gatecraft_checkin', 'Ticketing check-in', ['read' => true, 'gatecraft_checkin' => true]);
    $admin = get_role('administrator');
    if ($admin) foreach (['gatecraft_checkin', 'gatecraft_manage', 'gatecraft_admin'] as $cap) $admin->add_cap($cap);
    gatecraft_uploads();
}

function gatecraft_activate(): void
{
    gatecraft_install();
    gatecraft_register_rewrites();
    flush_rewrite_rules();
    gatecraft_schedule_worker();
}

function gatecraft_deactivate(): void
{
    wp_clear_scheduled_hook('gatecraft_worker');
    wp_clear_scheduled_hook('gatecraft_worker', ['soon']);
    flush_rewrite_rules();
}

function gatecraft_column_exists(string $table, string $column): bool
{
    return (bool)qv('SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?', [tbl($table), $column]);
}

/** Changes to existing installs, one step per database version. Each step is safe to run twice. */
function gatecraft_migrate(int $from): void
{
    // Version 18 has no table changes. Raising the number makes existing sites run gatecraft_install() once after the
    // update, which completes the upload folder (gatecraft_uploads()).
    if ($from < 2) {
        // Offline payment methods per event, and one price per ticket category.
        if (!gatecraft_column_exists('events', 'payment_methods')) q('ALTER TABLE {events} ADD COLUMN payment_methods VARCHAR(500) NULL AFTER terms');
        if (!gatecraft_column_exists('orders', 'pay_option')) q('ALTER TABLE {orders} ADD COLUMN pay_option VARCHAR(80) NULL AFTER stripe_session_id, ADD COLUMN pay_instructions TEXT NULL AFTER pay_option');
        if ($from > 0) {
            // Keep the price that is on sale right now as the category's single price.
            foreach (q("SELECT * FROM {events} WHERE status IN ('draft','published')")->fetchAll() as $ev) {
                foreach (event_offer($ev) as $c) {
                    $keep = $c['tier']['id'] ?? qv('SELECT id FROM {ticket_tiers} WHERE category_id = ? ORDER BY sort_order, id LIMIT 1', [$c['id']]);
                    if (!$keep) continue;
                    q('UPDATE {ticket_tiers} SET sort_order = sort_order + 1 WHERE category_id = ? AND id <> ?', [$c['id'], $keep]);
                    q('UPDATE {ticket_tiers} SET sort_order = 0 WHERE id = ?', [$keep]);
                    set_category_price((int)$c['id'], (int)qv('SELECT price FROM {ticket_tiers} WHERE id = ?', [$keep]));
                }
            }
        }
    }
    if ($from < 3) {
        // Rich descriptions with images and video can run past the 64 KB a TEXT column holds.
        q('ALTER TABLE {events} MODIFY COLUMN description MEDIUMTEXT NULL');
    }
    if ($from < 5) {
        // The limit per booking for a whole event is optional now, and the banner overlay can be switched off per event.
        q('ALTER TABLE {events} MODIFY COLUMN max_per_order INT UNSIGNED NULL DEFAULT NULL');
        if (!gatecraft_column_exists('events', 'hero_overlay')) q('ALTER TABLE {events} ADD COLUMN hero_overlay TINYINT(1) NOT NULL DEFAULT 1 AFTER cover_image');
    }
    if ($from < 6) {
        if (!gatecraft_column_exists('events', 'design')) q('ALTER TABLE {events} ADD COLUMN design TEXT NULL AFTER hero_overlay');
    }
    if ($from < 7) {
        // A hashed network address on each booking, for the limit on open bookings per connection.
        if (!gatecraft_column_exists('orders', 'ip_hash')) q('ALTER TABLE {orders} ADD COLUMN ip_hash CHAR(16) NULL AFTER pay_instructions, ADD KEY idx_event_ip (event_id, ip_hash)');
    }
    if ($from < 8) {
        // An optional colour per ticket category, shown on its card on the event page.
        if (!gatecraft_column_exists('ticket_categories', 'color')) q('ALTER TABLE {ticket_categories} ADD COLUMN color CHAR(7) NULL AFTER description');
    }
    if ($from < 17) {
        // High-demand protection: a person check (Cloudflare Turnstile) at checkout, switched on per event.
        if (!gatecraft_column_exists('events', 'bot_check')) q('ALTER TABLE {events} ADD COLUMN bot_check TINYINT(1) NOT NULL DEFAULT 0 AFTER hero_overlay');
    }
    if ($from < 16 && $from > 0) {
        // 4.0 changed the default colour to the Gatecraft brand. Sites that never picked a colour keep the one they have been showing.
        if (qv("SELECT COUNT(*) FROM {settings} WHERE k = 'brand_color'") == 0) q("INSERT INTO {settings} (k, v) VALUES ('brand_color', '#4f46e5')");
        // The event page colours also moved to the brand. Keep the earlier ones on sites that never chose their own.
        $d = json_decode((string)qv("SELECT v FROM {settings} WHERE k = 'event_design'"), true);
        $d = is_array($d) ? $d : [];
        $d += ['accent2' => '#ff7a59', 'hero_label' => '#fcd34d', 'hero_panel' => '#0c1020', 'cta_from' => '#f59e0b', 'cta_to' => '#ef4444', 'tile_from' => '#f59e0b', 'tile_to' => '#ef4444'];
        q("INSERT INTO {settings} (k, v) VALUES ('event_design', ?) ON DUPLICATE KEY UPDATE v = VALUES(v)", [wp_json_encode($d)]);
    }
    if ($from < 15) {
        // Bookings imported from other ticketing platforms, kept for records and reports.
        q("ALTER TABLE {orders} MODIFY COLUMN payment_method ENUM('free','offline','stripe','comp','cash','door_card','imported') NULL");
        if (!gatecraft_column_exists('orders', 'external_ref')) q('ALTER TABLE {orders} ADD COLUMN import_source VARCHAR(40) NULL, ADD COLUMN external_ref VARCHAR(100) NULL, ADD UNIQUE KEY uniq_import (import_source, external_ref)');
    }
    if ($from < 14) {
        // Payment details (bank account, PayPay ID...) per event, replacing the defaults from Settings.
        if (!gatecraft_column_exists('events', 'payment_details')) q('ALTER TABLE {events} ADD COLUMN payment_details TEXT NULL AFTER payment_methods');
    }
    if ($from < 13) {
        // 3.0: bookings made by staff (guest list, door sales), repeating events, waiting lists.
        // The full list, including later additions: on a fresh install this runs after the newer steps.
        q("ALTER TABLE {orders} MODIFY COLUMN payment_method ENUM('free','offline','stripe','comp','cash','door_card','imported') NULL");
        if (!gatecraft_column_exists('orders', 'created_by')) q('ALTER TABLE {orders} ADD COLUMN created_by BIGINT UNSIGNED NULL AFTER stripe_session_id');
        if (!gatecraft_column_exists('events', 'series_id')) q('ALTER TABLE {events} ADD COLUMN series_id INT UNSIGNED NULL AFTER language, ADD KEY idx_series (series_id, starts_at)');
    }
    if ($from < 12) {
        // A ticket category's maximum per booking is optional: empty means no limit.
        q('ALTER TABLE {ticket_categories} MODIFY COLUMN max_per_order INT UNSIGNED NULL DEFAULT NULL');
    }
    if ($from < 11) {
        // Messages to the audience are tracked per email, and can be stopped before they go out.
        if (!gatecraft_column_exists('email_queue', 'campaign_id')) q("ALTER TABLE {email_queue} MODIFY COLUMN status ENUM('queued','sending','sent','failed','cancelled') NOT NULL DEFAULT 'queued', ADD COLUMN campaign_id INT UNSIGNED NULL, ADD KEY idx_campaign (campaign_id)");
    }
    if ($from < 9) {
        // Tax lines and invoice numbers on bookings, and a language per event, booking and account.
        if (!gatecraft_column_exists('orders', 'tax_mode')) q('ALTER TABLE {orders}
            ADD COLUMN tax_mode VARCHAR(10) NULL AFTER ip_hash, ADD COLUMN tax_rate DECIMAL(5,2) NULL AFTER tax_mode,
            ADD COLUMN tax_label VARCHAR(60) NULL AFTER tax_rate, ADD COLUMN tax_amount INT UNSIGNED NOT NULL DEFAULT 0 AFTER tax_label,
            ADD COLUMN buyer_company VARCHAR(190) NULL AFTER tax_amount, ADD COLUMN buyer_tax_id VARCHAR(40) NULL AFTER buyer_company,
            ADD COLUMN buyer_address VARCHAR(500) NULL AFTER buyer_tax_id, ADD COLUMN invoice_number VARCHAR(40) NULL UNIQUE AFTER buyer_address,
            ADD COLUMN invoiced_at DATETIME NULL AFTER invoice_number, ADD COLUMN invoice_meta TEXT NULL AFTER invoiced_at,
            ADD COLUMN language VARCHAR(8) NULL AFTER invoice_meta');
        if (!gatecraft_column_exists('events', 'language')) q('ALTER TABLE {events} ADD COLUMN language VARCHAR(8) NULL AFTER design');
        foreach (['accounts', 'users'] as $t) {
            if (qv('SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?', [tbl($t)]) && !gatecraft_column_exists($t, 'language')) {
                q("ALTER TABLE {{$t}} ADD COLUMN language VARCHAR(8) NULL");
            }
        }
    }
}

/** Runs on every load: upgrades the tables after a plugin update. */
function gatecraft_maybe_upgrade(): void
{
    if ((int)get_option('gatecraft_db_version') >= GATECRAFT_DB_VERSION) return;
    // Several requests can arrive right after an update. Only one upgrades the tables; the others carry on without waiting.
    if ((int)qv('SELECT GET_LOCK(?, 0)', ['gatecraft_upgrade']) !== 1) return;
    try {
        wp_cache_delete('gatecraft_db_version', 'options'); wp_cache_delete('alloptions', 'options');
        if ((int)get_option('gatecraft_db_version') < GATECRAFT_DB_VERSION) gatecraft_install();
    } finally {
        qv('SELECT RELEASE_LOCK(?)', ['gatecraft_upgrade']);
    }
}