-- =====================================================
-- SMARTKING-AYU PRODUCTION DATABASE SETUP
-- =====================================================
-- File ini berisi setup lengkap database untuk produksi
-- Termasuk: struktur tabel, data awal, dan konfigurasi
--
-- CARA PENGGUNAAN:
-- 1. Import file ini ke MySQL/MariaDB:
--    mysql -u root -p < schema.sql
-- 2. Atau jalankan di phpMyAdmin
-- 3. File ini akan membuat database lengkap dengan sample data
--
-- FITUR YANG DICAKUP:
-- - 8 tabel utama (users, profil_pengguna, riwayat_kehamilan, 
--   alarm_ttd, alarm_anc, compliance_ttd, compliance_anc, video)
-- - Sample data untuk testing
-- - Views untuk query yang kompleks
-- - Index untuk performa optimal
-- - Foreign key constraints untuk integritas data
--
-- CREDENTIALS DEFAULT:
-- Admin: admin@smartking.com / password123
-- Test: test@test.com / password123
-- =====================================================

-- 1. CREATE DATABASE
-- =====================================================
CREATE DATABASE IF NOT EXISTS smartking_ayu 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE smartking_ayu;

-- 2. CREATE TABLES
-- =====================================================

-- Tabel users untuk data pengguna
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    ttl DATE NOT NULL,
    nik VARCHAR(16) UNIQUE NOT NULL,
    alamat TEXT NOT NULL,
    kata_sandi VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_nik (nik)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel profil_pengguna untuk data kehamilan
CREATE TABLE IF NOT EXISTS profil_pengguna (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    nama VARCHAR(255) NOT NULL,
    ttl DATE NOT NULL,
    tempat_lahir VARCHAR(100) NOT NULL,
    hpht DATE NOT NULL,
    tp DATE NOT NULL,
    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 CASCADE,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Script untuk update database yang sudah ada (jalankan jika tabel sudah dibuat)
-- ALTER TABLE profil_pengguna DROP COLUMN IF EXISTS gravida;
-- ALTER TABLE profil_pengguna DROP COLUMN IF EXISTS para;
-- ALTER TABLE profil_pengguna DROP COLUMN IF EXISTS abortus;

-- Tabel riwayat_kehamilan
CREATE TABLE riwayat_kehamilan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    hpht DATE NOT NULL,
    tp DATE NOT NULL,
    tanggal_persalinan DATE,
    berat_badan_awal DECIMAL(5,2),
    berat_badan_akhir DECIMAL(5,2),
    status VARCHAR(50) DEFAULT 'berlangsung',
    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 CASCADE
);



-- Tabel compliance untuk tracking kepatuhan minum TTD
CREATE TABLE compliance_ttd (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    tanggal DATE NOT NULL,
    status ENUM('sudah', 'belum') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_date (user_id, tanggal)
);

-- Tabel alarm TTD untuk pengingat minum tablet tambah darah
CREATE TABLE alarm_ttd (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    judul VARCHAR(255) NOT NULL DEFAULT 'Alarm TTD',
    deskripsi TEXT,
    waktu_alarm TIME NOT NULL,
    hari_aktif JSON, -- Array hari dalam seminggu [1,2,3,4,5,6,7]
    aktif BOOLEAN DEFAULT TRUE,
    periode_hari INT DEFAULT 270, -- Periode 270 hari
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    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 CASCADE
);

-- Tabel alarm ANC untuk pengingat pemeriksaan kehamilan
CREATE TABLE alarm_anc (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    judul VARCHAR(255) NOT NULL DEFAULT 'Alarm ANC',
    deskripsi TEXT,
    waktu_alarm TIME NOT NULL,
    hari_aktif JSON, -- Array hari dalam seminggu [1,2,3,4,5,6,7]
    aktif BOOLEAN DEFAULT TRUE,
    frekuensi ENUM('mingguan', 'bulanan', 'trimester') DEFAULT 'bulanan',
    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 CASCADE
);

-- Tabel compliance ANC untuk tracking pemeriksaan
CREATE TABLE compliance_anc (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    tanggal DATE NOT NULL,
    status ENUM('sudah', 'belum') NOT NULL,
    jenis_pemeriksaan VARCHAR(100),
    catatan TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_date_anc (user_id, tanggal)
);



-- Tabel video untuk konten edukasi
CREATE TABLE video (
    id INT AUTO_INCREMENT PRIMARY KEY,
    divideo VARCHAR(100) UNIQUE NOT NULL, -- ID unik video
    judulvideo VARCHAR(255) NOT NULL,
    deskripsivideo TEXT NOT NULL,
    linkyoutube VARCHAR(500) NOT NULL,
    trimester ENUM('1', '2', '3') NOT NULL,
    thumbnail VARCHAR(500),
    durasi VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 3. INSERT SAMPLE DATA
-- =====================================================

-- Sample user (password: password123)
INSERT IGNORE INTO users (id, nama, email, ttl, nik, alamat, kata_sandi) VALUES
(1, 'Admin SmartKing', 'admin@smartking.com', '1990-01-01', '1234567890123456', 'Jakarta', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi'),
(2, 'Test User', 'test@test.com', '2000-01-01', '1234567890123457', 'Bandung', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');

-- Sample profil pengguna
INSERT IGNORE INTO profil_pengguna (user_id, nama, ttl, tempat_lahir, hpht, tp) VALUES
(1, 'Admin SmartKing', '1990-01-01', 'Jakarta', '2024-01-01', '2024-10-08'),
(2, 'Test User', '2000-01-01', 'Bandung', '2024-06-01', '2025-03-08');

-- Sample riwayat kehamilan
INSERT IGNORE INTO riwayat_kehamilan (user_id, hpht, tp, status) VALUES
(1, '2024-01-01', '2024-10-08', 'berlangsung'),
(2, '2024-06-01', '2025-03-08', 'berlangsung');

-- Sample alarm TTD
INSERT IGNORE INTO alarm_ttd (user_id, judul, deskripsi, waktu_alarm, hari_aktif, start_date, end_date) VALUES
(1, 'Pengingat TTD Pagi', 'Minum tablet tambah darah setiap pagi', '08:00:00', '[1,2,3,4,5,6,7]', '2024-01-01', '2024-10-08'),
(2, 'Pengingat TTD', 'Jangan lupa minum TTD', '09:00:00', '[1,2,3,4,5,6,7]', '2024-06-01', '2025-03-08');

-- Sample alarm ANC
INSERT IGNORE INTO alarm_anc (user_id, judul, deskripsi, waktu_alarm, hari_aktif, frekuensi) VALUES
(1, 'Kontrol ANC', 'Jadwal kontrol kehamilan', '10:00:00', '[1,3,5]', 'bulanan'),
(2, 'Pemeriksaan Rutin', 'Kontrol kehamilan rutin', '14:00:00', '[2,4]', 'bulanan');

-- Sample compliance TTD
INSERT IGNORE INTO compliance_ttd (user_id, tanggal, status) VALUES
(1, '2024-09-01', 'sudah'),
(1, '2024-09-02', 'sudah'),
(1, '2024-09-03', 'belum'),
(2, '2024-09-01', 'sudah'),
(2, '2024-09-02', 'sudah');

-- Sample compliance ANC
INSERT IGNORE INTO compliance_anc (user_id, tanggal, status, jenis_pemeriksaan) VALUES
(1, '2024-09-01', 'sudah', 'Kontrol rutin'),
(2, '2024-09-01', 'sudah', 'USG');

-- Insert sample data untuk video dengan link YouTube yang benar
INSERT IGNORE INTO video (divideo, judulvideo, deskripsivideo, linkyoutube, trimester, durasi, thumbnail) VALUES
('VID001', 'Olahraga saat Hamil', 'Video edukasi tentang olahraga yang aman dilakukan selama kehamilan trimester pertama', 'https://youtu.be/D4V-x74fx-E?si=pk0__NtrwVI_iGW3', '1', '10:30', 'https://img.youtube.com/vi/D4V-x74fx-E/maxresdefault.jpg'),
('VID002', 'Perkembangan Janin', 'Video tentang perkembangan janin di trimester pertama', 'https://youtu.be/D4V-x74fx-E?si=pk0__NtrwVI_iGW3', '1', '8:45', 'https://img.youtube.com/vi/D4V-x74fx-E/maxresdefault.jpg'),
('VID003', 'Nutrisi Trimester 2', 'Video tentang kebutuhan nutrisi di trimester kedua', 'https://youtu.be/D4V-x74fx-E?si=pk0__NtrwVI_iGW3', '2', '12:15', 'https://img.youtube.com/vi/D4V-x74fx-E/maxresdefault.jpg'),
('VID004', 'Persiapan Persalinan', 'Video tentang persiapan menghadapi persalinan di trimester ketiga', 'https://youtu.be/D4V-x74fx-E?si=pk0__NtrwVI_iGW3', '3', '15:20', 'https://img.youtube.com/vi/D4V-x74fx-E/maxresdefault.jpg'),
('VID005', 'Pentingnya Tablet Tambah Darah', 'Edukasi tentang manfaat dan cara konsumsi TTD yang benar', 'https://youtu.be/D4V-x74fx-E?si=pk0__NtrwVI_iGW3', '1', '9:30', 'https://img.youtube.com/vi/D4V-x74fx-E/maxresdefault.jpg'),
('VID006', 'Perawatan Bayi Baru Lahir', 'Panduan perawatan bayi dalam 1000 hari pertama', 'https://youtu.be/D4V-x74fx-E?si=pk0__NtrwVI_iGW3', '3', '11:45', 'https://img.youtube.com/vi/D4V-x74fx-E/maxresdefault.jpg');

-- 4. CREATE VIEWS (Optional)
-- =====================================================

-- View untuk data lengkap user dengan profil
CREATE OR REPLACE VIEW v_user_profile AS
SELECT 
    u.id,
    u.nama,
    u.email,
    u.ttl,
    u.nik,
    u.alamat,
    u.created_at as user_created_at,
    p.tempat_lahir,
    p.hpht,
    p.tp,
    p.created_at as profile_created_at,
    p.updated_at as profile_updated_at,
    DATEDIFF(CURDATE(), p.hpht) as hari_kehamilan,
    ROUND(DATEDIFF(CURDATE(), p.hpht) / 7, 1) as minggu_kehamilan
FROM users u
LEFT JOIN profil_pengguna p ON u.id = p.user_id;

-- View untuk statistik compliance TTD
CREATE OR REPLACE VIEW v_compliance_ttd_stats AS
SELECT 
    user_id,
    COUNT(*) as total_hari,
    SUM(CASE WHEN status = 'sudah' THEN 1 ELSE 0 END) as sudah,
    SUM(CASE WHEN status = 'belum' THEN 1 ELSE 0 END) as belum,
    ROUND((SUM(CASE WHEN status = 'sudah' THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2) as persentase_kepatuhan
FROM compliance_ttd
GROUP BY user_id;

-- 5. OPTIMIZATION SETTINGS
-- =====================================================

-- Optimize tables
OPTIMIZE TABLE users, profil_pengguna, riwayat_kehamilan, alarm_ttd, alarm_anc, compliance_ttd, compliance_anc, video;

-- 6. FINAL MESSAGE
-- =====================================================
SELECT 'Database SMARTKING-AYU berhasil dibuat untuk produksi!' as message;
SELECT 'Total tables created: 8' as info;
SELECT 'Sample data inserted successfully' as status;