-- Tettevi Group final compatibility patch
CREATE TABLE IF NOT EXISTS activity_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  action VARCHAR(200) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SET @has_description := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'activity_logs' AND COLUMN_NAME = 'description');
SET @sql := IF(@has_description = 0, 'ALTER TABLE activity_logs ADD COLUMN description TEXT NULL AFTER action', 'SELECT "description column already exists"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @has_details := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'activity_logs' AND COLUMN_NAME = 'details');
SET @sql := IF(@has_details = 0, 'ALTER TABLE activity_logs ADD COLUMN details JSON NULL AFTER description', 'SELECT "details column already exists"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @has_ip := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'activity_logs' AND COLUMN_NAME = 'ip_address');
SET @sql := IF(@has_ip = 0, 'ALTER TABLE activity_logs ADD COLUMN ip_address VARCHAR(45) NULL AFTER details', 'SELECT "ip_address column already exists"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @has_ua := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'activity_logs' AND COLUMN_NAME = 'user_agent');
SET @sql := IF(@has_ua = 0, 'ALTER TABLE activity_logs ADD COLUMN user_agent VARCHAR(500) NULL AFTER ip_address', 'SELECT "user_agent column already exists"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
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) 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, short_description TEXT, price DECIMAL(12,2) NOT NULL DEFAULT 0, sale_price DECIMAL(12,2) NULL, stock INT DEFAULT 999, sku VARCHAR(100), featured_image VARCHAR(500), 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, INDEX idx_slug(slug)) 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), 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, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_cart(cart_id), UNIQUE KEY unique_cart_product(cart_id, product_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS orders (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_number VARCHAR(60) 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) 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) 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;
INSERT INTO activity_logs (action, description) VALUES ('final_header_contact_cart_patch','Final responsive header, contact, scroll, cart and SQL compatibility patch installed');