ALTER TABLE users ADD COLUMN recovery_codes_json JSON NULL AFTER two_factor_enabled;
CREATE TABLE IF NOT EXISTS entry_files (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, entry_id BIGINT UNSIGNED NOT NULL, field_name VARCHAR(190) NOT NULL,
 original_name VARCHAR(255) NOT NULL, stored_name VARCHAR(255) NOT NULL, mime_type VARCHAR(120) NOT NULL,
 file_size BIGINT UNSIGNED NOT NULL, sha256 CHAR(64) NOT NULL, created_at DATETIME NOT NULL,
 CONSTRAINT fk_file_entry FOREIGN KEY (entry_id) REFERENCES entries(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS privacy_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, entry_id BIGINT UNSIGNED NULL, email VARCHAR(190) NOT NULL,
 request_type ENUM('access','correction','deletion','export') NOT NULL, status ENUM('open','in_progress','completed','rejected') NOT NULL DEFAULT 'open',
 notes TEXT NULL, requested_at DATETIME NOT NULL, completed_at DATETIME NULL, handled_by BIGINT UNSIGNED NULL,
 CONSTRAINT fk_priv_entry FOREIGN KEY (entry_id) REFERENCES entries(id) ON DELETE SET NULL,
 CONSTRAINT fk_priv_user FOREIGN KEY (handled_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE draws ADD COLUMN draw_number INT NOT NULL DEFAULT 1 AFTER draw_token;
ALTER TABLE draws ADD COLUMN supersedes_draw_id BIGINT UNSIGNED NULL AFTER draw_number;
ALTER TABLE draws ADD COLUMN status ENUM('current','superseded','cancelled') NOT NULL DEFAULT 'current' AFTER supersedes_draw_id;
ALTER TABLE draws ADD COLUMN reason TEXT NULL AFTER status;
ALTER TABLE draws ADD COLUMN certificate_no VARCHAR(64) NULL AFTER notes;
CREATE TABLE IF NOT EXISTS backups (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, filename VARCHAR(255) NOT NULL, file_size BIGINT UNSIGNED NOT NULL,
 sha256 CHAR(64) NOT NULL, created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL,
 CONSTRAINT fk_backup_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;