-- Tettevi Group final checkout/order compatibility patch
CREATE TABLE IF NOT EXISTS products (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL, slug VARCHAR(200) NOT NULL UNIQUE, description LONGTEXT, price DECIMAL(12,2) NOT NULL DEFAULT 0, stock INT DEFAULT 999, featured_image VARCHAR(500), is_active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT 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), 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, 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) 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, 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) NULL UNIQUE, amount DECIMAL(12,2) 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;
DROP PROCEDURE IF EXISTS tg_add_col_final;
DELIMITER //
CREATE PROCEDURE tg_add_col_final(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_final('orders','order_number','VARCHAR(80) NULL');
CALL tg_add_col_final('orders','subtotal','DECIMAL(12,2) DEFAULT 0');
CALL tg_add_col_final('orders','discount','DECIMAL(12,2) DEFAULT 0');
CALL tg_add_col_final('orders','shipping','DECIMAL(12,2) DEFAULT 0');
CALL tg_add_col_final('orders','total','DECIMAL(12,2) DEFAULT 0');
CALL tg_add_col_final('orders','currency','VARCHAR(5) DEFAULT ''GHS''');
CALL tg_add_col_final('orders','billing_name','VARCHAR(200) NULL');
CALL tg_add_col_final('orders','billing_email','VARCHAR(191) NULL');
CALL tg_add_col_final('orders','billing_phone','VARCHAR(50) NULL');
CALL tg_add_col_final('orders','billing_address','TEXT NULL');
CALL tg_add_col_final('orders','billing_city','VARCHAR(120) NULL');
CALL tg_add_col_final('orders','billing_country','VARCHAR(120) DEFAULT ''Ghana''');
CALL tg_add_col_final('orders','notes','TEXT NULL');
CALL tg_add_col_final('order_items','product_name','VARCHAR(200) NULL');
CALL tg_add_col_final('order_items','quantity','INT NOT NULL DEFAULT 1');
CALL tg_add_col_final('order_items','price','DECIMAL(12,2) NOT NULL DEFAULT 0');
CALL tg_add_col_final('order_items','total','DECIMAL(12,2) NOT NULL DEFAULT 0');
CALL tg_add_col_final('payments','reference','VARCHAR(100) NULL');
CALL tg_add_col_final('payments','amount','DECIMAL(12,2) DEFAULT 0');
CALL tg_add_col_final('payments','currency','VARCHAR(5) DEFAULT ''GHS''');
CALL tg_add_col_final('payments','gateway','VARCHAR(50) DEFAULT ''paystack''');
CALL tg_add_col_final('payments','status','VARCHAR(30) DEFAULT ''pending''');
CALL tg_add_col_final('payments','gateway_response','LONGTEXT NULL');
CALL tg_add_col_final('payments','verified_at','TIMESTAMP NULL');
DROP PROCEDURE IF EXISTS tg_add_col_final;
INSERT IGNORE INTO products (name, slug, description, price, stock, is_active, created_at) VALUES
('Enterprise Website Package','enterprise-website-package','Premium corporate website package for businesses.',3500,999,1,NOW()),
('Business Branding Kit','business-branding-kit','Logo, brand identity, and marketing design package.',1200,999,1,NOW()),
('IT Consultation','it-consultation','Professional IT advisory session.',500,999,1,NOW()),
('SEO Optimization','seo-optimization','Technical and content SEO optimization service.',800,999,1,NOW());