-- =====================================================
-- ZLO PLATFORM - Database Schema
-- SaaS-level architecture for African Tech Startup
-- PHP 8+ | MySQL 8.0+ | Production Ready
-- =====================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- -----------------------------------------------------
-- Drop existing tables
-- -----------------------------------------------------
DROP TABLE IF EXISTS `analytics`;
DROP TABLE IF EXISTS `contact_messages`;
DROP TABLE IF EXISTS `demo_requests`;
DROP TABLE IF EXISTS `sales_pipeline`;
DROP TABLE IF EXISTS `leads`;
DROP TABLE IF EXISTS `testimonials`;
DROP TABLE IF EXISTS `media`;
DROP TABLE IF EXISTS `faqs`;
DROP TABLE IF EXISTS `menu_items`;
DROP TABLE IF EXISTS `menus`;
DROP TABLE IF EXISTS `blog_posts`;
DROP TABLE IF EXISTS `blog_categories`;
DROP TABLE IF EXISTS `case_studies`;
DROP TABLE IF EXISTS `features`;
DROP TABLE IF EXISTS `solutions`;
DROP TABLE IF EXISTS `industries`;
DROP TABLE IF EXISTS `partners`;
DROP TABLE IF EXISTS `clients`;
DROP TABLE IF EXISTS `pages`;
DROP TABLE IF EXISTS `settings`;
DROP TABLE IF EXISTS `role_permissions`;
DROP TABLE IF EXISTS `permissions`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `roles`;

-- -----------------------------------------------------
-- Table: roles
-- -----------------------------------------------------
CREATE TABLE `roles` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL UNIQUE,
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: permissions
-- -----------------------------------------------------
CREATE TABLE `permissions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL UNIQUE,
  `module` VARCHAR(50) NOT NULL,
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: role_permissions
-- -----------------------------------------------------
CREATE TABLE `role_permissions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `role_id` INT UNSIGNED NOT NULL,
  `permission_id` INT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_role_permission` (`role_id`, `permission_id`),
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: users
-- -----------------------------------------------------
CREATE TABLE `users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(150) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `role_id` INT UNSIGNED NOT NULL,
  `avatar` VARCHAR(255) DEFAULT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `status` ENUM('active', 'suspended', 'pending') DEFAULT 'active',
  `last_login` DATETIME DEFAULT NULL,
  `email_verified_at` DATETIME DEFAULT NULL,
  `remember_token` VARCHAR(100) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE RESTRICT,
  INDEX `idx_email` (`email`),
  INDEX `idx_role_id` (`role_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: industries
-- -----------------------------------------------------
CREATE TABLE `industries` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL UNIQUE,
  `icon` VARCHAR(50) DEFAULT NULL,
  `description` TEXT,
  `image` VARCHAR(255) DEFAULT NULL,
  `order_position` INT DEFAULT 0,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_slug` (`slug`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: solutions
-- -----------------------------------------------------
CREATE TABLE `solutions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(150) NOT NULL,
  `slug` VARCHAR(150) NOT NULL UNIQUE,
  `subtitle` VARCHAR(255) DEFAULT NULL,
  `short_description` TEXT NOT NULL,
  `full_description` LONGTEXT,
  `problem_statement` TEXT,
  `solution_description` TEXT,
  `benefits` JSON,
  `icon` VARCHAR(50) DEFAULT NULL,
  `hero_image` VARCHAR(255) DEFAULT NULL,
  `preview_image` VARCHAR(255) DEFAULT NULL,
  `demo_video_url` VARCHAR(255) DEFAULT NULL,
  `features_count` INT DEFAULT 0,
  `clients_count` INT DEFAULT 0,
  `roi_percentage` INT DEFAULT 0,
  `pricing_start` DECIMAL(10,2) DEFAULT NULL,
  `pricing_unit` VARCHAR(20) DEFAULT 'month',
  `meta_title` VARCHAR(150) DEFAULT NULL,
  `meta_description` TEXT,
  `order_position` INT DEFAULT 0,
  `featured` BOOLEAN DEFAULT FALSE,
  `status` ENUM('active', 'inactive', 'draft') DEFAULT 'draft',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_slug` (`slug`),
  INDEX `idx_status` (`status`),
  INDEX `idx_featured` (`featured`),
  INDEX `idx_order` (`order_position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: features
-- -----------------------------------------------------
CREATE TABLE `features` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `solution_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(150) NOT NULL,
  `description` TEXT,
  `icon` VARCHAR(50) DEFAULT NULL,
  `image` VARCHAR(255) DEFAULT NULL,
  `order_position` INT DEFAULT 0,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`solution_id`) REFERENCES `solutions`(`id`) ON DELETE CASCADE,
  INDEX `idx_solution_id` (`solution_id`),
  INDEX `idx_order` (`order_position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: case_studies
-- -----------------------------------------------------
CREATE TABLE `case_studies` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(200) NOT NULL,
  `slug` VARCHAR(200) NOT NULL UNIQUE,
  `client_name` VARCHAR(150) NOT NULL,
  `client_logo` VARCHAR(255) DEFAULT NULL,
  `industry_id` INT UNSIGNED DEFAULT NULL,
  `solution_id` INT UNSIGNED DEFAULT NULL,
  `problem` TEXT NOT NULL,
  `solution` TEXT NOT NULL,
  `results` JSON,
  `metrics` JSON,
  `image` VARCHAR(255) DEFAULT NULL,
  `testimonial` TEXT,
  `testimonial_author` VARCHAR(100) DEFAULT NULL,
  `testimonial_role` VARCHAR(100) DEFAULT NULL,
  `published` BOOLEAN DEFAULT FALSE,
  `published_at` DATETIME DEFAULT NULL,
  `views` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`industry_id`) REFERENCES `industries`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`solution_id`) REFERENCES `solutions`(`id`) ON DELETE SET NULL,
  INDEX `idx_slug` (`slug`),
  INDEX `idx_industry` (`industry_id`),
  INDEX `idx_solution` (`solution_id`),
  INDEX `idx_published` (`published`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: clients
-- -----------------------------------------------------
CREATE TABLE `clients` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `logo` VARCHAR(255) DEFAULT NULL,
  `website` VARCHAR(255) DEFAULT NULL,
  `industry_id` INT UNSIGNED DEFAULT NULL,
  `featured` BOOLEAN DEFAULT FALSE,
  `order_position` INT DEFAULT 0,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`industry_id`) REFERENCES `industries`(`id`) ON DELETE SET NULL,
  INDEX `idx_featured` (`featured`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: partners
-- -----------------------------------------------------
CREATE TABLE `partners` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `logo` VARCHAR(255) DEFAULT NULL,
  `type` ENUM('technology', 'reseller', 'institutional', 'strategic') DEFAULT 'technology',
  `website` VARCHAR(255) DEFAULT NULL,
  `description` TEXT,
  `featured` BOOLEAN DEFAULT FALSE,
  `order_position` INT DEFAULT 0,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_type` (`type`),
  INDEX `idx_featured` (`featured`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: blog_categories
-- -----------------------------------------------------
CREATE TABLE `blog_categories` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL UNIQUE,
  `description` TEXT,
  `color` VARCHAR(20) DEFAULT '#0066FF',
  `post_count` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: blog_posts
-- -----------------------------------------------------
CREATE TABLE `blog_posts` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(200) NOT NULL,
  `slug` VARCHAR(200) NOT NULL UNIQUE,
  `excerpt` TEXT,
  `content` LONGTEXT,
  `featured_image` VARCHAR(255) DEFAULT NULL,
  `category_id` INT UNSIGNED DEFAULT NULL,
  `author_id` INT UNSIGNED DEFAULT NULL,
  `tags` JSON,
  `meta_title` VARCHAR(150) DEFAULT NULL,
  `meta_description` TEXT,
  `status` ENUM('published', 'draft', 'scheduled') DEFAULT 'draft',
  `featured` BOOLEAN DEFAULT FALSE,
  `views` INT DEFAULT 0,
  `published_at` DATETIME DEFAULT 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_category` (`category_id`),
  INDEX `idx_author` (`author_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_featured` (`featured`),
  INDEX `idx_published_at` (`published_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: pages
-- -----------------------------------------------------
CREATE TABLE `pages` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(150) NOT NULL,
  `slug` VARCHAR(150) NOT NULL UNIQUE,
  `content` LONGTEXT,
  `template` VARCHAR(50) DEFAULT 'default',
  `meta_title` VARCHAR(150) DEFAULT NULL,
  `meta_description` TEXT,
  `meta_keywords` TEXT,
  `og_image` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('active', 'inactive', 'draft') DEFAULT 'draft',
  `show_in_menu` BOOLEAN DEFAULT FALSE,
  `menu_order` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_slug` (`slug`),
  INDEX `idx_status` (`status`),
  INDEX `idx_menu` (`show_in_menu`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: menus
-- -----------------------------------------------------
CREATE TABLE `menus` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL,
  `location` ENUM('header', 'footer', 'sidebar') DEFAULT 'header',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: menu_items
-- -----------------------------------------------------
CREATE TABLE `menu_items` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `menu_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(100) NOT NULL,
  `url` VARCHAR(255) NOT NULL,
  `parent_id` INT UNSIGNED DEFAULT NULL,
  `order_position` INT DEFAULT 0,
  `target` ENUM('_self', '_blank') DEFAULT '_self',
  `icon` VARCHAR(50) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`menu_id`) REFERENCES `menus`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`parent_id`) REFERENCES `menu_items`(`id`) ON DELETE CASCADE,
  INDEX `idx_menu_id` (`menu_id`),
  INDEX `idx_parent_id` (`parent_id`),
  INDEX `idx_order` (`order_position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: leads
-- -----------------------------------------------------
CREATE TABLE `leads` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `first_name` VARCHAR(100) NOT NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `company` VARCHAR(150) DEFAULT NULL,
  `email` VARCHAR(150) NOT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `country` VARCHAR(100) DEFAULT NULL,
  `city` VARCHAR(100) DEFAULT NULL,
  `industry` VARCHAR(100) DEFAULT NULL,
  `company_size` ENUM('1-10', '11-50', '51-200', '201-500', '500+') DEFAULT NULL,
  `interest_solution_id` INT UNSIGNED DEFAULT NULL,
  `message` TEXT,
  `source` VARCHAR(50) DEFAULT 'website',
  `utm_source` VARCHAR(100) DEFAULT NULL,
  `utm_medium` VARCHAR(100) DEFAULT NULL,
  `utm_campaign` VARCHAR(100) DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` TEXT,
  `status` ENUM('new', 'contacted', 'qualified', 'proposal', 'negotiation', 'won', 'lost', 'nurturing') DEFAULT 'new',
  `score` INT DEFAULT 0,
  `assigned_to` INT UNSIGNED DEFAULT NULL,
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`interest_solution_id`) REFERENCES `solutions`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_email` (`email`),
  INDEX `idx_status` (`status`),
  INDEX `idx_solution` (`interest_solution_id`),
  INDEX `idx_assigned` (`assigned_to`),
  INDEX `idx_created_at` (`created_at`),
  INDEX `idx_source` (`source`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: demo_requests
-- -----------------------------------------------------
CREATE TABLE `demo_requests` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `lead_id` INT UNSIGNED NOT NULL,
  `solution_id` INT UNSIGNED DEFAULT NULL,
  `preferred_date` DATE DEFAULT NULL,
  `preferred_time` TIME DEFAULT NULL,
  `timezone` VARCHAR(50) DEFAULT 'Africa/Lome',
  `participants_count` INT DEFAULT 1,
  `budget_range` ENUM('under_5k', '5k_10k', '10k_25k', '25k_50k', '50k_100k', 'over_100k', 'undisclosed') DEFAULT 'undisclosed',
  `decision_timeline` ENUM('immediate', '1_month', '3_months', '6_months', 'over_6_months') DEFAULT '3_months',
  `current_solution` TEXT,
  `pain_points` TEXT,
  `notes` TEXT,
  `status` ENUM('pending', 'scheduled', 'completed', 'cancelled', 'no_show') DEFAULT 'pending',
  `scheduled_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`lead_id`) REFERENCES `leads`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`solution_id`) REFERENCES `solutions`(`id`) ON DELETE SET NULL,
  INDEX `idx_lead_id` (`lead_id`),
  INDEX `idx_solution_id` (`solution_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_scheduled` (`scheduled_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: sales_pipeline
-- -----------------------------------------------------
CREATE TABLE `sales_pipeline` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `lead_id` INT UNSIGNED NOT NULL,
  `stage` ENUM('lead', 'discovery', 'demo', 'proposal', 'negotiation', 'won', 'lost') DEFAULT 'lead',
  `value_estimate` DECIMAL(12,2) DEFAULT 0,
  `currency` VARCHAR(3) DEFAULT 'XOF',
  `probability` INT DEFAULT 0,
  `expected_close_date` DATE DEFAULT NULL,
  `actual_close_date` DATE DEFAULT NULL,
  `loss_reason` TEXT,
  `activities` JSON,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`lead_id`) REFERENCES `leads`(`id`) ON DELETE CASCADE,
  INDEX `idx_lead_id` (`lead_id`),
  INDEX `idx_stage` (`stage`),
  INDEX `idx_expected_close` (`expected_close_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: testimonials
-- -----------------------------------------------------
CREATE TABLE `testimonials` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `client_name` VARCHAR(100) NOT NULL,
  `company` VARCHAR(150) DEFAULT NULL,
  `role` VARCHAR(100) DEFAULT NULL,
  `photo` VARCHAR(255) DEFAULT NULL,
  `content` TEXT NOT NULL,
  `rating` INT DEFAULT 5,
  `solution_id` INT UNSIGNED DEFAULT NULL,
  `featured` BOOLEAN DEFAULT FALSE,
  `status` ENUM('active', 'inactive', 'pending') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`solution_id`) REFERENCES `solutions`(`id`) ON DELETE SET NULL,
  INDEX `idx_featured` (`featured`),
  INDEX `idx_status` (`status`),
  INDEX `idx_solution` (`solution_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: faqs
-- -----------------------------------------------------
CREATE TABLE `faqs` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `question` TEXT NOT NULL,
  `answer` TEXT NOT NULL,
  `solution_id` INT UNSIGNED DEFAULT NULL,
  `category` VARCHAR(50) DEFAULT 'general',
  `order_position` INT DEFAULT 0,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`solution_id`) REFERENCES `solutions`(`id`) ON DELETE SET NULL,
  INDEX `idx_solution` (`solution_id`),
  INDEX `idx_category` (`category`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: media
-- -----------------------------------------------------
CREATE TABLE `media` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `file_name` VARCHAR(255) NOT NULL,
  `original_name` VARCHAR(255) NOT NULL,
  `file_path` VARCHAR(500) NOT NULL,
  `file_type` ENUM('image', 'video', 'document', 'audio') DEFAULT 'image',
  `mime_type` VARCHAR(100) DEFAULT NULL,
  `file_size` INT UNSIGNED DEFAULT 0,
  `dimensions` VARCHAR(50) DEFAULT NULL,
  `alt_text` VARCHAR(255) DEFAULT NULL,
  `uploaded_by` INT UNSIGNED DEFAULT NULL,
  `usage_count` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_file_type` (`file_type`),
  INDEX `idx_uploaded_by` (`uploaded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: contact_messages
-- -----------------------------------------------------
CREATE TABLE `contact_messages` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(150) NOT NULL,
  `subject` VARCHAR(200) DEFAULT NULL,
  `message` TEXT NOT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `status` ENUM('new', 'read', 'replied', 'archived') DEFAULT 'new',
  `replied_at` DATETIME DEFAULT NULL,
  `replied_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_email` (`email`),
  INDEX `idx_status` (`status`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: newsletter_subscribers
-- -----------------------------------------------------
CREATE TABLE `newsletter_subscribers` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(150) NOT NULL UNIQUE,
  `status` ENUM('subscribed', 'unsubscribed', 'bounced', 'pending') DEFAULT 'pending',
  `verification_token` VARCHAR(100) DEFAULT NULL,
  `verified_at` DATETIME DEFAULT NULL,
  `subscribed_at` DATETIME DEFAULT NULL,
  `unsubscribed_at` DATETIME DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `metadata` JSON DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_email` (`email`),
  INDEX `idx_status` (`status`),
  INDEX `idx_subscribed_at` (`subscribed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: analytics
-- -----------------------------------------------------
CREATE TABLE `analytics` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `page` VARCHAR(255) NOT NULL,
  `page_type` VARCHAR(50) DEFAULT 'page',
  `visitor_id` VARCHAR(64) DEFAULT NULL,
  `session_id` VARCHAR(64) DEFAULT NULL,
  `visitor_ip` VARCHAR(45) DEFAULT NULL,
  `country` VARCHAR(100) DEFAULT NULL,
  `city` VARCHAR(100) DEFAULT NULL,
  `device_type` ENUM('desktop', 'tablet', 'mobile') DEFAULT 'desktop',
  `browser` VARCHAR(100) DEFAULT NULL,
  `os` VARCHAR(100) DEFAULT NULL,
  `referrer` VARCHAR(500) DEFAULT NULL,
  `utm_source` VARCHAR(100) DEFAULT NULL,
  `utm_medium` VARCHAR(100) DEFAULT NULL,
  `utm_campaign` VARCHAR(100) DEFAULT NULL,
  `time_on_page` INT DEFAULT 0,
  `visited_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_page` (`page`),
  INDEX `idx_visitor` (`visitor_id`),
  INDEX `idx_session` (`session_id`),
  INDEX `idx_visited_at` (`visited_at`),
  INDEX `idx_country` (`country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: settings
-- -----------------------------------------------------
CREATE TABLE `settings` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `site_name` VARCHAR(100) DEFAULT 'ZLO Technologies',
  `site_tagline` VARCHAR(255) DEFAULT 'Digital Innovation for Africa',
  `logo` VARCHAR(255) DEFAULT NULL,
  `logo_white` VARCHAR(255) DEFAULT NULL,
  `favicon` VARCHAR(255) DEFAULT NULL,
  `contact_email` VARCHAR(150) DEFAULT 'contact@zlotech.net',
  `contact_phone` VARCHAR(20) DEFAULT '+228 92 60 59 49',
  `contact_address` TEXT,
  `social_facebook` VARCHAR(255) DEFAULT NULL,
  `social_twitter` VARCHAR(255) DEFAULT NULL,
  `social_linkedin` VARCHAR(255) DEFAULT NULL,
  `social_instagram` VARCHAR(255) DEFAULT NULL,
  `social_youtube` VARCHAR(255) DEFAULT NULL,
  `google_analytics_id` VARCHAR(50) DEFAULT NULL,
  `google_tag_manager` VARCHAR(50) DEFAULT NULL,
  `facebook_pixel` VARCHAR(50) DEFAULT NULL,
  `meta_default_title` VARCHAR(150) DEFAULT NULL,
  `meta_default_description` TEXT,
  `meta_default_keywords` TEXT,
  `custom_css` TEXT,
  `custom_js` TEXT,
  `maintenance_mode` BOOLEAN DEFAULT FALSE,
  `maintenance_message` TEXT,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================
-- SEED DATA
-- =====================================================

-- Default Roles
INSERT INTO `roles` (`name`, `description`) VALUES
('super_admin', 'Full system access'),
('admin', 'Administrative access'),
('editor', 'Content management'),
('sales', 'Sales team access'),
('viewer', 'Read-only access');

-- Default Permissions
INSERT INTO `permissions` (`name`, `module`, `description`) VALUES
('users.view', 'users', 'View users'),
('users.create', 'users', 'Create users'),
('users.edit', 'users', 'Edit users'),
('users.delete', 'users', 'Delete users'),
('solutions.view', 'solutions', 'View solutions'),
('solutions.create', 'solutions', 'Create solutions'),
('solutions.edit', 'solutions', 'Edit solutions'),
('solutions.delete', 'solutions', 'Delete solutions'),
('leads.view', 'leads', 'View leads'),
('leads.create', 'leads', 'Create leads'),
('leads.edit', 'leads', 'Edit leads'),
('leads.delete', 'leads', 'Delete leads'),
('blog.view', 'blog', 'View blog posts'),
('blog.create', 'blog', 'Create blog posts'),
('blog.edit', 'blog', 'Edit blog posts'),
('blog.delete', 'blog', 'Delete blog posts'),
('pages.view', 'pages', 'View pages'),
('pages.create', 'pages', 'Create pages'),
('pages.edit', 'pages', 'Edit pages'),
('pages.delete', 'pages', 'Delete pages'),
('settings.view', 'settings', 'View settings'),
('settings.edit', 'settings', 'Edit settings'),
('analytics.view', 'analytics', 'View analytics');

-- Assign all permissions to super_admin
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 1, id FROM `permissions`;

-- Default Admin User (password: admin123)
-- Password hash generated with password_hash('admin123', PASSWORD_DEFAULT)
INSERT INTO `users` (`name`, `email`, `password`, `role_id`, `status`) VALUES
('Administrator', 'admin@zlotech.net', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1, 'active');

-- Default Settings
INSERT INTO `settings` (`site_name`, `site_tagline`, `contact_email`, `contact_phone`, `contact_address`) VALUES
('ZLO Technologies', 'Digital Innovation for Africa', 'contact@zlotech.net', '+228 92 60 59 49', 'Lomé, Togo');

-- Default Industries
INSERT INTO `industries` (`name`, `slug`, `icon`, `description`) VALUES
('Transport & Logistique', 'transport-logistique', 'fa-truck', 'Solutions pour le transport et la logistique'),
('Gouvernement', 'gouvernement', 'fa-landmark', 'Solutions pour les institutions publiques'),
('Éducation', 'education', 'fa-graduation-cap', 'Solutions pour le secteur éducatif'),
('Télécommunications', 'telecommunications', 'fa-signal', 'Solutions pour les opérateurs telecom'),
('Commerce', 'commerce', 'fa-store', 'Solutions pour le commerce et la distribution'),
('Santé', 'sante', 'fa-hospital', 'Solutions pour le secteur de la santé');

-- Default Solutions
INSERT INTO `solutions` (`title`, `slug`, `subtitle`, `short_description`, `full_description`, `icon`, `featured`, `status`, `order_position`) VALUES
('Fleet Intelligence Platform', 'fleet-intelligence', 'Suivi et gestion de flotte en temps réel', 'Plateforme complète de gestion de flotte avec géolocalisation, analyse de performance et optimisation des itinéraires.', 'Notre plateforme Fleet Intelligence offre une visibilité totale sur votre flotte de véhicules. Grâce à la technologie GPS avancée et l\'analyse de données en temps réel, optimisez vos opérations, réduisez les coûts et améliorez la sécurité de vos conducteurs.', 'fa-truck-moving', 1, 'active', 1),
('Smart Commerce Platform', 'smart-commerce', 'E-commerce et livraison rapide', 'Solution e-commerce complète avec gestion de stock, paiement mobile et livraison express intégrée.', 'Smart Commerce Platform transforme votre business avec une solution e-commerce de nouvelle génération. Intégrez facilement les paiements mobiles, gérez vos stocks en temps réel et offrez une expérience d\'achat fluide à vos clients.', 'fa-shopping-cart', 1, 'active', 2),
('School Mobility Platform', 'school-mobility', 'Transport scolaire intelligent', 'Sécurisez le transport scolaire avec suivi en temps réel, notifications parents et gestion optimisée des itinéraires.', 'School Mobility Platform révolutionne le transport scolaire au Togo et en Afrique. Les parents suivent le trajet de leurs enfants en temps réel, les écoles optimisent leurs tournées et la sécurité est garantie à chaque étape.', 'fa-school', 1, 'active', 3),
('AI Automation for Enterprise', 'ai-automation', 'Automatisation intelligente pour entreprises', 'Automatisez vos processus métier avec l\'intelligence artificielle et gagnez en productivité.', 'Notre solution d\'automatisation IA analyse vos processus métier, identifie les opportunités d\'optimisation et met en place des workflows automatisés. Réduisez les tâches répétitives et concentrez-vous sur la valeur ajoutée.', 'fa-robot', 1, 'active', 4);

-- Default Blog Categories
INSERT INTO `blog_categories` (`name`, `slug`, `description`, `color`) VALUES
('Technologie', 'technologie', 'Actualités et innovations technologiques', '#0066FF'),
('Mobilité', 'mobilite', 'Transport et solutions de mobilité', '#00C853'),
('E-commerce', 'e-commerce', 'Commerce digital et vente en ligne', '#FF6D00'),
('IA & Data', 'ia-data', 'Intelligence artificielle et analyse de données', '#AA00FF'),
('Entreprise', 'entreprise', 'Conseils et bonnes pratiques business', '#2962FF');

-- Default Menus
INSERT INTO `menus` (`name`, `location`) VALUES
('Main Header', 'header'),
('Footer Links', 'footer');

-- Default Menu Items
INSERT INTO `menu_items` (`menu_id`, `title`, `url`, `order_position`) VALUES
(1, 'Accueil', '/', 1),
(1, 'Solutions', '/solutions', 2),
(1, 'Industries', '/industries', 3),
(1, 'Cas clients', '/case-studies', 4),
(1, 'Blog', '/blog', 5),
(1, 'À propos', '/about', 6),
(1, 'Contact', '/contact', 7);

-- Default FAQs
INSERT INTO `faqs` (`question`, `answer`, `category`, `order_position`) VALUES
('Comment puis-je demander une démonstration ?', 'Vous pouvez demander une démonstration en remplissant le formulaire sur notre page "Demander une démo". Notre équipe vous contactera sous 24 heures ouvrées pour planifier une session personnalisée.', 'general', 1),
('Quels sont les délais de déploiement ?', 'Les délais de déploiement varient selon la complexité du projet. Une solution standard peut être déployée en 2-4 semaines, tandis qu\'un projet sur mesure peut prendre 2-3 mois.', 'general', 2),
('Proposez-vous un support technique ?', 'Oui, nous offrons un support technique 24/7 pour tous nos clients. Vous pouvez nous contacter par email, téléphone ou via notre portail client.', 'general', 3),
('Quelles sont les options de paiement ?', 'Nous acceptons les virements bancaires, les paiements mobiles (Flooz, TMoney) et les paiements par carte. Pour les entreprises, nous proposons également des modalités de paiement échelonné.', 'general', 4);

-- =====================================================
-- VIEWS FOR ANALYTICS
-- =====================================================

CREATE VIEW `v_lead_stats` AS
SELECT 
  status,
  COUNT(*) as count,
  AVG(score) as avg_score
FROM leads
GROUP BY status;

CREATE VIEW `v_pipeline_value` AS
SELECT 
  stage,
  COUNT(*) as deals_count,
  SUM(value_estimate) as total_value,
  AVG(probability) as avg_probability
FROM sales_pipeline
GROUP BY stage;

CREATE VIEW `v_monthly_leads` AS
SELECT 
  DATE_FORMAT(created_at, '%Y-%m') as month,
  COUNT(*) as new_leads,
  SUM(CASE WHEN status = 'won' THEN 1 ELSE 0 END) as converted_leads
FROM leads
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month DESC;

-- =====================================================
-- STORED PROCEDURES
-- =====================================================

DELIMITER //

CREATE PROCEDURE `sp_get_dashboard_stats`()
BEGIN
  SELECT 
    (SELECT COUNT(*) FROM leads WHERE status = 'new') as new_leads,
    (SELECT COUNT(*) FROM demo_requests WHERE status = 'pending') as pending_demos,
    (SELECT COUNT(*) FROM contact_messages WHERE status = 'new') as unread_messages,
    (SELECT SUM(value_estimate) FROM sales_pipeline WHERE stage IN ('proposal', 'negotiation')) as pipeline_value,
    (SELECT COUNT(*) FROM blog_posts WHERE status = 'published') as published_posts,
    (SELECT COUNT(*) FROM users WHERE status = 'active') as active_users;
END //

CREATE PROCEDURE `sp_update_lead_score`(IN lead_id INT)
BEGIN
  DECLARE new_score INT DEFAULT 0;
  
  -- Score based on company size
  SET new_score = new_score + CASE 
    WHEN (SELECT company_size FROM leads WHERE id = lead_id) = '500+' THEN 30
    WHEN (SELECT company_size FROM leads WHERE id = lead_id) = '201-500' THEN 25
    WHEN (SELECT company_size FROM leads WHERE id = lead_id) = '51-200' THEN 20
    WHEN (SELECT company_size FROM leads WHERE id = lead_id) = '11-50' THEN 15
    ELSE 10
  END;
  
  -- Score based on demo request
  SET new_score = new_score + CASE 
    WHEN EXISTS (SELECT 1 FROM demo_requests WHERE lead_id = lead_id) THEN 20
    ELSE 0
  END;
  
  -- Score based on message completeness
  SET new_score = new_score + CASE 
    WHEN (SELECT message FROM leads WHERE id = lead_id) IS NOT NULL 
         AND LENGTH((SELECT message FROM leads WHERE id = lead_id)) > 50 THEN 10
    ELSE 0
  END;
  
  UPDATE leads SET score = new_score WHERE id = lead_id;
END //

DELIMITER ;

-- =====================================================
-- TRIGGERS
-- =====================================================

DELIMITER //

CREATE TRIGGER `trg_lead_created` 
AFTER INSERT ON `leads`
FOR EACH ROW
BEGIN
  -- Create initial pipeline entry
  INSERT INTO sales_pipeline (lead_id, stage, value_estimate, probability)
  VALUES (NEW.id, 'lead', 0, 10);
  
  -- Calculate initial score
  CALL sp_update_lead_score(NEW.id);
END //

CREATE TRIGGER `trg_post_published`
BEFORE UPDATE ON `blog_posts`
FOR EACH ROW
BEGIN
  IF NEW.status = 'published' AND OLD.status != 'published' THEN
    SET NEW.published_at = NOW();
  END IF;
END //

DELIMITER ;
