-- AFRI SYSTEME initial schema (MySQL)
CREATE DATABASE IF NOT EXISTS afrisysteme CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE afrisysteme;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150),
  email VARCHAR(150) UNIQUE,
  password_hash VARCHAR(255),
  role VARCHAR(50) DEFAULT 'client',
  created_at DATETIME DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS clients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_name VARCHAR(255),
  contact_name VARCHAR(150),
  phone VARCHAR(50),
  email VARCHAR(150),
  address TEXT,
  created_at DATETIME DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS stations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT,
  name VARCHAR(255),
  location_lat DECIMAL(10,7),
  location_lng DECIMAL(10,7),
  status VARCHAR(50) DEFAULT 'active',
  last_telemetry DATETIME,
  created_at DATETIME DEFAULT NOW(),
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS devices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  station_id INT,
  type VARCHAR(50),
  serial_number VARCHAR(100),
  firmware_version VARCHAR(50),
  created_at DATETIME DEFAULT NOW(),
  FOREIGN KEY (station_id) REFERENCES stations(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS telemetry (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  device_id INT,
  timestamp DATETIME,
  fuel_level FLOAT,
  flow FLOAT,
  temperature FLOAT,
  FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS leads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150),
  email VARCHAR(150),
  company VARCHAR(255),
  message TEXT,
  created_at DATETIME DEFAULT NOW()
);

-- Sample data
INSERT INTO clients (company_name, contact_name, phone, email, address) VALUES
('Société Energy SA', 'Mme Diop', '+221700000', 'contact@energy.sn', 'Dakar, SN');

INSERT INTO stations (client_id, name, location_lat, location_lng, status, last_telemetry) VALUES
(1,'Station Dakar Centre', 14.6937, -17.4441, 'active', NOW());

