-- EGTF BTP Admin Dashboard Database Schema

-- Admin settings table (for password and site config)
CREATE TABLE IF NOT EXISTS admin_settings (
  id SERIAL PRIMARY KEY,
  key VARCHAR(255) UNIQUE NOT NULL,
  value TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Projects table
CREATE TABLE IF NOT EXISTS projects (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  category VARCHAR(255) NOT NULL,
  location VARCHAR(255) NOT NULL,
  description TEXT,
  image_url TEXT NOT NULL,
  is_featured BOOLEAN DEFAULT false,
  display_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Gallery table (images and videos)
CREATE TABLE IF NOT EXISTS gallery (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  media_type VARCHAR(50) NOT NULL CHECK (media_type IN ('image', 'video')),
  media_url TEXT NOT NULL,
  thumbnail_url TEXT,
  category VARCHAR(255),
  is_featured BOOLEAN DEFAULT false,
  display_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Team members table
CREATE TABLE IF NOT EXISTS team_members (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  title VARCHAR(255) NOT NULL,
  role VARCHAR(255) NOT NULL,
  description TEXT,
  image_url TEXT,
  experience VARCHAR(50),
  experience_label VARCHAR(255),
  projects_count VARCHAR(50),
  projects_label VARCHAR(255),
  display_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Services table
CREATE TABLE IF NOT EXISTS services (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  icon VARCHAR(100) NOT NULL,
  display_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Contact messages table
CREATE TABLE IF NOT EXISTS contact_messages (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(50),
  company VARCHAR(255),
  service VARCHAR(255),
  message TEXT NOT NULL,
  is_read BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Site content table (for hero text, about text, etc.)
CREATE TABLE IF NOT EXISTS site_content (
  id SERIAL PRIMARY KEY,
  section VARCHAR(100) NOT NULL,
  key VARCHAR(255) NOT NULL,
  value TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(section, key)
);

-- Insert default admin password (change this!)
INSERT INTO admin_settings (key, value) 
VALUES ('admin_password', 'egtf2024admin')
ON CONFLICT (key) DO NOTHING;

-- Insert default site content
INSERT INTO site_content (section, key, value) VALUES
('hero', 'badge', 'Expert en BTP depuis plus de 10 ans'),
('hero', 'title', 'Construire l''avenir avec Excellence et Expertise'),
('hero', 'description', 'EGTF BTP réalise un large éventail de travaux de construction: ponts, routes, logements, génie civil et installations industrielles.'),
('about', 'title', 'Un Leader dans la Construction et les Travaux Publics'),
('about', 'description', 'EGTF BTP est une entreprise de construction de premier plan spécialisée dans les projets d''infrastructure et de travaux publics. Notre expertise couvre tous les aspects de la construction, de la conception à la réalisation.'),
('contact', 'address', 'ACI GOLF BP 2265'),
('contact', 'city', 'Bamako, Mali'),
('contact', 'phone1', '(+223) 20 28 00 65'),
('contact', 'phone2', '(+223) 66 78 83 78'),
('contact', 'phone3', '(+223) 66 74 59 01'),
('contact', 'email', 'info@egtf-mali.com'),
('contact', 'fax', '(+223) 54 713 79 90'),
('contact', 'hours', 'Lun - Sam: 9h00 - 17h00')
ON CONFLICT (section, key) DO NOTHING;