-- ============================================
-- Database: komitmen_dokter
-- SQL DDL Script for Doctor Commitment System
-- ============================================

-- Create database
CREATE DATABASE IF NOT EXISTS komitmen_dokter
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE komitmen_dokter;

-- ============================================
-- Table: dokter
-- ============================================
CREATE TABLE IF NOT EXISTS `dokter` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama_dokter` VARCHAR(255) NOT NULL,
  `poli_dokter` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: jadwal_dokter
-- ============================================
CREATE TABLE IF NOT EXISTS `jadwal_dokter` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_dokter` BIGINT UNSIGNED NOT NULL,
  `tanggal` DATE NOT NULL,
  `hari` VARCHAR(50) NOT NULL,
  `jam_mulai` TIME NOT NULL,
  `jam_berakhir` TIME NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `jadwal_dokter_id_dokter_index` (`id_dokter`),
  CONSTRAINT `jadwal_dokter_id_dokter_foreign` 
    FOREIGN KEY (`id_dokter`) 
    REFERENCES `dokter` (`id`) 
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- Insert sample doctors
INSERT INTO `dokter` (`nama_dokter`, `poli_dokter`, `created_at`, `updated_at`) VALUES
('Dr. Ahmad Hidayat, Sp.PD', 'Poli Penyakit Dalam', NOW(), NOW()),
('Dr. Siti Nurhaliza, Sp.A', 'Poli Anak', NOW(), NOW()),
('Dr. Budi Santoso, Sp.OG', 'Poli Kandungan', NOW(), NOW());

-- Insert sample schedules
INSERT INTO `jadwal_dokter` (`id_dokter`, `tanggal`, `hari`, `jam_mulai`, `jam_berakhir`, `created_at`, `updated_at`) VALUES
(1, '2026-01-05', 'Senin', '08:00:00', '12:00:00', NOW(), NOW()),
(1, '2026-01-07', 'Rabu', '13:00:00', '17:00:00', NOW(), NOW()),
(1, '2026-01-09', 'Jumat', '08:00:00', '12:00:00', NOW(), NOW()),
(2, '2026-01-06', 'Selasa', '09:00:00', '14:00:00', NOW(), NOW()),
(2, '2026-01-08', 'Kamis', '09:00:00', '14:00:00', NOW(), NOW()),
(3, '2026-01-05', 'Senin', '14:00:00', '18:00:00', NOW(), NOW()),
(3, '2026-01-06', 'Selasa', '14:00:00', '18:00:00', NOW(), NOW()),
(3, '2026-01-08', 'Kamis', '14:00:00', '18:00:00', NOW(), NOW());

-- ============================================
-- Useful Queries
-- ============================================

-- Query to view all active commitments
-- SELECT 
--     d.nama_dokter,
--     d.poli_dokter,
--     r.komitmen,
--     r.berlaku_mulai,
--     r.tanggal_submit,
--     r.status
-- FROM response_komitmen_dokter r
-- JOIN dokter d ON r.id_dokter = d.id
-- WHERE r.status = 'AKTIF'
-- ORDER BY r.tanggal_submit DESC;

-- Query to view commitment history for a specific doctor
-- SELECT 
--     komitmen,
--     berlaku_mulai,
--     tanggal_submit,
--     status,
--     ip_address
-- FROM response_komitmen_dokter
-- WHERE id_dokter = 1
-- ORDER BY tanggal_submit DESC;

-- Query to check valid tokens
-- SELECT 
--     t.token,
--     d.nama_dokter,
--     t.expires_at,
--     t.used_at
-- FROM komitmen_tokens t
-- JOIN dokter d ON t.id_dokter = d.id
-- WHERE t.used_at IS NULL 
--   AND t.expires_at > NOW()
-- ORDER BY t.created_at DESC;
