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

CREATE TABLE users (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password VARCHAR(255) NOT NULL,
 role VARCHAR(60) NOT NULL DEFAULT 'Administrator',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE projects (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(180) NOT NULL,
 location VARCHAR(180),
 client VARCHAR(180),
 status ENUM('Planning','Active','Completed','On Hold') DEFAULT 'Planning',
 start_date DATE NULL,
 end_date DATE NULL,
 description TEXT,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE personnel (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL,
 role VARCHAR(100),
 department VARCHAR(100),
 phone VARCHAR(40),
 email VARCHAR(190),
 status ENUM('Active','Inactive','On Leave') DEFAULT 'Active',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE equipment (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL,
 category VARCHAR(100),
 serial_number VARCHAR(100),
 status ENUM('Available','In Use','Maintenance','Inactive') DEFAULT 'Available',
 location VARCHAR(180),
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE activities (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 project_id INT UNSIGNED NULL,
 title VARCHAR(180) NOT NULL,
 location VARCHAR(180),
 priority ENUM('Low','Medium','High','Critical') DEFAULT 'Medium',
 status ENUM('Planned','In Progress','Completed','On Hold') DEFAULT 'Planned',
 start_time DATETIME NULL,
 description TEXT,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_activity_project FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE SET NULL
);

INSERT INTO users(name,email,password,role) VALUES
('System Administrator','admin@oilgas.local','$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC6j2x7R0oH6nXqgV5O','Administrator');

INSERT INTO projects(name,location,client,status,start_date,description) VALUES
('Offshore Well Intervention','Bonny Offshore Field','Sample Energy Ltd','Active','2026-08-01','Offshore well intervention and production support.'),
('Pipeline Integrity Inspection','Rivers State','Sample Petroleum Ltd','Planning','2026-09-01','Inspection and integrity assessment programme.');

INSERT INTO personnel(name,role,department,status) VALUES
('David Okoro','Field Engineer','Operations','Active'),
('Ibrahim Musa','HSE Supervisor','HSE','Active'),
('Grace Eze','Logistics Coordinator','Logistics','Active');

INSERT INTO equipment(name,category,serial_number,status,location) VALUES
('Hydraulic Pump Unit','Production Equipment','HPU-001','Available','Bonny Base'),
('Inspection Vehicle','Logistics','VEH-014','In Use','Port Harcourt'),
('Portable Generator','Power','GEN-032','Maintenance','Maintenance Yard');

INSERT INTO activities(project_id,title,location,priority,status,start_time,description) VALUES
(1,'Wellhead pressure inspection','Offshore Platform A','High','In Progress','2026-08-20 08:00:00','Inspect wellhead pressure and record operating parameters.'),
(1,'HSE toolbox meeting','Offshore Platform A','Medium','Completed','2026-08-20 06:30:00','Pre-shift safety briefing.'),
(2,'Pipeline route survey','Rivers State','Medium','Planned','2026-08-21 09:00:00','Initial route survey and inspection planning.');