-- Exams.fit v2 to v3. Back up the database first. Run once from phpMyAdmin.
ALTER TABLE admins ADD last_login DATETIME NULL AFTER locked_until;
ALTER TABLE content ADD canonical_url VARCHAR(1000) NULL AFTER meta_description, ADD quality_score TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER content_hash, ADD INDEX(updated_at);
ALTER TABLE categories ADD image VARCHAR(500) NULL AFTER description, ADD INDEX(type);
ALTER TABLE sections ADD content_types TEXT NULL AFTER category_id;
ALTER TABLE imports ADD items_skipped INT NOT NULL DEFAULT 0 AFTER items_updated, ADD errors LONGTEXT NULL AFTER log, ADD finished_at DATETIME NULL AFTER created_at, ADD INDEX(status,created_at);
ALTER TABLE import_snapshots ADD entity_type VARCHAR(30) NOT NULL DEFAULT 'content' AFTER import_id;
ALTER TABLE feeds ADD allowed_host VARCHAR(255) NOT NULL DEFAULT '' AFTER url, ADD failure_count SMALLINT UNSIGNED NOT NULL DEFAULT 0, ADD INDEX(enabled,next_run);
ALTER TABLE cron_log ADD duration_ms INT UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE reports ADD admin_note TEXT NULL AFTER status, ADD updated_at DATETIME NULL AFTER created_at, ADD INDEX(status,created_at);
CREATE TABLE IF NOT EXISTS content_relations(content_id BIGINT UNSIGNED NOT NULL,related_id BIGINT UNSIGNED NOT NULL,relation VARCHAR(30) NOT NULL DEFAULT 'related',position SMALLINT UNSIGNED NOT NULL DEFAULT 0,PRIMARY KEY(content_id,related_id,relation),INDEX(related_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS analytics_daily(day DATE NOT NULL,event VARCHAR(30) NOT NULL,path VARCHAR(255) NOT NULL,events BIGINT UNSIGNED NOT NULL DEFAULT 0,total_value BIGINT UNSIGNED NOT NULL DEFAULT 0,PRIMARY KEY(day,event,path)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS addon_history(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,addon_id VARCHAR(100) NOT NULL,version VARCHAR(30) NOT NULL,action VARCHAR(30) NOT NULL,manifest LONGTEXT NULL,backup_path VARCHAR(500) NULL,created_at DATETIME NOT NULL,INDEX(addon_id,created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
