-- Contoh Query Database untuk Aplikasi Prediksi Cuaca
-- File ini berisi query-query yang sesuai dengan implementasi PHP

-- =============================================================================
-- SETUP DATABASE
-- =============================================================================

-- Buat database
CREATE DATABASE IF NOT EXISTS cuaca_prediksi
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Gunakan database
USE cuaca_prediksi;

-- =============================================================================
-- TABEL UTAMA
-- =============================================================================

-- Tabel weather_predictions
CREATE TABLE weather_predictions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lokasi VARCHAR(100) NOT NULL,
    suhu DOUBLE NOT NULL,
    terasa_seperti DOUBLE NOT NULL,
    kondisi_cuaca ENUM('cerah', 'hujan', 'berawan', 'mendung', 'badai', 'salju', 'kabut') NOT NULL,
    icon_kondisi VARCHAR(50) NOT NULL,
    tanggal_jam DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabel users untuk authentication
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    nama VARCHAR(100) NOT NULL,
    level ENUM('admin', 'user') DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =============================================================================
-- DATA SAMPLE
-- =============================================================================

-- Insert admin default
INSERT INTO users (username, password, nama, level) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Administrator', 'admin');

-- Insert contoh data cuaca
INSERT INTO weather_predictions (lokasi, suhu, terasa_seperti, kondisi_cuaca, icon_kondisi, tanggal_jam) VALUES
('Jakarta', 30.5, 32.1, 'cerah', '☀️', '2025-11-14 10:00:00'),
('Bandung', 25.3, 26.8, 'berawan', '🌤️', '2025-11-14 10:00:00'),
('Surabaya', 31.2, 33.5, 'hujan', '🌧️', '2025-11-14 10:00:00'),
('Yogyakarta', 29.8, 31.2, 'cerah', '☀️', '2025-11-14 11:00:00'),
('Semarang', 28.7, 30.3, 'mendung', '⛅', '2025-11-14 11:00:00');

-- =============================================================================
-- QUERY-QUERY CRUD (SESUAI DENGAN IMPLEMENTASI PHP)
-- =============================================================================

-- LOGIN - Authentication (dari login.php)
SELECT id, username, nama, password, level
FROM users
WHERE username = 'admin';

-- DASHBOARD - Total records (dari dashboard.php)
SELECT COUNT(*) as total FROM weather_predictions;

-- DASHBOARD - Recent forecasts (dari dashboard.php)
SELECT * FROM weather_predictions
ORDER BY created_at DESC
LIMIT 5;

-- TAMPILAN DATA - Basic select dengan pagination (dari tampil.php)
SELECT * FROM weather_predictions
ORDER BY tanggal_jam DESC
LIMIT 10 OFFSET 0;

-- TAMPILAN DATA - Dengan search filter (dari tampil.php)
SELECT * FROM weather_predictions
WHERE (lokasi LIKE '%jakarta%' OR kondisi_cuaca LIKE '%cerah%')
ORDER BY tanggal_jam DESC
LIMIT 10 OFFSET 0;

-- TAMPILAN DATA - Dengan kondisi filter (dari tampil.php)
SELECT * FROM weather_predictions
WHERE kondisi_cuaca = 'cerah'
ORDER BY tanggal_jam DESC
LIMIT 10 OFFSET 0;

-- TAMPILAN DATA - Count total untuk pagination
SELECT COUNT(*) as total FROM weather_predictions;

-- TAMPILAN DATA - Count dengan filter
SELECT COUNT(*) as total FROM weather_predictions
WHERE kondisi_cuaca = 'cerah';

-- TAMBAH DATA - Insert baru (dari tambah.php)
INSERT INTO weather_predictions (lokasi, suhu, terasa_seperti, kondisi_cuaca, icon_kondisi, tanggal_jam)
VALUES ('Denpasar', 32.5, 34.2, 'cerah', '☀️', '2025-11-14 12:00:00');

-- UBAH DATA - Select untuk form edit (dari ubah.php)
SELECT * FROM weather_predictions WHERE id = 1;

-- UBAH DATA - Update data (dari ubah.php)
UPDATE weather_predictions
SET lokasi = 'Jakarta Barat',
    suhu = 31.0,
    terasa_seperti = 32.5,
    kondisi_cuaca = 'berawan',
    icon_kondisi = '🌤️',
    tanggal_jam = '2025-11-14 13:00:00'
WHERE id = 1;

-- HAPUS DATA - Select untuk konfirmasi hapus (dari hapus.php)
SELECT * FROM weather_predictions WHERE id = 1;

-- HAPUS DATA - Delete data (dari hapus.php)
DELETE FROM weather_predictions WHERE id = 1;

-- =============================================================================
-- QUERY REPORTING & ANALISIS (BISA DITAMBAHKAN)
-- =============================================================================

-- Laporan per kondisi cuaca
SELECT kondisi_cuaca, COUNT(*) as jumlah, AVG(suhu) as rata_rata_suhu
FROM weather_predictions
GROUP BY kondisi_cuaca
ORDER BY jumlah DESC;

-- Laporan per lokasi
SELECT lokasi, COUNT(*) as jumlah_prediksi, AVG(suhu) as rata_rata_suhu,
       MIN(tanggal_jam) as pertama, MAX(tanggal_jam) as terakhir
FROM weather_predictions
GROUP BY lokasi
ORDER BY jumlah_prediksi DESC;

-- Laporan harian
SELECT DATE(tanggal_jam) as tanggal,
       COUNT(*) as jumlah_prediksi,
       AVG(suhu) as rata_rata_suhu,
       GROUP_CONCAT(DISTINCT kondisi_cuaca) as kondisi_unik
FROM weather_predictions
WHERE tanggal_jam >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY DATE(tanggal_jam)
ORDER BY tanggal DESC;

-- Pencarian berdasarkan suhu range
SELECT * FROM weather_predictions
WHERE suhu BETWEEN 25 AND 30
ORDER BY suhu DESC;

-- Data terbaru per lokasi (untuk API)
SELECT wp1.*
FROM weather_predictions wp1
INNER JOIN (
    SELECT lokasi, MAX(tanggal_jam) as latest_time
    FROM weather_predictions
    GROUP BY lokasi
) wp2 ON wp1.lokasi = wp2.lokasi AND wp1.tanggal_jam = wp2.latest_time
ORDER BY wp1.tanggal_jam DESC;

-- =============================================================================
-- QUERY MAINTENANCE
-- =============================================================================

-- Backup tabel (contoh untuk export data)
SELECT * FROM weather_predictions ORDER BY id;

-- Hapus data lama (contoh cleanup)
DELETE FROM weather_predictions
WHERE tanggal_jam < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- Reset auto increment
ALTER TABLE weather_predictions AUTO_INCREMENT = 1;

-- =============================================================================
-- INDEX OPTIMIZATION
-- =============================================================================

-- Tambah index untuk performa (sudah ada di atas, tapi sebagai referensi)
CREATE INDEX idx_lokasi ON weather_predictions(lokasi);
CREATE INDEX idx_kondisi_cuaca ON weather_predictions(kondisi_cuaca);
CREATE INDEX idx_tanggal_jam ON weather_predictions(tanggal_jam);
CREATE INDEX idx_created_at ON weather_predictions(created_at);
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_level ON users(level);
