CREATE TABLE admins(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,email VARCHAR(190) UNIQUE NOT NULL,password VARCHAR(255) NOT NULL,failed_attempts SMALLINT NOT NULL DEFAULT 0,locked_until DATETIME NULL,last_login DATETIME NULL,created_at DATETIME NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE settings(`key` VARCHAR(100) PRIMARY KEY,value MEDIUMTEXT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE categories(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,external_id VARCHAR(64) UNIQUE NULL,parent_id INT UNSIGNED NULL,name VARCHAR(150) NOT NULL,slug VARCHAR(190) UNIQUE NOT NULL,type VARCHAR(50) NOT NULL DEFAULT 'general',description TEXT NULL,image VARCHAR(500) NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,INDEX(parent_id),INDEX(type)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE content(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,external_id VARCHAR(64) UNIQUE NOT NULL,type VARCHAR(50) NOT NULL,category_id INT UNSIGNED NULL,parent_id BIGINT UNSIGNED NULL,slug VARCHAR(255) UNIQUE NOT NULL,title VARCHAR(255) NOT NULL,excerpt TEXT NOT NULL,body LONGTEXT NOT NULL,data LONGTEXT NULL,meta_title VARCHAR(65) NULL,meta_description VARCHAR(170) NULL,canonical_url VARCHAR(1000) NULL,status ENUM('draft','validation_failed','needs_review','scheduled','published') NOT NULL DEFAULT 'draft',noindex TINYINT(1) NOT NULL DEFAULT 1,featured TINYINT(1) NOT NULL DEFAULT 0,ad_free TINYINT(1) NOT NULL DEFAULT 0,source_url VARCHAR(1000) NULL,author VARCHAR(190) NULL,reviewer VARCHAR(190) NULL,content_hash CHAR(64) NOT NULL,quality_score TINYINT UNSIGNED NOT NULL DEFAULT 0,views BIGINT UNSIGNED NOT NULL DEFAULT 0,published_at DATETIME NULL,reviewed_at DATETIME NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,INDEX(type,status,noindex),INDEX(category_id),INDEX(parent_id),INDEX(content_hash),INDEX(published_at),INDEX(updated_at),FULLTEXT(title,excerpt,body),CONSTRAINT fk_content_category FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE 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 sections(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,external_id VARCHAR(64) UNIQUE NULL,title VARCHAR(190) NOT NULL,category_id INT UNSIGNED NOT NULL DEFAULT 0,content_types TEXT NULL,layout VARCHAR(30) NOT NULL DEFAULT 'cards',item_limit TINYINT UNSIGNED NOT NULL DEFAULT 6,position SMALLINT UNSIGNED NOT NULL DEFAULT 0,enabled TINYINT(1) NOT NULL DEFAULT 1) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE imports(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,package_id VARCHAR(100) NOT NULL,version VARCHAR(30) NOT NULL,mode VARCHAR(20) NOT NULL,status VARCHAR(30) NOT NULL,items_created INT NOT NULL DEFAULT 0,items_updated INT NOT NULL DEFAULT 0,items_skipped INT NOT NULL DEFAULT 0,log LONGTEXT NULL,errors LONGTEXT NULL,admin_id INT UNSIGNED NULL,created_at DATETIME NOT NULL,finished_at DATETIME NULL,INDEX(package_id,version),INDEX(status,created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE import_snapshots(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,import_id BIGINT UNSIGNED NOT NULL,entity_type VARCHAR(30) NOT NULL DEFAULT 'content',content_id BIGINT UNSIGNED NOT NULL,previous_data LONGTEXT NULL,was_created TINYINT(1) NOT NULL DEFAULT 0,INDEX(import_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE import_files(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,import_id BIGINT UNSIGNED NOT NULL,target_path VARCHAR(500) NOT NULL,backup_path VARCHAR(500) NULL,was_created TINYINT(1) NOT NULL DEFAULT 0,INDEX(import_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE 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,INDEX(status,created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE feeds(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,external_id VARCHAR(64) UNIQUE NULL,name VARCHAR(190) NOT NULL,url VARCHAR(1000) NOT NULL,allowed_host VARCHAR(255) NOT NULL,category_id INT UNSIGNED NULL,kind VARCHAR(40) NOT NULL DEFAULT 'education_news',fetch_interval SMALLINT UNSIGNED NOT NULL DEFAULT 60,enabled TINYINT(1) NOT NULL DEFAULT 1,item_limit TINYINT UNSIGNED NOT NULL DEFAULT 10,next_run DATETIME NULL,last_run DATETIME NULL,last_status VARCHAR(255) NULL,health VARCHAR(20) NOT NULL DEFAULT 'new',failure_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,UNIQUE(url),INDEX(enabled,next_run)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE ads(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL,slot VARCHAR(50) NOT NULL,category_id INT UNSIGNED NULL,content_type VARCHAR(50) NULL,content_id BIGINT UNSIGNED NULL,code MEDIUMTEXT NOT NULL,device VARCHAR(20) NOT NULL DEFAULT 'all',starts_at DATETIME NULL,ends_at DATETIME NULL,enabled TINYINT(1) NOT NULL DEFAULT 0,INDEX(slot,enabled),INDEX(content_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE analytics(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,day DATE NOT NULL,event VARCHAR(30) NOT NULL,path VARCHAR(255) NOT NULL,session_hash CHAR(32) NULL,value INT UNSIGNED NOT NULL DEFAULT 1,label VARCHAR(255) NULL,device VARCHAR(30) NULL,browser VARCHAR(40) NULL,referrer VARCHAR(255) NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,INDEX(day,event),INDEX(path),INDEX(session_hash),INDEX(created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE 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 audit_log(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,admin_id INT UNSIGNED NULL,action VARCHAR(100) NOT NULL,details LONGTEXT NULL,created_at DATETIME NOT NULL,INDEX(created_at),INDEX(action)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE cron_log(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,job VARCHAR(60) NOT NULL,status VARCHAR(20) NOT NULL,message TEXT NULL,ran_at DATETIME NOT NULL,duration_ms INT UNSIGNED NOT NULL DEFAULT 0,INDEX(ran_at),INDEX(job,ran_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE reports(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,content_id BIGINT UNSIGNED NULL,email VARCHAR(190) NULL,kind VARCHAR(40) NOT NULL DEFAULT 'correction',message TEXT NOT NULL,status VARCHAR(30) NOT NULL DEFAULT 'open',admin_note TEXT NULL,created_at DATETIME NOT NULL,updated_at DATETIME NULL,INDEX(status,created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE addons(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,addon_id VARCHAR(100) UNIQUE NOT NULL,name VARCHAR(190) NOT NULL,version VARCHAR(30) NOT NULL,status VARCHAR(30) NOT NULL DEFAULT 'enabled',manifest LONGTEXT NOT NULL,backup_path VARCHAR(500) NULL,installed_at DATETIME NOT NULL,updated_at DATETIME NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE 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;
CREATE TABLE 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,INDEX(location,position)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE 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 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,INDEX(mime)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE 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),INDEX(status)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
