-- =========================================================
-- S.I TECH SYSTEM (SITS) - DATABASE SCHEMA
-- SILUNGWE INTELLIGENCE
-- =========================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ---------- ROLES & PERMISSIONS ----------
CREATE TABLE roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,       -- super_admin, administrator, developer, designer, support, client
  description VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE permissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  role_id INT NOT NULL,
  permission_key VARCHAR(100) NOT NULL,   -- e.g. projects.manage, invoices.create
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- USERS ----------
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  role_id INT NOT NULL DEFAULT 6,
  full_name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  phone VARCHAR(30) DEFAULT NULL,
  password_hash VARCHAR(255) NOT NULL,
  avatar VARCHAR(255) DEFAULT NULL,
  email_verified_at TIMESTAMP NULL DEFAULT NULL,
  two_factor_secret VARCHAR(255) DEFAULT NULL,
  two_factor_enabled TINYINT(1) DEFAULT 0,
  remember_token VARCHAR(255) DEFAULT NULL,
  status ENUM('active','suspended','pending') DEFAULT 'pending',
  theme_preference ENUM('light','dark') DEFAULT 'light',
  last_login_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB;

CREATE TABLE password_resets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(150) NOT NULL,
  token VARCHAR(255) NOT NULL,
  expires_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE sessions (
  id VARCHAR(128) PRIMARY KEY,
  user_id INT DEFAULT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  user_agent VARCHAR(255) DEFAULT NULL,
  payload TEXT,
  last_activity INT NOT NULL
) ENGINE=InnoDB;

-- ---------- CLIENTS / CRM ----------
CREATE TABLE clients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT DEFAULT NULL,               -- linked login account, nullable for leads
  company_name VARCHAR(150) DEFAULT NULL,
  contact_name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL,
  phone VARCHAR(30) DEFAULT NULL,
  address VARCHAR(255) DEFAULT NULL,
  lead_status ENUM('new','contacted','qualified','proposal_sent','won','lost') DEFAULT 'new',
  source VARCHAR(100) DEFAULT NULL,
  assigned_to INT DEFAULT NULL,           -- user_id of account manager
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE contacts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  name VARCHAR(150) NOT NULL,
  role VARCHAR(100) DEFAULT NULL,
  email VARCHAR(150) DEFAULT NULL,
  phone VARCHAR(30) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  user_id INT DEFAULT NULL,               -- staff member who logged/sent it
  direction ENUM('incoming','outgoing') DEFAULT 'outgoing',
  channel ENUM('email','call','whatsapp','meeting','system') DEFAULT 'email',
  subject VARCHAR(255) DEFAULT NULL,
  body TEXT,
  follow_up_at DATE DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------- PROJECTS ----------
CREATE TABLE projects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  description TEXT,
  developer_id INT DEFAULT NULL,
  designer_id INT DEFAULT NULL,
  priority ENUM('low','medium','high','urgent') DEFAULT 'medium',
  status ENUM('planning','in_progress','review','completed','on_hold','cancelled') DEFAULT 'planning',
  progress TINYINT DEFAULT 0,             -- 0-100
  start_date DATE DEFAULT NULL,
  deadline DATE DEFAULT NULL,
  budget DECIMAL(12,2) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  FOREIGN KEY (developer_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (designer_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE project_tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  description TEXT,
  assigned_to INT DEFAULT NULL,
  status ENUM('todo','in_progress','done') DEFAULT 'todo',
  due_date DATE DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE project_files (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  uploaded_by INT DEFAULT NULL,
  file_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  file_type VARCHAR(50) DEFAULT NULL,
  file_size INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE project_comments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  user_id INT DEFAULT NULL,
  comment TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE project_updates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  user_id INT DEFAULT NULL,
  update_text TEXT NOT NULL,
  progress_snapshot TINYINT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------- QUOTATIONS ----------
CREATE TABLE quotations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  quote_number VARCHAR(50) NOT NULL UNIQUE,
  issue_date DATE NOT NULL,
  expiry_date DATE DEFAULT NULL,
  subtotal DECIMAL(12,2) DEFAULT 0,
  tax_percent DECIMAL(5,2) DEFAULT 0,
  discount_percent DECIMAL(5,2) DEFAULT 0,
  total DECIMAL(12,2) DEFAULT 0,
  status ENUM('draft','sent','approved','declined','expired') DEFAULT 'draft',
  notes TEXT,
  created_by INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE quotation_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  quotation_id INT NOT NULL,
  description VARCHAR(255) NOT NULL,
  quantity DECIMAL(10,2) DEFAULT 1,
  unit_price DECIMAL(12,2) NOT NULL,
  line_total DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- INVOICES ----------
CREATE TABLE invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  project_id INT DEFAULT NULL,
  quotation_id INT DEFAULT NULL,
  invoice_number VARCHAR(50) NOT NULL UNIQUE,
  issue_date DATE NOT NULL,
  due_date DATE DEFAULT NULL,
  subtotal DECIMAL(12,2) DEFAULT 0,
  tax_percent DECIMAL(5,2) DEFAULT 0,
  discount_percent DECIMAL(5,2) DEFAULT 0,
  total DECIMAL(12,2) DEFAULT 0,
  amount_paid DECIMAL(12,2) DEFAULT 0,
  balance DECIMAL(12,2) DEFAULT 0,
  status ENUM('draft','sent','partially_paid','paid','overdue','cancelled') DEFAULT 'draft',
  notes TEXT,
  created_by INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL,
  FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE invoice_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  description VARCHAR(255) NOT NULL,
  quantity DECIMAL(10,2) DEFAULT 1,
  unit_price DECIMAL(12,2) NOT NULL,
  line_total DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  method ENUM('bank_transfer','mobile_money','cash','card','other') DEFAULT 'bank_transfer',
  reference VARCHAR(100) DEFAULT NULL,
  paid_at DATE NOT NULL,
  recorded_by INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
  FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------- SUPPORT TICKETS ----------
CREATE TABLE support_tickets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  project_id INT DEFAULT NULL,
  subject VARCHAR(200) NOT NULL,
  category VARCHAR(100) DEFAULT 'general',
  priority ENUM('low','medium','high','urgent') DEFAULT 'medium',
  status ENUM('open','pending','in_progress','resolved','closed') DEFAULT 'open',
  assigned_to INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL,
  FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE ticket_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT NOT NULL,
  user_id INT DEFAULT NULL,
  is_staff_reply TINYINT(1) DEFAULT 0,
  message TEXT NOT NULL,
  attachment_path VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------- BLOG ----------
CREATE TABLE blog_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(120) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE blog_posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT DEFAULT NULL,
  author_id INT DEFAULT NULL,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  excerpt VARCHAR(500) DEFAULT NULL,
  content LONGTEXT,
  featured_image VARCHAR(255) DEFAULT NULL,
  tags VARCHAR(255) DEFAULT NULL,
  meta_title VARCHAR(255) DEFAULT NULL,
  meta_description VARCHAR(255) DEFAULT NULL,
  status ENUM('draft','published') DEFAULT 'draft',
  published_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES blog_categories(id) ON DELETE SET NULL,
  FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE blog_comments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  post_id INT NOT NULL,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL,
  comment TEXT NOT NULL,
  status ENUM('pending','approved','spam') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (post_id) REFERENCES blog_posts(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- SITE CONTENT ----------
CREATE TABLE services (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  slug VARCHAR(170) NOT NULL UNIQUE,
  icon VARCHAR(100) DEFAULT NULL,
  short_description VARCHAR(255) DEFAULT NULL,
  description TEXT,
  sort_order INT DEFAULT 0,
  is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE portfolio (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  slug VARCHAR(170) NOT NULL UNIQUE,
  image VARCHAR(255) DEFAULT NULL,
  description TEXT,
  technologies VARCHAR(255) DEFAULT NULL,
  website_url VARCHAR(255) DEFAULT NULL,
  sort_order INT DEFAULT 0,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE testimonials (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_name VARCHAR(150) NOT NULL,
  company VARCHAR(150) DEFAULT NULL,
  photo VARCHAR(255) DEFAULT NULL,
  message TEXT NOT NULL,
  rating TINYINT DEFAULT 5,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE contacts_submissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL,
  phone VARCHAR(30) DEFAULT NULL,
  subject VARCHAR(200) DEFAULT NULL,
  message TEXT NOT NULL,
  status ENUM('new','read','replied') DEFAULT 'new',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE company_information (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_name VARCHAR(150) DEFAULT 'SILUNGWE INTELLIGENCE',
  tagline VARCHAR(255) DEFAULT NULL,
  logo VARCHAR(255) DEFAULT NULL,
  email VARCHAR(150) DEFAULT NULL,
  phone VARCHAR(30) DEFAULT NULL,
  whatsapp VARCHAR(30) DEFAULT NULL,
  address VARCHAR(255) DEFAULT NULL,
  map_embed TEXT,
  facebook VARCHAR(255) DEFAULT NULL,
  twitter VARCHAR(255) DEFAULT NULL,
  linkedin VARCHAR(255) DEFAULT NULL,
  instagram VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB;

CREATE TABLE settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(100) NOT NULL UNIQUE,
  setting_value TEXT
) ENGINE=InnoDB;

-- ---------- FILES / MESSAGING / NOTIFICATIONS / LOGS ----------
CREATE TABLE files (
  id INT AUTO_INCREMENT PRIMARY KEY,
  owner_id INT DEFAULT NULL,
  folder VARCHAR(255) DEFAULT '/',
  file_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  file_type VARCHAR(50) DEFAULT NULL,
  file_size INT DEFAULT NULL,
  visibility ENUM('private','client','public') DEFAULT 'private',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  type VARCHAR(100) DEFAULT 'general',
  title VARCHAR(200) NOT NULL,
  body VARCHAR(500) DEFAULT NULL,
  link VARCHAR(255) DEFAULT NULL,
  is_read TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE activity_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT DEFAULT NULL,
  action VARCHAR(150) NOT NULL,
  subject_type VARCHAR(100) DEFAULT NULL,
  subject_id INT DEFAULT NULL,
  description VARCHAR(255) DEFAULT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;

-- ---------- SEED DATA ----------
INSERT INTO roles (name, description) VALUES
('super_admin','Full unrestricted access'),
('administrator','Manage clients, projects, billing, tickets'),
('developer','Assigned to projects, updates tasks/progress'),
('designer','Assigned to design tasks'),
('support','Handles support tickets'),
('client','Portal access to own projects/invoices/tickets');

INSERT INTO company_information (company_name, tagline, email, phone, whatsapp, address)
VALUES ('SILUNGWE INTELLIGENCE', 'We Build Powerful Digital Solutions For Businesses', 'info@silungweintelligence.com', '+260 000 000000', '+260000000000', 'Lusaka, Zambia');

INSERT INTO services (title, slug, icon, short_description, sort_order) VALUES
('Custom Websites','custom-websites','fa-solid fa-code','Bespoke, high-performance websites built around your brand.',1),
('E-Commerce','e-commerce','fa-solid fa-cart-shopping','Full online stores with secure payments and inventory sync.',2),
('POS Systems','pos-systems','fa-solid fa-cash-register','Point-of-sale platforms for retail, restaurants, and lounges.',3),
('School Systems','school-systems','fa-solid fa-graduation-cap','Student, staff, and fee management for institutions.',4),
('ERP Systems','erp-systems','fa-solid fa-diagram-project','End-to-end resource planning across departments.',5),
('Inventory Systems','inventory-systems','fa-solid fa-boxes-stacked','Real-time stock tracking and reporting.',6),
('Courier Systems','courier-systems','fa-solid fa-truck-fast','Parcel booking, tracking, and driver management.',7),
('Hospital Systems','hospital-systems','fa-solid fa-hospital','Patient records and clinical workflow management.',8),
('Property Management','property-management','fa-solid fa-building','Tenant, lease, and maintenance tracking.',9),
('Payment Integration','payment-integration','fa-solid fa-credit-card','Mobile money and card gateway integrations.',10),
('Business Automation','business-automation','fa-solid fa-gears','Workflow automation to save time and reduce errors.',11),
('UI/UX Design','ui-ux-design','fa-solid fa-pen-ruler','Interfaces that are intuitive, modern, and on-brand.',12),
('Hosting','hosting','fa-solid fa-server','Reliable hosting and deployment for your platform.',13),
('Maintenance','maintenance','fa-solid fa-screwdriver-wrench','Ongoing support, updates, and monitoring.',14);

INSERT INTO portfolio (title, slug, description, technologies, website_url, sort_order) VALUES
('Bubbles Tech','bubbles-tech','Software and firmware e-commerce platform with mobile money checkout and licensed downloads.','PHP, MySQL, Lipila API','https://bubblestechs.com',1),
('Burgmann Direct','burgmann-direct','Corporate business platform with a modern client-facing presence.','PHP, MySQL, JavaScript','https://burgmann-direct.com',2),
('ConsultEnv','consultenv','Consultancy services website with service showcase and inquiry handling.','PHP, MySQL, JavaScript','https://consultenv.com',3),
('LSC Courier','lsc-courier','Zambian parcel delivery platform with customer, driver, and admin portals plus live tracking.','PHP, MySQL, Chart.js, Lipila API','https://clientdemo.acbizglobal.com/lsc-fixed/public/index.php',4);