CREATE DATABASE IF NOT EXISTS employer_management CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE employer_management;

CREATE TABLE IF NOT EXISTS employers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    matricule VARCHAR(50) UNIQUE NOT NULL,
    nom_responsable VARCHAR(100) NOT NULL,
    numero_telephone VARCHAR(20) NOT NULL,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    agent_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_matricule (matricule),
    INDEX idx_agent_id (agent_id),
    INDEX idx_location (latitude, longitude)
);

