-- Upgrade v1.1.0 installations. Back up the database first. Run statements once.
ALTER TABLE content ADD parent_id BIGINT UNSIGNED NULL AFTER category_id, ADD ad_free TINYINT(1) NOT NULL DEFAULT 0 AFTER featured, ADD reviewer VARCHAR(190) NULL AFTER author, ADD INDEX(parent_id), ADD INDEX(content_hash);
ALTER TABLE content MODIFY status ENUM('draft','validation_failed','needs_review','scheduled','published') NOT NULL DEFAULT 'draft';
ALTER TABLE feeds ADD external_id VARCHAR(64) UNIQUE NULL AFTER id, ADD kind VARCHAR(40) NOT NULL DEFAULT 'education_news' AFTER category_id, ADD fetch_interval SMALLINT UNSIGNED NOT NULL DEFAULT 60 AFTER kind, ADD next_run DATETIME NULL AFTER item_limit, ADD health VARCHAR(20) NOT NULL DEFAULT 'new';
ALTER TABLE ads ADD category_id INT UNSIGNED NULL AFTER slot, ADD device VARCHAR(20) NOT NULL DEFAULT 'all' AFTER code, ADD starts_at DATETIME NULL AFTER device, ADD ends_at DATETIME NULL AFTER starts_at;
ALTER TABLE analytics ADD session_hash CHAR(32) NULL AFTER path, ADD label VARCHAR(255) NULL AFTER value, ADD browser VARCHAR(40) NULL AFTER device, ADD created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD INDEX(session_hash);
ALTER TABLE reports ADD email VARCHAR(190) NULL AFTER content_id, ADD kind VARCHAR(40) NOT NULL DEFAULT 'correction' AFTER email;
ALTER TABLE addons ADD backup_path VARCHAR(500) NULL AFTER manifest, ADD updated_at DATETIME NOT NULL AFTER installed_at;
CREATE TABLE IF NOT EXISTS menus(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,external_id VARCHAR(64) UNIQUE NULL,label VARCHAR(100) NOT NULL,url VARCHAR(500) NOT NULL,location VARCHAR(30) NOT NULL DEFAULT 'header',position SMALLINT UNSIGNED NOT NULL DEFAULT 0,enabled TINYINT(1) NOT NULL DEFAULT 1) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS redirects(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,external_id VARCHAR(64) UNIQUE NULL,source VARCHAR(255) UNIQUE NOT NULL,target VARCHAR(1000) NOT NULL,status_code SMALLINT UNSIGNED NOT NULL DEFAULT 301,enabled TINYINT(1) NOT NULL DEFAULT 1,hits BIGINT UNSIGNED NOT NULL DEFAULT 0) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS media(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,external_id VARCHAR(64) UNIQUE NULL,path VARCHAR(500) UNIQUE NOT NULL,title VARCHAR(255) NOT NULL,alt_text VARCHAR(255) NULL,mime VARCHAR(100) NOT NULL,size BIGINT UNSIGNED NOT NULL DEFAULT 0,meta LONGTEXT NULL,created_at DATETIME NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS import_queue(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,file_path VARCHAR(500) NOT NULL,mode VARCHAR(20) NOT NULL DEFAULT 'skip',status VARCHAR(30) NOT NULL DEFAULT 'queued',message TEXT NULL,created_at DATETIME NOT NULL,ran_at DATETIME NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS broken_links(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,content_id BIGINT UNSIGNED NULL,url VARCHAR(1000) NOT NULL,status_code SMALLINT NULL,status VARCHAR(30) NOT NULL DEFAULT 'open',checked_at DATETIME NOT NULL,INDEX(content_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS saved_items(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,token CHAR(64) NOT NULL,content_id BIGINT UNSIGNED NOT NULL,created_at DATETIME NOT NULL,UNIQUE(token,content_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
