-- Tettevi Group final runtime patch
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 wishlists (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, product_id INT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY unique_wishlist(user_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) 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 LONGTEXT NULL, verified_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_reference(reference)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO product_categories (id,name,slug,sort_order) VALUES (1,'Services','services',1),(2,'Design','design',2),(3,'Hosting','hosting',3);
INSERT IGNORE INTO products (category_id,name,slug,description,short_description,price,sale_price,stock,is_featured,is_active) VALUES
(1,'Business Website Package','business-website-package','Premium responsive business website package by Tettevi Group.','Responsive business website',2500,3200,999,1,1),
(1,'Social Media Management — Monthly','social-media-management','Monthly social media management for growing brands.','Monthly social media package',800,NULL,999,1,1),
(1,'SEO Audit & Strategy Report','seo-audit-package','Technical SEO audit and growth strategy.','SEO audit and strategy',450,600,999,0,1),
(2,'Logo & Brand Identity Kit','logo-brand-design','Premium logo and brand identity package.','Brand identity kit',600,NULL,999,1,1),
(3,'Domain + Hosting — 1 Year','domain-hosting-1yr','Domain registration plus hosting for one year.','Domain and hosting',350,420,999,0,1),
(1,'Email Marketing Campaign Setup','email-marketing-setup','Email marketing campaign setup and automation.','Email marketing setup',700,NULL,999,0,1);