-- ZLO Technologies - Database Schema
-- Version simple et fonctionnelle

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Users table
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 ENUM('admin', 'editor') DEFAULT 'editor',
    avatar VARCHAR(255) DEFAULT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    last_login DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Services/Solutions table
CREATE TABLE services (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    description TEXT,
    full_content LONGTEXT,
    icon VARCHAR(50) DEFAULT 'fa-cog',
    image VARCHAR(255) DEFAULT NULL,
    featured BOOLEAN DEFAULT FALSE,
    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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Projects table
CREATE TABLE projects (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    client_name VARCHAR(150) DEFAULT NULL,
    description TEXT,
    full_content LONGTEXT,
    image VARCHAR(255) DEFAULT NULL,
    gallery JSON DEFAULT NULL,
    technologies JSON DEFAULT NULL,
    featured BOOLEAN DEFAULT FALSE,
    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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Team members table
CREATE TABLE team_members (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    role VARCHAR(100) NOT NULL,
    bio TEXT,
    image VARCHAR(255) DEFAULT NULL,
    email VARCHAR(150) DEFAULT NULL,
    linkedin 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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Contact messages table
CREATE TABLE contact_messages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    subject VARCHAR(200) DEFAULT NULL,
    message TEXT NOT NULL,
    status ENUM('new', 'read', 'replied', 'archived') DEFAULT 'new',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Settings table
CREATE TABLE settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default admin (password: admin123)
INSERT INTO users (name, email, password, role, status) VALUES 
('Administrateur', 'admin@zlotech.net', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'active');

-- Insert sample services
INSERT INTO services (title, slug, description, icon, featured, order_position, status) VALUES
('Transformation Digitale', 'transformation-digitale', 'Accompagnement complet dans votre transformation numérique avec des solutions sur mesure.', 'fa-laptop-code', 1, 1, 'active'),
('Développement Web & Mobile', 'developpement-web-mobile', 'Applications web et mobiles performantes et adaptées à vos besoins.', 'fa-mobile-alt', 1, 2, 'active'),
('Conseil IT', 'conseil-it', 'Expertise et conseil pour optimiser votre infrastructure informatique.', 'fa-lightbulb', 1, 3, 'active'),
('Support Technique', 'support-technique', 'Support 24/7 pour garantir la continuité de vos services.', 'fa-headset', 0, 4, 'active');

-- Insert sample projects
INSERT INTO projects (title, slug, client_name, description, featured, order_position, status) VALUES
('Portail Gouvernemental', 'portail-gouvernemental', 'Ministère de la Digitalisation', 'Développement d\'un portail citoyen moderne et accessible.', 1, 1, 'active'),
('Application Mobile', 'application-mobile', 'Startup Fintech', 'Application de paiement mobile sécurisée.', 1, 2, 'active'),
('Système ERP', 'systeme-erp', 'Entreprise Logistique', 'Implémentation d\'un ERP sur mesure.', 1, 3, 'active');

-- Insert sample team members
INSERT INTO team_members (name, role, bio, email, order_position, status) VALUES
('John Doe', 'CEO & Fondateur', 'Expert en transformation digitale avec 15 ans d\'expérience.', 'john@zlotech.net', 1, 'active'),
('Jane Smith', 'Directrice Technique', 'Ingénieure logicielle spécialisée en architecture cloud.', 'jane@zlotech.net', 2, 'active'),
('Mike Johnson', 'Lead Developer', 'Développeur full-stack passionné par les nouvelles technologies.', 'mike@zlotech.net', 3, 'active');

-- Insert default settings
INSERT INTO settings (setting_key, setting_value) VALUES
('site_name', 'ZLO Technologies'),
('site_tagline', 'Digital Innovation for Africa'),
('contact_email', 'contact@zlotech.net'),
('contact_phone', '+228 92 60 59 49'),
('contact_address', 'Lomé, Togo'),
('facebook_url', 'https://facebook.com/zlotech'),
('twitter_url', 'https://twitter.com/zlotech'),
('linkedin_url', 'https://linkedin.com/company/zlotech'),
('instagram_url', '');

SET FOREIGN_KEY_CHECKS = 1;
