
CREATE TABLE `gudang` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `nama` VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `categories` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `nama` VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `units` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `nama` VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `customers` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `nama` VARCHAR(100) NOT NULL,
  `alamat` TEXT,
  `telepon` VARCHAR(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `suppliers` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `nama` VARCHAR(100) NOT NULL,
  `alamat` TEXT,
  `telepon` VARCHAR(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `items` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `barcode` VARCHAR(100) NOT NULL,
  `nama` VARCHAR(100) NOT NULL,
  `harga` DECIMAL(10,2) NOT NULL,
  `stok` INT DEFAULT 0,
  `id_unit` INT,
  `id_category` INT,
  `id_gudang` INT,
  FOREIGN KEY (`id_unit`) REFERENCES `units`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`id_category`) REFERENCES `categories`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`id_gudang`) REFERENCES `gudang`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `sales` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `tanggal` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `customer_id` INT,
  `total` DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `sale_detail` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `sale_id` INT,
  `item_id` INT,
  `harga` DECIMAL(10,2),
  `qty` INT,
  `subtotal` DECIMAL(12,2),
  FOREIGN KEY (`sale_id`) REFERENCES `sales`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`item_id`) REFERENCES `items`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `product_in` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `item_id` INT,
  `supplier_id` INT,
  `qty` INT NOT NULL,
  `tanggal` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `keterangan` TEXT,
  `id_gudang` INT,
  FOREIGN KEY (`item_id`) REFERENCES `items`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`supplier_id`) REFERENCES `suppliers`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`id_gudang`) REFERENCES `gudang`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `product_out` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `item_id` INT,
  `qty` INT NOT NULL,
  `tanggal` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `keterangan` TEXT,
  `id_gudang` INT,
  FOREIGN KEY (`item_id`) REFERENCES `items`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`id_gudang`) REFERENCES `gudang`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
