CREATE DATABASE IF NOT EXISTS `testing`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `testing`;

CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama` VARCHAR(100) NOT NULL,
  `email` VARCHAR(120) NOT NULL,
  `whatsapp` VARCHAR(20) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('admin', 'client') NOT NULL DEFAULT 'client',
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  UNIQUE KEY `users_whatsapp_unique` (`whatsapp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `products` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama_produk` VARCHAR(120) NOT NULL,
  `harga` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `deskripsi` TEXT NULL,
  `marketing_badges` VARCHAR(120) NULL,
  `status` ENUM('aktif', 'nonaktif') NOT NULL DEFAULT 'aktif',
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `purchases` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `harga` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `bukti_transfer` VARCHAR(255) NULL,
  `status` ENUM('pending', 'verified', 'completed', 'rejected') NOT NULL DEFAULT 'pending',
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_purchases_user_id` (`user_id`),
  KEY `idx_purchases_product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `products` (`nama_produk`, `harga`, `deskripsi`, `status`, `created_at`, `updated_at`)
SELECT 'Jaringan Standar', 550000, 'Paket internet standar untuk kebutuhan harian.', 'aktif', NOW(), NOW()
WHERE NOT EXISTS (
  SELECT 1 FROM `products` WHERE `nama_produk` = 'Jaringan Standar'
);

INSERT INTO `products` (`nama_produk`, `harga`, `deskripsi`, `status`, `created_at`, `updated_at`)
SELECT 'Jaringan Top', 750000, 'Paket internet dengan performa lebih stabil dan cepat.', 'aktif', NOW(), NOW()
WHERE NOT EXISTS (
  SELECT 1 FROM `products` WHERE `nama_produk` = 'Jaringan Top'
);

INSERT INTO `products` (`nama_produk`, `harga`, `deskripsi`, `marketing_badges`, `status`, `created_at`, `updated_at`)
SELECT 'Jaringan Super Top', 999000, 'Paket rekomendasi dengan performa terbaik.', 'top_recommended', 'aktif', NOW(), NOW()
WHERE NOT EXISTS (
  SELECT 1 FROM `products` WHERE `nama_produk` = 'Jaringan Super Top'
);

INSERT INTO `products` (`nama_produk`, `harga`, `deskripsi`, `status`, `created_at`, `updated_at`)
SELECT 'Jaringan Premium', 1500000, 'Paket premium untuk kebutuhan paling tinggi.', 'aktif', NOW(), NOW()
WHERE NOT EXISTS (
  SELECT 1 FROM `products` WHERE `nama_produk` = 'Jaringan Premium'
);