CREATE TABLE admins (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,email VARCHAR(190) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL);
CREATE TABLE jobs (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(180) NOT NULL,company VARCHAR(180) NOT NULL,city VARCHAR(100) NOT NULL,category VARCHAR(100) NOT NULL,type VARCHAR(60) NOT NULL,experience VARCHAR(100) NOT NULL DEFAULT '',salary VARCHAR(150) NOT NULL DEFAULT '',description MEDIUMTEXT NOT NULL,requirements MEDIUMTEXT NOT NULL,instructions MEDIUMTEXT NOT NULL,apply_email VARCHAR(190) NOT NULL DEFAULT '',apply_url VARCHAR(1000) NOT NULL DEFAULT '',contact_name VARCHAR(180) NOT NULL,contact_email VARCHAR(190) NOT NULL,deadline DATE NULL,source_url VARCHAR(1000) NOT NULL DEFAULT '',source_text MEDIUMTEXT NOT NULL,logo VARCHAR(100) NOT NULL DEFAULT '',status VARCHAR(20) NOT NULL DEFAULT 'pending',review_note TEXT NULL,checked_at DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,INDEX public_jobs(status,deadline,id));
CREATE TABLE reports (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,job_id INT UNSIGNED NOT NULL,reason TEXT NOT NULL,status VARCHAR(20) NOT NULL DEFAULT 'open',created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(job_id) REFERENCES jobs(id));
CREATE TABLE requests (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,bucket VARCHAR(70) NOT NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,INDEX rate_window(bucket,created_at));
CREATE TABLE audit (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,admin_id INT UNSIGNED NOT NULL,action VARCHAR(80) NOT NULL,job_id INT UNSIGNED NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(180) NOT NULL,body MEDIUMTEXT NOT NULL,published TINYINT NOT NULL DEFAULT 0,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE settings (name VARCHAR(80) PRIMARY KEY,value TEXT NOT NULL);
INSERT INTO settings VALUES ('whatsapp_url','');
INSERT INTO articles(title,body,published) VALUES ('Build a clear, focused CV','Put relevant skills and experience first. Use current contact details. Describe achievements accurately. Tailor your CV to the vacancy, check spelling and save a clearly named PDF.',1),('Write an application email','Use the job title and reference in the subject. Explain briefly why your experience fits. Attach only requested documents. Check the employer address and attachments before sending.',1),('Prepare for an attachment','State your course, institution and attachment dates. Include your institution letter if requested. Describe relevant practical projects and follow the employer’s deadline.',1),('Recognise suspicious adverts','Confirm vacancies through official employer channels. Question requests to pay to secure a job. Never share banking passwords, PINs or one-time codes.',1);

CREATE TABLE IF NOT EXISTS adverts (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(180) NOT NULL,image VARCHAR(100) NOT NULL DEFAULT '',destination VARCHAR(1000) NOT NULL,placement VARCHAR(20) NOT NULL DEFAULT 'sidebar',starts DATE NOT NULL,ends DATE NOT NULL,sort_order INT NOT NULL DEFAULT 0,active TINYINT NOT NULL DEFAULT 0,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);