-- ============================================
-- MySQL Database Setup untuk Chatbot LMS
-- Universitas Yatsi Madani
-- ============================================
-- 
-- Cara menggunakan:
-- 1. Buka XAMPP Control Panel
-- 2. Start Apache dan MySQL
-- 3. Buka phpMyAdmin (http://localhost/phpmyadmin)
-- 4. Buat database baru dengan nama: yatsi_chatbot
-- 5. Import file SQL ini ke database tersebut
-- 
-- Atau jalankan via command line:
-- mysql -u root -p yatsi_chatbot < mysql-setup.sql
-- ============================================

-- Buat database (jika belum ada)
CREATE DATABASE IF NOT EXISTS `yatsi_chatbot` 
  DEFAULT CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

USE `yatsi_chatbot`;

-- ============================================
-- Table: users
-- ============================================
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `nim` VARCHAR(20) DEFAULT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `fullName` VARCHAR(255) NOT NULL,
  `role` ENUM('student', 'lecturer', 'admin', 'staff') DEFAULT 'student',
  
  -- Academic Info (JSON untuk fleksibilitas)
  `academicInfo` JSON DEFAULT NULL,
  
  -- Lecturer Info (JSON)
  `lecturerInfo` JSON DEFAULT NULL,
  
  -- Profile Info (JSON)
  `profile` JSON DEFAULT NULL,
  
  -- LMS Integration (JSON)
  `lmsIntegration` JSON DEFAULT NULL,
  
  -- Chat Preferences (JSON)
  `chatPreferences` JSON DEFAULT NULL,
  
  -- Status
  `isActive` BOOLEAN DEFAULT TRUE,
  `isVerified` BOOLEAN DEFAULT FALSE,
  `lastLogin` DATETIME DEFAULT NULL,
  `loginCount` INT(11) DEFAULT 0,
  
  -- Timestamps
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `nim` (`nim`),
  KEY `idx_role` (`role`),
  KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: chat_sessions
-- ============================================
CREATE TABLE IF NOT EXISTS `chat_sessions` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `userId` INT(11) NOT NULL,
  `sessionId` VARCHAR(255) NOT NULL,
  
  -- Context (JSON)
  `context` JSON DEFAULT NULL,
  
  -- Status
  `status` ENUM('active', 'paused', 'completed', 'escalated', 'abandoned') DEFAULT 'active',
  
  -- LMS Data (JSON)
  `lmsData` JSON DEFAULT NULL,
  
  -- Analytics (JSON)
  `analytics` JSON DEFAULT NULL,
  
  -- Timestamps
  `startedAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `endedAt` DATETIME DEFAULT NULL,
  `lastActivityAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `sessionId` (`sessionId`),
  KEY `idx_userId` (`userId`),
  KEY `idx_status` (`status`),
  KEY `idx_startedAt` (`startedAt`),
  FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: messages
-- ============================================
CREATE TABLE IF NOT EXISTS `messages` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `sessionId` INT(11) NOT NULL,
  `role` ENUM('user', 'assistant', 'system') NOT NULL,
  `content` TEXT NOT NULL,
  `messageType` ENUM('text', 'image', 'file', 'quick_reply', 'card') DEFAULT 'text',
  
  -- Metadata (JSON)
  `metadata` JSON DEFAULT NULL,
  
  -- Attachments (JSON array)
  `attachments` JSON DEFAULT NULL,
  
  -- Timestamp
  `timestamp` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  KEY `idx_sessionId` (`sessionId`),
  KEY `idx_timestamp` (`timestamp`),
  FOREIGN KEY (`sessionId`) REFERENCES `chat_sessions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Insert Sample Data (Optional)
-- ============================================

-- Sample Admin User (password: admin123 - hashed dengan bcrypt)
INSERT INTO `users` (`email`, `password`, `fullName`, `role`, `isActive`, `isVerified`) 
VALUES 
('admin@yatsi.ac.id', '$2a$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewY5GyYqJqJqJqJq', 'Admin User', 'admin', TRUE, TRUE)
ON DUPLICATE KEY UPDATE `email`=`email`;

-- Sample Student User (password: student123 - hashed dengan bcrypt)
INSERT INTO `users` (`nim`, `email`, `password`, `fullName`, `role`, `isActive`, `isVerified`, `academicInfo`) 
VALUES 
('87654321', 'student@yatsi.ac.id', '$2a$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewY5GyYqJqJqJqJq', 'Student User', 'student', TRUE, TRUE, 
  JSON_OBJECT(
    'faculty', 'Fakultas Teknik',
    'studyProgram', 'Teknik Informatika',
    'semester', 3,
    'academicYear', '2023/2024',
    'class', 'TI-21',
    'studentStatus', 'active'
  ))
ON DUPLICATE KEY UPDATE `email`=`email`;

-- ============================================
-- Views (Optional - untuk reporting)
-- ============================================

-- View: Active Sessions
CREATE OR REPLACE VIEW `v_active_sessions` AS
SELECT 
  cs.id,
  cs.sessionId,
  u.email,
  u.fullName,
  cs.status,
  cs.startedAt,
  cs.lastActivityAt,
  (SELECT COUNT(*) FROM messages m WHERE m.sessionId = cs.id) as messageCount
FROM chat_sessions cs
JOIN users u ON cs.userId = u.id
WHERE cs.status = 'active';

-- View: User Statistics
CREATE OR REPLACE VIEW `v_user_stats` AS
SELECT 
  u.id,
  u.email,
  u.fullName,
  u.role,
  COUNT(DISTINCT cs.id) as totalSessions,
  COUNT(DISTINCT m.id) as totalMessages,
  MAX(cs.lastActivityAt) as lastActivity
FROM users u
LEFT JOIN chat_sessions cs ON u.id = cs.userId
LEFT JOIN messages m ON cs.id = m.sessionId
GROUP BY u.id, u.email, u.fullName, u.role;

-- ============================================
-- Selesai!
-- ============================================
-- Database sudah siap digunakan.
-- Pastikan environment variable di .env sudah dikonfigurasi:
-- DB_TYPE=mysql
-- DB_HOST=localhost
-- DB_PORT=3306
-- DB_USER=root
-- DB_PASSWORD=
-- DB_NAME=yatsi_chatbot
-- ============================================