-- =====================================================
-- TETTEVI GROUP — Complete Database Schema
-- MySQL 8.0+ Compatible
-- =====================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';

-- ── Database ──────────────────────────────────────────
CREATE DATABASE IF NOT EXISTS `tettevigroup`
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `tettevigroup`;

-- ── Roles ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `roles` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL UNIQUE,
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO `roles` (`name`, `description`) VALUES
  ('super_admin', 'Full system access'),
  ('admin', 'Administrative access'),
  ('editor', 'Content management access'),
  ('user', 'Standard user access');

-- ── Users ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `first_name` VARCHAR(100) NOT NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(191) NOT NULL UNIQUE,
  `phone` VARCHAR(20),
  `password` VARCHAR(255),
  `role` ENUM('super_admin','admin','editor','user') DEFAULT 'user',
  `avatar` VARCHAR(500),
  `google_id` VARCHAR(100),
  `auth_provider` ENUM('local','google') DEFAULT 'local',
  `email_verified_at` TIMESTAMP NULL,
  `remember_token` VARCHAR(100),
  `last_login_at` TIMESTAMP NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (`email`),
  INDEX idx_google_id (`google_id`)
) ENGINE=InnoDB;

-- Default super admin (password: Admin@2025!)
INSERT INTO `users` (`first_name`,`last_name`,`email`,`password`,`role`,`is_active`) VALUES
  ('Tettevi','Admin','admin@tettevigroup.com','$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','super_admin',1);

-- ── Password Resets ───────────────────────────────────
CREATE TABLE IF NOT EXISTS `password_resets` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(191) NOT NULL,
  `token` VARCHAR(255) NOT NULL,
  `expires_at` TIMESTAMP NOT NULL,
  `used` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_email_token (`email`, `token`)
) ENGINE=InnoDB;

-- ── Services ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `services` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(200) NOT NULL,
  `slug` VARCHAR(200) NOT NULL UNIQUE,
  `division` ENUM('it','real_estate','travel') NOT NULL,
  `icon` VARCHAR(100),
  `short_description` TEXT,
  `full_description` LONGTEXT,
  `features` JSON,
  `featured_image` VARCHAR(500),
  `is_featured` TINYINT(1) DEFAULT 0,
  `sort_order` INT DEFAULT 0,
  `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;

-- ── Portfolio Categories ───────────────────────────────
CREATE TABLE IF NOT EXISTS `portfolio_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;

INSERT INTO `portfolio_categories` (`name`,`slug`) VALUES
  ('IT Solutions','it-solutions'),
  ('Real Estate','real-estate'),
  ('Travel','travel'),
  ('Design','design'),
  ('Branding','branding');

-- ── Portfolio Projects ────────────────────────────────
CREATE TABLE IF NOT EXISTS `portfolio_projects` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `category_id` INT UNSIGNED,
  `title` VARCHAR(200) NOT NULL,
  `slug` VARCHAR(200) NOT NULL UNIQUE,
  `client` VARCHAR(200),
  `description` LONGTEXT,
  `featured_image` VARCHAR(500),
  `gallery` JSON,
  `technologies` JSON,
  `results` TEXT,
  `project_url` VARCHAR(500),
  `is_featured` TINYINT(1) DEFAULT 0,
  `sort_order` INT DEFAULT 0,
  `completed_at` DATE,
  `meta_title` VARCHAR(200),
  `meta_description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`category_id`) REFERENCES `portfolio_categories`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ── Team Members ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS `team_members` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(200) NOT NULL,
  `position` VARCHAR(200),
  `department` VARCHAR(100),
  `bio` TEXT,
  `photo` VARCHAR(500),
  `email` VARCHAR(191),
  `phone` VARCHAR(20),
  `linkedin` VARCHAR(500),
  `twitter` VARCHAR(500),
  `is_leadership` TINYINT(1) DEFAULT 0,
  `sort_order` INT DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── Clients ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `clients` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(200) NOT NULL,
  `logo` VARCHAR(500),
  `website` VARCHAR(500),
  `testimonial` TEXT,
  `is_featured` TINYINT(1) DEFAULT 1,
  `sort_order` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── Testimonials ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS `testimonials` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(200) NOT NULL,
  `role` VARCHAR(200),
  `company` VARCHAR(200),
  `text` TEXT NOT NULL,
  `rating` TINYINT DEFAULT 5,
  `photo` VARCHAR(500),
  `is_approved` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── Careers / Jobs ────────────────────────────────────
CREATE TABLE IF NOT EXISTS `jobs` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(200) NOT NULL,
  `slug` VARCHAR(200) NOT NULL UNIQUE,
  `department` VARCHAR(100),
  `type` ENUM('full_time','part_time','contract','internship') DEFAULT 'full_time',
  `location` VARCHAR(200) DEFAULT 'Accra, Ghana',
  `description` LONGTEXT,
  `requirements` LONGTEXT,
  `benefits` TEXT,
  `salary_range` VARCHAR(100),
  `deadline` DATE,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── Job Applications ──────────────────────────────────
CREATE TABLE IF NOT EXISTS `job_applications` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `job_id` INT UNSIGNED,
  `name` VARCHAR(200) NOT NULL,
  `email` VARCHAR(191) NOT NULL,
  `phone` VARCHAR(20),
  `cover_letter` TEXT,
  `cv_file` VARCHAR(500),
  `status` ENUM('pending','reviewing','shortlisted','rejected','hired') DEFAULT 'pending',
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`job_id`) REFERENCES `jobs`(`id`) ON DELETE CASCADE,
  INDEX idx_job (`job_id`)
) ENGINE=InnoDB;

-- ── Product Categories ────────────────────────────────
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,
  `description` TEXT,
  `image` VARCHAR(500),
  `parent_id` INT UNSIGNED NULL,
  `sort_order` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`parent_id`) REFERENCES `product_categories`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ── Products ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `products` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `category_id` INT UNSIGNED,
  `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),
  `stock` INT DEFAULT 0,
  `sku` VARCHAR(100),
  `featured_image` VARCHAR(500),
  `gallery` JSON,
  `is_featured` TINYINT(1) DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `weight` DECIMAL(8,2),
  `meta_title` VARCHAR(200),
  `meta_description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`category_id`) REFERENCES `product_categories`(`id`) ON DELETE SET NULL,
  INDEX idx_slug (`slug`)
) ENGINE=InnoDB;

-- ── Carts ─────────────────────────────────────────────
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,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX idx_session (`session_id`)
) ENGINE=InnoDB;

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,
  FOREIGN KEY (`cart_id`) REFERENCES `carts`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── Wishlists ─────────────────────────────────────────
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`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── Orders ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `orders` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `order_number` VARCHAR(30) NOT NULL UNIQUE,
  `user_id` INT UNSIGNED NULL,
  `status` ENUM('pending','processing','completed','cancelled','refunded') DEFAULT 'pending',
  `subtotal` DECIMAL(12,2) NOT NULL,
  `discount` DECIMAL(12,2) DEFAULT 0,
  `shipping` DECIMAL(12,2) DEFAULT 0,
  `total` DECIMAL(12,2) NOT NULL,
  `currency` VARCHAR(5) DEFAULT 'GHS',
  `notes` TEXT,
  -- Billing details
  `billing_name` VARCHAR(200),
  `billing_email` VARCHAR(191),
  `billing_phone` VARCHAR(20),
  `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,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX idx_status (`status`),
  INDEX idx_user (`user_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `order_items` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `order_id` INT UNSIGNED NOT NULL,
  `product_id` INT UNSIGNED,
  `product_name` VARCHAR(200) NOT NULL,
  `quantity` INT NOT NULL,
  `price` DECIMAL(12,2) NOT NULL,
  `total` DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ── Payments ──────────────────────────────────────────
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,
  `currency` VARCHAR(5) DEFAULT 'GHS',
  `gateway` VARCHAR(50) DEFAULT 'paystack',
  `status` ENUM('pending','success','failed','refunded') DEFAULT 'pending',
  `gateway_response` JSON,
  `verified_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
  INDEX idx_reference (`reference`)
) ENGINE=InnoDB;

-- ── Blog Categories ───────────────────────────────────
CREATE TABLE IF NOT EXISTS `blog_categories` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL UNIQUE,
  `description` TEXT,
  `sort_order` INT DEFAULT 0
) ENGINE=InnoDB;

INSERT INTO `blog_categories` (`name`, `slug`) VALUES
  ('Technology', 'technology'),
  ('Real Estate', 'real-estate'),
  ('Travel', 'travel'),
  ('Business', 'business'),
  ('Company News', 'company-news');

-- ── Blog Tags ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `blog_tags` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB;

-- ── Blogs ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `blogs` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `category_id` INT UNSIGNED,
  `author_id` INT UNSIGNED,
  `title` VARCHAR(300) NOT NULL,
  `slug` VARCHAR(300) NOT NULL UNIQUE,
  `excerpt` TEXT,
  `content` LONGTEXT,
  `featured_image` VARCHAR(500),
  `status` ENUM('draft','published','archived') DEFAULT 'draft',
  `views` INT DEFAULT 0,
  `allow_comments` TINYINT(1) DEFAULT 1,
  `meta_title` VARCHAR(200),
  `meta_description` TEXT,
  `og_image` VARCHAR(500),
  `published_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`category_id`) REFERENCES `blog_categories`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX idx_slug (`slug`),
  INDEX idx_status (`status`)
) ENGINE=InnoDB;

-- ── Blog Tag Map ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS `blog_tag_map` (
  `blog_id` INT UNSIGNED NOT NULL,
  `tag_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`blog_id`, `tag_id`),
  FOREIGN KEY (`blog_id`) REFERENCES `blogs`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`tag_id`) REFERENCES `blog_tags`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── Comments ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `comments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `blog_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NULL,
  `parent_id` INT UNSIGNED NULL,
  `name` VARCHAR(200),
  `email` VARCHAR(191),
  `content` TEXT NOT NULL,
  `status` ENUM('pending','approved','rejected') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`blog_id`) REFERENCES `blogs`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX idx_blog (`blog_id`),
  INDEX idx_status (`status`)
) ENGINE=InnoDB;

-- ── Contact Messages ──────────────────────────────────
CREATE TABLE IF NOT EXISTS `messages` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(200) NOT NULL,
  `email` VARCHAR(191) NOT NULL,
  `phone` VARCHAR(20),
  `subject` VARCHAR(300),
  `message` TEXT NOT NULL,
  `status` ENUM('unread','read','replied','archived') DEFAULT 'unread',
  `ip_address` VARCHAR(45),
  `admin_notes` TEXT,
  `replied_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_status (`status`)
) ENGINE=InnoDB;

-- ── Newsletter Subscriptions ──────────────────────────
CREATE TABLE IF NOT EXISTS `subscriptions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(191) NOT NULL UNIQUE,
  `name` VARCHAR(200),
  `status` ENUM('active','unsubscribed') DEFAULT 'active',
  `token` VARCHAR(100),
  `ip_address` VARCHAR(45),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_status (`status`)
) ENGINE=InnoDB;

-- ── Site Settings ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS `site_settings` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `setting_key` VARCHAR(100) NOT NULL UNIQUE,
  `setting_value` LONGTEXT,
  `setting_type` ENUM('text','textarea','image','boolean','json') DEFAULT 'text',
  `label` VARCHAR(200),
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO `site_settings` (`setting_key`, `setting_value`, `setting_type`, `label`) VALUES
  ('site_name',       'Tettevi Group',                    'text',    'Site Name'),
  ('site_tagline',    'Technology. Real Estate. Travel.',  'text',    'Site Tagline'),
  ('site_email',      'info@tettevigroup.com',             'text',    'Site Email'),
  ('site_phone',      '+233 553 86 9190',                  'text',    'Phone Number'),
  ('site_address',    'Accra, Ghana',                      'text',    'Address'),
  ('site_logo',       '/assets/images/logo.png',           'image',   'Logo'),
  ('site_favicon',    '/assets/images/favicon.ico',        'image',   'Favicon'),
  ('og_image',        '/assets/images/og-default.jpg',     'image',   'Default OG Image'),
  ('facebook_url',    '',                                  'text',    'Facebook URL'),
  ('twitter_url',     '',                                  'text',    'Twitter URL'),
  ('linkedin_url',    '',                                  'text',    'LinkedIn URL'),
  ('instagram_url',   '',                                  'text',    'Instagram URL'),
  ('stat_projects',   '250',                               'text',    'Stat: Projects'),
  ('stat_clients',    '180',                               'text',    'Stat: Clients'),
  ('stat_years',      '10',                                'text',    'Stat: Years'),
  ('stat_countries',  '15',                                'text',    'Stat: Countries'),
  ('google_analytics','',                                  'text',    'Google Analytics ID'),
  ('maintenance_mode','0',                                 'boolean', 'Maintenance Mode');

-- ── Activity Logs ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS `activity_logs` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NULL,
  `action` VARCHAR(200) NOT NULL,
  `model` VARCHAR(100),
  `model_id` INT UNSIGNED,
  `details` JSON,
  `ip_address` VARCHAR(45),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX idx_user (`user_id`),
  INDEX idx_created (`created_at`)
) ENGINE=InnoDB;

-- ── Properties (Real Estate) ──────────────────────────
CREATE TABLE IF NOT EXISTS `properties` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(200) NOT NULL,
  `slug` VARCHAR(200) NOT NULL UNIQUE,
  `type` ENUM('sale','rent','investment') DEFAULT 'sale',
  `property_type` ENUM('house','apartment','land','commercial','villa') DEFAULT 'house',
  `price` DECIMAL(14,2) NOT NULL,
  `currency` VARCHAR(5) DEFAULT 'GHS',
  `bedrooms` INT,
  `bathrooms` INT,
  `area_sqft` DECIMAL(10,2),
  `location` VARCHAR(200),
  `city` VARCHAR(100),
  `region` VARCHAR(100),
  `description` LONGTEXT,
  `features` JSON,
  `featured_image` VARCHAR(500),
  `gallery` JSON,
  `is_featured` TINYINT(1) DEFAULT 0,
  `status` ENUM('available','sold','rented','off_market') DEFAULT 'available',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── Tours ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `tours` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(200) NOT NULL,
  `slug` VARCHAR(200) NOT NULL UNIQUE,
  `destination` VARCHAR(200),
  `duration_days` INT,
  `price` DECIMAL(12,2) NOT NULL,
  `currency` VARCHAR(5) DEFAULT 'GHS',
  `max_persons` INT,
  `description` LONGTEXT,
  `itinerary` JSON,
  `includes` JSON,
  `excludes` JSON,
  `featured_image` VARCHAR(500),
  `gallery` JSON,
  `is_featured` TINYINT(1) DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── Coupon Codes ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS `coupons` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) NOT NULL UNIQUE,
  `type` ENUM('percentage','fixed') DEFAULT 'percentage',
  `value` DECIMAL(10,2) NOT NULL,
  `min_order` DECIMAL(10,2) DEFAULT 0,
  `max_uses` INT,
  `used_count` INT DEFAULT 0,
  `expires_at` TIMESTAMP NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── Backup Logs ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS `backup_logs` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `filename` VARCHAR(300) NOT NULL,
  `size` INT,
  `type` ENUM('manual','auto') DEFAULT 'manual',
  `created_by` INT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;



-- =====================================================
-- DEMO CONTENT SEED DATA — products, blogs, works, properties
-- Run this after schema.sql so shop/blog/work/property detail pages show real database content.
-- Safe to re-run because INSERT IGNORE is used with unique slugs.
-- =====================================================

INSERT IGNORE INTO `product_categories` (`name`,`slug`,`description`,`sort_order`) VALUES
('Digital Solutions','digital-solutions','Premium websites, apps and automation solutions.',1),
('Branding & Design','branding-design','Corporate branding and graphic design packages.',2),
('Travel Services','travel-services','Travel planning, tours and ticketing services.',3),
('Real Estate Services','real-estate-services','Property advisory and investment products.',4);

INSERT IGNORE INTO `products` (`category_id`,`name`,`slug`,`description`,`short_description`,`price`,`sale_price`,`stock`,`sku`,`featured_image`,`is_featured`,`is_active`,`meta_title`,`meta_description`) VALUES
((SELECT id FROM product_categories WHERE slug='digital-solutions' LIMIT 1),'Premium Corporate Website Package','premium-corporate-website','A complete ultra-premium corporate website package with responsive pages, SEO foundation, contact forms, blog, portfolio, and admin-ready structure.','Ultra-premium responsive website package for companies.',4500.00,NULL,20,'TG-WEB-001',NULL,1,1,'Premium Corporate Website Package','Ultra-premium website design package by Tettevi Group'),
((SELECT id FROM product_categories WHERE slug='branding-design' LIMIT 1),'Complete Brand Identity Kit','complete-brand-identity-kit','Professional logo system, brand colors, typography guide, social media templates, business card design, and launch-ready brand assets.','Professional branding package for growing businesses.',2500.00,2200.00,25,'TG-BRAND-001',NULL,1,1,'Complete Brand Identity Kit','Logo and brand identity design package'),
((SELECT id FROM product_categories WHERE slug='travel-services' LIMIT 1),'Corporate Travel Management Setup','corporate-travel-management-setup','End-to-end business travel planning setup for companies, including booking workflow, travel policy guidance, and reporting templates.','Corporate travel setup and advisory package.',1800.00,NULL,30,'TG-TRAVEL-001',NULL,0,1,'Corporate Travel Management Setup','Corporate travel management service package'),
((SELECT id FROM product_categories WHERE slug='real-estate-services' LIMIT 1),'Real Estate Investment Advisory','real-estate-investment-advisory','Premium consultation for property buyers and investors seeking secure opportunities in Ghana real estate markets.','Property investment advisory package.',1200.00,NULL,40,'TG-REAL-001',NULL,1,1,'Real Estate Investment Advisory','Real estate advisory package in Ghana');

INSERT IGNORE INTO `blogs` (`category_id`,`author_id`,`title`,`slug`,`excerpt`,`content`,`featured_image`,`status`,`allow_comments`,`meta_title`,`meta_description`,`published_at`) VALUES
((SELECT id FROM blog_categories WHERE slug='technology' LIMIT 1),(SELECT id FROM users WHERE email='admin@tettevigroup.com' LIMIT 1),'Why Every Business Needs a Premium Website in 2026','professional-website-business-2026','A practical guide to why modern businesses need fast, secure and conversion-focused websites.','<p>A premium website is more than an online brochure. It is your digital office, sales representative, customer support desk, brand proof and search engine asset working every day.</p><p>For businesses in Ghana and beyond, customers compare brands online before calling. A strong website improves trust, captures leads, supports payments, and gives your brand a professional foundation.</p><p>Tettevi Group builds websites with responsive layouts, SEO-ready structure, fast loading pages, and clean user journeys that help visitors take action.</p>',NULL,'published',1,'Why Every Business Needs a Premium Website in 2026','Premium website benefits for modern businesses',NOW()),
((SELECT id FROM blog_categories WHERE slug='real-estate' LIMIT 1),(SELECT id FROM users WHERE email='admin@tettevigroup.com' LIMIT 1),'Property Investment Guide for Accra Buyers','property-investment-accra-guide','Key things buyers should know before investing in Accra real estate.','<p>Accra remains one of Ghana’s strongest real estate markets, but smart investors need clear goals, proper documentation checks, and professional guidance.</p><p>Before buying, confirm land title, location growth potential, road access, rental demand, and total ownership cost. A good investment is not only about price; it is about security, long-term demand, and exit options.</p><p>Tettevi Group supports buyers with property sourcing, due diligence guidance, negotiation support, and investment advisory.</p>',NULL,'published',1,'Property Investment Guide for Accra Buyers','Real estate investment guide for Accra buyers',NOW()),
((SELECT id FROM blog_categories WHERE slug='travel' LIMIT 1),(SELECT id FROM users WHERE email='admin@tettevigroup.com' LIMIT 1),'Stress-Free Travel Planning for Families and Businesses','stress-free-travel-planning','How professional travel planning saves time, cost and stress.','<p>Travel becomes easier when flights, accommodation, documents, transport, and itinerary details are coordinated early.</p><p>Families need convenience and safety. Businesses need reliability, cost control and proper records. With professional planning, travellers avoid last-minute pressure and enjoy smoother trips.</p><p>Tettevi Group helps clients plan tours, flights, logistics and travel experiences with care and attention to detail.</p>',NULL,'published',1,'Stress-Free Travel Planning','Professional travel planning tips',NOW());

INSERT IGNORE INTO `portfolio_projects` (`category_id`,`title`,`slug`,`client`,`description`,`featured_image`,`gallery`,`technologies`,`results`,`project_url`,`is_featured`,`completed_at`,`meta_title`,`meta_description`) VALUES
((SELECT id FROM portfolio_categories WHERE slug='it-solutions' LIMIT 1),'Premium E-Commerce Platform','ecommerce-platform','Retail Ghana Ltd','A complete e-commerce platform with product catalogue, cart, checkout, payment integration and admin order management.',NULL,JSON_ARRAY(),JSON_ARRAY('PHP 8','MySQL','Paystack','Responsive UI','Admin Dashboard'),JSON_ARRAY('Improved digital sales workflow','Reduced manual order handling','Created mobile-first shopping experience'),NULL,1,CURDATE(),'Premium E-Commerce Platform','Portfolio detail for e-commerce platform'),
((SELECT id FROM portfolio_categories WHERE slug='branding' LIMIT 1),'Corporate Brand Identity System','corporate-brand-identity-system','Tettevi Group Client','A full visual identity project including logo direction, color system, typography, marketing templates and brand launch assets.',NULL,JSON_ARRAY(),JSON_ARRAY('Brand Strategy','Graphic Design','Social Media Kits','Print Assets'),JSON_ARRAY('Consistent brand presence','Professional launch materials','Improved customer trust'),NULL,1,CURDATE(),'Corporate Brand Identity System','Branding portfolio project'),
((SELECT id FROM portfolio_categories WHERE slug='real-estate' LIMIT 1),'Real Estate Listing Portal','property-portal','Zuri Estate','A property listing and lead capture portal for real estate buyers, sellers and investors.',NULL,JSON_ARRAY(),JSON_ARRAY('PHP','MySQL','Property Search','Lead Forms'),JSON_ARRAY('Better property discovery','More qualified enquiries','Responsive buyer experience'),NULL,1,CURDATE(),'Real Estate Listing Portal','Real estate portal portfolio project');

INSERT IGNORE INTO `properties` (`title`,`slug`,`type`,`property_type`,`price`,`currency`,`bedrooms`,`bathrooms`,`area_sqft`,`location`,`city`,`region`,`description`,`features`,`featured_image`,`gallery`,`is_featured`,`status`) VALUES
('4-Bedroom Executive Villa','east-legon-villa','sale','villa',850000.00,'GHS',4,3,3444.00,'East Legon, Accra','Accra','Greater Accra','A stunning executive villa in one of Accra’s most desirable residential areas with spacious interiors and premium finishing.',JSON_ARRAY('Fitted Kitchen','CCTV Security','Double Garage','Backup Generator','Air Conditioning','Garden'),NULL,JSON_ARRAY(),1,'available'),
('Trasacco Estate Luxury Home','trasacco-luxury','sale','villa',1200000.00,'GHS',5,4,5597.00,'Trasacco, Accra','Accra','Greater Accra','An exceptional luxury residence within a secure gated estate, designed for comfort, privacy and high-end living.',JSON_ARRAY('Swimming Pool','Smart Home System','Home Theater','Solar Power','Staff Quarters','Landscaped Garden'),NULL,JSON_ARRAY(),1,'available'),
('Airport Residential Apartment','airport-residential-apartment','rent','apartment',15000.00,'GHS',3,3,1800.00,'Airport Residential Area, Accra','Accra','Greater Accra','A premium apartment in a prime business-friendly location with easy access to major offices, restaurants and airport routes.',JSON_ARRAY('Elevator','Gym Access','Security','Backup Power','Fitted Kitchen'),NULL,JSON_ARRAY(),0,'available');

INSERT IGNORE INTO `tours` (`title`,`slug`,`destination`,`duration_days`,`price`,`currency`,`max_persons`,`description`,`itinerary`,`includes`,`excludes`,`featured_image`,`gallery`,`is_featured`,`is_active`) VALUES
('Accra City Premium Experience','accra-city-premium-experience','Accra, Ghana',1,850.00,'GHS',20,'A curated city tour covering culture, food, landmarks and premium travel support.',JSON_ARRAY('Pickup','City landmarks','Cultural experience','Drop-off'),JSON_ARRAY('Transport','Guide','Selected entry fees'),JSON_ARRAY('Personal shopping'),NULL,JSON_ARRAY(),1,1);

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================
-- END OF SCHEMA
-- =====================================================