-- Runtime Admin + Shop Fixes for Tettevi Group
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 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 orders (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,order_number VARCHAR(60) NOT NULL UNIQUE,user_id INT UNSIGNED NULL,status VARCHAR(30) DEFAULT 'pending',subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,discount DECIMAL(12,2) DEFAULT 0,shipping DECIMAL(12,2) DEFAULT 0,total DECIMAL(12,2) NOT NULL DEFAULT 0,currency VARCHAR(5) DEFAULT 'GHS',notes TEXT,billing_name VARCHAR(200),billing_email VARCHAR(191),billing_phone VARCHAR(30),billing_address TEXT,billing_city VARCHAR(100),billing_country VARCHAR(100) DEFAULT 'Ghana',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX idx_status (status),INDEX idx_user (user_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS order_items (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,order_id INT UNSIGNED NOT NULL,product_id INT UNSIGNED NULL,product_name VARCHAR(200) NOT NULL,quantity INT NOT NULL DEFAULT 1,price DECIMAL(12,2) NOT NULL DEFAULT 0,total DECIMAL(12,2) NOT NULL DEFAULT 0,INDEX idx_order (order_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS payments (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,order_id INT UNSIGNED NOT NULL,reference VARCHAR(100) NOT NULL UNIQUE,amount DECIMAL(12,2) NOT NULL DEFAULT 0,currency VARCHAR(5) DEFAULT 'GHS',gateway VARCHAR(50) DEFAULT 'paystack',status VARCHAR(30) DEFAULT 'pending',gateway_response JSON NULL,verified_at TIMESTAMP NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX idx_reference (reference)) 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');
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 ('runtime_fix','Runtime admin, shop and responsive fix installed');