-- ============================================================
-- 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;
