

-- Tabel kategori
CREATE TABLE `kategori` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama_kategori` VARCHAR(100) NOT NULL,
  `created_at` DATETIME NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabel produk
CREATE TABLE `produk` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `kategori_id` INT UNSIGNED NOT NULL,
  `nama_produk` VARCHAR(255) NOT NULL,
  `deskripsi` TEXT NULL,
  `harga` INT UNSIGNED NOT NULL,
  `gambar` VARCHAR(255) NULL,
  `created_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_produk_kategori` FOREIGN KEY (`kategori_id`) REFERENCES `kategori`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabel pesanan
CREATE TABLE `pesanan` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `status` VARCHAR(50) NOT NULL DEFAULT 'pending',
  `total_harga` INT UNSIGNED NOT NULL,
  `created_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_pesanan_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabel pesanan_detail
CREATE TABLE `pesanan_detail` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `pesanan_id` INT UNSIGNED NOT NULL,
  `produk_id` INT UNSIGNED NOT NULL,
  `qty` INT UNSIGNED NOT NULL,
  `harga` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_pesanan_detail_pesanan` FOREIGN KEY (`pesanan_id`) REFERENCES `pesanan`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_pesanan_detail_produk` FOREIGN KEY (`produk_id`) REFERENCES `produk`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabel ci_sessions
CREATE TABLE `ci_sessions` (
  `id` VARCHAR(128) NOT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `timestamp` INT UNSIGNED NOT NULL,
  `data` BLOB NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert kategori dummy
INSERT INTO `kategori` (`nama_kategori`, `created_at`) VALUES
('Canva', NOW()),
('YouTube Premium', NOW()),
('Mobile Legends', NOW()),
('Free Fire', NOW());

-- Insert produk dummy
INSERT INTO `produk` (`kategori_id`, `nama_produk`, `deskripsi`, `harga`, `gambar`, `created_at`) VALUES
((SELECT `id` FROM `kategori` WHERE `nama_kategori` = 'Canva'), 'Canva Pro 1 Bulan', 'Akses akun Canva Pro selama 1 bulan penuh.', 50000, 'canva_pro_1bulan.jpg', NOW()),
((SELECT `id` FROM `kategori` WHERE `nama_kategori` = 'YouTube Premium'), 'YouTube Premium 1 Bulan', 'Akses YouTube Premium 1 bulan tanpa iklan.', 70000, 'yt_premium_1bulan.jpg', NOW()),
((SELECT `id` FROM `kategori` WHERE `nama_kategori` = 'Mobile Legends'), 'Mobile Legends Diamond 1000', 'Top up diamond MLBB sebanyak 1000 diamond.', 120000, 'mlbb_diamond_1000.jpg', NOW()),
((SELECT `id` FROM `kategori` WHERE `nama_kategori` = 'Free Fire'), 'Free Fire Diamond 500', 'Top up diamond Free Fire sebanyak 500 diamond.', 75000, 'ff_diamond_500.jpg', NOW());
