-- Create users table if it doesn't exist
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_identifier` varchar(50) NOT NULL UNIQUE,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL UNIQUE,
  `password` varchar(255) NOT NULL,
  `role` enum('owner','regional_manager','yard_manager','dispatch','driver','field_rep','mechanic','wash_crew') NOT NULL,
  `status` enum('active','inactive','suspended') DEFAULT 'active',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert sample data if the table is empty
-- Owner (ID: 1)
INSERT INTO `users` (`user_identifier`, `name`, `email`, `password`, `role`, `status`)
SELECT 'U0001', 'Admin User', 'admin@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'owner', 'active'
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `users` WHERE `email` = 'admin@example.com');

-- Regional Manager (ID: 2)
INSERT INTO `users` (`user_identifier`, `name`, `email`, `password`, `role`, `status`, `created_by`)
SELECT 'U0002', 'Regional Manager', 'regional@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'regional_manager', 'active', 1
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `users` WHERE `email` = 'regional@example.com');

-- Yard Manager (ID: 3)
INSERT INTO `users` (`user_identifier`, `name`, `email`, `password`, `role`, `status`, `created_by`)
SELECT 'U0003', 'Yard Manager', 'yard@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'yard_manager', 'active', 2
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `users` WHERE `email` = 'yard@example.com');

-- Dispatcher (ID: 4)
INSERT INTO `users` (`user_identifier`, `name`, `email`, `password`, `role`, `status`, `created_by`)
SELECT 'U0004', 'Dispatch User', 'dispatch@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'dispatch', 'active', 3
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `users` WHERE `email` = 'dispatch@example.com');

-- Drivers (IDs: 5-8)
INSERT INTO `users` (`user_identifier`, `name`, `email`, `password`, `role`, `status`, `created_by`)
SELECT 'U0005', 'John Smith', 'john@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'driver', 'active', 4
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `users` WHERE `email` = 'john@example.com');

INSERT INTO `users` (`user_identifier`, `name`, `email`, `password`, `role`, `status`, `created_by`)
SELECT 'U0006', 'Sarah Johnson', 'sarah@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'driver', 'active', 4
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `users` WHERE `email` = 'sarah@example.com');

INSERT INTO `users` (`user_identifier`, `name`, `email`, `password`, `role`, `status`, `created_by`)
SELECT 'U0007', 'Michael Brown', 'michael@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'driver', 'active', 4
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `users` WHERE `email` = 'michael@example.com');

INSERT INTO `users` (`user_identifier`, `name`, `email`, `password`, `role`, `status`, `created_by`)
SELECT 'U0008', 'Emily Davis', 'emily@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'driver', 'active', 4
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `users` WHERE `email` = 'emily@example.com');

-- Field Reps/Mechanics (IDs: 9-10)
INSERT INTO `users` (`user_identifier`, `name`, `email`, `password`, `role`, `status`, `created_by`)
SELECT 'U0009', 'David Wilson', 'david@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'field_rep', 'active', 4
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `users` WHERE `email` = 'david@example.com');

INSERT INTO `users` (`user_identifier`, `name`, `email`, `password`, `role`, `status`, `created_by`)
SELECT 'U0010', 'Jessica Martinez', 'jessica@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'mechanic', 'active', 4
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `users` WHERE `email` = 'jessica@example.com');

-- Wash Crew (IDs: 11-12)
INSERT INTO `users` (`user_identifier`, `name`, `email`, `password`, `role`, `status`, `created_by`)
SELECT 'U0011', 'Robert Taylor', 'robert@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'wash_crew', 'active', 3
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `users` WHERE `email` = 'robert@example.com');

INSERT INTO `users` (`user_identifier`, `name`, `email`, `password`, `role`, `status`, `created_by`)
SELECT 'U0012', 'Amanda Clark', 'amanda@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'wash_crew', 'active', 3
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `users` WHERE `email` = 'amanda@example.com');
