SET @account_type_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users' AND COLUMN_NAME='account_type');
SET @account_type_sql = IF(@account_type_exists=0, "ALTER TABLE users ADD COLUMN account_type VARCHAR(16) NOT NULL DEFAULT 'member' AFTER role", 'SELECT 1');
PREPARE stmt FROM @account_type_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @trust_level_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users' AND COLUMN_NAME='trust_level');
SET @trust_level_sql = IF(@trust_level_exists=0, "ALTER TABLE users ADD COLUMN trust_level TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER account_type", 'SELECT 1');
PREPARE stmt FROM @trust_level_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @login_disabled_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users' AND COLUMN_NAME='login_disabled');
SET @login_disabled_sql = IF(@login_disabled_exists=0, "ALTER TABLE users ADD COLUMN login_disabled TINYINT(1) NOT NULL DEFAULT 0 AFTER trust_level", 'SELECT 1');
PREPARE stmt FROM @login_disabled_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @created_by_admin_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users' AND COLUMN_NAME='created_by_admin_id');
SET @created_by_admin_sql = IF(@created_by_admin_exists=0, "ALTER TABLE users ADD COLUMN created_by_admin_id BIGINT UNSIGNED NULL AFTER login_disabled, ADD KEY idx_users_created_by_admin (created_by_admin_id)", 'SELECT 1');
PREPARE stmt FROM @created_by_admin_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @created_by_fk_exists = (SELECT COUNT(*) FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='users' AND CONSTRAINT_NAME='fk_users_created_by_admin');
SET @created_by_fk_sql = IF(@created_by_fk_exists=0, "ALTER TABLE users ADD CONSTRAINT fk_users_created_by_admin FOREIGN KEY (created_by_admin_id) REFERENCES users(id) ON DELETE SET NULL", 'SELECT 1');
PREPARE stmt FROM @created_by_fk_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


SET @answer_status_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='answers' AND COLUMN_NAME='status');
SET @answer_status_sql = IF(@answer_status_exists=0, "ALTER TABLE answers ADD COLUMN status VARCHAR(32) NOT NULL DEFAULT 'published' AFTER body, ADD KEY idx_answers_status_created (status, created_at)", 'SELECT 1');
PREPARE stmt FROM @answer_status_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

INSERT INTO settings (`key`,value,type,is_secret,created_at,updated_at) VALUES
('community.spam_moderate_score','35','integer',0,NOW(),NOW()),
('community.spam_reject_score','80','integer',0,NOW(),NOW()),
('security.turnstile_enabled','0','boolean',0,NOW(),NOW()),
('security.turnstile_site_key','','string',0,NOW(),NOW()),
('security.turnstile_secret_key','','string',1,NOW(),NOW())
ON DUPLICATE KEY UPDATE `key`=VALUES(`key`);
