-- EGS (Electronic Guided Spiritual) - MySQL Database Schema
-- Kompatibel 100% untuk diimpor ke database cPanel phpMyAdmin, Laragon, maupun VPS.


-- Tabel Users untuk Autentikasi
CREATE TABLE IF NOT EXISTS `users` (
  `id` VARCHAR(36) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` VARCHAR(20) NOT NULL DEFAULT 'peserta', -- 'peserta' atau 'peneliti'
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_users_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel Profil & ID Responden Pasien
CREATE TABLE IF NOT EXISTS `profiles` (
  `id` VARCHAR(36) NOT NULL,
  `respondent_id` VARCHAR(50) NOT NULL DEFAULT '', -- ID Responden
  `name` VARCHAR(255) NOT NULL DEFAULT '',         -- Nama Pasien
  `medical` VARCHAR(255) NOT NULL DEFAULT '',      -- Nomor Rekam Medis
  `birthday` VARCHAR(50) DEFAULT NULL,             -- Tanggal Lahir
  `gender` VARCHAR(50) NOT NULL DEFAULT '',        -- Jenis Kelamin
  `diagnosis` VARCHAR(255) NOT NULL DEFAULT 'Penyakit Ginjal Kronik', -- Diagnosis
  `duration` VARCHAR(255) NOT NULL DEFAULT '',     -- Lama Hemodialisis (contoh: 2 tahun)
  `role` VARCHAR(20) NOT NULL DEFAULT 'peserta',
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_profiles_respondent` (`respondent_id`),
  KEY `idx_profiles_medical` (`medical`),
  CONSTRAINT `fk_profiles_user` FOREIGN KEY (`id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel Sesi / Token Autentikasi
CREATE TABLE IF NOT EXISTS `auth_tokens` (
  `token` VARCHAR(255) NOT NULL,
  `user_id` VARCHAR(36) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`token`),
  KEY `idx_tokens_user_id` (`user_id`),
  CONSTRAINT `fk_tokens_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel Sesi Spiritual (Riwayat Sesi Pasien)
CREATE TABLE IF NOT EXISTS `spiritual_sessions` (
  `id` VARCHAR(36) NOT NULL,
  `user_id` VARCHAR(36) NOT NULL,
  `respondent_id` VARCHAR(50) NOT NULL DEFAULT '',
  `session_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `duration` INT NOT NULL DEFAULT 0, -- Dalam detik
  `mood` VARCHAR(100) NOT NULL DEFAULT '', -- Evaluasi Terminasi: 'Lebih tenang', 'Tetap sama', dll
  `reflection` TEXT, -- Refleksi dan catatan spiritual
  `stages_completed` INT NOT NULL DEFAULT 7,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sessions_user` (`user_id`),
  KEY `idx_sessions_resp` (`respondent_id`),
  CONSTRAINT `fk_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel Monitoring TTV (Tensi, Nadi, RR, SpO2)
CREATE TABLE IF NOT EXISTS `vital_signs` (
  `id` VARCHAR(36) NOT NULL,
  `user_id` VARCHAR(36) NOT NULL,
  `respondent_id` VARCHAR(50) NOT NULL DEFAULT '',
  `session_id` VARCHAR(36) DEFAULT NULL, -- Opsional terhubung ke sesi spiritual
  `phase` VARCHAR(20) NOT NULL DEFAULT 'rutin', -- 'pre', 'post', 'rutin'
  `systolic` INT NOT NULL DEFAULT 0,  -- mmHg (contoh: 120)
  `diastolic` INT NOT NULL DEFAULT 0, -- mmHg (contoh: 80)
  `heart_rate` INT NOT NULL DEFAULT 0, -- Nadi (kali/menit, contoh: 82)
  `respiratory_rate` INT NOT NULL DEFAULT 0, -- RR (kali/menit, contoh: 18)
  `spo2` INT NOT NULL DEFAULT 0, -- Saturasi Oksigen (%, contoh: 98)
  `notes` VARCHAR(255) DEFAULT '',
  `recorded_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_vitals_user` (`user_id`),
  KEY `idx_vitals_resp` (`respondent_id`),
  KEY `idx_vitals_session` (`session_id`),
  CONSTRAINT `fk_vitals_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel Video Edukasi Spiritual
CREATE TABLE IF NOT EXISTS `education_videos` (
  `id` VARCHAR(36) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `video_url` VARCHAR(500) NOT NULL,
  `category` VARCHAR(100) NOT NULL DEFAULT 'Spiritual Hemodialisis',
  `duration_text` VARCHAR(50) NOT NULL DEFAULT '10 Menit',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel SOP 7 Tahap Intervensi EGS (Dapat Diedit oleh Admin)
CREATE TABLE IF NOT EXISTS `egs_stages` (
  `id` INT NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `subtitle` VARCHAR(255) NOT NULL,
  `seconds` INT NOT NULL DEFAULT 60,
  `video_url` VARCHAR(500) NOT NULL DEFAULT '',
  `instruction` TEXT NOT NULL,
  `dzikir` TEXT NOT NULL,
  `meaning` TEXT NOT NULL,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;