-- ============================================================ -- Karibu Connect Support Desk -- Database Setup Script — Version 1.0 -- Karibu Connect ICT Department -- -- INSTRUCTIONS: -- 1. Open phpMyAdmin at http://localhost/phpmyadmin -- 2. Click the "Import" tab -- 3. Choose this file and click "Go" -- 4. The database and all tables will be created automatically -- ============================================================ -- Create the database if it doesn't already exist -- utf8mb4 supports full Unicode including special characters CREATE DATABASE IF NOT EXISTS karibu_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE karibu_db; -- ── TICKETS TABLE ───────────────────────────────────────────── -- Core table — stores every support ticket raised in the system. -- Tickets are created from the KWS form, UHC form, or the NOC desk. -- Each ticket tracks its full lifecycle from Open to Resolved. CREATE TABLE IF NOT EXISTS tickets ( id VARCHAR(20) PRIMARY KEY, -- Unique ticket ID e.g. KWS-0001 timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, -- When the ticket was submitted name VARCHAR(100), -- Reporter full name email VARCHAR(150), -- Reporter email address phone VARCHAR(30), -- Reporter phone number site VARCHAR(200), -- Affected site/location category VARCHAR(100), -- Issue category e.g. No Connectivity summary VARCHAR(255), -- One-line issue description description TEXT, -- Full detailed description users_affected VARCHAR(50), -- Number of users affected issue_start VARCHAR(100), -- When the issue started priority ENUM('High','Medium','Low') DEFAULT 'Medium', status ENUM('Open','In Progress','Pending Client','Resolved') DEFAULT 'Open', tier TINYINT DEFAULT 1, -- Support tier: 1=NOC, 2=Network, 3=On-Site assignee VARCHAR(100) DEFAULT 'Unassigned', -- Assigned NOC agent channel VARCHAR(50) DEFAULT 'Form', -- How the ticket was submitted client VARCHAR(100) DEFAULT 'KWS', -- Client: KWS, UHC, Commercial, Kenya Railways notes TEXT, -- Internal NOC notes created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); -- ── TICKET TIMELINE TABLE ───────────────────────────────────── -- Stores the complete activity history for each ticket. -- Every status change, escalation, note, and resolution -- is recorded here as a separate entry. CREATE TABLE IF NOT EXISTS ticket_timeline ( id INT AUTO_INCREMENT PRIMARY KEY, ticket_id VARCHAR(20) NOT NULL, -- References tickets.id timestamp VARCHAR(30), -- Time of the activity e.g. "14:32" author VARCHAR(100), -- Who performed the action cls VARCHAR(20) DEFAULT '', -- CSS class for timeline styling note TEXT, -- Description of what happened created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- If a ticket is deleted, all its timeline entries are deleted too FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE ); -- ── USERS TABLE ─────────────────────────────────────────────── -- Stores all NOC desk user accounts. -- Three access levels control what each user can see and do: -- superadmin — full access including user and system management -- nocagent — can create and manage tickets -- viewer — read-only access to tickets CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, -- Login username (must be unique) password VARCHAR(255) NOT NULL, -- Account password full_name VARCHAR(100), -- Display name shown in the UI role ENUM('superadmin','nocagent','engineer','viewer') DEFAULT 'nocagent', email VARCHAR(150), -- User email for notifications created_by VARCHAR(50) DEFAULT 'system', -- Which admin created this account active TINYINT(1) DEFAULT 1, -- 1=active, 0=deactivated created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); -- ── AUDIT LOG TABLE ─────────────────────────────────────────── -- Permanent record of every significant action in the system. -- Cannot be deleted by regular users — provides full accountability. -- Entries include: logins, ticket changes, user management, -- site additions/removals, and category changes. CREATE TABLE IF NOT EXISTS audit_log ( id INT AUTO_INCREMENT PRIMARY KEY, timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, username VARCHAR(50), -- Who performed the action role VARCHAR(20), -- Their role at the time action_type VARCHAR(50), -- Action code e.g. TICKET_CREATE details TEXT, -- Human-readable description ip_address VARCHAR(45), -- Client IP for security tracking created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); -- ── KWS SITES TABLE ─────────────────────────────────────────── -- Stores all KWS and UHC site locations managed by Karibu Connect. -- Used by the Site Management view in the NOC desk. -- Super Admins can add, edit or remove sites without code changes. CREATE TABLE IF NOT EXISTS kws_sites ( id INT AUTO_INCREMENT PRIMARY KEY, region VARCHAR(100), -- Conservation Area or county grouping park VARCHAR(200), -- National Park or facility name station VARCHAR(200), -- Specific gate, station or facility active TINYINT(1) DEFAULT 1, -- 1=active, 0=removed added_by VARCHAR(50) DEFAULT 'system', created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); -- ── DEFAULT SUPER ADMIN ACCOUNT ────────────────────────────── -- Creates the first admin account used for initial system setup. -- Change this password immediately after first login. INSERT IGNORE INTO users (username, password, full_name, role, email, created_by) VALUES ('admin', 'admin123', 'Super Admin', 'superadmin', 'admin@karibuconnect.com', 'system'); -- ── DATABASE INDEXES ───────────────────────────────────────── -- Indexes speed up the most common queries in the system. -- Without indexes, every filter or search would scan the full table. CREATE INDEX IF NOT EXISTS idx_tickets_status ON tickets(status); CREATE INDEX IF NOT EXISTS idx_tickets_priority ON tickets(priority); CREATE INDEX IF NOT EXISTS idx_tickets_client ON tickets(client); CREATE INDEX IF NOT EXISTS idx_timeline_ticket ON ticket_timeline(ticket_id); CREATE INDEX IF NOT EXISTS idx_audit_user ON audit_log(username); CREATE INDEX IF NOT EXISTS idx_audit_action ON audit_log(action_type); -- ── CATEGORIES TABLE ──────────────────────────────────────── -- Stores issue categories shared across the NOC desk and field officer forms. -- Managed via the Categories view in the NOC desk by Super Admins. -- Changes here instantly reflect in the KWS and UHC submission forms. CREATE TABLE IF NOT EXISTS categories ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, -- Category label shown in dropdowns tier TINYINT DEFAULT 1, -- Auto-assigned support tier (1/2/3) active TINYINT(1) DEFAULT 1, -- 1=visible in forms, 0=hidden sort_order INT DEFAULT 0, -- Display order in dropdown created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); -- Pre-load default categories INSERT IGNORE INTO categories (name, tier, active, sort_order) VALUES ('No Connectivity — Complete outage', 1, 1, 0), ('Slow Speeds — Below expected performance', 2, 1, 1), ('Intermittent Drops — Connection keeps cutting', 1, 1, 2), ('Dish Offline — Hardware / physical issue', 3, 1, 3), ('Router / DHCP Issue — Devices not connecting', 1, 1, 4), ('New Installation Request', 1, 1, 5), ('Other', 1, 1, 6); -- ── UPDATE EXISTING DATABASE (if upgrading) ───────────────── -- Run this if you already have the database set up and just -- need to add the Network Engineer role to existing users table ALTER TABLE users MODIFY COLUMN role ENUM('superadmin','nocagent','engineer','viewer') DEFAULT 'nocagent'; -- Setup complete SELECT 'Karibu Connect database setup complete!' AS message;