-- Tettevi Group FINAL all-in-one database patch
-- Safe to run multiple times. It creates missing admin/content/shop/cart/order tables and fixes activity_logs.description.
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS pages (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(220) NOT NULL,slug VARCHAR(220) NOT NULL,content LONGTEXT NULL,status VARCHAR(30) DEFAULT 'published',meta_title VARCHAR(255) NULL,meta_description TEXT NULL,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 services (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(220) NOT NULL,slug VARCHAR(220) NOT NULL,category VARCHAR(120) NULL,excerpt TEXT NULL,content LONGTEXT NULL,icon VARCHAR(80) NULL,image VARCHAR(500) NULL,status VARCHAR(30) DEFAULT 'active',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 portfolio_projects (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(220) NOT NULL,slug VARCHAR(220) NOT NULL,category VARCHAR(120) NULL,client VARCHAR(180) NULL,excerpt TEXT NULL,description LONGTEXT NULL,image VARCHAR(500) NULL,project_url VARCHAR(500) NULL,status VARCHAR(30) DEFAULT 'published',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 blogs (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(255) NOT NULL,slug VARCHAR(255) NOT NULL,excerpt TEXT NULL,content LONGTEXT NULL,featured_image VARCHAR(500) NULL,category VARCHAR(120) NULL,status VARCHAR(30) DEFAULT 'published',author_id INT UNSIGNED NULL,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 comments (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,post_id INT UNSIGNED NULL,name VARCHAR(150) NULL,email VARCHAR(191) NULL,comment TEXT NULL,status VARCHAR(30) DEFAULT 'pending',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 team_members (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(180) NOT NULL,position VARCHAR(180) NULL,bio TEXT NULL,photo VARCHAR(500) NULL,email VARCHAR(191) NULL,linkedin VARCHAR(500) NULL,sort_order INT DEFAULT 0,status VARCHAR(30) DEFAULT 'active',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 testimonials (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(180) NOT NULL,company VARCHAR(180) NULL,position VARCHAR(180) NULL,message TEXT NULL,rating INT DEFAULT 5,photo VARCHAR(500) NULL,status VARCHAR(30) DEFAULT 'active',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 clients (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(180) NOT NULL,logo VARCHAR(500) NULL,website VARCHAR(500) NULL,industry VARCHAR(120) NULL,status VARCHAR(30) DEFAULT 'active',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 properties (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(220) NOT NULL,slug VARCHAR(220) NOT NULL,location VARCHAR(220) NULL,price DECIMAL(14,2) DEFAULT 0,type VARCHAR(80) NULL,bedrooms INT DEFAULT 0,bathrooms INT DEFAULT 0,description LONGTEXT NULL,featured_image VARCHAR(500) NULL,status VARCHAR(30) DEFAULT 'available',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 tours (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(220) NOT NULL,slug VARCHAR(220) NOT NULL,destination VARCHAR(180) NULL,price DECIMAL(14,2) DEFAULT 0,duration VARCHAR(100) NULL,description LONGTEXT NULL,featured_image VARCHAR(500) NULL,status VARCHAR(30) DEFAULT 'active',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 product_categories (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL,slug VARCHAR(100) NOT NULL UNIQUE,sort_order INT DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS products (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,category_id INT UNSIGNED NULL,name VARCHAR(200) NOT NULL,slug VARCHAR(200) NOT NULL UNIQUE,description LONGTEXT NULL,short_description TEXT NULL,price DECIMAL(12,2) NOT NULL DEFAULT 0,sale_price DECIMAL(12,2) NULL,stock INT DEFAULT 999,sku VARCHAR(100) NULL,featured_image VARCHAR(500) NULL,gallery LONGTEXT NULL,is_featured TINYINT(1) DEFAULT 0,is_active TINYINT(1) DEFAULT 1,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 carts (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NULL,session_id VARCHAR(100) NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX idx_session(session_id),INDEX idx_user(user_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS cart_items (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,cart_id INT UNSIGNED NOT NULL,product_id INT UNSIGNED NOT NULL,quantity INT NOT NULL DEFAULT 1,price DECIMAL(12,2) NOT NULL DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY unique_cart_product(cart_id,product_id),INDEX idx_cart(cart_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS orders (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,order_number VARCHAR(80) NULL UNIQUE,user_id INT UNSIGNED NULL,status VARCHAR(30) DEFAULT 'pending',subtotal DECIMAL(12,2) DEFAULT 0,discount DECIMAL(12,2) DEFAULT 0,shipping DECIMAL(12,2) DEFAULT 0,total DECIMAL(12,2) DEFAULT 0,currency VARCHAR(5) DEFAULT 'GHS',billing_name VARCHAR(200) NULL,billing_email VARCHAR(191) NULL,billing_phone VARCHAR(50) NULL,billing_address TEXT NULL,billing_city VARCHAR(120) NULL,billing_country VARCHAR(120) DEFAULT 'Ghana',notes TEXT NULL,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(220) NULL,quantity INT NOT NULL DEFAULT 1,price DECIMAL(12,2) NOT NULL DEFAULT 0,total DECIMAL(12,2) NOT NULL DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,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 LONGTEXT NULL,verified_at TIMESTAMP NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX idx_reference(reference)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS coupons (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(80) NOT NULL,type VARCHAR(30) DEFAULT 'fixed',amount DECIMAL(12,2) DEFAULT 0,usage_limit INT DEFAULT 0,used_count INT DEFAULT 0,expires_at DATETIME NULL,status VARCHAR(30) DEFAULT 'active',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 jobs (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(220) NOT NULL,slug VARCHAR(220) NOT NULL,department VARCHAR(120) NULL,location VARCHAR(160) NULL,type VARCHAR(80) NULL,description LONGTEXT NULL,status VARCHAR(30) DEFAULT 'open',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 job_applications (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,job_id INT UNSIGNED NULL,name VARCHAR(180) NULL,email VARCHAR(191) NULL,phone VARCHAR(60) NULL,cover_letter TEXT NULL,cv_path VARCHAR(500) NULL,status VARCHAR(30) DEFAULT 'new',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 messages (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(180) NULL,email VARCHAR(191) NULL,phone VARCHAR(60) NULL,subject VARCHAR(255) NULL,message TEXT NULL,status VARCHAR(30) DEFAULT 'new',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 subscriptions (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(180) NULL,email VARCHAR(191) NOT NULL UNIQUE,status VARCHAR(30) DEFAULT 'active',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_settings (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,page VARCHAR(160) NOT NULL,meta_title VARCHAR(255) NULL,meta_description TEXT NULL,keywords TEXT NULL,canonical_url VARCHAR(500) NULL,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 backups (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,filename VARCHAR(255) NULL,path VARCHAR(500) NULL,size VARCHAR(60) NULL,status VARCHAR(30) DEFAULT 'created',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(160) NOT NULL,description TEXT NULL,ip_address VARCHAR(80) NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS settings (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,setting_key VARCHAR(160) NOT NULL,setting_value LONGTEXT NULL,setting_group VARCHAR(80) DEFAULT 'general',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP PROCEDURE IF EXISTS tg_add_col;
DELIMITER //
CREATE PROCEDURE tg_add_col(IN t VARCHAR(64), IN c VARCHAR(64), IN d TEXT)
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = t AND COLUMN_NAME = c) THEN
    SET @s = CONCAT('ALTER TABLE `', t, '` ADD COLUMN `', c, '` ', d);
    PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
  END IF;
END//
DELIMITER ;
CALL tg_add_col('activity_logs','description','TEXT NULL');
CALL tg_add_col('activity_logs','ip_address','VARCHAR(80) NULL');
CALL tg_add_col('orders','subtotal','DECIMAL(12,2) DEFAULT 0');
CALL tg_add_col('orders','discount','DECIMAL(12,2) DEFAULT 0');
CALL tg_add_col('orders','shipping','DECIMAL(12,2) DEFAULT 0');
CALL tg_add_col('orders','total','DECIMAL(12,2) DEFAULT 0');
CALL tg_add_col('orders','billing_name','VARCHAR(200) NULL');
CALL tg_add_col('orders','billing_email','VARCHAR(191) NULL');
CALL tg_add_col('orders','billing_phone','VARCHAR(50) NULL');
CALL tg_add_col('orders','billing_address','TEXT NULL');
CALL tg_add_col('orders','billing_city','VARCHAR(120) NULL');
CALL tg_add_col('orders','billing_country','VARCHAR(120) DEFAULT ''Ghana''');
CALL tg_add_col('order_items','product_name','VARCHAR(220) NULL');
CALL tg_add_col('order_items','price','DECIMAL(12,2) NOT NULL DEFAULT 0');
CALL tg_add_col('order_items','total','DECIMAL(12,2) NOT NULL DEFAULT 0');
DROP PROCEDURE IF EXISTS tg_add_col;

INSERT IGNORE INTO product_categories (name,slug,sort_order) VALUES ('Digital Services','digital-services',1),('Travel','travel',2),('Real Estate','real-estate',3);
INSERT IGNORE INTO products (name,slug,description,short_description,price,stock,is_featured,is_active) VALUES
('Enterprise Website Package','enterprise-website-package','Premium corporate website design and development package.','Premium corporate website package',3500,999,1,1),
('Business Branding Kit','business-branding-kit','Logo, brand identity, flyers and business graphics.','Complete branding kit',1200,999,1,1),
('IT Consultation','it-consultation','Professional IT strategy and consultation session.','IT consultation',500,999,1,1),
('SEO Optimization','seo-optimization','Technical SEO and content optimization service.','SEO optimization',800,999,1,1);
INSERT INTO activity_logs (action,description,created_at) VALUES ('final_all_in_one_patch','Final admin, cart, checkout, favicon, and responsive patch installed',NOW());