-- Additional Tables for Enhanced Admin Features

-- Testimonials Table
CREATE TABLE IF NOT EXISTS testimonials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_name VARCHAR(255) NOT NULL,
    client_position VARCHAR(255),
    company_name VARCHAR(255),
    testimonial_text TEXT NOT NULL,
    rating INT DEFAULT 5,
    service_type VARCHAR(100),
    client_photo VARCHAR(255),
    status ENUM('active', 'inactive') DEFAULT 'active',
    featured BOOLEAN DEFAULT FALSE,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_featured (featured),
    INDEX idx_display_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- About Section Content Table
CREATE TABLE IF NOT EXISTS about_content (
    id INT AUTO_INCREMENT PRIMARY KEY,
    section_key VARCHAR(100) NOT NULL UNIQUE,
    title VARCHAR(255),
    content TEXT,
    image_url VARCHAR(255),
    display_order 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_section_key (section_key),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FAQ Table
CREATE TABLE IF NOT EXISTS faqs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question TEXT NOT NULL,
    answer TEXT NOT NULL,
    category VARCHAR(100),
    display_order 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_category (category),
    INDEX idx_status (status),
    INDEX idx_display_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Resources Table
CREATE TABLE IF NOT EXISTS resources (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    resource_type ENUM('guide', 'blog', 'video', 'document', 'link') DEFAULT 'guide',
    file_url VARCHAR(255),
    external_url VARCHAR(500),
    icon VARCHAR(100),
    category VARCHAR(100),
    download_count INT DEFAULT 0,
    status ENUM('active', 'inactive') DEFAULT 'active',
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_resource_type (resource_type),
    INDEX idx_status (status),
    INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Team Members Table
CREATE TABLE IF NOT EXISTS team_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    position VARCHAR(255),
    bio TEXT,
    photo VARCHAR(255),
    email VARCHAR(255),
    linkedin_url VARCHAR(500),
    display_order 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_status (status),
    INDEX idx_display_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default about content sections
INSERT INTO about_content (section_key, title, content, display_order) VALUES
('mission', 'Our Mission', 'To provide reliable water and clean energy solutions that empower communities and industries across Kenya.', 1),
('values', 'Our Values', 'Sustainability, Reliability, Safety, and Community Impact guide everything we do.', 2),
('vision', 'Our Vision', 'To be the leading provider of water and renewable energy solutions in East Africa.', 3)
ON DUPLICATE KEY UPDATE title = VALUES(title), content = VALUES(content), display_order = VALUES(display_order);

-- Site Settings Table
CREATE TABLE IF NOT EXISTS site_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    setting_type ENUM('boolean', 'text', 'number', 'json') DEFAULT 'text',
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_setting_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Services Table
CREATE TABLE IF NOT EXISTS services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    short_description TEXT,
    full_description TEXT,
    icon_class VARCHAR(100), -- For Font Awesome or Material Icons class
    icon_type ENUM('fontawesome', 'material', 'custom') DEFAULT 'fontawesome',
    color VARCHAR(7) DEFAULT '#FF6B35', -- Hex color
    category VARCHAR(100),
    features TEXT, -- JSON array of features
    image_url VARCHAR(255),
    display_order 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_status (status),
    INDEX idx_category (category),
    INDEX idx_display_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Statistics Table
CREATE TABLE IF NOT EXISTS statistics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    value INT NOT NULL,
    suffix VARCHAR(10) DEFAULT '',
    label VARCHAR(255) NOT NULL,
    icon_class VARCHAR(100),
    color VARCHAR(7) DEFAULT '#00B4D8',
    display_order 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_status (status),
    INDEX idx_display_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Technology & Equipment Table
CREATE TABLE IF NOT EXISTS technology_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    icon_class VARCHAR(100),
    image_url VARCHAR(255),
    category VARCHAR(100),
    features TEXT, -- JSON array
    display_order 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_status (status),
    INDEX idx_category (category),
    INDEX idx_display_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default statistics
INSERT IGNORE INTO statistics (value, suffix, label, icon_class, color, display_order) VALUES
(72, ' hrs', 'Average mobilization', 'fa-clock', '#00B4D8', 1),
(99, '%', 'Safety record', 'fa-shield-alt', '#10B981', 2),
(40, '%', 'Energy savings', 'fa-bolt', '#FF6B35', 3),
(3, 'M+', 'Liters pumped daily', 'fa-tint', '#00B4D8', 4);

-- Videos Table
CREATE TABLE IF NOT EXISTS videos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    video_url VARCHAR(500) NOT NULL, -- YouTube, Vimeo, or direct video URL
    video_type ENUM('youtube', 'vimeo', 'direct', 'embed') DEFAULT 'youtube',
    thumbnail_url VARCHAR(500),
    autoplay BOOLEAN DEFAULT TRUE,
    `loop` BOOLEAN DEFAULT FALSE,
    muted BOOLEAN DEFAULT TRUE,
    show_controls BOOLEAN DEFAULT TRUE,
    display_order 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_status (status),
    INDEX idx_display_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default technology items
INSERT IGNORE INTO technology_items (title, description, icon_class, category, features, display_order) VALUES
('Advanced Drilling Rigs', 'State-of-the-art drilling equipment capable of reaching depths up to 300 meters with precision and efficiency.', 'fa-hard-hat', 'Equipment', '["Up to 300m depth", "Precision drilling", "Modern technology"]', 1),
('Geophysical Survey Tools', 'Cutting-edge geophysical instruments for accurate groundwater detection and aquifer mapping.', 'fa-search-location', 'Survey', '["Groundwater detection", "Aquifer mapping", "Data accuracy"]', 2),
('Solar Pump Systems', 'Efficient solar-powered pumping solutions with smart controllers and battery backup.', 'fa-solar-panel', 'Solar', '["Zero electricity costs", "Smart controllers", "Battery backup"]', 3),
('Water Quality Testing', 'Comprehensive water quality analysis equipment ensuring safe and clean water supply.', 'fa-flask', 'Testing', '["Quality assurance", "Comprehensive analysis", "Safety standards"]', 4);

-- Insert default services
INSERT IGNORE INTO services (title, short_description, full_description, icon_class, color, category, features, display_order) VALUES
('Hydrogeological Survey', 
 'Scientific groundwater assessment using advanced geophysical techniques to locate reliable water sources.',
 'Our comprehensive hydrogeological surveys utilize cutting-edge geophysical methods including electrical resistivity, seismic refraction, and ground-penetrating radar. We analyze geological formations, water table depths, and aquifer characteristics to identify optimal drilling locations with maximum yield potential.',
 'fa-search-location', '#00B4D8', 'Survey', 
 '["Advanced geophysical techniques", "Aquifer mapping", "Water quality assessment", "Drilling site recommendations"]', 1),
('Borehole Drilling', 
 'Professional drilling services with modern rigs, ensuring quality and durability for long-term water access.',
 'We operate state-of-the-art drilling rigs capable of reaching depths up to 300 meters. Our experienced team follows international safety standards and uses premium casing materials to ensure your borehole provides reliable water access for decades.',
 'fa-hard-hat', '#0A1828', 'Drilling',
 '["Modern drilling equipment", "Up to 300m depth", "Premium casing materials", "International safety standards"]', 2),
('Solarized Pumps', 
 'Solar-powered pump installations for sustainable, cost-effective water extraction and distribution.',
 'Our solar pump systems eliminate electricity costs while providing reliable water supply. We design custom solutions based on your water requirements, well depth, and daily usage patterns. All systems include battery backup and smart controllers for optimal efficiency.',
 'fa-solar-panel', '#FF6B35', 'Solar',
 '["Zero electricity costs", "Battery backup systems", "Smart controllers", "Custom solutions"]', 3),
('Home Solar', 
 'Complete solar energy solutions for residential properties, reducing electricity costs and carbon footprint.',
 'Transform your home into an energy-efficient powerhouse with our comprehensive solar installations. From rooftop panels to battery storage systems, we provide end-to-end solutions that reduce your electricity bills by up to 90% while contributing to environmental sustainability.',
 'fa-home', '#FF6B35', 'Energy',
 '["Rooftop installations", "Battery storage", "Up to 90% savings", "Grid-tie systems"]', 4),
('Water Supply', 
 'Comprehensive water supply systems from source to tap, ensuring reliable access for communities and industries.',
 'We design and install complete water distribution networks including storage tanks, filtration systems, and piping infrastructure. Our solutions serve residential communities, commercial establishments, and industrial facilities with reliable, clean water supply.',
 'fa-tint', '#00B4D8', 'Supply',
 '["Storage solutions", "Filtration systems", "Distribution networks", "Community & industrial"]', 5),
('Irrigation', 
 'Efficient irrigation systems for agriculture and horticulture, maximizing crop yield and water conservation.',
 'Maximize your agricultural productivity with our smart irrigation systems. We offer drip irrigation, sprinkler systems, and automated controllers that optimize water usage, reduce waste, and increase crop yields by up to 40%.',
 'fa-seedling', '#06B6D4', 'Irrigation',
 '["Drip & sprinkler systems", "Automated controllers", "40% yield increase", "Water conservation"]', 6);

-- Insert default section visibility settings
INSERT INTO site_settings (setting_key, setting_value, setting_type, description) VALUES
('section_hero', '1', 'boolean', 'Show/Hide Hero Section'),
('section_services', '1', 'boolean', 'Show/Hide Services Section'),
('section_statistics', '1', 'boolean', 'Show/Hide Statistics Section'),
('section_video', '1', 'boolean', 'Show/Hide Video Showcase Section'),
('section_details', '1', 'boolean', 'Show/Hide Detail Sections'),
('section_testimonials', '1', 'boolean', 'Show/Hide Testimonials Section'),
('section_projects', '1', 'boolean', 'Show/Hide Projects Section'),
('section_gallery', '1', 'boolean', 'Show/Hide Gallery Section'),
('section_technology', '1', 'boolean', 'Show/Hide Technology Section'),
('section_about', '1', 'boolean', 'Show/Hide About Section'),
('section_resources', '1', 'boolean', 'Show/Hide Resources Section'),
('section_contact', '1', 'boolean', 'Show/Hide Contact Section'),
('color_primary', '#FF6B35', 'text', 'Primary Brand Color'),
('color_secondary', '#00B4D8', 'text', 'Secondary Brand Color'),
('color_accent', '#0A1828', 'text', 'Accent Color'),
('color_text', '#0F172A', 'text', 'Text Color'),
('color_bg', '#FFFFFF', 'text', 'Background Color')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value), setting_type = VALUES(setting_type), description = VALUES(description);