-- Tettevi Group Admin Content Tables Patch
-- Run in phpMyAdmin after uploading this build.
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS pages (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(200) NOT NULL,slug VARCHAR(200) NOT NULL UNIQUE,content LONGTEXT,status VARCHAR(30) DEFAULT 'published',meta_title VARCHAR(200),meta_description TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS blog_comments (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,blog_id INT UNSIGNED NULL,name VARCHAR(150),email VARCHAR(191),comment TEXT,status VARCHAR(30) DEFAULT 'pending',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS clients (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(200) NOT NULL,logo VARCHAR(500),website VARCHAR(500),testimonial TEXT,is_featured TINYINT(1) DEFAULT 1,sort_order INT DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS testimonials (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(200) NOT NULL,role VARCHAR(200),company VARCHAR(200),text TEXT NOT NULL,rating TINYINT DEFAULT 5,photo VARCHAR(500),is_approved TINYINT(1) DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS team_members (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(200) NOT NULL,position VARCHAR(200),department VARCHAR(100),bio TEXT,photo VARCHAR(500),email VARCHAR(191),phone VARCHAR(20),is_active TINYINT(1) DEFAULT 1,sort_order INT DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS seo_meta (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,page_key VARCHAR(120) NOT NULL UNIQUE,meta_title VARCHAR(200),meta_description TEXT,keywords TEXT,og_image VARCHAR(500),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS activity_logs (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NULL,action VARCHAR(120) NOT NULL,description TEXT,ip_address VARCHAR(45),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS backup_logs (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,filename VARCHAR(255),size_bytes BIGINT DEFAULT 0,status VARCHAR(30) DEFAULT 'completed',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS coupons (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(50) NOT NULL UNIQUE,type VARCHAR(20) DEFAULT 'percent',value DECIMAL(10,2) NOT NULL DEFAULT 0,is_active TINYINT(1) DEFAULT 1,expires_at DATE NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS job_applications (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,job_id INT UNSIGNED NULL,full_name VARCHAR(200),email VARCHAR(191),phone VARCHAR(30),cover_letter TEXT,cv_file VARCHAR(500),status VARCHAR(30) DEFAULT 'new',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO pages (title,slug,content,status,meta_title,meta_description) VALUES
('Home','home','Tettevi Group home page','published','Tettevi Group','IT, Real Estate, Travel and Transportation services'),
('About','about','About Tettevi Group','published','About Tettevi Group','Learn about Tettevi Group'),
('Services','services','Our professional services','published','Tettevi Group Services','IT, real estate and travel services'),
('Shop','shop','Premium services shop','published','Tettevi Group Shop','Shop premium digital, real estate and travel services');
INSERT IGNORE INTO clients (id,name,website,testimonial,is_featured,sort_order) VALUES
(1,'Travel People LTD','https://travelpeoplegh.com','Trusted travel and booking solutions partner.',1,1),
(2,'BookingInGhana','https://www.bookingingh.com','Travel technology and booking platform.',1,2),
(3,'Standard Arabia Vaults','https://www.standardarabiavaults.com','Precious metals logistics platform.',1,3);
INSERT IGNORE INTO testimonials (id,name,role,company,text,rating,is_approved) VALUES
(1,'Premium Client','Business Owner','Ghana','Tettevi Group delivered a professional digital solution with speed and excellence.',5,1);
INSERT IGNORE INTO team_members (id,name,position,department,bio,is_active,sort_order) VALUES
(1,'Tettevi Group Team','Digital Solutions Team','IT','Professional team delivering IT, real estate, travel and transportation solutions.',1,1);
INSERT IGNORE INTO seo_meta (page_key,meta_title,meta_description,keywords) VALUES
('home','Tettevi Group — IT, Real Estate, Travel','Diversified global enterprise in IT, real estate, travel and transportation.','Tettevi Group, IT Ghana, Real Estate Ghana, Travel Ghana'),
('shop','Tettevi Group Shop','Premium business, branding, travel and real estate service packages.','shop, services, Tettevi Group');
INSERT IGNORE INTO coupons (code,type,value,is_active) VALUES ('WELCOME10','percent',10,1);
INSERT INTO activity_logs (action,description) VALUES ('admin_content_patch','Admin layout and content table patch installed');