CREATE TABLE IF NOT EXISTS menu (
  id VARCHAR(64) PRIMARY KEY,
  nama VARCHAR(150) NOT NULL,
  kategori ENUM('makanan','minuman') NOT NULL DEFAULT 'makanan',
  harga DECIMAL(15,2) NOT NULL DEFAULT 0,
  stok INT NOT NULL DEFAULT 0,
  foto LONGTEXT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_menu_kategori (kategori),
  INDEX idx_menu_nama (nama)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS transaksi (
  id VARCHAR(64) PRIMARY KEY,
  tanggal DATETIME NOT NULL,
  customer VARCHAR(150) NULL,
  wa VARCHAR(40) NULL,
  items JSON NULL,
  subtotal DECIMAL(15,2) NOT NULL DEFAULT 0,
  diskon DECIMAL(15,2) NOT NULL DEFAULT 0,
  total DECIMAL(15,2) NOT NULL DEFAULT 0,
  metode VARCHAR(30) NULL,
  bayar DECIMAL(15,2) NOT NULL DEFAULT 0,
  kembali DECIMAL(15,2) NOT NULL DEFAULT 0,
  kasir VARCHAR(100) NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_transaksi_tanggal (tanggal),
  INDEX idx_transaksi_metode (metode),
  INDEX idx_transaksi_customer (customer)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS belanja_harian (
  id VARCHAR(64) PRIMARY KEY,
  tanggal DATE NOT NULL,
  nominal DECIMAL(15,2) NOT NULL DEFAULT 0,
  keterangan VARCHAR(255) NOT NULL,
  kasir VARCHAR(100) NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_belanja_tanggal (tanggal)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pesanan (
  id VARCHAR(64) PRIMARY KEY,
  tanggal DATETIME NOT NULL,
  customerName VARCHAR(150) NULL,
  customer VARCHAR(150) NULL,
  wa VARCHAR(40) NULL,
  catatan TEXT NULL,
  items JSON NULL,
  subtotal DECIMAL(15,2) NOT NULL DEFAULT 0,
  diskon DECIMAL(15,2) NOT NULL DEFAULT 0,
  total DECIMAL(15,2) NOT NULL DEFAULT 0,
  paymentMethod VARCHAR(30) NULL,
  metode VARCHAR(30) NULL,
  paymentStatus VARCHAR(50) NULL,
  status VARCHAR(50) NULL,
  kasir VARCHAR(100) NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_pesanan_tanggal (tanggal),
  INDEX idx_pesanan_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;