CREATE DATABASE IF NOT EXISTS goeat_db;
USE goeat_db;

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nisn` varchar(50) NOT NULL UNIQUE,
  `nama` varchar(100) NOT NULL,
  `kelas` varchar(20) DEFAULT NULL,
  `jurusan` varchar(50) DEFAULT NULL,
  `lantai` varchar(10) DEFAULT NULL,
  `role` enum('siswa','bc') NOT NULL DEFAULT 'siswa',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nama_produk` varchar(100) NOT NULL,
  `kategori` enum('makanan','minuman') NOT NULL,
  `harga` decimal(10,2) NOT NULL,
  `gambar` varchar(255) DEFAULT NULL,
  `deskripsi` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `total_harga` decimal(10,2) NOT NULL,
  `jenis_pengiriman` enum('regular','express') NOT NULL DEFAULT 'regular',
  `biaya_pengiriman` decimal(10,2) NOT NULL DEFAULT 0.00,
  `metode_pembayaran` enum('cash','qris') NOT NULL DEFAULT 'cash',
  `status` enum('menunggu_konfirmasi','masih_disaji','siap_dikirim','selesai','ditolak') NOT NULL DEFAULT 'menunggu_konfirmasi',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `order_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `qty` int(11) NOT NULL,
  `harga_satuan` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert some dummy products
INSERT INTO `products` (`nama_produk`, `kategori`, `harga`, `gambar`, `deskripsi`) VALUES
('Nasi Goreng', 'makanan', 12000, 'https://via.placeholder.com/150', 'Nasi goreng spesial'),
('Mie Goreng', 'makanan', 10000, 'https://via.placeholder.com/150', 'Mie goreng sedap'),
('Es Teh Manis', 'minuman', 4000, 'https://via.placeholder.com/150', 'Es teh manis segar'),
('Es Jeruk', 'minuman', 5000, 'https://via.placeholder.com/150', 'Es jeruk asli');

-- Insert dummy BC account (Bisnis Center) for testing
INSERT INTO `users` (`nisn`, `nama`, `role`) VALUES ('BC001', 'Admin Bisnis Center', 'bc');