CREATE TABLE IF NOT EXISTS email_verifications (
 token_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin PRIMARY KEY,
 user_serial BIGINT UNSIGNED NOT NULL,
 email VARCHAR(100) NOT NULL,
 auth_version INT UNSIGNED NOT NULL,
 expires_at DATETIME NOT NULL,
 INDEX(user_serial), INDEX(expires_at),
 FOREIGN KEY(user_serial) REFERENCES users(user_serial) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS club_due_adjustments (
 adjustment_serial BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 due_serial BIGINT UNSIGNED NOT NULL,
 amount_cents BIGINT NOT NULL,
 corrected_amount_cents BIGINT UNSIGNED NOT NULL,
 reason VARCHAR(500) NOT NULL,
 recorded_by BIGINT UNSIGNED NOT NULL,
 request_key CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL UNIQUE,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(due_serial) REFERENCES club_dues(due_serial) ON DELETE RESTRICT,
 FOREIGN KEY(recorded_by) REFERENCES users(user_serial) ON DELETE RESTRICT,
 CHECK(amount_cents<>0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS club_payment_reversals (
 reversal_serial BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 payment_serial BIGINT UNSIGNED NOT NULL UNIQUE,
 reason VARCHAR(500) NOT NULL,
 recorded_by BIGINT UNSIGNED NOT NULL,
 request_key CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL UNIQUE,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(payment_serial) REFERENCES club_payments(payment_serial) ON DELETE RESTRICT,
 FOREIGN KEY(recorded_by) REFERENCES users(user_serial) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;