CREATE DATABASE IF NOT EXISTS fsm CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE fsm;
CREATE TABLE users(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,email VARCHAR(190) UNIQUE NOT NULL,password_hash VARCHAR(255) NOT NULL,role ENUM('admin','manager','engineer','customer') NOT NULL,phone VARCHAR(40),status ENUM('active','inactive','former') DEFAULT 'active',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE customers(id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NULL,organization VARCHAR(180) NOT NULL,contact_name VARCHAR(120),phone VARCHAR(40),email VARCHAR(190),address TEXT,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE manufacturers(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) UNIQUE NOT NULL);
CREATE TABLE equipment(id INT AUTO_INCREMENT PRIMARY KEY,customer_id INT NOT NULL,manufacturer_id INT NULL,name VARCHAR(180) NOT NULL,model VARCHAR(120),serial_no VARCHAR(120),asset_tag VARCHAR(100) UNIQUE,department VARCHAR(120),location VARCHAR(255),install_date DATE,warranty_until DATE,status ENUM('active','inactive','condemned') DEFAULT 'active',FOREIGN KEY(customer_id) REFERENCES customers(id),FOREIGN KEY(manufacturer_id) REFERENCES manufacturers(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE engineer_skills(id INT AUTO_INCREMENT PRIMARY KEY,engineer_id INT NOT NULL,skill VARCHAR(150) NOT NULL,level ENUM('L1','L2','L3','L4') DEFAULT 'L1',FOREIGN KEY(engineer_id) REFERENCES users(id) ON DELETE CASCADE);
CREATE TABLE sla_rules(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50),priority ENUM('low','medium','high','critical') UNIQUE,response_minutes INT,resolution_minutes INT);
CREATE TABLE complaints(id INT AUTO_INCREMENT PRIMARY KEY,ticket_no VARCHAR(40) UNIQUE NOT NULL,customer_id INT NOT NULL,equipment_id INT NULL,created_by INT NOT NULL,assigned_engineer_id INT NULL,preferred_engineer_id INT NULL,title VARCHAR(200) NOT NULL,description TEXT,category VARCHAR(100),priority ENUM('low','medium','high','critical') DEFAULT 'medium',status ENUM('new','assigned','accepted','traveling','on_site','diagnosing','waiting_part','under_repair','resolved','customer_confirmation','closed','cancelled','reopened','escalated') DEFAULT 'new',sla_due_at DATETIME NULL,response_due_at DATETIME NULL,customer_confirmed_at DATETIME NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,resolved_at DATETIME NULL,closed_at DATETIME NULL,FOREIGN KEY(customer_id) REFERENCES customers(id),FOREIGN KEY(equipment_id) REFERENCES equipment(id) ON DELETE SET NULL,FOREIGN KEY(created_by) REFERENCES users(id),FOREIGN KEY(assigned_engineer_id) REFERENCES users(id) ON DELETE SET NULL,FOREIGN KEY(preferred_engineer_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE complaint_history(id INT AUTO_INCREMENT PRIMARY KEY,complaint_id INT NOT NULL,user_id INT NULL,status VARCHAR(50),note TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(complaint_id) REFERENCES complaints(id) ON DELETE CASCADE,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE complaint_comments(id INT AUTO_INCREMENT PRIMARY KEY,complaint_id INT NOT NULL,user_id INT NOT NULL,body TEXT NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(complaint_id) REFERENCES complaints(id) ON DELETE CASCADE,FOREIGN KEY(user_id) REFERENCES users(id));
CREATE TABLE attachments(id INT AUTO_INCREMENT PRIMARY KEY,complaint_id INT NOT NULL,user_id INT NOT NULL,file_name VARCHAR(255),stored_name VARCHAR(255),mime VARCHAR(120),size_bytes INT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(complaint_id) REFERENCES complaints(id) ON DELETE CASCADE,FOREIGN KEY(user_id) REFERENCES users(id));
CREATE TABLE service_visits(id INT AUTO_INCREMENT PRIMARY KEY,complaint_id INT NOT NULL,engineer_id INT NOT NULL,visit_started DATETIME,visit_ended DATETIME,latitude DECIMAL(10,7),longitude DECIMAL(10,7),notes TEXT,FOREIGN KEY(complaint_id) REFERENCES complaints(id) ON DELETE CASCADE,FOREIGN KEY(engineer_id) REFERENCES users(id));
CREATE TABLE service_reports(id INT AUTO_INCREMENT PRIMARY KEY,complaint_id INT UNIQUE NOT NULL,engineer_id INT NOT NULL,diagnosis TEXT,root_cause TEXT,action_taken TEXT,final_result TEXT,job_level ENUM('L1','L2','L3','L4') DEFAULT 'L1',parts_summary TEXT,engineer_signature VARCHAR(255),customer_signature VARCHAR(255),report_pdf VARCHAR(255),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,approved_at DATETIME NULL,FOREIGN KEY(complaint_id) REFERENCES complaints(id) ON DELETE CASCADE,FOREIGN KEY(engineer_id) REFERENCES users(id));
CREATE TABLE parts(id INT AUTO_INCREMENT PRIMARY KEY,part_no VARCHAR(80) UNIQUE,name VARCHAR(150),manufacturer VARCHAR(120),unit_cost DECIMAL(12,2) DEFAULT 0,min_stock INT DEFAULT 0,stock_qty INT DEFAULT 0);
CREATE TABLE service_report_parts(id INT AUTO_INCREMENT PRIMARY KEY,report_id INT NOT NULL,part_id INT NOT NULL,qty INT NOT NULL,FOREIGN KEY(report_id) REFERENCES service_reports(id) ON DELETE CASCADE,FOREIGN KEY(part_id) REFERENCES parts(id));
CREATE TABLE pm_schedules(id INT AUTO_INCREMENT PRIMARY KEY,equipment_id INT NOT NULL,frequency_months INT NOT NULL,last_done DATE,next_due DATE,status ENUM('scheduled','due','overdue','completed') DEFAULT 'scheduled',notes TEXT,FOREIGN KEY(equipment_id) REFERENCES equipment(id) ON DELETE CASCADE);
CREATE TABLE calibrations(id INT AUTO_INCREMENT PRIMARY KEY,equipment_id INT NOT NULL,performed_on DATE,due_on DATE,engineer_id INT NULL,result VARCHAR(100),certificate_file VARCHAR(255),notes TEXT,FOREIGN KEY(equipment_id) REFERENCES equipment(id) ON DELETE CASCADE,FOREIGN KEY(engineer_id) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE inventory_transactions(id INT AUTO_INCREMENT PRIMARY KEY,part_id INT NOT NULL,user_id INT NULL,qty INT NOT NULL,type ENUM('receive','issue','adjust','return'),reference VARCHAR(120),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(part_id) REFERENCES parts(id),FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE engineer_job_requests(id INT AUTO_INCREMENT PRIMARY KEY,complaint_id INT NOT NULL,engineer_id INT NOT NULL,status ENUM('pending','approved','rejected') DEFAULT 'pending',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_req(complaint_id,engineer_id),FOREIGN KEY(complaint_id) REFERENCES complaints(id) ON DELETE CASCADE,FOREIGN KEY(engineer_id) REFERENCES users(id) ON DELETE CASCADE);
CREATE TABLE feedback(id INT AUTO_INCREMENT PRIMARY KEY,complaint_id INT UNIQUE NOT NULL,customer_id INT NOT NULL,rating TINYINT,comments TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(complaint_id) REFERENCES complaints(id) ON DELETE CASCADE,FOREIGN KEY(customer_id) REFERENCES customers(id));
CREATE TABLE notifications(id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NOT NULL,title VARCHAR(180),body TEXT,is_read TINYINT DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE);
CREATE TABLE employees(id INT AUTO_INCREMENT PRIMARY KEY,user_id INT UNIQUE NOT NULL,employee_no VARCHAR(80) UNIQUE,designation VARCHAR(120),department VARCHAR(120),joining_date DATE NOT NULL,leaving_date DATE NULL,exit_reason VARCHAR(255),highest_level ENUM('L1','L2','L3','L4') DEFAULT 'L1',exit_status ENUM('active','exit_in_progress','left') DEFAULT 'active',manager_approved TINYINT DEFAULT 0,hr_approved TINYINT DEFAULT 0,FOREIGN KEY(user_id) REFERENCES users(id));
CREATE TABLE certificates(id INT AUTO_INCREMENT PRIMARY KEY,employee_id INT NOT NULL,certificate_no VARCHAR(100) UNIQUE,certificate_type VARCHAR(80),issued_at DATETIME DEFAULT CURRENT_TIMESTAMP,pdf_path VARCHAR(255),verification_hash CHAR(64),FOREIGN KEY(employee_id) REFERENCES employees(id));
CREATE TABLE audit_logs(id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id INT NULL,action VARCHAR(120),entity VARCHAR(80),entity_id INT NULL,metadata JSON,ip_address VARCHAR(64),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL);
INSERT INTO sla_rules(name,priority,response_minutes,resolution_minutes) VALUES ('Low','low',1440,4320),('Medium','medium',240,1440),('High','high',60,480),('Critical','critical',30,240);
INSERT INTO users(name,email,password_hash,role) VALUES ('System Admin','admin@example.com','$2y$12$7MH9EmKLD2YErLkwynlG1uvi.C58ln5d6WVeF9elr6C8yrETDTpnG','admin');
INSERT INTO users(name,email,password_hash,role) VALUES ("Service Manager","manager@example.com","$2y$12$pCfnukyQqKMOtMKqnCIwl.XBdm/3mIlUMG/5.xmlux8gzFQDcn69i","manager");
INSERT INTO users(name,email,password_hash,role) VALUES ("Field Engineer","engineer@example.com","$2y$12$PqIhB4JV.mDogn/svXrlQudjfCW0ipCRQbKfcm0YN.s1frjZ4S8Rm","engineer");
INSERT INTO users(name,email,password_hash,role) VALUES ("Demo Customer","customer@example.com","$2y$12$Ir4TH6PIdQcGp/ZJtRTbU.OXFo7DWIdKh2Q9XUxMmCPfnx51xmo1m","customer");
INSERT INTO customers(user_id,organization,contact_name,email) SELECT id,'Demo Hospital','Demo Customer','customer@example.com' FROM users WHERE email='customer@example.com';
INSERT INTO employees(user_id,employee_no,designation,department,joining_date,highest_level) SELECT id,'FSE-001','Field Service Engineer','Service','2024-01-15','L3' FROM users WHERE email='engineer@example.com';
INSERT INTO manufacturers(name) VALUES ('Philips'),('GE HealthCare'),('Mindray'),('Dräger');
INSERT INTO parts(part_no,name,manufacturer,unit_cost,min_stock,stock_qty) VALUES ('FUS-001','Equipment Fuse','Generic',2.50,10,50),('BAT-001','Backup Battery','Generic',45,5,12),('ECG-001','ECG Cable','Philips',35,3,8);