-- ============================================================
-- Triccess Hospitality — Database Schema
-- Import this file into a MySQL/MariaDB database before first use:
--   mysql -u root -p triccess_hospitality < config/schema.sql
-- (create the database first: CREATE DATABASE triccess_hospitality;)
-- ============================================================

SET NAMES utf8mb4;

-- ---------- USERS (Academy students + CMS/Academy admins) ----------
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('student','admin') NOT NULL DEFAULT 'student',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- E-BOOKS ----------
CREATE TABLE IF NOT EXISTS ebooks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  description TEXT,
  category VARCHAR(60) DEFAULT 'Guide',
  file_path VARCHAR(255) NOT NULL,
  cover_path VARCHAR(255) DEFAULT NULL,
  downloads INT DEFAULT 0,
  created_by INT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- CUSTOMER STORIES ----------
CREATE TABLE IF NOT EXISTS customer_stories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_name VARCHAR(150) NOT NULL,
  title VARCHAR(200) NOT NULL,
  excerpt VARCHAR(400),
  content TEXT NOT NULL,
  cover_path VARCHAR(255) DEFAULT NULL,
  category VARCHAR(60) DEFAULT 'Case Study',
  created_by INT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- BLOG POSTS ----------
CREATE TABLE IF NOT EXISTS blog_posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  slug VARCHAR(220) NOT NULL UNIQUE,
  excerpt VARCHAR(400),
  content TEXT NOT NULL,
  cover_path VARCHAR(255) DEFAULT NULL,
  category VARCHAR(60) DEFAULT 'Insight',
  author_id INT DEFAULT NULL,
  status ENUM('draft','published') DEFAULT 'published',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- PODCASTS ----------
CREATE TABLE IF NOT EXISTS podcasts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  description TEXT,
  audio_path VARCHAR(255) NOT NULL,
  cover_path VARCHAR(255) DEFAULT NULL,
  episode_number INT DEFAULT NULL,
  duration_minutes INT DEFAULT NULL,
  created_by INT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- ACADEMY COURSES ----------
CREATE TABLE IF NOT EXISTS courses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  description TEXT,
  category VARCHAR(80) DEFAULT 'Certificate',
  level VARCHAR(40) DEFAULT 'Beginner',
  duration VARCHAR(60),
  thumbnail VARCHAR(255) DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- ENROLLMENTS ----------
CREATE TABLE IF NOT EXISTS enrollments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  course_id INT NOT NULL,
  status ENUM('active','completed') DEFAULT 'active',
  progress_percent TINYINT UNSIGNED DEFAULT 0,
  enrolled_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_enroll (user_id, course_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- SEED DATA
-- ============================================================

-- Default admin account.
-- Email:    admin@triccesshospitality.co.ke
-- Password: Admin@2026!
-- CHANGE THIS PASSWORD IMMEDIATELY after your first login at /admin/login.php
INSERT INTO users (full_name, email, password_hash, role) VALUES
('Triccess Admin', 'admin@triccesshospitality.co.ke', '$2b$10$iGUeZNkmZaA/uHIeS8onRexa4r/i7PsuBovb5GYFdGuUt7QA1MJ2a', 'admin');

-- Sample e-books (file_path points at /uploads/ebooks/ — replace placeholder
-- PDFs with your real files via the admin panel; these rows exist so the
-- public page isn't empty on first load)
INSERT INTO ebooks (title, description, category, file_path, downloads) VALUES
('The State of African Hospitality 2026', 'A data-driven look at where guest expectations, technology, and the market are heading across the continent.', 'Report', 'placeholder.pdf', 412),
('Beyond the POS: A Guide to Owning Your Guest Data', 'A practical framework for moving from third-party platforms to an owned guest relationship.', 'Playbook', 'placeholder.pdf', 298),
('The Independent Hotelier''s Guide to Direct Bookings', 'Reduce OTA dependency and build a direct booking engine guests actually prefer to use.', 'Guide', 'placeholder.pdf', 176),
('Building a Loyalty Program That Actually Works', 'Why generic points systems fail in African markets — and what to build instead.', 'Playbook', 'placeholder.pdf', 233),
('The Franchise Readiness Checklist', 'Twenty questions every hospitality founder should answer before franchising a concept.', 'Checklist', 'placeholder.pdf', 154),
('AI in African Hospitality: A Practical Guide', 'Where AI genuinely helps hospitality operators today — and where the hype outruns the reality.', 'Guide', 'placeholder.pdf', 341);

-- Sample customer stories
INSERT INTO customer_stories (client_name, title, excerpt, content, category) VALUES
('Independent Restaurant Group', 'Replacing four apps with one owned guest relationship', 'How a 6-location restaurant group consolidated delivery, loyalty, and booking into a single branded app.', 'Full case study content goes here — the challenge, the approach, the platforms deployed, and the measurable outcome.', 'Digital Experience'),
('Boutique Hotel Chain', 'Taking a 40-room property from struggling to fully booked', 'An 18-month operational turnaround built on consulting, training, and revenue management.', 'Full case study content goes here — the challenge, the approach, and the measurable outcome.', 'Consulting'),
('Regional QSR Brand', 'Scaling from one city to three without losing consistency', 'Franchise development and the Franchise Portal gave head office real-time oversight across every new unit.', 'Full case study content goes here.', 'Franchise Development');

-- Sample blog posts
INSERT INTO blog_posts (title, slug, excerpt, content, category, status) VALUES
('Why African Hospitality Is Ready to Lead, Not Follow', 'why-african-hospitality-is-ready-to-lead', 'The infrastructure gap has closed faster than most operators realize.', 'Full article content goes here.', 'Industry', 'published'),
('The Real Cost of Renting Your Guest Relationship', 'the-real-cost-of-renting-your-guest-relationship', 'OTA and delivery commissions are the visible cost. The invisible cost is bigger.', 'Full article content goes here.', 'Strategy', 'published'),
('Five Signs Your Restaurant Is Ready to Franchise', 'five-signs-your-restaurant-is-ready-to-franchise', 'Great concepts don''t always mean franchise-ready operations. Here''s how to tell the difference.', 'Full article content goes here.', 'Franchise', 'published');

-- Sample podcast episodes (audio_path points at /uploads/podcasts/)
INSERT INTO podcasts (title, description, audio_path, episode_number, duration_minutes) VALUES
('Building Hospitality Brands That Own Their Data', 'A conversation on why ownership of the guest relationship is the next competitive battleground.', 'placeholder.mp3', 1, 34),
('From Single Restaurant to Regional Franchise', 'What it actually takes to franchise a hospitality concept across borders.', 'placeholder.mp3', 2, 41),
('The Future of AI in African Hospitality', 'Separating genuine AI use cases from the hype, with real examples from the field.', 'placeholder.mp3', 3, 29);

-- Academy courses
INSERT INTO courses (title, description, category, level, duration) VALUES
('Front-of-House Service Excellence', 'Foundational service standards for hosts, servers, and guest-facing staff.', 'Certificate', 'Beginner', '4 weeks'),
('Culinary & Kitchen Management', 'Kitchen operations, food cost control, and back-of-house leadership.', 'Certificate', 'Intermediate', '6 weeks'),
('General Manager Pathway', 'A structured leadership track for future hotel and restaurant GMs.', 'Leadership', 'Advanced', '12 weeks'),
('Train-the-Trainer Program', 'Build internal capability to train and certify your own teams.', 'Certificate', 'Intermediate', '3 weeks'),
('Digital Guest Experience Fundamentals', 'How to run loyalty, CRM, and digital platforms as a hospitality manager, not just a technologist.', 'Workshop', 'Beginner', '2 weeks');