-- GBU Director Works and Maintenance ERP | MySQL 8.0+ | utf8mb4
CREATE DATABASE IF NOT EXISTS gbu_maintenance CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE gbu_maintenance;
CREATE TABLE IF NOT EXISTS app_settings (
 name VARCHAR(100) PRIMARY KEY, value TEXT NOT NULL
) ENGINE=InnoDB;
INSERT IGNORE INTO app_settings(name,value) VALUES
 ('job_prefix','JC/GBU/DW'),('complaint_prefix','CMP/GBU'),
 ('institution_name','GAUTAM BUDDHA UNIVERSITY'),
 ('office_name','Office of Director Works / Maintenance'),
 ('institution_address','Greater Noida, Gautam Buddha Nagar, Uttar Pradesh');
CREATE TABLE IF NOT EXISTS users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(160) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE,
 phone VARCHAR(30) DEFAULT NULL, staff_code VARCHAR(80) DEFAULT NULL,
 designation VARCHAR(150) DEFAULT NULL, password_hash VARCHAR(255) NOT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1,last_login_at DATETIME NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS roles (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(90) NOT NULL UNIQUE,
 slug VARCHAR(100) NOT NULL UNIQUE, protected TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS permissions (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(100) NOT NULL UNIQUE,
 description VARCHAR(255) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS user_roles (
 user_id BIGINT UNSIGNED NOT NULL, role_id INT UNSIGNED NOT NULL,
 PRIMARY KEY(user_id,role_id),FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS role_permissions (
 role_id INT UNSIGNED NOT NULL, permission_id INT UNSIGNED NOT NULL,
 PRIMARY KEY(role_id,permission_id),FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE,
 FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS sequences (
 name VARCHAR(45) PRIMARY KEY, next_value BIGINT UNSIGNED NOT NULL DEFAULT 1
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS categories (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(110) NOT NULL UNIQUE,
 default_days INT NOT NULL DEFAULT 3, active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS locations (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(160) NOT NULL,
 type VARCHAR(70) DEFAULT NULL,parent_id INT UNSIGNED DEFAULT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1,
 FOREIGN KEY(parent_id) REFERENCES locations(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS contractors (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(170) NOT NULL UNIQUE,
 contact_name VARCHAR(160) DEFAULT NULL,phone VARCHAR(30) DEFAULT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS complaints (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 complaint_no VARCHAR(45) NOT NULL UNIQUE,
 external_tracking_id VARCHAR(170) DEFAULT NULL, -- nonunique: legacy dataset repeats this value
 complainant_name VARCHAR(180) NOT NULL,complainant_type VARCHAR(90) DEFAULT NULL,
 request_area VARCHAR(90) DEFAULT NULL, employee_code VARCHAR(100) DEFAULT NULL,
 mobile VARCHAR(35) DEFAULT NULL,email VARCHAR(190) DEFAULT NULL,
 alternate_email VARCHAR(190) DEFAULT NULL,
 category_id INT UNSIGNED DEFAULT NULL, category_raw VARCHAR(150) DEFAULT NULL,
 description TEXT NOT NULL, photo_url TEXT DEFAULT NULL,
 location_id INT UNSIGNED DEFAULT NULL,location_text VARCHAR(500) NOT NULL,
 severity VARCHAR(80) DEFAULT NULL, priority ENUM('low','normal','high','critical') NOT NULL DEFAULT 'normal',
 reported_at DATETIME NOT NULL, source ENUM('manual','excel','portal','api') NOT NULL DEFAULT 'manual',
 source_status VARCHAR(90) DEFAULT NULL, legacy_engineer VARCHAR(180) DEFAULT NULL,
 legacy_assignee VARCHAR(180) DEFAULT NULL,legacy_assignee_mobile VARCHAR(35) DEFAULT NULL,
 legacy_assigned_date VARCHAR(60) DEFAULT NULL, legacy_resolution TEXT DEFAULT NULL,
 original_row_json JSON DEFAULT NULL, fingerprint CHAR(64) NOT NULL,
 created_by BIGINT UNSIGNED DEFAULT NULL,created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX ix_external_tracking(external_tracking_id),INDEX ix_fingerprint(fingerprint),
 INDEX ix_reported(reported_at), INDEX ix_cat(category_id),INDEX ix_location(location_id),
 FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE SET NULL,
 FOREIGN KEY(location_id) REFERENCES locations(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS import_batches (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,filename VARCHAR(255) NOT NULL, sha256 CHAR(64) NOT NULL,
 uploaded_by BIGINT UNSIGNED DEFAULT NULL,total_rows INT NOT NULL DEFAULT 0,
 status ENUM('staged','partial','complete') NOT NULL DEFAULT 'staged',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS import_rows (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,batch_id BIGINT UNSIGNED NOT NULL,
 sheet_row INT NOT NULL,raw_json JSON NOT NULL,normalized_json JSON NOT NULL,
 fingerprint CHAR(64) NOT NULL DEFAULT '',external_tracking_id VARCHAR(170) DEFAULT NULL,
 result ENUM('ready','review','imported','skipped') NOT NULL DEFAULT 'ready',
 reason VARCHAR(500) DEFAULT NULL,complaint_id BIGINT UNSIGNED DEFAULT NULL,
 override_approved_by BIGINT UNSIGNED DEFAULT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_batch_row(batch_id,sheet_row),INDEX ix_batch_status(batch_id,result),
 FOREIGN KEY(batch_id) REFERENCES import_batches(id) ON DELETE CASCADE,
 FOREIGN KEY(complaint_id) REFERENCES complaints(id) ON DELETE SET NULL,
 FOREIGN KEY(override_approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS job_cards (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,job_no VARCHAR(60) NOT NULL UNIQUE,
 complaint_id BIGINT UNSIGNED NOT NULL UNIQUE,
 assigned_engineer_id BIGINT UNSIGNED DEFAULT NULL,assigned_technician_id BIGINT UNSIGNED DEFAULT NULL,
 contractor_id INT UNSIGNED DEFAULT NULL,
 status ENUM('created','assigned','in_progress','on_hold','completed','verified','closed','reopened','cancelled') NOT NULL DEFAULT 'created',
 target_days INT UNSIGNED NOT NULL DEFAULT 3,due_at DATETIME DEFAULT NULL,
 completion_at DATETIME DEFAULT NULL,verification_at DATETIME DEFAULT NULL,
 work_report TEXT DEFAULT NULL,verification_remark TEXT DEFAULT NULL,
 satisfactory TINYINT(1) DEFAULT NULL,
 created_by BIGINT UNSIGNED DEFAULT NULL,created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX ix_job_engineer(assigned_engineer_id,status,due_at),INDEX ix_job_technician(assigned_technician_id,status,due_at),
 INDEX ix_job_status(status,due_at),FOREIGN KEY(complaint_id) REFERENCES complaints(id) ON DELETE RESTRICT,
 FOREIGN KEY(assigned_engineer_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(assigned_technician_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(contractor_id) REFERENCES contractors(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS job_assignments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,job_id BIGINT UNSIGNED NOT NULL,
 engineer_id BIGINT UNSIGNED DEFAULT NULL,technician_id BIGINT UNSIGNED DEFAULT NULL,
 contractor_id INT UNSIGNED DEFAULT NULL,assigned_by BIGINT UNSIGNED DEFAULT NULL,
 note VARCHAR(1000) DEFAULT NULL,assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(job_id) REFERENCES job_cards(id) ON DELETE CASCADE,
 FOREIGN KEY(engineer_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(technician_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(contractor_id) REFERENCES contractors(id) ON DELETE SET NULL,
 FOREIGN KEY(assigned_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS job_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,job_id BIGINT UNSIGNED NOT NULL,
 old_status VARCHAR(40) DEFAULT NULL,new_status VARCHAR(40) DEFAULT NULL,
 note TEXT DEFAULT NULL,actor_id BIGINT UNSIGNED DEFAULT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(job_id) REFERENCES job_cards(id) ON DELETE CASCADE,
 FOREIGN KEY(actor_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS job_measurements (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,job_id BIGINT UNSIGNED NOT NULL,
 description VARCHAR(500) NOT NULL, count_no DECIMAL(12,3) DEFAULT NULL,
 length DECIMAL(12,3) DEFAULT NULL,breadth DECIMAL(12,3) DEFAULT NULL,
 height DECIMAL(12,3) DEFAULT NULL,quantity DECIMAL(14,3) NOT NULL DEFAULT 0,
 unit VARCHAR(30) DEFAULT NULL,unit_rate DECIMAL(14,2) NOT NULL DEFAULT 0,
 amount DECIMAL(16,2) NOT NULL DEFAULT 0,reference VARCHAR(200) DEFAULT NULL,
 created_by BIGINT UNSIGNED DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(job_id) REFERENCES job_cards(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,actor_id BIGINT UNSIGNED DEFAULT NULL,
 entity_type VARCHAR(60) NOT NULL,entity_id BIGINT UNSIGNED NOT NULL,
 action VARCHAR(80) NOT NULL,details_json JSON DEFAULT NULL,ip_address VARCHAR(45) DEFAULT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,INDEX ix_audit_entity(entity_type,entity_id),
 FOREIGN KEY(actor_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS login_attempts (
 attempt_key VARCHAR(72) PRIMARY KEY,failures INT UNSIGNED NOT NULL DEFAULT 0,
 last_attempt_at DATETIME NOT NULL
) ENGINE=InnoDB;
INSERT IGNORE INTO sequences(name,next_value) VALUES ('complaint',1),('job_card',1);
INSERT IGNORE INTO categories(name,default_days) VALUES
 ('Civil',5),('Electrical',1),('Plumbing',2),('Carpentry',4),('Water',1),
 ('Cleanliness',1),('Horticulture',4),('Sewage',1),('Paint',7),('Pest Control',3),
 ('AC',3),('Housekeeping',1),('Other',3);
INSERT IGNORE INTO roles(name,slug,protected) VALUES
 ('Super Administrator','super_admin',1),('Director Works','director',1),
 ('Executive Engineer','engineer',1),('Technician','technician',1),
 ('Data Entry Operator','data_entry',1),('Read-only Officer','viewer',1);
INSERT IGNORE INTO permissions(code,description) VALUES
 ('complaints.view','View complaint register'),('complaints.create','Register complaint'),
 ('complaints.edit','Edit complaint details'),('complaints.import','Upload and validate bulk complaints'),
 ('jobs.view','View assigned job cards'),('jobs.view_all','View all jobs across the office'),
 ('jobs.generate','Create job cards from complaints'),('jobs.assign','Assign/reassign engineers, technicians and contractors'),
 ('jobs.update','Enter progress and work reports'),('jobs.verify','Verify or close completed works'),
 ('jobs.print','Print single/batch job cards'),('jobs.measure','Record work measurements and rates'),
 ('reports.view','View maintenance performance reports'),('reports.export','Download reports as CSV'),
 ('masters.manage','Manage complaint types/locations/contractors'),
 ('users.manage','Manage staff logins and role assignments'),('roles.manage','Manage permissions'),
 ('audit.view','View system audit trail');
-- Role-permission defaults. Super administrator gains all permissions.
INSERT IGNORE INTO role_permissions(role_id,permission_id)
 SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.slug='super_admin';
INSERT IGNORE INTO role_permissions(role_id,permission_id)
 SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.slug='director'
 AND p.code IN('complaints.view','complaints.create','complaints.edit','complaints.import','jobs.view','jobs.view_all','jobs.generate','jobs.assign','jobs.update','jobs.verify','jobs.print','jobs.measure','reports.view','reports.export','masters.manage','audit.view');
INSERT IGNORE INTO role_permissions(role_id,permission_id)
 SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.slug='engineer'
 AND p.code IN('complaints.view','jobs.view','jobs.update','jobs.print','jobs.measure','reports.view');
INSERT IGNORE INTO role_permissions(role_id,permission_id)
 SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.slug='technician'
 AND p.code IN('jobs.view','jobs.update');
INSERT IGNORE INTO role_permissions(role_id,permission_id)
 SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.slug='data_entry'
 AND p.code IN('complaints.view','complaints.create','complaints.edit','complaints.import','jobs.view','jobs.view_all','jobs.generate','jobs.print','reports.view');
INSERT IGNORE INTO role_permissions(role_id,permission_id)
 SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.slug='viewer'
 AND p.code IN('complaints.view','jobs.view','jobs.view_all','reports.view');